/// <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); }