public static void ParseTable(Excel.Range startYearCell, SummaryPanel panel) { string yearStr = startYearCell.Value.ToString(); int year; if (!int.TryParse(yearStr, out year)) { throw new Exception("Summary: Unkonw year string yearStr in cell " + startYearCell.Address); } panel.year = year; panel.yearStr = yearStr; Excel.Range lefttop = startYearCell.Offset[1, 0]; Excel.Range righttop = lefttop.End[Excel.XlDirection.xlToRight]; int colStart = lefttop.Column; int colEnd = righttop.Column; int rowStart = lefttop.Row; int rowEnd = lefttop.Worksheet.UsedRange.Rows.Count; int cols = colEnd - colStart + 1, rows = rowEnd - rowStart + 1; if (cols > 100 || cols < 2 || rows < 2) { throw new Exception("Summary: 表格大小不合理,请检查表的格式"); } Table res = new Table(); for (int row = 0; row < rows; row++) { Excel.Range rowBegin = lefttop.Offset[row, 0]; if (rowBegin.Value == null || (rowBegin.Value.ToString() as string).Trim().Length == 0) { //该行为空 continue; } string[] rowData = new string[cols]; for (int col = 0; col < cols; col++) { rowData[col] = rowBegin.Offset[0, col].Value.ToString(); } res.Add(rowData); } panel.tableData = res; Trace.TraceInformation("Summary table year is {0}, city is {1}, table is {2}", year, panel.city, res.ToString()); }
public static List <SummaryPanel> ReadSummaryPanels(Excel.Application app, string filename) { List <SummaryPanel> res = new List <SummaryPanel>(); Excel.Workbook workbook = app.Workbooks.Open(filename, ReadOnly: true); Excel.Worksheet sheet = null; foreach (Excel.Worksheet s in workbook.Sheets) { if (s.Name == "summary") { sheet = s; } } if (sheet == null) { workbook.Close(false); throw new Exception("Cannot find sheet : summary"); } Excel.Range cityStartCell = sheet.Cells[1, 1]; while (cityStartCell.Column <= sheet.UsedRange.Columns.Count) { SummaryPanel panel = new SummaryPanel(); string strWithCity = cityStartCell.Value.ToString(); foreach (string city in KAO.citys) { if (strWithCity.Contains(city)) { panel.city = city; } } if (panel.city.Length == 0) { workbook.Close(false); throw new Exception("Summary: 表格格式不正确,表格名称应该包括中文城市名。:" + strWithCity); } Excel.Range yearCell = cityStartCell.Offset[0, 1].End[Excel.XlDirection.xlDown]; ParseTable(yearCell, panel); res.Add(panel); cityStartCell = cityStartCell.End[Excel.XlDirection.xlToRight]; } return(res); }