NPOI 之导入导出

转自https://www.cnblogs.com/zuowj/archive/2015/05/04/4475663.html
转别人的，做了一点点改动

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

namespace RaysUtil.Office.Excel
{
    //titleRow1.Cells[0].Hyperlink = new HSSFHyperlink(HyperlinkType.Url);
    public static class ExcelHelper
    {
        /// <summary>
        /// 获取保存文件路径
        /// </summary>
        /// <returns></returns>
        private static string GetSaveFilePath()
        {
            SaveFileDialog saveFileDig = new SaveFileDialog();
            saveFileDig.Filter = "Excel Office97 - 2003(*.xls) | *.xls | Excel Office2007及以上(*.xlsx) | *.xlsx";
            saveFileDig.Title = "导出到";
            saveFileDig.OverwritePrompt = true;
            saveFileDig.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            string filePath = null;
            if (saveFileDig.ShowDialog() == DialogResult.OK)
            {
                filePath = saveFileDig.FileName;
            }
            return filePath;
        }
        /// <summary>
        /// 获取要打开要导入的文件名称（含完整路径）
        /// </summary>
        /// <returns></returns>
        private static string GetOpenFilePath()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
            openFileDialog.FilterIndex = 0;
            openFileDialog.Title = "打开";
            openFileDialog.CheckFileExists = true;
            openFileDialog.CheckPathExists = true;
            openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            string filePath = null;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                filePath = openFileDialog.FileName;
            }
            return filePath;
        }
        /// <summary>
        /// 是否兼容模式
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        private static bool IsCompatible(string filePath)
        {
            //return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
            if (Path.GetExtension(filePath).ToLower() == ".xls")
            {
                return true;
            }
            else if (Path.GetExtension(filePath).ToLower() == ".xlsx")
            {
                return false;
            }
            else
            {
                throw new Exception("文件扩展名非法或者文件路径不正确！！！");
            }
        }
        /// <summary>
        /// 创建工作簿
        /// </summary>
        /// <param name="isCompatible">是否兼容模式</param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            else
            {
                return new XSSFWorkbook();
            }
        }
        /// <summary>
        /// 依据文件流创建工作簿
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }
        /// <summary>
        /// 创建表格单元格样式
        /// </summary>
        /// <param name="workbook">当前工作簿</param>
        /// <param name="borderStyleB">是否有下边框,默认True</param>
        /// <param name="borderStyleL">是否有左边框,默认True</param>
        /// <param name="borderStyleR">是否有右边框,默认True</param>
        /// <param name="borderStyleT">是否有上边框,默认True</param>
        /// <param name="borderStyle">有边框的样式,默认薄边框</param>
        /// <param name="colorIndex">背景色</param>
        /// <param name="isAlignment">是否横向对齐，默认True</param>
        /// <param name="horizontalAlignment">横向对齐,默认横向居中</param>
        /// <param name="verticalAlignment">垂直对齐，默认垂直居中</param>
        /// <param name="isSetFont">是否设置字体信息,默认False</param>
        /// <param name="font">字体信息,默认null</param>
        /// <param name="fontSize">字体大小</param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook, bool borderStyleB = true, bool borderStyleL = true, bool borderStyleR = true, bool borderStyleT = true, NPOI.SS.UserModel.BorderStyle borderStyle = NPOI.SS.UserModel.BorderStyle.Thin, short colorIndex = HSSFColor.LightGreen.Index, bool isAlignment = true, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.Center, VerticalAlignment verticalAlignment = VerticalAlignment.Center, bool isSetFont = false, IFont font = null, short fontSize = 30)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = colorIndex;
            //边框
            style.BorderBottom = borderStyleB ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
            style.BorderLeft = borderStyleL ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
            style.BorderRight = borderStyleR ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
            style.BorderTop = borderStyleT ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
            //对齐
            if (isAlignment)
            {
                style.Alignment = horizontalAlignment;
            }
            else
            {
                style.VerticalAlignment = verticalAlignment;
            }
            if (isSetFont)
            {
                if (font == null)
                {
                    font = workbook.CreateFont();
                    font.Boldweight = short.MaxValue;
                    font.FontHeightInPoints = fontSize;
                }
                style.SetFont(font);
            }
            return style;
        }
        /// <summary>
        /// 创建表格单元格样式
        /// </summary>
        /// <param name="workbook">当前工作簿</param>
        /// <param name="cellStyle">边框样式模板</param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook, CellStyleModel cellStyle)
        {
            if (cellStyle==null)
            {
                cellStyle = new CellStyleModel();
            }
            ICellStyle style = workbook.CreateCellStyle();
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = cellStyle.ColorIndex;
            //边框
            style.BorderBottom = cellStyle.BorderStyleB ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
            style.BorderLeft = cellStyle.BorderStyleL ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
            style.BorderRight = cellStyle.BorderStyleR ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
            style.BorderTop = cellStyle.BorderStyleT ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
            //对齐
            if (cellStyle.IsAlignment)
            {
                style.Alignment = cellStyle.HorizontalAlignment;
            }
            else
            {
                style.VerticalAlignment = cellStyle.VerticalAlignment;
            }
            if (cellStyle.IsSetFont)
            {
                if (cellStyle.Font == null)
                {
                    cellStyle.Font = workbook.CreateFont();
                    cellStyle.Font.Boldweight = short.MaxValue;
                    cellStyle.Font.FontHeightInPoints = cellStyle.FontSize;
                }
                style.SetFont(cellStyle.Font);
            }
            return style;
        }
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowStart">开始行的索引</param>
        /// <param name="rowEnd">结束行的索引</param>
        /// <param name="colStart">开始列的索引</param>
        /// <param name="colEnd">结束行的索引</param>
        public static void SetCellRangeAddress(ISheet sheet, int rowStart, int rowEnd, int colStart, int colEnd)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
            sheet.AddMergedRegion(cellRangeAddress);
        }
        /// <summary>
        /// 工作表生成DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();
            #region 操作首行(标题行)
            //获取首行
            IRow headerRow = sheet.GetRow(headerRowIndex);
            //PhysicalNumberOfCells获取不为空的列个数
            //LastCellNum获取最后一个不为空的列个数
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    //如果标题行遇到空列，则不再向后继续读取
                    cellCount = i + 1;//返回真实列数
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            #endregion
            #region 遍历数据行，标题行除外
            //遍历数据行，标题行除外
            for (int i = (headerRowIndex + 1); i < sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
                {
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        dataRow[i] = row.GetCellEx(j).ToString();
                    }
                    table.Rows.Add(dataRow);
                }
            }
            #endregion
            return table;
        }
        #region 公共导出方法
        /// <summary>
        /// DataSet导出Excel
        /// </summary>
        /// <param name="sourceDs">DataSet源</param>
        /// <param name="filePath">文件保存路径</param>
        /// <param name="titles">首行标题数组</param>
        /// <param name="childTitles">子标题数组</param>
        /// <param name="dateTimes">子标题时间</param>
        /// <param name="cellStyle">样式类</param>
        /// <returns>返回生成的Excel保存路径</returns>
        public static string ExportToExcel(DataSet sourceDs, string filePath, string[] titles = null, string[] childTitles = null, string[] dateTimes = null)
        {
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            #region 检测标题、子标题、时间
            if (titles == null)
            {
                titles = new string[sourceDs.Tables.Count];
                for (int i = 0; i < titles.Length; i++)
                {
                    titles[i] = sourceDs.Tables[i].TableName;
                }
            }
            if (dateTimes == null)
            {
                dateTimes = new string[sourceDs.Tables.Count];
                for (int i = 0; i < dateTimes.Length; i++)
                {
                    titles[i] = DateTime.Now.ToString("yyyy-MM-dd");
                }
            }
            if (titles != null && (titles.Length < sourceDs.Tables.Count || titles.Length > sourceDs.Tables.Count))
            {
                throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
            }
            if (childTitles != null && (childTitles.Length < sourceDs.Tables.Count || childTitles.Length > sourceDs.Tables.Count))
            {
                throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
            }
            if (dateTimes != null && (dateTimes.Length < sourceDs.Tables.Count || dateTimes.Length > sourceDs.Tables.Count))
            {
                throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
            }
            #endregion


            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
            #region sheet处理
            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                DataTable table = sourceDs.Tables[i];
                string sheetName = string.IsNullOrEmpty(table.TableName) ? "sheet" + i.ToString() : table.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow titleRow1 = sheet.CreateRow(0);
                IRow titleRow2 = sheet.CreateRow(1);
                IRow headerRow = sheet.CreateRow(2);

                #region 处理首行
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = headerRow.CreateCell(column.Ordinal);
                    ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
                    ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = headerCellStyle;
                }
                //标题行样式
                int cellMaxIndex = titleRow1.LastCellNum - 1;
                SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
                SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
                SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
                titleRow1.Cells[0].SetCellValue(titles[i]);
                if (childTitles != null)
                {
                    titleRow2.Cells[0].SetCellValue(childTitles[i]);
                }
                titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(dateTimes[i]);
                titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));

                titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
                titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
                titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);
                titleRow1.HeightInPoints = 40;
                titleRow2.HeightInPoints = 20;
                #endregion

                #region 处理数据行，首行除外
                int rowIndex = 3;
                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in table.Columns)
                    {
                        ICell dataCell = dataRow.CreateCell(column.Ordinal);
                        dataCell.SetCellValue((row[column] ?? "").ToString());
                        dataCell.CellStyle = dataCellStyle;
                    }
                    rowIndex++;
                }
                #endregion
                //设置列宽
                for (int k = 0; k < table.Columns.Count; k++)
                {
                    sheet.SetColumnWidth(k, (table.Columns[k].Caption.Length < 20 ? 20 : table.Columns[k].Caption.Length) * 256);
                }
            }
            #endregion
            #region 保存
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(fs);
            }
            workbook = null;
            #endregion
            return filePath;
        }
        /// <summary>
        /// DataSet导出Excel
        /// </summary>
        /// <param name="sourceDs">DataSet源</param>
        /// <param name="filePath">文件保存路径</param>
        /// <param name="titles">首行标题数组</param>
        /// <param name="childTitles">子标题数组</param>
        /// <param name="dateTimes">子标题时间</param>
        /// <param name="cellStyle">样式类</param>
        /// <returns></returns>
        public static string ExportToExcel(DataSet sourceDs, string filePath, CellStyleModel cellStyle , string[] titles = null, string[] childTitles = null, string[] dateTimes = null)
        {
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            #region 检测标题、子标题、时间
            if (titles == null)
            {
                titles = new string[sourceDs.Tables.Count];
                for (int i = 0; i < titles.Length; i++)
                {
                    titles[i] = sourceDs.Tables[i].TableName;
                }
            }
            if (dateTimes == null)
            {
                dateTimes = new string[sourceDs.Tables.Count];
                for (int i = 0; i < dateTimes.Length; i++)
                {
                    titles[i] = DateTime.Now.ToString("yyyy-MM-dd");
                }
            }
            if (titles != null && (titles.Length < sourceDs.Tables.Count || titles.Length > sourceDs.Tables.Count))
            {
                throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
            }
            if (childTitles != null && (childTitles.Length < sourceDs.Tables.Count || childTitles.Length > sourceDs.Tables.Count))
            {
                throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
            }
            if (dateTimes != null && (dateTimes.Length < sourceDs.Tables.Count || dateTimes.Length > sourceDs.Tables.Count))
            {
                throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
            }
            #endregion


            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle ?? new CellStyleModel { ColorIndex = 22 });
            //数据行样式
            
            ICellStyle dataCellStyle = GetCellStyle(workbook, cellStyle ?? new CellStyleModel { HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.Left, ColorIndex = 42 });
            #region sheet处理
            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                DataTable table = sourceDs.Tables[i];
                string sheetName = string.IsNullOrEmpty(table.TableName) ? "sheet" + i.ToString() : table.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow titleRow1 = sheet.CreateRow(0);
                IRow titleRow2 = sheet.CreateRow(1);
                IRow headerRow = sheet.CreateRow(2);

                #region 处理首行
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = headerRow.CreateCell(column.Ordinal);
                    ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
                    ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = headerCellStyle;
                }
                //标题行样式
                int cellMaxIndex = titleRow1.LastCellNum - 1;
                SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
                SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
                SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
                titleRow1.Cells[0].SetCellValue(titles[i]);
                if (childTitles != null)
                {
                    titleRow2.Cells[0].SetCellValue(childTitles[i]);
                }
                titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(dateTimes[i]);
                titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
                    cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));

                titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
                    cellStyle??new CellStyleModel { BorderStyleT=false,ColorIndex=32767}));
                titleRow2.Cells[0].CellStyle = GetCellStyle(workbook,
                    cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
                titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook,
                    cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
                titleRow1.HeightInPoints = 40;
                titleRow2.HeightInPoints = 20;
                #endregion

                #region 处理数据行，首行除外
                int rowIndex = 3;
                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in table.Columns)
                    {
                        ICell dataCell = dataRow.CreateCell(column.Ordinal);
                        dataCell.SetCellValue((row[column] ?? "").ToString());
                        dataCell.CellStyle = dataCellStyle;
                    }
                    rowIndex++;
                }
                #endregion
                //设置列宽
                for (int k = 0; k < table.Columns.Count; k++)
                {
                    sheet.SetColumnWidth(k, (table.Columns[k].Caption.Length < 20 ? 20 : table.Columns[k].Caption.Length) * 256);
                }
            }
            #endregion
            #region 保存
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(fs);
            }
            workbook = null;
            #endregion
            return filePath;
        }
        /// <summary>
        /// DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">DataTable源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="filePath">文件保存路径</param>
        /// <param name="title">首行标题</param>
        /// <param name="childTitle">子标题</param>
        /// <param name="dateTime">子标题时间</param>
        /// <param name="cellStyle">样式类</param>
        /// <returns>返回生成的Excel保存路径</returns>
        public static string ExportToExcel(DataTable sourceTable, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
        {
            if (sourceTable.Rows.Count <= 0) throw new ArgumentException("DataTable源不存在有效的数据!");
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow titleRow1 = sheet.CreateRow(0);
            IRow titleRow2 = sheet.CreateRow(1);
            IRow headerRow = sheet.CreateRow(2);

            #region 处理首行
            foreach (DataColumn column in sourceTable.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
                ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
                cell.SetCellValue(column.ColumnName);
                cell.CellStyle = headerCellStyle;
            }

            //标题行样式
            int cellMaxIndex = titleRow1.LastCellNum - 1;
            SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
            SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
            SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);

            titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sourceTable.TableName : title);
            titleRow2.Cells[0].SetCellValue(childTitle);
            titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);

            titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));

            //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
            titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
            titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);

            titleRow1.HeightInPoints = 40;
            titleRow2.HeightInPoints = 20;
            #endregion

            #region 处理数据行，首行除外
            int rowIndex = 3;
            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    ICell dataCell = dataRow.CreateCell(column.Ordinal);
                    dataCell.SetCellValue((row[column] ?? "").ToString());
                    dataCell.CellStyle = dataCellStyle;
                }
                rowIndex++;
            }
            //设置列宽
            for (int k = 0; k < sourceTable.Columns.Count; k++)
            {
                sheet.SetColumnWidth(k, (sourceTable.Columns[k].Caption.Length < 20 ? 20 : sourceTable.Columns[k].Caption.Length) * 256);
            }
            #endregion
            #region 保存
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(fs);
            }
            sheet = null;
            headerRow = null;
            workbook = null;
            #endregion
            return filePath;
        }
        /// <summary>
        /// DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">DataTable源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="filePath">文件保存路径</param>
        /// <param name="title">首行标题</param>
        /// <param name="childTitle">子标题</param>
        /// <param name="dateTime">子标题时间</param>
        /// <param name="cellStyle">样式类</param>
        /// <returns></returns>
        public static string ExportToExcel(DataTable sourceTable, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
        {
            if (sourceTable.Rows.Count <= 0) throw new ArgumentException("DataTable源不存在有效的数据!");
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=22});
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { HorizontalAlignment=NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=42});
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow titleRow1 = sheet.CreateRow(0);
            IRow titleRow2 = sheet.CreateRow(1);
            IRow headerRow = sheet.CreateRow(2);

            #region 处理首行
            foreach (DataColumn column in sourceTable.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
                ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
                cell.SetCellValue(column.ColumnName);
                cell.CellStyle = headerCellStyle;
            }

            //标题行样式
            int cellMaxIndex = titleRow1.LastCellNum - 1;
            SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
            SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
            SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);

            titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sourceTable.TableName : title);
            titleRow2.Cells[0].SetCellValue(childTitle);
            titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);

            titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
            //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
            titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
            titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
            titleRow1.HeightInPoints = 40;
            titleRow2.HeightInPoints = 20;
            #endregion

            #region 处理数据行，首行除外
            int rowIndex = 3;
            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    ICell dataCell = dataRow.CreateCell(column.Ordinal);
                    dataCell.SetCellValue((row[column] ?? "").ToString());
                    dataCell.CellStyle = dataCellStyle;
                }
                rowIndex++;
            }
            //设置列宽
            for (int k = 0; k < sourceTable.Columns.Count; k++)
            {
                sheet.SetColumnWidth(k, (sourceTable.Columns[k].Caption.Length < 20 ? 20 : sourceTable.Columns[k].Caption.Length) * 256);
            }
            #endregion
            #region 保存
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(fs);
            }
            sheet = null;
            headerRow = null;
            workbook = null;
            #endregion
            return filePath;
        }
        /// <summary>
        /// List导出Excel
        /// </summary>
        /// <typeparam name="T">List元素类型</typeparam>
        /// <param name="data">List数据源</param>
        /// <param name="headerNameList">首行数据映射源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="filePath">导出的文件地址</param>
        /// <param name="title">首行标题</param>
        /// <param name="childTitle">子标题</param>
        /// <param name="dateTime">子标题时间</param>
        /// <returns></returns>
        public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "") where T : class
        {
            if (data.Count <= 0) throw new ArgumentException("List数据源不存在有效的数据");
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow titleRow1 = sheet.CreateRow(0);
            IRow titleRow2 = sheet.CreateRow(1);
            IRow headerRow = sheet.CreateRow(2);

            #region 处理首行
            for (int i = 0; i < headerNameList.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                ICell titleCell1 = titleRow1.CreateCell(i);
                ICell titleCell2 = titleRow2.CreateCell(i);
                cell.SetCellValue(headerNameList[i].Value);
                cell.CellStyle = headerCellStyle;
            }
            //标题行样式
            int cellMaxIndex = titleRow1.LastCellNum - 1;
            SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
            SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
            SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
            //Console.WriteLine("合并后列数是{0}",titleRow1.PhysicalNumberOfCells);
            titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
            titleRow2.Cells[0].SetCellValue(childTitle);
            titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
            titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));

            //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
            titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
            titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);
            titleRow1.HeightInPoints = 40;
            titleRow2.HeightInPoints = 20;
            #endregion
            #region 处理数据行
            Type t = typeof(T);
            int rowIndex = 3;
            foreach (T item in data)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int j = 0; j < headerNameList.Count; j++)
                {
                    object pValue = t.GetProperty(headerNameList[j].Key).GetValue(item, null);
                    ICell dataCell = dataRow.CreateCell(j);
                    dataCell.SetCellValue((pValue ?? "").ToString());
                    dataCell.CellStyle = dataCellStyle;
                }
                rowIndex++;
            }
            //设置列宽
            for (int k = 0; k < headerRow.Cells.Count; k++)
            {
                sheet.SetColumnWidth(k, (headerRow.Cells[k].StringCellValue.Length < 20 ? 20 : headerRow.Cells[k].StringCellValue.Length) * 256);
            }
            #endregion
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            sheet = null;
            headerRow = null;
            workbook = null;
            return filePath;
        }
        /// <summary>
        /// List导出Excel
        /// </summary>
        /// <typeparam name="T">List元素类型</typeparam>
        /// <param name="data">List数据源</param>
        /// <param name="headerNameList">首行数据映射源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="filePath">导出的文件地址</param>
        /// <param name="title">首行标题</param>
        /// <param name="childTitle">子标题</param>
        /// <param name="dateTime">子标题时间</param>
        /// <param name="cellStyle">样式类</param>
        /// <returns></returns>
        public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "") where T : class
        {
            if (data.Count <= 0) throw new ArgumentException("List数据源不存在有效的数据");
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=22});
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { HorizontalAlignment= NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=42});
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow titleRow1 = sheet.CreateRow(0);
            IRow titleRow2 = sheet.CreateRow(1);
            IRow headerRow = sheet.CreateRow(2);

            #region 处理首行
            for (int i = 0; i < headerNameList.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                ICell titleCell1 = titleRow1.CreateCell(i);
                ICell titleCell2 = titleRow2.CreateCell(i);
                cell.SetCellValue(headerNameList[i].Value);
                cell.CellStyle = headerCellStyle;
            }
            //标题行样式
            int cellMaxIndex = titleRow1.LastCellNum - 1;
            SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
            SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
            SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
            //Console.WriteLine("合并后列数是{0}",titleRow1.PhysicalNumberOfCells);
            titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
            titleRow2.Cells[0].SetCellValue(childTitle);
            titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
            titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
            //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
            titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
            titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
            titleRow1.HeightInPoints = 40;
            titleRow2.HeightInPoints = 20;
            #endregion
            #region 处理数据行
            Type t = typeof(T);
            int rowIndex = 3;
            foreach (T item in data)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int j = 0; j < headerNameList.Count; j++)
                {
                    object pValue = t.GetProperty(headerNameList[j].Key).GetValue(item, null);
                    ICell dataCell = dataRow.CreateCell(j);
                    dataCell.SetCellValue((pValue ?? "").ToString());
                    dataCell.CellStyle = dataCellStyle;
                }
                rowIndex++;
            }
            //设置列宽
            for (int k = 0; k < headerRow.Cells.Count; k++)
            {
                sheet.SetColumnWidth(k, (headerRow.Cells[k].StringCellValue.Length < 20 ? 20 : headerRow.Cells[k].StringCellValue.Length) * 256);
            }
            #endregion
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            sheet = null;
            headerRow = null;
            workbook = null;
            return filePath;
        }
        /// <summary>
        /// DataGridView导出Excel
        /// </summary>
        /// <param name="grid">DataGridView数据源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="filePath">文件路径</param>
        /// <param name="title">首行标题</param>
        /// <param name="childTitle">子标题</param>
        /// <param name="dateTime">子标题时间</param>
        /// <returns></returns>
        public static string ExportToExcel(DataGridView grid, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
        {
            if (grid.Rows.Count <= 0) throw new ArgumentException("DataGridView数据源不存在游侠的数据！");
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow titleRow1 = sheet.CreateRow(0);
            IRow titleRow2 = sheet.CreateRow(1);
            IRow headerRow = sheet.CreateRow(2);

            for (int i = 0; i < grid.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                ICell titleCell1 = titleRow1.CreateCell(i);
                ICell titleCell2 = titleRow2.CreateCell(i);
                cell.SetCellValue(grid.Columns[i].HeaderText);
                cell.CellStyle = headerCellStyle;
            }
            //标题行样式
            int cellMaxIndex = titleRow1.LastCellNum - 1;
            SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
            SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
            SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
            titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
            titleRow2.Cells[0].SetCellValue(childTitle);
            titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
            titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));

            //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
            titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
            titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);
            titleRow1.HeightInPoints = 40;
            titleRow2.HeightInPoints = 20;

            int rowIndex = 3;
            foreach (DataGridViewRow row in grid.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int j = 0; j < grid.Columns.Count; j++)
                {
                    ICell dataCell = dataRow.CreateCell(j);
                    dataCell.SetCellValue((row.Cells[j].Value ?? "").ToString());
                    dataCell.CellStyle = dataCellStyle;
                }
                rowIndex++;
            }

            //设置列宽
            for (int k = 0; k < grid.Columns.Count; k++)
            {
                sheet.SetColumnWidth(k, (grid.Columns[k].HeaderText.Length < 20 ? 20 : grid.Columns[k].HeaderText.Length) * 256);
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            sheet = null;
            headerRow = null;
            workbook = null;
            return filePath;
        }
        /// <summary>
        /// DataGridView导出Excel
        /// </summary>
        /// <param name="grid">DataGridView数据源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="filePath">文件路径</param>
        /// <param name="title">首行标题</param>
        /// <param name="childTitle">子标题</param>
        /// <param name="dateTime">子标题时间</param>
        /// <param name="cellStyle">样式类</param>
        /// <returns></returns>
        public static string ExportToExcel(DataGridView grid, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
        {
            if (grid.Rows.Count <= 0) throw new ArgumentException("DataGridView数据源不存在游侠的数据！");
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = GetSaveFilePath();
            }
            if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
            bool isCompatible = IsCompatible(filePath);
            IWorkbook workbook = CreateWorkbook(isCompatible);
            //表头行样式
            ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=22});
            //数据行样式
            ICellStyle dataCellStyle = GetCellStyle(workbook,cellStyle??new CellStyleModel { HorizontalAlignment=NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=42});
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow titleRow1 = sheet.CreateRow(0);
            IRow titleRow2 = sheet.CreateRow(1);
            IRow headerRow = sheet.CreateRow(2);

            for (int i = 0; i < grid.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                ICell titleCell1 = titleRow1.CreateCell(i);
                ICell titleCell2 = titleRow2.CreateCell(i);
                cell.SetCellValue(grid.Columns[i].HeaderText);
                cell.CellStyle = headerCellStyle;
            }
            //标题行样式
            int cellMaxIndex = titleRow1.LastCellNum - 1;
            SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
            SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
            SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
            titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
            titleRow2.Cells[0].SetCellValue(childTitle);
            titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
            titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
            //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
            titleRow2.Cells[0].CellStyle = GetCellStyle(workbook,
                cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
            titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, 
                cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
            titleRow1.HeightInPoints = 40;
            titleRow2.HeightInPoints = 20;
            int rowIndex = 3;
            foreach (DataGridViewRow row in grid.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int j = 0; j < grid.Columns.Count; j++)
                {
                    ICell dataCell = dataRow.CreateCell(j);
                    dataCell.SetCellValue((row.Cells[j].Value ?? "").ToString());
                    dataCell.CellStyle = dataCellStyle;
                }
                rowIndex++;
            }

            //设置列宽
            for (int k = 0; k < grid.Columns.Count; k++)
            {
                sheet.SetColumnWidth(k, (grid.Columns[k].HeaderText.Length < 20 ? 20 : grid.Columns[k].HeaderText.Length) * 256);
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            sheet = null;
            headerRow = null;
            workbook = null;
            return filePath;
        }
        #endregion
        #region 公共导入方法
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="headerRowIndex">表头行索引</param>
        /// <param name="isCompatible">是否兼容模式</param>
        /// <returns>返回DataTable</returns>
        public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
        {
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }
            DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="headerRowIndex">表头行索引</param>
        /// <returns></returns>
        public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = GetOpenFilePath();
            }
            if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            using (FileStream fs = File.OpenRead(excelFilePath))
            {
                bool isCompatible = IsCompatible(excelFilePath);
                return ImportFromExcel(fs, sheetName, headerRowIndex, isCompatible);
            }
        }
        /// <summary>
        /// Excel导入DataSet,如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headRowIndex">表头行索引</param>
        /// <param name="isCompatible">是否兼容模式</param>
        /// <returns>返回dataSet</returns>
        public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
        {
            DataSet ds = new DataSet();
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
                ds.Tables.Add(table);
            }
            excelFileStream.Close();
            workbook = null;
            return ds;
        }
        /// <summary>
        /// Excel导入DataSet,如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径</param>
        /// <param name="headerRowIndex">表头行索引</param>
        /// <returns>返回dataSet</returns>
        public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = GetOpenFilePath();
            }
            if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            using (FileStream fs = File.OpenRead(excelFilePath))
            {
                bool isCompatible = IsCompatible(excelFilePath);
                return ImportFromExcel(fs, headerRowIndex, isCompatible);
            }
        }
        /// <summary>
        /// Excel导入List
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="excelFilePath">Excel文件全路径</param>
        /// <param name="headerRowIndex">行索引</param>
        /// <returns></returns>
        public static IList<T> ImportFromExcel<T>(string excelFilePath, int headerRowIndex = 0) where T : class, new()
        {
            IList<T> list = new List<T>();
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = GetOpenFilePath();
            }
            if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            try
            {
                using (FileStream fs = File.OpenRead(excelFilePath))
                {
                    bool isComPatible = IsCompatible(excelFilePath);
                    IWorkbook workbook = CreateWorkbook(isComPatible, fs);
                    ISheet sheet = workbook.GetSheetAt(0);
                    IRow headerRow = sheet.GetRow(0);
                    int headerCellNum = headerRow.LastCellNum;
                    for (int i = headerRow.FirstCellNum; i < headerCellNum; i++)
                    {
                        if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                        {
                            //如果标题行遇到空列，则不再向后继续读取
                            headerCellNum = i + 1;//返回真实列数
                            break;
                        }
                    }
                    //T t = default(T);
                    T t = new T();
                    PropertyInfo[] properties = typeof(T).GetProperties();
                    if (properties.Count() < headerCellNum)
                    {
                        throw new Exception("对象属性与工作表字段数量不符！");
                    }
                    for (int i = headerRowIndex + 1; i < sheet.LastRowNum + 1; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
                        {
                            t = Activator.CreateInstance<T>();
                            for (int j = row.FirstCellNum; j < headerCellNum; j++)
                            {
                                //如果碰到空cell，那么创建cell后赋空字符串
                                var value = ValueType(properties[j].PropertyType, row.GetCellEx(j).ToString());
                                properties[j].SetValue(t, value, null);
                            }
                            list.Add(t);

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw new Exception(ex.Message);
            }
            return list;
        }
        /// <summary>
        /// Excel导入List
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="excelFilePath">excel文件路径</param>
        /// <param name="fields">实体属性的字符串表示形式的数组，顺序与excel字段保持一致</param>
        /// <param name="headerRowIndex">头部索引行</param>
        /// <returns></returns>
        public static IList<T> ImportFromExcel<T>(ISheet sheet, string[] fields, int headerRowIndex = 0) where T : class, new()
        {
            if (fields == null) throw new ArgumentNullException("fields", "参数不能为null!");
            IList<T> list = new List<T>();
            try
            {
                IRow headerRow = sheet.GetRow(0);
                int headerCellNum = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i < headerCellNum; i++)
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        //如果标题行遇到空列，则不再向后继续读取
                        headerCellNum = i + 1;//返回真实列数
                        break;
                    }
                }
                if (fields.Length != headerCellNum)
                {
                    throw new Exception("指定的对象属性数量超过工作表字段数量！");
                }
                //T t = default(T);
                T t = new T();
                for (int i = headerRowIndex + 1; i < sheet.LastRowNum + 1; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
                    {
                        t = Activator.CreateInstance<T>();
                        for (int j = 0; j < fields.Length; j++)
                        {
                            var property = typeof(T).GetProperty(fields[j]);
                            //如果碰到空cell，那么创建cell后赋空字符串
                            var value = ValueType(property.PropertyType, row.GetCellEx(j).ToString());
                            property.SetValue(t, value, null);
                        }
                        list.Add(t);
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw new Exception(ex.Message);
            }
            return list;
        }
        /// <summary>
        /// Excel导入List
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="excelFilePath">excel文件全路径</param>
        /// <param name="fields">实体属性的字符串表示形式的数组，顺序与excel字段保持一致</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="headerRowIndex">首行索引，默认0</param>
        /// <returns></returns>
        public static IList<T> ImportFromExcel<T>(string excelFilePath, string[] fields, string sheetName, int headerRowIndex = 0) where T : class, new()
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = GetOpenFilePath();
            }
            if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            FileStream fs = null;
            IWorkbook workbook = null;
            IList<T> list = null;
            try
            {
                using (fs = File.OpenRead(excelFilePath))
                {
                    bool isComPatible = IsCompatible(excelFilePath);
                    workbook = CreateWorkbook(isComPatible, fs);
                    list = ImportFromExcel<T>(workbook.GetSheet(sheetName), fields, headerRowIndex);
                }
            }
            catch (Exception ex)
            {
                workbook = null;
                if (fs != null) fs.Dispose();
                list = null;
                throw new Exception(ex.Message);
            }
            return list;
        }
        /// <summary>
        /// Excel导入List
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="excelFilePath">excel文件全路径</param>
        /// <param name="fields">实体属性的字符串表示形式的数组，顺序与excel字段保持一致</param>
        /// <param name="sheetIndex">第几个工作表，默认值为1</param>
        /// <param name="headerRowIndex">首行索引，默认0</param>
        /// <returns></returns>
        public static IList<T> ImportFromExcel<T>(string excelFilePath, string[] fields, int sheetIndex = 1, int headerRowIndex = 0) where T : class, new()
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = GetOpenFilePath();
            }
            if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
            FileStream fs = null;
            IWorkbook workbook = null;
            IList<T> list = null;
            try
            {
                using (fs = File.OpenRead(excelFilePath))
                {
                    bool isComPatible = IsCompatible(excelFilePath);
                    workbook = CreateWorkbook(isComPatible, fs);
                    list = ImportFromExcel<T>(workbook.GetSheetAt(sheetIndex - 1), fields, headerRowIndex);
                }
            }
            catch (Exception ex)
            {
                workbook = null;
                if (fs != null) fs.Dispose();
                list = null;
                throw new Exception(ex.Message);
            }
            return list;
        }
        #endregion
        #region 公共转换方法
        /// <summary>
        /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列名，如第0列为A，第1列为B...</returns>
        public static string ConvertColumnIndexToColumnName(int index)
        {
            index++;
            int letterCount = 26;
            char[] array = new char[100];
            int i = 0;
            while (index > 0)
            {
                int mod = index % letterCount;
                if (mod == 0) mod = letterCount;
                array[i++] = (char)(mod - 1 + 'A');
                index = (index - 1) / 26;
            }
            StringBuilder sb = new StringBuilder(i);
            for (int j = i - 1; j >= 0; j--)
            {
                sb.Append(array[j]);
            }
            return sb.ToString();
        }
        /// <summary>
        /// 转化日期
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public static DateTime ConvertDate(object date)
        {
            string dateString = (date ?? "").ToString();
            DateTime dt = new DateTime();
            if (DateTime.TryParse(dateString, out dt))
            {
                return dt;
            }
            try
            {
                string spString = "";
                if (dateString.Contains("-"))
                {
                    spString = "-";
                }
                else if (dateString.Contains("/"))
                {
                    spString = "/";
                }
                string[] time = dateString.Split(spString.ToCharArray());
                int year = Convert.ToInt32(time[2]);
                int month = Convert.ToInt32(time[0]);
                int day = Convert.ToInt32(time[1]);
                string years = Convert.ToString(year);
                string months = Convert.ToString(month);
                string days = Convert.ToString(day);
                if (months.Length == 4)
                {
                    dt = Convert.ToDateTime(date);
                }
                else
                {
                    string rq = "";
                    if (years.Length == 1)
                    {
                        years = "0" + years;
                    }
                    if (months.Length == 1)
                    {
                        months = "0" + months;
                    }
                    if (days.Length == 1)
                    {
                        days = "0" + days;
                    }
                    rq = "20" + years + "-" + months + "-" + days;
                    dt = Convert.ToDateTime(rq);
                }
            }
            catch
            {
                throw new Exception("日期格式不正确，转换日期失败！");
            }
            return dt;
        }
        /// <summary>
        /// 转化数字
        /// </summary>
        /// <param name="d">数字字符串</param>
        /// <returns></returns>
        public static decimal ConvertDecimal(object d)
        {
            string dString = (d ?? "").ToString();
            decimal result = 0m;
            if (decimal.TryParse(dString, out result))
            {
                return result;
            }
            else
            {
                throw new Exception("数字格式不正确,转换数字失败!");
            }
        }
        /// <summary>
        /// 转换布尔
        /// </summary>
        /// <param name="b">布尔值字符串</param>
        /// <returns></returns>
        public static bool ConvertBoolean(object b)
        {
            string bString = (b ?? "").ToString();
            bool result = false;
            if (bool.TryParse(bString, out result))
            {
                return result;
            }
            else if (bString == "0" || bString == "1")
            {
                return (bString == "0");
            }
            else
            {
                throw new Exception("布尔格式不正确，转换布尔类型失败！");
            }
        }
        /// <summary>
        /// 实体类属性类型与Excel字段类型的转换
        /// </summary>
        /// <param name="t"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ValueType(Type t, string value)
        {
            object o = null;
            string strType = t.Name;
            if (strType == "Nullable`1")
            {
                strType = t.GetGenericArguments()[0].Name;
            }
            switch (strType)
            {
                case "Decimal":
                    o = decimal.Parse(value);
                    break;
                case "Int32":
                    o = int.Parse(value);
                    break;
                case "Float":
                    o = float.Parse(value);
                    break;
                case "DateTime":
                    o = DateTime.Parse(value);
                    break;
                case "Char":
                    o = char.Parse(value);
                    break;
                case "Boolean":
                    if (new string[] { "0","1"}.Contains(value))
                    {
                        o = Convert.ToBoolean(int.Parse(value));
                    }
                    else
                    {
                        o = bool.Parse(value);
                    }                   
                    break;
                default:
                    o = value;
                    break;
            }
            return o;
        }
        #endregion

    }
}

 

