NPOI导出Excel

安装npoi nuget包，在设置列宽时，不使用自动设置AutoSizeColumn，这个设了也未必准且有性能问题。

设置单元格的自定义格式，可以参考excel。

合并列代码片段：合并列的单元格格式每个都要处理。

 // 建立合并列   
            iRowIndex = iRowIndex + 2;
            IRow mergefundRow = sheet.CreateRow(iRowIndex);
            ICell mergefundCell = mergefundRow.CreateCell(0);
            mergefundCell.CellStyle = cellStyle;
            mergefundCell.SetCellValue("基金信息");
            var region2 = new CellRangeAddress(iRowIndex, iRowIndex, 0, 11);
            sheet.AddMergedRegion(region2);
            for (int i = region2.FirstRow; i <= region2.LastRow; i++)
            {
                IRow row = HSSFCellUtil.GetRow(i, (HSSFSheet)sheet);
                for (int j = region2.FirstColumn; j <= region2.LastColumn; j++)
                {
                    ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
                    singleCell.CellStyle = cellStyle;
                }
            }

下面是完整的导出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:Userss-huangsbDesktopxxx.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);
            }
        }
    }
}