用代码导出复杂Excel的事件

PS：事件是一个功能导出excel，本来这个是比较简单的，可提的要求里面居然要合并单元格，而且组出来的数据本身就是逻辑，搞的我一下子就无语了。试着研究了一下，不过没有想象的难。时间问题。。

解决方案：使用插件"NPOI"，合并的用法是一个方法指定起始行，结束行，起始列，结束列。居中就比较蛋疼，把所有单元格循环设置成居中（关于这点如果有做过的人有更好的方法请告诉我）。贴代码啦~~

public FileResult OneExport(OnePageInput input)
        {
            input.UserType = AbpSession.UserType;//用户类型
            input.carrierID = (long)AbpSession.UserId;//承运商
            decimal Total = 0;//总价
            var data = _dispatchAppService.WebPOClearOne(input, ref Total);

            ViewData["timeFrom"] = input.timeFrom.Value.ToString("yyyy-MM-dd");
            ViewData["timeTo"] = input.timeTo.Value.ToString("yyyy-MM-dd");
            ViewData["shipperName"] = input.shipperName;
            ViewData["proName"] = input.proName;
            ViewData["endAreaName"] = input.endAreaName;

            
            // 创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet($"{AbpSession.Name}待开票");
            ////设置默认行高
            sheet1.DefaultRowHeight = 18 * 20;
            //设置每列的宽度
            for (int y = 0; y < 15; y++)
            {
                sheet1.SetColumnWidth(y, 20 * 256);
            }
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("资源单号");
            row1.CreateCell(1).SetCellValue("货主名称");
            row1.CreateCell(2).SetCellValue("驾驶员");
            row1.CreateCell(3).SetCellValue("车牌");
            row1.CreateCell(4).SetCellValue("卸货地");
            row1.CreateCell(5).SetCellValue("装车时间");
            row1.CreateCell(6).SetCellValue("合同子项号");
            row1.CreateCell(7).SetCellValue("产品名称");
            row1.CreateCell(8).SetCellValue("重量");
            row1.CreateCell(9).SetCellValue("运量汇总");
            row1.CreateCell(10).SetCellValue("单价");
            row1.CreateCell(11).SetCellValue("小计");
            row1.CreateCell(12).SetCellValue("状态");
            int i = 1;
            //将数据逐步写入sheet1各个行
            foreach (var item in data)
            {
                int rowSpanCount = item.loadList.Count < 2 ? 1 : item.loadList.Count;

                //第一个TR
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i);
                rowtemp.CreateCell(0).SetCellValue(item.ResCode);
                rowtemp.CreateCell(1).SetCellValue(item.shipperName);
                rowtemp.CreateCell(2).SetCellValue(item.driverName);
                rowtemp.CreateCell(3).SetCellValue(item.carNum);
                rowtemp.CreateCell(4).SetCellValue(item.endAreaString);
                rowtemp.CreateCell(5).SetCellValue(item.loadingTimeStr);
                if (item.loadList.Count==0)
                {
                    rowtemp.CreateCell(6).SetCellValue("");
                    rowtemp.CreateCell(7).SetCellValue("");
                    rowtemp.CreateCell(8).SetCellValue("");
                }
                else
                {
                    rowtemp.CreateCell(6).SetCellValue(item.loadList[0].ContractItemNumber);
                    rowtemp.CreateCell(7).SetCellValue(item.loadList[0].GoodsName);
                    rowtemp.CreateCell(8).SetCellValue(item.loadList[0].TransportVolume.ToString());
                }
                rowtemp.CreateCell(9).SetCellValue(item.loadingNum.ToString("0.000"));
                rowtemp.CreateCell(10).SetCellValue(item.price+" "+item.unitName+ "/元");
                if (item.ErrorState == 0)
                {
                    item.ErrorStateStr = "正常";
                }
                else if (item.ErrorState == 1)
                {
                    item.ErrorStateStr = "异常";
                }
                else if (item.ErrorState == 2)
                {
                    item.ErrorStateStr = "已修改";
                }
                else if (item.ErrorState == 3)
                {
                    item.ErrorStateStr = "补录数据";
                }
                
                rowtemp.CreateCell(11).SetCellValue(item.sumPrice.Value.ToString("0.00"));
                rowtemp.CreateCell(12).SetCellValue(item.ErrorStateStr);
                if (item.loadList.Count > 1)
                {
                    for (int z = 1; z < item.loadList.Count; z++)
                    {
                        i++;
                        NPOI.SS.UserModel.IRow rowtemp2 = sheet1.CreateRow(i);
                        rowtemp2.CreateCell(6).SetCellValue(item.loadList[z].ContractItemNumber);
                        rowtemp2.CreateCell(7).SetCellValue(item.loadList[z].GoodsName);
                        rowtemp2.CreateCell(8).SetCellValue(item.loadList[z].TransportVolume.ToString());
                    }
                }
                i = i + 1;
                //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                if (item.loadingNum>1)
                {
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 0, 0));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 1, 1));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 2, 2));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 3, 3));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 4, 4));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 5, 5));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 9, 9));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 10, 10));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 11, 11));
                    sheet1.AddMergedRegion(new CellRangeAddress(i-1 - rowSpanCount+1, i-1, 12, 12));
                }
            }
            //单元格对齐样式
            ICellStyle style0 = book.CreateCellStyle();
            style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中 
            style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            for (int a = 0; a < i; a++)
            {
                for (int b = 0; b < 13; b++)
                {
                    ICell cell = sheet1.GetRow(a).GetCell(b);
                    if (cell!=null)
                    {
                        cell.CellStyle = style0;
                    }
                    
                }
            }
            //sheet1.SetDefaultColumnStyle(1,style0);
            //写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", AbpSession.Name + "-待开票-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        }