当前位置:
首页 > Python基础教程 >
-
C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式(2)
0);
T t = default(T);
#region 获取深度表达式更新列表
List<IExcelCellExpressDeepUpdate<T>> excelCellExpressDeepUpdateList = new List<IExcelCellExpressDeepUpdate<T>>(0);
if (excelFileDescription != null)
{
foreach (var item in excelFileDescription.ExcelDeepUpdateList)
{
if (item is IExcelCellExpressDeepUpdate<T>)
{
excelCellExpressDeepUpdateList.Add((IExcelCellExpressDeepUpdate<T>)item);
}
}
}
#endregion
#region 通过表达式映射列表读取对象T
Func<List<ExcelCellFieldMapper>, T> expressMapperFunc = (excelCellFieldMapperList) =>
{
t = new T();
foreach (var cellMapper in excelCellFieldMapperList)
{
string currentCellExpress = cellMapper.CellExpressRead.CellCoordinateExpress;
object pValue = ExcelHelper.GetVByExpress(currentCellExpress, workbook, sheet);
try
{
cellMapper.PropertyInfo.SetValue(t, Convert.ChangeType(pValue, cellMapper.PropertyInfo.PropertyType));
}
catch (Exception)
{
}
}
return t;
};
#endregion
#region 执行初始表达式数据收集
//获取t的单元格映射列表
List<ExcelCellFieldMapper> cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();
t = expressMapperFunc(cellMapperList);
#endregion
#region 执行深度更新策略收集数据
Action<IExcelCellExpressDeepUpdate<T>> actionDeepReadAction = (excelCellExpressDeepUpdate) =>
{
//获取初始表达式映射列表
cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();
//执行单元格表达式深度更新
bool isContinute = false;
do
{
//通过深度更新策略更新初始表达式数据
foreach (var cellMapper in cellMapperList)
{
if (cellMapper.CellExpressRead != null)
{
string currentCellExpress = cellMapper.CellExpressRead.CellCoordinateExpress;
currentCellExpress = excelCellExpressDeepUpdate.GetNextCellExpress(currentCellExpress);
cellMapper.CellExpressRead.CellCoordinateExpress = currentCellExpress;
}
}
t = expressMapperFunc(cellMapperList);
isContinute = excelCellExpressDeepUpdate.IsContinute(t);
if (isContinute)
{
tList.Add(t);
}
} while (isContinute);
};
foreach (var item in excelCellExpressDeepUpdateList)
{
actionDeepReadAction(item);
}
#endregion
return tList;
}
/// <summary>
/// 获取文件首个sheet的标题位置
/// </summary>
/// <typeparam name="T">T必须做了标题映射</typeparam>
/// <param name="filePath"></param>
/// <returns></returns>
public static int FileFirstSheetTitleIndex<T>(string filePath)
{
int titleIndex = 0;
if (File.Exists(filePath))
{
try
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
try
{
workbook = new XSSFWorkbook(fileStream);
}
catch (Exception)
{
workbook = new HSSFWorkbook(fileStream);
}
string[] titleArray = ExcelTitleFieldMapper.GetModelFieldMapper<T>().Select(m => m.ExcelTitle).ToArray();
ISheet sheet = workbook.GetSheetAt(0);
titleIndex = ExcelHelper.GetSheetTitleIndex(sheet, titleArray);
}
}
catch (Exception e)
{
throw new Exception($"文件：{filePath}被占用！", e);
}
}
return titleIndex;
}
/// <summary>
/// 获取文件首个sheet的标题位置
/// </summary>
/// <param name="filePath"></param>
/// <param name="titleNames"></param>
/// <returns></returns>
public static int FileFirstSheetTitleIndex(string filePath, params string[] titleNames)
{
int titleIndex = 0;
if (File.Exists(filePath))
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
try
{
workbook = new XSSFWorkbook(fileStream);
}
catch (Exception)
{
workbook = new HSSFWorkbook(fileStream);
}
ISheet sheet = workbook.GetSheetAt(0);
titleIndex = ExcelHelper.GetSheetTitleIndex(sheet, titleNames);
}
}
return titleIndex;
}
#endregion
#region 辅助方法
/// <summary>
/// 根据标题名称获取标题行下标位置
/// </summary>
/// <param name="sheet">要查找的sheet</param>
/// <param name="titleNames">标题名称</param>
/// <returns></returns>
private static int GetSheetTitleIndex(ISheet sheet, params string[] titleNames)
{
int titleIndex = -1;
if (sheet != null && titleNames != null && titleNames.Length > 0)
{
var rows = sheet.GetRowEnumerator();
List<ICell> cellList = null;
List<string> rowValueList = null;
//从第1行数据开始获取
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
cellList = row.Cells;
rowValueList = new List<string>(cellList.Count);
foreach (var cell in cellList)
{
rowValueList.Add(cell.ToString());
}
bool isTitle = true;
foreach (var title in titleNames)
{
if (!rowValueList.Contains(title))
{
isTitle = false;
break;
}
}
if (isTitle)
{
titleIndex = row.RowNum;
break;
}
}
}
return titleIndex;
}
#endregion
}
2-ExcelCellExpressReadAttribute 单元格表达式读取特性：
/// <summary> /// Excel单元格-表达式读取-标记特性 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)] public class ExcelCellExpressReadAttribute : System.Attribute { /// <summary> /// 读取数据使用：该参数使用表达式生成数据（Excel文件中支持的表达式均可以，可以是单元格位置也可以是表达式（如：A1,B2,C1+C2...）） /// </summary> public string CellCoordinateExpress { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 生成单元格表达式读取特性 /// </summary> /// <param name="cellCoordinateExpress">初始单元格表达式</param> /// <param name="outputFormat">（可选）格式化字符串</param> public ExcelCellExpressReadAttribute(string cellCoordinateExpress, string outputFormat = "") { this.CellCoordinateExpress = cellCoordinateExpress; this.OutputFormat = outputFormat; } }
3-ExcelCellFieldMapper 单元格字段映射类
/// <summary> /// 单元格字段映射类 /// </summary> internal class ExcelCellFieldMapper { /// <summary> /// 属性信息(一个属性可以添加一个表达式读取，多个变量替换和多个坐标写入) /// </summary> public PropertyInfo PropertyInfo { get; set; } /// <summary> /// 单元格—表达式读取（单元格坐标表达式（如：A1,B2,C1+C2...横坐标使用26进制字母，纵坐标使用十进制数字）） /// </summary> public ExcelCellExpressReadAttribute CellExpressRead { get; set; } /// <summary> /// 单元格—模板文件的预定义变量写入（{A} {B}） /// </summary> public List<ExcelCellParamWriteAttribute> CellParamWriteList { get; set; } /// <summary> /// 单元格—坐标位置写入（(0,0),(1,1)） /// </summary> public List<ExcelCellPointWriteAttribute> CellPointWriteList { get; set; } /// <summary> /// 获取对应关系_T属性添加了单元格映射关系 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static List<ExcelCellFieldMapper> GetModelFieldMapper<T>() { List<ExcelCellFieldMapper> fieldMapperList = new List<ExcelCellFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); ExcelCellExpressReadAttribute cellExpress = null; List<ExcelCellParamWriteAttribute> cellParamWriteList = null; List<ExcelCellPointWriteAttribute> cellPointWriteList = null; foreach (var item in tPropertyInfoList) { cellExpress = item.GetCustomAttribute<ExcelCellExpressReadAttribute>(); cellParamWriteList = item.GetCustomAttributes<ExcelCellParamWriteAttribute>().ToList(); cellPointWriteList = item.GetCustomAttributes<ExcelCellPointWriteAttribute>().ToList(); if (cellExpress != null || cellParamWriteList.Count > 0 || cellPointWriteList.Count > 0) { fieldMapperList.Add(new ExcelCellFieldMapper { CellExpressRead = cellExpress, CellParamWriteList = cellParamWriteList, CellPointWriteList = cellPointWriteList, PropertyInfo = item }); } } return fieldMapperList; } }
4-ExcelCellParamWriteAttribute Excel单元格-模板参数写入-标记特性
/// <summary> /// Excel单元格-模板参数写入-标记特性 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = true)] public class ExcelCellParamWriteAttribute : System.Attribute { /// <summary> /// 模板文件的预定义变量使用（{A} {B}） /// </summary> public string CellParamName { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } public ExcelCellParamWriteAttribute(string cellParamName, string outputFormat = "") { CellParamName = cellParamName; OutputFormat = outputFormat; } }
5-ExcelCellPointWriteAttribute Excel单元格-表达式读取-标记特性
/// <summary> /// Excel单元格-表达式读取-标记特性 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = true)] public class ExcelCellPointWriteAttribute : System.Attribute { /// <summary> /// 单元格位置（A3,B4...） /// </summary> public string CellPosition { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } public ExcelCellPointWriteAttribute(string cellPosition, string outputFormat = null) { CellPosition = cellPosition; OutputFormat = outputFormat; } }
6-ExcelFileDescription Excel文件描述类，含有深度更新策略
public class ExcelFileDescription { public ExcelFileDescription(int titleRowIndex) { this.TitleRowIndex = titleRowIndex; } public ExcelFileDescription(IExcelDeepUpdate excelDeepUpdate) { this.ExcelDeepUpdateList = new List<IExcelDeepUpdate> { excelDeepUpdate }; } public ExcelFileDescription(List<IExcelDeepUpdate> excelDeepUpdateList) { this.ExcelDeepUpdateList = excelDeepUpdateList; } /// <summary> /// 标题所在行位置（默认为0，没有标题填-1） /// </summary> public int TitleRowIndex { get; set; } /// <summary> /// Excel深度更新策略 /// </summary> public List<IExcelDeepUpdate> ExcelDeepUpdateList { get; set; } }
7-ExcelTitleAttribute Excel标题标记特性
/// <summary> /// Excel标题标记特性 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)] public class ExcelTitleAttribute : System.Attribute { /// <summary> /// Excel行标题(标题和下标选择一个即可) /// </summary> public string RowTitle { get; set; } /// <summary> /// Excel行下标(标题和下标选择一个即可,默认值-1) /// </summary> public int RowTitleIndex { get; set; } /// <summary> /// 单元格是否要检查空数据（true为检查，为空的行数据不添加） /// </summary> public bool IsCheckContentEmpty { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 是否是公式列 /// </summary> public bool IsCoordinateExpress { get; set; } /// <summary> /// 标题特性构造方法 /// </summary> /// <param name="title">标题</param> /// <param name="isCheckEmpty">单元格是否要检查空数据</param> /// <param name="isCoordinateExpress">是否是公式列</param> /// <param name="outputFormat">是否有格式化输出要求</param> public ExcelTitleAttribute(string title, bool isCheckEmpty = false, bool isCoordinateExpress = false, string outputFormat = "") { RowTitle = title; IsCheckContentEmpty = isCheckEmpty; IsCoordinateExpress = isCoordinateExpress; OutputFormat = outputFormat; RowTitleIndex = -1; } public ExcelTitleAttribute(int titleIndex, bool isCheckEmpty = false, bool isCoordinateExpress = false, string outputFormat = "") { RowTitleIndex = titleIndex; IsCheckContentEmpty = isCheckEmpty; IsCoordinateExpress = isCoordinateExpress; OutputFormat = outputFormat; } }
8-ExcelTitleFieldMapper 标题字段映射类
/// <summary> /// 标题字段映射类 /// </summary> internal class ExcelTitleFieldMapper { /// <summary> /// 属性信息 /// </summary> public PropertyInfo PropertyInfo { get; set; } /// <summary> /// 行标题 /// </summary> public string ExcelTitle { get; set; } /// <summary> /// 行标题下标位置 /// </summary> public int ExcelTitleIndex { get; set; } /// <summary> /// 是否要做行内容空检查 /// </summary> public bool IsCheckContentEmpty { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 是否是公式列 /// </summary> public bool IsCoordinateExpress { get; set; } /// <summary> /// 获取对应关系_T属性添加了标题映射关系 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>() { List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); ExcelTitleAttribute excelTitleAttribute = null; foreach (var tPropertyInfo in tPropertyInfoList) { excelTitleAttribute = (ExcelTitleAttribute)tPropertyInfo.GetCustomAttribute(typeof(ExcelTitleAttribute)); if (excelTitleAttribute != null) { fieldMapperList.Add(new ExcelTitleFieldMapper { PropertyInfo = tPropertyInfo, ExcelTitle = excelTitleAttribute.RowTitle, ExcelTitleIndex = excelTitleAttribute.RowTitleIndex, IsCheckContentEmpty = excelTitleAttribute.IsCheckContentEmpty, OutputFormat = excelTitleAttribute.OutputFormat, IsCoordinateExpress = excelTitleAttribute.IsCoordinateExpress }); } } return fieldMapperList; } /// <summary> /// 获取对应关系_手动提供映射关系 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="fieldNameAndShowNameDic"></param> /// <returns></returns> public static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>(Dictionary<string, string> fieldNameAndShowNameDic) { List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); PropertyInfo propertyInfo = null; foreach (var item in fieldNameAndShowNameDic) { propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(item.Key, StringComparison.OrdinalIgnoreCase)); fieldMapperList.Add(new ExcelTitleFieldMapper { PropertyInfo = propertyInfo, ExcelTitle = item.Value, ExcelTitleIndex = -1, OutputFormat = null, IsCheckContentEmpty = false, IsCoordinateExpress = false }); } return fieldMapperList; } /// <summary> /// 获取对应关系_未提供（默认属性名和标题名一致） /// </summary> /// <returns></returns> public static List<ExcelTitleFieldMapper> GetModelDefaultFieldMapper<T>() { List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); foreach (var item in tPropertyInfoList) { fieldMapperList.Add(new ExcelTitleFieldMapper { PropertyInfo = item, ExcelTitle = item.Name, ExcelTitleIndex = -1, OutputFormat = null, IsCheckContentEmpty = false, IsCoordinateExpress = false }); } return fieldMapperList; } }
接口封装类：
a-CellFactory 单元格工厂类
/// <summary> /// 单元格工厂类 /// </summary> public class CellFactory { private static Regex _CellPostionRegex = new Regex("[A-Z]+\\d+"); private static Regex _RowRegex = new Regex("\\d+"); /// <summary> /// 通过Excel单元格坐标位置初始化对象 /// </summary> /// <param name="excelCellPosition">A1,B2等等</param> /// <returns></returns> public static ICellModel GetCellByExcelPosition(string excelCellPosition) { CellModel cellModel = null; bool isMatch = CellFactory._CellPostionRegex.IsMatch(excelCellPosition); if (isMatch) { Match rowMath = CellFactory._RowRegex.Match(excelCellPosition); int rowPositon = Convert.ToInt32(rowMath.Value); int rowIndex = rowPositon - 1; int columnIndex = CellFactory.GetExcelColumnIndex(excelCellPosition.Replace(rowPositon.ToString(), "")); cellModel = new CellModel(rowIndex, columnIndex); } return cellModel; } /// <summary> /// 将数据放入单元格中 /// </summary> /// <param name="cell">单元格对象</param> /// <param name="cellValue">数据</param> /// <param name="outputFormat">格式化字符串</param> /// <param name="isCoordinateExpress">是否是表达式数据</param> public static void SetCellValue(ICell cell, object cellValue, string outputFormat, bool isCoordinateExpress) { if (cell != null) { if (isCoordinateExpress) { cell.SetCellFormula(cellValue.ToString()); } else { if (!string.IsNullOrEmpty(outputFormat)) { string formatValue = null; IFormatProvider formatProvider = null; if (cellValue is DateTime) { formatProvider = new DateTimeFormatInfo(); ((DateTimeFormatInfo)formatProvider).ShortDatePattern = outputFormat; } formatValue = ((IFormattable)cellValue).ToString(outputFormat, formatProvider); cell.SetCellValue(formatValue); } else
栏目列表
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
SQL SERVER中递归
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
这是目前我见过最好的跨域解决方案！
减少回流与重绘
减少回流与重绘
如何使用KrpanoToolJS在浏览器切图
performance.now() 与 Date.now() 对比
一款纯 JS 实现的轻量化图片编辑器
关于开发 VS Code 插件遇到的 workbench.scm.
前端设计模式——观察者模式
前端设计模式——中介者模式
创建型-原型模式