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");
}
}
}
public clsAQ1ReportDetailRow(clsAQ1ReportHeaderRow headerRow, UInt32 excelRowNo)
{
HeaderRow = headerRow;
ExcelRowNo = excelRowNo;
}
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);
}
}