excel(泛型和datatable)帮助类,含添加和读取表头批注

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

namespace Tools
{
    public class ExcelHelper
    {
        internal static readonly log4net.ILog log = LogManager.GetLogger("log4netlogger");

        #region 导入数据
        /// 从Excel中加载数据（泛型）-  npoi   缺点：不能有datetime？类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fileName">文件名称（用于判断后缀）</param>
        /// <param name="importType">importType 1物理路径上传 2文件流上传 </param>
        /// <param name="filePath">excel文件路径</param>
        /// <param name="stream">文件流</param>
        /// <param name="beginRowIndex">起始行(从0开始)，例如第一行为列头，从第二行开始为数据，则 beginRowIndex = 1</param>
        /// <param name="IsReadComments">是否读取批注 默认为false ;true 读取表头批注 false 读取表头</param>
        /// <returns>泛型列表</returns>
        public static IEnumerable<T> ImportExcel<T>(string fileName, int importType = 1, string filePath = "", Stream stream = null, int beginRowIndex = 1,bool IsReadComments=false) where T : new()
        {
            List<T> resultList = new List<T>();
            List<string> colName = new List<string>();
            Dictionary<int, string> dicColName = new Dictionary<int, string>();
            try
            {
                var fileExt = Path.GetExtension(fileName).ToLower();//获取文件后缀
                IWorkbook workbook;
                if (importType == 1)
                {
                    stream = new FileStream(filePath, FileMode.Open);
                }

                if (fileExt == ".xls")
                    workbook = new HSSFWorkbook(stream);
                else if (fileExt == ".xlsx")
                    workbook = new XSSFWorkbook(stream);
                else
                    return resultList;


                ISheet sheet = workbook.GetSheetAt(0);//获取sheet

                int rowStart = sheet.FirstRowNum;//开始行
                int rowEnd = sheet.LastRowNum;//结束行

                if (rowEnd >= 0)
                {
                    int colStart = sheet.GetRow(rowStart).FirstCellNum;//开始列
                    int colEnd = sheet.GetRow(rowStart).LastCellNum;//结束列

                    var firstRow = sheet.GetRow(rowStart);
        
                    //判断读取批注还是表头
                    if (IsReadComments == true)
                    {    
                        //处理表头 
                        for (int i = colStart; i < colEnd; i++)
                        {
                            var value = firstRow.GetCell(i);
                            dicColName[i] = value == null ? "" : value.CellComment.String.String.ToLower();//获取批注
                        }
                    }
                    else
                    {
                        for (int i = colStart; i < colEnd; i++)
                        {
                            var value = firstRow.GetCell(i);
                            dicColName[i] = value == null ? "" : value.ToString();
                        }
                    }

                    List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());
                    propertyInfoList = propertyInfoList.Where(p => dicColName.Values.Contains(p.Name)).ToList();

                    for (int i = rowStart + beginRowIndex; i <= rowEnd; i++)
                    {
                        var row = sheet.GetRow(i);//获取行
                        if (row == null)
                            continue;
                        T t = new T();
                        for (int j = colStart; j <= colEnd; j++)
                        {
                            var cell = row.GetCell(j);//获取列

                            if (cell == null)
                                continue;
                            var propertyInfo = propertyInfoList.Where(a => a.Name == dicColName[j]).FirstOrDefault();
                            if (propertyInfo != null)
                            {
                                var cellType = cell.CellType; //propertyInfo.PropertyType.Name.ToLower();
                                object cellValue = cell.ToString();

                                cellValue = DatatableAndListHelper.JudgeType(propertyInfo, cellValue);
                                propertyInfo.SetValue(t, cellValue);//赋值
                            }
                        }
                        resultList.Add(t);
                    }
                }
                stream.Close();
                return resultList;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw;
            }

        }

