/// <summary> /// 向excel写入数据表 /// </summary> public static ExcelPackage LoadFromDataTable(this ExcelPackage doc, DataTable dt, Action <DataTableOptions> dtOptionsAction) { Harry.Check.NotNull(doc, nameof(doc)); Harry.Check.NotNull(dt, nameof(dt)); var dtOptions = new DataTableOptions(); dtOptionsAction?.Invoke(dtOptions); dtOptions.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; if (dt.Rows != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (i % dtOptions.PageSize == 0) { //写入表头 rownum = 1; currentSheet = createSheet(doc, dt, ref rownum, $"{dt.TableName}({(++currentSheetIndex).ToString()})", dtOptions); } //写入数据行 writeRow(currentSheet, dt.Rows[i], rownum++, dtOptions); } } else { //无数据,写个表头然后退出 //写入表头 rownum = 1; currentSheet = createSheet(doc, dt, ref rownum, $"{dt.TableName}({(++currentSheetIndex).ToString()})", dtOptions); } return(doc); }
private static void writeRow(ExcelWorksheet sheet, DataRow dr, int rownum, DataTableOptions dtOptions) { if (dtOptions.DataHeight != null) { //设置行高 sheet.Row(rownum).Height = dtOptions.DataHeight.Value; } foreach (DataColumn column in dr.Table.Columns) { var cell = sheet.Cells[rownum, column.Ordinal + 1]; var value = dr[column]; if (value != null && value != DBNull.Value) { cell.Value = dr[column]; } ValidateCellStyle(cell, column.DataType); //设置单元格样式 dtOptions.DataCellAction?.Invoke(column, cell); } }
//创建一张新表,并写入表头信息 private static ExcelWorksheet createSheet(ExcelPackage doc, DataTable dt, ref int rownum, string sheetName, DataTableOptions dtOptions) { 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++; //写入字段信息 foreach (DataColumn column in dt.Columns) { var header = worksheet.Cells[rownum, column.Ordinal + 1]; header.Value = column.Caption; var excelColumn = worksheet.Column(column.Ordinal + 1); dtOptions.HeaderCellAction?.Invoke(column, excelColumn, header); } //var fieldNameCells = worksheet.Cells[rownum, 1, rownum, dt.Columns.Count]; //dtOptions.HeaderStyleAction?.Invoke(fieldNameCells.Style); if (dtOptions.HeaderHeight != null) { //设置header行高 worksheet.Row(rownum).Height = dtOptions.HeaderHeight.Value; } //冻结首行 if (dtOptions.FrozenHeader) { worksheet.View.FreezePanes(rownum + 1, 1); } rownum++; return(worksheet); }