导出数据到EXCEL并生成多个Sheet
一、准备工作
引用:Microsoft.Office.Interop.Excel
准备多个DataTable数据添加到DataSet中。
二、代码
public void CreateExcel(DataSet ds, string filePath) { //创建excel运行环境 Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = null; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet = null; ExcelApp.Visible = true; ExcelWorkBook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //创建excel的sheet名称 List<string> SheetNames = new List<string>(); SheetNames.Add("人力数据分析"); SheetNames.Add("性别"); SheetNames.Add("省份"); SheetNames.Add("城市"); SheetNames.Add("页面访问人数(每天)"); SheetNames.Add("每天具体数据(小时)"); for (int i = 1; i < ds.Tables.Count; i++) ExcelWorkBook.Worksheets.Add(); //添加新的sheet到excel中 for (int i = 0; i < ds.Tables.Count; i++) { int r = 1; // 初始化excel的第一行Position=1 ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets[i + 1]; //样式设置 var range = ExcelWorkSheet.get_Range("A1", "K1"); range.Font.Size = 12; range.Font.Name = "黑体"; range.ColumnWidth = 17; //设置单元格的宽度 //把列的名字写进sheet内 for (int col = 1; col <= ds.Tables[i].Columns.Count; col++) ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Columns[col - 1].ColumnName; r++; //把每一行写进excel的sheet中 for (int row = 0; row < ds.Tables[i].Rows.Count; row++) //r是excelRow,col是excelColumn { //Excel的行和列开始位置写Row=1 ,Col=1 for (int col = 1; col <= ds.Tables[i].Columns.Count; col++) ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Rows[row][col - 1].ToString(); r++; } ExcelWorkSheet.Name = SheetNames[i]; } ExcelWorkBook.SaveAs(filePath); ExcelWorkBook.Close(); ExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWorkBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp); GC.Collect(); foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel")) process.Kill(); }
三、实现跟谷歌类似文件下载后在最下方显示下载的文件
public ActionResult OutPutExcel() { ILog log = LogManager.GetLogger(typeof(SystemSetController)); try { DateTime beginTime = Convert.ToDateTime(Request.Form["LiveBeginTime"]); DateTime endTime = Convert.ToDateTime(Request.Form["LiveEndTime"]); long liveId = Convert.ToInt32(Request.Form["liveId"]); //生成的Excel名称 string fileName = string.Format("{0}.xlsx", DateTime.Now.ToString(@"yyyy-MM-dd-HHmmss")); //获取数据 DataSet ds = _SystemSetBLL.GetLiveAnalysis(beginTime, endTime, liveId); var filePath = Server.MapPath("~/UploadFile/file/" + fileName); //生成EXCEL _SystemSetBLL.CreateExcel(ds, filePath); Response.Clear(); Response.Charset = "utf-8"; Response.HeaderEncoding = Encoding.UTF8; Response.AddHeader("content-type", "application/x-msdownload"); Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName)); Response.ContentType = "application/vnd.ms-excel"; Response.BinaryWrite(System.IO.File.ReadAllBytes(filePath)); Response.End(); } catch (Exception e) { log.Error("导出数据失败:" + e.Message); } return null; }
注意:项目部署到服务器上可能会报错,原因可能是服务器没安装excel或者服务器上的excel组件没有配置好,需要勾选“交互式用户”及其他,在网上可查,我也忘记了在哪了。
无它,本人学的计算机专业,但好久没做这一行业,贴出来的有些浅薄,只为帮助有需要的人和巩固一下。