Click here to Skip to main content
14,869,839 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi.....
i have one application converting multiple .dbf files into single excel sheet my pblm is while generate the excelwork book sheet save as Table1,Table2,......Table6, but i want dbf file name as sheet name

What I have tried:

C#
protected void Button1_Click(object sender, EventArgs e)
{
  string Text = "";
  int i = 0;
  
  if (FileUpload1.HasFiles)
  {
    DataSet ds = new DataSet();
    
    foreach (HttpPostedFile uploadedFile in FileUpload1.PostedFiles)
    {
      uploadedFile.SaveAs(System.IO.Path.Combine(Server.MapPath("~/"), FileUpload1.FileName));

      Text += String.Format("{0}<br>", uploadedFile.FileName);
      string dbfFileName = Server.MapPath(FileUpload1.FileName);// file name with path.
      // fileName = FileUpload1.FileName;// Only file name.
      //string dbfFileName = @"D:\myData.dbf";
      string constr = "Provider=VFPOLEDB.1;Data Source=" + dbfFileName;
      string ExcelFileName = AppDomain.CurrentDomain.BaseDirectory + "converted_file.xls";
      using (OleDbConnection con = new OleDbConnection(constr))
      {
        var sql = "select * from " + Path.GetFileName(dbfFileName) + ";";
        OleDbCommand cmd = new OleDbCommand(sql, con);
        DataTable dt = new DataTable();
                    
        try
        {
          con.Open();
        }
        catch (Exception ex)
        {
          // Console.WriteLine("Error connecting database: " + ex.Message);
          return;
        }
        if (con.State == ConnectionState.Open)
        {
          OleDbDataAdapter da = new OleDbDataAdapter(cmd);
          //  Console.Write("Reading database...  ");
          da.Fill(dt);
          ds.Tables.Add(dt);
                       
          // Console.WriteLine("Completed.");
        }
        if (con.State == ConnectionState.Open)
        {
          try
          {
            con.Close();
          }
          catch
          {
          }
        }
      }
    }
    Response.Write(Text);
    using (XLWorkbook wb = new XLWorkbook())
    {
      foreach (DataTable dt in ds.Tables)
      {
        //Add DataTable as Worksheet.
        wb.Worksheets.Add(dt);
      }

      //Export the Excel file.
      Response.Clear();
      Response.Buffer = true;
      Response.Charset = "";
      Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
      string FileName = "dbffiles" + DateTime.Now + ".xls";
      // Response.AddHeader("content-disposition", "attachment;filename"= + FileName);
      Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
      using (MemoryStream MyMemoryStream = new MemoryStream())
      {
        wb.SaveAs(MyMemoryStream);
                  
        MyMemoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
      }
    }
  }
  else
  {
    Text = "";
  }
}

public override void VerifyRenderingInServerForm(Control control)
{
}
Posted
Updated 20-Jun-17 20:35pm
v2

1 solution

try

OleDbCommand cmd = new OleDbCommand(sql, con);
          DataTable dt = new DataTable();
          dt.TableName = dbfFileName;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900