C#项目自定义汇总导出Excel功能

项目中经常需要导出excel，有时还有汇总需求。但导出方式都一样，而汇总方式不同。可以参考以下方式，使用标准的导出方式，和自定义的汇总方式。

1.首先NuGet引用NPOI version 2.3.0

2.导出表格源码

/// <summary>
        /// 【汇总的外置方法】func第一个参数是上次的行数据,第二个参数是当前行数据,第三个参数是Sheet中的当前行,第四个参数是否是最后一行数据,如果增加了新行,则必须返回true
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public void ExportDataToExcel(DataTable dt,  string FileName, Func<DataRow, DataRow, IRow,Boolean, Boolean> func)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ICellStyle cellstyle = hssfworkbook.CreateCellStyle();//设置垂直居中格式
            cellstyle.VerticalAlignment = VerticalAlignment.Top;    //垂直居中
            cellstyle.Alignment = HorizontalAlignment.Center;       //居中

            #region 初始化Sheets
            List<ISheet> Sheets = new List<ISheet>();//Sheets集合
            Action addSheet=()=>{
                ISheet sheet = hssfworkbook.CreateSheet();
                IRow row = sheet.CreateRow(0);
                //表头
                for (int hi = 0; hi < dt.Columns.Count; hi++)
                {
                    ICell cell = row.CreateCell(hi);
                    cell.SetCellValue(dt.Columns[hi].ColumnName);
                    cell.CellStyle = cellstyle;
                }
                Sheets.Add(sheet);
            };
            
            #endregion
            int sheetIndex = 0;
            int rowcount = -1;
            DataRow lastIRow = null;
            try
            {
                addSheet();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    rowcount++;
                    if (rowcount > 65535 * (sheetIndex+1))
                    {
                        addSheet();
                        ++sheetIndex;
                    }
                    int currentI = rowcount - (sheetIndex * 65535);
                    IRow row1 = Sheets.ToArray()[sheetIndex].CreateRow(currentI + 1);
                    if (lastIRow==null)
                    {
                        lastIRow = dt.Rows[i];
                    }
                    DataRow currentDataRow = dt.Rows[i];
                    Boolean isend =(i==dt.Rows.Count-1);
                    if (!isend)
                    {
                        var isaddCount = func(lastIRow, currentDataRow, row1, isend);
                        if (isaddCount)
                        {
                            rowcount++;
                            currentI = rowcount - (sheetIndex * 65535);
                            row1 = Sheets.ToArray()[sheetIndex].CreateRow(currentI + 1);
                        }
                    }
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                       
                        string colName = dt.Columns[j].ColumnName;
                        if (colName.IndexOf("监管码") > 0 || colName.IndexOf("产品码") > 0 || colName.IndexOf("中奖用户") > 0)
                        {

                            cell.SetCellValue("'" + dt.Rows[i][j].ToString());
                            cell.CellStyle = cellstyle;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            cell.CellStyle = cellstyle;
                        }

                    }
                    if (isend)
                    {
                       func(lastIRow, currentDataRow, row1, isend);                        
                    }
                    
                    lastIRow = currentDataRow;
                }
                //转为字节数组
                MemoryStream stream = new MemoryStream();
                hssfworkbook.Write(stream);

                stream.Position = 0;
                StreamReader rd = new StreamReader(stream, System.Text.Encoding.BigEndianUnicode);
                StringBuilder sb = new StringBuilder();
                sb.Append(rd.ReadToEnd());
                stream.Close();
                hssfworkbook.Close();

                StringWriter sw = new StringWriter();
                sw.Write(sb.ToString());
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName + ".xls", System.Text.Encoding.UTF8));
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.BigEndianUnicode;
                System.Web.HttpContext.Current.Response.Write(sw);
                sw.Close();
                System.Web.HttpContext.Current.Response.End();
               

            }
            catch (Exception ex)
            {
                hssfworkbook.Close();
                Acexe.Common.Utils.Log("error", String.Format("ExportDataToExcel {0}{1}{2}", ex.Message, System.Environment.NewLine, ex.StackTrace));
            }
        }

3.自定义参考源码

 

Func<DataRow, DataRow, IRow,Boolean, Boolean> func = (a, b, c,d) =>
            {
                ICellStyle cellstyle = c.Sheet.Workbook.CreateCellStyle();//设置垂直居中格式
                cellstyle.VerticalAlignment = VerticalAlignment.Top;    //垂直居中
                cellstyle.Alignment = HorizontalAlignment.Right;       //居中
               
                if(d)
                {
                    if (Decimal.TryParse(b["红包金额"].ToString(), out recordMoney))
                    {
                        moneyCount += recordMoney;
                    }
                    ICell cell = c.Sheet.CreateRow(c.RowNum+1).CreateCell(0);
                    cell.SetCellValue("UserId:" + a["UserId"].ToString() + " 汇总: " + moneyCount.ToString("0.00"));
                    cell.CellStyle = cellstyle;
                    c.Sheet.AddMergedRegion(new CellRangeAddress(c.RowNum+1, c.RowNum+1, 0, a.Table.Columns.Count - 1));
                    return true;
                }
                else
                {
                    if (!(a["UserId"].ToString().Equals(b["UserId"].ToString())))
                    {
                        ICell cell = c.CreateCell(0);
                        cell.SetCellValue("UserId:" + a["UserId"].ToString() + " 汇总: " + moneyCount.ToString("0.00"));
                        cell.CellStyle = cellstyle;
                        c.Sheet.AddMergedRegion(new CellRangeAddress(c.RowNum, c.RowNum, 0, a.Table.Columns.Count - 1));
                        if (Decimal.TryParse(b["红包金额"].ToString(), out recordMoney))
                        {
                            moneyCount = recordMoney;
                        }
                        return true;
                    }
                    else
                    {
                        if (Decimal.TryParse(b["红包金额"].ToString(), out recordMoney))
                        {
                            moneyCount += recordMoney;
                        }
                        return false;
                    }
                }
            };