Winsoft.GOV/Winsoft.GOV.ImportDataForm/ExcelHelper.cs

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

namespace Winsoft.GOV.ImportDataForm
{
    public class ExcelHelper
    {
        public delegate T GetRowEvent<T>(IRow row);
        public delegate void WriteRowEvent<T>(IRow row, T obj);
        public delegate void WriteHeardEvent(IRow row);

        public static void WriteExcel<T>(IList<T> list, string filePath, WriteHeardEvent wh, WriteRowEvent<T> wr)
        {
            if (!string.IsNullOrEmpty(filePath) && null != list && list.Count > 0)
            {
                IWorkbook book;
 
                if (Path.GetExtension(filePath) == ".xls")
                {
                    book = new HSSFWorkbook();
                }
                else
                {
                    book = new XSSFWorkbook();
                }
                ISheet sheet = book.CreateSheet(Path.GetFileNameWithoutExtension(filePath));
                wh(sheet.CreateRow(0));
                int i = 1;
                foreach(T obj in list)
                {
                    wr(sheet.CreateRow(i), obj);
                    i++;
                }
                // 写入到客户端  
                using (MemoryStream ms = new MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
        }
        public static IList<T> ExcelToList<T>(string excelPath, GetRowEvent<T> f)
        {
            return ExcelToList(excelPath, true, f);
        }

        public static IList<T> ExcelToList<T>(string excelPath, bool firstRowAsHeader, GetRowEvent<T> f)
        {
            int sheetCount;
            return ExcelToList(excelPath, firstRowAsHeader, out sheetCount, f);
        }

        public static IList<T> ExcelToList<T>(string excelPath, bool firstRowAsHeader, out int sheetCount, GetRowEvent<T> f)
        {
            IList<T> list = new List<T>();
            using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                IFormulaEvaluator evaluator;
                if (Path.GetExtension(excelPath) == ".xls")
                {
                    workbook = new HSSFWorkbook(fileStream);
                    evaluator = new HSSFFormulaEvaluator(workbook);
                }
                else
                {
                    workbook = new XSSFWorkbook(fileStream);
                    evaluator = new XSSFFormulaEvaluator(workbook);
                }
                sheetCount = workbook.NumberOfSheets;
                
                for (int i = 0; i < sheetCount; ++i)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    ExcelToList(firstRowAsHeader, sheet, evaluator, ref list, f);
                }       

                return list;
            }
        }

        private static void ExcelToList<T>(bool firstRowAsHeader, ISheet sheet, IFormulaEvaluator evaluator, ref IList<T> list, GetRowEvent<T> f)
        {
            if (sheet.LastRowNum == 0)
                return;
            int i = sheet.FirstRowNum;
            if (firstRowAsHeader)
                i++;
            for (; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    return;
                T obj = f(row);
                if (obj == null)
                    continue;
                list.Add(obj);         
            }            
        }

        /// <summary>
        /// Excel读取到DataSet
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string excelPath)
        {
            return ExcelToDataSet(excelPath, true);
        }

        /// <summary>
        /// Excel读取到DataSet
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="firstRowAsHeader"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
        {
            int sheetCount;
            return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
        }

