demo20230512/Supports/ExcelExporter.cs

/*
 * If you need to use this object to export excel. follow the step:
 *   1) Use CreateWorkbook to create new workbook
 *   2) Use CreateSheet to create worksheet in workbook
 *   3) Use CreateInsertableArea to prepare insertable area
 *   4) Insert data with sub class
 *   5) Use ExportExcel to export excel
 * 
 *   example:
 *   var productLca = _db.ProductLCAs.First();
 *   var excelExporter = new ProductLCAExcelExporter();
 *   var workbook = excelExporter.CreateWorkbook();
 *   var sheet = excelExporter.CreateSheet(workbook, "test");
 *   excelExporter.CreateInsertableArea(sheet, 0, 34, 0, 8);
 *   excelExporter.InsertLcaData(workbook, sheet, productLca);
 *   excelExporter.ExoprtExcel(workbook, "final2");
 */
// hack !!... 使用 NPOI 匯出 excel
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Http;
//using Weee.Models;

namespace Weee.Supports
{
    public class ExcelExporter
    {
        public IWorkbook CreateWorkbook()
        {
            return new XSSFWorkbook();
        }

        public ISheet CreateSheet(IWorkbook workbook, string sheetName)
        {
            return workbook.CreateSheet(sheetName);
        }

        public void CreateInsertableArea(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
        {
            for (var i = firstRow; i <= lastRow; i++)
            {
                sheet.CreateRow(i);
                for (var j = firstCol; j <= lastCol; j++)
                {
                    sheet.GetRow(i).CreateCell(j);
                }
            }
        }

        protected void SetCellStyle(ISheet sheet, ICellStyle style, int firstRow, int lastRow, int firstCol, int lastCol)
        {
            for (int i = firstRow; i <= lastRow; i++) {
                for (int j = firstCol; j <= lastCol; j++) {
                    sheet.GetRow(i).GetCell(j).CellStyle = style;
                }
            }

            for (int j = firstCol; j <= lastCol; j++) {
                sheet.AutoSizeColumn(j);
            }
        }

        protected void InsertHorizontalHeader(ISheet sheet, int firstRow, int lastRow, int firstCol, string[] header)
        {
            sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, 0, 0));

            int col = firstCol;
            foreach (var title in header) {
                sheet.GetRow(firstRow).GetCell(col).SetCellValue(title);
                col++;
            }
        }

        protected void InsertVerticalHeader(ISheet sheet, int firstRow, int firstCol, int lastCol, string[] header)
        {
            int row = firstRow;
            foreach (var title in header) {
                sheet.AddMergedRegion(new CellRangeAddress(row, row, firstCol, lastCol));
                sheet.GetRow(row).GetCell(firstCol).SetCellValue(title);
                row++;
            }
        }

        protected ICellStyle CreateCommonStyleWithBorder(IWorkbook workbook)
        {
            var style = (XSSFCellStyle)workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.WrapText = true;

            return style;
        }

        protected ICellStyle CreateCommonStyleWithBorder(IWorkbook workbook, XSSFColor cellColor)
        {
            var style = (XSSFCellStyle)this.CreateCommonStyleWithBorder(workbook);
            style.SetFillBackgroundColor(cellColor);
            style.FillPattern = FillPattern.LeastDots;

            return style;
        }

        protected ICellStyle CreateCommonStyleWithoutBorder(IWorkbook workbook)
        {
            var style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.WrapText = true;

            return style;
        }
    }
}