public WebParserResult ParserCheck(WebParserConfig parserConfig) { string fileNameParser = this.GetParserPath(); WebParserResult result = new WebParserResult() { ParserStatus = "Ok", ParserError = "" }; Excel.Application xlApp = null; try { Console.WriteLine("Parser test starting. Please wait..."); // Application xlApp = new Excel.Application(); // открываем Excel файл Excel.Workbook xlWb1 = xlApp.Workbooks.Open(fileNameParser); xlApp.Run(@"ParserAddinTest"); //xlWb1.Close(false); // закрываем книгу без сохранения xlApp.DisposeChildInstances(); } catch (Exception exc) { result.ParserStatus = "Error"; result.ParserError = exc.Message; } finally { if (xlApp != null) { xlApp.Quit(); //закрываем Excel xlApp.Dispose(); } } return(result); }
public WebParserResult ParseWebSite(WebParserConfig parserConfig, WebParserParams parserParam) { string fileNameAddin = this.GetParserPath(); string parserConfigName = parserConfig.AddinConfigName; WebParserResult parserResult = new WebParserResult() { ParserStatus = "Ok", ParserError = "" }; Excel.Application xlApp = null; Excel.Workbook xlWb1 = null; Excel.Workbook xlWb2 = null; Excel.Worksheet xlSht = null; try { // Запустить Application xlApp = new Excel.Application(); // Открыть надстройку xlWb1 = xlApp.Workbooks.Open(fileNameAddin); // Создать лист для ввода данных // Excel.Workbook xlWb2 = xlApp.Workbooks.Open(fileNameExcelWorkbook); xlWb2 = xlApp.Workbooks.Add(); //Excel.Worksheet xlSht = xlWb2.Sheets["Лист1"]; //имя листа в файле xlSht = xlWb2.Sheets[1] as Excel.Worksheet; //имя листа в файле //xlApp.Visible = true; //xlApp.DisplayAlerts = false; int startRowNumber = parserConfig.StartRowNumber; // Вывод заголовка if (startRowNumber > 1) { xlSht.Cells[1, parserConfig.DealNumberColumn].Value = "Номер документа"; xlSht.Cells[1, parserConfig.IsTrackColumn].Value = "Обрабатывать"; } // Заполнение исходных данных на листе xlSht.Cells[startRowNumber, parserConfig.DealNumberColumn].Value = parserParam.DocumentNumber; xlSht.Cells[startRowNumber, parserConfig.IsTrackColumn].Value = "Да"; // Запуск парсера var res = xlApp.Run(@"StartParser", parserConfigName); if (!String.IsNullOrEmpty((string)res)) { throw new Exception((string)res); } object row1 = xlSht.Cells[startRowNumber, parserConfig.ResultNumberColumn].Value; if (row1 == null) { parserResult.ParserStatus = "Not found"; } else { string col2HyperlinkAddress = xlSht.Cells[startRowNumber, parserConfig.DealNumberHyperlinkColumn].Hyperlinks[1].Address; string col3 = xlSht.Cells[startRowNumber, parserConfig.DocumentPdfFolderNameColumn].Value.ToString(); string col4 = xlSht.Cells[startRowNumber, parserConfig.DocumentPdfUrlColumn].Value.ToString(); DateTime col5 = (DateTime)xlSht.Cells[startRowNumber, parserConfig.LastDealDateColumn].Value; parserResult.CardUrl = col2HyperlinkAddress; parserResult.LastDealDate = col5; parserResult.DocumentPdfUrl = col4; parserResult.DocumentPdfFolderName = col3; parserResult.HasAttachment = string.IsNullOrEmpty(col4) ? false : true; parserResult.DocumentPfdPath = GetDocumentFullPath(parserConfig, parserResult, fileNameAddin); } } catch (Exception exc) { parserResult.ParserStatus = "Error"; parserResult.ParserError = exc.Message; } finally { if (xlWb2 != null) { xlWb2.Saved = true; } xlApp.DisposeChildInstances(); if (xlApp != null) { xlApp.Quit(); //закрываем Excel xlApp.Dispose(); } } return(parserResult); }
public static void Renderer(Plotter plotter, string template) { if (Path.GetExtension(template).ToLower() != ".xlsm") { return; } if (plotter.AllData == null || plotter.AllData.Keys.Count == 0) { return; } int rows = plotter.AllData.Keys .Select(item => plotter.AllData[item].Count) .Max(); if (rows < 1) { return; } var excel = new Excel.Application(); var wbooks = excel.Workbooks; var wbook = wbooks.Add(template); // create new w/ file as template Thread.Sleep(500); // FIXME: prevents Excel from crashing List <string> plots = plotter.AllData.Keys.ToList(); for (int i = 0; i < plots.Count; i++) { string plot = plots[i]; string tmpFile = Path.GetTempFileName(); plotter.SaveAsCsv( tmpFile, plot, o => { if (o.GetType() == typeof(DateTime)) { return(string.Format("{0:MM/dd/yyyy}", (DateTime)o)); } return(o.ToString()); }); excel.Run("UPDATE_LOGGER", tmpFile, plots.Count, i, plot); Thread.Sleep(500); // FIXME: prevents Excel from crashing } #if true // return control to the user excel.Visible = true; excel.UserControl = true; #endif #if false // BUGBUG: it seems that Excel is occasionally leaving // a zombie process behind, after the workbook is closed. void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; } finally { GC.Collect(); } } //wbook.Close(false); // can't do this: want to keep document open //excel.Quit(); // can't do this: will ask to save & close releaseObject(excel); // doesn't seem to do anything releaseObject(wbook); // doesn't seem to do anything #endif }