最近在用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 == 0) 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[1]; 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 + 1, colCount];//标题 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //获取实际数据 for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) { for (int colIndex = 0; colIndex < colCount; colIndex++) { objData[rowIndex, colIndex] = dt.Rows[rowIndex][colIndex].ToString(); } } for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) { objData2[rowIndex, 0] = dt.Rows[rowIndex][3].ToString(); objData3[rowIndex, 0] = dt.Rows[rowIndex][14].ToString(); objData4[rowIndex, 0] = dt.Rows[rowIndex][46].ToString(); objData5[rowIndex, 0] = dt.Rows[rowIndex][49].ToString(); objData6[rowIndex, 0] = dt.Rows[rowIndex][52].ToString(); } range = (Range)worksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowCount + 1, 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 = 0; 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 == 0) { byteFile = new byte[1]; } else { byteFile = new byte[files.Length]; } files.Read(byteFile, 0, (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(); }
用了这个方法以后,的确比用stringbuilder快很多,而且可以导出正确格式的xlsx,但是打不开,后来发现问题出在这
//xlApp.ScreenUpdating = false; //屏幕不跟新,加快速度
把这一行注释了以后,就正常了,虽然我也不是很明白原理....
因为只会调用方法,但是不明白方法中每个语句的含义和用法,所以会有很多问题,希望自己能慢慢解决吧
2019-04-21