.net MVC 数据库数据导出到Excel表格并下载到本地

需要引用组件： NPOI

NPOI.OOXML,

NPOI.OpenXML4Net,

NPOI.OpenXmlFormats

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

public FileResult Export()
        {
            //Excel表格 存储路径 
            string path = Server.MapPath(string.Format("/Template/{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssffff")));
            //获取要导出的数据
            DataTable dt = new BLL.ZK_T_InspectionLedger().GetAllList().Tables[0];
            string year = dt.Rows[0]["WorkYear"] == null ? "" : dt.Rows[0]["WorkYear"].ToString();
            //删除表中不需要导出的列
            dt.Columns.Remove("ID");
            dt.Columns.Remove("WorkYear");
            dt.Columns.Remove("CreateTime");

            //修改列名
            dt.Columns["SerialNumber"].ColumnName = "序号";
            dt.Columns["KeyWork"].ColumnName = "重点工作";
            dt.Columns["ResponsibleLeadership"].ColumnName = "责任领导";
            dt.Columns["UndertakePostAndUser"].ColumnName = "承办部门及承办人";
            dt.Columns["CompletionTimeLimit"].ColumnName = "完成时限";
            dt.Columns["WorkProgress"].ColumnName = "工作进展";
            dt.Columns["ExistingProblems"].ColumnName = "存在问题";
            dt.Columns["ActualProgress"].ColumnName = "实际进度";
            dt.Columns["TimeProgress"].ColumnName = "时间进度";

            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);//创建工作表

            #region 标题
            IRow row = sheet.CreateRow(0);//在工作表中添加一行
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);//在行中添加一列
                sheet.SetColumnWidth(i, 10 * 512);
                cell.SetCellValue(dt.Columns[i].ColumnName);//设置列的内容	 
            }
            #endregion

            #region 填充数据
            for (int i = 1; i <= dt.Rows.Count; i++)//遍历DataTable行
            {
                DataRow dataRow = dt.Rows[i - 1];
                row = sheet.CreateRow(i);//在工作表中添加一行

                for (int j = 0; j < dt.Columns.Count; j++)//遍历DataTable列
                {
                    ICell cell = row.CreateCell(j);//在行中添加一列
                    cell.SetCellValue(dataRow[j].ToString());//设置列的内容	 
                }
            }
            #endregion

            #region 输出到Excel
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);

            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                byte[] bArr = ms.ToArray();
                fs.Write(bArr, 0, bArr.Length);
                fs.Flush();
            }
            #endregion

            return File(path, "application/octet-stream", string.Format("{0}年新媒体产业基地{1}项重点工作.xls",year, dt.Rows.Count));
        }