.Net Core SQL结果直接生成Excel表格

可以将任意SQL查询结果生成Excel表格以供下载

1.引用依赖包

 

 

 

 2.执行查询SQL，获得结果列表

var sql = "SELECT Id AS N'学生编号', Name AS N'学生姓名' FROM Student";
var queryList = _context.Database.GetDbConnection().Query(sql).ToList();

查看Query方法定义可知返回结果虽然是IEnumerable<dynamic>，实际上是IEnumerable<DapperRow>

查看DapperRow定义可知其支持IDictionary<string, object>接口，实际上只要支持该接口均可动态生成Excel表格

 

 

 

3.传入表名和queryList调用以下方法，取第一个元素的键名作为表头名，然后生成Excel表格对象

public static HSSFWorkbook BuildSwitchData(string sheetName, IList<dynamic> list)
        {
            if (list == null || !list.Any())
                return null;

            var keys = ((IDictionary<string, object>)list.First()).Keys;
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
            row_Title.HeightInPoints = 25F; //设置列头行高
            HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
            cs_Title_Font.IsBold = true; //字体加粗
            cs_Title_Font.FontHeightInPoints = 14; //字体大小
            cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
            #region 生成列头
            int ii = 0;
            foreach (string key in keys)
            {
                HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
                cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                cell_Title.SetCellValue(key);
                sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
                ii++;
            }

            #endregion


            HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            for (int i = 0; i < list.Count; i++)
            {

                HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
                row_Content.HeightInPoints = 15;
                int jj = 0;

                var values = ((IDictionary<string, object>)list[i]).Values.ToArray();
                for (int j = 0; j < values.Length; j++)
                {
                    HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
                    cell_Conent.CellStyle = cs_Content;

                    //获取对应属性的值
                    object value = values[j];
                    string cell_value = value == null ? "" : value.ToString();
                    cell_Conent.SetCellValue(cell_value);
                    jj++;
                }
            }

            return wb;
        }

 

4.将表格对象写入内存后，做为文件流返回给浏览器下载该文件

        private IActionResult GenExportResult(string name, IList<dynamic> dataList)
        {
            var excelBook = NpoiExcelExportUtils.BuildSwitchData("table", dataList);
            if(excelBook == null)
                return NoContent();

            //将Excel表格转化为流，输出
            MemoryStream bookStream = new MemoryStream();//创建文件流
            excelBook.Write(bookStream);
            bookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek，把0位置指定为开始位置
            var fileName = name + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";//文件名

            HttpContext.Response.Headers.Add("Name", System.Web.HttpUtility.UrlEncode(fileName));
            HttpContext.Response.Headers.Add("Access-Control-Expose-Headers", "Name");
            return File(bookStream, "application/vnd.ms-excel", fileName);//最后以文件形式返回
        }