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

偏执的太偏执、 2022-05-09 15:48 154阅读 0赞

项目中经常需要导出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;
                        }
                    }
                };