这里是使用Microsoft.Office.Interop.Excel类进行Excel表格操作,核心代码就这些了。麻烦的是服务器上的一些关于access的权限配置。
string file = System.AppDomain.CurrentDomain.BaseDirectory + "对账单\\" + filename; if (ExcelHelper.DataTableToCSV(dt, file + System.Web.HttpContext.Current.Session["us_Name"] + ".csv", new string[] { "rowId" })) { HttpResponse resp; resp = System.Web.HttpContext.Current.Response; resp.Clear(); resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.ContentType = "application/vnd.ms-excel"; resp.Charset = "GB2312"; resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8) + ".csv"); resp.WriteFile(file + System.Web.HttpContext.Current.Session["us_Name"] + ".csv"); }
public static bool DataTableToCSV(System.Data.DataTable excelTable, string filePath,string[] removeFileds) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); app.Visible = false; Workbooks workBooks = app.Workbooks; //创建Excel工作簿 Workbook wBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; foreach (string item in removeFileds) { if (excelTable.Columns.Contains(item)) { excelTable.Columns.Remove(item); } } try { if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; //关键性代码,直接关系到性能问题
Array arr = Array.CreateInstance(typeof(String), row, col); for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { arr.SetValue(excelTable.Rows[i][j].ToString(), i, j); } } Microsoft.Office.Interop.Excel.Range range = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[row + 1, col]); range.Value2 = arr; } int size = excelTable.Columns.Count; for (int k = 0; k < size; k++) { wSheet.Cells[1, 1 + k] = excelTable.Columns[k].ColumnName; Range xlsRang = (Range)wSheet.Cells[1, 1 + k]; xlsRang.EntireColumn.AutoFit(); } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); wBook.SaveCopyAs(filePath); //自定义保存的文件名称 } catch (Exception err) { return false; } finally { workBooks.Close(); //关闭工作簿 if (app != null) { app.Workbooks.Close(); app.Quit(); //退出Excel int intGenerate = System.GC.GetGeneration(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //将XLSAPP从内存托管堆中移除 app = null; System.GC.Collect(intGenerate); } GC.Collect(); //强行退出Excel app = null; } return true; }