分类:
.net
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;
}
}
}评价
