private static void ReadAQ1ExcelWorkSheet(Worksheet sheet, int sheetNo, SharedStringTable sst, List<clsAQ1ReportDetailRow> detailRows)
{
Debug.WriteLine("ReadAQ1ExcelWorkSheet");
var cells = sheet.Descendants<Cell>();
var rows = sheet.Descendants<Row>();
Debug.WriteLine("Row Count = {0}", rows.LongCount());
Debug.WriteLine("Cell Count = {0}", cells.LongCount());
Debug.WriteLine("");
clsAQ1ReportHeaderRow headerRow = new clsAQ1ReportHeaderRow(sheetNo);
clsAQ1ReportDetailRow detailRow = null;
int rowCount = rows.Count();
RowTypeEnum rowType = RowTypeEnum.UNKNOWN_ROW_TYPE;
RowSubTypeEnum rowSubType = RowSubTypeEnum.UNKNOWN_ROW_SUB_TYPE;
foreach (Row row in rows)
{
SetRowTypeSubType(ref rowType, ref rowSubType, row, rowCount, sst);
Debug.WriteLine("\t" + "rowType: " + rowType.ToString() + ", rowSubType: " + rowSubType.ToString());
switch (rowType)
{
case RowTypeEnum.HeaderRow:
PopulateAQ1HeaderRow(sst, headerRow, rowSubType, row);
continue;
case RowTypeEnum.DetailRow:
if (rowSubType == RowSubTypeEnum.DetailRow1)
{
detailRow = new clsAQ1ReportDetailRow(headerRow, row.RowIndex);
detailRows.Add(detailRow);
}
PopulateAQ1DetailRow(sst, detailRow, rowSubType, row);
break;
case RowTypeEnum.FooterRow:
continue;
default:
throw new Exception("UNKNOWN Row Type");
}
}
}
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;
}
}
}