public ExcelHelper(string fileName, bool editable = true, int header = 1)
{
if (string.IsNullOrEmpty(fileName))
{
throw new Exception("Check if current filename is empty!");
}
if (!File.Exists(fileName))
{
throw new Exception("Please make sure current file exists!");
}
document = SpreadsheetDocument.Open(fileName, editable);
worksheet = document.GetWorksheet(0);
sheetData = worksheet.GetSheetData();
styleSheet = document.GetStylesheet();
this.Header = header;
this.cellFormatOption = CellFormatOption.OverWrite;
}
private void SetStyleIndexByCellFormatOption(Cell cell, object value, CellFormatOption option, MappingAttribute attribute)
{
int styleIndex = 0;
switch (cellFormatOption)
{
case CellFormatOption.KeepStyle:
break;
case CellFormatOption.OverWrite:
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
cell.SetCellStyle(styleIndex);
break;
case CellFormatOption.ReferenceStyle:
if (string.IsNullOrEmpty(attribute.ReferenceStyle))
{
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
}
else
{
Cell refCell = SpreadsheetExtender.GetCell(worksheet, attribute.ReferenceStyle);
if (refCell == null)
{
throw new ArgumentException("Please confirm the cell to reference is not null!");
}
if (refCell.StyleIndex == null || (refCell.StyleIndex != null && !refCell.StyleIndex.HasValue))
{
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
}
else
{
styleIndex = int.Parse(refCell.StyleIndex.ToString());
}
cell.SetCellStyle(styleIndex);
} break;
}
}
private void SetStyleIndexByCellFormatOption(Cell cell, object value, CellFormatOption option)
{
int styleIndex = 0;
switch (option)
{
case CellFormatOption.KeepStyle:
if (cell.StyleIndex != null && cell.StyleIndex.HasValue)
{
return;
}
if ((cell.StyleIndex == null || (cell.StyleIndex != null && !cell.StyleIndex.HasValue)))
{
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
}
cell.SetCellStyle(styleIndex); break;
case CellFormatOption.OverWrite:
case CellFormatOption.ReferenceStyle:
styleIndex = SpreadsheetExtender.GetCellStyleIndex(styleSheet, value);
cell.SetCellStyle(styleIndex); break;
}
}
