/// <summary>
/// 根据excel流读取 - 表头（标题）关联生成动态数据集合
/// </summary>
/// <param name="excelStream"></param>
/// <param name="dynamicFieldMapper"></param>
/// <param name="sheetIndex"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static List <dynamic> Read(Stream excelStream, Dictionary <string, string> dynamicFieldMapper, int sheetIndex = 0)
{
if (!(dynamicFieldMapper?.Any() ?? false))
{
throw new Exception("表头对应关系不能为空！可以尝试不传该参数。");
}
var workbook = CreateWorkBook(excelStream);
var sheet = workbook.GetSheetAt(sheetIndex);
var headerMap = new Dictionary <int, string>();
var headerRow = sheet.GetRow(_defaultHeaderNum);
if (headerRow != null)
{
for (var i = 0; i < headerRow.PhysicalNumberOfCells; i++)
{
headerMap.Add(i, headerRow.GetCell(i)?.StringCellValue);
}
}
var startIndex = _defaultHeaderNum + 1;
var result = new List <dynamic>();
for (var i = startIndex; i < sheet.PhysicalNumberOfRows; i++)
{
var readRow = sheet.GetRow(i);
if (readRow != null)
{
var model = new ExcelDynamicObject();
for (var j = 0; j < readRow.PhysicalNumberOfCells; j++)
{
//字典存在映射关系才进行赋值
if (!headerMap.ContainsKey(j))
{
continue;
}
var curHeaderName = headerMap[j];
if (string.IsNullOrEmpty(curHeaderName) || !dynamicFieldMapper.ContainsKey(curHeaderName))
{
continue;
}
var cellValue = GetCellValue(readRow.GetCell(j));
model.AddProperty(dynamicFieldMapper[curHeaderName], cellValue);
}
result.Add(model);
}
}
return(result);
}
public void TestDynamicObjectExtension()
{
dynamic d = new ExcelDynamicObject();
d.AddProperty("item1", "item1");
d.AddProperty("item2", "item2");
var success = d is ExcelDynamicObject;
dynamic a = new Object();
success = a is ExcelDynamicObject;
Console.WriteLine(d.item1);
Console.WriteLine(d.item2);
}
/// <summary>
/// 根据excel流读取 - 位置关联生成动态数据集合
/// </summary>
/// <param name="excelStream"></param>
/// <param name="dynamicFieldMapper"></param>
/// <param name="sheetIndex"></param>
/// <param name="containsHeader"></param>
/// <returns></returns>
public static List <dynamic> Read(Stream excelStream, Dictionary <int, string> dynamicFieldMapper = null, int sheetIndex = 0, bool containsHeader = true)
{
var workbook = CreateWorkBook(excelStream);
var sheet = workbook.GetSheetAt(sheetIndex);
dynamicFieldMapper = GetDynamicReadMapper(dynamicFieldMapper, sheet.GetRow(0)?.PhysicalNumberOfCells ?? 0);
var startIndex = containsHeader ? _defaultHeaderNum + 1 : 0;
var result = new List <dynamic>();
for (var i = startIndex; i < sheet.PhysicalNumberOfRows; i++)
{
var readRow = sheet.GetRow(i);
if (readRow == null)
{
continue;
}
var model = new ExcelDynamicObject();
for (var j = 0; j < readRow.PhysicalNumberOfCells; j++)
{
//字典存在映射关系才进行赋值
if (!dynamicFieldMapper.ContainsKey(j))
{
continue;
}
var cellValue = GetCellValue(readRow.GetCell(j));
model.AddProperty(dynamicFieldMapper[j], cellValue);
}
result.Add(model);
}
return(result);
}
public void TestExport()
{
var random = new Random();
//
// var list = new List<TestExcelExportModel>();
// for (int i = 0; i < 10000; i++)
// {
// list.Add(new TestExcelExportModel
// {
// Id = Guid.NewGuid().ToString(),
// CreateByAt = DateTime.Now.AddDays(i),
// ExportEnum = (TestExcelExportEnum) random.Next(0, 2)
// });
// }
var list = new List <dynamic>();
for (int i = 0; i < 10000; i++)
{
var item = new ExcelDynamicObject();
item.AddProperty("Id", Guid.NewGuid().ToString());
item.AddProperty("CreateByAt", DateTime.Now.AddDays(i));
item.AddProperty("ExportEnum", (TestExcelExportEnum)random.Next(0, 3));
list.Add(item);
}
var result = ExcelHelper.Export(list, "template/2.xlsx"); //, "template/2.xlsx"
//, "template/2.xlsx"
Console.Write(result.Length);
Console.WriteLine(list[0].Id);
Console.WriteLine(list[0].CreateByAt);
Console.WriteLine(list[0].ExportEnum);
}
