NPOI導出Excel

安裝npoi nuget包，在設置列寬時，不使用自動設置AutoSizeColumn，這個設了也未必準且有性能問題。

設置單元格的自定義格式，可以參考excel。

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;

namespace ConsoleApp1
{
    internal class Program
    {
        public static void Main()
        {
            DataTable table = new DataTable();
            table.Columns.Add("客戶");
            table.Columns.Add("XX份額");
            table.Columns.Add("XX占比");
            table.Rows.Add("科比","8000000000000", "0.9");
            table.Rows.Add("科比2","8000000000000.94", "0.7");
            table.Rows.Add("科比3","8000000000000.886", "0.5");

            IWorkbook workbook = new HSSFWorkbook();
            string fileName = @"C:\Users\s-huangsb\Desktop\xxx.xls";
            ExportExcel(table, fileName, workbook);
            try
            {
                using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                }
            }
            catch (Exception ex)
            {
                //handle exception
            }
        }

        private static void ExportExcel(DataTable table, string fileName, IWorkbook workbook)
        {

            ISheet sheet = workbook.CreateSheet("客戶信息");
            ICellStyle headercellStyle = GetHeaderStyle(workbook);

            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.IsBold = false;
            cellfont.FontName = "宋體";
            cellfont.FontHeightInPoints = 11;

            ICellStyle cellStyle = GetCellStyle(workbook);
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.SetFont(cellfont);

            ICellStyle numCellStyle = GetCellStyle(workbook);
            numCellStyle.SetFont(cellfont);
            numCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            numCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");

            ICellStyle ratioCellStyle = GetCellStyle(workbook);
            ratioCellStyle.SetFont(cellfont);
            ratioCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            ratioCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            int iRowIndex = 0;
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(iRowIndex);
            foreach (DataColumn item in table.Columns)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(item.ColumnName);
                cell.CellStyle = headercellStyle;
                icolIndex++;
            }
            iRowIndex++;

            int iCellIndex = 0;
            foreach (DataRow row in table.Rows)
            {
                IRow DataRow = sheet.CreateRow(iRowIndex);
                foreach (DataColumn colItem in table.Columns)
                {
                    ICell cell = DataRow.CreateCell(iCellIndex);
                    if (colItem.ColumnName.Contains("份額"))
                    {
                        cell.SetCellValue(ToDoubleEx(row[colItem]));
                        cell.CellStyle = numCellStyle;
                    }
                    else if (colItem.ColumnName.Contains("占比"))
                    {
                        cell.SetCellValue(Convert.ToDouble(row[colItem]));
                        cell.CellStyle = ratioCellStyle;
                    }
                    else
                    {
                        cell.SetCellValue(row[colItem].ToString());
                        cell.CellStyle = cellStyle;
                    }
                    iCellIndex++;
                }
                iCellIndex = 0;
                iRowIndex++;
            }            

            List<int> colsLength = new List<int>();
            foreach (DataColumn column in table.Columns)
            {
                var length = table.AsEnumerable().Max(row => row[column].ToString().Length);
                colsLength.Add(length);
            }

            AutoColumnWidth(sheet, table.Columns.Count, colsLength.ToArray(), 9);
        }

        private static void AutoColumnWidth(ISheet sheet, int cols, int[] colLength, int addlength)
        {
            for (int col = 0; col < cols; col++)
            {
                var columnWidth = colLength[col] * 256 + 30 * 256;
                sheet.SetColumnWidth(col, columnWidth);
            }
        }

        private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            return cellStyle;
        }

        private static ICellStyle GetHeaderStyle(IWorkbook workbook)
        {
            ICellStyle headercellStyle = workbook.CreateCellStyle();
            headercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            headercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            
            headercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            headercellStyle.FillPattern = FillPattern.SolidForeground;
            NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
            headerfont.IsBold = true;
            headerfont.FontName = "宋體";
            headerfont.FontHeightInPoints = 11;
            headercellStyle.SetFont(headerfont);

            return headercellStyle;
        }

        private static double ToDoubleEx(object obj)
        {
            if (obj == DBNull.Value)
            {
                return 0;
            }
            string str = obj.ToString();
            if (str == null || str.Trim() == string.Empty)
            {
                return 0;
            }
            else
            {
                return Convert.ToDouble(str);
            }
        }
    }
}