public static List <ConsumerPanel> ReadConsumerPanels(Excel.Application app, string filename) { List <ConsumerPanel> res = new List <ConsumerPanel>(); Excel.Workbook workbook = app.Workbooks.Open(filename, ReadOnly: true); foreach (string city in KAO.citys) { Excel.Worksheet sheet = null; foreach (Excel.Worksheet s in workbook.Sheets) { if (s.Name == city) { sheet = s; } } if (sheet == null) { continue; } ConsumerPanel panel = new ConsumerPanel(); panel.city = city; Excel.Range startCell = sheet.Cells[1, 1]; while (startCell.Column <= sheet.UsedRange.Columns.Count) { Excel.Range yearCell = startCell.Offset[2, 1]; panel.tableDataList.Add(ConsumerPanel.ParseTable(yearCell)); startCell = startCell.End[Excel.XlDirection.xlToRight]; } res.Add(panel); } return(res); }
public static void CopyToConsumerPanelExcel(Excel.Application app, List <ConsumerPanel> panelList, string dirName) { if (!Directory.Exists(dirName)) { throw new Exception("Dir " + dirName + " not exists"); } string[] entries = Directory.GetFileSystemEntries(dirName); foreach (string entry in entries) { string filename = Path.GetFileName(entry); char[] delims = { '.', '-' }; string city = filename.Split(delims)[2]; if (!KAO.cityTable.TryGetValue(city.ToLower(), out city)) { throw new Exception("Can not determine city :" + filename); } ConsumerPanel curPanel = null; foreach (ConsumerPanel p in panelList) { if (p.city == city) { curPanel = p; } } if (curPanel == null) { throw new Exception("Cannot find panel of city " + city); } Excel.Workbook book = app.Workbooks.Open(entry, ReadOnly: false); if (curPanel.tableDataList.Count != book.Sheets.Count) { throw new Exception("Panel table List count not equal to sheets count"); } int idx = 0; foreach (Excel.Worksheet curSheet in book.Sheets) { Trace.TraceInformation("Copying to {0}:{1}", book.Name, curSheet.Name); TableTuple curTuple = curPanel.tableDataList[idx++]; Excel.Range startYearRange = curSheet.UsedRange.Find(curTuple.Item1.ToString(), Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); string startYearStr = startYearRange.Value.ToString(); int startYear; if (!int.TryParse(startYearStr, out startYear) || startYear != curTuple.Item1) { throw new Exception("Can not find a place to paste with year " + startYearStr); } Excel.Range startCell = startYearRange.End[Excel.XlDirection.xlDown]; Excel.Range endCell = startCell.Offset[curTuple.Item2.Rows.Count, curTuple.Item2.Columns.Count]; Excel.Range pasteRange = curSheet.Range[startCell, endCell]; Excel.Range copyRange = curTuple.Item2; copyRange.Copy(Type.Missing); pasteRange.PasteSpecial(Excel.XlPasteType.xlPasteValues); } } }