NPOI动态生成Excel(主表明细表)展示模版方法(个人笔记4-29)
因项目要求导出业务数据要求展示主表和明细表(比如订单表+订单明细表)，我只做过把主表和明细一起导出来，大多数应该都是这样子吧，写一个sql，返回一个数据源…
然后我就在网上搜了下，发现自己有点井底之蛙了哈哈哈，还是有很多人的项目有这个需求的！！！
我这边的最后效果大概就是这样子，由于本人是小白所以一直都是先满足功能，再满足体验…然后我直接把我的渣渣代码贴出来，大神们别笑
#region Bus_DeliverySheet_ExportExcel(扩展动态生成excel列表式模版)
/// <summary>
/// 导出送货单(Excel)
/// </summary>
/// <param name="keyValue">主键</param>
/// <param name="lyx">扩展Excel模版导出(主从表数据)</param>
/// <param name="lyx">2019年4月28日 09:55:49</param>
public void Bus_DeliverySheet_ExportExcel(string keyValue)
{
//主
var DeliverySheetData = bus_DeliverySheetIBLL.GetBus_DeliverySheetEntitySql(keyValue);
//明细
var DeliverySheetDetail = bus_DeliverySheetIBLL.GetBus_DeliverySheet_DetailList(DeliverySheetData.Bds_Unique);
List<Bus_DeliverySheet_DetailEntity> list = DeliverySheetDetail.ToList();
var xlworkbook = new XLWorkbook();
//Sheet名称
var worksheets = xlworkbook.Worksheets.Add("送货单");
//表格标题
worksheets.Cell("A2").Value = "送 货 单";
worksheets.Cell("A2").Style.Font.FontSize = 15;
worksheets.Range(2, 1, 2, 12).Merge();
worksheets.Range(2, 1, 2, 12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
////送货主信息
worksheets.Cell("A5").Value = "送货单:";
worksheets.Cell("A5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("B5").Value = DeliverySheetData.Bds_SheetNo;//送货单，合并5个单元格
worksheets.Cell("B5").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Cell("B5").DataType = 0;
worksheets.Range(5, 2, 5, 6).Merge();
worksheets.Range(5, 2, 5, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; //字段值左对齐
worksheets.Cell("G5").Value = "送货日期:";
worksheets.Cell("G5").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("H5").Value = DeliverySheetData.Bds_DeliveryDate;//送货日期，合并5个单元格
worksheets.Cell("H5").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Range(5, 8, 5, 12).Merge(); //加长度空格
worksheets.Range(5, 8, 5, 12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheets.Cell("A6").Value = "货主名称:";
worksheets.Cell("A6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("B6").Value = DeliverySheetData.Bds_OwnerUnique;//货主名称，合并5个单元格
worksheets.Cell("B6").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Range(6, 2, 6, 6).Merge();
worksheets.Range(6, 2, 6, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheets.Cell("G6").Value = "仓库:";
worksheets.Cell("G6").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("H6").Value = DeliverySheetData.Bds_WarehouseUnique;//仓库，合并5个单元格
worksheets.Cell("H6").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Range(6, 8, 6, 12).Merge();
worksheets.Range(6, 8, 6, 12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
//A7 - H7合并
worksheets.Cell("A7").Value = "供应商名:";
worksheets.Cell("A7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("B7").Value = DeliverySheetData.Bds_SupplierNo;//供应商名，合并5个单元格
worksheets.Cell("B7").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Range(7, 2, 7, 6).Merge();
worksheets.Range(7, 2, 7, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheets.Cell("G7").Value = "单据类型:";
worksheets.Cell("G7").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("H7").Value = DeliverySheetData.Bds_Type;//单据类型，合并3个单元格
worksheets.Cell("H7").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Range(7, 8, 7, 12).Merge();
worksheets.Range(7, 8, 7, 12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheets.Cell("A8").Value = "备注:";
worksheets.Cell("A8").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //主信息字段右对齐
worksheets.Cell("B8").Value = DeliverySheetData.Bds_Remark;//备注，合并5个单元格
worksheets.Cell("B8").Style.Font.Underline = XLFontUnderlineValues.Single; //下划线，单一线
worksheets.Range(8, 2, 8, 6).Merge();
worksheets.Range(8, 2, 8, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
//=======主表信息和明细信息中间隔一行单元格========
////表格表头处理
worksheets.Cell("A10").Value = "编号";
worksheets.Cell("B10").Value = "商品编号";
//worksheets.Range(9, 2, 9, 4).Merge();
//worksheets.Range(9, 2, 9, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("C10").Value = "商品名称";
// worksheets.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //全局 列被居中
worksheets.Cell("D10").Value = "商品型号";
worksheets.Cell("E10").Value = "小单位";
worksheets.Cell("F10").Value = "条形码";
worksheets.Cell("G10").Value = "商品规格";
worksheets.Cell("H10").Value = "数量";
//明细列表字段样式
//worksheets.Style.Fill.BackgroundColor = XLColor.White; //(白色)背景 不指定列头则默认全局生效 , 可当做隐藏网格效果
// worksheets.Cell("A9").Style.Fill.BackgroundColor = XLColor.NapierGreen; //(绿)列头背景
worksheets.Cell("A10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //主信息字段居中对齐
worksheets.Cell("B10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("C10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("D10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("E10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("F10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("G10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell("H10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//{送货单!A4:L4,送货单!B5:F5,送货单!H5:L5,送货单!B6:F6,送货单!H6:L6,送货单!B7:F7,送货单!H7:L7,送货单!B9:D9}
////循环添加表格内容
int count = 0;
for (int i = 0; i < list.Count; i++)
{
//i+10从上往下数到10个格子开始循环(索引改变，循环须改变)
count += (int)list[i].Bdsd_Qty; //强制转换int类型，累计存储全部总数量
worksheets.Cell(i + 11, 1).Value = i + 1;
worksheets.Cell(i + 11, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 2).Value = "'" + list[i].Bdsd_GoodsNo; //商品编号
worksheets.Cell(i + 11, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//worksheets.Range(i + 10, 2, i + 10, 4).Merge();
//worksheets.Range(i + 10, 2, i + 10, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 3).Value = "'" + list[i].Bdsd_GoodsName; //商品名称 // "'"+ 字段， 为阻止excel导出执行了科学计数法(1.11122E+19)
worksheets.Cell(i + 11, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 4).Value = "'" + list[i].Bdsd_GoodsModel; //型号
worksheets.Cell(i + 11, 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 5).Value = list[i].Bdsd_GoodsSmallUnit; //小单位
worksheets.Cell(i + 11, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 6).Value = "'" + list[i].Bdsd_GoodsBarCode; //条形码
worksheets.Cell(i + 11, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 7).Value = "'" + list[i].Bdsd_GoodsSpecification; //规格
worksheets.Cell(i + 11, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(i + 11, 8).Value = list[i].Bdsd_Qty;//数量
worksheets.Cell(i + 11, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//list.Count+10所有数据加上从上往下数10行开始计算,定位到后两列
worksheets.Cell(list.Count + 11, 7).Value = "合计:";
worksheets.Cell(list.Count + 11, 7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(list.Count + 11, 7).Style.Fill.BackgroundColor = XLColor.BallBlue; //背景蓝
worksheets.Cell(list.Count + 11, 8).Value = count; //遍历累计全部数量
worksheets.Cell(list.Count + 11, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheets.Cell(list.Count + 11, 8).Style.Fill.BackgroundColor = XLColor.BallBlue; //背景蓝
//这是明细添加列表边框，由于没有完全实现，先注释满足功能
////行1 -8 前9是excel从一行数线 1是索引1开始 - 8是excel每个小格子数量 THIN 正常实线 ，粗线 MEDIUM ， 超级粗线 THICK , 双实线 DOUBLE , 虚线 Hair
//操作行代码的ws.Range()四个参数含义：①从excel顶部开始向行数 ②是索引1 ③是所有数据的总数量
//worksheets.Range(i + 9, 1, i + 9, 8).Style.Border.TopBorder = XLBorderStyleValues.Thin;
//worksheets.Range(i + 9, 1, i + 9, 8).Style.Border.TopBorderColor = XLColor.Black;
//列9 - 16 9从上数到列表上部数量线，16是从上数到底部数量线 从上数格子到第9格开始遍历，加上list.count总数
//worksheets.Range(9, i + 1, list.Count + 9, i + 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
//worksheets.Range(9, i + 1, list.Count + 9, i + 1).Style.Border.LeftBorderColor = XLColor.Black;
}
var rngTable = worksheets.Range("A1:L1"); //长度
var rngHeaders = rngTable.Range("A1:L1");
worksheets.Row(1).Height = 20;
rngHeaders.FirstCell().Style
.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
rngHeaders.FirstRow().Merge();
worksheets.Columns().AdjustToContents();
var col1 = worksheets.Column("D");
col1.Width = 15;
MemoryStream ms = new MemoryStream();
xlworkbook.SaveAs(ms);
ms.Flush();
ms.Position = 0;
//隐藏网格属性(无效)，如不需要网格，可全局设置背景为白色，效果一样
//HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet s1 = new HSSFSheet(workbook);
//ISheet sheet1 = workbook.CreateSheet("送货单"); //Sheet1
//sheet1.DisplayGridlines = false;
byte[] oByte = null;
oByte = ms.ToArray();
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.sheet.binary.macroEnabled.12";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + DeliverySheetData.Bds_SheetNo + "送货单" + ".xlsx");
System.Web.HttpContext.Current.Response.BinaryWrite(oByte);
//清除缓存
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
//关闭缓冲区
ms.Close();
}
#endregion
我这么小白的注释应该可以看明白
最后就是几个小问题：
网格问题：我设置属性无效，然后我用style背景色调成白色也可达成一样的效果，
科学计数法问题：比如身份证长度会强制转换成科学计数法，我没找到好办法，就试出了用“‘’”+字段的傻瓜解决方案，代码里也注释，
边框问题：代码里我注释了，可以实现，但是有点问题。我没找到