使用NPOI 寫Excel Tool

因為專案需求時常需要生成Excel 的動作， 但每次都要撰寫一次生成Excel動作實在麻煩， 因此做了以下的開發工具

using System;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelHelper.AppCode
{
 public class ExcelHelper
 {
        public static byte[] GetExcelProduct(List<List<object>> excel, string sheetName = "sheet1")
        {
            try
            {
                IWorkbook wb = new XSSFWorkbook();
                // Title Style
                IFont fontTitle = wb.CreateFont();
                fontTitle.FontHeightInPoints = 13;
                fontTitle.Boldweight = (short)FontBoldWeight.Bold;

                ICellStyle styleTiltle = wb.CreateCellStyle();
                styleTiltle.Alignment = HorizontalAlignment.Center;
                styleTiltle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
                var colorRgb = new byte[] { (byte)254, (byte)249, (byte)231 };
                ((XSSFCellStyle)styleTiltle).SetFillForegroundColor(new XSSFColor(colorRgb));
                styleTiltle.FillPattern = FillPattern.SolidForeground;
                styleTiltle.SetFont(fontTitle);
                styleTiltle.VerticalAlignment = VerticalAlignment.Center;

                // Component Title Style
                ICellStyle syleComponent = wb.CreateCellStyle();
                colorRgb = new byte[] { (byte)250, (byte)240, (byte)230 };
                ((XSSFCellStyle)syleComponent).SetFillForegroundColor(new XSSFColor(colorRgb));
                syleComponent.FillPattern = FillPattern.SolidForeground;
                syleComponent.Alignment = HorizontalAlignment.Center;
                syleComponent.SetFont(fontTitle);
                syleComponent.VerticalAlignment = VerticalAlignment.Center;

                // Content Style
                IFont fontContent = wb.CreateFont();
                fontContent.FontHeightInPoints = 13;

                ICellStyle styleContent = wb.CreateCellStyle();
                styleContent.Alignment = HorizontalAlignment.Center;
                styleContent.SetFont(fontContent);
                styleContent.VerticalAlignment = VerticalAlignment.Center;
                ISheet ws = wb.CreateSheet(sheetName);//設定頁籤
                for (int row = 0; row < excel.Count; row++)
                {
                    ws.CreateRow(row);

                    for (int column = 0; column < excel[row].Count; column++)
                    {
                        ICell cell = ws.GetRow(row).CreateCell(column);
                        cell.SetCellValue(excel[row][column]?.ToString());
                        if (row == 0)
                            cell.CellStyle = styleTiltle;
                        else
                            cell.CellStyle = styleContent;

                    }
                }
                using (System.IO.MemoryStream memory = new System.IO.MemoryStream())
                {

                    wb.Write(memory);
                    return memory.ToArray();
                }

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

在呼叫上的引用也很容易

//想要生成的Excel
List<List<object>> exceldata = new List<List<object>>();
//先加入第一欄的內容
exceldata.Add(new List<object>
{
    "欄位標題","欄位標題1","欄位標題2","欄位標題3","欄位標題4"
});
接著將資料集匯入
foreach(var data in dataList)
{
    exceldata.Add(new List<object>{data.1,......});
}
//如此呼叫就可以取得byte[]資料集
ExcelHelper.GetExcelProduct(excel: exceldata, sheetName: "dataList");