C# 使用NPOI根据数据库实体数据生成自定义excel并导出

头上有犄角不是我の错

已于 2023-12-15 16:21:34 修改

阅读量694

点赞数 18

文章标签： c# excel

于 2023-12-15 16:02:07 首次发布

本文链接：https://blog.csdn.net/weixin_43880874/article/details/135016857

版权

因项目中数据库实体数据需要导出成表格的需求，因此使用了NPOI完成了此功能
其中通过NPOI实现了自定义表格功能，例如根据实体创建工作簿、边框和字体样式、合并单元格、填充单元格颜色、设置超链接跳转指定的工作簿、设置下拉属性等等，希望可以帮到有同需求的伙伴
注：目前只支持生成.xls格式的表格

目录效果图：

实体工作簿效果图：

代码如下：

1.引用系统和对应NPOI包

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

2.主代码

// * 数据准备
var exportList = XXX; // XXX：通过自己方法获取项目对应的实体数据
if (exportList == null || exportList.Count() == 0)
{
    throw new Exception("无实体数据");
}

// * 导出excel，保存临时文件,下载excel文件
// 创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();

// 通用格式设置
ICellStyle headStyle, headYellowStyle, headBlueStyle, iCellStyle, titleStyle;  // 列头，黄色背景列头，蓝色背景列头，单元格，标题
SetPublicStyle(workbook, out headStyle, out headYellowStyle, out headBlueStyle, out iCellStyle, out titleStyle);

// 创建目录Sheet
CreateCatalogueSheet(exportList, workbook, headStyle, headYellowStyle, headBlueStyle, iCellStyle, titleStyle);

// 创建每个实体Sheet
CreateAllBizSheet(exportList, workbook, headStyle, headYellowStyle, headBlueStyle, iCellStyle, titleStyle, sys);

// 文件名称
string fileName = GetFileName(sys);

// * 保存为Excel文件
MemoryStream ms = new MemoryStream();
workbook.Write(ms);

var filePath = Server.MapPath(fileName);
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
{
    byte[] buffer = ms.ToArray(); // 转化为byte格式存储
    fs.Write(buffer, 0, buffer.Length);
    fs.Flush();
}

return File(fileName, MimeMapping.GetMimeMapping(filePath), fileName);

3.数据以及组装的自定义方法

