基于NPOI的扩展

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

namespace Tools
{
    public static class NPOIHelper
    {
        /// <summary>
        /// 设置表格样式，边框线为黑色实线
        /// </summary>
        /// <param name="hssfworkbook">Excel表格</param>
        /// <returns></returns>
        public static ICellStyle CreateCellStyle(IWorkbook workbook)
        {
            var style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;
            style.TopBorderColor = HSSFColor.BLACK.index;
            style.BottomBorderColor = HSSFColor.BLACK.index;
            style.LeftBorderColor = HSSFColor.BLACK.index;
            style.RightBorderColor = HSSFColor.BLACK.index;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            return style;
        }

        /// <summary>
        /// 创建一个普通的Excel表格
        /// </summary>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="headers">表格头部，默认取DataTable的ColumnName</param>
        /// <param name="columnWidths">各列宽度（单位：字符宽度）</param>
        /// <param name="rowHight">各行高度（单位：磅）</param>
        /// <param name="rowCount">表格创建行数，默认取DataTable的Rows.Count</param>
        /// <param name="data">数据</param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, DataTable data)
        {
            var hssfworkbook = new HSSFWorkbook();
            CreateSheet(hssfworkbook, sheetName, headers, columnWidths, rowHight, rowCount, data);
            return hssfworkbook;
        }

        /// <summary>
        /// 创建一个普通的Excel表格
        /// </summary>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="headers">表格头部</param>
        /// <param name="columnWidths">各列宽度（单位：字符宽度）</param>
        /// <param name="rowHight">各行高度（单位：磅）</param>
        /// <param name="rowCount">表格创建行数</param>
        /// <param name="data">数据</param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, List<List<string>> data)
        {
            var hssfworkbook = new HSSFWorkbook();
            CreateSheet(hssfworkbook, sheetName, headers, columnWidths, rowHight, rowCount, data);
            return hssfworkbook;
        }

        /// <summary>
        /// 创建一个普通的sheet表格
        /// </summary>
        /// <param name="workbook">Excel表格</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="headers">表格头部，默认取DataTable的ColumnName</param>
        /// <param name="columnWidths">各列宽度（单位：字符宽度）</param>
        /// <param name="rowHight">各行高度（单位：磅）</param>
        /// <param name="rowCount">表格创建行数，默认取DataTable的Rows.Count</param>
        /// <param name="data">数据</param>
        /// <returns></returns>
        public static ISheet CreateSheet(IWorkbook workbook, string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, DataTable data)
        {
            List<string> l = null;
            List<List<string>> d = null;

            if (data != null && data.Rows.Count > 0)
            {
                d = new List<List<string>>();
                foreach (DataRow dr in data.Rows)
                {
                    l = new List<string>();
                    foreach (object obj in dr.ItemArray)
                    {
                        l.Add(obj.ToZMStringOrEmpty());
                    }
                    d.Add(l);
                }

                if (headers.IsNullOrEmpty())
                {
                    var h = new List<string>();
                    foreach (DataColumn dc in data.Columns)
                    {
                        h.Add(dc.ColumnName);
                    }

                    //for (var i = 0; i < data.Columns.Count; ++i )
                    //{
                    //    h.Add(data.Columns[i].ColumnName);
                    //}

                    headers = h.ToArray();
                }
            }

            return CreateSheet(workbook, sheetName, headers, columnWidths, rowHight, rowCount, d);
        }

        /// <summary>
        /// 创建一个普通的sheet表格
        /// </summary>
        /// <param name="workbook">Excel表格</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <param name="headers">表格头部</param>
        /// <param name="columnWidths">各列宽度（单位：字符宽度）</param>
        /// <param name="rowHight">各行高度（单位：磅）</param>
        /// <param name="rowCount">表格创建行数</param>
        /// <param name="data">数据</param>
        /// <returns></returns>
        public static ISheet CreateSheet(IWorkbook workbook, string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, List<List<string>> data)
        {
            var sheet = workbook.CreateSheet(sheetName);
            ICellStyle cellStyle = null;
            ICell cell = null;
            IRow row = null;
            var columnCount = 0;

            // 设置头部
            if (headers.HasItem())
            {
                row = sheet.CreateRow(0);
                row.Height = 500;
                //新建一个字体样式对象
                IFont font = workbook.CreateFont();
                //设置字体加粗样式
                font.Boldweight = short.MaxValue;
                cellStyle = CreateCellStyle(workbook);
                for (var i = 0; i < headers.Length; ++i)
                {
                    cell = row.CreateCell(i, CellType.STRING);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue(headers[i]);
                    //使用SetFont方法将字体样式添加到单元格样式中
                    cell.CellStyle.SetFont(font);
                    cell.CellStyle.Alignment = HorizontalAlignment.CENTER;
                }
                columnCount = headers.Length;
            }

            // 设置列宽
            if (columnWidths.HasItem())
            {
                for (var i = 0; i < columnWidths.Length; ++i)
                {
                    // 设置表格宽度
                    sheet.SetColumnWidth(i, columnWidths[i] * 256);
                }
            }

            // 设置数据
            if (data.HasItem())
            {
                rowCount = data.Count;
                foreach (var array in data)
                {
                    if (array.Count > columnCount)
                    {
                        columnCount = array.Count;
                    }
                }
            }

            cellStyle = CreateCellStyle(workbook);
            for (var i = 0; i < rowCount; ++i)
            {
                row = sheet.CreateRow(sheet.LastRowNum + 1);
                row.Height = (short)(rowHight * 20);
                for (var j = 0; j < columnCount; ++j)
                {
                    cell = row.CreateCell(j, CellType.STRING);
                    cell.CellStyle = cellStyle;
                    try
                    {
                        cell.SetCellValue(data[i][j].ToZMStringOrEmpty());
                    }
                    catch
                    {
                        cell.SetCellValue(string.Empty);
                    }
                }
            }

            return sheet;
        }
    }
}