Exemple #1
0
        /// <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();
        }
Exemple #2
0
 /// <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);
 }
Exemple #3
0
        /// <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);
        }
Exemple #4
0
        /// <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);
        }
Exemple #5
0
        /// <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);
        }
Exemple #6
0
        /// <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);
            }
        }
Exemple #7
0
        /// <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");
        }
Exemple #8
0
        /// <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"));
        }