public bool OpenWorksheet(String path)
{
// Если не экономим память, то создаём новый экземпляр COM OLE
wb?.Close(0);
if (Path.GetExtension(path) == ".csv")
{
var convResult = CSV_Converter.Runner.Open(path, ";");
Logger.info($"Конвертация CSV файла из \"{path}\" в \"{convResult.Filename}\".");
path = convResult.Filename;
filesToRemove.Add(convResult.Filename);
if (!convResult.Success)
{
throw new ApplicationException("Ошибка конвертации файла!");
}
}
wb = app.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
worksheet = wb.Worksheets[1];
return(true);
}
public bool OpenWorksheet(String path)
{
wb?.Close(0);
wb = app.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
worksheet = wb.Worksheets[1];
return(worksheet != null ? true : false);
}
public bool OpenWorksheet(String path)
{
// Если не экономим память, то создаём новый экземпляр COM OLE
wb?.Close(0);
wb = app.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
worksheet = wb.Worksheets[1];
return(true);
}
public ExcelDoc TryOpenDoc(string path, bool readOnly)
{
Workbook doc = null;
try
{
if (App == null)
{
throw new Exception($"Не удалось открыть документ {path} приложение не запущено");
}
if (!readOnly && IsDocumentOpen(path))
{
throw new Exception($"Документ уже редактируется {path}");
}
TryPath = path;
doc = App.Workbooks.Open(path, Type.Missing, readOnly);
ExcelDoc d = new ExcelDoc(path, this, doc, readOnly);
Docs.Add(d);
TryPath = null;
return(d);
}
catch (Exception e)
{
doc?.Close(false);
TryPath = null;
return(null);
}
}
public void Quit(bool save = false, string fname = null)
{
_wSheets?.Protect(Program.Pws);
if (save)
{
_wBoock.Save();
}
if (fname != null)
{
_wBoock.SaveAs(fname);
}
_wBoock?.Close(0);
_exApp.Quit();
Marshal.ReleaseComObject(_exApp);
if (_wBoock != null)
{
Marshal.ReleaseComObject(_wBoock);
}
if (_wSheets != null)
{
Marshal.ReleaseComObject(_wSheets);
}
if (_sheets != null)
{
Marshal.ReleaseComObject(_sheets);
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
static void Main(string[] args)
{
Application application = new Application();
application.DisplayAlerts = false;
Workbook scheduleWorkbook = null;
var startDate = DateTime.Now.Date;
var endDate = startDate.AddMonths(1);
try
{
scheduleWorkbook = application.Workbooks.Add();
var tat = new TatarstanWorkDayCalendar(TimeSpan.FromHours(10),
TimeSpan.FromHours(18));
tat.EnsureInitializedAsync().Wait();
var workTimeCalculator =
new WorkTimeCalculator(tat);
Worksheet scheduleSheet = scheduleWorkbook.ActiveSheet;
PrintDateRow(scheduleSheet, startDate, endDate);
var schedule = workTimeCalculator.GetSchedule(startDate, new WorkTime(20, 0, 0));
PrintSchedule(scheduleSheet, 2, schedule);
scheduleWorkbook.SaveAs(@"\C:\Users\almaz\OneDrive\Документы\test.xlsx");
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
scheduleWorkbook?.Close(true);
application.Quit();
Marshal.ReleaseComObject(application);
}
}
protected ExcelFileBase(Action <ComObjectManager, Worksheet> buildWorksheet)
{
if (buildWorksheet == null)
{
throw new ArgumentNullException(nameof(buildWorksheet));
}
ExcelApplication excel = null;
try
{
excel = Com.Get(() => new ExcelApplication {
Visible = false
});
_workbook = Com.Get(() => excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));
var worksheet = Com.Get(() => (Worksheet)_workbook.Worksheets[1]);
buildWorksheet(Com, worksheet);
}
catch
{
_workbook?.Close();
excel?.Quit();
Com.Dispose();
throw;
}
}
public void Dispose(bool flag)
{
if (!IsAlreadyOpened)
{
Workbook?.Close();
}
}
private async void CalculateRowSize()
{
int row_size = 0;
Application application = null;
Workbooks wbooks = null;
Workbook sourceWorkbook = null;
try
{
application = new Application();
wbooks = application.Workbooks;
listbox_excellPaths.Enabled = false;
foreach (ExcellFile item in excellFilePaths)
{
if (item.RowSize == 0)
{
WriteConsole($"{item.FileName} Boyutu Okunuyor...");
listbox_excellPaths.SelectedItem = item;
await Task.Run(() =>
{
sourceWorkbook = wbooks.Open(item.Path);
Worksheet wsheet = (Worksheet)sourceWorkbook.Worksheets.get_Item(1);
row_size += wsheet.UsedRange.Rows.Count;
item.RowSize = wsheet.UsedRange.Rows.Count;
sourceWorkbook?.Close();
Marshal.ReleaseComObject(sourceWorkbook);
});
int index = listbox_excellPaths.Items.IndexOf(item);
listbox_excellPaths.Items.Remove(item);
listbox_excellPaths.Items.Insert(index, item);
}
else
{
row_size += item.RowSize;
}
SetRowCount(row_size);
}
listbox_excellPaths.Enabled = true;
wbooks?.Close();
Marshal.ReleaseComObject(wbooks);
application?.Quit();
Marshal.ReleaseComObject(application);
WriteConsole($"Toplam Satır Boyutu {row_size} olarak okundu.");
MessageBox.Show($"{row_size}", "Toplam Satır Sayısı");
}
catch { }
finally
{
try
{
//sourceWorkbook?.Close();
//Marshal.ReleaseComObject(sourceWorkbook);
}
catch (Exception) { }
application = null;
}
}
static void CloseWorkBook(Workbook workbook)
{
try
{
workbook.Close();
}
catch { /* Do Nothing*/ }
}
public List <T> Load <T>() where T : class
{
Workbook workbook = null;
Worksheet worksheet;
//if (typeof(T).FullName == typeof(Address).FullName)
//{
// FileInfo addresseDataFile = new FileInfo(@".\data\address.xlsx");
// workbook = excelApplication.Workbooks.Open(addresseDataFile.FullName);
// worksheet = workbook.Sheets[1];
// Dictionary<int, Address> addresses = AddressDataLoad(worksheet);
//}
//if (typeof(T).FullName == typeof(Company).FullName)
//{
// FileInfo companyDataFile = new FileInfo(@".\data\company.xlsx");
// workbook = excelApplication.Workbooks.Open(companyDataFile.FullName);
// worksheet = workbook.Sheets[1];
// Dictionary<int, Company> company = CompanyDataLoad(worksheet);
//}
if (typeof(T).FullName == typeof(OFD).FullName)
{
FileInfo ofdDataFile = new FileInfo(@".\data\ofd.xlsx");
workbook = excelApplication.Workbooks.Open(ofdDataFile.FullName);
worksheet = workbook.Sheets[1];
return(OfdDataLoad(worksheet).Cast <T>().ToList());
}
if (typeof(T).FullName == typeof(User).FullName)
{
FileInfo usersDataFile = new FileInfo(@".\data\users.xlsx");
workbook = excelApplication.Workbooks.Open(usersDataFile.FullName);
worksheet = workbook.Sheets[1];
return(UserDataLoad(worksheet).Cast <T>().ToList());
}
if (typeof(T).FullName == typeof(Store).FullName)
{
FileInfo storesDataFile = new FileInfo(@".\data\stores.xlsx");
FileInfo companyDataFile = new FileInfo(@".\data\company.xlsx");
FileInfo addresseDataFile = new FileInfo(@".\data\address.xlsx");
workbook = excelApplication.Workbooks.Open(companyDataFile.FullName);
worksheet = workbook.Sheets[1];
Dictionary <int, Company> companyes = CompanyDataLoad(worksheet);
workbook = excelApplication.Workbooks.Open(addresseDataFile.FullName);
worksheet = workbook.Sheets[1];
Dictionary <int, Address> addresses = AddressDataLoad(worksheet);
workbook = excelApplication.Workbooks.Open(storesDataFile.FullName);
worksheet = workbook.Sheets[1];
return(StoreDataLoad(worksheet, companyes, addresses).Cast <T>().ToList());
}
workbook?.Close(false);
return(null);
}
public void Dispose()
{
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(Range);
Marshal.ReleaseComObject(Worksheet);
Workbook?.Close();
Marshal.ReleaseComObject(Workbook);
Application?.Quit();
Marshal.ReleaseComObject(Application);
}
public static async Task <bool> ExportSchedules(DateTime month, List <Calendars> calendarsH, List <Calendars> calendarsA, bool print, List <int> holidays)
{
Application xlApp = null;
Workbook xlWorkBook = null;
await Task.Run(() => xlApp = new Application());
Nullable <Boolean> printed = null;
Nullable <Boolean> exported = null;
try
{
if (print)
{
PrintDialog printDialog = new PrintDialog();
printed = printDialog.ShowDialog();
if (printed == true)
{
xlWorkBook = await GetExcel.GetSchedules(month, calendarsH, calendarsA, xlApp, holidays);
xlWorkBook.PrintOutEx();
}
}
else
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.FileName = "HORARIOS_ASISTENCIAS_" + month.ToString("MMMM-yyyy").ToUpper();
saveFileDialog.Filter = "Documento Excel (*.xlsx)|*.xlsx";
exported = saveFileDialog.ShowDialog();
if (exported == true)
{
xlWorkBook = await GetExcel.GetSchedules(month, calendarsH, calendarsA, xlApp, holidays);
xlWorkBook.SaveAs(saveFileDialog.FileName);
}
}
}
catch { }
finally
{
xlWorkBook?.Close();
xlApp.Quit();
}
return((bool)(printed ?? exported));
}
/// <summary>
/// Closes Excel (and an active workbook).
/// </summary>
/// <remarks>
/// If a workbook is open, it will be closed first.
/// You can specify whether to save changes.
/// </remarks>
/// <param name="saveChanges">whether to save changes on open workbook</param>
public void Close(bool saveChanges = true)
{
try
{
// Close the spreadsheet (if it exists) (and save changes if desired).
_workbook?.Close(saveChanges);
// Close Excel.
_excelApp.Quit();
}
finally
{
// Release all the excel COM objects.
Dispose();
}
}
public int ConvertToPDF(string sourceFile, string pdfFile)
{
if (string.IsNullOrEmpty(sourceFile))
{
throw new ArgumentNullException(nameof(sourceFile));
}
if (string.IsNullOrEmpty(pdfFile))
{
throw new ArgumentNullException(nameof(pdfFile));
}
if (!File.Exists(sourceFile))
{
throw new FileNotFoundException($"source file '{sourceFile}' doesn't exists");
}
if (File.Exists(pdfFile))
{
File.Delete(pdfFile);
}
if (!Check(true))
{
return(-1);
}
Workbook workBook = null;
try
{
workBook = app.Workbooks.Open(sourceFile);
workBook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, pdfFile);
return(1);
}
catch (Exception ex)
{
Kill();
throw new Exception("convert failed: " + sourceFile, ex);
}
finally
{
workBook?.Close(XlSaveAction.xlDoNotSaveChanges);
workBook?.Dispose();
}
}
public void CreateCustomerData(ref ISelenium browser)
{
try
{
var admincommonObj = new AdminSuite.Common();
string user, password = "******";
_testDataFilePath = _currentDirPath.Parent.FullName;
_app = new Application();
//var testDataObj = new TestData();
_app.DisplayAlerts = false;
_workBook = _app.Workbooks.Open(_testDataFilePath + "\\TestData\\TestData.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
_workSheets = _workBook.Worksheets;
var _userSheet = (Microsoft.Office.Interop.Excel.Worksheet)_workSheets.get_Item("Users");
Random rnd = new Random();
int rndNumber;
for (int i = 0; i < 10; i++)
{
rndNumber = rnd.Next(10000);
user = "******" + rndNumber;
admincommonObj.CreateCustomer(browser, user, password);
_userSheet.Cells[i + 2, "B"] = user;
_userSheet.Cells[i +2, "C"] = password;
admincommonObj.PerformManualAdjustment(browser, user, "Test Accounts", "100", "Yes", "Test Accounts", "", "", "", "Today");
_workBook.Save();
}
}
catch (Exception Ex)
{
Framework.BaseTest.CaptureScreenshot(browser, "");
Console.WriteLine(Ex.StackTrace);
Framework.BaseTest.Fail(Ex.Message);
}
finally
{
_workBook.Save();
_workBook.Close();
_workSheet = null;
_workBook = null;
_app.Quit();
}
}
/// <summary>
/// Closes the file and excel and returns true on success
/// </summary>
/// <returns>True if excel closed successfully</returns>
public bool CloseFile()
{
//if(!CanUse) return false;
try
{
SimpleLog.Info($"Closing Excel file \"{Workbook.FullName}\".");
Workbook?.Close();
ExcelApp?.Quit();
Dispose();
SimpleLog.Info("Excel closed.");
return(true);
}
catch (Exception e)
{
SimpleLog.Log("Failed to close excel.");
SimpleLog.Error(e.ToString());
}
return(false);
}
public static void RowsToExcelFile(List <List <string> > excelData, string outExcelFileName)
{
var xlApp = new Application();
Workbook xlWorkBook = null;
Worksheet xlWorkSheet = null;
try
{
xlApp.Visible = false;
xlWorkBook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkSheet.Name = "Exported";
int rowindex = 0;
foreach (var row in excelData)
{
int colindex = 0;
foreach (var col in row)
{
var newCell = (Range)xlWorkSheet.Cells[rowindex + 1, colindex + 1];
newCell.Value = col;
newCell.Font.Bold = rowindex == 0;
colindex++;
}
rowindex++;
}
Range usedRange = xlWorkSheet.UsedRange;
usedRange.Columns.AutoFit();
xlWorkBook.SaveAs(outExcelFileName);
}
finally
{
xlWorkBook?.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
}
public bool SaveAs(string fileName, System.Data.DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0) return false;
if (xlApp == null) return false;
_workbook = xlApp.Workbooks.Add(Type.Missing);
_worksheet = (Worksheet)_workbook.Worksheets[1];
for (var i = 0; i < dataTable.Columns.Count; i++)
{
_worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
//range = (Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;
//range.Font.Bold = true;
}
_workbook.SaveAs(fileName,XlFileFormat.xlWorkbookNormal, "", "", false, false, XlSaveAsAccessMode.xlExclusive,XlPlatform.xlWindows, false, false, false, false);
_workbook.Close(true, fileName, false);
xlApp.Quit();
_connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=Excel 8.0;"; // строка подключения
using (var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString)) // используем OleDb
{
var queryValues = String.Empty;
excelConn.Open();
for (var i = 0; i < dataTable.Rows.Count; i++)
{
for (var c = 0; c < dataTable.Columns.Count; c++)
{
queryValues += dataTable.Rows[i][c] + "','";
}
queryValues = queryValues.Substring(0, queryValues.Length - 3);
var writeCmd = new System.Data.OleDb.OleDbCommand("INSERT INTO [Лист1$] VALUES ('" + queryValues + "')", excelConn);
writeCmd.ExecuteNonQuery(); // вставляем данные в лист1 файла - filename
writeCmd.Dispose();
queryValues = String.Empty;
}
excelConn.Close();
}
return System.IO.File.Exists(fileName);
}
/// <summary>
/// Methode die het excel sheet opslaat als PDF bestand.
/// </summary>
/// <param name="excelBestand">Excel bestand om te zetten</param>
public void ConverteerBestand(FileInfo excelBestand)
{
try
{
string bestandsnaam = excelBestand.Name;
string bestandsPad = excelBestand.FullName;
string doelPad = String.Format("{0}.pdf", bestandsPad.Substring(0, bestandsPad.LastIndexOf('.')));
// Het excel bestand openen
excelWorkBook = excel.Workbooks.Open(bestandsPad,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
// Opslaan als PDF-bestand
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
doelPad, paramExportQuality,
true, true, Type.Missing,
Type.Missing, false,
paramMissing);
//Het ophalen van de gewichten en bandenspanning
if (bestandsnaam.Equals("TestBandingspanningGewichten.xlsx"))
{
string currentSheet = "Sheet1";
Excel.Sheets excelSheets = excelWorkBook.Worksheets;
Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
lijstGewichten = new List<string>();
lijstGewichten.Add(((Excel.Range)xlws.Cells[12, 4]).Value2.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[13, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[15, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[16, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[17, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[3, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[4, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[6, 4]).Value.ToString());
lijstGewichten.Add(((Excel.Range)xlws.Cells[7, 4]).Value.ToString());
}
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
}
catch (Exception)
{
Logging.log.WriteLine(applicatieNaam, String.Format("{0} kon niet geconverteerd worden", excelBestand.Name));
}
}
private void Window_Closing(object sender, CancelEventArgs e)
{
Workbook?.Save();
Workbook?.Close();
}
private void BgwExportaDocumento_DoWork(object sender, DoWorkEventArgs e)
{
try
{
_app = new Application();
_workBook = _app.Workbooks.Add(misValue);
_workSheet = (Worksheet)_workBook.Worksheets.Item[1];
//Configurações visuais para célula
_workSheet.Cells.Columns.AutoFit();
_workSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
_workSheet.Cells.Font.Size = 13;
for (var i = 0; i < 2; i++)
{
EscreveDados(tituloForm, dataGridView, headers, headersData);
if (!bgwExportaDocumento.CancellationPending) continue;
e.Cancel = true;
return;
}
_workBook.SaveAs(salvar.FileName, XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value,
false,
false,
XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlUserResolution, true, Missing.Value,
Missing.Value, Missing.Value);
e.Result = true;
}
catch (Exception ex)
{
MessageBox.Show(
"Erro ao salvar. Se estiver substituindo um arquivo já existente tenha certeza que ele está fechado e tente novamente.\n" + ex.Message,
"ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
e.Result = false;
}
finally
{
try
{
_workBook.Close(true, misValue, misValue);
_app.Quit(); // encerra o excel
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
private void SaveXLS(Patient patient)
{
if (patient.results.Count > 0)
{
try
{
// Открываем приложение
application = new Microsoft.Office.Interop.Excel.Application
{
DisplayAlerts = false
};
// Файл шаблона
const string template = "resources/template.xlt";
// Открываем книгу
workBook = application.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, template));
// Получаем активную таблицу
worksheet = workBook.ActiveSheet as Worksheet;
// Записываем данные
//worksheet.Range["A2"].Value = DateTime.Now;
worksheet.Range["A1"].Value = "Название сеанса";
worksheet.Range["B1"].Value = "Дата";
worksheet.Range["C1"].Value = "Продолжительность";
worksheet.Range["D1"].Value = "Горизонтальное отклонение";
worksheet.Range["E1"].Value = "Горизонтальное отклонение (динамика)";
worksheet.Range["F1"].Value = "Вертикальное отклонение";
worksheet.Range["G1"].Value = "Вертикальное отклонение (динамика)";
worksheet.Range["H1"].Value = "Попадания";
worksheet.Range["I1"].Value = "Промахи";
worksheet.Range["J1"].Value = "Баллы";
worksheet.Range["K1"].Value = "Комментарий";
for (int i = 0; i < patient.results.Count; i++)
{
worksheet.Range["A" + (i + 2).ToString()].Value = patient.results[i].Name;
worksheet.Range["B" + (i + 2).ToString()].Value = patient.results[i].Date.ToShortDateString() + " " + patient.results[i].Date.ToShortTimeString();
worksheet.Range["C" + (i + 2).ToString()].Value = patient.results[i].Length.ToString();
worksheet.Range["D" + (i + 2).ToString()].Value = patient.results[i].HorDev.ToString();
worksheet.Range["E" + (i + 2).ToString()].Value = patient.results[i].HorDevDin.ToString();
worksheet.Range["F" + (i + 2).ToString()].Value = patient.results[i].VerDev.ToString();
worksheet.Range["G" + (i + 2).ToString()].Value = patient.results[i].VerDevDin.ToString();
worksheet.Range["H" + (i + 2).ToString()].Value = patient.results[i].Hit.ToString();
worksheet.Range["I" + (i + 2).ToString()].Value = patient.results[i].Miss.ToString();
worksheet.Range["J" + (i + 2).ToString()].Value = patient.results[i].Balls.ToString();
worksheet.Range["K" + (i + 2).ToString()].Value = patient.results[i].Comment;
}
bufar = patient.AverageResult();
worksheet.Range["A" + (patient.results.Count + 4).ToString()].Value = "Средний результат";
worksheet.Range["C" + (patient.results.Count + 4).ToString()].Value = bufar.Length.ToString();
worksheet.Range["D" + (patient.results.Count + 4).ToString()].Value = bufar.HorDev.ToString();
worksheet.Range["E" + (patient.results.Count + 4).ToString()].Value = bufar.HorDevDin.ToString();
worksheet.Range["F" + (patient.results.Count + 4).ToString()].Value = bufar.VerDev.ToString();
worksheet.Range["G" + (patient.results.Count + 4).ToString()].Value = bufar.VerDevDin.ToString();
worksheet.Range["H" + (patient.results.Count + 4).ToString()].Value = bufar.Hit.ToString();
worksheet.Range["I" + (patient.results.Count + 4).ToString()].Value = bufar.Miss.ToString();
worksheet.Range["J" + (patient.results.Count + 4).ToString()].Value = bufar.Balls.ToString();
worksheet.Range["K" + (patient.results.Count + 4).ToString()].Value = bufar.Comment;
bufbr = patient.BestResults();
worksheet.Range["A" + (patient.results.Count + 5).ToString()].Value = "Лучший результат";
worksheet.Range["C" + (patient.results.Count + 5).ToString()].Value = bufbr.Length.ToString();
worksheet.Range["D" + (patient.results.Count + 5).ToString()].Value = bufbr.HorDev.ToString();
worksheet.Range["E" + (patient.results.Count + 5).ToString()].Value = bufbr.HorDevDin.ToString();
worksheet.Range["F" + (patient.results.Count + 5).ToString()].Value = bufbr.VerDev.ToString();
worksheet.Range["G" + (patient.results.Count + 5).ToString()].Value = bufbr.VerDevDin.ToString();
worksheet.Range["H" + (patient.results.Count + 5).ToString()].Value = bufbr.Hit.ToString();
worksheet.Range["I" + (patient.results.Count + 5).ToString()].Value = bufbr.Miss.ToString();
worksheet.Range["J" + (patient.results.Count + 5).ToString()].Value = bufbr.Balls.ToString();
worksheet.Range["K" + (patient.results.Count + 5).ToString()].Value = bufbr.Comment;
//сохранение
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Таблица результатов пациентов (*.xls)|*.xls";
sfd.FileName = patient.Name + "_результаты" + ".xls";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
workBook.SaveAs(sfd.FileName);
//закрытие процесса
if (application != null)
{
int excelProcessId = -1;
GetWindowThreadProcessId(application.Hwnd, ref excelProcessId);
Marshal.ReleaseComObject(worksheet);
workBook.Close();
Marshal.ReleaseComObject(workBook);
application.Quit();
Marshal.ReleaseComObject(application);
application = null;
// Прибиваем висящий процесс
try
{
Process process = Process.GetProcessById(excelProcessId);
process.Kill();
}
finally { }
}
}
catch (Exception)
{
MessageBox.Show("Произошла непредвиденная ошибка, попробуйте ещё раз", "Внимание");
}
}
}
public bool Close(Workbook wb)
{
wb.Close(false, null, null);
excel.Quit();
return true;
}
private void CloseExcel(Workbook wb)
{
wb.Save();
wb.Close();
}
//
//**********************************************************************************************
//
public List<TMeteo> ReadPrecipitation(String aFile, DateTime aFirst, DateTime aLast)
{
Double OldValue = 0.0;
Double NewValue = 0.0;
Int32 FirstMonth = aFirst.Month;
Int32 LastMonth = aLast.Month;
String SheetName;
List<TMeteo> Prec = new List<TMeteo> ();
Prec.Clear();
MyWorkBook = ExcelApp.Workbooks.Open(aFile, Missing, Missing, Missing,
Missing, Missing, Missing, Missing, Missing, Missing, Missing,
Missing, Missing, Missing, Missing);
for (Int32 Month = FirstMonth; Month <= LastMonth; Month++)
{
OldValue = 0.0;
SheetName = GetNameOfMonth(Month);
MyWorkSheet = (Worksheet)MyWorkBook.Worksheets.get_Item(SheetName);
Range MyRange = MyWorkSheet.UsedRange;
System.Array MyValues = (System.Array)MyRange.Cells.get_Value(XlRangeValueDataType.xlRangeValueDefault);
String[,] MyData = ConvertToStringArray(MyValues);
for (Int32 i = 0; i < MyData.GetLength(0); i++)
{
TMeteo MyMeteo = new TMeteo();
if ((MyData[i, 0].IndexOf("/") >= 0) || (MyData[i,0].IndexOf("-") >= 0))
{
MyMeteo.Date = Convert.ToDateTime(MyData[i, 0]);
NewValue = Convert.ToDouble(MyData[i, 1]);
MyMeteo.Value = NewValue - OldValue;
OldValue = NewValue;
Prec.Add(MyMeteo);
}
}
}
MyWorkBook.Close(false, "", null);
return Prec;
}
private static void CloseExcel(Workbook workbook)
{
workbook.Close();
}
private static void GeneratedForm(Dictionary <string, List <Dict> > dict)
{
Application app = null;
Workbook workBook = null;
Worksheet worksheet;
Worksheet sheet = null;
Range range;
try
{
app = new Application()
{
Visible = true,
DisplayAlerts = false
};
app.Workbooks.Add(true);
if (File.Exists(_path))
{
File.Delete(_path);
}
workBook = app.Workbooks.Add(Missing.Value);
app.Sheets.Add(Missing.Value, Missing.Value, dict.Count);
worksheet = (Worksheet)workBook.Sheets[1];//数据库字典汇总表
worksheet.Name = "数据库字典汇总表";
worksheet.Cells[1, 1] = "数据库字典汇总表";
worksheet.Cells[2, 1] = "编号";
worksheet.Cells[2, 2] = "表英文名称";
worksheet.Cells[2, 3] = "表中文名称";
worksheet.Cells[2, 4] = "数据说明";
worksheet.Cells[2, 5] = "表结构描述(页号)";
var type = typeof(Dict);
var properties = (from p in type.GetProperties()
where p.Name != "表名" && p.Name != "表说明"
select p).ToArray();
for (var i = 0; i < dict.Count; i++)
{
var list = dict.ElementAt(i).Value;
sheet = (Worksheet)workBook.Sheets[i + 2];//数据表
sheet.Name = $"{(101d + i) / 100:F}";
sheet.Cells[1, 1] = "数据库表结构设计明细";
sheet.Cells[2, 1] = $"表名：{dict.ElementAt(i).Key}";
sheet.Cells[3, 1] = list[0].表说明;
for (var j = 0; j < properties.Count(); j++)
{
if (properties[j].Name != "表名" && properties[j].Name != "表说明")
{
sheet.Cells[4, j + 1] = properties[j].Name;
for (var k = 0; k < list.Count; k++)
{
sheet.Cells[k + 5, j + 1] = type.GetProperty(properties[j].Name).GetValue(list[k], null);
}
}
}
worksheet.Cells[i + 3, 1] = i + 1;
worksheet.Cells[i + 3, 2] = dict.ElementAt(i).Key;
worksheet.Cells[i + 3, 3] = dict.ElementAt(i).Value[0].表说明;
worksheet.Cells[i + 3, 4] = string.Empty;
worksheet.Cells[i + 3, 5] = $"表{sheet.Name}";
#region 数据表样式
range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[list.Count + 4, properties.Count()]]; //选取单元格
range.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中设置
range.EntireColumn.AutoFit(); //自动调整列宽
range.Borders.LineStyle = XlLineStyle.xlContinuous; //所有框线
range.Borders.Weight = XlBorderWeight.xlMedium; //边框常规粗细
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 14; //字体大小
range.NumberFormatLocal = "@";
range = sheet.Range[sheet.Cells[4, 1], sheet.Cells[list.Count + 4, properties.Count()]]; //选取单元格
range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中设置
range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, properties.Count()]]; //选取单元格
range.Merge(Missing.Value);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中设置
range.Font.Bold = true; //字体加粗
range.Font.Size = 24; //字体大小
range = sheet.Range[sheet.Cells[2, 1], sheet.Cells[2, properties.Count()]]; //选取单元格
range.Merge(Missing.Value);
range = sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, properties.Count()]]; //选取单元格
range.Merge(Missing.Value);
#endregion
}
#region 汇总表样式
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[dict.Count + 2, 5]]; //选取单元格
range.ColumnWidth = 30; //设置列宽
range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中设置
range.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中设置
range.Borders.LineStyle = XlLineStyle.xlContinuous; //所有框线
range.Borders.Weight = XlBorderWeight.xlMedium; //边框常规粗细
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 14; //字体大小
range.NumberFormatLocal = "@";
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 5]]; //选取单元格
range.Merge(Missing.Value);
range.Font.Bold = true; //字体加粗
range.Font.Size = 24; //字体大小
#endregion
sheet?.SaveAs(_path);
worksheet.SaveAs(_path);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
workBook?.Close();
app?.Quit();
range = null;
sheet = null;
worksheet = null;
workBook = null;
app = null;
GC.Collect();
}
}
public void WriteToFile(string fileName, List <BreastCancerSampleItem> t)
{
var factory = new BreastCancerSampleItemPropertyFactory();
var headers1 = BreastCancerSampleItemFormat.DefaultHeader.Split('\t');
var converters1 = (from header in headers1
select factory.FindConverter(header)).ToArray();
var filtered = (from index in Enumerable.Range(0, headers1.Length)
let oldheader = headers1[index]
let oldconverter = converters1[index]
where t.Any(m => !string.IsNullOrEmpty(oldconverter.GetProperty(m)))
select new { Header = oldheader, Converter = oldconverter }).ToArray();
var xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
try
{
Workbook workbook = xlApp.Workbooks.Add();
try
{
Worksheet workSheet = workbook.Worksheets[1];
for (int j = 0; j < filtered.Length; j++)
{
workSheet.Cells[1, j + 1] = filtered[j].Header;
Range r = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, j + 1];
r.EntireColumn.NumberFormat = "@";
}
for (int i = 0; i < t.Count; i++)
{
var row = i + 2;
try
{
for (int j = 0; j < filtered.Length; j++)
{
workSheet.Cells[row, j + 1] = filtered[j].Converter.GetProperty(t[i]);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
break;
}
var position = "A" + row.ToString();
Range range = workSheet.Range[position];
var value = range.Value2;
if (t[i].Dataset.StartsWith("GSE"))
{
var link = string.Format(@"http://www.ncbi.nlm.nih.gov/geo/query/acc.cgi?acc={0}", t[i].Dataset);
workSheet.Hyperlinks.Add(range, link);
}
else if (t[i].Dataset.StartsWith("E-"))
{
var link = string.Format(@"http://www.ebi.ac.uk/arrayexpress/experiments/{0}", t[i].Dataset);
workSheet.Hyperlinks.Add(range, link);
}
else
{
var link = string.Format(@"https://www.google.com/search?q={0}", t[i].Dataset);
workSheet.Hyperlinks.Add(range, link);
}
if (t[i].Sample.StartsWith("GSM"))
{
position = "B" + row.ToString();
range = workSheet.Range[position];
var link = string.Format(@"http://www.ncbi.nlm.nih.gov/geo/query/acc.cgi?acc={0}", t[i].Sample);
workSheet.Hyperlinks.Add(range, link);
}
}
workbook.Saved = true;
workbook.SaveAs(fileName, XlFileFormat.xlExcel12);
}
finally
{
workbook.Close(false);
}
}
finally
{
xlApp.Quit();
}
}
static int Main(string[] args)
{
#region Command Line argument processing
if ( args.Contains ("--help") )
{
Console.WriteLine ("This tool depends on Microsoft Office 2010+");
Console.WriteLine ("Valid switches are");
Console.WriteLine ("-ignore <comma separated list of file extn> : Ignore line with pattern");
Console.WriteLine ("-include <comma separated list of file extn> : Filter for pattern");
Console.WriteLine ("-concurrency <No of minutes> : Concurrency Window in minutes");
Console.WriteLine ("-toppages <No of pages> : No of Top Pages/day");
Console.WriteLine ("-peaks <No of peaks> : No of Peak Hours to consider");
Console.WriteLine ("-param <comma seperated list of patterns> : Summarize specific URL parameters");
Console.WriteLine ("-export <export filename> : Excel file report name, default will be with time stamp");
Console.WriteLine ("-folder <log file folder path> : Current folder will be defaulted. All .log files in this folder will be processed.");
Console.WriteLine ("Add a space after the pattern if you want extension mapping (e.g. .aspx ,.jpg)");
return 0;
}
if ( args.Length % 2 != 0 )
{
throw new ArgumentException ("Command line arguments not valid, try --help to see valid ones!");
}
Dictionary<string, string> cmdArgs = new Dictionary<string, string> ();
for ( int i = 0; i < args.Length; i += 2 )
{
cmdArgs.Add (args[i].ToLower (), args[i + 1]);
}
List<string> ignoredTypes = new List<string> (), filterTypes = new List<string> (), hitsPerURLParams = new List<string> ();
if ( cmdArgs.ContainsKey (IgnoreSwitch) )
{
ignoredTypes = cmdArgs[IgnoreSwitch].ToLower ().Split (',').ToList ();
}
if ( cmdArgs.ContainsKey (FilterSwitch) )
{
filterTypes = cmdArgs[FilterSwitch].ToLower ().Split (',').ToList ();
}
if ( cmdArgs.ContainsKey (URLParamsSwitch) )
{
hitsPerURLParams = cmdArgs[URLParamsSwitch].ToLower ().Split (',').ToList ();
}
float concurrencyWindow = 5;
if ( cmdArgs.ContainsKey (ConcurrencySwitch) )
{
concurrencyWindow = float.Parse (cmdArgs[ConcurrencySwitch]);
}
else
cmdArgs.Add (ConcurrencySwitch, concurrencyWindow.ToString ());
int topPagesPerDay = 10;
if ( cmdArgs.ContainsKey (TopPagesSwitch) )
{
topPagesPerDay = int.Parse (cmdArgs[TopPagesSwitch]);
}
else
cmdArgs.Add (TopPagesSwitch, topPagesPerDay.ToString ());
int peakHoursCount = 3;
if ( cmdArgs.ContainsKey (PeakHoursSwitch) )
{
peakHoursCount = int.Parse (cmdArgs[PeakHoursSwitch]);
}
else
cmdArgs.Add (PeakHoursSwitch, peakHoursCount.ToString ());
string exportFileName = null;
if ( cmdArgs.ContainsKey (ExportFileSwitch) )
{
try
{
exportFileName = Path.GetFullPath (cmdArgs[ExportFileSwitch]);
}
catch ( Exception e )
{
Console.WriteLine ("Error creating report file:{0},{1}", e.GetType ().Name, e.Message);
}
}
if ( exportFileName == null )
{
exportFileName = Path.GetFullPath ("Processing results_" + DateTime.Now.ToString ("dd_hh_mm") + ".xlsx");
Console.WriteLine ("Writing output to {0}", exportFileName);
}
string curerntPath;
if ( cmdArgs.ContainsKey (FolderSwitch) )
{
try
{
curerntPath = Path.GetFullPath (cmdArgs[FolderSwitch]);
}
catch ( Exception e )
{
Console.WriteLine ("Error accessing folder {0}:{1},{2}", cmdArgs[FolderSwitch], e.GetType ().Name, e.Message);
return 1;
}
}
else
{
curerntPath = Directory.GetCurrentDirectory ();
Console.WriteLine ("Working on IIS logs from current folder {0}", curerntPath);
}
#endregion
Stopwatch stopWatch = new Stopwatch ();
stopWatch.Start ();
//var files = Directory.GetFiles(curerntPath, "*.log").ToList();
var files = new DirectoryInfo (curerntPath)
.GetFiles ("*.log")
.OrderBy (f => f.LastWriteTime)
.Select (f => f.FullName)
.ToArray ();
var totalFile = files.Count ();
if ( totalFile == 0 )
{
Console.WriteLine ("No log files found!!");
return 0;
}
Console.WriteLine ("Found {0} log files", totalFile);
var tmpFile = System.IO.Path.GetTempFileName ();
int fileCount = 0;
int headerRows = 4;
int entryCount = 0;
List<IISLogEntry> processingList = new List<IISLogEntry> ();
DateTime nextTime = DateTime.MinValue;
long TotalHits = 0, ServedRequests = 0;
List<ConcurrentRequest> requests = new List<ConcurrentRequest> ();
HashSet<string> uniqueIPs = new HashSet<string> ();
Dictionary<int, int> httpStatus = new Dictionary<int, int> ();
Dictionary<string, MethodInfo> pageViewsForPeriod = new Dictionary<string, MethodInfo> ();
int totalDays = 0, totalHours = 0;
Dictionary<string, MethodInfo> pageViewsDaily = new Dictionary<string, MethodInfo> ();
HashSet<MethodInfo> dailyPages = new HashSet<MethodInfo> ();
Dictionary<string, MethodInfo> pageViewsHourly = new Dictionary<string, MethodInfo> ();
HashSet<MethodInfo> hourlyPages = new HashSet<MethodInfo> ();
//hits for key URL parameters
Dictionary<string, MethodInfo> urlParamHits = new Dictionary<string, MethodInfo> ();
DateTime firstEntry = DateTime.MinValue, lastEntry = DateTime.MinValue;
//placeholder
HashSet<MethodInfo> filteredEntries = new HashSet<MethodInfo> ();
int startRow = 1, startCol = 1;
int reportRow = 2, reportCol = 1;
Console.WriteLine ("Preparing to Process..");
foreach ( var f in files )
{
try
{
++fileCount;
var progress = fileCount * 100 / totalFile;
IEnumerable<string> matchedEntries = null;
var contents = File.ReadLines (f);
Dictionary<string, int> fieldIndex = new Dictionary<string, int> ();
#region Content filter
if ( filterTypes.Any () && ignoredTypes.Any () )
matchedEntries = contents.Where (s => s.StartsWith ("#") ||
( filterTypes.Any (x => s.ToLower ().Contains (x)) &&
!ignoredTypes.Any (x => s.ToLower ().Contains (x)) ));
else if ( filterTypes.Any () )
matchedEntries = contents.Where (s => s.StartsWith ("#") || filterTypes.Any (x => s.ToLower ().Contains (x)));
else if ( ignoredTypes.Any () )
matchedEntries = contents.Where (s => s.StartsWith ("#") || !ignoredTypes.Any (x => s.ToLower ().Contains (x)));
else
matchedEntries = contents;
foreach ( var rawLogEntry in matchedEntries )
{
IISLogEntry logEntry;
if ( rawLogEntry.StartsWith ("#") )
{
if ( rawLogEntry.StartsWith ("#Fields:") )
fieldIndex = ParseHeaderFields (rawLogEntry);
}
else
{
Console.Write ("\r{0} File {1} of {2} files ({3}%), processing {4} ", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"), fileCount, totalFile, progress, ++TotalHits);
var columns = rawLogEntry.Split (' ');
logEntry = new IISLogEntry ()
{
TimeStamp = DateTime.Parse (columns[0] + " " + columns[1]),
ClientIPAddress = fieldIndex.ContainsKey (IISLogEntry.propClientIPAddress) ? columns[fieldIndex[IISLogEntry.propClientIPAddress]] : String.Empty,
UserName = fieldIndex.ContainsKey (IISLogEntry.propUserName) ? columns[fieldIndex[IISLogEntry.propUserName]] : String.Empty,
ServiceNameandInstanceNumber = fieldIndex.ContainsKey (IISLogEntry.propServiceNameandInstanceNumber) ? columns[fieldIndex[IISLogEntry.propServiceNameandInstanceNumber]] : String.Empty,
ServerName = fieldIndex.ContainsKey (IISLogEntry.propServerName) ? columns[fieldIndex[IISLogEntry.propServerName]] : String.Empty,
ServerIPAddress = fieldIndex.ContainsKey (IISLogEntry.propServerIPAddress) ? columns[fieldIndex[IISLogEntry.propServerIPAddress]] : String.Empty,
ServerPort = fieldIndex.ContainsKey (IISLogEntry.propClientIPAddress) ? Int32.Parse (columns[fieldIndex[IISLogEntry.propServerPort]]) : 0,
Method = fieldIndex.ContainsKey (IISLogEntry.propMethod) ? columns[fieldIndex[IISLogEntry.propMethod]] : String.Empty,
URIStem = fieldIndex.ContainsKey (IISLogEntry.propURIStem) ? columns[fieldIndex[IISLogEntry.propURIStem]] : String.Empty,
URIQuery = fieldIndex.ContainsKey (IISLogEntry.propURIQuery) ? columns[fieldIndex[IISLogEntry.propURIQuery]] : String.Empty,
HTTPStatus = fieldIndex.ContainsKey (IISLogEntry.propHTTPStatus) ? Int32.Parse (columns[fieldIndex[IISLogEntry.propHTTPStatus]]) : 0,
//Win32Status = fieldIndex.ContainsKey(IISLogEntry.propWin32Status) ? Int32.Parse(row[fieldIndex[IISLogEntry.propWin32Status]]) : 0,
BytesSent = fieldIndex.ContainsKey (IISLogEntry.propBytesSent) ? Int32.Parse (columns[fieldIndex[IISLogEntry.propBytesSent]]) : 0,
BytesReceived = fieldIndex.ContainsKey (IISLogEntry.propBytesReceived) ? Int32.Parse (columns[fieldIndex[IISLogEntry.propBytesReceived]]) : 0,
TimeTaken = fieldIndex.ContainsKey (IISLogEntry.propTimeTaken) ? Int32.Parse (columns[fieldIndex[IISLogEntry.propTimeTaken]]) : 0,
ProtocolVersion = fieldIndex.ContainsKey (IISLogEntry.propProtocolVersion) ? columns[fieldIndex[IISLogEntry.propProtocolVersion]] : String.Empty,
Host = fieldIndex.ContainsKey (IISLogEntry.propHost) ? columns[fieldIndex[IISLogEntry.propHost]] : String.Empty,
UserAgent = fieldIndex.ContainsKey (IISLogEntry.propUserAgent) ? columns[fieldIndex[IISLogEntry.propUserAgent]] : String.Empty,
Cookie = fieldIndex.ContainsKey (IISLogEntry.propCookie) ? columns[fieldIndex[IISLogEntry.propCookie]] : String.Empty,
Referrer = fieldIndex.ContainsKey (IISLogEntry.propReferrer) ? columns[fieldIndex[IISLogEntry.propReferrer]] : String.Empty,
ProtocolSubstatus = fieldIndex.ContainsKey (IISLogEntry.propProtocolSubstatus) ? columns[fieldIndex[IISLogEntry.propProtocolSubstatus]] : String.Empty
};
#endregion
#region entry processing
var url = logEntry.URIStem.ToLower ();
#region HTTP status codes & IP
if ( httpStatus.ContainsKey (logEntry.HTTPStatus) )
httpStatus[logEntry.HTTPStatus]++;
else
httpStatus.Add (logEntry.HTTPStatus, 1);
if ( !uniqueIPs.Contains (logEntry.ClientIPAddress) )
uniqueIPs.Add (logEntry.ClientIPAddress);
#endregion
if ( nextTime == DateTime.MinValue )
{
firstEntry = logEntry.TimeStamp;
lastEntry = logEntry.TimeStamp;
nextTime = logEntry.TimeStamp.Date.
AddHours (logEntry.TimeStamp.Hour).
AddMinutes (logEntry.TimeStamp.Minute).
AddMinutes (concurrencyWindow);
}
if ( logEntry.TimeStamp > nextTime )
{
if ( processingList.Any () )
{
requests.Add (new ConcurrentRequest (concurrencyWindow)
{
TimeStamp = nextTime,
Transactions = processingList.Count,
AverageResponseTime = processingList.Average (p => p.TimeTaken),
BytesSent = processingList.Sum (t => t.BytesSent)
});
processingList.Clear ();
}
else
{
requests.Add (new ConcurrentRequest (concurrencyWindow)
{
TimeStamp = nextTime,
Transactions = 0,
AverageResponseTime = 0,
BytesSent = 0
});
}
nextTime = nextTime.AddMinutes (concurrencyWindow);
}
if ( lastEntry.Hour != logEntry.TimeStamp.Hour )
{
totalHours++;
AddHourlyPages (pageViewsHourly, hourlyPages, lastEntry);
}
if ( lastEntry.Date != logEntry.TimeStamp.Date )
{
totalDays++;
AddDailyPages (pageViewsDaily, dailyPages, lastEntry);
}
//add the current one to future processing, otherwise one in teh borderlien will be missing
if ( logEntry.HTTPStatus == 200 )
{
processingList.Add (logEntry);
ServedRequests++;
if ( pageViewsForPeriod.ContainsKey (url) )
pageViewsForPeriod[url].Hit (logEntry.TimeTaken);
else
pageViewsForPeriod.Add (url, new MethodInfo (logEntry.URIStem, logEntry.TimeTaken));
if ( lastEntry.Hour == logEntry.TimeStamp.Hour )
{
if ( pageViewsHourly.ContainsKey (url) )
pageViewsHourly[url].Hit (logEntry.TimeTaken);
else
pageViewsHourly.Add (url, new MethodInfo (logEntry.URIStem, logEntry.TimeTaken));
}
if ( lastEntry.Date == logEntry.TimeStamp.Date )
{
if ( pageViewsDaily.ContainsKey (url) )
pageViewsDaily[url].Hit (logEntry.TimeTaken);
else
pageViewsDaily.Add (url, new MethodInfo (logEntry.URIStem, logEntry.TimeTaken));
}
if ( hitsPerURLParams.Any () )
{
var urlParam = hitsPerURLParams.Where (p => logEntry.URIQuery.Contains (p)).FirstOrDefault ();
if ( urlParam != null && urlParam != String.Empty )
{
if ( urlParamHits.ContainsKey (url) )
urlParamHits[url].Hit (logEntry.TimeTaken);
else
urlParamHits.Add (url, new MethodInfo (urlParam, logEntry.TimeTaken));
}
}
}
lastEntry = logEntry.TimeStamp;
}
}
if ( processingList.Any () )
{
requests.Add (new ConcurrentRequest (concurrencyWindow)
{
TimeStamp = nextTime,
Transactions = processingList.Count,
AverageResponseTime = processingList.Average (p => p.TimeTaken),
BytesSent = processingList.Sum (t => t.BytesSent)
});
processingList.Clear ();
}
AddHourlyPages (pageViewsHourly, hourlyPages, lastEntry);
AddDailyPages (pageViewsDaily, dailyPages, lastEntry);
#endregion
}
catch ( Exception e )
{
Console.WriteLine ("Error!! {0}:{1} - {2}", e.GetType ().Name, e.Message, e.StackTrace);
Debug.WriteLine ("Error!! {0}:{1}", e.GetType ().Name, e.Message);
}
}
Console.WriteLine ("\nGenerating Statistics");
#region resultprocessing
IEnumerable<MethodInfo> topPages;
IEnumerable<IGrouping<DateTime, MethodInfo>> hourlyHits = null;
long peakHits;
IEnumerable<IGrouping<DateTime, MethodInfo>> peakHourPages = null;
try
{
excelApp = new Application ();
excelApp.Visible = false;
reportSpreadsheet = excelApp.Workbooks.Add ();
excelApp.Calculation = XlCalculation.xlCalculationManual;
reportSheet = reportSpreadsheet.ActiveSheet;
#region Concurrent Users
if ( requests.Any () )
{
Console.WriteLine ("{0} Calculating Concurrent User Count", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
reportSheet.Name = "Concurrent Users";
reportSheet.Cells[reportRow, reportCol++] = "Timestamp";
reportSheet.Cells[reportRow, reportCol++] = "Requests";
reportSheet.Cells[reportRow, reportCol++] = "TPS";
reportSheet.Cells[reportRow, reportCol++] = "Average Response Time";
reportSheet.Cells[reportRow, reportCol++] = "Concurrent Users (based on Little's Law)";
reportSheet.Cells[reportRow, reportCol++] = "Bytes Sent";
reportSheet.Cells[reportRow, reportCol++] = "Network Speed (Mbps)";
foreach ( var p in requests )
{
reportCol = 1; reportRow++;
reportSheet.Cells[reportRow, reportCol++] = p.TimeStamp;
reportSheet.Cells[reportRow, reportCol++] = p.Transactions;
reportSheet.Cells[reportRow, reportCol++] = p.Tps;
reportSheet.Cells[reportRow, reportCol++] = p.AverageResponseTime;
reportSheet.Cells[reportRow, reportCol++] = p.ConcurrentUsers;
reportSheet.Cells[reportRow, reportCol++] = p.BytesSent;
reportSheet.Cells[reportRow, reportCol++] = p.NetworkSpeed;
}
}
#endregion
reportSpreadsheet.Application.DisplayAlerts = false;
reportSpreadsheet.SaveAs (exportFileName, ConflictResolution: XlSaveConflictResolution.xlLocalSessionChanges);
#region Page visit Summary
if ( pageViewsForPeriod.Any () )
{
Console.WriteLine ("{0} Genrating Page visit Summary", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
reportSheet = reportSpreadsheet.Worksheets.Add (Type.Missing, reportSheet, 1);
reportSheet.Name = "Page visit Summary";
startRow = startCol = 1;
startRow = CollectionToTable (pageViewsForPeriod.Values, startRow, startCol, "Page visit Summary (for the period)");
reportSheet.Shapes.AddChart (XlChartType.xlLine).Select ();
excelApp.ActiveChart.SetSourceData (Source: reportSheet.get_Range ("A1:B" + startRow));
reportSheet.Shapes.AddChart (XlChartType.xlPie).Select ();
excelApp.ActiveChart.SetSourceData (Source: reportSheet.get_Range ("A1:B" + startRow));
excelApp.ActiveChart.ClearToMatchStyle ();
try
{
excelApp.ActiveChart.ChartStyle = 256;
}
catch ( Exception e )
{ }
excelApp.ActiveChart.SetElement (Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
excelApp.ActiveChart.ChartTitle.Text = "Page visit Summary (for the period) Most Visited Pages";
reportSheet.Shapes.AddChart (XlChartType.xlBarClustered).Select ();
excelApp.ActiveChart.SetSourceData (Source: reportSheet.get_Range ("A1:D" + startRow));
excelApp.ActiveChart.ClearToMatchStyle ();
try
{
excelApp.ActiveChart.ChartStyle = 222;
}
catch ( Exception e )
{ }
excelApp.ActiveChart.SetElement (Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
excelApp.ActiveChart.ChartTitle.Text = "Page visit Summary (for the period) Average Response Time";
SpreadCharts (reportSheet);
}
#endregion
#region Daily Analysis
if ( dailyPages.Any () )
{
Console.WriteLine ("{0} Genrating Daily Statistics", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
reportSheet = reportSpreadsheet.Worksheets.Add (Type.Missing, reportSheet, 1);
reportSheet.Name = "Daily Analysis";
foreach ( var d in dailyPages.Select (p => p.Timestamp).Distinct () )
{
filteredEntries.UnionWith (dailyPages.Where (p => p.Timestamp == d.Date)
.OrderByDescending (p => p.Hits).Take (topPagesPerDay));
//Debug.WriteLine("Date: {0} - {1}", date, MethodInfo.TotalHits(dailyPages.Where(p => p.Timestamp == d.Date)));
}
topPages = filteredEntries.Where (p => filteredEntries.Count (q => q.Url == p.Url) > totalDays / 2);
startRow = startCol = 1;
AddChartFromSeries (startRow, startCol, "Daily Top Pages - Visits Trend", topPages, p => p.Hits, d => d.ToString (DateTimeFormatInfo.CurrentInfo.ShortDatePattern));
startRow = reportRow + 10;
startCol = 1;
AddChartFromSeries (startRow, startCol, "Daily Top Pages - Response Time(Average) Trend", topPages, p => p.AvgResponseTime, d => d.ToString (DateTimeFormatInfo.CurrentInfo.ShortDatePattern));
startRow = reportRow + 10;
startCol = 1;
AddChartFromSeries (startRow, startCol, "Daily Top Pages - Response Time(90%tile) Trend", topPages, p => p.NinetiethPercentile, d => d.ToString (DateTimeFormatInfo.CurrentInfo.ShortDatePattern));
startRow = 1;
startCol = 30;
filteredEntries.Clear ();
//reportSheet.Cells[reportRow, reportCol] = "Date";
foreach ( var d in dailyPages.Select (p => p.Timestamp).Distinct () )
{
filteredEntries.UnionWith (dailyPages.Where (p => p.Timestamp == d.Date)
.OrderByDescending (p => p.NinetiethPercentile).Take (topPagesPerDay));
}
topPages = filteredEntries.Where (p => filteredEntries.Count (q => q.Url == p.Url) > totalDays / 2);
AddChartFromSeries (startRow, startCol, "Daily Slow Pages - Response Time(90%tile) Trend", topPages, p => p.NinetiethPercentile, d => d.ToString (DateTimeFormatInfo.CurrentInfo.ShortDatePattern));
startRow = reportRow + 10;
startCol = 30;
filteredEntries.Clear ();
//reportSheet.Cells[reportRow, reportCol] = "Date";
foreach ( var d in dailyPages.Select (p => p.Timestamp).Distinct () )
{
filteredEntries.UnionWith (dailyPages.Where (p => p.Timestamp == d.Date)
.OrderByDescending (p => p.AvgResponseTime).Take (topPagesPerDay));
//Debug.WriteLine("Date: {0} - {1}", date, MethodInfo.TotalHits(dailyPages.Where(p => p.Timestamp == d.Date)));
}
topPages = filteredEntries.Where (p => filteredEntries.Count (q => q.Url == p.Url) > totalDays / 2);
AddChartFromSeries (startRow, startCol, "Daily Slow Pages - Response Time(Average) Trend", topPages, p => p.AvgResponseTime, d => d.ToString (DateTimeFormatInfo.CurrentInfo.ShortDatePattern));
SpreadCharts (reportSheet);
}
#endregion
#region Hourly analysis
if ( hourlyPages.Any () )
{
Console.WriteLine ("{0} Genrating Hourly Statistics", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
reportSheet = reportSpreadsheet.Worksheets.Add (Type.Missing, reportSheet, 1);
reportSheet.Name = "Hourly Analysis";
startRow = 1;
startCol = 1;
filteredEntries.Clear ();
foreach ( var d in hourlyPages.Select (p => p.Timestamp).Distinct () )
{
filteredEntries.UnionWith (hourlyPages.Where (p => p.Timestamp == d.Date.AddHours (d.Hour))
.OrderByDescending (p => p.Hits).Take (topPagesPerDay));
//Debug.WriteLine("Date: {0} - {1}", date, MethodInfo.TotalHits(dailyPages.Where(p => p.Timestamp == d.Date)));
}
var totalHits = hourlyPages.Sum (p => p.Hits);
//filter out top pages which are there for 10% of time or 2% traffic
topPages = filteredEntries.Where (p => filteredEntries.Count (q => q.Url == p.Url) > totalHours / 10 || p.Hits > totalHits * 2 / 100);
startRow += AddChartFromSeries (startRow, startCol, "Hourly Top Pages Summary (By Hits)", topPages, p => p.Hits, d => d.ToString ());
excelApp.ActiveChart.Axes (XlAxisType.xlCategory).CategoryType = XlCategoryType.xlCategoryScale;
hourlyHits = hourlyPages.GroupBy (p => p.Timestamp, q => q);
peakHits = hourlyHits.Select (p => p.Sum (q => q.Hits)).OrderByDescending (p => p).Take (peakHoursCount).Min ();
peakHourPages = hourlyHits.Where (p => p.Sum (q => q.Hits) >= peakHits);
startRow += 10; startCol = 1;
startRow += AddChartFromSeries (startRow, startCol, "Peak Hour Top Pages Summary (By Hits)", peakHourPages.SelectMany (g => g.Where (p => p.Hits > peakHits * 2 / 100)), p => p.Hits, d => d.ToString ());
excelApp.ActiveChart.Axes (XlAxisType.xlCategory).CategoryType = XlCategoryType.xlCategoryScale;
CollectionToTable (peakHourPages.SelectMany (g => g), startRow + 10, 1, "Peak Hour Pages", true);
SpreadCharts (reportSheet);
}
#endregion
#region URL Param Hits Summary
if ( hitsPerURLParams.Any () )
{
Console.WriteLine ("{0} Genrating URL parameter statistics", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
reportSheet = reportSpreadsheet.Worksheets.Add (Type.Missing, reportSheet, 1);
startRow = startCol = 1;
reportSheet.Name = "URL Parameters";
CollectionToTable (urlParamHits.Values, startRow, startCol, "URL Parameters Summary (for the period)");
}
#endregion
#region Summary
Console.WriteLine ("{0} Genrating Summary", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
reportSheet = reportSpreadsheet.Worksheets.Add (reportSheet, Type.Missing, 1);
reportRow = reportCol = 1;
reportSheet.Name = "Summary";
reportSheet.Cells[reportRow, 1] = "Running From";
reportSheet.Cells[reportRow++, 2] = curerntPath;
reportSheet.Cells[reportRow, 1] = "Commandline Argument";
reportSheet.Cells[reportRow++, 2] = string.Join (";", cmdArgs.Select (x => x.Key + "=" + x.Value));
reportSheet.Cells[reportRow, 1] = "Files Processed";
reportSheet.Cells[reportRow++, 2] = fileCount;
reportSheet.Cells[reportRow, 1] = "From";
reportSheet.Cells[reportRow++, 2] = firstEntry;
reportSheet.Cells[reportRow, 1] = "To";
reportSheet.Cells[reportRow++, 2] = lastEntry;
reportSheet.Cells[reportRow, 1] = "TotalHits";
reportSheet.Cells[reportRow++, 2] = TotalHits;
reportSheet.Cells[reportRow, 1] = "Average Transactions/Sec";
reportSheet.Cells[reportRow++, 2] = requests.Average (p => p.Tps);
if ( hourlyHits!=null )
{
reportSheet.Cells[reportRow, 1] = "Average Transactions/Hour";
reportSheet.Cells[reportRow++, 2] = hourlyHits.Average (p => p.Sum (q => q.Hits));
}
if ( peakHourPages!=null )
{
reportSheet.Cells[reportRow, 1] = "Peak Hour Transactions/Hour";
reportSheet.Cells[reportRow++, 2] = peakHourPages.Average (p => p.Sum (q => q.Hits));
reportSheet.Cells[reportRow, 1] = "Peak Hour Transactions/Sec";
reportSheet.Cells[reportRow++, 2] = peakHourPages.Average (p => p.Sum (q => q.Hits) / 3600);
}
reportSheet.Cells[reportRow, 1] = "UniqueIPs";
reportSheet.Cells[reportRow++, 2] = uniqueIPs.Count;
reportSheet.Cells[reportRow, 1] = "ServedRequests";
reportSheet.Cells[reportRow++, 2] = ServedRequests;
reportRow += 10;
reportSheet.Cells[reportRow++, 1] = "Http Status code summary";
reportSheet.Cells[reportRow, 1] = "HTTP Code";
reportSheet.Cells[reportRow++, 2] = "Count";
foreach ( var i in httpStatus )
{
reportSheet.Cells[reportRow, reportCol++] = i.Key;
reportSheet.Cells[reportRow++, reportCol--] = ( i.Value );
}
#endregion
}
catch ( Exception e )
{
Console.WriteLine ("Error!! {0}:{1} - {2}", e.GetType ().Name, e.Message, e.StackTrace);
Debug.WriteLine ("Error!! {0}:{1}", e.GetType ().Name, e.Message);
}
finally
{
if ( excelApp != null )
{
excelApp.Calculation = XlCalculation.xlCalculationAutomatic;
if ( reportSpreadsheet != null )
{
reportSpreadsheet.Save ();
reportSpreadsheet.Close ();
excelApp.Quit ();
}
}
File.Delete (tmpFile);
stopWatch.Stop ();
Console.WriteLine ("Done, Final time : {0}", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
}
#endregion
return 0;
}
public void Dispose()
{
workBook.Close(false);
excelApp.Quit();
}
private void CreateSpreadSheet(ADODB.Recordset objRS, string strPath)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlWB = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets xlSheets = xlWB.Worksheets;
Worksheet xlWS = new Worksheet();
xlWS = xlSheets.get_Item(1);
//adding headers to spreadsheet
xlWS.Columns["AM"].NumberFormat = "@";
int t = 1;
while (objRS.EOF != true)
{
for (int j = 0; j < objRS.Fields.Count; j++)
{
if (t == 1)
{
xlWS.Cells.set_Item(t, j + 1, objRS.Fields[j].Name.ToString());
}
// try - to catch bogus data that might appear in recordset (bad characters, etc.)
try
{
xlWS.Cells.set_Item(t + 1, j + 1, objRS.Fields[j].Value);
}
catch
{
xlWS.Cells.set_Item(t + 1, j + 1, "");
}
}
if (objRS.EOF != true)
{
objRS.MoveNext();
t++;
}
}
xlWS.Columns.AutoFit();
if (File.Exists(strPath))
{
File.Delete(strPath);
}
xlWB.SaveAs(strPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Kill excel object from memory
xlWB.Close();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWS);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlWS = null;
xlWB = null;
xlApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
public void Dispose()
{
Workbook.Close();
}
private void createNewDataFile()
{
try
{
_xlwb = _xlApp.Workbooks.Add(Type.Missing);
_xlws = _xlwb.ActiveSheet;
_xlws.Name = dataSheet;
_xlws = (Worksheet)_xlwb.Worksheets[1];
_xlws.Cells[1, DATA_ROWS.Team_Num] = "Team#";
_xlws.Cells[1, DATA_ROWS.Match_Num] = "Match#";
_xlws.Cells[1, DATA_ROWS.Scout_Name] = "Scout_Name";
_xlws.Cells[1, DATA_ROWS.Alliance] = "Alliance";
_xlws.Cells[1, DATA_ROWS.Auto_Defense_Reached] = "Auto_Defense_Reached";
_xlws.Cells[1, DATA_ROWS.Auto_Defense_Crossed] = "Auto_Defense_Crossed";
_xlws.Cells[1, DATA_ROWS.Auto_Low_Goal_Scored] = "Auto_Low_Goal_Scored";
_xlws.Cells[1, DATA_ROWS.Auto_High_Goal_Scored] = "Auto_High_Goal_Scored";
_xlws.Cells[1, DATA_ROWS.Auto_Starting_Position] = "Auto_Starting_Position";
_xlws.Cells[1, DATA_ROWS.Auto_Ending_Position] = "Auto_Ending_Position";
_xlws.Cells[1, DATA_ROWS.Tele_Portcullis] = "Tele_Portcullis";
_xlws.Cells[1, DATA_ROWS.Tele_Fries] = "Tele_Fries";
_xlws.Cells[1, DATA_ROWS.Tele_Rampart] = "Tele_Rampart";
_xlws.Cells[1, DATA_ROWS.Tele_Moat] = "Tele_Moat";
_xlws.Cells[1, DATA_ROWS.Tele_Drawbridge] = "Tele_Drawbridge";
_xlws.Cells[1, DATA_ROWS.Tele_SallyPort] = "Tele_SallyPort";
_xlws.Cells[1, DATA_ROWS.Tele_RockWall] = "Tele_RockWall";
_xlws.Cells[1, DATA_ROWS.Tele_RoughTerrain] = "Tele_RoughTerrain";
_xlws.Cells[1, DATA_ROWS.Tele_LowBar] = "Tele_LowBar";
_xlws.Cells[1, DATA_ROWS.Tele_Low_Goal_Scored] = "Tele_Low_Goal_Scored";
_xlws.Cells[1, DATA_ROWS.Tele_High_Goal_Scored] = "Tele_High_Goal_Scored";
_xlws.Cells[1, DATA_ROWS.Tele_Missed_High_Goal] = "Tele_Missed_High_Goal";
_xlws.Cells[1, DATA_ROWS.Robot_Disabled] = "Robot_Disabled";
_xlws.Cells[1, DATA_ROWS.Time_Disabled] = "Time_Disabled";
_xlws.Cells[1, DATA_ROWS.End_Challenged] = "End_Challenged";
_xlws.Cells[1, DATA_ROWS.End_Scaled] = "End_Scaled";
_xlws.Cells[1, DATA_ROWS.Notes] = "Notes";
_xlwb.SaveAs(_DATA_DIRECTORY + _EXCEL_FILENAME, XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
_xlwb.Close(true, misValue, misValue);
_xlApp.Quit();
}
catch (Exception ex)
{
HandleException(ex);
}
}
private static void CloseExcel(_Worksheet worksheet, Application application, Workbook workbook, string path)
{
var range = worksheet.Range["A1", "F1"];
range.EntireColumn.AutoFit();
range.Font.Bold = true;
application.Visible = false;
application.UserControl = false;
workbook.SaveAs(path, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close();
}
public void Dispose()
{
covidenceExcel?.Close(0);
excel?.Quit();
}
public void generateReport(string fileName)
{
try
{
if (Owner != null)
{
if (Owner.TestRunner != null)
{
m_xlApp = new Microsoft.Office.Interop.Excel.Application();
m_xlWorkBook = m_xlApp.Workbooks.Add(misValue);
m_xlWorkSheet = (Worksheet)m_xlWorkBook.Worksheets.get_Item(1);
m_xlWorkSheet.Cells[1, 1] = "_Test_Phase_Number";
m_xlWorkSheet.Cells[1, 2] = "_Type";
m_xlWorkSheet.Cells[1, 3] = "Object Text";
m_xlWorkSheet.Cells[1, 4] = "_Expected_Result";
m_xlWorkSheet.Cells[1, 5] = "TestCaseName";
(m_xlWorkSheet.Range[m_xlWorkSheet.Cells[1, 1], m_xlWorkSheet.Cells[1, 5]]).Interior.Color = XlRgbColor.rgbLightCyan;
int j = 2;
foreach (ITestSuit suit in Owner.TestRunner.TestSuits)
{
foreach (ItestCase test in suit.TestCases)
{
m_xlWorkSheet.Cells[j, 1] = test.TestPhaseNumber;
m_xlWorkSheet.Cells[j, 2] = "Test Phase Number";
m_xlWorkSheet.Cells[j, 3] = test.TestPhaseNumber;
j++;
m_xlWorkSheet.Cells[j, 2] = "Test Description";
m_xlWorkSheet.Cells[j, 3] = test.TestDescription;
foreach (string precondition in test.TestPrecondition)
{
j++;
m_xlWorkSheet.Cells[j, 2] = "Test Pre-Condition";
m_xlWorkSheet.Cells[j, 3] = precondition + "\n";
}
foreach (string TestSteps in test.TestSteps)
{
j++;
m_xlWorkSheet.Cells[j, 2] = "Test Step";
m_xlWorkSheet.Cells[j, 3] = TestSteps + "\n";
}
string ExpectedResultString = "";
foreach (string ExpectedResult in test.ExpectedResult)
{
ExpectedResultString += ExpectedResult + "\n";
}
m_xlWorkSheet.Cells[j, 4] = ExpectedResultString;
m_xlWorkSheet.Cells[j, 5] = test.Name;
j++;
}
}
oRange = m_xlWorkSheet.Range[m_xlWorkSheet.Cells[1, 1], m_xlWorkSheet.Cells[j, 5]];
oRange.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
oRange.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
oRange.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
oRange.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
m_xlWorkBook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
m_xlWorkBook.Close(true, misValue, misValue);
m_xlApp.Quit();
releaseObject(m_xlWorkSheet);
releaseObject(m_xlWorkBook);
releaseObject(m_xlApp);
MessageBox.Show("Excel report Generated");
}
}
}
catch
{
}
}
public void createFile(SheetInformations[] sheetInformationsArray, string destFile)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook curWorkBook = null;
Workbook destWorkbook = null;
Worksheet workSheet = null;
Worksheet newWorksheet = null;
Object defaultArg = Type.Missing;
try
{
// Criando Arquivo Excel se ele não existe
Workbook tmpWorkBook = app.Workbooks.Add(defaultArg);
// newWorksheet = (Worksheet) destWorkbook.Worksheets[1];
// newWorksheet.Delete();
tmpWorkBook.SaveAs(destFile);
tmpWorkBook.Close(true, defaultArg, defaultArg);
Marshal.ReleaseComObject(tmpWorkBook);
foreach (var sheetInfo in sheetInformationsArray)
{
curWorkBook = app.Workbooks.Open(sheetInfo.file,
defaultArg, defaultArg, defaultArg, defaultArg,
defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg,
defaultArg, defaultArg);
workSheet = (Worksheet)curWorkBook.Sheets[sheetInfo.indexStr];
workSheet.UsedRange.Copy(defaultArg);
destWorkbook = app.Workbooks.Open(
destFile, defaultArg, false,
defaultArg, defaultArg,
defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg,
defaultArg, defaultArg);
newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet.Name = sheetInfo.newName;
// newWorksheet = (Worksheet) destWorkbook.Worksheets[sheetInfo.newName];
newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues,
XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
destWorkbook.Save();
curWorkBook.Close(false, sheetInfo.file, defaultArg);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (destWorkbook != null)
{
int nWorksheets = destWorkbook.Worksheets.Count;
if (nWorksheets > 1)
{
workSheet = (Worksheet)destWorkbook.Sheets[nWorksheets];
workSheet.Delete();
}
destWorkbook.Save();
destWorkbook.Close(defaultArg, defaultArg, defaultArg);
}
}
Marshal.ReleaseComObject(curWorkBook);
Marshal.ReleaseComObject(destWorkbook);
app.Quit();
}
private void btnXuatBaoCao_Click(object sender, EventArgs e)
{
if (chonDuongDan.ShowDialog() == DialogResult.OK)
{
string saveLocation = chonDuongDan.FileName;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Không thể sử dụng thư viện Excel", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
xlApp.Visible = false;
object misValue = System.Reflection.Missing.Value;
Workbook wb = xlApp.Workbooks.Add(misValue);
Worksheet ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
MessageBox.Show("Không thể tạo được WorkSheet", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
int row = 1;
string fontName = "Times New Roman";
int fontSizeTieuDe = 18;
int fontSizeTenTruong = 14;
int fontSizeNoiDung = 12;
//Xuất dòng Tiêu đề của File báo cáo: Lưu ý
Excel.Range row1_TieuDe_Baocao = ws.get_Range("A1", "E1");
row1_TieuDe_Baocao.Merge();
row1_TieuDe_Baocao.Font.Size = fontSizeTieuDe;
row1_TieuDe_Baocao.Font.Name = fontName;
row1_TieuDe_Baocao.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
if (dgvTon.Visible)
{
row1_TieuDe_Baocao.Value2 = "Báo cáo tồn";
}
if (dgvCongno.Visible)
{
row1_TieuDe_Baocao.Value2 = "Báo cáo công nợ";
}
//Tạo Ô Số Thứ Tự (STT)
Excel.Range row2_STT = ws.get_Range("A2", "A2");//Cột A dòng 2
row2_STT.Font.Size = fontSizeTenTruong;
row2_STT.Font.Name = fontName;
row2_STT.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row2_STT.Value2 = "STT";
//Tạo Ô t1
Excel.Range row2_col1 = ws.get_Range("B2", "B2");//Cột A dòng 2
row2_col1.Font.Size = fontSizeTenTruong;
row2_col1.Font.Name = fontName;
row2_col1.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (dgvTon.Visible)
{
row2_col1.Value2 = "Sách";
}
if (dgvCongno.Visible)
{
row2_col1.Value2 = "Khách hàng";
}
//Tạo Ô
Excel.Range row2_col2 = ws.get_Range("C2", "C2");//Cột B dòng 2
row2_col2.Font.Size = fontSizeTenTruong;
row2_col2.Font.Name = fontName;
row2_col2.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (dgvTon.Visible)
{
row2_col2.Value2 = "Tồn đầu";
}
if (dgvCongno.Visible)
{
row2_col2.Value2 = "Nợ đầu";
}
//Tạo Ô Thể loại
Excel.Range row2_col3 = ws.get_Range("D2", "D2");//Cột C dòng 2
row2_col3.Font.Size = fontSizeTenTruong;
row2_col3.Font.Name = fontName;
row2_col3.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
row2_col3.Value2 = "Phát sinh";
//Tạo Ô Tác giả
Excel.Range row2_col4 = ws.get_Range("E2", "E2");//Cột D dòng 2
row2_col4.Font.Size = fontSizeTenTruong;
row2_col4.Font.Name = fontName;
row2_col4.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (dgvTon.Visible)
{
row2_col4.Value2 = "Tồn cuối";
}
if (dgvCongno.Visible)
{
row2_col4.Value2 = "Nợ cuối";
}
//Tô nền vàng các cột tiêu đề:
Excel.Range row2_CotTieuDe = ws.get_Range("A2", "E2");
//nền vàng
row2_CotTieuDe.Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.Yellow);
//in đậm
row2_CotTieuDe.Font.Bold = true;
//chữ đen
row2_CotTieuDe.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Black);
int stt = 0;
row = 2; // dữ liệu bắt đầu từ dòng số 3 trong file Excel
if (dgvTon.Visible)
{
foreach (DataRow baocao in bcTon.Tables[0].Rows)
{
stt++;
row++;
dynamic[] arr = { stt, baocao[0], baocao[1], baocao[2], baocao[3] };
Excel.Range rowData = ws.get_Range("A" + row, "E" + row); // Lấy dòng thứ row để đổ dữ liệu
rowData.Font.Size = fontSizeNoiDung;
rowData.Font.Name = fontName;
rowData.Value2 = arr;
}
}
if (dgvCongno.Visible)
{
foreach (DataRow baocao in bcCongNo.Tables[0].Rows)
{
stt++;
row++;
dynamic[] arr = { stt, baocao[1], baocao[2], baocao[3], baocao[4] };
Excel.Range rowData = ws.get_Range("A" + row, "E" + row); // Lấy dòng thứ row để đổ dữ liệu
rowData.Font.Size = fontSizeNoiDung;
rowData.Font.Name = fontName;
rowData.Value2 = arr;
}
}
//Kẻ khung toàn bộ
BorderAround(ws.get_Range("A2", "E" + row));
ws.get_Range("A1", "E" + row).Columns.AutoFit();
//Lưu file
wb.SaveAs(saveLocation);
//Đóng file để hoàn tất quá trình lưu trữ
wb.Close(true, misValue, misValue);
//thoát và thu hồi bộ nhớ COM
xlApp.Quit();
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
MessageBox.Show("Lưu thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static void CompareLogs(string oldLogFileName, string newLogFileName)
{
// bool orgProgresSetting = false;//Håller på vad progress vad satt till innan
// if (MainForm.StopGracefully)
// return;
excelApp = new ApplicationClass();
// Skapa instansen här istället för globalt i denna klass, för att det inte ska skapas en Excelprocess om man bara kör en funktion i denna klassen
#region Progress
// Progress
// orgProgresSetting = MainForm.ShowProgress;//Håller på vad progress vad satt till innan
// if (MainForm.ShowProgress)
// MainForm.ShowProgress = false;
// MainForm.ShowTextProgress = true;//Visar texten bara
#endregion
var oldCi = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
#region read Old Log
var oldBook = new Hashtable();
try
{
// _compareProgress.StartTotal("Loading old Log...", 0);//-1 );
// Öppna den gamla loggen
oldLog = excelApp.Workbooks._Open(
oldLogFileName,
Type.Missing, // filename,
0,
Type.Missing,
XlPlatform.xlWindows, // XlTextQualifier.xlTextQualifierNone,
Type.Missing,
Type.Missing,
Type.Missing,
false, // COMMA
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
// get the collection of sheets in the workbook
var oldSheets = oldLog.Worksheets;
var numOfOldSheets = oldLog.Worksheets.Count;
//// get the first and only worksheet from the collection of worksheets
//// loop through 10 rows of the spreadsheet and place each row in the list view
// _compareProgress.StartTotal("Loading old Log sheets...", numOfOldSheets);//-1 );
// int sheetsDone = 0;//För progress
// Store old rows
for (var sheetNr = 1; sheetNr <= numOfOldSheets; sheetNr++)
{
var name = ((Worksheet)oldSheets.Item[sheetNr]).Name;
var oldWorksheet = (Worksheet)oldSheets.Item[sheetNr];
var oldRows = new Hashtable();
GetExcelRows(oldWorksheet, oldRows);
oldBook.Add(name, oldRows);
// _compareProgress.SetTotal(++sheetsDone);
// if (MainForm.StopGracefully)
// break;
}
}
catch (Exception e)
{
// MessageBox.Show("Error in retrieving old log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: " + e.Message + ").");
throw new Exception(
"Error in retrieving old log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: "
+ e.Message + ").",
e);
}
#endregion
#region read New Log
try
{
// if (MainForm.StopGracefully)
// return;
// _compareProgress.StartTotal("Loading new Log...", 0);//-1 );
// Excel.Workbook tempWB = newLog;
// Ev. ha If(_newLog ==null)...
newLog = excelApp.Workbooks._Open(
newLogFileName, // filename, filename,
Type.Missing,
0,
Type.Missing,
XlPlatform.xlWindows, // XlTextQualifier.xlTextQualifierNone,
Type.Missing,
Type.Missing,
Type.Missing,
false, // COMMA
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
var newSheets = newLog.Worksheets;
var numOfNewSheets = newLog.Worksheets.Count;
//// get the first worksheet from the collection of worksheets
//// loop through 10 rows of the spreadsheet and place each row in the list view
// Hashtable newBook = new Hashtable();
// Hashtable oldRows = new Hashtable();
// _compareProgress.StartTotal("Loading new Log sheets and compares...", numOfNewSheets);//-1 );
// int sheetsDone = 0;//För progress
var specialCaseForAllProfilesHandled = false;
// Compare to old rows
for (var sheetNr = 1; sheetNr <= numOfNewSheets; sheetNr++)
{
// if (MainForm.StopGracefully)
// break;
var name = ((Worksheet)newSheets.Item[sheetNr]).Name;
var newWorksheet = (Worksheet)newSheets.Item[sheetNr];
// Specialfall för AllProfiles-flikar
if (name.StartsWith("AllProfiles") && !specialCaseForAllProfilesHandled)
{
CheckAllProfiles(oldBook, newSheets);
specialCaseForAllProfilesHandled = true;
}
else if (name.StartsWith("AllProfiles") && specialCaseForAllProfilesHandled)
{
}
else if (oldBook.ContainsKey(name))
{
// if (MainForm.StopGracefully)
// break;
// Läs in hela nuv. nya arket till en HT
var newRows = new Hashtable();
GetExcelRows(newWorksheet, newRows);
var rows = 0;
var colums = 0; // newWorksheet.UsedRange.Columns.Count;
if (CompareExcelRows(newWorksheet, oldBook[name] as Hashtable, newRows, ref rows, ref colums))
{
#region Sortera
// if (MainForm.StopGracefully)
// break;
// Sortera på new
var column = GetStandardExcelColumnName(colums + 1);
var range = newWorksheet.Range["A4", column + rows.ToString(CultureInfo.InvariantCulture)];
// "IV"
if (name != "DatabaseInfo")
{
range.Sort(
range.Columns[colums + 1, Type.Missing],
XlSortOrder.xlDescending,
// För att felsöka Excelprogrammering, använd macroEdit for VB i excel...
// range.Columns[2,Type.Missing], Type.Missing, Excel.XlSortOrder.xlDescending
Type.Missing,
Type.Missing,
XlSortOrder.xlDescending,
Type.Missing,
XlSortOrder.xlDescending,
XlYesNoGuess.xlNo,
Type.Missing,
Type.Missing,
XlSortOrientation.xlSortColumns,
// ReSharper disable RedundantArgumentDefaultValue
XlSortMethod.xlPinYin,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal);
// ReSharper restore RedundantArgumentDefaultValue
}
// Ta bort "new"-kolumnen
range = newWorksheet.Range[column + 1, column + rows.ToString(CultureInfo.InvariantCulture)];
// "IV"
range.Delete(Type.Missing); // false//(object)false);//
// För old o diff överskriften...
if (name == "DatabaseInfo")
{
range = newWorksheet.Range["C:D", "C:D"];
range.EntireColumn.AutoFit(); // autofittar hela columnen för all som loggas
}
// newWorksheet.set
#endregion
}
// if (MainForm.StopGracefully)
// break;
}
else
{
Console.WriteLine(name + " didn't exist in old Excel book.");
}
// oldBook.Add(name, oldRows);
// _compareProgress.SetTotal(++sheetsDone);
}
// Spara en ny fil
// newLog.FullName = newLog.FullName + "-Compared";
// newLog.Save();// .Save();
newLogFileName = newLog.FullName.Replace(".xls", string.Empty) + "-Compared" + ".xls";
newLog.SaveCopyAs(newLogFileName); // MainForm.LastLogPath);//newLog.FullName + "-Compared");
newLog.Close(false, Type.Missing, Type.Missing);
// _book.Close(false, Type.Missing, Type.Missing);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
}
catch (Exception e)
{
// MessageBox.Show("Error in comparing old log with new log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: " + e.Message + ").");
// throw e;
throw new Exception(
"Error in comparing old log with new log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: "
+ e.Message + ").",
e);
}
#endregion - read new
System.Threading.Thread.CurrentThread.CurrentCulture = oldCi;
// MainForm.ShowTextProgress = false;//Visar inte ens texten längre
// MainForm.ShowProgress = orgProgresSetting;//Håller på vad progress vad satt till innan
// Stäng Excel
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
}
}
public void Export(string path, List <ExcelEntity> excelEntities)
{
var excel = new Application();
Workbook workBook = null;
Worksheet workSheet = null;
try
{
if (File.Exists(path))
{
workBook = excel.Workbooks.Open(path, ReadOnly: false);
foreach (Worksheet ws in workBook.Sheets)
{
if (ws.Name == "WFH")
{
workSheet = ws;
break;
}
}
if (workSheet != null)
{
workSheet.Cells.Clear();
}
else
{
workSheet = (Worksheet)workBook.Worksheets.Add();
workSheet.Name = "WFH";
}
}
else
{
workBook = excel.Workbooks.Add();
workSheet = (Worksheet)workBook.ActiveSheet;
workSheet.Name = "WFH";
}
ExcelEntity e;
int rowIndex = 1;
workSheet.Cells[rowIndex, ExcelColumns.Name] = nameof(e.Name);
workSheet.Cells[rowIndex, ExcelColumns.Email] = nameof(e.Email);
workSheet.Cells[rowIndex, ExcelColumns.EmployeeCode] = nameof(e.EmployeeCode);
workSheet.Cells[rowIndex, ExcelColumns.Date] = nameof(e.Date);
workSheet.Cells[rowIndex, ExcelColumns.TimeIn] = nameof(e.TimeIn);
workSheet.Cells[rowIndex, ExcelColumns.TimeOut] = nameof(e.TimeOut);
workSheet.Cells[rowIndex, ExcelColumns.Hours] = nameof(e.Hours);
workSheet.Cells[rowIndex, ExcelColumns.Remarks] = nameof(e.Remarks);
foreach (ExcelEntity entity in excelEntities)
{
rowIndex++;
workSheet.Cells[rowIndex, ExcelColumns.Name] = entity.Name;
workSheet.Cells[rowIndex, ExcelColumns.Email] = entity.Email;
workSheet.Cells[rowIndex, ExcelColumns.EmployeeCode] = $"'{entity.EmployeeCode}";
workSheet.Cells[rowIndex, ExcelColumns.Date] = entity.Date;
workSheet.Cells[rowIndex, ExcelColumns.TimeIn] = entity.TimeIn;
workSheet.Cells[rowIndex, ExcelColumns.TimeOut] = entity.TimeOut;
workSheet.Cells[rowIndex, ExcelColumns.Hours] = entity.Hours;
workSheet.Cells[rowIndex, ExcelColumns.Remarks] = entity.Remarks;
}
workBook.SaveAs(path);
}
finally
{
workBook?.Close();
}
}
private IEnumerable<List<string>> LoadFromFile(string filePath, string searchString)
{
if (File.Exists(filePath))
{
bool found = false;
List<string> list = new List<string>();
try
{
workbook = application.Workbooks.Open(filePath);
foreach (Worksheet worksheet in workbook.Worksheets)
{
for (int i = 1; i <= worksheet.UsedRange.Rows.Count; i++)
{
found = false;
list.Clear();
for (int j = 1; j <= worksheet.UsedRange.Columns.Count; j++)
{
var value = worksheet.Cells[i, j].Value;
if (value != null)
{
string stringValue = value.ToString();
list.Add(stringValue);
if (stringValue.Contains(searchString))
{
found = true;
}
}
}
if (found)
{
yield return list;
}
}
Marshal.FinalReleaseComObject(worksheet);
}
}
finally
{
Marshal.FinalReleaseComObject(workbook.Worksheets);
if (workbook != null)
{
workbook.Close();
}
Marshal.FinalReleaseComObject(workbook);
GC.Collect();
}
}
}
private void CloseFile()
{
_workbook.Close(0);
_excelApp.Quit();
_excelApp.Dispose();
}
//Open the first worksheet and get the information
//I am only outputting certain fields - the other fields maybe private data that will not be
//entered into the database
//For now I will try to have as parameters the fields I want to get out of the excel document
//Maybe they should be public data members or private data members with access functions
public static void readWorksheet(Workbook workbook, out string[] course, out string[] individualID, out string[] lastName, out string[] firstName, out string[] email)
{
//select the first sheet
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
//find the used range in worksheet
Range excelRange = worksheet.UsedRange;
//get an object array of all of the cells in the worksheet (their values)
object[,] valueArray = (object[,])excelRange.get_Value(
XlRangeValueDataType.xlRangeValueDefault);
//make an array of all the data in each of the columns
string[] yT = new string[worksheet.UsedRange.Rows.Count];
string[] matchCourse = new string[worksheet.UsedRange.Rows.Count];
string[] department = new string[worksheet.UsedRange.Rows.Count];
course = new string[worksheet.UsedRange.Rows.Count];
string[] section = new string[worksheet.UsedRange.Rows.Count];
string[] finalGrade = new string[worksheet.UsedRange.Rows.Count];
individualID = new string[worksheet.UsedRange.Rows.Count];
string[] classification = new string[worksheet.UsedRange.Rows.Count];
string[] enrollmentStatus = new string[worksheet.UsedRange.Rows.Count];
lastName = new string[worksheet.UsedRange.Rows.Count];
firstName = new string[worksheet.UsedRange.Rows.Count];
string[] nickname = new string[worksheet.UsedRange.Rows.Count];
string[] phone = new string[worksheet.UsedRange.Rows.Count];
email = new string[worksheet.UsedRange.Rows.Count];
string[] notes = new string[worksheet.UsedRange.Rows.Count];
//access the cells
//go through the rows and put the values from each column into the correct array
for (int row = 1; row < worksheet.UsedRange.Rows.Count; ++row)
{
int col = 1;
yT[row] = valueArray[row, col].ToString();
matchCourse[row] = valueArray[row, ++col].ToString();
department[row] = valueArray[row, ++col].ToString();
course[row] = valueArray[row, ++col].ToString();
section[row] = valueArray[row, ++col].ToString();
finalGrade[row] = valueArray[row, ++col].ToString();
individualID[row] = valueArray[row, ++col].ToString();
classification[row] = valueArray[row, ++col].ToString();
enrollmentStatus[row] = valueArray[row, ++col].ToString();
lastName[row] = valueArray[row, ++col].ToString();
firstName[row] = valueArray[row, ++col].ToString();
nickname[row] = valueArray[row, ++col].ToString();
phone[row] = valueArray[row, ++col].ToString();
email[row] = valueArray[row, ++col].ToString();
notes[row] = valueArray[row, ++col].ToString();
}
//clean up stuffs
workbook.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(workbook);
//I need to figure out how to access _excelApp to quit excel
//_excelApp.Quit();
}
private static void run(string outDir)
{
// search template
var inFile = Path.GetFullPath("Template.xls");
if (!File.Exists(inFile))
{
log("не найден файл шаблона " + inFile, Priority.High);
finish();
return;
}
report("шаблон " + inFile);
// search data files
var inDir = Path.GetFullPath("in");
if (!Directory.Exists(inDir))
{
Directory.CreateDirectory(inDir);
}
var files = Directory.GetFiles(inDir).Select(Path.GetFullPath).ToArray();
report("количество файлов в папке " + inDir + " равно " + files.Length);
if (files.Length == 0)
{
finish();
return;
}
_excel = new Application();
_excel.EnableEvents = false;
_template = _excel.Workbooks.Open(inFile);
_filledWorkbooks = new List<Workbook>(files.Length);
_filledWorkbooks.AddRange(files.Select(file =>
{
try
{
Workbook workBook = _excel.Workbooks.Open(file);
// validate in file
if (workBook.Sheets.Count != _template.Sheets.Count )
{
log("количесво листов в документе " + file + " равно " + workBook.Sheets.Count, Priority.High);
return null;
}
return workBook;
}
catch (Exception ex)
{
logError(file, ex, "Не смог прочитать файл", Priority.High);
return null;
}
;
}
).Where(x => x != null).ToArray());
report("количество соответсвующих шаблону документов Excel равно " + _filledWorkbooks.Count);
if (_filledWorkbooks.Count == 0)
{
finish();
return;
}
int errorCounter = 0;
int emptyCounter = 0;
foreach (Worksheet sheet in _template.Sheets)
{
report(sheet.Name);
var lastCell = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
var r = lastCell.Row;
var c = sheet.Columns.Count;
for (int i = 1; i <= r; i++)
{
for (int j = 1; j <= c; j++)
{
Range cell = sheet.Cells[i, j];
int colorNumber = System.Convert.ToInt32(cell.Interior.Color);
Color color = System.Drawing.ColorTranslator.FromOle(colorNumber);
if (color != Color.White) //cell is gray
{
decimal summ = 0;
foreach (var filled in _filledWorkbooks)
{
var filledSheet = filled.Sheets[sheet.Name] as Worksheet;
var filledCell = filledSheet.Cells[i, j] as Range;
var value = filledCell.Value2;
string message = null;
decimal add = 0;
Priority p = Priority.Low;
if (value == null)
{
emptyCounter++;
}
else
{
string repr = string.Format("{0}", value);
if (String.IsNullOrWhiteSpace(repr))
{
message = "Ячейка пробел";
errorCounter++;
p = Priority.High;
}
else
{
if (!Decimal.TryParse(repr, out add))
{
message = "Нечисленное значение";
errorCounter++;
p = Priority.High;
}
}
}
if (message != null)
{
log(filled.FullName, filledSheet.Name, filledCell.Row, filledCell.Column, message, p, Severity.Error);
}
summ += add;
}
cell.Value2 = summ;
}
}
}
}
report("ошибок " + errorCounter);
report("пустых цветных ячеек" + emptyCounter);
if (!Directory.Exists(inDir))
{
Directory.CreateDirectory(inDir);
}
var outFile = Path.Combine(outDir, "all.xls");
_template.SaveAs(outFile);
_template.Close(false);
report("сделано " + outFile);
report("нажмите любую клавишу для завершения");
finish();
}
static void Main(string[] args)
{
copyExcellTo = System.Configuration.ConfigurationManager.AppSettings["copyExcellTo"];
savePDFTo = System.Configuration.ConfigurationManager.AppSettings["savePDFTo"];
Console.WriteLine("start job: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString());
List<FilePath> filePaths = new List<FilePath>();
FilePath filePath = new FilePath();
LogWithoutPath("begin trying to get current id");
documentID = GetCurrentID();
LogWithoutPath("current id: " + documentID);
LogWithoutPath("end trying to get current id");
//List<HL7Data> hL7Data = new List<HL7Data>();
using (IDbConnection db = new SqlConnection(databaseConnection))
{
try
{
//string query = "select "
//string query = "select top 10000"
//string query = "select [ documentid] as DocumentID,documentpath as DocumentPath from donedocuments4";
//string query = "select documentid as DocumentID,documentpath as DocumentPath from documentbackup"
// + " where documentid not in ("
// + " select documentid from DOCUMENTS "
// + " where DOCUMENTS.DOCUMENTTYPEID = 'xls'"
// + " and DOCUMENTS.ISDELETED = '0'"
// + " and DOCUMENTS.DOCUMENTGROUPID <> 2)";
string query = "select "
//D:\SRSServer\Storage
+ " documentid as DocumentID,'D:\\SRSServer\\Storage\\' + LEFT(CAST(DOCUMENTS.PERSONID AS int),2) + '\\' + STR(DOCUMENTS.PERSONID,10,0) + '\\' + STR(DOCUMENTS.DOCUMENTID,10,0) as DocumentPath"
//+ " documentid as DocumentID,'\\\\hmdn-srseds\\SRSServer\\Storage\\' + LEFT(CAST(DOCUMENTS.PERSONID AS int),2) + '\\' + STR(DOCUMENTS.PERSONID,10,0) + '\\' + STR(DOCUMENTS.DOCUMENTID,10,0) as DocumentPath"
+ " from DOCUMENTS "
+ " join PERSON on DOCUMENTS.PERSONID = PERSON.PERSONID"
+ " join DOCUMENTGROUP on DOCUMENTS.DOCUMENTGROUPID = DOCUMENTGROUP.DOCUMENTGROUPID"
+ " where DOCUMENTS.DOCUMENTTYPEID = 'xls'"
+ " and DOCUMENTS.ISDELETED = '0'"
+ " and DOCUMENTS.DOCUMENTGROUPID <> 2"
//+ " and documentID > 2004108635"
+ " and DOCUMENTS.DOCUMENTID > 2004460939"
//+ " and documentID > 2004427839"
//+ documentID
+ " order by DOCUMENTS.DOCUMENTID";
//+ " order by DOCUMENTS.DOCUMENTID desc";
filePaths = db.Query<FilePath>(query, commandTimeout: 270).ToList();
}
catch (Exception er)
{
string s1 = er.ToString();
// Log.LogMessage(er.ToString(), "Labcorp Interface", "Error");
}
for (int i = 0; i < filePaths.Count; i++)
{
try
{
LogWithoutPath("start excel");
app = new Application();
app.DisplayAlerts = false;
LogWithoutPath("end start excel");
LogWithoutPath("start get file name");
LogWithoutPath("ID: " + filePaths[i].DocumentID);
//string newfilePath = filePaths[i].DocumentPath.Replace("D:\\", "E:\\");
string newfilePath = filePaths[i].DocumentPath.Replace("D:\\", "\\\\192.168.190.161\\");
// newfilePath = newfilePath.Replace("\\\\", "\\");
theFileName = GetFileName(newfilePath);
LogWithoutPath("file path: D: " + theFileName);
LogWithoutPath("end get file name");
LogWithoutPath("start open work book");
//wkb = app.Workbooks.Open(copyExcellTo + theFileName + ".xls");
try
{
//wkb = app.Workbooks.Open(filePaths[i].DocumentPath);
wkb = app.Workbooks.Open(newfilePath);
LogWithoutPath("end open work book");
//Workbook wkb = app.Workbooks.Open("C:\\temp3\\2004107065.xls");
//\\hmdn-srseds\SRSServer\Storage\16\1627369463\2004108635
//Workbook wkb = app.Workbooks.Open(filePaths[i].DocumentPath);
LogWithoutPath("start get work sheet");
sheet = (Worksheet)wkb.Worksheets[1];
LogWithoutPath("end get work sheet");
//sheet.PageSetup.FitToPagesWide = 1;
sheet.Select(Type.Missing);
//wkb.set.ActiveSheet = sheet;
LogWithoutPath("start save to pdf");
}
catch (Exception er)
{
string s1 = er.ToString();
}
try
{
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, savePDFTo + theFileName + ".pdf", XlFixedFormatQuality.xlQualityStandard, false, false, 1, 1, true);
}
catch
{ }
LogWithoutPath("end save to pdf");
Console.WriteLine("finished with number: " + i.ToString());
LogWithoutPath("start close resources");
try
{
wkb.Close(false);
app.Application.Quit();
app.Quit();
}
catch { }
try
{
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(app);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(app);
}
catch { }
try
{
Process[] excelProcess = Process.GetProcessesByName("Excel");
excelProcess[0].Kill();
}
catch { }
LogWithoutPath("end close resources");
try
{
SaveID(filePaths[i].DocumentID);
//if (i >= 10000)
//{
// Environment.Exit(0);
//}
}
catch { }
}
catch (Exception er )
{
string s1 = er.ToString();
wkb.Close(false);
app.Application.Quit();
app.Quit();
//Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(app);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(app);
Process[] excelProcess = Process.GetProcessesByName("Excel");
excelProcess[0].Kill();
LogWithoutPath("Problem with ID: " + filePaths[i].DocumentID);
}
}
Console.WriteLine("end job: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString());
}
}
public void LoadToExcel()
{
if (pathToFile == null)
return;
ObjExcel = new Application();
string path = pathToFile;
ObjWorkBook = ObjExcel.Workbooks.Add();
ObjWorkSheet = (Worksheet)ObjWorkBook.Sheets[1];
CreatTemplate(ObjExcel, ObjWorkBook, ObjWorkSheet);
int k = 3;
Range exelcells;
foreach (StudentSubGroup groop in eStorage.StudentSubGroups)
{
PartialSchedule partSchedule;
partSchedule = schedule.GetPartialSchedule(groop);
StudentsClass[] sched;
ClassRoom clas;
sched = partSchedule.GetClasses();
int ifor1 = 0, ifor2 = 32;
((Range)ObjWorkSheet.Cells[1, k]).Value2 = groop.NameGroup;
((Range)ObjWorkSheet.Cells[2, k]).Value2 = groop.NumberSubGroup;
((Range)ObjWorkSheet.Cells[1, k]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[1, k]).VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[2, k]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[2, k]).VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[1, k]).Font.Bold = true;
((Range)ObjWorkSheet.Cells[1, k]).Borders.ColorIndex = 1;
((Range)ObjWorkSheet.Cells[2, k]).Borders.ColorIndex = 1;
for (int i = 0; i < (Domain.Services.Constants.CLASSES_IN_DAY * Domain.Services.Constants.DAYS_IN_WEEK * Domain.Services.Constants.WEEKS_IN_SCHEDULE); i++)
{
((Range)ObjWorkSheet.Cells[(i + 3), k]).Borders.ColorIndex = 1;
((Range)ObjWorkSheet.Cells[(i + 3), k]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[(i + 3), k]).VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
if ((i + 1) % 12 == 0)
((Range)ObjWorkSheet.Cells[(i + 3), k]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium;
if (sched[i] != null && i >= 0 && i <= 35)
{
string str;
clas = schedule.GetClassRoom(sched[i]);
str = sched[i].Name + " (" + clas.Housing + " а." + clas.Number + " )";
for (int n = 0; n < sched[i].Teacher.Length; n++)
{
str = str + " " + sched[i].Teacher[n].Name;
}
((Range)ObjWorkSheet.Cells[(i + 3 + ifor1), k]).ColumnWidth = 30;
((Range)ObjWorkSheet.Cells[(i + 3 + ifor1), k]).Value2 = str;
}
if (sched[i] != null && i >= 36 && i <= 71)
{
string str;
clas = schedule.GetClassRoom(sched[i]);
str = sched[i].Name + " (" + clas.Housing + " а." + clas.Number + " )";
for (int n = 0; n < sched[i].Teacher.Length; n++)
{
str = str + " " + sched[i].Teacher[n].Name;
}
((Range)ObjWorkSheet.Cells[(i - ifor2), k]).ColumnWidth = 30;
((Range)ObjWorkSheet.Cells[(i - ifor2), k]).Value2 = str;
}
if (i >= 0 && i <= 35) ifor1++;
if (i >= 36 && i <= 71) ifor2--;
}
k++;
}
//ANALYSING
//------------------------------------------------------------------------------------
string[,] grstr = new string[74, (k - 3)];
for (int i = 0; i < (k - 3); i++)
{
for (int zed = 0; zed < 74; zed++)
{
if (((Range)ObjWorkSheet.Cells[zed + 1, i + 3]).Value2 != null)
{
grstr[zed, i] = ((Range)ObjWorkSheet.Cells[zed + 1, i + 3]).Value2.ToString();
}
}
}
//Horizantal
for (int zed = 0; zed < 74; zed++)
{
if (zed != 1)
{
for (int i = 0; i < (k - 4); i++)
{
if (grstr[zed, i] != null && grstr[zed, i] == grstr[zed, i + 1])
{
exelcells = ObjWorkSheet.Range[ObjWorkSheet.Cells[zed + 1, i + 3], ObjWorkSheet.Cells[zed + 1, i + 4]];
((Range)ObjWorkSheet.Cells[zed + 1, i + 4]).Clear();
exelcells.Merge(Type.Missing);
}
}
}
}
//Vertical
for (int i = 0; i < (k - 3); i++)
{
for (int zed = 0; zed < 73; zed += 2)
{
if (grstr[zed, i] == grstr[zed + 1, i])
{
exelcells = ObjWorkSheet.Range[ObjWorkSheet.Cells[zed + 1, i + 3], ObjWorkSheet.Cells[zed + 2, i + 3]];
((Range)ObjWorkSheet.Cells[zed + 2, i + 3]).Value2 = "";
exelcells.Merge(Type.Missing);
}
}
}
//------------------------------------------------------------------------------------
ObjWorkBook.SaveAs(path);
ObjWorkBook.Close();
ObjExcel.Quit();
ObjWorkSheet = null;
ObjWorkBook = null;
ObjExcel = null;
}
/// <summary>
/// 字典表
/// </summary>
/// <param name="path">文件输出路径</param>
/// <param name="tables">字典表</param>
/// <param name="action">进度条委托</param>
/// <returns>创建数据字典表数量</returns>
protected int CreateDictionary(string path, Table[] tables, Action <int> action)
{
var folderPath = path.Substring(0, path.LastIndexOf("\\", StringComparison.Ordinal));
if (!Directory.Exists(folderPath))//目录不存在就创建
{
Directory.CreateDirectory(folderPath);
}
var oldCi = Thread.CurrentThread.CurrentCulture;//线程启动时，设置当前线程的CultureInfo与互操作库一致
Thread.CurrentThread.CurrentCulture = new CultureInfo("zh-CN");
Application app = null;
Workbook workBook = null;
Worksheet sheet = null;
try
{
var progressCount = 0;
app = new Application()
{
Visible = false,
DisplayAlerts = false
};
Thread.CurrentThread.CurrentCulture = oldCi;
app.Workbooks.Add(true);
if (File.Exists(path))
{
File.Delete(path);
}
workBook = app.Workbooks.Add(Missing.Value);
var length = tables.Length;
while (length > 0)
{
if (length > 200)
{
app.Sheets.Add(Missing.Value, workBook.Sheets[workBook.Sheets.Count], 200);
progressCount += 200;
length -= 200;
}
else
{
app.Sheets.Add(Missing.Value, workBook.Sheets[workBook.Sheets.Count], length);
progressCount += length;
length = 0;
}
}
var worksheet = (Worksheet)workBook.Sheets[1];
worksheet.Name = "数据库字典汇总表";
worksheet.Cells[1, 1] = "数据库字典汇总表";
worksheet.Cells[2, 1] = "编号";
worksheet.Cells[2, 2] = "表名";
worksheet.Cells[2, 3] = "备注";
worksheet.Cells[2, 4] = "数据说明";
worksheet.Cells[2, 5] = "表结构描述(页号)";
var type = typeof(TableColumn);
var properties = type.GetProperties();
Range range;
var time = DateTime.Now;
//app.Sheets.Add(Missing.Value, workBook.Sheets[workBook.Sheets.Count], tables.Length);
for (var i = 0; i < tables.Length; i++)
{
sheet = (Worksheet)workBook.Sheets[i + 2];//数据表
sheet.Name = $"{(100d + i + 1) / 100:F}";
sheet.Cells[1, 1] = "数据库表结构设计明细";
sheet.Cells[2, 1] = $"表名：{tables[i].TableName}";
sheet.Cells[3, 1] = tables[i].TableComment;
var fields = tables[i].TableColumns;
for (var j = 0; j < properties.Length; j++)
{
foreach (var colum in properties[j].GetCustomAttributes().OfType <DescriptionAttribute>())
{
sheet.Cells[4, j + 1] = string.IsNullOrEmpty(colum.Description) ? properties[j].Name : colum.Description;
}
for (var k = 0; k < fields.Length; k++)
{
sheet.Cells[k + 5, j + 1] = type.GetProperty(properties[j].Name).GetValue(fields[k], null);
}
}
worksheet.Cells[i + 3, 1] = i + 1;
worksheet.Cells[i + 3, 2] = tables[i].TableName;
worksheet.Cells[i + 3, 3] = tables[i].TableComment;
worksheet.Cells[i + 3, 4] = string.Empty;
worksheet.Cells[i + 3, 5] = $"表{sheet.Name}";
#region 数据表样式
range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[fields.Length + 4, properties.Length]]; //选取单元格
range.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中设置
range.EntireColumn.AutoFit(); //自动调整列宽
range.Borders.LineStyle = XlLineStyle.xlContinuous; //所有框线
range.Borders.Weight = XlBorderWeight.xlMedium; //边框常规粗细
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 14; //字体大小
range.NumberFormatLocal = "@";
range = sheet.Range[sheet.Cells[4, 1], sheet.Cells[fields.Length + 4, properties.Length]]; //选取单元格
range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中设置
range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, properties.Length]]; //选取单元格
range.Merge(Missing.Value);
range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中设置
range.Font.Bold = true; //字体加粗
range.Font.Size = 24; //字体大小
range = sheet.Range[sheet.Cells[2, 1], sheet.Cells[2, properties.Length]]; //选取单元格
range.Merge(Missing.Value);
range = sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, properties.Length]]; //选取单元格
range.Merge(Missing.Value);
#endregion
progressCount++;
action(progressCount / 2);
}
Loger.Debug($"操作耗时{(DateTime.Now - time).TotalSeconds}秒");
#region 汇总表样式
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[tables.Length + 2, 5]]; //选取单元格
range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中设置
range.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中设置
range.ColumnWidth = 30; //设置列宽
range.Borders.LineStyle = XlLineStyle.xlContinuous; //所有框线
range.Borders.Weight = XlBorderWeight.xlMedium; //边框常规粗细
range.Font.Name = "宋体"; //设置字体
range.Font.Size = 14; //字体大小
range.NumberFormatLocal = "@";
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 5]]; //选取单元格
range.Merge(Missing.Value);
range.Font.Bold = true; //字体加粗
range.Font.Size = 24; //字体大小
#endregion
sheet?.SaveAs(path);
worksheet.SaveAs(path);
return(tables.Length);
}
finally
{
workBook?.Close();
app?.Quit();
KillExcel(app);
GC.Collect();
}
}
public void LoadToExcel()
{
if (pathToFile == null)
return;
string path = pathToFile;
ObjExcel = new Application();
ObjWorkBook = ObjExcel.Workbooks.Add();
ObjWorkSheet = (Worksheet)ObjWorkBook.Sheets[1];
ScheduleExcel.CreatTemplate(ObjExcel, ObjWorkBook, ObjWorkSheet);
int k = 3;
foreach (Teacher teach in eStorage.Teachers)
{
PartialSchedule partSchedule;
partSchedule = schedule.GetPartialSchedule(teach);
StudentsClass[] sched;
ClassRoom clas;
sched = partSchedule.GetClasses();
int ifor1 = 0, ifor2 = 32;
((Range)ObjWorkSheet.Cells[2, k]).Clear();
((Range)ObjWorkSheet.Cells[2, k]).Value2 = teach.Name;
((Range)ObjWorkSheet.Cells[2, k]).Orientation = 75;
for (int i = 0; i < (Domain.Services.Constants.CLASSES_IN_DAY * Domain.Services.Constants.DAYS_IN_WEEK * Domain.Services.Constants.WEEKS_IN_SCHEDULE); i++)
{
if (sched[i] != null && i >= 0 && i <= 35)
{
string str;
clas = schedule.GetClassRoom(sched[i]);
str = sched[i].Name + "(" + clas.Housing + " а." + clas.Number + ")";
((Range)ObjWorkSheet.Cells[(i + 3 + ifor1), k]).ColumnWidth = 30;
((Range)ObjWorkSheet.Cells[(i + 3 + ifor1), k]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[(i + 3 + ifor1), k]).VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[(i + 3 + ifor1), k]).Value2 = str;
}
if (sched[i] != null && i >= 36 && i < 72)
{
string str;
clas = schedule.GetClassRoom(sched[i]);
str = sched[i].Name + "(" + clas.Housing + " а." + clas.Number + ")";
((Range)ObjWorkSheet.Cells[(i - ifor2), k]).ColumnWidth = 30;
((Range)ObjWorkSheet.Cells[(i - ifor2), k]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[(i - ifor2), k]).VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
((Range)ObjWorkSheet.Cells[(i - ifor2), k]).Value2 = str;
}
if (i >= 0 && i <= 35) ifor1++;
if (i >= 36 && i <= 71) ifor2--;
}
k++;
}
ObjWorkBook.SaveAs(path);
ObjWorkBook.Close();
ObjExcel.Quit();
ObjWorkSheet = null;
ObjWorkBook = null;
ObjExcel = null;
}
public void ExcelTest(string workbook)
{
var outDir = Path.GetDirectoryName(new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
m_xlApp = new Application();
Assert.IsNotNull(m_xlApp, "Could not create an Excel Application object");
var xlWorkbooks = m_xlApp.Workbooks;
var testMacrosPath = Path.Combine(outDir, "testMacros.xlsm");
var testMacros = xlWorkbooks.Open(testMacrosPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var pidAsObject = m_xlApp.Run(testMacros.Name + "!getpid");
m_xlProcess = Process.GetProcessById(pidAsObject);
var xldnaToLoad = PrepareXlDna(outDir);
workbook = Path.Combine(outDir, "testWorkbooks", workbook);
var workbookName = Path.GetFileName(workbook);
Assert.IsTrue(File.Exists(workbook));
Assert.IsTrue(m_xlApp.RegisterXLL(xldnaToLoad));
m_xlApp.DisplayAlerts = false;
m_xlWorkbook = xlWorkbooks.Open(workbook, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var workDir = Path.Combine(outDir, "evidence");
if (!Directory.Exists(workDir))
{
try { Directory.CreateDirectory(workDir); } catch (Exception) { }; // more than 1 thread might get here, but only 1 will succeed
}
Assert.IsTrue(Directory.Exists(workDir));
m_xlApp.Run(testMacros.Name + "!setProcessCurrentDirectory", workDir);
m_xlApp.CalculateBeforeSave = false;
bool vbaResult = false;
try
{
vbaResult = m_xlApp.Run(m_xlWorkbook.Name + "!VBATest");
}
catch (COMException x)
{
// This HR means "Programmatic access to Visual Basic is not trusted"
// It is returned also in case the macro does not exist
// If we get anything other than this type of error, we rethrow.
// Otherwise there's nothing to test and we carry on.
if ((uint)x.HResult != 0x800a03ec)
throw;
vbaResult = true;
}
Assert.IsTrue(vbaResult);
Range resultRange = m_xlApp.Range["RESULT"];
Assert.AreEqual(1, resultRange.Count);
resultRange.Dirty();
m_xlApp.CalculateFullRebuild();
dynamic testResult = resultRange.Value;
if (typeof(int) == testResult.GetType())
Assert.Fail("RESULT={0}", hashErrorToString((uint)testResult));
Assert.AreEqual(typeof(bool), testResult.GetType(), "RESULT={0}", testResult.ToString());
Assert.IsTrue((bool)testResult);
// the test has passed. we don't need to leave the xlsm around
m_xlWorkbook.Close(false);
m_xlWorkbook = null;
File.Delete(workbook);
}
public void Export()
{
try
{
_workbook = CreateWorkbook();
this.Application.Visible = !_hideApp;
BeforeExport();
WriteHeader();
WriteColumnHeader();
WriteContent();
AfterContentWritten();
}
catch (Exception ex)
{
try
{
Application.DisplayAlerts = false;
if (_workbook != null)
_workbook.Close(false, Missing, Missing);
Application.DisplayAlerts = true;
}
catch (Exception) { }
Global.ShowError(ex);
//throw ex;
}
if (this.UseIndependentApp)
{
try
{
Application.DisplayAlerts = false;
Application.Quit();
}
catch (Exception) { }
}
}
private void ReleaseResources(Application xlApp, Workbook xlWorkBook, List<Worksheet> workSheets)
{
xlWorkBook.Close(true);
xlApp.Quit();
workSheets.ForEach(i => ReleaseObject(i));
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
}
//saves changes to the file through a function so it may be called multiple times
static void save(Application Excel, Workbook THING, string fileNomen)
{
string path = loc;
Excel.DisplayAlerts = false;
THING.Application.DefaultFilePath = path;
THING.SaveAs(fileNomen, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
THING.Close();
}
//Open the first worksheet and get the information
//For now I will try to have as parameters the fields I want to get out of the excel document
//public static object[,]
public static void readWorksheet(Workbook workbook, out string[] Course, out string[] IndividualID, out string[] LastName, out string[] FirstName, out string[] Email)
{
//select the first sheet
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
//find the used range in worksheet
Range excelRange = worksheet.UsedRange;
//get an object array of all of the cells in the worksheet (their values)
object[,] valueArray = (object[,])excelRange.get_Value(
XlRangeValueDataType.xlRangeValueDefault);
//make an array of all the data in each of the columns
string[] YT = new string[worksheet.UsedRange.Rows.Count];
string[] MatchCourse = new string[worksheet.UsedRange.Rows.Count];
string[] Department = new string[worksheet.UsedRange.Rows.Count];
Course = new string[worksheet.UsedRange.Rows.Count];
string[] Section = new string[worksheet.UsedRange.Rows.Count];
string[] FinalGrade = new string[worksheet.UsedRange.Rows.Count];
IndividualID = new string[worksheet.UsedRange.Rows.Count];
string[] Classification = new string[worksheet.UsedRange.Rows.Count];
string[] EnrollmentStatus = new string[worksheet.UsedRange.Rows.Count];
LastName = new string[worksheet.UsedRange.Rows.Count];
FirstName = new string[worksheet.UsedRange.Rows.Count];
string[] Nickname = new string[worksheet.UsedRange.Rows.Count];
string[] Phone = new string[worksheet.UsedRange.Rows.Count];
Email = new string[worksheet.UsedRange.Rows.Count];
string[] Notes = new string[worksheet.UsedRange.Rows.Count];
//access the cells
for (int row =1; row < worksheet.UsedRange.Rows.Count; ++row)
{
int col = 1;
YT[row] = valueArray[row,col].ToString();
MatchCourse[row] = valueArray[row,++col].ToString();
Department[row] = valueArray[row,++col].ToString();
Course[row] = valueArray[row,++col].ToString();
Section[row] = valueArray[row,++col].ToString();
FinalGrade[row] = valueArray[row, ++col].ToString();
IndividualID[row] = valueArray[row, ++col].ToString();
Classification[row] = valueArray[row, ++col].ToString();
EnrollmentStatus[row] = valueArray[row, ++col].ToString();
LastName[row] = valueArray[row, ++col].ToString();
FirstName[row] = valueArray[row, ++col].ToString();
Nickname[row] = valueArray[row, ++col].ToString();
Phone[row] = valueArray[row, ++col].ToString();
Email[row] = valueArray[row, ++col].ToString();
Notes[row] = valueArray[row, ++col].ToString();
}
//clean up stuffs
workbook.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(workbook);
//return valueArray;
//_excelApp.Quit();
//Marshal.FinalReleaseComObject(_excelApp);
}
//
//**********************************************************************************************
//
public List<TMeteo> ReadIrrigation(String aFile)
{
Double NewValue = 0.0;
String SheetName = @"Irrigation";
List<TMeteo> Irrig = new List<TMeteo>();
Irrig.Clear();
MyWorkBook = ExcelApp.Workbooks.Open(aFile, Missing, Missing, Missing,
Missing, Missing, Missing, Missing, Missing, Missing, Missing,
Missing, Missing, Missing, Missing);
try
{
try
{
MyWorkSheet = (Worksheet)MyWorkBook.Worksheets.get_Item(SheetName);
Range MyRange = MyWorkSheet.UsedRange;
System.Array MyValues = (System.Array)MyRange.Cells.get_Value(XlRangeValueDataType.xlRangeValueDefault);
String[,] MyData = ConvertToStringArray(MyValues);
for (Int32 i = 1; i < MyData.GetLength(0); i++)
{
TMeteo MyMeteo = new TMeteo();
if ((MyData[i, 0].IndexOf("-") >= 0) || (MyData[i, 0].IndexOf("/") >= 0))
{
MyMeteo.Date = Convert.ToDateTime(MyData[i, 0]);
NewValue = Convert.ToDouble(MyData[i, 1]);
MyMeteo.Value = NewValue;
Irrig.Add(MyMeteo);
}
}
}
catch (Exception e)
{
MessageBox.Show("??? Error opening sheet Irrigation in file " + aFile + ": " + e.Message);
}
}
finally
{
MyWorkBook.Close(false, "", null);
}
return Irrig;
}
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects And Open Template File
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param path : The path to save/open the EXCEL file to/from
public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
{
try
{
this.myTemplateValues = myTemplateValues;
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(templatePath,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = "ATemplate";
//Fills the Excel Template File Selected With A 2D Test Array
FillTemplate_WithTestValues();
//Select all used cells within current worksheet
SelectAllUsedCells();
#region Finish and Release
try
{
NAR(sheets);
NAR(worksheet);
workbook.Close(true, path, Type.Missing);
NAR(workbook);
EXL.UserControl = false;
EXL.Quit();
NAR(EXL);
//kill the EXCEL process as a safety measure
KillExcel();
// Show that processing is finished
ProgressEventArgs pe = new ProgressEventArgs(100);
OnProgressChange(pe);
//MessageBox.Show("Finished adding test values to Template", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (COMException)
{
Console.WriteLine("User closed Excel manually, so we don't have to do that");
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
public void Close()
{
wb.Close();
}
public void Dispose()
{
LogDebug("Closing Excel.");
_excelBook?.Close(false);
_excelApp?.Application.Quit();
}
/// <summary>
/// Fills the TestData.xls file based from Events,market and selections files based on Mapping.xls
/// </summary>
public void MapTestDataXls()
{
try
{
_testDataFilePath = _currentDirPath.Parent.FullName;
System.Data.DataTable mappingDataTable = XlsReader.LoadExcelData(_testDataFilePath + "\\TestData\\FillUploadFiles\\Mapping.xls", "Events");
System.Data.DataTable eventsDataTable = XlsReader.LoadExcelData(_testDataFilePath + "\\TestData\\UploadFiles\\EVENTS.xls", "EVENTS");
System.Data.DataTable marketsDataTable = XlsReader.LoadExcelData(_testDataFilePath + "\\TestData\\UploadFiles\\market.xls", "market");
System.Data.DataTable selectionsDataTable = XlsReader.LoadExcelData(_testDataFilePath + "\\TestData\\UploadFiles\\SELECTIONS.xls", "SELECTIONS");
_app = new Application();
var testDataObj = new TestData();
_app.DisplayAlerts = false;
_workBook = _app.Workbooks.Open(_testDataFilePath + "\\TestData\\TestData.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
_workSheets = _workBook.Worksheets;
//GUITestData,BetSlipTestData,GUITestData,Others
for (int i = 0; i < mappingDataTable.Rows.Count; i++)
{
string sourceFile = mappingDataTable.Rows[i]["Source"].ToString();
string sourceSheet = mappingDataTable.Rows[i]["SourceSheet"].ToString();
int sourceRow = Convert.ToInt32(mappingDataTable.Rows[i]["SourceRow"]);
string destinationFile = mappingDataTable.Rows[i]["Destination"].ToString();
string destinationSheet = mappingDataTable.Rows[i]["DestSheet"].ToString();
int destinationRow = Convert.ToInt32(mappingDataTable.Rows[i]["DestRow"]);
_workSheet = (Microsoft.Office.Interop.Excel.Worksheet)_workSheets.get_Item(destinationSheet);
Microsoft.Office.Interop.Excel.Range range = _workSheet.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
int colIndexParam1 = 1;
int colIndexParam2 = 1;
int colIndexParam3 = 1;
int colIndexParam4 = 1;
int colIndexParam5 = 1;
int colIndexParam6 = 1;
int colIndexParam7 = 1;
switch (sourceFile.ToUpper().Trim())
{
case "EVENTS.CSV":
if (mappingDataTable.Rows[i]["param1"].ToString().ToUpper().Trim() != "NULL")
{
_param1 = eventsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param1"].ToString()].ToString();
colIndexParam1 = GetColumnIndex(mappingDataTable.Rows[i]["param1"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam1] = _param1;
}
if (mappingDataTable.Rows[i]["param2"].ToString().ToUpper().Trim() != "NULL")
{
_param2 = eventsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param2"].ToString()].ToString();
colIndexParam2 = GetColumnIndex(mappingDataTable.Rows[i]["param2"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam2] = _param2;
}
if (mappingDataTable.Rows[i]["param3"].ToString().ToUpper().Trim() != "NULL")
{
_param3 = eventsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param3"].ToString()].ToString();
colIndexParam3 = GetColumnIndex(mappingDataTable.Rows[i]["param3"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam3] = _param3;
}
if (mappingDataTable.Rows[i]["param4"].ToString().ToUpper().Trim() != "NULL")
{
_param4 = eventsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param4"].ToString()].ToString();
colIndexParam4 = GetColumnIndex(mappingDataTable.Rows[i]["param4"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam4] = _param4;
}
if (mappingDataTable.Rows[i]["param5"].ToString().ToUpper().Trim() != "NULL")
{
_param5 = eventsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param5"].ToString()].ToString();
colIndexParam5 = GetColumnIndex(mappingDataTable.Rows[i]["param5"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam5] = _param5;
}
if (mappingDataTable.Rows[i]["param6"].ToString().ToUpper().Trim() != "NULL")
{
_param6 = eventsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param6"].ToString()].ToString();
colIndexParam6 = GetColumnIndex(mappingDataTable.Rows[i]["param6"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam6] = _param6;
}
break;
case "MARKET.CSV":
if (mappingDataTable.Rows[i]["param1"].ToString().ToUpper().Trim() != "NULL")
{
_param1 = marketsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param1"].ToString()].ToString();
colIndexParam1 = GetColumnIndex(mappingDataTable.Rows[i]["param1"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam1] = _param1;
}
if (mappingDataTable.Rows[i]["param2"].ToString().ToUpper().Trim() != "NULL")
{
_param2 = marketsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param2"].ToString()].ToString();
colIndexParam2 = GetColumnIndex(mappingDataTable.Rows[i]["param2"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam2] = _param2;
}
if (mappingDataTable.Rows[i]["param3"].ToString().ToUpper().Trim() != "NULL")
{
_param3 = marketsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param3"].ToString()].ToString();
colIndexParam3 = GetColumnIndex(mappingDataTable.Rows[i]["param3"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam3] = _param3;
}
if (mappingDataTable.Rows[i]["param4"].ToString().ToUpper().Trim() != "NULL")
{
_param4 = marketsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param4"].ToString()].ToString();
colIndexParam4 = GetColumnIndex(mappingDataTable.Rows[i]["param4"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam4] = _param4;
}
if (mappingDataTable.Rows[i]["param5"].ToString().ToUpper().Trim() != "NULL")
{
_param5 = marketsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param5"].ToString()].ToString();
colIndexParam5 = GetColumnIndex(mappingDataTable.Rows[i]["param5"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam5] = _param5;
}
if (mappingDataTable.Rows[i]["param7"].ToString().ToUpper().Trim() != "NULL")
{
_param7 = marketsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param7"].ToString()].ToString();
colIndexParam7 = GetColumnIndex(mappingDataTable.Rows[i]["param7"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam7] = _param7;
}
break;
case "SELECTIONS.CSV":
if (mappingDataTable.Rows[i]["param1"].ToString().ToUpper().Trim() != "NULL")
{
_param1 = selectionsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param1"].ToString()].ToString();
colIndexParam1 = GetColumnIndex(mappingDataTable.Rows[i]["param1"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam1] = _param1;
}
if (mappingDataTable.Rows[i]["param2"].ToString().ToUpper().Trim() != "NULL")
{
_param2 = selectionsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param2"].ToString()].ToString();
colIndexParam2 = GetColumnIndex(mappingDataTable.Rows[i]["param2"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam2] = _param2;
}
if (mappingDataTable.Rows[i]["param3"].ToString().ToUpper().Trim() != "NULL")
{
_param3 = selectionsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param3"].ToString()].ToString();
colIndexParam3 = GetColumnIndex(mappingDataTable.Rows[i]["param3"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam3] = _param3;
}
if (mappingDataTable.Rows[i]["param4"].ToString().ToUpper().Trim() != "NULL")
{
_param4 = selectionsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param4"].ToString()].ToString();
colIndexParam4 = GetColumnIndex(mappingDataTable.Rows[i]["param4"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam4] = _param4;
}
if (mappingDataTable.Rows[i]["param5"].ToString().ToUpper().Trim() != "NULL")
{
_param5 = selectionsDataTable.Rows[sourceRow - 2][mappingDataTable.Rows[i]["param5"].ToString()].ToString();
colIndexParam5 = GetColumnIndex(mappingDataTable.Rows[i]["param5"].ToString(), ref _workSheet, ref colCount);
_workSheet.Cells[destinationRow, colIndexParam5] = _param5;
}
break;
default:
break;
}
}
}
catch (Exception Ex)
{
Console.WriteLine(Ex.StackTrace);
Framework.BaseTest.Fail(Ex.Message);
}
finally
{
_workBook.Save();
_workBook.Close();
_workSheet = null;
_workBook = null;
_app.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
