excel帮助类（泛型和datatable）

向右看齐 2023-07-24 03:13 0阅读 0赞

> 在做项目的过程中经常会遇到关于excel的导入和导出操作，所以就封装了一个excel帮助类来提升开发的速度。。。

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;
            }
    
        }
    }