NPOI导出excel

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace bmy.Common
{
    public static class NPOIExcelHelper
    {
 /// 

        ///  组装workbook.
        /// 
        /// 列头
        /// dataTable数据
        /// 表头
        /// 
        public static HSSFWorkbook BuildWorkbook1(Dictionary<string, string> dictionary, DataTable dt, string columnHeader = "")
        {
            var workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet1" : dt.TableName);

            var dateStyle = workbook.CreateCellStyle();
            var format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            // var arrColWidth = new int[dt.Columns.Count];
            var arrColWidth = new int[dictionary.Count + 1];
            int itemCoutn = 0;//需要导出的列的数量.
            foreach (DataColumn item in dt.Columns)
            {
                //判断需要导出的 “列”

                if (dictionary.ContainsKey(item.ColumnName))
                {
                    arrColWidth[itemCoutn] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                    itemCoutn++;
                }
            }
            itemCoutn = 0;
            for (var i = 0; i )
            {
                for (var j = 0; j )
                {
                    //判断需要导出的 “列”
                    if (dictionary.ContainsKey(dt.Rows[i][j].ToString()))
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
            }
            int rowIndex = 0;//行索引，第一行为：表头（列头）

            foreach (DataRow row in dt.Rows)
            {
                #region 表头 列头
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 19.5F;
                        headerRow.Height = 40 * 20;
                        headerRow.CreateCell(0).SetCellValue(columnHeader);

                        //CellStyle
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中    
                        headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中 
                        // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）    
                        headStyle.FillForegroundColor = (short)11;
                        //定义font
                        IFont fOnt= workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        //根据表的列数计算
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dictionary.Count));
                    #endregion

                    }
                    {
                        #region 列头及样式

                        var headerRow = sheet.CreateRow(1);
                        //CellStyle
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中    
                        headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中 
                        //定义font
                        IFont fOnt= workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        int columnCount = 0;
                        foreach (var dic in dictionary)
                        {
                            foreach (DataColumn column in dt.Columns)
                            {
                                //判断需要导出的 “列”
                                if (dic.Key.ToLower()==column.ColumnName.ToLower())
                                {
                                    //headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                                    //headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                                    //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                                    headerRow.CreateCell(columnCount).SetCellValue(dic.Value);//column.ColumnName
                                    headerRow.GetCell(columnCount).CellStyle = headStyle;
                                    sheet.SetColumnWidth(columnCount, (arrColWidth[columnCount]) * 256);
                                    columnCount++;
                                    continue;
                                }
                            }
                        }
                        #endregion
                    }

                    rowIndex = 2;
                }
                #endregion


                #region 内容
                var dataRow = sheet.CreateRow(rowIndex);

                int columnCOntentCount= 0; //column.Ordinal
                foreach (var dicKey in dictionary)
                {
                    foreach (DataColumn column in dt.Columns)
                    {
                        //判断需要导出的 “列”
                        if (dicKey.Key.ToLower()==column.ColumnName.ToLower())
                        {
                            var newCell = dataRow.CreateCell(columnContentCount);

                            string drValue = row[column].ToString();

                            switch (column.DataType.ToString())
                            {
                                case "System.String"://字符串类型
                                    newCell.SetCellValue(drValue);
                                    break;
                                case "System.DateTime"://日期类型
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    newCell.SetCellValue(dateV);

                                    newCell.CellStyle = dateStyle;//格式化显示
                                    break;
                                case "System.Boolean"://布尔型
                                    bool boolV = false;
                                    bool.TryParse(drValue, out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16"://整型
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(drValue, out intV);
                                    newCell.SetCellValue(intV);
                                    break;
                                case "System.Decimal"://浮点型
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(drValue, out doubV);
                                    newCell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull"://空值处理
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                            columnContentCount++;//列索引
                            continue;
                        }
                    }
                }

                #endregion

                rowIndex++;
            }
            //自动列宽
            for (int i = 0; i <= dictionary.Count; i++)
                sheet.AutoSizeColumn(i, true);


            return workbook;
        }
    
    }    

}