JW/Framework/Bowin.Common/Utility/NpoiExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Web;
using System.Configuration;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Collections;
using NPOI.XSSF.UserModel;

namespace Bowin.Common.Utility
{
    public class NpoiExcelHelper : IDisposable
    {

        /// excel路径
        /// </summary>
        public string FilePath { get; set; }

        private List<IGrouping<Func<IWorkbook, HSSFCellStyle>, NpoiExcelCellStyle>> _cellStyleListField;
        private List<IGrouping<Func<IWorkbook, HSSFCellStyle>, NpoiExcelCellStyle>> _cellStyleList 
        {
            get
            {
                if (_cellStyleListField == null)
                {
                    _cellStyleListField = new List<IGrouping<Func<IWorkbook, HSSFCellStyle>, NpoiExcelCellStyle>>();
                }
                return _cellStyleListField;
            }
            set
            {
                _cellStyleListField = value;
            }
        }

        public void PresetCellStyle(List<NpoiExcelCellStyle> cellStyleList)
        {
            var styleGroup = cellStyleList.GroupBy(x => x.StyleFunc).ToList();

            this._cellStyleList = styleGroup;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="CloumnList">中文列名列表</param>
        /// <param name="HeaderText">表头</param>
        /// <param name="SavePath">保存路径</param>
        public void Export(DataTable dtSource, string[] CloumnList, string HeaderText)
        {
            string fileName = HeaderText + ".xls";

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            if (HttpContext.Current.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
            }
            else
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            }
            HttpContext.Current.Response.Clear();

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            //默认宽度-高度
            sheet1.DefaultColumnWidth = 1 * 15;
            short color_BLACK = HSSFColor.BLACK.index;

            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;
            //设置背景（根据上面的定义的颜色 进行赋值）
            //style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //style.FillPattern = ICellStyle.SQUARES;
            //style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //居中
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            //设置字体
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = (short)FontBoldWeight.BOLD;//粗体

            style.SetFont(font);

            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(hssfworkbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            if (CloumnList.Length > 0)
            {
                IRow row = sheet1.CreateRow(0);
                for (int i = 0; i < CloumnList.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(CloumnList[i]);
                    var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == -1));
                    if (customCellStyle != null)
                    {
                        cell.CellStyle = customCellStyle.Style;
                    }
                    else
                    {
                        cell.CellStyle = style;
                    }
                }
            }

