在excel工作簿中为Excel工作表生成名称 [英] Generate names for excel sheet in excel workbook

问题描述

嗨.....

i有一个应用程序将多个.dbf文件转换为单个excel表格我的pblm是生成excelwork书籍表格保存为Table1，Table2，......表6，但我希望dbf文件名作为工作表名称



我尝试过：

  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）;  //  带路径的文件名。 
  //   fileName = FileUpload1.FileName ; //仅文件名。 
  //   string dbfFileName = @D：\ myData.dbf;  
  string  constr =   Provider = VFPOLEDB.1; Data Source = + dbfFileName; 
  string  ExcelFileName = AppDomain.CurrentDomain.BaseDirectory +   converted_file.xls; 
 使用（OleDbConnection con =  new  OleDbConnection（constr））
 {
  var  sql =   select * from + Path.GetFileName（dbfFileName）+  ;; 
 OleDbCommand cmd =  new  OleDbCommand（sql，con）; 
 DataTable dt =  new  DataTable（）; 
 
 尝试 
 {
 con.Open（）; 
} 
  catch （例外情况）
 {
  //   Console.WriteLine（连接数据库时出错：+ ex.Message）;  
  return ; 
} 
  if （con.State == ConnectionState.Open）
 {
 OleDbDataAdapter da =  new  OleDbDataAdapter（cmd）; 
  //   Console.Write（读取数据库......）;  
 da.Fill（dt）; 
 ds.Tables.Add（dt）; 
 
  //   Console.WriteLine（已完成。）;  
} 
  if （con.State == ConnectionState.Open）
 {
 尝试 
 {
 con.Close（）; 
} 
  catch  
 {
} 
} 
} 
} 
 Response.Write（Text）; 
 使用（XLWorkbook wb =  new  XLWorkbook（））
 {
  foreach （DataTable dt  in  ds.Tables）
 {
  //  将DataTable添加为工作表。 
 wb.Worksheets.Add（dt）; 
} 
 
  //  导出Excel文件。 
 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）; 
 使用（MemoryStream MyMemoryStream =  new  MemoryStream（））
 {
 wb.SaveAs（MyMemoryStream）; 
 
 MyMemoryStream.WriteTo（Response.OutputStream）; 
 Response.Flush（）; 
 Response.End（）; 
} 
} 
} 
 其他 
 {
 Text =  ; 
} 
} 
 
  public  覆盖  void  VerifyRenderingInServerForm（控制控件）
 {
} 

解决方案

< blockquote>尝试

 OleDbCommand cmd = new OleDbCommand（sql，con）; 
 DataTable dt = new DataTable（）; 
  dt.TableName = dbfFileName;  
 

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:

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)
{
}

解决方案

try

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

这篇关于在excel工作簿中为Excel工作表生成名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！