扩展IRow的GetCell方法

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace RaysUtil.Office.Excel
{
    public static  class NpoiEx
    {
        /// <summary>
        /// 如果当前单元格为空，创建新的字符串类型的单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static ICell GetCellEx(this IRow row,int index)
        {
            return row.FirstOrDefault(n => n.ColumnIndex == index) ?? row.CreateCell(index,CellType.String);
        }
    }
}

 

样式类

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

namespace RaysUtil.Office.Excel
{
    //==============================================================
    //  作者：*
    //  时间：2018/1/6 21:06:18
    //  文件名：CellStyleModel
    //  版本：V1.0.1  
    //  说明： 
    //  修改者：**
    //  修改说明： 分离出样式，单独写一个类
    //==============================================================
    public  class CellStyleModel
    {
        /// <summary>
        /// 是否存在下边框，默认true
        /// </summary>
        public bool BorderStyleB { get; set; } = true;
        /// <summary>
        /// 是否存在左边框，默认true
        /// </summary>
        public bool BorderStyleL { get; set; } = true;
        /// <summary>
        /// 是否存在右边框,默认true
        /// </summary>
        public bool BorderStyleR { get; set; } = true;
        /// <summary>
        /// 是否存在上边框,默认true
        /// </summary>
        public bool BorderStyleT { get; set; } = true;
        /// <summary>
        /// 有边框的样式,默认薄边框
        /// </summary>
        public BorderStyle BorderStyle { get; set; } = BorderStyle.Thin;
        /// <summary>
        /// 背景色
        /// </summary>
        public short ColorIndex { get; set; } = HSSFColor.LightGreen.Index;
        /// <summary>
        /// 是否横向对齐，默认True
        /// </summary>
        public bool IsAlignment { get; set; } = true;
        /// <summary>
        /// 横向对齐,默认横向居中
        /// </summary>
        public HorizontalAlignment HorizontalAlignment { get; set; } = HorizontalAlignment.Center;
        /// <summary>
        /// 垂直对齐，默认垂直居中
        /// </summary>
        public VerticalAlignment VerticalAlignment { get; set; } = VerticalAlignment.Center;
        /// <summary>
        /// 是否设置字体信息,默认False
        /// </summary>
        public bool IsSetFont { get; set; } = false;
        /// <summary>
        /// 字体信息,默认null
        /// </summary>
        public IFont Font { get; set; } = null;
        /// <summary>
        /// 字体大小，默认30
        /// </summary>
        public short FontSize { get; set; } = 30;
    }
}