NPOI导出数据处理参考

        #region

        public static NPOI.HSSF.UserModel.HSSFWorkbook DoMeterialRatioExport(List<Model.Common.Report.VMeterialRactioJSON> models, IEnumerable<FieldList> fieldMap, string notile = "")
        {
            //创建工作簿
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //创建表
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet");
            //自适应列宽
            sheet.AutoSizeColumn(1, true);

            try
            {
                //调色板
                //HSSFPalette palette = book.GetCustomPalette();
                //palette.SetColorAtIndex(1,114, 162,220);
                //列名样式
                NPOI.SS.UserModel.ICellStyle styleColName = book.CreateCellStyle();
                NPOI.SS.UserModel.IFont fontColName = book.CreateFont();
                styleColName.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styleColName.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                styleColName.FillPattern = FillPattern.SolidForeground;
                styleColName.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                fontColName.FontHeightInPoints = 12;
                styleColName.BorderBottom =  BorderStyle.Thin;
                styleColName.BorderLeft = BorderStyle.Thin;
                styleColName.BorderRight = BorderStyle.Thin;
                styleColName.BorderTop = BorderStyle.Thin;
                //fontColName.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
                styleColName.SetFont(fontColName);

                //数据的样式、字体大小
                NPOI.SS.UserModel.ICellStyle styleBody = book.CreateCellStyle();
                NPOI.SS.UserModel.IFont fontBody = book.CreateFont();

                styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                fontBody.FontHeightInPoints = 12;
                styleBody.SetFont(fontBody);

                //NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow(0);

                #region 表头

                Type type = typeof(Model.Common.Report.Data);
                PropertyInfo[] properties = type.GetProperties();

                #endregion

                int i = 0;
                foreach (var m in models)
                {
                    #region 表头

                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(i);
                    NPOI.SS.UserModel.ICell colPoCell = row.CreateCell(0);
                    colPoCell.SetCellValue("PO：" + m.PO);
                    colPoCell.CellStyle = styleColName;
                    //合并单元格
                    SetCellRangeAddress(sheet, i, i, 0, 23);
                    i++;

                    int iHead = 0;
                    NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow(i);
                    foreach (var c in fieldMap)
                    {
                        NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(iHead);
                        colNameCell.SetCellValue(c.FieldName);
                        colNameCell.CellStyle = styleColName;
                        iHead++;
                    }
                    i++;

                    #endregion

                    //数据
                    var data = m.Data;
                    //总计数据
                    var sumData = m.SumData;

                    foreach (var c in data)
                    {
                        foreach (var c1 in c.SumData)
                        {
                            int jContent2 = 0;
                            NPOI.SS.UserModel.IRow r1 = sheet.CreateRow(i);
                            foreach (var cm in fieldMap)
                            {
                                NPOI.SS.UserModel.ICell colNameCell = r1.CreateCell(jContent2);
                                if (igonStr.Contains(cm.FieldValue))
                                {
                                    if(cm.FieldValue == "ShipPN")
                                        colNameCell.SetCellValue(c.ShipPN);
                                    else 
                                        colNameCell.SetCellValue("");
                                    jContent2++;
                                    continue;
                                }
                                PropertyInfo p1 = properties.SingleOrDefault(w => w.Name == cm.FieldValue);
                                if (p1 == null)
                                    throw new Exception("数据模型没有相应的属性：" + cm.FieldValue);
                                else if (p1.PropertyType.FullName.Contains("System.DateTime"))
                                    colNameCell.SetCellValue(string.Format("{0:yyyy-MM-dd}", p1.GetValue(c1, null)));
                                else
                                    colNameCell.SetCellValue(p1.GetValue(c1, null)?.ToString());
                                jContent2++;

                            }
                            i++;
                        }

                        //合并单元格
                        SetCellRangeAddress(sheet, i-3, i-1, 0, 0);
                    }

                    foreach (var c1 in sumData)
                    {
                        int sumNum = 0;
                        NPOI.SS.UserModel.IRow r2 = sheet.CreateRow(i);
                        foreach (var cm2 in fieldMap)
                        {
                            NPOI.SS.UserModel.ICell colNameCell2 = r2.CreateCell(sumNum);
                            if (igonStr.Contains(cm2.FieldValue))
                            {
                                if (cm2.FieldValue == "ShipPN")
                                    colNameCell2.SetCellValue(properties.SingleOrDefault(w => w.Name == "Item").GetValue(c1,null)?.ToString());
                                else
                                    colNameCell2.SetCellValue("");
                                sumNum++;
                                continue;
                            }
                            PropertyInfo p1 = properties.SingleOrDefault(w => w.Name == cm2.FieldValue);
                            if (p1 == null)
                                throw new Exception("数据模型没有相应的属性：" + cm2.FieldValue);
                            else if (p1.PropertyType.FullName.Contains("System.DateTime"))
                                colNameCell2.SetCellValue(string.Format("{0:yyyy-MM-dd}", p1.GetValue(c1, null)));
                            else
                                colNameCell2.SetCellValue(p1.GetValue(c1, null)?.ToString());
                            sumNum++;
                        }
                        SetCellRangeAddress(sheet, i, i, 0, 1);
                        i++;
                    }
                }

                //自适应列宽
                for (int x = 0; x < models.Count(); x++)
                {
                    sheet.AutoSizeColumn(x, true);
                }

                return book;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion

        #region 帮助类

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

        #endregion

 

使用：

using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                var t = DoMeterialRatioExport(data, Init_MeterialRatio_Export(), "");
                t.Write(ms);
                ms.Close();
                ms.Dispose();
                HttpContext.Response.Headers.Add("Content-Disposition", string.Format("attachment; filename=报表A.xls"));
                HttpContext.Response.BodyWriter.WriteAsync(ms.ToArray());
            }

参阅：https://www.xin3721.com/articlecsharp/c6596.html