public List <T> GetSettlementItemsFromSheet <T>(string sheetName, SettlementHistoryWorkbook workbook) where T : SettlementItem, new()
{
List <T> items = new List <T>();
SettlementHistoryWorkbook.HelperSheet sheet = workbook[sheetName];
if (sheet != null)
{
Dictionary <string, PropertyInfo> columnProperties = GetColumnProperties <T>(sheet);
int rowIndex = 0;
foreach (SettlementHistoryWorkbook.HelperRow row in sheet.GetRows().Skip(2))
{
if (IsLastRow <T>(row))
{
break;
}
T item = new T();
item.SettlementId = _settlementId;
item.id = $"{_settlementId}-{rowIndex++}";
foreach (SettlementHistoryWorkbook.HelperCell cell in row.GetCells())
{
if (columnProperties.ContainsKey(cell.Name))
{
PropertyInfo property = columnProperties[cell.Name];
SetValue(property, item, cell);
}
}
items.Add(item);
}
}
return(items);
}
public HelperSheet(SettlementHistoryWorkbook parent, Doc.Worksheet worksheet, string sheetName)
{
_parent = parent;
_worksheet = worksheet;
this.SheetName = sheetName;
var rows = worksheet.GetFirstChild <Doc.SheetData>().Elements <Doc.Row>();
}
private static void FixTemplate(string template)
{
System.Console.WriteLine("Fixing template cells C32, AA31");
const string formula = "IF(B27>0,(0.04*B27)+20,0)";
using (var wb = new SettlementHistoryWorkbook(template))
{
for (int i = 1; i <= 52; i++)
{
string sheet = $"Week_{i}";
wb.UpdateCellValue(sheet, "C3", "");
wb.UpdateCellValue(sheet, "C32", "0");
wb.UpdateCellFormula(sheet, "AA31", formula);
}
wb.Save();
}
}
public SettlementHistory Parse()
{
try
{
SettlementHistoryWorkbook workbook = new SettlementHistoryWorkbook(_filename);
SettlementHistory settlement = new SettlementHistory();
settlement.SettlementId = this._settlementId;
settlement.Credits = GetCredits(workbook);
settlement.Deductions = GetDeductions(workbook);
settlement.SettlementDate = GetLastCreditDate(settlement);
return(settlement);
}
catch (Exception e)
{
System.Console.WriteLine($"Error parsing {_filename}: {_settlementId}. Error:\n\t{e.Message}");
return(null);
}
}
private List <Deduction> GetDeductions(SettlementHistoryWorkbook workbook)
{
return(GetSettlementItemsFromSheet <Deduction>("DEDUCTIONS", workbook));
}
private List <Credit> GetCredits(SettlementHistoryWorkbook workbook)
{
return(GetSettlementItemsFromSheet <Credit>("MASTER SHEET", workbook));
}
internal HelperRow(SettlementHistoryWorkbook parent, string sheetName, Doc.Row row)
{
_parent = parent;
_row = row;
_sheetName = sheetName;
}
internal HelperCell(SettlementHistoryWorkbook parent, string sheetName, Doc.Cell cell)
{
_cell = cell;
_parent = parent;
_sheetName = sheetName;
}