#region 数据

 /// <summary>
 /// 创建目录Sheet
 /// </summary>
 /// <param name="exportList">数据源</param>
 /// <param name="workbook">工作薄</param>
 /// <param name="headStyle">列头样式</param>
 /// <param name="headYellowStyle">黄色背景列头样式</param>
 /// <param name="headBlueStyle">蓝色背景列头样式</param>
 /// <param name="iCellStyle">单元格字体样式</param>
 /// <param name="titleStyle">标题样式</param>
 private static void CreateCatalogueSheet(List<Data> exportList, HSSFWorkbook workbook, ICellStyle headStyle, ICellStyle headYellowStyle, ICellStyle headBlueStyle, ICellStyle iCellStyle, ICellStyle titleStyle)
 {
     ISheet cdSheet = workbook.CreateSheet("目录");
     int cdRowIndex = 0;  // 第几行

     // 定义列头值
     string[] cdHeaderVueList = { "代码", "名称", "XX否", "XXXX", "XXXX", "XXXX", "XX否", "XX否", "描述" };

     // * 创建 标题
     IRow cdTitleRow = cdSheet.CreateRow(cdRowIndex);  // 创建第1行
     cdTitleRow.HeightInPoints = 20;  //高度
     cdTitleRow.CreateCell(cdRowIndex).SetCellValue("数据来源目录"); // 在1行创建第0列并设置标题值
     cdTitleRow.GetCell(cdRowIndex).CellStyle = titleStyle;

     // 合并单元格
     SetCellRangeAddress(cdSheet, 0, 0, 0, cdHeaderVueList.Count() - 1);
     cdRowIndex++;

     // * 创建列头
     var cdHeadRow = cdSheet.CreateRow(cdRowIndex);  // 创建第2行
     var cdHeaderCell = 0;
     foreach (var headerVue in cdHeaderVueList)
     {
         cdHeadRow.CreateCell(cdHeaderCell).SetCellValue(headerVue);

         // 单元格设置列宽                           
         if (headerVue.Equals("XX否"))
         {
             cdSheet.SetColumnWidth(cdHeaderCell, 1800);
         }
         else if (headerVue.Equals("描述"))
         {
             cdSheet.SetColumnWidth(cdHeaderCell, 8000);
         }
         else
         {
             cdSheet.SetColumnWidth(cdHeaderCell, 4500);
         }            

         // 设置列头样式 -- 背景色
         if (cdHeaderCell == 2 || cdHeaderCell == 7 || cdHeaderCell == 8)
         {
             cdHeadRow.GetCell(cdHeaderCell).CellStyle = headBlueStyle;
         }
         else
         {
             cdHeadRow.GetCell(cdHeaderCell).CellStyle = headYellowStyle;
         }

         cdHeaderCell++;
     }
     cdRowIndex++;

     // 根据代码排序
     exportList = exportList.OrderBy(o => o.Item<string>("biz")).ToList();

     // * 创建数据列
     foreach (var export in exportList)
     {
         cdTitleRow = cdSheet.CreateRow(cdRowIndex);

         // 创建URL链接
         HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
         link.Address = $"#{export.Item<string>("biz")}!A1"; // 设置超链接点击跳转的地址

         // 代码
         var crCell0 = cdTitleRow.CreateCell(0);
         crCell0.SetCellValue(export.Item<string>("biz"));

         // 设置代码样式
         crCell0.Hyperlink = link;
         crCell0.CellStyle = iCellStyle;

         // 创建单元格并且绑定数据
         // XXXX
         var crCell1 = cdTitleRow.CreateCell(1);
         crCell1.SetCellValue(export.Item<string>("XXXX"));

         // XX否
         var crCell2 = cdTitleRow.CreateCell(2);
         crCell2.SetCellValue(export.Item<bool>("XXXX"));

         // XXXX
         var crCell3 = cdTitleRow.CreateCell(3);
         crCell3.SetCellValue(export.Item<string>("XXXX"));

         // XXXX
         var crCell4 = cdTitleRow.CreateCell(4);
         crCell4.SetCellValue(export.Item<string>("XXXX"));

         // XXXX
         var crCell5 = cdTitleRow.CreateCell(5);
         crCell5.SetCellValue(export.Item<string>("XXXX"));

         // XX否
         var crCell6 = cdTitleRow.CreateCell(6);
         crCell6.SetCellValue(export.Item<bool>("XXXX"));

         // XX否
         var crCell7 = cdTitleRow.CreateCell(7);
         crCell7.SetCellValue(export.Item<bool>("XXXX"));

         // 描述
         var crCell8 = cdTitleRow.CreateCell(8);
         crCell8.SetCellValue(export.Item<string>("XXXX"));

         // 设置其他样式
         crCell1.CellStyle = crCell2.CellStyle = crCell3.CellStyle = crCell4.CellStyle = crCell5.CellStyle = crCell6.CellStyle = crCell7.CellStyle = crCell8.CellStyle = headStyle;

         // 设置XX否、XX否、XX否下拉框
         string[] flVue = { "是", "否" };
         SetCellDropdownListHasError(cdSheet, cdRowIndex, cdRowIndex, 2, 2, flVue);
         SetCellDropdownListHasError(cdSheet, cdRowIndex, cdRowIndex, 6, 6, flVue);
         SetCellDropdownListHasError(cdSheet, cdRowIndex, cdRowIndex, 7, 7, flVue);

         cdRowIndex++;
     }
 }

 /// <summary>
 /// 创建每个实体Sheet
 /// </summary>
 /// <param name="exportList">数据源</param>
 /// <param name="workbook">工作薄</param>
 /// <param name="headStyle">列头样式</param>
 /// <param name="headYellowStyle">黄色背景列头样式</param>
 /// <param name="headBlueStyle">蓝色背景列头样式</param>
 /// <param name="iCellStyle">单元格字体样式</param>
 /// <param name="titleStyle">标题样式</param>
 private static void CreateAllBizSheet(List<Data> exportList, HSSFWorkbook workbook, ICellStyle headStyle, ICellStyle headYellowStyle, ICellStyle headBlueStyle, ICellStyle iCellStyle, ICellStyle titleStyle)
 {
     // 循环创建每个实体Sheet
     foreach (var export in exportList)
     {
         ISheet sheet = workbook.CreateSheet(export.Item<string>("biz"));   // 参数为工作表名称
         int rowIndex = 0;  // 第几行

         // 定义列头值
         string[] headerVueList = { "代码", "名称", "XX否", "类型", "精度", "物理字段", "关联数据来源", "分类", "计算否", "非空否", "启用否", "描述" };

         // * 创建 标题
         IRow titleRow = sheet.CreateRow(rowIndex);  // 创建第1行
         titleRow.HeightInPoints = 20;  //高度

         for (int h = 0; h <= headerVueList.Count(); ++h)
         {
             if (h == 0)
             {
                 var row0 = titleRow.CreateCell(0);
                 row0.SetCellValue("返回目录");

                 // 创建URL链接
                 HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
                 link.Address = $"#目录!A1"; // 设置超链接点击跳转的地址
                 row0.Hyperlink = link;
                 row0.CellStyle = iCellStyle;
             }
             else if (h == 6)
             {
                 var row6 = titleRow.CreateCell(6);
                 row6.SetCellValue(export.Item<string>("bizName")); // 在1行创建第7列并设置标题值
                 row6.CellStyle = titleStyle;
             }
             else
             {
                 titleRow.CreateCell(h); // 创建单元格
             }
         }

         rowIndex++;

         // * 创建列头
         IRow headRow = sheet.CreateRow(rowIndex);  // 创建第2行

         var headerCell = 0;
         foreach (var headerVue in headerVueList)
         {
             headRow.CreateCell(headerCell).SetCellValue(headerVue);

             // 单元格设置列宽                           
             if (headerVue.Equals("XX否") || headerVue.Equals("精度") || headerVue.Equals("计算否") || headerVue.Equals("非空否")
                 || headerVue.Equals("启用否") || headerVue.Equals("类型") || headerVue.Equals("分类"))
             {
                 sheet.SetColumnWidth(headerCell, 1800);
             }
             else if (headerVue.Equals("物理字段"))
             {
                 sheet.SetColumnWidth(headerCell, 2000);
             }
             else if (headerVue.Equals("代码") || headerVue.Equals("名称"))
             {
                 sheet.SetColumnWidth(headerCell, 5500);
             }
             else if (headerVue.Equals("关联数据来源"))
             {
                 sheet.SetColumnWidth(headerCell, 7800);
             }
             else if ( headerVue.Equals("描述"))
             {
                 sheet.SetColumnWidth(headerCell, 8000);
             }
             else
             {
                 sheet.SetColumnWidth(headerCell, 4500);
             }

             // 设置列头样式 -- 背景色
             if (headerCell == 2 || headerCell == 10 || headerCell == 11)
             {
                 headRow.GetCell(headerCell).CellStyle = headBlueStyle;
             }
             else
             {
                 headRow.GetCell(headerCell).CellStyle = headYellowStyle;
             }

             headerCell++;
         }

         rowIndex++;

         // * 创建数据                     
         foreach (var data in export.Item<List<SData>>("allData"))
         {
             var dataRow = sheet.CreateRow(rowIndex);
             var newCell = 0;
             foreach (var d in data)
             {
                 var newCreateCell = dataRow.CreateCell(newCell);
                 newCreateCell.SetCellValue(d.Value.ToString());
                 newCreateCell.CellStyle = headStyle;

                 // 设置分类下拉框
                 if (d.Key.ToString() == "type")
                 {
                     string[] flVue = { "字段", "参数", "子参数" };
                     SetCellDropdownListHasError(sheet, rowIndex, rowIndex, newCell, newCell, flVue);
                 }
                 // 设置XX否、计算否、非空否、启用否下拉框
                 else if (d.Key.ToString() == "XX" || d.Key.ToString() == "isCalculate" || d.Key.ToString() == "isNull"
                     || d.Key.ToString() == "isenable")
                 {
                     string[] flVue = { "是", "否" };
                     SetCellDropdownListHasError(sheet, rowIndex, rowIndex, newCell, newCell, flVue);
                 }
                 // 设置类型下拉框
                 else if (d.Key.ToString() == "fieldType")
                 {
                     string[] flVue = { "开关", "整数", "关联", "数值", "字符串", "日期", "时间", "文本", "富文本", "密码" };
                     SetCellDropdownListHasError(sheet, rowIndex, rowIndex, newCell, newCell, flVue);
                 }

                 // 设置关联数据来源路径
                 else if (d.Key.ToString() == "refBiz")
                 {
                    // 创建URL链接
                    HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
                    link.Address = $"#{d.Value.ToString()?.Sp_First()}!A1"; // 设置超链接点击跳转的地址
                   newCreateCell.Hyperlink = link;
                   newCreateCell.CellStyle = iCellStyle;                                         
                 }

                 newCell++;
             }

             rowIndex++;
         }
     }
 }

 /// <summary>
 /// 获取文件名称
 /// </summary>
 /// <returns></returns>
 private static string GetFileName()
 {
     // 项目名称
     var sysName = XXX; // XXX：自己项目的名称

     // 文件名称 目前只支持生成.xls格式的表格
     return $"{sysName}数据来源结构.xls";
 }

 #endregion