        /// <summary>
        /// Excel读取到DataSet
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="firstRowAsHeader"></param>
        /// <param name="sheetCount"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader, out int sheetCount)
        {
            using (DataSet ds = new DataSet())
            {
                using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook;
                    IFormulaEvaluator evaluator;
                    if (Path.GetExtension(excelPath) == ".xls")
                    {
                        workbook = new HSSFWorkbook(fileStream);
                        evaluator = new HSSFFormulaEvaluator(workbook);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fileStream);
                        evaluator = new XSSFFormulaEvaluator(workbook);
                    }

                    sheetCount = workbook.NumberOfSheets;

                    for (int i = 0; i < sheetCount; ++i)
                    {
                        ISheet sheet = workbook.GetSheetAt(i);
                        DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
                        ds.Tables.Add(dt);
                    }

                    return ds;
                }
            }
        }
        /// <summary>
        /// Excel读取到DataTable
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string excelPath, string sheetName)
        {
            return ExcelToDataTable(excelPath, sheetName, true);
        }
        /// <summary>
        /// Excel读取到DataTable
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <param name="firstRowAsHeader"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
        {
            using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                IFormulaEvaluator evaluator;
                if (Path.GetExtension(excelPath) == ".xls")
                {
                    workbook = new HSSFWorkbook(fileStream);
                    evaluator = new HSSFFormulaEvaluator(workbook);
                }
                else
                {
                    workbook = new XSSFWorkbook(fileStream);
                    evaluator = new XSSFFormulaEvaluator(workbook);
                }

                ISheet sheet = workbook.GetSheet(sheetName) as ISheet;

                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
            }
        }

        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
        {
            if (firstRowAsHeader)
            {
                return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
            }
            else
            {
                return ExcelToDataTable(sheet, evaluator);
            }
        }

        private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = GetCellCount(sheet);

                for (int i = 0; i < cellCount; i++)
                {
                    if (firstRow.GetCell(i) != null)
                    {
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    FillDataRowByHSSFRow(row, evaluator, ref dr);
                    dt.Rows.Add(dr);
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }

        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    int cellCount = GetCellCount(sheet);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dt.Columns.Add(string.Format("F{0}", i), typeof(string));
                    }

                    for (int i = 0; i < sheet.FirstRowNum; ++i)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }

                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i) as IRow;
                        DataRow dr = dt.NewRow();
                        FillDataRowByHSSFRow(row, evaluator, ref dr);
                        dt.Rows.Add(dr);
                    }
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }

        private static void FillDataRowByHSSFRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
        {
            if (row != null)
            {
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    ICell cell = row.GetCell(j) as ICell;

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Blank:
                                dr[j] = DBNull.Value;
                                break;
                            case CellType.Boolean:
                                dr[j] = cell.BooleanCellValue;
                                break;
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                                break;
                            case CellType.String:
                                dr[j] = cell.StringCellValue;
                                break;
                            case CellType.Error:
                                dr[j] = cell.ErrorCellValue;
                                break;
                            case CellType.Formula:
                                cell = evaluator.EvaluateInCell(cell) as ICell;
                                dr[j] = cell.ToString();
                                break;
                            default:
                                throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
                        }
                    }
                }
            }
        }

        private static int GetCellCount(ISheet sheet)
        {
            int firstRowNum = sheet.FirstRowNum;

            int cellCount = 0;

            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i) as IRow;

                if (row != null && row.LastCellNum > cellCount)
                {
                    cellCount = row.LastCellNum;
                }
            }

            return cellCount;
        }
    }

    public class ExcelHelper<T> where T : new()
    {


        #region Entity To Excel
        /// <summary>
        /// 返回 Excel导出配置对象
        /// </summary>
        /// <returns></returns>
        public static ExcelExportSetting<T> CreateExportInstance()
        {
            return new ExcelExportSetting<T>();
        }

        #endregion



    }













    public class ExcelExportSetting<T>
    {
        string fileName = "导出Excel";
        /// <summary>
        /// Excel标题
        /// </summary>
        public string FileName
        {
            get { return fileName; }
            set { fileName = value; }
        }


        string title = String.Empty;
        /// <summary>
        /// Excel标题
        /// </summary>
        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        string description = String.Empty;
        /// <summary>
        /// Excel描述
        /// </summary>
        public string Description
        {
            get { return description; }
            set { description = value; }
        }


        private IEnumerable<T> source;
        /// <summary>
        /// 数据源
        /// </summary>
        public IEnumerable<T> Source
        {
            get { return source; }
            set { source = value; }
        }

        IEnumerable<ExcelColum<T>> colum;
        /// <summary>
        /// 列配置
        /// </summary>
        public IEnumerable<ExcelColum<T>> Colum
        {
            get { return colum; }
            set { colum = value; }
        }
        IEnumerable<ExcelColumGroup> columGroup;
        /// <summary>
        /// 列头分组
        /// </summary>
        public IEnumerable<ExcelColumGroup> ColumGroup
        {
            get { return columGroup; }
            set { columGroup = value; }
        }


    }
    /// <summary>
    /// Excel数据列
    /// </summary>
    /// <typeparam name="K"></typeparam>
    public class ExcelColum<K>
    {
        /// <summary>
        /// 列名称
        /// </summary>
        public string Name = String.Empty;


        short width = 0;
        /// <summary>
        /// 列宽度
        /// </summary>
        public short Width
        {
            get { return this.width; }
            set { this.width = value; }
        }

        private ExcelColumType realColumType;
        /// <summary>
        /// 实际Excel类型
        /// </summary>
        internal ExcelColumType RealColumType
        {
            get { return realColumType; }
            set { realColumType = value; }
        }


        ExcelColumType columType;
        /// <summary>
        /// 列数据类型
        /// </summary>
        public ExcelColumType ColumType
        {
            get { return columType; }
            set { columType = value; }
        }
        /// <summary>
        /// 是否对该列求和
        /// </summary>
        public bool DoColumSum
        {
            get;
            set;
        }


        Func<K, object> result;
        /// <summary>
        /// 计算列单元格结果
        /// </summary>
        public Func<K, object> Result
        {
            get { return result; }
            set { result = value; }
        }

        string fieldName;
        /// <summary>
        /// 自定特定字段 ， 服务于 ResultByFieldName
        /// </summary>
        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }

        Func<K, string, object> resultByFieldName;
        /// <summary>
        /// 指定特定字段运行委托,基于FieldName；如果 Result为null 则执行
        /// K 对象
        /// string 字段名
        /// object 返回值
        /// </summary>
        public Func<K, string, object> ResultByFieldName
        {
            get { return resultByFieldName; }
            set { resultByFieldName = value; }
        }

    }


    public class ExcelColumGroup
    {
        int cols;
        /// <summary>
        /// 合并单元格列数
        /// </summary>
        public int Cols
        {
            get { return cols; }
            set { cols = value; }
        }
        string name;
        /// <summary>
        /// 分组名称
        /// </summary>
        public string Name
        {
            get { return name; }
            set { name = value; }
        }



        int startIndex;
        /// <summary>
        /// 其实位置索引
        /// </summary>
        public int StartIndex
        {
            get { return startIndex; }
            set { startIndex = value; }
        }


    }

    /// <summary>
    /// Excel支持的数据类型
    /// </summary>
    public enum ExcelColumType
    {
        Double,
        String,
        DateTime,
        Bool
    }




    public static class ExcelExportExtension
    {


        #region excel import

        /// <summary>
        /// Excel读取到DataSet
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string excelPath)
        {
            return ExcelToDataSet(excelPath, true);
        }

        /// <summary>
        /// Excel读取到DataSet
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="firstRowAsHeader"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
        {
            int sheetCount;
            return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
        }

        /// <summary>
        /// Excel读取到DataSet
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="firstRowAsHeader"></param>
        /// <param name="sheetCount"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader, out int sheetCount)
        {
            using (DataSet ds = new DataSet())
            {
                using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook;
                    IFormulaEvaluator evaluator;
                    if (Path.GetExtension(excelPath) == ".xls")
                    {
                        workbook = new HSSFWorkbook(fileStream);
                        evaluator = new HSSFFormulaEvaluator(workbook);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fileStream);
                        evaluator = new XSSFFormulaEvaluator(workbook);
                    }

                    sheetCount = workbook.NumberOfSheets;

                    for (int i = 0; i < sheetCount; ++i)
                    {
                        ISheet sheet = workbook.GetSheetAt(i) as ISheet;
                        DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
                        ds.Tables.Add(dt);
                    }

                    return ds;
                }
            }
        }
        /// <summary>
        /// Excel读取到DataTable
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string excelPath, string sheetName)
        {
            return ExcelToDataTable(excelPath, sheetName, true);
        }
        /// <summary>
        /// Excel读取到DataTable
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="sheetName"></param>
        /// <param name="firstRowAsHeader"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
        {
            using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                IFormulaEvaluator evaluator;
                if (Path.GetExtension(excelPath) == ".xls")
                {
                    workbook = new HSSFWorkbook(fileStream);
                    evaluator = new HSSFFormulaEvaluator(workbook);
                }
                else
                {
                    workbook = new XSSFWorkbook(fileStream);
                    evaluator = new XSSFFormulaEvaluator(workbook);
                }

                ISheet sheet = workbook.GetSheet(sheetName) as ISheet;

                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
            }
        }

        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
        {
            if (firstRowAsHeader)
            {
                return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
            }
            else
            {
                return ExcelToDataTable(sheet, evaluator);
            }
        }

        private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = GetCellCount(sheet);

                for (int i = 0; i < cellCount; i++)
                {
                    if (firstRow.GetCell(i) != null)
                    {
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    FillDataRowByHSSFRow(row, evaluator, ref dr);
                    dt.Rows.Add(dr);
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }

        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    int cellCount = GetCellCount(sheet);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dt.Columns.Add(string.Format("F{0}", i), typeof(string));
                    }

                    for (int i = 0; i < sheet.FirstRowNum; ++i)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }

                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dr = dt.NewRow();
                        FillDataRowByHSSFRow(row, evaluator, ref dr);
                        dt.Rows.Add(dr);
                    }
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }

        private static void FillDataRowByHSSFRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
        {
            if (row != null)
            {
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    HSSFCell cell = row.GetCell(j) as HSSFCell;

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Blank:
                                dr[j] = DBNull.Value;
                                break;
                            case CellType.Boolean:
                                dr[j] = cell.BooleanCellValue;
                                break;
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                                break;
                            case CellType.String:
                                dr[j] = cell.StringCellValue;
                                break;
                            case CellType.Error:
                                dr[j] = cell.ErrorCellValue;
                                break;
                            case CellType.Formula:
                                cell = evaluator.EvaluateInCell(cell) as HSSFCell;
                                dr[j] = cell.ToString();
                                break;
                            default:
                                throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
                        }
                    }
                }
            }
        }

        private static int GetCellCount(ISheet sheet)
        {
            int firstRowNum = sheet.FirstRowNum;

            int cellCount = 0;

            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i);

                if (row != null && row.LastCellNum > cellCount)
                {
                    cellCount = row.LastCellNum;
                }
            }

            return cellCount;
        }

        #endregion


        /// <summary>
        /// 导出Excel，发送数据流到客户端
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="setting"></param>
        public static void Export<T>(this ExcelExportSetting<T> setting)
        {

            //string filename = setting.FileName + DateTime.Now.ToString("yyyy-MM-dd/HH:mm") + ".xls";
            //string filename = HttpUtility.UrlEncode(setting.FileName + DateTime.Now.ToString("-yyyy-MM-dd-HHmmss") + ".xls", System.Text.Encoding.UTF8);//防止文件名乱码
            //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            //System.Web.HttpContext.Current.Response.Clear();
            //var cols = setting.Colum.Count();
            //HSSFWorkbook hssworkbook = new HSSFWorkbook();
            //var sheet1 = hssworkbook.CreateSheet("Sheet1");
            //DocumentSummaryInformation docSummaryInfo = PropertySetFactory.CreateDocumentSummaryInformation();
            //docSummaryInfo.Company = String.Empty;
            //SummaryInformation summaryInfo = PropertySetFactory.CreateSummaryInformation();
            //summaryInfo.Subject = setting.Title;
            //hssworkbook.DocumentSummaryInformation = docSummaryInfo;
            //hssworkbook.SummaryInformation = summaryInfo;
            ////自动调整某列宽度
            //for (var i = 0; i < cols; i++)
            //{
            //    sheet1.AutoSizeColumn(i, true);
            //}

            //var currentRowIndex = 0;

            ////设置标题信息
            //if (!String.IsNullOrWhiteSpace(setting.Title))
            //{
            //    var row0 = sheet1.CreateRow(currentRowIndex);
            //    for (var i = 0; i < cols; i++)
            //    {
            //        row0.CreateCell(i).Row.Height = 600;
            //    }
            //    row0.GetCell(0).SetCellValue(setting.Title);

            //    currentRowIndex++;

            //    //标题样式
            //    sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));
            //    var titlestyle = hssworkbook.CreateCellStyle();
            //    titlestyle.WrapText = true;
            //    var titleFont = hssworkbook.CreateFont();
            //    titleFont.FontHeight = 20 * 20;
            //    titlestyle.SetFont(titleFont);
            //    titlestyle.Alignment = HorizontalAlignment.CENTER;
            //    sheet1.GetRow(0).GetCell(0).CellStyle = titlestyle;
            //}




            ////设置描述信息
            //if (!String.IsNullOrWhiteSpace(setting.Description))
            //{
            //    var row1 = sheet1.CreateRow(currentRowIndex);
            //    for (var i = 0; i < cols; i++)
            //    {
            //        row1.CreateCell(i).Row.Height = 1000;
            //    }
            //    row1.GetCell(0).SetCellValue(setting.Description);

            //    currentRowIndex++;

            //    //描述样式
            //    sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, cols - 1));
            //}



            ////设置列分组
            //if (setting.ColumGroup != null && setting.ColumGroup.Count() > 0)
            //{

            //    var rowGroup = sheet1.CreateRow(currentRowIndex);
            //    var columGroup = setting.ColumGroup;

            //    for (var i = 0; i < cols; i++)
            //    {
            //        var cell = rowGroup.CreateCell(i, CellType.STRING);
            //        cell.SetCellValue(String.Empty);
            //        cell.Row.Height = 500;

            //    }
            //    var groupStyle = hssworkbook.CreateCellStyle();
            //    var groupFont = hssworkbook.CreateFont();
            //    groupFont.FontHeight = 15 * 15;
            //    groupFont.Boldweight = 6000;
            //    groupStyle.SetFont(groupFont);
            //    groupStyle.Alignment = HorizontalAlignment.CENTER;

            //    for (var j = columGroup.Count() - 1; j >= 0; j--)
            //    {
            //        var cell = rowGroup.GetCell(columGroup.ElementAt(j).StartIndex);
            //        cell.SetCellValue(columGroup.ElementAt(j).Name);
            //        cell.CellStyle = groupStyle;
            //        sheet1.AddMergedRegion(new CellRangeAddress(2, 2, columGroup.ElementAt(j).StartIndex, columGroup.ElementAt(j).StartIndex + columGroup.ElementAt(j).Cols - 1));
            //    }

            //    currentRowIndex++;
            //}
            ////列头行索引
            //var columHeaderRowIndex = currentRowIndex;



            ////列头样式
            //var headerfont = hssworkbook.CreateFont();
            //headerfont.FontHeightInPoints = 12;
            //headerfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
            //var headerstyle = hssworkbook.CreateCellStyle();
            //headerstyle.SetFont(headerfont);
            ////设置列头信息
            //var row2 = sheet1.CreateRow(columHeaderRowIndex);
            //for (var i = 0; i < cols; i++)
            //{
            //    var cell = row2.CreateCell(i);
            //    cell.Row.Height = 500;
            //    cell.SetCellValue(setting.Colum.ElementAt(i).Name);
            //    cell.CellStyle = headerstyle;

            //}




            ////输出数据
            //for (var i = 0; i < setting.Source.Count(); i++)
            //{
            //    var model = setting.Source.ElementAt(i);
            //    var rowTemp = sheet1.CreateRow(i + columHeaderRowIndex + 1);

            //    Type modelType = model.GetType();
            //    PropertyInfo[] properties = modelType.GetProperties();
            //    var proDic = new Dictionary<string, PropertyInfo>();
            //    foreach (var item in properties)
            //    {
            //        proDic.Add(item.Name, item);
            //    }
            //    for (var j = 0; j < setting.Colum.Count(); j++)
            //    {
            //        rowTemp.CreateCell(j).SetCellValueT(setting.Colum.ElementAt(j).ColumType, setting.Colum.ElementAt(j).Result == null ? setting.Colum.ElementAt(j).ResultByFieldName(model, setting.Colum.ElementAt(j).FieldName) : setting.Colum.ElementAt(j).Result(model));
            //    }
            //}



            //HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssworkbook);
            ////对设定并可行的列求和
            //var containSum = false;
            //foreach (var item in setting.Colum)
            //{
            //    if (item.DoColumSum)
            //    {
            //        containSum = true;
            //        break;
            //    }
            //}

            //if (containSum)
            //{
            //    var style = hssworkbook.CreateCellStyle();
            //    var font = hssworkbook.CreateFont();
            //    font.FontHeight = 15 * 15;
            //    font.Boldweight = 6000;
            //    font.Color = HSSFColor.BLUE.index;

            //    style.SetFont(font);


            //    //求和 SUM(A2:A12)
            //    var letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            //    var totalRow = sheet1.CreateRow(columHeaderRowIndex + 1 + setting.Source.Count());
            //    var rowName = "A";
            //    for (var x = 0; x < setting.Colum.Count(); x++)
            //    {
            //        rowName = x == 0 ? "A" : (x > 26 ? letter.ElementAt(x / 26).ToString() + letter.ElementAt(x % 26).ToString() : letter.ElementAt(x % 26).ToString());
            //        if (setting.Colum.ElementAt(x).DoColumSum)
            //        {
            //            var tempCell = totalRow.CreateCell(x);
            //            tempCell.CellFormula = "SUM(" + rowName + (columHeaderRowIndex + 2) + ":" + rowName + (columHeaderRowIndex + 1 + setting.Source.Count()) + ")";
            //            tempCell.CellStyle = style;
            //            tempCell = e.EvaluateInCell(tempCell);
            //        }
            //        else
            //        {
            //            var temp = totalRow.CreateCell(x);
            //            temp.CellStyle = style;
            //            if (x == 0) temp.SetCellValue("合计");
            //        }
            //    }
            //}










            ////表格样式
            //for (var i = 0; i < cols; i++)
            //{
            //    if (setting.Colum.ElementAt(i).Width <= 0) continue;
            //    sheet1.SetColumnWidth(i, setting.Colum.ElementAt(i).Width);
            //}



            ////写入响应流
            //MemoryStream filestream = new MemoryStream();
            //hssworkbook.Write(filestream);
            //System.Web.HttpContext.Current.Response.BinaryWrite(filestream.GetBuffer());
            //System.Web.HttpContext.Current.Response.End();





        }


        /// <summary>
        ///构建路径
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static string BiuldPath(string filePath)
        {
            //return HttpContext.Current.Server.MapPath("~/Upload/" + filePath);
            return String.Empty;
        }

        /// <summary>
        /// 给单元格赋值，并设置数据类型
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        private static void SetCellValueT(this NPOI.SS.UserModel.ICell cell, ExcelColumType type, object value)
        {
            if (value == null)
                return;
            if (type == ExcelColumType.Bool)
                cell.SetCellValue((bool)value);
            else if (type == ExcelColumType.DateTime)
                cell.SetCellValue((DateTime)value);
            else if (type == ExcelColumType.Double)
            {
                double number = 0;
                if (double.TryParse(value.ToString(), out number))
                    cell.SetCellValue(number);
                else
                    cell.SetCellValue(value.ToString());
            }
            else
                cell.SetCellValue(value.ToString());

        }

    }

}