public void InsertAt(ExcelRangeBase range) { // Load data into cells first ... // --- There may be a slight performance hit with doing this before // formatting. range.LoadFromDataTable(this.Table, this.ShowHeaders); // ... then apply formatting. // --- This is second mainly so that table values themselves can be // modified or some kind of conditional formatting (not the kind // that's built-in with Excel) can be applied. var referenceRange = this.ShowHeaders ? range.Offset(1, 0) : range; foreach (var fmt in this.Formattings) { fmt.Apply(referenceRange); } }
public static ExcelRangeBase ToExcelSheet(this DataTable dataTable, ExcelWorksheet sheet, ExcelRangeBase start = null) { if (start == null) { start = sheet.Cells[@"A1"]; } var range = start.LoadFromDataTable(dataTable, true, OfficeOpenXml.Table.TableStyles.Medium23); var table = sheet.Tables.Where(t => t.Address.ToString() == range.Address.ToString()).Single(); // fix formatting of dates foreach (var col in dataTable.Columns.OfType <DataColumn>()) { if (col.DataType == typeof(DateTime)) { sheet.Column(col.Ordinal + range.Start.Column).Style.Numberformat.Format = @"YYYY-MM-dd" + (dataTable.Rows.OfType <DataRow>().Any(dr => !(dr[col] is DBNull) && ((DateTime)dr[col]).TimeOfDay.Ticks > 0) ? @" HH:mm:ss" : string.Empty); } } // freeze first row sheet.View.FreezePanes(range.Start.Row + 1, range.Start.Column); // expand column widths sheet.Cells[range.Address].AutoFitColumns(); return(range); }