我有不包含列的数据表，也没有带有“ 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时，格式错误。
我也想读取数据表中的相同文件以存储在数据库中。当我去阅读创建的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();
}
您的＃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();
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句