            if (dtSource.Rows.Count > 0)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    IRow rowsub = sheet1.CreateRow(i + 1);

                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        var cell = rowsub.CreateCell(j);
                        if (dtSource.Rows[i][j] != "" && dtSource.Rows[i][j] != null)
                        {
                            if (dtSource.Rows[i][j].GetType().FullName == "System.Int32")
                            {
                                cell.SetCellValue(Convert.ToInt32(dtSource.Rows[i][j]));//如果是值类型、那么以原格式输出
                            }
                            else
                            {
                                cell.SetCellValue(dtSource.Rows[i][j].ToString());
                            }

                        }
                        else
                        {
                            cell.SetCellValue(dtSource.Rows[i][j] as string);
                        }

                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                        if (customCellStyle != null)
                        {
                            cell.CellStyle = customCellStyle.Style;
                        }
                    }
                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }

        public void ExportTable(string html, string HeaderText = null)
        {
            string fileName = HeaderText + ".xls";

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            if (HttpContext.Current.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
            }
            else
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            }
            HttpContext.Current.Response.Clear();

            string rowContent = string.Empty;
            MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选

            

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ;//创建Workbook对象
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");//创建工作表


            short color_BLACK = HSSFColor.BLACK.index;

            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;
            //设置背景（根据上面的定义的颜色 进行赋值）
            //style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //style.FillPattern = ICellStyle.SQUARES;
            //style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //居中
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            //设置字体
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = (short)FontBoldWeight.BOLD;//粗体

            style.SetFont(font);

            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(hssfworkbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            //写在tr循环中 
            for (int i = 0; i < rowCollection.Count; i++)
            {
                HSSFRow row = (HSSFRow)sheet1.CreateRow(i);
                rowContent = rowCollection[i].Value;

                MatchCollection columnCollection = Regex.Matches(rowCollection[i].Value, @"<td[^>]*>[\s\S]*?<\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选
                //遍历td
                for (int j = 0; j < columnCollection.Count; j++)
                {
                    var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                    var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
                    if (match.Success)
                    {
                        int rowspan = Convert.ToInt32(match.Groups["row"].Value);//表格跨行
                        int colspan = Convert.ToInt32(match.Groups["col"].Value);//表格跨列
                        int rowcount = Convert.ToInt32(match.Groups["row1"].Value);//所在行
                        int col = Convert.ToInt32(match.Groups["col1"].Value);//所在列
                        string value = match.Groups["value"].Value;

                        if (colspan == 1)//判断是否跨列
                        {
                            var cell = row.CreateCell(col);//创建列
                            cell.SetCellValue(value);//设置列的值
                            if (customCellStyle != null)
                            {
                                cell.CellStyle = customCellStyle.Style;
                            }
                            else
                            {
                                cell.CellStyle = style;
                            }

                            if (value.Length > 0)
                            {
                                int width = value.Length * 25 / 6;
                                if (width > 255)
                                    width = 250;
                                sheet1.SetColumnWidth(col, width * 256);
                            }
                        }
                        else if (colspan == 0)
                        {
                            var cell = row.CreateCell(col);//创建列
                            cell.SetCellValue(value);//设置列的值
                            if (customCellStyle != null)
                            {
                                cell.CellStyle = customCellStyle.Style;
                            }
                            else
                            {
                                cell.CellStyle = style;
                            }
                        }
                        //判断是否跨行、跨列
                        if (rowspan > 1 || colspan > 1)
                        {
                            int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0;
                            if (rowspan > 1)//跨行
                            {
                                firstRow = rowcount;
                                lastRow = firstRow + rowspan - 1;
                            }
                            else
                            {
                                firstRow = lastRow = i;
                            }
                            if (colspan > 1)//跨列
                            {
                                firstCol = col;
                                int cols = col + colspan;
                                for (; col < cols; col++)
                                {
                                    var cell = row.CreateCell(col);
                                    cell.SetCellValue(value);
                                    if (customCellStyle != null)
                                    {
                                        cell.CellStyle = customCellStyle.Style;
                                    }
                                    else
                                    {
                                        cell.CellStyle = style;
                                    }
                                }
                                lastCol = col - 1;
                            }
                            else
                            {
                                firstCol = lastCol = col;
                            }
                            //关键是这里，设置起始行数，结束行数；起始列数，结束列数
                            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
                        }
                    }
                }
            }
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }


        public void Export(DataTable dtSource, string fileName = null)
        {
            fileName = (fileName ?? Function.GetGUID()) + ".xls";
            //HttpContext.Current.Response.Headers.Clear();
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlPathEncode(fileName)));
            HttpContext.Current.Response.Clear();

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            //默认宽度-高度
            sheet1.DefaultColumnWidth = 1 * 15;
            short color_BLACK = HSSFColor.BLACK.index;

            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THICK;
            style.BorderLeft = BorderStyle.THICK;
            style.BorderRight = BorderStyle.THICK;
            style.BorderTop = BorderStyle.THICK;
            //设置背景（根据上面的定义的颜色 进行赋值）
            //style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //style.FillPattern = ICellStyle.SQUARES;
            //style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //居中
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            //设置字体
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = (short)FontBoldWeight.BOLD;//粗体
            style.SetFont(font);

            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(hssfworkbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            if (dtSource.Columns.Count > 0)
            {
                IRow row = sheet1.CreateRow(0);
                for (int i = 0; i < dtSource.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dtSource.Columns[i] != null ? dtSource.Columns[i].ToString() : "");
                    var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == 0));
                    if (customCellStyle != null)
                    {
                        cell.CellStyle = customCellStyle.Style;
                    }
                    else
                    {
                        cell.CellStyle = style;
                    }
                }
            }

            if (dtSource.Rows.Count > 0)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    IRow rowsub = sheet1.CreateRow(i + 1);
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        var cell = rowsub.CreateCell(j);

                        if (dtSource.Rows[i][j] != null && dtSource.Rows[i][j] != "")
                        {
                            cell.SetCellValue(dtSource.Rows[i][j].ToString());
                        }
                        else
                        {
                            cell.SetCellValue(dtSource.Rows[i][j] as string);
                        }
                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                        if (customCellStyle != null)
                        {
                            cell.CellStyle = customCellStyle.Style;
                        }
                    }
                }
            }
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }


        public byte[] ExportMemoryStream(DataTable dtSource, string FileName, string[] CloumnList)
        {
            string fileName = FileName + DateTime.Now.ToString("yyyyMMdd") + ".xls";

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            //默认宽度-高度
            sheet1.DefaultColumnWidth = 1 * 15;
            short color_BLACK = HSSFColor.BLACK.index;

            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THICK;
            style.BorderLeft = BorderStyle.THICK;
            style.BorderRight = BorderStyle.THICK;
            style.BorderTop = BorderStyle.THICK;
            //设置背景（根据上面的定义的颜色 进行赋值）
            //style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //style.FillPattern = ICellStyle.SQUARES;
            //style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //居中
            style.Alignment = HorizontalAlignment.CENTER; ;
            style.VerticalAlignment = VerticalAlignment.CENTER; ;
            //设置字体
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = (short)FontBoldWeight.BOLD;//粗体

            style.SetFont(font);

            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(hssfworkbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            if (CloumnList.Length > 0)
            {
                IRow row = sheet1.CreateRow(0);
                for (int i = 0; i < CloumnList.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(CloumnList[i]);
                    var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == 0));
                    if (customCellStyle != null)
                    {
                        cell.CellStyle = customCellStyle.Style;
                    }
                    else
                    {
                        cell.CellStyle = style;
                    }
                }
            }

            if (dtSource.Rows.Count > 0)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    IRow rowsub = sheet1.CreateRow(i + 1);

                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        var cell = rowsub.CreateCell(j);
                        if (dtSource.Rows[i][j] != "" && dtSource.Rows[i][j] != null)
                        {
                            cell.SetCellValue(dtSource.Rows[i][j].ToString());
                        }
                        else
                        {
                            cell.SetCellValue(dtSource.Rows[i][j] as string);
                        }
                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                        if (customCellStyle != null)
                        {
                            cell.CellStyle = customCellStyle.Style;
                        }
                    }
                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            byte[] b = file.ToArray();
            hssfworkbook = null;
            file.Close();
            file.Dispose();
            return b;

            //HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            //HttpContext.Current.Response.End();

        }

        /// <summary>
        /// 将指定路径的文件，写入table中，不带列标题，返回该DataTable
        /// </summary>
        /// <param name="FilePath">导入数据文件路径</param>
        /// <returns></returns>
        public DataTable Import(string FilePath)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook wk = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
            ISheet sheet = wk.GetSheetAt(0);

            int rowCount = sheet.PhysicalNumberOfRows;
            int columnCount = sheet.GetRow(0).PhysicalNumberOfCells;
            try
            {
                for (int i = 0; i < columnCount; i++)
                {
                    dt.Columns.Add(i.ToString());
                }
                for (int i = 0; i < rowCount; i++)
                {
                    DataRow row = dt.NewRow();
                    for (int y = 0; y < columnCount; y++)
                    {
                        row[y] = sheet.GetRow(i).GetCell(y);
                    }
                    dt.Rows.Add(row);
                }
            }
            catch (Exception e)
            { }
            sheet = null;
            wk = null;
            return dt;
        }

        /// <summary>
        /// 将指定路径的文件，写入table中，带列标题，返回该DataTable
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public DataTable ImpotColumns(string FilePath)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook wk = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
            ISheet sheet = wk.GetSheetAt(0);

            int rowCount = sheet.PhysicalNumberOfRows;
            int columnCount = sheet.GetRow(0).PhysicalNumberOfCells;
            try
            {
                for (int i = 0; i < columnCount; i++)
                {
                    DataColumn dtColumn = new DataColumn(sheet.GetRow(0).GetCell(i).StringCellValue);
                    dt.Columns.Add(dtColumn);
                    // dt.Columns.Add(i.ToString());
                }
                for (int i = 1; i < rowCount; i++)
                {
                    DataRow row = dt.NewRow();
                    for (int y = 0; y < columnCount; y++)
                    {
                        row[y] = sheet.GetRow(i).GetCell(y);
                    }
                    dt.Rows.Add(row);
                }
            }
            catch (Exception e)
            { }

            sheet = null;
            wk = null;
            return dt;
        }

        /// <summary>
        /// 将指定路径的文件，写入DataSet中，带列标题，返回该DataSet
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public DataSet ImpotAllExcel(string FilePath)
        {
            var ds = new DataSet();
            HSSFWorkbook wk = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));

            for (int sheetIndex = 0; sheetIndex < wk.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet = wk.GetSheetAt(sheetIndex);
                DataTable dt = new DataTable(sheet.SheetName);

                int rowCount = sheet.PhysicalNumberOfRows;
                int columnCount = sheet.GetRow(0).PhysicalNumberOfCells;
                try
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        DataColumn dtColumn = new DataColumn(sheet.GetRow(0).GetCell(i).StringCellValue);
                        dt.Columns.Add(dtColumn);
                    }

                    for (int i = 1; i < rowCount; i++)
                    {
                        var row = sheet.GetRow(i);
                        DataRow drRow = dt.NewRow();

                        for (int y = 0; y < columnCount; y++)
                        {
                            drRow[y] = row.GetCell(y);
                        }

                        dt.Rows.Add(drRow);
                    }
                }
                catch (Exception e)
                { }
                sheet = null;

                ds.Tables.Add(dt);
            }

            wk = null;

            return ds;
        }

        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="path">路径</param>
        public void SaveAs(Stream fileStream, string filePath)
        {
            FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.None);
            int bufferLen = 4096;
            byte[] buffer = new byte[bufferLen];

            int count = 0;
            while ((count = fileStream.Read(buffer, 0, bufferLen)) > 0)
            {
                stream.Write(buffer, 0, count);
            }
            stream.Close();
            fileStream.Close();
            stream.Dispose();
            fileStream.Dispose();
        }

        /// <summary>
        /// 创建一个包含多个列名的excel，返回创建的文件路径
        /// </summary>
        /// <param name="Columns">列名</param>
        /// <returns></returns>
        public string CreateFile(string[] Columns)
        {
            string path = HttpContext.Current.Server.MapPath(Function.GetTempFile()) + Function.GetGUID() + ".xls";
            try
            {
                HSSFWorkbook wk = new HSSFWorkbook();
                ISheet sheet = wk.CreateSheet("sheet1");
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < Columns.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(Columns[i]);
                }

                FileStream stream = new FileStream(path, FileMode.Create);
                wk.Write(stream);
                stream.Dispose();
                wk = null;
                return path;
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 导出数据到服务器
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="filpath">保存路径</param>
        public void SaveInServer(DataTable dtSource, string filpath)
        {
            //HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            //HttpContext.Current.Response.Clear();

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            //默认宽度-高度
            sheet1.DefaultColumnWidth = 1 * 15;
            short color_BLACK = HSSFColor.BLACK.index;

            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THICK;
            style.BorderLeft = BorderStyle.THICK;
            style.BorderRight = BorderStyle.THICK;
            style.BorderTop = BorderStyle.THICK;

            style.Alignment = HorizontalAlignment.CENTER; ;
            style.VerticalAlignment = VerticalAlignment.CENTER; ;
            //设置字体
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            font.Boldweight = (short)FontBoldWeight.BOLD;//粗体
            style.SetFont(font);

            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(hssfworkbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            if (dtSource.Columns.Count > 0)
            {
                IRow row = sheet1.CreateRow(0);
                for (int i = 0; i < dtSource.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dtSource.Columns[i] != null ? dtSource.Columns[i].ToString() : "");
                    var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == 0));
                    if (customCellStyle != null)
                    {
                        cell.CellStyle = customCellStyle.Style;
                    }
                    else
                    {
                        cell.CellStyle = style;
                    }
                }
            }
            if (dtSource.Rows.Count > 0)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    IRow rowsub = sheet1.CreateRow(i + 1);
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        var cell = rowsub.CreateCell(j);
                        if (dtSource.Rows[i][j] != null && dtSource.Rows[i][j] != "")
                        {
                            cell.SetCellValue(dtSource.Rows[i][j].ToString());
                        }
                        else
                        {
                            cell.SetCellValue(dtSource.Rows[i][j] as string);
                        }
                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                        if (customCellStyle != null)
                        {
                            cell.CellStyle = customCellStyle.Style;
                        }
                    }
                }
            }
            FileStream fs = new FileStream(filpath, FileMode.Create);
            hssfworkbook.Write(fs);
            fs.Dispose();
            hssfworkbook = null;
        }

        public void SaveInServer(DataSet dsSource, string filpath)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            foreach (DataTable dtSource in dsSource.Tables)
            {
                ISheet sheet1 = hssfworkbook.CreateSheet(dtSource.TableName);
                //默认宽度-高度
                sheet1.DefaultColumnWidth = 1 * 15;
                short color_BLACK = HSSFColor.BLACK.index;

                ICellStyle style = hssfworkbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.THICK;
                style.BorderLeft = BorderStyle.THICK;
                style.BorderRight = BorderStyle.THICK;
                style.BorderTop = BorderStyle.THICK;

                style.Alignment = HorizontalAlignment.CENTER; ;
                style.VerticalAlignment = VerticalAlignment.CENTER; ;
                //设置字体
                IFont font = hssfworkbook.CreateFont();
                font.FontHeightInPoints = 12;//字号
                font.Boldweight = (short)FontBoldWeight.BOLD;//粗体
                style.SetFont(font);

                var customerStyleGroupList = _cellStyleList
                    .Select(x => new { Style = x.Key.Invoke(hssfworkbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                    .ToList();

                if (dtSource.Columns.Count > 0)
                {
                    IRow row = sheet1.CreateRow(0);
                    for (int i = 0; i < dtSource.Columns.Count; i++)
                    {
                        ICell cell = row.CreateCell(i);
                        cell.SetCellValue(dtSource.Columns[i] != null ? dtSource.Columns[i].ToString() : "");
                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == 0));
                        if (customCellStyle != null)
                        {
                            cell.CellStyle = customCellStyle.Style;
                        }
                        else
                        {
                            cell.CellStyle = style;
                        }
                    }
                }
                if (dtSource.Rows.Count > 0)
                {
                    for (int i = 0; i < dtSource.Rows.Count; i++)
                    {
                        IRow rowsub = sheet1.CreateRow(i + 1);
                        for (int j = 0; j < dtSource.Columns.Count; j++)
                        {
                            var cell = rowsub.CreateCell(j);
                            if (dtSource.Rows[i][j] != null && dtSource.Rows[i][j] != "")
                            {
                                cell.SetCellValue(dtSource.Rows[i][j].ToString());
                            }
                            else
                            {
                                cell.SetCellValue(dtSource.Rows[i][j] as string);
                            }
                            var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                            if (customCellStyle != null)
                            {
                                cell.CellStyle = customCellStyle.Style;
                            }
                        }
                    }
                }
            }

            FileStream fs = new FileStream(filpath, FileMode.Create);
            hssfworkbook.Write(fs);
            fs.Dispose();

            hssfworkbook = null;
        }
        public static DataTable ToDataTable<T>(List<T> items)
        {

            DataTable dataTable = new DataTable(typeof(T).Name);

            //Get all the properties

            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in Props)
            {
                if (GetDefault(prop.PropertyType.FullName) != null)
                {
                    //Setting column names as Property names
                    dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                }
            }

            foreach (T item in items)
            {
                if (item != null)
                {
                    DataRow dr = dataTable.NewRow();

                    foreach (PropertyInfo prop in Props)
                    {
                        if (GetDefault(prop.PropertyType.FullName) != null)
                        {
                            //inserting property values to datatable rows
                            dr[prop.Name] = prop.GetValue(item, null) ?? GetDefault(prop.PropertyType.FullName);
                        }
                    }

                    dataTable.Rows.Add(dr);
                }
            }

            //put a breakpoint here and check datatable

            return dataTable;

        }

        public static object GetDefault(string dataType)
        {

            if (dataType.Contains("System.String"))
            {
                return string.Empty;
            }
            if (dataType.Contains("System.Boolean"))
            {
                return false;
            }
            if (dataType.Contains("System.Decimal"))
            {
                return 0.0;
            }
            if (dataType.Contains("System.DateTime"))
            {
                return DateTime.MinValue;
            }
            if (dataType.Contains("System.Int64"))
            {
                return 0;
            }
            if (dataType.Contains("System.Guid"))
            {
                return null;
            }
            if (dataType.Contains("System.Int16"))
            {
                return 0;
            }
            if (dataType.Contains("Int32"))
            {
                return 0;

            }
            if (dataType.Contains("System.Object"))
            {
                return null;
            }

            return null;
        }


        public string Path { get; set; }
        internal HSSFWorkbook Wookbook { get; set; }
        internal ISheet Sheet { get; set; }

        /// <summary>
        /// 创建一个包含多个列名的excel，返回创建的文件路径
        /// </summary>
        /// <param name="Columns">列名</param>
        /// <returns></returns>
        public static NpoiExcelHelper Create(string[] Columns)
        {
            NpoiExcelHelper excelHelper = new NpoiExcelHelper();
            excelHelper.Path = HttpContext.Current.Server.MapPath(Function.GetTempFile()) + Function.GetGUID() + ".xls";
            try
            {
                excelHelper.Wookbook = new HSSFWorkbook();
                excelHelper.Sheet = excelHelper.Wookbook.CreateSheet("sheet1");
                IRow row = excelHelper.Sheet.CreateRow(0);
                for (int i = 0; i < Columns.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(Columns[i]);
                }

                return excelHelper;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public ICellStyle CreateStyle()
        {
            return Wookbook.CreateCellStyle();
        }

        public void SetValue(DataTable dtSource, ICellStyle headerStyle = null, ICellStyle cellStyle = null)
        {
            var customerStyleGroupList = _cellStyleList
                .Select(x => new { Style = x.Key.Invoke(Wookbook), CellList = x.Select(w => new { w.RowIndex, w.ColumnIndex }).ToList() })
                .ToList();

            if (dtSource.Columns.Count > 0)
            {
                IRow row = Sheet.CreateRow(0);
                for (int i = 0; i < dtSource.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dtSource.Columns[i] != null ? dtSource.Columns[i].ToString() : "");

                    var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == i && w.RowIndex == 0));
                    if (customCellStyle != null)
                    {
                        cell.CellStyle = customCellStyle.Style;
                    }
                    if (headerStyle != null)
                    {
                        cell.CellStyle = headerStyle;
                    }
                }
            }
            if (dtSource.Rows.Count > 0)
            {
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    IRow rowsub = Sheet.CreateRow(i + 1);
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        ICell cell;
                        cell = rowsub.CreateCell(j);
                        if (dtSource.Rows[i][j] != null && dtSource.Rows[i][j] != "")
                        {
                            cell.SetCellValue(dtSource.Rows[i][j].ToString());
                        }
                        else
                        {
                            cell.SetCellValue(dtSource.Rows[i][j] as string);
                        }
                        var customCellStyle = customerStyleGroupList.FirstOrDefault(x => x.CellList.Any(w => w.ColumnIndex == j && w.RowIndex == i));
                        if (customCellStyle != null)
                        {
                            cell.CellStyle = customCellStyle.Style;
                        }
                        if (cellStyle != null)
                        {
                            cell.CellStyle = cellStyle;
                        }
                    }
                }
            }
        }

        public void MergeColumn(int firstRow, int lastRow, int firstColumn, int lastColumn)
        {
            Sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
        }

        public void DrawBorder(int firstRow, int lastRow, int firstColumn, int lastColumn)
        {
            var cellRange = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
            for (int i = 0; i <= (lastRow - firstRow); i++)
            {
                var row = Sheet.GetRow(i + firstRow);
                for (int j = 0; j <= (lastColumn - firstColumn); j++)
                {
                    var cell = row.GetCell(j + firstColumn);
                    cell.CellStyle.BorderTop = BorderStyle.THICK;
                    cell.CellStyle.BorderBottom = BorderStyle.THICK;
                    cell.CellStyle.BorderLeft = BorderStyle.THICK;
                    cell.CellStyle.BorderRight = BorderStyle.THICK;
                }
            }
        }

        public void Save()
        {
            try
            {
                FileStream stream = new FileStream(Path, FileMode.Create);
                Wookbook.Write(stream);
                stream.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Dispose()
        {
            Path = null;
            Sheet = null;
            Wookbook = null;
        }

        #region 从Excel取数据并记录到List集合里

        /// <summary>
        /// 从Excel取数据并记录到List集合里
        /// </summary>
        /// <param name="cellHeard">单元头的Key和Value：{ { "Author", "作者" }, { "Price", "单价" } };</param>
        /// <param name="filePath">保存文件绝对路径</param>
        /// <param name="errorMsg">错误信息</param>
        /// <returns>转换后的List对象集合</returns>
        public static List<T> ExcelToEntityList<T>(Dictionary<string, string> cellHeard, string filePath, out StringBuilder errorMsg, out List<T> errList) where T : new()
        {
            List<T> enlist = new List<T>();
            errorMsg = new StringBuilder();
            errList = new List<T>();
            try
            {
                if (Regex.IsMatch(filePath, ".xls$")) // 2003
                {
                    enlist = Excel2003ToEntityList<T>(cellHeard, filePath, out errorMsg, out errList);
                }
                else if (Regex.IsMatch(filePath, ".xlsx$")) // 2007
                {
                    enlist = Excel2007ToEntityList<T>(cellHeard, filePath, out errorMsg, out errList);
                }
                return enlist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 从Excel2003取数据并记录到List集合里
        /// <summary>
        /// 从Excel2003取数据并记录到List集合里
        /// </summary>
        /// <param name="cellHeard">单元头的Key和Value：{ { "Author", "作者" }, { "Price", "单价" } };</param>
        /// <param name="filePath">保存文件绝对路径</param>
        /// <param name="errorMsg">错误信息</param>
        /// <returns>转换好的List对象集合</returns>
        private static List<T> Excel2003ToEntityList<T>(Dictionary<string, string> cellHeard, string filePath, out StringBuilder errorMsg, out List<T> errList) where T : new()
        {
            try
            {
                using (FileStream fs = File.OpenRead(filePath))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);
                    HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页

                    return ExcelToEntityList(cellHeard, sheet, out errorMsg, out errList);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 从Excel2007取数据并记录到List集合里
        /// <summary>
        /// 从Excel2007取数据并记录到List集合里
        /// </summary>
        /// <param name="cellHeard">单元头的Key和Value：{ { "Author", "作者" }, { "Price", "单价" } };</param>
        /// <param name="filePath">保存文件绝对路径</param>
        /// <param name="errorMsg">错误信息</param>
        /// <returns>转换好的List对象集合</returns>
        private static List<T> Excel2007ToEntityList<T>(Dictionary<string, string> cellHeard, string filePath, out StringBuilder errorMsg, out List<T> errList) where T : new()
        {
            try
            {
                using (FileStream fs = File.OpenRead(filePath))
                {
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);
                    XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页

                    return ExcelToEntityList(cellHeard, sheet, out errorMsg, out errList);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        private static List<T> ExcelToEntityList<T>(Dictionary<string, string> cellHeard, ISheet sheet, out StringBuilder errorMsg, out List<T> errList) where T : new()
        {
            errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时，会有格式的错误信息
            errList = new List<T>();//将错误的数据记录在集合中
            List<T> enlist = new List<T>(); // 转换后的集合
            List<string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称
            List<string> vals = cellHeard.Values.ToList(); // 属性名称
            IRow row = sheet.GetRow(0);

            for (int i = 0; i < vals.Count; i++)
            {
                //string cellValer = row.Cells[i].ToString();
                if (!row.Cells.Select(x => x.StringCellValue).Contains(vals[i]))//判断Excel列名是否在单元头中存在
                {
                    throw new Exception("Excel导入列信息不完整。");
                }
            }

            for (int i = 1; i <= sheet.LastRowNum; i++) // 从1开始，第0行为单元头
            {
                // 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
                if (sheet.GetRow(i) == null)
                {
                    continue;
                }
                if (sheet.GetRow(i).Cells.Count == 0)
                {
                    continue;
                }
                if (sheet.GetRow(i) != null && string.IsNullOrWhiteSpace(sheet.GetRow(i).Cells.FirstOrDefault().ToString()))//TODO：针对于Excel清除内容读取的空白行做调整
                {
                    continue;
                }

                T en = new T();
                string errStr = ""; // 当前行转换时，是否有错误信息，格式为：第1行数据转换异常：XXX列；
                for (int j = 0; j < keys.Count; j++)
                {
                    // 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName
                    if (keys[j].IndexOf(".") >= 0)
                    {
                        // 2.1解析子类属性
                        string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                        string subClassName = properotyArray[0]; // '.'前面的为子类的名称
                        string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
                        System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
                        if (subClassInfo != null)
                        {
                            // 2.1.1 获取子类的实例
                            var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
                            // 2.1.2 根据属性名称获取子类里的属性信息
                            System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
                            if (properotyInfo != null)
                            {
                                try
                                {
                                    // Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
                                    properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
                                }
                                catch (Exception e)
                                {
                                    if (errStr.Length == 0)
                                    {
                                        errStr = "第" + i + "行数据转换异常：";
                                    }
                                    errStr += cellHeard[keys[j]] + "列；";
                                    errList.Add(en);//将错误的数据记录起来
                                }

                            }
                        }
                    }
                    else
                    {
                        // 3.给指定的属性赋值
                        System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]);
                        if (properotyInfo != null)
                        {
                            try
                            {
                                // Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
                                properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
                            }
                            catch (Exception e)
                            {
                                if (errStr.Length == 0)
                                {
                                    errStr = "第" + i + "行数据转换异常：";
                                }
                                errStr += cellHeard[keys[j]] + "列；";
                                errList.Add(en);//将错误的数据记录起来
                            }
                        }
                    }
                }
                // 若有错误信息，就添加到错误信息里
                if (errStr.Length > 0)
                {
                    errorMsg.AppendLine(errStr);
                }
                enlist.Add(en);
            }

            return enlist;
        }


        #region 从Excel获取值传递到对象的属性里
        /// <summary>
        /// 从Excel获取值传递到对象的属性里
        /// </summary>
        /// <param name="distanceType">目标对象类型</param>
        /// <param name="sourceCell">对象属性的值</param>
        private static Object GetExcelCellToProperty(Type distanceType, ICell sourceCell)
        {
            object rs = distanceType.IsValueType ? Activator.CreateInstance(distanceType) : null;

            // 1.判断传递的单元格是否为空
            if (sourceCell == null || string.IsNullOrEmpty(sourceCell.ToString()))
            {
                return rs;
            }

            // 2.Excel文本和数字单元格转换，在Excel里文本和数字是不能进行转换，所以这里预先存值
            object sourceValue = null;
            switch (sourceCell.CellType)
            {
                case CellType.BLANK:
                    break;

                case CellType.BOOLEAN: sourceValue = sourceCell.BooleanCellValue;
                    break;

                case CellType.ERROR: sourceValue = sourceCell.ErrorCellValue;
                    break;

                case CellType.FORMULA: sourceValue = sourceCell.CellFormula;
                    break;

                case CellType.NUMERIC: sourceValue = sourceCell.NumericCellValue;
                    break;

                case CellType.STRING: sourceValue = sourceCell.StringCellValue;
                    break;

                case CellType.Unknown:
                    break;

                default:
                    break;
            }

            string valueDataType = distanceType.Name;

            // 在这里进行特定类型的处理
            switch (valueDataType.ToLower()) // 以防出错，全部小写
            {
                case "string":
                    rs = sourceValue.ToString();
                    break;
                case "int":
                case "int16":
                case "int32":
                    rs = (int)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
                    break;
                case "float":

                case "nullable`1": //由于ChangeType无法强制转换可空类型  所以对单价为空的数据做处理
                    rs = (decimal?)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), (Nullable.GetUnderlyingType(distanceType) ?? distanceType));
                    break;
                case "single":
                    rs = (float)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
                    break;
                case "datetime":
                    rs = sourceCell.DateCellValue;
                    break;
                case "guid":
                    rs = (Guid)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
                    return rs;
            }
            return rs;
        }
        #endregion

        #region Excel导出




        /// <summary>
        /// 实体类集合导出到EXCLE2003
        /// </summary>
        /// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
        /// <param name="enList">数据源</param>
        /// <param name="sheetName">工作表名称</param>
        /// <returns>文件的下载地址</returns>

        private static string _ImportFileVirtualFolder;
        /// <summary>
        /// 导入文件的储存地址
        /// </summary>
        public static string ImportFileVirtualFolder
        {
            get
            {
                if (string.IsNullOrEmpty(_ImportFileVirtualFolder))
                    _ImportFileVirtualFolder = GetAppSettingValue<string>("ImportFileVirtualFolder");
                return _ImportFileVirtualFolder;
            }
        }

        public static string EntityListToExcel2003(Dictionary<string, string> cellHeard, IList enList, string sheetName, string filestr, List<NpoiExcelCellStyle> cellStyleList = null)
        {
            try
            {
                if (cellStyleList == null)
                {
                    cellStyleList = new List<NpoiExcelCellStyle>();
                }
                string fileName = Guid.NewGuid() + ".xls"; // 文件名称
                string urlPath = string.Format("{0}{1}/{2}", ImportFileVirtualFolder, DateTime.Now.ToString("yyyy-MM-dd"), fileName); // 文件下载的URL地址，供给前台下载
                string filePath = HttpContext.Current.Server.MapPath(urlPath); // 文件路径

                // 1.检测是否存在文件夹，若不存在就建立个文件夹   
                filePath = HttpContext.Current.Server.MapPath(string.Format("{0}//{1}//{2}", ImportFileVirtualFolder, DateTime.Now.ToString("yyyy-MM-dd"), fileName));
                string directoryName = System.IO.Path.GetDirectoryName(filePath);// System.IO.Path.GetDirectoryName(filePath);
                if (!Directory.Exists(directoryName))
                {
                    Directory.CreateDirectory(directoryName);
                }

                // 2.解析单元格头部，设置单元头的中文名称
                IWorkbook workbook = new HSSFWorkbook(); // 工作簿
                ISheet sheet = workbook.CreateSheet(sheetName); // 工作表
                IRow row = sheet.CreateRow(0);
                List<string> keys = cellHeard.Keys.ToList();
                ICellStyle style = workbook.CreateCellStyle();//设置行样式；
                ICellStyle cellStyle = setCellStyle(workbook);//设置行样式；
                IFont font = workbook.CreateFont();//设置字体样式
                font.Color = HSSFColor.OLIVE_GREEN.RED.index;

                //设置字体加粗样式
                font.Boldweight = short.MaxValue;
                style.SetFont(font);
                for (int i = 0; i < keys.Count; i++)
                {
                    var cell = row.CreateCell(i);
                    cell.SetCellValue(cellHeard[keys[i]]); // 列名为Key的值
                    row.RowStyle = style;
                    var customCellStyle = cellStyleList.FirstOrDefault(x => x.ColumnIndex == i && x.RowIndex == 0);
                    if (customCellStyle != null)
                    {
                        cell.CellStyle = customCellStyle.StyleFunc.Invoke(workbook);
                    }
                }

                // 3.List对象的值赋值到Excel的单元格里
                int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头)
                foreach (var en in enList)
                {
                    IRow rowTmp = sheet.CreateRow(rowIndex);
                    for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称，获取对象指定属性的值
                    {
                        string cellValue = ""; // 单元格的值
                        object properotyValue = null; // 属性的值
                        System.Reflection.PropertyInfo properotyInfo = null; // 属性的信息

                        // 3.1 若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.UserName
                        if (keys[i].IndexOf(".") >= 0)
                        {
                            // 3.1.1 解析子类属性(这里只解析1层子类，多层子类未处理)
                            string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                            string subClassName = properotyArray[0]; // '.'前面的为子类的名称
                            string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
                            System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
                            if (subClassInfo != null)
                            {
                                // 3.1.2 获取子类的实例
                                var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
                                // 3.1.3 根据属性名称获取子类里的属性类型
                                properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
                                if (properotyInfo != null)
                                {
                                    properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值
                                }
                            }
                        }
                        else
                        {
                            // 3.2 若不是子类的属性，直接根据属性名称获取对象对应的属性
                            properotyInfo = en.GetType().GetProperty(keys[i]);
                            if (properotyInfo != null)
                            {
                                properotyValue = properotyInfo.GetValue(en, null);
                            }
                        }

                        // 3.3 属性值经过转换赋值给单元格值
                        if (properotyValue != null)
                        {
                            cellValue = properotyValue.ToString();
                            // 3.3.1 对时间初始值赋值为空
                            if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
                            {
                                cellValue = "";
                            }
                        }

                        // 3.4 填充到Excel的单元格里
                        // rowTmp.CreateCell(i).SetCellValue(cellValue);
                        ICell cell1 = rowTmp.CreateCell(i);
                        cell1.SetCellValue(cellValue);

                        var customCellStyle = cellStyleList.FirstOrDefault(x => x.ColumnIndex == i && x.RowIndex == rowIndex);
                        if (customCellStyle != null)
                        {
                            cell1.CellStyle = customCellStyle.StyleFunc.Invoke(workbook);
                        }
                        else if (keys[i].Equals("ErrorMessage"))//异常信息标红
                        {
                            cell1.CellStyle = cellStyle;
                        }

                    }
                    rowIndex++;
                }

                // 4.生成文件
                FileStream file = new FileStream(filePath, FileMode.Create);
                workbook.Write(file);
                file.Close();

                // 5.返回下载路径
                return urlPath;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //设置单元格字体颜色
        private static HSSFCellStyle setCellStyle(IWorkbook workbook)
        {
            HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFFont ffont = (HSSFFont)workbook.CreateFont();
            //ffont.FontHeight = 20 * 20;
            //ffont.FontName = "宋体";
            ffont.Color = HSSFColor.RED.index;
            fCellStyle.SetFont(ffont);

            return fCellStyle;
        }

        #endregion Excel导出

        #region 获取配置信息

        private static AppSettingsReader _reader;
        private static AppSettingsReader Reader { get { return _reader ?? (_reader = new AppSettingsReader()); } }

        /// <summary>
        /// 从appSettings节中读取相应键值
        /// </summary>
        public static T GetAppSettingValue<T>(string key)
        {
            T result = default(T);
            object value = Reader.GetValue(key, typeof(T));
            if (value != null)
            {
                result = (T)value;
            }
            return result;
        }
        #endregion

        #region 判断是否为兼容 { ".xls", ".xlsx" }
        /// <summary>
        /// 判断是否为兼容 { ".xls", ".xlsx" }
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool GetIsCompatible(string filePath)
        {
            string ext = System.IO.Path.GetExtension(filePath);
            return new[] { ".xls", ".xlsx" }.Count(e => e.Equals(ext, StringComparison.OrdinalIgnoreCase)) > 0;
        }

        #endregion

    }

    public class NpoiExcelCellStyle
    {
        /// <summary>
        /// 应用样式的列序号，从0开始
        /// </summary>
        public int ColumnIndex { get; set; }
        /// <summary>
        /// 应用样式的行序号，从0开始
        /// </summary>
        public int RowIndex { get; set; }

        public Func<IWorkbook, HSSFCellStyle> StyleFunc { get; set; }
    }
}