using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Windows.Forms;
|
|
namespace iWareCommon.Utils
|
{
|
/// <summary>
|
/// Excel操作类
|
/// </summary>
|
public class ExcelHelper
|
{
|
|
/// <summary>
|
/// 从Excel取数据并记录到List集合里
|
/// </summary>
|
/// <param name="cellHeard">单元头的值和名称：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
|
/// <param name="filePath">保存文件绝对路径</param>
|
/// <param name="errorMsg">错误信息</param>
|
/// <returns>转换后的List对象集合</returns>
|
public static List<T> ExcelToEntityListForCreateTable<T>(Dictionary<string, string> cellHeard, string filePath,
|
out string tableDesc, out string tableName,
|
out StringBuilder errorMsg) where T : new()
|
{
|
List<T> enlist = new List<T>();
|
errorMsg = new StringBuilder();
|
try
|
{
|
enlist = Excel2003ToEntityListForCreateTable<T>(cellHeard, filePath, out tableDesc, out tableName, out errorMsg);
|
return enlist;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 从Excel取数据并记录到List集合里
|
/// </summary>
|
/// <param name="cellHeard">单元头的值和名称：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
|
/// <param name="filePath">保存文件绝对路径</param>
|
/// <param name="errorMsg">错误信息</param>
|
/// <returns>转换后的List对象集合</returns>
|
public static List<T> ExcelToEntityListForInitCodeItems<T>(Dictionary<string, string> cellHeard, string filePath,
|
out StringBuilder errorMsg) where T : new()
|
{
|
List<T> enlist = new List<T>();
|
errorMsg = new StringBuilder();
|
try
|
{
|
enlist = Excel2003ToEntityListForInitCodeItems<T>(cellHeard, filePath, out errorMsg);
|
return enlist;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 从Excel2003取数据并记录到List集合里
|
/// </summary>
|
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
|
/// <param name="filePath">保存文件绝对路径</param>
|
/// <param name="errorMsg">错误信息</param>
|
/// <returns>转换好的List对象集合</returns>
|
private static List<T> Excel2003ToEntityListForCreateTable<T>(Dictionary<string, string> cellHeard,
|
string filePath, out string tableDesc, out string tableName, out StringBuilder errorMsg) where T : new()
|
{
|
errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时，会有格式的错误信息
|
List<T> enlist = new List<T>(); // 转换后的集合
|
List<string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称
|
tableDesc = "";
|
tableName = "";
|
try
|
{
|
IWorkbook workbook = GetWorkBook(filePath);
|
if (workbook == null)
|
{
|
errorMsg.Append("没有找到IWorkbook");
|
return null;
|
}
|
|
ISheet sheet = (ISheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页
|
//ICell cell = sheet.GetRow(0).GetCell(0);
|
//tableDesc = cell.StringCellValue;
|
//cell = sheet.GetRow(1).GetCell(0);
|
|
for (int i = 1; i <= sheet.LastRowNum; i++) // 从3开始，第0行为表汉字名,第1行是表名，第2行是单元头
|
{
|
// 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
|
if (sheet.GetRow(i) == null)
|
{
|
break;
|
}
|
|
T en = new T();
|
string errStr = ""; // 当前行转换时，是否有错误信息，格式为：第1行数据转换异常：XXX列；
|
for (int j = 0; j < keys.Count; j++)
|
{
|
// 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName
|
if (keys[j].IndexOf(".") >= 0)
|
{
|
// 2.1解析子类属性
|
string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
|
string subClassName = properotyArray[0]; // '.'前面的为子类的名称
|
string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
|
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
|
if (subClassInfo != null)
|
{
|
// 2.1.1 获取子类的实例
|
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
|
// 2.1.2 根据属性名称获取子类里的属性信息
|
System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
|
if (properotyInfo != null)
|
{
|
try
|
{
|
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
|
properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
|
}
|
catch (Exception e)
|
{
|
if (errStr.Length == 0)
|
{
|
errStr = "第" + i + "行数据转换异常：";
|
}
|
errStr += cellHeard[keys[j]] + "列；异常信息:" + e.Message;
|
}
|
|
}
|
}
|
}
|
else
|
{
|
// 3.给指定的属性赋值
|
System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]);
|
if (properotyInfo != null)
|
{
|
try
|
{
|
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
|
properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
|
}
|
catch (Exception e)
|
{
|
if (errStr.Length == 0)
|
{
|
errStr = "第" + i + "行数据转换异常：";
|
}
|
errStr += cellHeard[keys[j]] + "列；异常信息:" + e.Message;
|
}
|
}
|
}
|
}
|
// 若有错误信息，就添加到错误信息里
|
if (errStr.Length > 0)
|
{
|
errorMsg.AppendLine(errStr);
|
}
|
enlist.Add(en);
|
}
|
|
return enlist;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
|
/// <summary>
|
/// 从Excel2003取数据并记录到List集合里
|
/// </summary>
|
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
|
/// <param name="filePath">保存文件绝对路径</param>
|
/// <param name="errorMsg">错误信息</param>
|
/// <returns>转换好的List对象集合</returns>
|
private static List<T> Excel2003ToEntityListForInitCodeItems<T>(Dictionary<string, string> cellHeard,
|
string filePath, out StringBuilder errorMsg) where T : new()
|
{
|
errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时，会有格式的错误信息
|
List<T> enlist = new List<T>(); // 转换后的集合
|
List<string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称
|
try
|
{
|
IWorkbook workbook = GetWorkBook(filePath);
|
if (workbook == null)
|
{
|
errorMsg.Append("没有找到IWorkbook");
|
return null;
|
}
|
ISheet sheet = (ISheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页
|
for (int i = 1; i <= sheet.LastRowNum; i++) // 从1开始，第0行是单元头
|
{
|
// 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
|
if (sheet.GetRow(i) == null)
|
{
|
break;
|
}
|
|
T en = new T();
|
string errStr = ""; // 当前行转换时，是否有错误信息，格式为：第1行数据转换异常：XXX列；
|
for (int j = 0; j < keys.Count; j++)
|
{
|
// 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName
|
if (keys[j].IndexOf(".") >= 0)
|
{
|
// 2.1解析子类属性
|
string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
|
string subClassName = properotyArray[0]; // '.'前面的为子类的名称
|
string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
|
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
|
if (subClassInfo != null)
|
{
|
// 2.1.1 获取子类的实例
|
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
|
// 2.1.2 根据属性名称获取子类里的属性信息
|
System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
|
if (properotyInfo != null)
|
{
|
try
|
{
|
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
|
properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
|
}
|
catch (Exception e)
|
{
|
if (errStr.Length == 0)
|
{
|
errStr = "第" + i + "行数据转换异常：";
|
}
|
errStr += cellHeard[keys[j]] + "列；异常信息:" + e.Message;
|
}
|
|
}
|
}
|
}
|
else
|
{
|
// 3.给指定的属性赋值
|
System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]);
|
if (properotyInfo != null)
|
{
|
try
|
{
|
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
|
properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
|
}
|
catch (Exception e)
|
{
|
if (errStr.Length == 0)
|
{
|
errStr = "第" + i + "行数据转换异常：";
|
}
|
errStr += cellHeard[keys[j]] + "列；异常信息:" + e.Message;
|
}
|
}
|
}
|
}
|
// 若有错误信息，就添加到错误信息里
|
if (errStr.Length > 0)
|
{
|
errorMsg.AppendLine(errStr);
|
}
|
enlist.Add(en);
|
}
|
return enlist;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
|
|
/// <summary>
|
/// 从Excel获取值传递到对象的属性里
|
/// </summary>
|
/// <param name="distanceType">目标对象类型</param>
|
/// <param name="sourceCell">对象属性的值</param>
|
private static Object GetExcelCellToProperty(Type distanceType, ICell sourceCell)
|
{
|
object rs = distanceType.IsValueType ? Activator.CreateInstance(distanceType) : null;
|
|
// 1.判断传递的单元格是否为空
|
if (sourceCell == null || string.IsNullOrEmpty(sourceCell.ToString()))
|
{
|
return rs;
|
}
|
|
// 2.Excel文本和数字单元格转换，在Excel里文本和数字是不能进行转换，所以这里预先存值
|
object sourceValue = null;
|
switch (sourceCell.CellType)
|
{
|
case CellType.Blank:
|
break;
|
|
case CellType.Boolean:
|
break;
|
|
case CellType.Error:
|
break;
|
|
case CellType.Formula:
|
break;
|
|
case CellType.Numeric:
|
sourceValue = sourceCell.NumericCellValue;
|
break;
|
|
case CellType.String:
|
sourceValue = sourceCell.StringCellValue;
|
break;
|
|
case CellType.Unknown:
|
break;
|
|
default:
|
break;
|
}
|
|
string valueDataType = distanceType.Name;
|
|
// 在这里进行特定类型的处理
|
if (sourceValue == null) return null;
|
switch (valueDataType.ToLower()) // 以防出错，全部小写
|
{
|
case "string":
|
rs = sourceValue.ToString().Trim();//移除空格
|
break;
|
case "int":
|
case "int16":
|
case "int32":
|
rs = (int)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
|
break;
|
case "float":
|
case "single":
|
rs = (float)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
|
break;
|
case "datetime":
|
rs = sourceCell.DateCellValue;
|
break;
|
case "guid":
|
rs = (Guid)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
|
return rs;
|
case "decimal":
|
rs = (decimal)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
|
return rs;
|
default:
|
throw new Exception("不支持的数据类型！");
|
}
|
return rs;
|
}
|
|
/// <summary>
|
/// 获取IWorkbook
|
/// </summary>
|
/// <param name="filePath"></param>
|
/// <returns></returns>
|
private static IWorkbook GetWorkBook(string filePath)
|
{
|
IWorkbook workbook = null;
|
using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read))
|
{
|
//获取后缀名
|
string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
|
//判断是否是excel文件
|
if (extension == ".xlsx" || extension == ".xls")
|
{
|
//判断excel的版本
|
if (extension == ".xlsx")
|
{
|
workbook = new XSSFWorkbook(fs);
|
}
|
else
|
{
|
workbook = new HSSFWorkbook(fs);
|
}
|
}
|
}
|
return workbook;
|
}
|
|
|
|
#region 测试用例汇总
|
|
|
public static List<T> ExcelToEntityListForCSYLHZ<T>(Dictionary<string, string> cellHeard, string filePath,
|
out StringBuilder errorMsg) where T : new()
|
{
|
List<T> enlist = new List<T>();
|
errorMsg = new StringBuilder();
|
try
|
{
|
enlist = Excel2003ToEntityListForCSYLHZ<T>(cellHeard, filePath, out errorMsg);
|
return enlist;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
private static List<T> Excel2003ToEntityListForCSYLHZ<T>(Dictionary<string, string> cellHeard,
|
string filePath, out StringBuilder errorMsg) where T : new()
|
{
|
errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时，会有格式的错误信息
|
List<T> enlist = new List<T>(); // 转换后的集合
|
List<string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称
|
try
|
{
|
IWorkbook workbook = GetWorkBook(filePath);
|
if (workbook == null)
|
{
|
errorMsg.Append("没有找到IWorkbook");
|
return null;
|
}
|
int sheets = workbook.NumberOfSheets;
|
for (int pp = 0; pp < sheets - 1; pp++)
|
{
|
ISheet sheet = (ISheet)workbook.GetSheetAt(pp); // 获取此文件第一个Sheet页
|
for (int i = 6; i <= sheet.LastRowNum; i++) // 从1开始，第0行是单元头
|
{
|
// 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
|
if (sheet.GetRow(i) == null)
|
{
|
break;
|
}
|
|
T en = new T();
|
string errStr = ""; // 当前行转换时，是否有错误信息，格式为：第1行数据转换异常：XXX列；
|
for (int j = 0; j < keys.Count; j++)
|
{
|
// 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName
|
if (keys[j].IndexOf(".") >= 0)
|
{
|
// 2.1解析子类属性
|
string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
|
string subClassName = properotyArray[0]; // '.'前面的为子类的名称
|
string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
|
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
|
if (subClassInfo != null)
|
{
|
// 2.1.1 获取子类的实例
|
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
|
// 2.1.2 根据属性名称获取子类里的属性信息
|
System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
|
if (properotyInfo != null)
|
{
|
try
|
{
|
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
|
properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
|
}
|
catch (Exception e)
|
{
|
if (errStr.Length == 0)
|
{
|
errStr = "第" + i + "行数据转换异常：";
|
}
|
errStr += cellHeard[keys[j]] + "列；异常信息:" + e.Message;
|
}
|
|
}
|
}
|
}
|
else
|
{
|
// 3.给指定的属性赋值
|
System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]);
|
if (properotyInfo != null)
|
{
|
try
|
{
|
// Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
|
properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j)), null);
|
}
|
catch (Exception e)
|
{
|
if (errStr.Length == 0)
|
{
|
errStr = "第" + i + "行数据转换异常：";
|
}
|
errStr += cellHeard[keys[j]] + "列；异常信息:" + e.Message;
|
}
|
}
|
}
|
}
|
// 若有错误信息，就添加到错误信息里
|
if (errStr.Length > 0)
|
{
|
errorMsg.AppendLine(errStr);
|
}
|
enlist.Add(en);
|
}
|
}
|
|
return enlist;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
|
#endregion
|
}
|
}
|
