public static ExcelRange WriteToColumn(this WorksheetWithManualHeaders sheet,
int row,
int column,
object value,
string format = null)
{
var cell = sheet.Sheet.Cells[row, column];
if (string.IsNullOrEmpty(format))
{
if (value is DateTime)
{
cell.Style.Numberformat.Format = "dd/mm/yyyy";
}
if (value is decimal || value is double)
{
cell.AddDecimalFormat();
}
}
else
{
cell.Style.Numberformat.Format = format;
}
cell.Value = value;
return(cell);
}
public static WorksheetWithManualHeaders AddHeader(this WorksheetWithManualHeaders sheet,
params string[] headers)
{
foreach (var header in headers)
{
sheet.Headers.Add(header,
new ManualExcelHeader(TreatHeader(header),
GetHighestColumn(sheet.Headers.Values) + 1));
}
return(sheet);
}
public static WorksheetWithManualHeaders WriteHeaders(this WorksheetWithManualHeaders sheet,
int row = 1)
{
foreach (var excelHeader in sheet.Headers.Values)
{
var cell = sheet.Sheet.Cells[row,
excelHeader.Column];
cell.Value = excelHeader.Header;
cell.Style.Font.Bold = true;
}
return(sheet);
}
public static WorksheetWithManualHeaders LoadHeaders(this WorksheetWithManualHeaders sheet,
int headersRow)
{
foreach (var i in Enumerable.Range(1,
sheet.Sheet.Dimension.Columns))
{
var cell = sheet.Sheet.Cells[headersRow,
i];
var headerValue = cell.Value?.ToString();
if (!string.IsNullOrEmpty(headerValue))
{
sheet.AddHeader(headerValue);
}
}
return(sheet);
}
public static void WriteToColumn(this WorksheetWithManualHeaders sheet,
int row,
string columnHeader,
object value,
string format = null)
{
if (!sheet.Headers.ContainsKey(columnHeader))
{
return;
}
var header = sheet.Headers[columnHeader];
WriteToColumn(sheet,
row,
header.Column,
value,
format);
}
public static bool HasHeader(this WorksheetWithManualHeaders sheet,
params string[] headers)
{
return(headers.All(i => sheet.Headers.ContainsKey(i)));
}
public static int GetLastRow(this WorksheetWithManualHeaders manualHeaders) => GetLastRow(manualHeaders.Sheet);
public static void AutoFit(this WorksheetWithManualHeaders manualHeaders)
{
manualHeaders.Sheet.AutoFit();
}