4.样式设置自定义方法

       #region 格式设置

       // <summary>
       /// 合并单元格
       /// </summary>
       /// <param name="sheet">要合并单元格所在的sheet</param>
       /// <param name="rowstart">开始行的索引</param>
       /// <param name="rowend">结束行的索引</param>
       /// <param name="colstart">开始列的索引</param>
       /// <param name="colend">结束列的索引</param>
       private static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
       {
           var cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
           sheet.AddMergedRegion(cellRangeAddress);
       }

       /// <summary>
       /// 通用格式设置
       /// </summary>
       /// <param name="workbook">工作薄</param>
       /// <param name="headStyle">列头样式</param>
       /// <param name="headYellowStyle">黄色背景列头样式</param>
       /// <param name="headBlueStyle">蓝色背景列头样式</param>
       /// <param name="iCellStyle">单元格字体样式</param>
       /// <param name="titleStyle">标题样式</param>
       private static void SetPublicStyle(HSSFWorkbook workbook, out ICellStyle headStyle, out ICellStyle headYellowStyle, out ICellStyle headBlueStyle, out ICellStyle iCellStyle, out ICellStyle titleStyle)
       {
           // 列头样式设置
           headStyle = workbook.CreateCellStyle();
           headStyle.Alignment = HorizontalAlignment.General;  // 设置水平居左显示
           headStyle.FillPattern = (FillPattern)1; // 设置填充模式
           headStyle.FillForegroundColor = IndexedColors.White.Index; // 设置边框色

           // 黄色背景列头样式
           headYellowStyle = workbook.CreateCellStyle();
           headYellowStyle.Alignment = HorizontalAlignment.General;  // 设置水平居左显示
           headYellowStyle.FillForegroundColor = IndexedColors.Yellow.Index; // 设置边框色
           headYellowStyle.FillPattern = (FillPattern)1;
           headYellowStyle.BorderBottom = headYellowStyle.BorderLeft = headYellowStyle.BorderRight = headYellowStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // 边框加黑

           // 蓝色背景列头样式
           headBlueStyle = workbook.CreateCellStyle();
           var palette = workbook.GetCustomPalette(); //调色板实例

           // 注：第一个参数：设置调色板新增颜色的编号，自已设置即可；取值范围8-64
           // 如果是8，9会导致边框被覆盖，其中，8是改变边框的颜色，9是改变整个sheet的背景颜色，这边场景不需要影响其他列格故填10
           palette.SetColorAtIndex((short)10, 155, 194, 230);
           var backgroundColor = palette.FindColor(155, 194, 230);
           headBlueStyle.FillForegroundColor = backgroundColor.Indexed;
           headBlueStyle.FillPattern = FillPattern.SolidForeground;
           headBlueStyle.FillBackgroundColor = backgroundColor.Indexed;
           headBlueStyle.Alignment = HorizontalAlignment.General;  // 设置水平居左显示
           headBlueStyle.BorderBottom = headBlueStyle.BorderLeft = headBlueStyle.BorderRight = headBlueStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // 边框加黑*/

           // 设置单元格字体样式
           iCellStyle = workbook.CreateCellStyle();
           IFont iFont = workbook.CreateFont();
           iFont.Color = HSSFColor.Blue.Index; // 设置颜色
           iFont.Underline = FontUnderlineType.Single; // 设置链接下划线
           iCellStyle.SetFont(iFont);
           iCellStyle.BorderBottom = iCellStyle.BorderLeft = iCellStyle.BorderRight = iCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // 边框加黑

           // 设置标题样式
           titleStyle = workbook.CreateCellStyle();
           titleStyle.Alignment = HorizontalAlignment.Center;
           IFont tFont = GetFontStyle(workbook, "", null, 16);
           titleStyle.SetFont(tFont);
           titleStyle.BorderBottom = titleStyle.BorderLeft = titleStyle.BorderRight = titleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // 边框加黑

           // 字体样式
           IFont font = workbook.CreateFont();
           font.FontHeightInPoints = 10;
           headStyle.BorderBottom = headStyle.BorderLeft = headStyle.BorderRight = headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
           headStyle.SetFont(font);
       }

       /// <summary>
       /// 绑定数据源到下拉框
       /// </summary>
       /// <param name="sheet">页签</param>
       /// <param name="firstRow">开始行</param>
       /// <param name="lastRow">终结行</param>
       /// <param name="firstCol">开始列</param>
       /// <param name="lastCol">终结列</param>
       /// <param name="vals">值的集合</param>
       private static void SetCellDropdownListHasError(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string[] vals)
       {
           if (vals.Length < 1)
           {
               return;
           }

           // 设置生成下拉框的行和列 最大行数：65535
           CellRangeAddressList cellRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

           HSSFDataValidationHelper helper = new HSSFDataValidationHelper((HSSFSheet)sheet);

           // 启用下拉验证,设置下拉的值
           var dropDownConstraint = helper.CreateExplicitListConstraint(vals);
           IDataValidation dropDownValidation = helper.CreateValidation(dropDownConstraint, cellRegions);
           dropDownValidation.ShowPromptBox = true;
           dropDownValidation.ShowErrorBox = true;
           dropDownValidation.CreateErrorBox("输入不合法", "必须从下拉列表中选择");
           sheet.AddValidationData(dropDownValidation);
       }

       /// <summary>
       /// 获取字体样式
       /// </summary>
       /// <param name="hssfworkbook">Excel操作类</param>
       /// <param name="fontname">字体名</param>
       /// <param name="fontcolor">字体颜色</param>
       /// <param name="fontsize">字体大小</param>
       /// <returns></returns>
       private static IFont GetFontStyle(IWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize)
       {
           IFont font = hssfworkbook.CreateFont();
           if (!string.IsNullOrEmpty(fontfamily))
           {
               font.FontName = fontfamily;
           }
           if (fontcolor != null)
           {
               font.Color = fontcolor.Indexed; //.GetIndex();
           }
           font.FontHeightInPoints = (short)fontsize;
           return font;
       }

       #endregion

头上有犄角不是我の错

关注

18
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
C# 使用NPOI根据数据库实体数据生成自定义excel并导出

其中通过NPOI实现了自定义表格功能，例如根据实体创建工作簿、边框和字体样式、合并单元格、填充单元格颜色、设置下拉属性等等，希望可以帮到有同需求的伙伴。因项目中数据库实体数据需要导出成表格的需求，因此使用了NPOI完成了此功能。1.引用系统和对应NPOI包。3.数据以及组装的自定义方法。4.样式设置自定义方法。
复制链接

扫一扫