/// <summary> /// 向excel写入数据集合 /// </summary> public static ExcelPackage LoadFromEnumerableData <T>(this ExcelPackage doc, IEnumerable <T> data, Action <EnumerableDataOptions <T> > optionsAction) { Harry.Check.NotNull(doc, nameof(doc)); Harry.Check.NotNull(data, nameof(data)); var options = new EnumerableDataOptions <T>(); optionsAction?.Invoke(options); if (string.IsNullOrEmpty(options.TableName)) { options.TableName = "Sheet"; } options.WorkbookAction?.Invoke(doc.Workbook); //doc.Workbook.Worksheets.Add($"{dt.TableName}") // .Cells[1, 1].LoadFromDataTable(dt, false); //return; int rownum = 0; int currentSheetIndex = 0; ExcelWorksheet currentSheet = null; rownum = 1; currentSheet = createSheetForEnumerableData(doc, ref rownum, $"{options.TableName}({(++currentSheetIndex).ToString()})", options); int i = 0; foreach (var item in data) { //写入数据行 if (options.DataHeight != null) { //设置行高 currentSheet.Row(rownum).Height = options.DataHeight.Value; } options.DataRowAction?.Invoke(currentSheet, rownum, item); rownum++; i++; if (i % options.PageSize == 0) { //写入表头 rownum = 1; currentSheet = createSheetForEnumerableData(doc, ref rownum, $"{options.TableName}({(++currentSheetIndex).ToString()})", options); } } return(doc); }
private static ExcelWorksheet createSheetForEnumerableData <T>(ExcelPackage doc, ref int rownum, string sheetName, EnumerableDataOptions <T> options) { ExcelWorksheet worksheet = doc.Workbook.Worksheets.Add(doc.GetValidSheetName(sheetName)); ////写入表名称 //var titleCell = worksheet.Cells[rownum, 1]; //titleCell.Value = dt.TableName; ////设置标题样式 //dtOptions.TitleStyleAction?.Invoke(titleCell.Style); //rownum++; //写入表头信息 //if (options.HeaderRowActions != null && options.HeaderRowActions.Count > 0) //{ // for (int i = 1; i <= options.HeaderRowActions.Count; i++) // { // var header = worksheet.Cells[rownum, i]; // var excelColumn = worksheet.Column(i); // options.HeaderRowActions[i].Invoke(excelColumn, header); // } //} options.HeaderRowAction?.Invoke(worksheet, rownum); if (options.HeaderHeight != null) { //设置header行高 worksheet.Row(rownum).Height = options.HeaderHeight.Value; } //冻结首行 if (options.FrozenHeader) { worksheet.View.FreezePanes(rownum + 1, 1); } rownum++; return(worksheet); }