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
}
