public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string outputFile, out string mergedFile) { mergedFile = string.Empty; if (this.Logger == null) { this.Logger = new QVConfigLog(); } this.Logger.Message("Beigin to merge funnel revenue reports."); excel = new Excel.Application(); Dictionary <string, string> totalReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "Total"); Dictionary <string, string> tsReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "TS"); Dictionary <string, string> onlineReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "Online"); Dictionary <string, string> partnerReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "Partner"); Dictionary <string, string> smeReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "SME"); Excel.Workbook tempWorkbook = null; Excel.Workbook bookDest = null; try { tempWorkbook = excel.Workbooks.Add(Missing.Value); //create a new work sheet Excel.Worksheet tempWorkSheet = tempWorkbook.Worksheets[1] as Excel.Worksheet; this.Logger.Message("Copy to tempwork."); foreach (var item in totalReports.Keys) { totalBook = excel.Workbooks.Open(totalReports[item]); Excel.Worksheet totalSheet = totalBook.Worksheets[1]; totalSheet.Name = item; //copy month report to dest //Column totalSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[item]; //append TS reports //Column if (tsReports.ContainsKey(item)) { TSBook = excel.Workbooks.Open(tsReports[item]); Excel.Worksheet weekSheet = TSBook.Worksheets[1]; weekSheet.Name = item + "1"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "1"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.TSColumn + WeeklyStatisticReportParameters.TSRowCount, copyDestSheet, "A22"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append Online report //Column if (onlineReports.ContainsKey(item)) { onlineBook = excel.Workbooks.Open(onlineReports[item]); Excel.Worksheet weekSheet = onlineBook.Worksheets[1]; weekSheet.Name = item + "2"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "2"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.OnlineColumn + WeeklyStatisticReportParameters.OnlineRowCount, copyDestSheet, "A40"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append Partner revenue //Column this.Logger.Message("append Partner revenue," + partnerReports.Count); if (partnerReports.ContainsKey(item)) { this.Logger.Message("append Partner revenue"); partnerBook = excel.Workbooks.Open(partnerReports[item]); Excel.Worksheet weekSheet = partnerBook.Worksheets[1]; weekSheet.Name = item + "3"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "3"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.PartnerColumn + WeeklyStatisticReportParameters.PartnerRowCount, copyDestSheet, "A62"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append SME revenue //Column if (smeReports.ContainsKey(item)) { this.Logger.Message("append SME revenue"); smeBook = excel.Workbooks.Open(smeReports[item]); Excel.Worksheet weekSheet = smeBook.Worksheets[1]; weekSheet.Name = item + "4"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "4"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.SMEColumn + WeeklyStatisticReportParameters.SMERowCount, copyDestSheet, "A85"); //ExcelUtilies.ReleaseComObject(weekSheet); } copyDestSheet.Range["A1", WeeklyStatisticReportParameters.TotalColumn + "110"].Interior.ColorIndex = 0; Excel.Range range = copyDestSheet.Range["A1"]; range.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); //ExcelUtilies.ReleaseComObject(range); this.SetTitle(copyDestSheet, 1, 1, "Total by product"); this.SetTitle(copyDestSheet, 22, 1, "Telesales"); this.SetTitle(copyDestSheet, 40, 1, "Online"); this.SetTitle(copyDestSheet, 62, 1, "Partner revenue"); this.SetTitle(copyDestSheet, 85, 1, "SME revenue"); if (GeneralParameters.KFR2 == false && GeneralParameters.KFR1 == false && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "U", "AC"); ExcelUtilies.DeleteColumns(copyDestSheet, "I", "N"); } else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == false && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "X", "AC"); ExcelUtilies.DeleteColumns(copyDestSheet, "K", "N"); } else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == true && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "AA", "AC"); ExcelUtilies.DeleteColumns(copyDestSheet, "M", "N"); } //ExcelUtilies.ReleaseComObject(copyDestSheet); //close working workbook this.CloseSourceReportsExcel(); } //Copy KFR revenue to tempWorkSheet this.CopyKFRRevenue2SheetDest(tempWorkbook, MergedFiles); this.Logger.Message("Create dest book."); bookDest = excel.Workbooks.Add(Missing.Value); //get the first sheet of dest book this.Logger.Message("get the first sheet of dest book"); Excel.Worksheet sheetDest = bookDest.Worksheets[1] as Excel.Worksheet; //copy to dest from temp workbook this.Logger.Message("copy to dest from temp workbook."); foreach (var item in tempWorkbook.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (totalReports.Keys.Contains(sheet.Name)) { sheet.Copy(Missing.Value, sheetDest); } else if (sheet.Name == "KFR Revenue") { sheet.Copy(sheetDest, Missing.Value); } } //delete sheet1 sheet2 sheet3 excel.DisplayAlerts = false; this.Logger.Message("delete sheet1 sheet2 sheet3"); foreach (var item in bookDest.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (!totalReports.Keys.Contains(sheet.Name) && sheet.Name != "KFR Revenue") { sheet.Delete(); } } //clear the temp workbook this.Logger.Message("clear the temp workbook"); FunnelReportHelper.SaveTempWorkbook(tempWorkbook); //save the dest this.Logger.Message("save the dest."); mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.LastWeekendDate().WeekOfYearString() + ".xls"); //mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.LatestTwoWeeksEndDate().WeekOfYearString() + ".xls"); if (File.Exists(mergedFile)) { File.Delete(mergedFile); } bookDest.SaveAs(mergedFile); bookDest.Close(); excel.DisplayAlerts = true; } catch (Exception ex) { this.Logger.Error(ex.Message + "\n" + ex.StackTrace); excel.DisplayAlerts = false; this.CloseSourceReportsExcel(); FunnelReportHelper.SaveTempWorkbook(tempWorkbook); excel.DisplayAlerts = true; throw; } finally { excel.Application.Quit(); //ExcelUtilies.ReleaseComObject(excel); //excel = null; //System.GC.Collect(); ExcelUtilies.Kill(excel); } return(true); }