在做项目的过程中经常会遇到关于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)