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