Click here to Skip to main content
15,439,373 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;
 
Share this answer
 

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