c#用object将datatable快速填充excel后下载表格后打不开的问题

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

最近在用c#的asp.net，需要批量导出数据。原本用的是stringbuilder逐个填充，但是只能做到html强制格式转换为xls，这不是真正的excel表格，所以在网上找了datatable快速填充到excel的方法。

使用该方法后，发现下载了以后的表格没有办法用exce软件l打开，而且电脑的所有的excel文件都打不开了，重启后才可以，花了一天时间才找到问题所在。

如下图所示，打开表格，什么都不显示，表格名字也不对。

有的人遇到这种情况，可能是视图里面选择了隐藏，需要取消隐藏，但是我并没有隐藏数据，而且这几个按钮点击了就变暗，并没有反应

我觉得是我的代码有问题，就仔细检查了，最后终于发现问题所在了

以下是我的方法

protected void ExportExcel(System.Data.DataTable dt)
{
if (dt == null || dt.Rows.Count == ) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
return;
} System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[];
Microsoft.Office.Interop.Excel.Range range, range2, range3, range4, range5, range6, range7;
long totalCount = dt.Rows.Count;
//long rowRead = 0;
//float percent = 0;
xlApp.ScreenUpdating = false; //屏幕不跟新，加快速度
int colCount = dt.Columns.Count;//列总数
int rowCount = dt.Rows.Count;//行总数
object[,] objData = new object[rowCount + , colCount];//标题
for (int i = ; i < dt.Columns.Count; i++)
{
worksheet.Cells[, i + ] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, i + ];
range.Interior.ColorIndex = ;
range.Font.Bold = true;
}
//获取实际数据
for (int rowIndex = ; rowIndex < rowCount; rowIndex++)
{
for (int colIndex = ; colIndex < colCount; colIndex++)
{
objData[rowIndex, colIndex] = dt.Rows[rowIndex][colIndex].ToString();
}
}
for (int rowIndex = ; rowIndex < rowCount; rowIndex++)
{
objData2[rowIndex, ] = dt.Rows[rowIndex][].ToString();
objData3[rowIndex, ] = dt.Rows[rowIndex][].ToString();
objData4[rowIndex, ] = dt.Rows[rowIndex][].ToString();
objData5[rowIndex, ] = dt.Rows[rowIndex][].ToString();
objData6[rowIndex, ] = dt.Rows[rowIndex][].ToString();
}
range = (Range)worksheet.Range[xlApp.Cells[, ], xlApp.Cells[rowCount + , colCount]];
range.NumberFormatLocal = "@";//写入到表中的数据格式以文本形式存在
range.Value2 = objData; worksheet.Cells.Columns.AutoFit();
xlApp.Visible = true;
//11.保存表格到根目录下指定名称的文件中
string pathname = "~/Upload" + "/" + "Pks_data" + "/" + (string)Session[AppSetting.SESSION_USERNAME] +
DateTime.Now.Date.ToString("yyyyMMdd") + ".xlsx";
string filePath = Server.MapPath(pathname);//路径
if (System.IO.File.Exists(Path.GetFullPath(filePath)))
{
File.Delete(Path.GetFullPath(filePath));
}
xlApp.ActiveWorkbook.SaveAs(filePath);
xlApp.Quit();
xlApp = null;
GC.Collect();
DownloadFile(pathname);
} public void DownloadFile(string fileRpath)
{
string filepath = Server.MapPath(fileRpath);
Response.ClearHeaders();
Response.Clear();
Response.Expires = ;
Response.Buffer = true;
Response.AddHeader("Accept-Language", "zh-tw");
string name = System.IO.Path.GetFileName(filepath);
System.IO.FileStream files = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
byte[] byteFile = null;
if (files.Length == )
{
byteFile = new byte[];
}
else
{
byteFile = new byte[files.Length];
}
files.Read(byteFile, , (int)byteFile.Length);
files.Close(); Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8));
Response.ContentType = "application/octet-stream;charset=gbk";
Response.BinaryWrite(byteFile);
Response.Flush();
Response.End(); }