        /// <summary>
        /// 从Excel中加载数据（datatable）-  npoi
        /// </summary>
        /// <param name="fileName">文件名称（用于判断后缀）</param>
        /// <param name="importType">importType 1物理路径上传 2文件流上传 </param>
        /// <param name="filePath">excel文件路径</param>
        /// <param name="stream">文件流</param>
        /// <param name="beginRowIndex">起始行(从0开始)，例如第一行为列头，从第二行开始为数据，则 beginRowIndex = 1</param>
        /// <param name="IsReadComments">是否读取批注 默认为false ;true 读取表头批注 false 读取表头</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string fileName, int importType = 1, string filePath = "", Stream stream = null, int beginRowIndex = 1, bool IsReadComments = false)
        {
            DataTable table = new DataTable();
            List<string> colName = new List<string>();
            Dictionary<int, string> dicColName = new Dictionary<int, string>();
            try
            {
                var fileExt = Path.GetExtension(fileName).ToLower();//获取文件后缀
                IWorkbook workbook;
                if (importType == 1)
                {
                    stream = new FileStream(filePath, FileMode.Open);
                }

                if (fileExt == ".xls")
                    workbook = new HSSFWorkbook(stream);
                else if (fileExt == ".xlsx")
                    workbook = new XSSFWorkbook(stream);
                else
                    return table;


                ISheet sheet = workbook.GetSheetAt(0);//获取sheet

                int rowStart = sheet.FirstRowNum;//开始行
                int rowEnd = sheet.LastRowNum;//结束行

                if (rowEnd >= 0)
                {
                    int colStart = sheet.GetRow(rowStart).FirstCellNum;//开始列
                    int colEnd = sheet.GetRow(rowStart).LastCellNum;//结束列

                    var firstRow = sheet.GetRow(rowStart);

                    //判断读取批注还是表头
                    if (IsReadComments == true)
                    {
                        //处理表头 
                        for (int i = colStart; i < colEnd; i++)
                        {
                            var value = firstRow.GetCell(i);
                            dicColName[i] = value == null ? "" : value.CellComment.String.String.ToLower();//获取批注
                        }
                    }
                    else
                    {
                        for (int i = colStart; i < colEnd; i++)
                        {
                            var value = firstRow.GetCell(i);
                            dicColName[i] = value == null ? "" : value.ToString();
                        }
                    }
                    //处理数据源
                    for (int i = rowStart + beginRowIndex; i <= rowEnd; i++)
                    {
                        var row = sheet.GetRow(i);//获取行
                        if (row == null)
                            continue;
                        DataRow dataRow = table.NewRow();
                        for (int j = colStart; j <= colEnd; j++)
                        {
                            var cell = row.GetCell(j);//获取列

                            if (cell == null)
                                continue;
                            dataRow[j] = cell.ToString();
                        }
                        table.Rows.Add(dataRow);
                    }
                }
                stream.Close();

                return table;
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
                throw;
            }
        }

        #endregion


        #region 导出数据

