/// <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"));
}