private static void PopulateAQ1HeaderRow(SharedStringTable sst, clsAQ1ReportHeaderRow objAQ1HeaderRow, RowSubTypeEnum rowSubType, Row row)
{
Debug.WriteLine("PopulateAQ1HeaderRow");
if (rowSubType == RowSubTypeEnum.HeaderRow1)
{
objAQ1HeaderRow.Date = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(2), sst).TrimStart(' ', ':');
}
else if (rowSubType == RowSubTypeEnum.HeaderRow2)
{
objAQ1HeaderRow.PageNo = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(2), sst);
}
else if (rowSubType == RowSubTypeEnum.HeaderRow3)
{
//Empty Row. No action;
}
else if (rowSubType == RowSubTypeEnum.HeaderRow4)
{
objAQ1HeaderRow.GeneratedBy = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(4), sst);
}
else if (rowSubType == RowSubTypeEnum.HeaderRow5)
{
objAQ1HeaderRow.WardName = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(1), sst).TrimStart(' ', ':');
}
else if (rowSubType == RowSubTypeEnum.HeaderRow6)
{
objAQ1HeaderRow.MeterBinderNo = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(1), sst).TrimStart(' ', ':');
objAQ1HeaderRow.ReadingCycle = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(4), sst);
objAQ1HeaderRow.Year = clsOpenXmlBC.GetCellValue(row.Elements<Cell>().ElementAt(7), sst);
}
}
private static void SetRowTypeSubType(ref RowTypeEnum rowType, ref RowSubTypeEnum rowSubType, Row row, int rowCount, SharedStringTable sst)
{
Debug.WriteLine("SetRowTypeSubType");
rowType = RowTypeEnum.UNKNOWN_ROW_TYPE;
rowSubType = RowSubTypeEnum.UNKNOWN_ROW_SUB_TYPE;
if (row.RowIndex == 1)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow1;
}
else if (row.RowIndex == 2)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow2;
}
else if (row.RowIndex == 3)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow3;
}
else if (row.RowIndex == 4)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow4;
}
else if (row.RowIndex == 5)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow5;
}
else if (row.RowIndex == 6)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow6;
}
else if (row.RowIndex == 7)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow7;
}
else if (row.RowIndex == 8)
{
rowType = RowTypeEnum.HeaderRow;
rowSubType = RowSubTypeEnum.HeaderRow8;
}
else if (row.RowIndex == rowCount)
{
rowType = RowTypeEnum.FooterRow;
rowSubType = RowSubTypeEnum.FooterRow3;
}
else
{
Cell cell = row.Elements<Cell>().ElementAt(0);
string cellColumn = clsOpenXmlBC.GetExcelColumnName(cell.CellReference.ToString());
string cellValue = clsOpenXmlBC.GetCellValue(cell, sst);
Debug.WriteLine("\t" + "cell.CellReference" + cell.CellReference.ToString() + ", cellColumn: " + cellColumn + ", cellValue: " + cellValue);
if ((cellColumn == "A") && (clsValidation.IsInt32(cellValue)))
{
rowType = RowTypeEnum.DetailRow;
rowSubType = RowSubTypeEnum.DetailRow1;
}
else if ((cellColumn == "B") && (cellValue == "Total Meter Readings Entered :"))
{
rowType = RowTypeEnum.FooterRow;
rowSubType = RowSubTypeEnum.FooterRow1;
}
else if (((cellColumn == "B") && (cellValue == "Total Meter Readings Not Entered"))
|| ((cellColumn == "A") && (cellValue == "Total Meter Readings Not Entered")))
{
rowType = RowTypeEnum.FooterRow;
rowSubType = RowSubTypeEnum.FooterRow2;
}
else
{
rowType = RowTypeEnum.DetailRow;
rowSubType = RowSubTypeEnum.DetailRow2;
}
}
}
private static void PopulateAQ1DetailRow(SharedStringTable sst, clsAQ1ReportDetailRow objAQ1ReportRow, RowSubTypeEnum rowSubType, Row row)
{
Debug.WriteLine("PopulateAQ1DetailRow");
string cellValue;
string column;
foreach (Cell c in row.Elements<Cell>())
{
cellValue = clsOpenXmlBC.GetCellValue(c, sst);
column = clsOpenXmlBC.GetExcelColumnName(c.CellReference.ToString());
Debug.WriteLine("\t" + "CellReference: " + c.CellReference.ToString() + ", column: " + column);
switch (column)
{
case "A": objAQ1ReportRow.A_row1_Folio = cellValue; break;
case "B": objAQ1ReportRow.B_row1_CCN = cellValue; break;
case "C": objAQ1ReportRow.C_row1_CCNStat = cellValue; break;
case "D": objAQ1ReportRow.D_row1_CCNLink = cellValue; break;
case "E": objAQ1ReportRow.E_row1_CCN10 = cellValue; break;
case "F": objAQ1ReportRow.F_row1_MtrStat = cellValue; break;
case "G": objAQ1ReportRow.G_row1_GAP = cellValue; break;
case "H":
if (rowSubType == RowSubTypeEnum.DetailRow1)
objAQ1ReportRow.H_row1_CurrentDate = cellValue;
else if (rowSubType == RowSubTypeEnum.DetailRow2)
objAQ1ReportRow.H_row2_CurrentRdg = cellValue;
break;
case "I":
if (rowSubType == RowSubTypeEnum.DetailRow1)
objAQ1ReportRow.I_row1_PreviousDate = cellValue;
else if (rowSubType == RowSubTypeEnum.DetailRow2)
objAQ1ReportRow.I_row2_PreviousRdg = cellValue;
break;
case "J": objAQ1ReportRow.J_row1_CutRemvDt = cellValue; break;
case "K": objAQ1ReportRow.K_row1_RstrRplcDt = cellValue; break;
case "L": objAQ1ReportRow.L_row1_CsmpByMeter = cellValue; break;
case "M": objAQ1ReportRow.M_row1_Days = cellValue; break;
case "N": objAQ1ReportRow.N_row1_CsmpBilled = cellValue; break;
case "O": objAQ1ReportRow.O_row1_Water = cellValue; break;
case "P": objAQ1ReportRow.P_row1_Sewarage = cellValue; break;
case "Q": objAQ1ReportRow.Q_row1_Rent = cellValue; break;
case "R": objAQ1ReportRow.R_row1_BillAmt = cellValue; break;
case "S": objAQ1ReportRow.S_row1_Additional = cellValue; break;
case "T": objAQ1ReportRow.T_row1_Flat = cellValue; break;
case "U": objAQ1ReportRow.U_row1_Flag = cellValue; break;
case "V": objAQ1ReportRow.V_row1_GroupCode = cellValue; break;
case "W": objAQ1ReportRow.W_row1_RateCharge = cellValue; break;
case "X":
if (rowSubType == RowSubTypeEnum.DetailRow1)
objAQ1ReportRow.X_row1_CutDate = cellValue;
else if (rowSubType == RowSubTypeEnum.DetailRow2)
objAQ1ReportRow.X_row2_Reason_Part2 = cellValue;
break;
case "Y": objAQ1ReportRow.Y_row1_Reason = cellValue; break;
}
}
}