        public static MemoryStream ExportExcel<T>(IList<T> list, string fileName = "导出excel")
        {
            try
            {
                Dictionary<int, string> dicColName = new Dictionary<int, string>();

                HSSFWorkbook workbook = new HSSFWorkbook();

                var sheet = workbook.CreateSheet(fileName);
                var propertyInfos = new List<PropertyInfo>(typeof(T).GetProperties());
                var colcount = propertyInfos.Count;

                //创建表头
                var headCell = sheet.CreateRow(0);
                for (int i = 0; i < colcount; i++)
                {
                    dicColName[i] = propertyInfos[i].Name;
                    headCell.CreateCell(i).SetCellValue(propertyInfos[i].Name);
                }
                //创建数据列 
                if (list != null)
                    for (int i = 1; i <= list.Count; i++)
                    {
                        var cell = sheet.CreateRow(i);
                        var info = list[i];
                        for (int j = 0; j < colcount; j++)
                        {
                            var p = propertyInfos.Where(a => a.Name == dicColName[j]).FirstOrDefault();//获取信息
                            if (p == null)
                                continue;
                            var value = p.GetValue(list[i]) == null ? "" : p.GetValue(list[i]).ToString();//获取值
                            cell.CreateCell(j).SetCellValue(value);
                        }
                    }

                //内存流
                MemoryStream ms = new MemoryStream();

                workbook.Write(ms);
                return ms;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("data:{0},错误：{1}", list, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// datatable导出excel
        /// </summary>
        /// <param name="table">数据源</param>
        /// <param name="fileName">sheet名称</param>
        /// <param name="beginRowIndex"></param>
        /// <returns></returns>
        public static MemoryStream ExportExcel(DataTable table, string fileName = "导出excel")
        {
            try
            {
                Dictionary<int, string> dicColName = new Dictionary<int, string>();

                HSSFWorkbook workbook = new HSSFWorkbook();

                var sheet = workbook.CreateSheet(fileName);
                var rowcount = table.Rows.Count;
                var colcount = table.Columns.Count;

                //创建表头
                var headCell = sheet.CreateRow(0);
                for (int i = 0; i < colcount; i++)
                {
                    headCell.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                }
                //创建数据列   
                for (int i = 1; i <= rowcount; i++)
                {
                    var cell = sheet.CreateRow(i);
                    var info = table.Rows[i];
                    for (int j = 0; j < colcount; j++)
                    {
                        var value = info[j] == null ? "" : info[j].ToString();
                        cell.CreateCell(j).SetCellValue(value);
                    }
                }

                MemoryStream ms = new MemoryStream();

                workbook.Write(ms);

                return ms;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("data:{0},错误：{1}", table, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 导出自定义表头excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="headers">key:字段名 value:表头名称</param>
        /// <param name="fileName"></param>
        /// <param name="IsRequiredComment">是否需要添加批注 默认为false 批注为headers的key</param>
        /// <returns></returns>
        public static MemoryStream ExportExcel<T>(IList<T> list, Dictionary<string, string> headers, string fileName = "导出excel", bool IsRequiredComment = false)
        {
            try
            {
                Dictionary<int, string> dicColName = new Dictionary<int, string>();

                HSSFWorkbook workbook = new HSSFWorkbook();

                var sheet = workbook.CreateSheet(fileName);
                var propertyInfos = new List<PropertyInfo>(typeof(T).GetProperties());
                var colcount = headers.Count();
                var patr = sheet.CreateDrawingPatriarch();
                //创建表头
                var headCell = sheet.CreateRow(0);
                for (int i = 0; i < colcount; i++)
                {
                    var cell = headCell.CreateCell(i);
                    cell.SetCellValue(headers.ElementAt(i).Value);

                    if (IsRequiredComment == true)
                    {
                        HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 4));
                        comment1.String = new HSSFRichTextString(headers.ElementAt(i).Key);
                        cell.CellComment = comment1;
                    }
                }


                //创建数据列 
                if (list != null)
                    for (int i = 1; i <= list.Count; i++)
                    {
                        var row = sheet.CreateRow(i);
                        var info = list[i];
                        for (int j = 0; j < colcount; j++)
                        {
                            //list.GetType().GetProperty(headers.ElementAt(i).Key);//获取信息写法2
                            var p = propertyInfos.Where(a => a.Name == headers.ElementAt(j).Key).FirstOrDefault();//获取信息
                            if (p == null)
                                continue;
                            dynamic cellvaule = p.GetValue(list[i]);//获取值
                            var value = cellvaule ? "" : cellvaule.ToString();
                            var type = GetCellType(p.PropertyType.FullName);//获取类型
                            if (p.PropertyType.FullName == typeof(decimal).FullName)
                            {
                                double deci;
                                double.TryParse(value, out deci);
                                value = deci;
                            }
                            var cell = row.CreateCell(j);
                            cell.SetCellType(type);
                            cell.SetCellValue(value);
                        }
                    }

                //内存流
                MemoryStream ms = new MemoryStream();

                workbook.Write(ms);
                return ms;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("data:{0},错误：{1}", list, ex.Message));
                throw;
            }
        }

        /// <summary>
        ///  datatable导出excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="headers">key:字段名 value:表头名称</param>
        /// <param name="fileName"></param>
        /// <param name="IsRequiredComment">是否需要添加批注 默认为false 批注为headers的key</param>
        /// <returns></returns>
        public static MemoryStream ExportExcel(DataTable table, Dictionary<string, string> headers, string fileName = "导出excel", bool IsRequiredComment = false)
        {
            try
            {
                Dictionary<int, string> dicColName = new Dictionary<int, string>();

                HSSFWorkbook workbook = new HSSFWorkbook();

                var sheet = workbook.CreateSheet(fileName);
                var Columns = table.Columns;
                var rowcount = table.Rows.Count;
                var colcount = headers.Count;

                var patr = sheet.CreateDrawingPatriarch();
                //创建表头
                var headCell = sheet.CreateRow(0);
                for (int i = 0; i < colcount; i++)
                {
                    var cell = headCell.CreateCell(i);
                    cell.SetCellValue(headers.ElementAt(i).Value);

                    if (IsRequiredComment == true)
                    {
                        HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 4));
                        comment1.String = new HSSFRichTextString(headers.ElementAt(i).Key);
                        cell.CellComment = comment1;
                    }
                }
                //创建数据列   
                for (int i = 1; i <= rowcount; i++)
                {
                    var row = sheet.CreateRow(i);
                    var info = table.Rows[i];
                    for (int j = 0; j < colcount; j++)
                    {
                        if (!Columns.Contains(headers.ElementAt(j).Key))//判断该列名是否存在
                            continue;

                        var cellvaule = info[headers.ElementAt(j).Key];
                        var value = cellvaule == null ? "" : cellvaule.ToString();
                        row.CreateCell(j).SetCellValue(value);
                    }
                }

                MemoryStream ms = new MemoryStream();

                workbook.Write(ms);

                return ms;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("data:{0},错误：{1}", table, ex.Message));
                throw;
            }
        }

        #endregion


        internal void GetValueByType(CellType cellType, object cellValue, ICell cell)
        {

            //判断excel表的类型
            switch (cellType)
            {
                case CellType.String:
                    cellValue = cell.StringCellValue;
                    break;
                case CellType.Numeric:
                    cellValue = cell.NumericCellValue;
                    break;
                case CellType.Boolean:
                    cellValue = cell.BooleanCellValue;
                    break;
                case CellType.Blank://空值
                    cellValue = "";
                    break;
                case CellType.Formula:
                    cellValue = cell.CellFormula;
                    break;
                default:
                    break;
            }
        }

        private static CellType GetCellType(dynamic type)
        {
            if (type == typeof(int).FullName || type == typeof(float).FullName || type == typeof(decimal).FullName || type == typeof(double).FullName)
            {
                return CellType.Numeric;
            }

            if (type == typeof(bool).FullName)
            {
                return CellType.Boolean;
            }

            return CellType.String;
        }

    }
}