/// <summary> /// Initialize a new ReportCreator /// </summary> /// <param name="migrationList">Building list.</param> /// <param name="bxFilePath">Path of the file for brussels.</param> /// <param name="vvFilePath">Path of the file for Verviers.</param> /// <param name="anFilePath">Path of the file for Antwerp.</param> public ReportCreator(List <MigrationElement> migrationList, string bxFilePath, string anFilePath, string vvFilePath) { //1) We must first segregate any city with the zipcode string[] antw = new string[] { "2", "8", "90", "91", "92", "97", "98", "99", "35", "36", "37", "39" }; string[] brux = new string[] { "30", "31", "32", "33", "34", "38", "1", "7", "60", "61", "62", "64", "65", "93", "94", "95", "96" }; string[] Verv = new string[] { "4", "5", "66", "67", "68", "69" }; List <MigrationElement> bxMigration = new List <MigrationElement>(); List <MigrationElement> anMigration = new List <MigrationElement>(); List <MigrationElement> vvMigration = new List <MigrationElement>(); CheckExcellProcesses(); foreach (var item in migrationList) { if (item.CodePostalImmeuble.StartsWithAny(brux)) { bxMigration.Add(item); } } foreach (var item in migrationList) { if (item.CodePostalImmeuble.StartsWithAny(antw)) { anMigration.Add(item); } } foreach (var item in migrationList) { if (item.CodePostalImmeuble.StartsWithAny(Verv)) { vvMigration.Add(item); } } string currentMonth = ExcelMonth.PageName[DateTime.Now.Month - 1]; if (bxMigration.Count > 0) { ExcelExportHelper bxExcel = new ExcelExportHelper(bxFilePath); //CityProcess(bxExcel, bxFilePath, currentMonth, bxMigration,"MSM"); CityProcess(bxExcel, bxFilePath, currentMonth, bxMigration, "MSM"); } if (anMigration.Count > 0) { ExcelExportHelper anExcel = new ExcelExportHelper(anFilePath); //CityProcess(anExcel, anFilePath, currentMonth, anMigration, "KMA"); CityProcess(anExcel, anFilePath, currentMonth, anMigration, "KMA"); } if (vvMigration.Count > 0) { ExcelExportHelper vvExcel = new ExcelExportHelper(vvFilePath); //CityProcess(vvExcel, vvFilePath, currentMonth, vvMigration, "BGI"); CityProcess(vvExcel, vvFilePath, currentMonth, vvMigration, "BGI"); } KillExcel(); }
/// <summary> /// Opens the given file in memory. /// Note: /// 1) This call will change the culture prior to instantiating the XL objects ! /// 2) if it doesn't exist yet, an exception will be thrown. /// </summary> /// <param name="strFileName">The file to open. (if it doesn't exist yet, an /// exception will be thrown.)</param> /// <remarks></remarks> public ExcelExportHelper(string strFileName) { _originalCulture = ExcelExportHelper.GetCurrentCulture(); ExcelExportHelper.SetExcelCulture(); // create new instance of the XLS application and open existing worksheet _objXLApp = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application", true)); _objXLBook = _objXLApp.workbooks.open(strFileName); _objXLSheet = _objXLBook.worksheets(1); }
/// <summary> /// Creates a NEW workbook in memory. (This call will change the culture /// prior to instantiating the XL objects !) /// </summary> /// <remarks>This call will change the cultureprior to instantiating the XL objects!</remarks> public ExcelExportHelper() { _originalCulture = ExcelExportHelper.GetCurrentCulture(); ExcelExportHelper.SetExcelCulture(); // create new instance of the Excell application and create one worksheet _objXLApp = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application", true)); _objXLBook = _objXLApp.workbooks.add(); _objXLSheet = _objXLBook.worksheets(1); }
/// <summary> /// Function to do some proper clean up of objects accessed via OLE /// </summary> /// <remarks></remarks> void System.IDisposable.Dispose() { // reset the XLS objects _objXLSheet = null; _objXLBook = null; _objXLApp = null; //note that the application is not closed here because in some cases, you'll want to display the generated document and consequently, the application should not be closed ExcelExportHelper.RestoreCulture(OriginalCulture); }
/// <summary> /// Closes the workbook from memory, exits the application, cleans up the objects, and /// restores the culture. /// </summary> /// <remarks></remarks> public void Close() { //Closes the worksheet , workbook and application without saving _objXLSheet = null; if ((_objXLBook != null)) { _objXLBook.close(false); } _objXLApp.quit(); Marshal.ReleaseComObject(_objXLApp); //Now, restore the culture ExcelExportHelper.RestoreCulture(_originalCulture); }
/// <summary> /// Same as importData(dtSource as dataTable) except that this method can be called /// without instantiating an excelexportHelper object. /// This method automatically sizes the columns, adds borders around each cell and adds /// an alternating row style. /// </summary> /// <param name="dtSource">The datatable to export</param> /// <param name="strHeader">The left header for the worsheet</param> /// <remarks></remarks> public static void ExportTable(DataTable dtSource, string strHeader = "") { // // export data of a datatable to a new XLS (use of imortData sub defined above) // Use to export the datatable in one line (example Exporttable(dtResults) // ------------------------------------------------------------------------- ExcelExportHelper xlExporter = null; System.Globalization.CultureInfo originalCulture = GetCurrentCulture(); try { if (dtSource.Rows.Count == 0) { throw new Exception("There is no row to export. Operation aborted"); } SetExcelCulture(); xlExporter = new ExcelExportHelper(); { xlExporter.SetHeader(strHeader); xlExporter.ImportData(dtSource); xlExporter.AutoFitColumns(); xlExporter.SetBorders(); xlExporter.Application.visible = true; } } catch (Exception ex) { if (xlExporter != null) { xlExporter.Close(); } throw new Exception("Excel Export Error", ex); } finally { RestoreCulture(originalCulture); } }
/// <summary> /// This method process the value into the cell of the Excel file. /// </summary> /// <param name="excelExport">New Excel File.</param> /// <param name="filePath">Path of the file</param> /// <param name="currentMonth">Current Month.</param> /// <param name="migration">Migration data.</param> private void CityProcess(ExcelExportHelper excelExport, string filePath, string currentMonth, List <MigrationElement> migration, string sales) { int excelRowCount = NomberOfRowUsed(filePath, currentMonth); excelExport.ChangeSheet(currentMonth); foreach (var item in migration) { CoreBuiness(excelExport, item, excelRowCount, sales); excelRowCount++; } string directory = Path.GetDirectoryName(filePath); string fileName = Path.GetFileNameWithoutExtension(filePath); excelExport.Save(directory + @"\tempbx.xlsx"); excelExport.Close(); excelExport.Dispose(); BackupOldExcelFile(filePath); File.Copy(directory + @"\tempbx.xlsx", directory + @"\" + fileName + ".xlsx", true); File.Delete(directory + @"\tempbx.xlsx"); }
/// <summary> /// Method the process the value to Excel /// </summary> /// <param name="currentExcel">Excel file instance.</param> /// <param name="currentElement">Current data element.</param> /// <param name="rowCount">The row where the data will be placed.</param> private void CoreBuiness(ExcelExportHelper currentExcel, MigrationElement currentElement, int rowCount, string saleseName) { //int tot = currentElement.Element.TotEau + currentElement.Element.TotInteg + currentElement.Element.TotNchauf; currentExcel.InsertRows(rowCount); if (currentElement.Element.TotalDevice < 51) { for (int i = 1; i < 40; i++) { currentExcel.SetLineColor(rowCount, i); } } else { for (int i = 1; i < 40; i++) { currentExcel.SetSecondLineColor(rowCount, i); } } currentExcel.SetCell(rowCount, 1, saleseName); currentExcel.SetCell(rowCount, 2, currentElement.Site); currentExcel.SetCell(rowCount, 4, currentElement.NumeroImmeuble); currentExcel.SetCell(rowCount, 6, currentElement.Element.TotalAdressImeuble); currentExcel.SetCell(rowCount, 7, currentElement.CodePostalImmeuble); currentExcel.SetCell(rowCount, 8, currentElement.LocaliteImmeuble); currentExcel.SetCell(rowCount, 9, currentElement.Element.CodeNameGerant); currentExcel.SetCell(rowCount, 10, currentElement.ChauffageNombreNRad); currentExcel.SetCell(rowCount, 11, "DR3"); currentExcel.SetCell(rowCount, 12, currentElement.Element.Dop3RadioChaufLocatValue); currentExcel.SetCell(rowCount, 13, currentElement.Element.Dop3RadioChaufVenteValue); currentExcel.SetCell(rowCount, 14, currentElement.Element.Dop3RadioChaufRelevValue); string TypeText = ""; if (currentElement.Element.TotEau > 0) { if (currentElement.Element.TotNEauch > 0 & currentElement.Element.TotNEauFr == 0) { TypeText = "EC"; } else if (currentElement.Element.TotNEauch == 0 & currentElement.Element.TotNEauFr > 0) { TypeText = "EF"; } else { TypeText = "EF EC"; } } currentExcel.SetCell(rowCount, 15, currentElement.Element.TotEau); currentExcel.SetCell(rowCount, 16, TypeText); currentExcel.SetCell(rowCount, 17, currentElement.Element.DomaquaTotalLocatiValue); currentExcel.SetCell(rowCount, 18, currentElement.Element.DomaquaTotalVenteValue); currentExcel.SetCell(rowCount, 19, currentElement.Element.DomaquaTotalReleveValue); currentExcel.SetCell(rowCount, 20, currentElement.Element.TotInteg); currentExcel.SetCell(rowCount, 21, "RADIO"); currentExcel.SetCell(rowCount, 22, currentElement.Element.SensonicIn1_2LocatValue); currentExcel.SetCell(rowCount, 23, currentElement.Element.SensonicIn1_2VenteValue); currentExcel.SetCell(rowCount, 24, currentElement.Element.SensonicIn1_2RelevValue); currentExcel.SetCell(rowCount, 28, currentElement.DocumentName); currentExcel.SetCell(rowCount, 29, DateTime.Now.ToString("dd/MM/yyyy")); }