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