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); } }