使用DataTable和ExcelLibrary如何创建Excel表格？

Question

使用DataTable和ExcelLibrary如何创建Excel表格？

3

我有一个数据表，其中包含多列和日期，数据类型为 "DATE"。

我尝试了以下选项：

1）第三方DLL- ExcelLibrary 如果数据集中没有日期列，则它可以正常工作，否则它会使用一些虚拟值（如-65284）代替日期。

ExcelLibrary.DataSetHelper.CreateWorkbook(@"C:\Users\ABC\Documents\Excel\Report123.xls", ds);

2) 使用简单导出格式而不使用第三方DLL，如下所示

public void ExportToExcel(System.Data.DataTable dt)
{
    if (dt.Rows.Count > 0)
    {
        string filename = "Report123.xls";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();

        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
}

以上代码可以完美地提取Excel，但当我们打开同一个Excel时，会出现格式错误的错误。

我也想读取同一个文件到datatable中，以存储到数据库中。当我尝试读取创建的Excel文件（通过第二个选项）时，我会得到外部表格不符合预期格式的错误。如果我将同一文件另存为，则可以正常工作。

但我不想每次都“另存为”文件，请帮我解决这个问题。

更新：

public void ExportToExcel1(System.Data.DataTable dt)
{
    //clear the response of any junk. This may not be necessary
    Response.Clear();

    //add a header so it has a nice file name
    Response.AddHeader("content-disposition", "attachment;filename=Reportengg.xlsx");

    //Set the MIME type correctly
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    //create a new package, this is the equivalent of an XLSX file.
    var package = new ExcelPackage();

    //Add a new sheet to the workbook
    var sheet = package.Workbook.Worksheets.Add("Sheet1");

    //EPPlus contains helper function to load data from a DataTable, though you could manually fill in rows/column values yourself if you want
    sheet.Cells["A1"].LoadFromDataTable(dt, true);
  //  byte[] array = package.GetAsByteArray();
    //write the file bytes to the response
    Response.BinaryWrite(package.GetAsByteArray());


    //end the response so we don't send anymore down and corrupt the file
    Response.End();
}

- dwan

第二种技术是要避免的。格式确实有误。它不是一个Excel文件，而是一个带有Excel扩展名和MIME类型的HTML文件。你应该坚持像第一种技术那样创建真正的Excel文件。 - mason

2个回答

-1

这个对我有用：

        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Report123.xls"));
        Response.ContentType = "application/vnd.ms-excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
        GridView1.AllowPaging = false;
        GridView1.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.End();

- losopha

@losopha- 出现相同的错误 "外部表格格式不符合预期。" - dwan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- mason · Accepted Answer

您的＃1技术是将文件保存在服务器上。服务器端代码无法直接保存到客户端文件系统。您必须将文件字节写入响应，然后客户端的PC将选择如何处理它。无论他们选择“另存为”还是直接保存到某个下载文件夹中，都取决于其浏览器设置。我不熟悉ExcelLibrary，但我想象他们有某种API来获取文件字节？那么，请执行此操作。然后将这些字节写入响应。

byte[] bytes = GetBytesFromTheirApiSomehow();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Disposition", "attachment; filename=filename.xlsx");
Response.BinaryWrite(bytes);
Response.End();

我查看了ExcelLibrary的源代码。该库似乎不再维护。也许你应该转向一个正在积极维护的库，例如EPPlus。一个EPPlus实现可能如下所示：

public void ExportToExcel(System.Data.DataTable dt)
{
    //clear the response of any junk. This may not be necessary
    Response.Clear();

    //add a header so it has a nice file name
    Response.AddHeader("content-disposition", "attachment;filename=myexcelfile.xlsx");

    //Set the MIME type correctly
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    //create a new package, this is the equivalent of an XLSX file.
    var package = new ExcelPackage();

    //Add a new sheet to the workbook
    var sheet = package.Workbook.Worksheets.Add("My Data"); 

    //EPPlus contains helper function to load data from a DataTable, though you could manually fill in rows/column values yourself if you want
    sheet.Cells["A1"].LoadFromDataTable(dt, true); 

    //write the file bytes to the response
    Response.BinaryWrite(package.GetAsByteArray());

    //end the response so we don't send anymore down and corrupt the file
    Response.End();
}