导出数据到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组件没有配置好，需要勾选“交互式用户”及其他，在网上可查，我也忘记了在哪了。

无它，本人学的计算机专业，但好久没做这一行业，贴出来的有些浅薄，只为帮助有需要的人和巩固一下。

转载于:https://www.cnblogs.com/yxzs/p/4976917.html