C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式收集数据&单元格映射&标题映射&模板文件的参数数据替换（第二版-增加深度读取和更新功能）
前言：新的的封装类，增加了单元格映射深度更新和读取的功能，预留了标题映射的深度更新接口待扩展。。。（以后有时间和精力再完善吧）
【深度更新】：我这里定义的深度更新策略，指的是：假如我们需要读取一组单元格的映射数据为一个对象，但是有不止一组这样的单元格数据对象，且这些对象的单元格位置排列是有规律的！
如：我要收集一个对象，在A1,A2，B1,B2的位置组成的一个数据对象，下一个对象位置在： A5,C6,B5,B6的位置，同理。。。
前面的文章介绍了使用单元格映射关系，我可以顺利收集到其中一个对象，但是我不可能把所有的单元格都建立对象关联起来，且数据又不符合标题行数据映射；那么就提出了一个新的策略，我这里叫：深度更新表达式读取策略。
下面放置完整代码，这版本做了深度更新的接口的抽象和封装，类有点多：
1-ExcelHelper 帮助类：
/// <summary>
/// EXCEL帮助类
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <typeparam name="TCollection">泛型类集合</typeparam>
public class ExcelHelper
{
private static Logger _Logger = LogManager.GetCurrentClassLogger();
public static IWorkbook GetExcelWorkbook(string filePath)
{
IWorkbook workbook = null;
try
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
try
{
workbook = new XSSFWorkbook(fileStream);
}
catch (Exception)
{
workbook = new HSSFWorkbook(fileStream);
}
}
}
catch (Exception e)
{
throw new Exception($"文件：{filePath}被占用！", e);
}
return workbook;
}
public static ISheet GetExcelWorkbookSheet(IWorkbook workbook, int sheetIndex = 0)
{
ISheet sheet = null;
if (workbook != null)
{
if (sheetIndex >= 0)
{
sheet = workbook.GetSheetAt(sheetIndex);
}
}
return sheet;
}
public static ISheet GetExcelWorkbookSheet(IWorkbook workbook, string sheetName = "sheet1")
{
ISheet sheet = null;
if (workbook != null && !string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
}
return sheet;
}
public static IRow GetOrCreateRow(ISheet sheet, int rowIndex)
{
IRow row = null;
if (sheet != null)
{
row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
}
return row;
}
public static ICell GetOrCreateCell(ISheet sheet, int rowIndex, int columnIndex)
{
ICell cell = null;
IRow row = ExcelHelper.GetOrCreateRow(sheet, rowIndex);
if (row != null)
{
cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
}
return cell;
}
/// <summary>
/// 根据单元格表达式和单元格数据集获取数据
/// </summary>
/// <param name="cellExpress">单元格表达式</param>
/// <param name="workbook">excel工作文件</param>
/// <param name="currentSheet">当前sheet</param>
/// <returns></returns>
public static object GetVByExpress(string cellExpress, IWorkbook workbook, ISheet currentSheet)
{
object value = null;
//含有单元格表达式的取表达式值，没有表达式的取单元格字符串
if (!string.IsNullOrEmpty(cellExpress) && workbook != null && currentSheet != null)
{
IFormulaEvaluator formulaEvaluator = null;
if (workbook is HSSFWorkbook)
{
formulaEvaluator = new HSSFFormulaEvaluator(workbook);
}
else
{
formulaEvaluator = new XSSFFormulaEvaluator(workbook);
}
//创建临时行，单元格，执行表达式运算；
IRow newRow = currentSheet.CreateRow(currentSheet.LastRowNum + 1);
ICell cell = newRow.CreateCell(0);
cell.SetCellFormula(cellExpress);
cell = formulaEvaluator.EvaluateInCell(cell);
value = cell.ToString();
currentSheet.RemoveRow(newRow);
}
return value ?? "";
}
#region 创建工作表
/// <summary>
/// 将列表数据生成工作表
/// </summary>
/// <param name="tList">要导出的数据集</param>
/// <param name="fieldNameAndShowNameDic">键值对集合（键：字段名，值：显示名称）</param>
/// <param name="workbook">更新时添加：要更新的工作表</param>
/// <param name="sheetName">指定要创建的sheet名称时添加</param>
/// <param name="excelFileDescription">读取或插入定制需求时添加</param>
/// <returns></returns>
public static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, Dictionary<string, string> fieldNameAndShowNameDic, IWorkbook workbook = null, string sheetName = "sheet1", ExcelFileDescription excelFileDescription = null) where T : new()
{
List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);
workbook = ExcelHelper.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription);
return workbook;
}
/// <summary>
/// 将列表数据生成工作表（T的属性需要添加：属性名列名映射关系）
/// </summary>
/// <param name="tList">要导出的数据集</param>
/// <param name="workbook">更新时添加：要更新的工作表</param>
/// <param name="sheetName">指定要创建的sheet名称时添加</param>
/// <param name="excelFileDescription">读取或插入定制需求时添加</param>
/// <returns></returns>
public static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, IWorkbook workbook = null, string sheetName = "sheet1", ExcelFileDescription excelFileDescription = null) where T : new()
{
List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>();
workbook = ExcelHelper.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription);
return workbook;
}
private static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, List<ExcelTitleFieldMapper> titleMapperList, IWorkbook workbook, string sheetName, ExcelFileDescription excelFileDescription = null)
{
CellModelColl cellModelColl = new CellModelColl(0);
int defaultBeginTitleIndex = 0;
if (excelFileDescription != null)
{
defaultBeginTitleIndex = excelFileDescription.TitleRowIndex;
}
//补全标题行映射数据的标题和下标位置映射关系
ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName: sheetName);
IRow titleRow = null;
if (sheet != null)
{
titleRow = sheet.GetRow(defaultBeginTitleIndex);
}
if (titleRow != null)
{
List<ICell> titleCellList = titleRow.Cells;
foreach (var titleMapper in titleMapperList)
{
if (titleMapper.ExcelTitleIndex < 0)
{
foreach (var cellItem in titleCellList)
{
if (cellItem.ToString().Equals(titleMapper.ExcelTitle, StringComparison.OrdinalIgnoreCase))
{
titleMapper.ExcelTitleIndex = cellItem.ColumnIndex;
break;
}
}
}
else if (string.IsNullOrEmpty(titleMapper.ExcelTitle))
{
ICell cell = titleRow.GetCell(titleMapper.ExcelTitleIndex);
if (cell != null)
{
titleMapper.ExcelTitle = cell.ToString();
}
}
}
}
else
{
//如果是新建Sheet页，则手动初始化下标关系
for (int i = 0; i < titleMapperList.Count; i++)
{
titleMapperList[i].ExcelTitleIndex = i;
}
}
int currentRowIndex = defaultBeginTitleIndex;
//添加标题单元格数据
foreach (var titleMapper in titleMapperList)
{
cellModelColl.Add(new CellModel
{
RowIndex = defaultBeginTitleIndex,
ColumnIndex = titleMapper.ExcelTitleIndex,
CellValue = titleMapper.ExcelTitle,
IsCellFormula = false
});
}
currentRowIndex++;
//将标题行数据转出单元格数据
foreach (var item in tList)
{
foreach (var titleMapper in titleMapperList)
{
cellModelColl.Add(new CellModel
{
RowIndex = currentRowIndex,
ColumnIndex = titleMapper.ExcelTitleIndex,
CellValue = titleMapper.PropertyInfo.GetValue(item),
IsCellFormula = titleMapper.IsCoordinateExpress
});
}
currentRowIndex++;
}
workbook = ExcelHelper.CreateOrUpdateWorkbook(cellModelColl, workbook, sheetName);
return workbook;
}
/// <summary>
/// 将单元格数据列表生成工作表
/// </summary>
/// <param name="commonCellList">所有的单元格数据列表</param>
/// <param name="workbook">更新时添加：要更新的工作表</param>
/// <param name="sheetName">指定要创建的sheet名称时添加</param>
/// <returns></returns>
public static IWorkbook CreateOrUpdateWorkbook(CellModelColl commonCellList, IWorkbook workbook = null, string sheetName = "sheet1")
{
//xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型；
//Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表；
//Excel 2007及以后版本，一个工作表最多可有1048576行，16384列；
if (workbook == null)
{
workbook = new XSSFWorkbook();
//workbook = new HSSFWorkbook();
}
ISheet worksheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName);
if (worksheet != null && commonCellList != null && commonCellList.Count > 0)
{
//设置首列显示
IRow row1 = null;
int rowIndex = 0;
int maxRowIndex = commonCellList.Max(m => m.RowIndex);
Dictionary<int, CellModel> rowColumnIndexCellDIC = null;
ICell cell = null;
object cellValue = null;
do
{
rowColumnIndexCellDIC = commonCellList.GetRawCellList(rowIndex).ToDictionary(m => m.ColumnIndex);
int maxColumnIndex = rowColumnIndexCellDIC.Count > 0 ? rowColumnIndexCellDIC.Keys.Max() : 0;
if (rowColumnIndexCellDIC != null && rowColumnIndexCellDIC.Count > 0)
{
row1 = worksheet.GetRow(rowIndex);
if (row1 == null)
{
row1 = worksheet.CreateRow(rowIndex);
}
int columnIndex = 0;
do
{
cell = row1.GetCell(columnIndex);
if (cell == null)
{
cell = row1.CreateCell(columnIndex);
}
if (rowColumnIndexCellDIC.ContainsKey(columnIndex))
{
cellValue = rowColumnIndexCellDIC[columnIndex].CellValue;
CellFactory.SetCellValue(cell, cellValue, outputFormat: null, rowColumnIndexCellDIC[columnIndex].IsCellFormula);
}
columnIndex++;
} while (columnIndex <= maxColumnIndex);
}
rowIndex++;
} while (rowIndex <= maxRowIndex);
//设置表达式重算（如果不添加该代码，表达式更新不出结果值）
worksheet.ForceFormulaRecalculation = true;
}
return workbook;
}
/// <summary>
/// 更新模板文件数据：将使用单元格映射的数据T存入模板文件中
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="t"></param>
/// <param name="excelFileDescription"></param>
/// <returns></returns>
public static IWorkbook UpdateTemplateWorkbook<T>(IWorkbook workbook, ISheet sheet, T t, ExcelFileDescription excelFileDescription = null)
{
//该方法默认替换模板数据在首个sheet里
CellModelColl commonCellColl = ExcelHelper.ReadCellList(workbook, sheet, false);
List<IExcelCellPointDeepUpdate> excelCellPointDeepList = new List<IExcelCellPointDeepUpdate>(0);
if (excelFileDescription != null)
{
excelCellPointDeepList.Add((IExcelCellPointDeepUpdate)excelFileDescription.ExcelDeepUpdateList);
}
//获取t的单元格映射列表
List<ExcelCellFieldMapper> cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();
foreach (var cellMapper in cellMapperList)
{
if (cellMapper.CellParamWriteList.Count > 0)
{
foreach (var cellParamWriteAttribute in cellMapper.CellParamWriteList)
{
CellModel cellModel = commonCellColl.GetCell(cellParamWriteAttribute.CellParamName);
if (cellModel != null)
{
cellModel.CellValue = cellMapper.PropertyInfo.GetValue(t);
}
}
}
if (cellMapper.CellPointWriteList.Count > 0)
{
object cellValue = cellMapper.PropertyInfo.GetValue(t);
ICellModel firstCellPosition = null;
foreach (var cellPointWriteAttribute in cellMapper.CellPointWriteList)
{
firstCellPosition = CellFactory.GetCellByExcelPosition(cellPointWriteAttribute.CellPosition);
CellFactory.SetDeepUpdateCellValue(sheet, firstCellPosition.RowIndex, firstCellPosition.ColumnIndex, cellValue, cellPointWriteAttribute.OutputFormat, false, excelCellPointDeepList);
}
}
}
workbook = ExcelHelper.CreateOrUpdateWorkbook(commonCellColl, workbook, sheet.SheetName);
return workbook;
}
#endregion
#region 保存工作表到文件
/// <summary>
/// 保存Workbook数据为文件
/// </summary>
/// <param name="workbook"></param>
/// <param name="fileDirectoryPath"></param>
/// <param name="fileName"></param>
public static void SaveWorkbookToFile(IWorkbook workbook, string filePath)
{
//xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型；
//Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表；
//Excel 2007及以后版本，一个工作表最多可有1048576行，16384列；
MemoryStream ms = new MemoryStream();
//这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致
ms.Seek(0, SeekOrigin.Begin);
workbook.Write(ms);
byte[] myByteArray = ms.GetBuffer();
string fileDirectoryPath = filePath.Split('\\')[0];
if (!Directory.Exists(fileDirectoryPath))
{
Directory.CreateDirectory(fileDirectoryPath);
}
string fileName = filePath.Replace(fileDirectoryPath, "");
if (File.Exists(filePath))
{
File.Delete(filePath);
}
File.WriteAllBytes(filePath, myByteArray);
}
/// <summary>
/// 保存workbook到字节流中(提供给API接口使用)
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static byte[] SaveWorkbookToByte(IWorkbook workbook)
{
MemoryStream stream = new MemoryStream();
stream.Seek(0, SeekOrigin.Begin);
workbook.Write(stream);
byte[] byteArray = stream.GetBuffer();
return byteArray;
}
#endregion
#region 读取Excel数据
/// <summary>
/// 读取Excel数据1_手动提供属性信息和标题对应关系
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath"></param>
/// <param name="fieldNameAndShowNameDic"></param>
/// <param name="excelFileDescription"></param>
/// <returns></returns>
public static List<T> ReadTitleDataList<T>(string filePath, Dictionary<string, string> fieldNameAndShowNameDic, ExcelFileDescription excelFileDescription) where T : new()
{
//标题属性字典列表
List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);
List<T> tList = ExcelHelper._GetTList<T>(filePath, titleMapperList, excelFileDescription);
return tList ?? new List<T>(0);
}
/// <summary>
/// 读取Excel数据2_使用Excel标记特性和文件描述自动创建关系
/// </summary>
/// <param name="filePath"></param>
/// <param name="excelFileDescription"></param>
/// <returns></returns>
public static List<T> ReadTitleDataList<T>(string filePath, ExcelFileDescription excelFileDescription) where T : new()
{
//标题属性字典列表
List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>();
List<T> tList = ExcelHelper._GetTList<T>(filePath, titleMapperList, excelFileDescription);
return tList ?? new List<T>(0);
}
private static List<T> _GetTList<T>(string filePath, List<ExcelTitleFieldMapper> titleMapperList, ExcelFileDescription excelFileDescription) where T : new()
{
List<T> tList = new List<T>(500 * 10000);
T t = default(T);
try
{
IWorkbook workbook = ExcelHelper.GetExcelWorkbook(filePath);
IFormulaEvaluator formulaEvaluator = null;
if (workbook is XSSFWorkbook)
{
formulaEvaluator = new XSSFFormulaEvaluator(workbook);
}
else if (workbook is HSSFWorkbook)
{
formulaEvaluator = new HSSFFormulaEvaluator(workbook);
}
int sheetCount = workbook.NumberOfSheets;
int currentSheetIndex = 0;
int currentSheetRowTitleIndex = -1;
do
{
var sheet = workbook.GetSheetAt(currentSheetIndex);
//标题下标属性字典
Dictionary<int, ExcelTitleFieldMapper> sheetTitleIndexPropertyDic = new Dictionary<int, ExcelTitleFieldMapper>(0);
//如果没有设置标题行，则通过自动查找方法获取
if (excelFileDescription.TitleRowIndex < 0)
{
string[] titleArray = titleMapperList.Select(m => m.ExcelTitle).ToArray();
currentSheetRowTitleIndex = ExcelHelper.GetSheetTitleIndex(sheet, titleArray);
}
else
{
currentSheetRowTitleIndex = excelFileDescription.TitleRowIndex;
}
var rows = sheet.GetRowEnumerator();
bool isHaveTitleIndex = false;
//含有Excel行下标
if (titleMapperList.Count > 0 && titleMapperList[0].ExcelTitleIndex >= 0)
{
isHaveTitleIndex = true;
foreach (var titleMapper in titleMapperList)
{
sheetTitleIndexPropertyDic.Add(titleMapper.ExcelTitleIndex, titleMapper);
}
}
PropertyInfo propertyInfo = null;
int currentRowIndex = 0;
if (currentSheetRowTitleIndex >= 0)
{
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
currentRowIndex = row.RowNum;
//到达标题行（寻找标题行映射）
if (isHaveTitleIndex == false && currentRowIndex == currentSheetRowTitleIndex)
{
ICell cell = null;
string cellValue = null;
Dictionary<string, ExcelTitleFieldMapper> titleMapperDic = titleMapperList.ToDictionary(m => m.ExcelTitle);
for (int i = 0; i < row.Cells.Count; i++)
{
cell = row.Cells[i];
cellValue = cell.StringCellValue;
if (titleMapperDic.ContainsKey(cellValue))
{
sheetTitleIndexPropertyDic.Add(i, titleMapperDic[cellValue]);
}
}
}
//到达内容行
if (currentRowIndex > currentSheetRowTitleIndex)
{
t = new T();
ExcelTitleFieldMapper excelTitleFieldMapper = null;
foreach (var titleIndexItem in sheetTitleIndexPropertyDic)
{
ICell cell = row.GetCell(titleIndexItem.Key);
excelTitleFieldMapper = titleIndexItem.Value;
//没有数据的单元格默认为null
string cellValue = cell?.ToString() ?? "";
propertyInfo = excelTitleFieldMapper.PropertyInfo;
try
{
if (excelTitleFieldMapper.IsCheckContentEmpty)
{
if (string.IsNullOrEmpty(cellValue))
{
t = default(T);
break;
}
}
if (excelTitleFieldMapper.IsCoordinateExpress || cell.CellType == CellType.Formula)
{
//读取含有表达式的单元格值
cellValue = formulaEvaluator.Evaluate(cell).StringValue;
propertyInfo.SetValue(t, Convert.ChangeType(cellValue, propertyInfo.PropertyType));
}
else if (propertyInfo.PropertyType.IsEnum)
{
object enumObj = propertyInfo.PropertyType.InvokeMember(cellValue, BindingFlags.GetField, null, null, null);
propertyInfo.SetValue(t, Convert.ChangeType(enumObj, propertyInfo.PropertyType));
}
else
{
propertyInfo.SetValue(t, Convert.ChangeType(cellValue, propertyInfo.PropertyType));
}
}
catch (Exception e)
{
ExcelHelper._Logger.Debug($"文件_{filePath}读取{currentRowIndex + 1}行内容失败！");
t = default(T);
break;
}
}
if (t != null)
{
tList.Add(t);
}
}
}
}
currentSheetIndex++;
} while (currentSheetIndex + 1 <= sheetCount);
}
catch (Exception e)
{
throw new Exception($"文件：{filePath}被占用！", e);
}
return tList ?? new List<T>(0);
}
public static CellModelColl ReadCellList(IWorkbook workbook, ISheet sheet, bool isRunFormula = false)
{
CellModelColl commonCells = new CellModelColl(10000);
IFormulaEvaluator formulaEvaluator = null;
if (workbook != null)
{
if (workbook is HSSFWorkbook)
{
formulaEvaluator = new HSSFFormulaEvaluator(workbook);
}
else
{
formulaEvaluator = new XSSFFormulaEvaluator(workbook);
}
}
if (sheet != null)
{
CellModel cellModel = null;
var rows = sheet.GetRowEnumerator();
//从第1行数据开始获取
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
List<ICell> cellList = row.Cells;
ICell cell = null;
foreach (var cellItem in cellList)
{
cell = cellItem;
if (isRunFormula && cell.CellType == CellType.Formula)
{
cell = formulaEvaluator.EvaluateInCell(cell);
}
cellModel = new CellModel
{
RowIndex = cell.RowIndex,
ColumnIndex = cell.ColumnIndex,
CellValue = cell.ToString(),
IsCellFormula = cell.CellType == CellType.Formula
};
commonCells.Add(cellModel);
}
}
}
return commonCells;
}
/// <summary>
/// 获取文件单元格数据对象
/// </summary>
/// <typeparam name="T">T的属性必须标记了ExcelCellAttribute</typeparam>
/// <param name="filePath">文建路径</param>
/// <param name="sheetIndex">（可选）sheet所在位置</param>
/// <param name="sheetName">（可选）sheet名称</param>
/// <returns></returns>
public static T ReadCellData<T>(IWorkbook workbook, ISheet sheet) where T : new()
{
T t = new T();
if (workbook != null)
{
if (sheet != null)
{
Dictionary<PropertyInfo, ExcelCellFieldMapper> propertyMapperDic = ExcelCellFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.PropertyInfo);
string cellExpress = null;
string pValue = null;
PropertyInfo propertyInfo = null;
foreach (var item in propertyMapperDic)
{
cellExpress = item.Value.CellExpressRead.CellCoordinateExpress;
propertyInfo = item.Key;
pValue = ExcelHelper.GetVByExpress(cellExpress, workbook, sheet).ToString();
if (!string.IsNullOrEmpty(pValue))
{
try
{
propertyInfo.SetValue(t, Convert.ChangeType(pValue, propertyInfo.PropertyType));
}
catch (Exception)
{
throw;
}
}
}
}
}
return t;
}
/// <summary>
/// 读取单元格数据对象列表-支持深度读取
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="excelFileDescription"></param>
/// <returns></returns>
public static List<T> ReadCellData<T>(IWorkbook workbook, ISheet sheet, ExcelFileDescription excelFileDescription) where T : new()
{
List<T> tList = new List<T>(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
}
View Code
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;
}
}
View Code
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;
}
}
View Code
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;
}
}
View Code
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;
}
}
View Code
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; }
}
View Code
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;
}
}
View Code
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;
}
}
View Code
接口封装类：
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
{
if (cellValue is decimal || cellValue is double || cellValue is int)
{
cell.SetCellValue(Convert.ToDouble(cellValue));
}
else if (cellValue is DateTime)
{
cell.SetCellValue((DateTime)cellValue);
}
else if (cellValue is bool)
{
cell.SetCellValue((bool)cellValue);
}
else
{
cell.SetCellValue(cellValue.ToString());
}
}
}
}
}
public static void SetDeepUpdateCellValue(ISheet sheet, int rowIndex, int columnIndex, object cellValue, string outputFormat, bool isCoordinateExpress, List<IExcelCellPointDeepUpdate> excelDeepUpdateList)
{
if (sheet != null)
{
//更新起始单元格数据
ICell nextCell = ExcelHelper.GetOrCreateCell(sheet, rowIndex, columnIndex);
CellFactory.SetCellValue(nextCell, cellValue, outputFormat, isCoordinateExpress);
#region 执行单元格深度更新策略
ICellModel startCellPosition = new CellModel
{
RowIndex = rowIndex,
ColumnIndex = columnIndex
};
ICellModel nextCellPosition = null;
Action<IExcelCellPointDeepUpdate> actionDeepUpdateAction = (excelDeepUpdate) =>
{
//获取起始执行单元格位置
nextCellPosition = excelDeepUpdate.GetNextCellPoint(startCellPosition);
//执行深度更新，一直到找不到下个单元格为止
do
{
nextCell = ExcelHelper.GetOrCreateCell(sheet, nextCellPosition.RowIndex, nextCellPosition.ColumnIndex);
if (nextCell != null)
{
CellFactory.SetCellValue(nextCell, cellValue, outputFormat, isCoordinateExpress);
nextCellPosition = excelDeepUpdate.GetNextCellPoint(nextCellPosition);
}
} while (nextCell != null);
};
foreach (var excelDeepUpdate in excelDeepUpdateList)
{
actionDeepUpdateAction(excelDeepUpdate);
}
#endregion
}
}
/// <summary>
/// 数字转字母
/// </summary>
/// <param name="columnIndex"></param>
/// <returns></returns>
public static string GetExcelColumnPosition(int number)
{
var a = number / 26;
var b = number % 26;
if (a > 0)
{
return CellFactory.GetExcelColumnPosition(a - 1) + (char)(b + 65);
}
else
{
return ((char)(b + 65)).ToString();
}
}
/// <summary>
/// 字母转数字
/// </summary>
/// <param name="columnPosition"></param>
/// <returns></returns>
public static int GetExcelColumnIndex(string zm)
{
int index = 0;
char[] chars = zm.ToUpper().ToCharArray();
for (int i = 0; i < chars.Length; i++)
{
index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
}
return index - 1;
}
}
View Code
b-CellModel 单元格定义类
public class CellModel : ICellModel
{
public int RowIndex { get; set; }
public int ColumnIndex { get; set; }
public object CellValue { get; set; }
public bool IsCellFormula { get; set; }
public CellModel() { }
/// <summary>
/// 默认初始化对象
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="cellValue"></param>
public CellModel(int rowIndex, int columnIndex, object cellValue = default(object)) : this(rowIndex, columnIndex, cellValue, false)
{
}
/// <summary>
/// 默认初始化对象
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="cellValue"></param>
/// <param name="isCellFormula"></param>
public CellModel(int rowIndex, int columnIndex, object cellValue, bool isCellFormula)
{
this.RowIndex = rowIndex;
this.ColumnIndex = columnIndex;
this.CellValue = cellValue;
this.IsCellFormula = isCellFormula;
}
/// <summary>
/// 获取单元格位置
/// </summary>
/// <returns></returns>
public string GetCellPosition()
{
return CellFactory.GetExcelColumnPosition(this.ColumnIndex) + (this.RowIndex + 1).ToString();
}
}
public class CellModelColl : List<CellModel>, IList<CellModel>
{
public CellModelColl() { }
public CellModelColl(int capacity) : base(capacity)
{
}
/// <summary>
/// 根据行下标，列下标获取单元格数据
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public CellModel this[int rowIndex, int columnIndex]
{
get
{
CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
return cell;
}
set
{
CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
if (cell != null)
{
cell.CellValue = value.CellValue;
}
}
}
public CellModel CreateOrGetCell(int rowIndex, int columnIndex)
{
CellModel cellModel = this[rowIndex, columnIndex];
if (cellModel == null)
{
cellModel = new CellModel()
{
RowIndex = rowIndex,
ColumnIndex = columnIndex
};
this.Add(cellModel);
}
return cellModel;
}
public CellModel GetCell(string cellStringValue)
{
CellModel cellModel = null;
cellModel = this.FirstOrDefault(m => m.CellValue.ToString().Equals(cellStringValue, System.StringComparison.OrdinalIgnoreCase));
return cellModel;
}
/// <summary>
/// 所有一行所有的单元格数据
/// </summary>
/// <param name="rowIndex">行下标</param>
/// <returns></returns>
public List<CellModel> GetRawCellList(int rowIndex)
{
List<CellModel> cellList = null;
cellList = this.FindAll(m => m.RowIndex == rowIndex);
return cellList ?? new List<CellModel>(0);
}
/// <summary>
/// 所有一列所有的单元格数据
/// </summary>
/// <param name="columnIndex">列下标</param>
/// <returns></returns>
public List<CellModel> GetColumnCellList(int columnIndex)
{
List<CellModel> cellList = null;
cellList = this.FindAll(m => m.ColumnIndex == columnIndex);
return cellList ?? new List<CellModel>(0);
}
}
View Code
c-ExcelCellExpressDeepUpdate<T> 单元格表达式深度更新类
public class ExcelCellExpressDeepUpdate<T> : IExcelCellExpressDeepUpdate<T>
{
private Regex cellPointRegex = new Regex("[A-Z]+[0-9]+");
private Action<ICellModel> updateCellPointFunc { get; set; }
public Func<T, bool> CheckContinuteFunc { get; set; }
public ExcelCellExpressDeepUpdate(Action<ICellModel> updateCellPointFunc, Func<T, bool> checkIsContinuteFunc)
{
this.updateCellPointFunc = updateCellPointFunc;
this.CheckContinuteFunc = checkIsContinuteFunc;
}
public bool IsContinute(T t)
{
return this.CheckContinuteFunc(t);
}
public string GetNextCellExpress(string currentExpress)
{
string nextCellExpress = currentExpress;
List<ICellModel> cellModelList = this.GetCellModelList(currentExpress);
string oldPointStr = null;
string newPointStr = null;
foreach (var item in cellModelList)
{
oldPointStr = item.GetCellPosition();
this.updateCellPointFunc(item);
newPointStr = item.GetCellPosition();
nextCellExpress = nextCellExpress.Replace(oldPointStr, newPointStr);
}
return nextCellExpress;
}
private List<ICellModel> GetCellModelList(string cellExpress)
{
List<ICellModel> cellModelList = new List<ICellModel>(100);
MatchCollection matchCollection = this.cellPointRegex.Matches(cellExpress);
foreach (Match matchItem in matchCollection)
{
cellModelList.Add(CellFactory.GetCellByExcelPosition(matchItem.Value));
}
return cellModelList;
}
}
View Code
d-ExcelCellPointDeepUpdate 单元格坐标深度更新类
public class ExcelCellPointDeepUpdate : IExcelCellPointDeepUpdate
{
private Action<ICellModel> updateCellPointFunc { get; set; }
public ExcelCellPointDeepUpdate(Action<ICellModel> updateCellPointFunc)
{
this.updateCellPointFunc = updateCellPointFunc;
}
public ICellModel GetNextCellPoint(ICellModel cellModel)
{
ICellModel nextCell = null;
ICellModel cell = new CellModel(cellModel.RowIndex, cellModel.ColumnIndex);
if (cellModel != null && this.updateCellPointFunc != null)
{
this.updateCellPointFunc(cell);
if (cell.RowIndex != cellModel.RowIndex || cell.ColumnIndex != cellModel.ColumnIndex)
{
nextCell = cell;
}
}
return nextCell;
}
}
View Code
e-ICellModel 单元格抽象接口
public interface ICellModel
{
int RowIndex { get; set; }
int ColumnIndex { get; set; }
object CellValue { get; set; }
bool IsCellFormula { get; set; }
string GetCellPosition();
}
View Code
f-IExcelCellDeepUpdate 单元格深度更新接口
/// <summary>
/// 单元格深度更新接口
/// </summary>
public interface IExcelCellDeepUpdate : IExcelDeepUpdate
{
}
View Code
g-IExcelCellExpressDeepUpdate<T> 单元格表达式深度更新接口
public interface IExcelCellExpressDeepUpdate<T> : IExcelCellDeepUpdate
{
string GetNextCellExpress(string currentExpress);
bool IsContinute(T t);
}
View Code
h-IExcelCellPointDeepUpdate 单元格坐标深度更新接口
/// <summary>
/// 单元格坐标深度更新接口
/// </summary>
public interface IExcelCellPointDeepUpdate : IExcelCellDeepUpdate
{
ICellModel GetNextCellPoint(ICellModel cellModel);
}
View Code
i-IExcelDeepUpdate Excel深度更新大抽象接口
/// <summary>
/// Excel深度更新策略接口
/// </summary>
public interface IExcelDeepUpdate
{
}
View Code
j-IExcelTitleDeepUpdate Excel标题深度更新接口
/// <summary>
/// Excel标题深度更新策略
/// </summary>
public interface IExcelTitleDeepUpdate : IExcelDeepUpdate
{
}
View Code
深度更新使用示例一：
string path = @"C:\Users\Administrator\Desktop\控制台测试\Test\WebApplication1\WebApplication1\2020年2月 paypal凭证.xlsx";
ExcelFileDescription excelFileDescription = new ExcelFileDescription(new ExcelCellExpressDeepUpdate<AccountMultiCurrencyTransactionSource_Summary>(m => m.RowIndex += 15, m => m.BeginingBalance > 0));
IWorkbook workbook = ExcelHelper.GetExcelWorkbook(path);
ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName: "chictoo+7");
List<AccountMultiCurrencyTransactionSource_Summary> dataList = ExcelHelper.ReadCellData<AccountMultiCurrencyTransactionSource_Summary>(workbook, sheet, excelFileDescription);
/// <summary>
/// 账户_多币种交易报表_数据源
/// </summary>
public class AccountMultiCurrencyTransactionSource_Summary
{
[ExcelCellExpressRead("A2")]
public string AccountName { get; set; }
/// <summary>
/// 期初
/// </summary>
[ExcelCellExpressReadAttribute("B3")]
public double BeginingBalance { get; set; }
/// <summary>
/// 收款
/// </summary>
[ExcelCellExpressReadAttribute("B4")]
[ExcelTitle(3)]
public double TotalTransactionPrice { get; set; }
}
总结：时间有限，没有来得及进行深度的抽象和优化，优化有机会再继续吧。
/// <summary>
/// 账户_多币种交易报表_数据源
/// </summary>
public class AccountMultiCurrencyTransactionSource_Summary
{
[ExcelCellExpressRead("A2")]
public string AccountName { get; set; }
/// <summary>
/// 期初
/// </summary>
[ExcelCellExpressReadAttribute("B3")]
public double BeginingBalance { get; set; }
/// <summary>
/// 收款
/// </summary>
[ExcelCellExpressReadAttribute("B4")]
[ExcelTitle(3)]
public double TotalTransactionPrice { get; set; }
}
赞 (0)