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);
}
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string outputFile, out string mergedFile)
{
if (this.Logger == null)
{
this.Logger = new QVConfigLog();
}
this.Logger.Message("Beigin to merge funnel reports.");
excel = new Excel.Application();
Dictionary <string, string> centerNormalWeeklyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "DetailNormalWeekly");
Dictionary <string, string> centerSpecialWeeklyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "DetailSpecialWeekly");
Dictionary <string, string> centerDailyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "DetailDaily");
Dictionary <string, string> TSNormalWeeklyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "TSNormalWeekly");
Dictionary <string, string> TSDailyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "TSDaily");
string SummaryMonthlyReport = MergedFiles["FunnelCNMini_Summary_Monthly"].OutputFullName;
string SummaryDailyReport = MergedFiles["FunnelCNMini_Summary_Daily"].OutputFullName;
string SummaryTargetReport = MergedFiles["FunnelCNMini_Summary_Target"].OutputFullName;
string SummaryRevenueByPACReport = MergedFiles["FunnelCNMini_Summary_RevenueByPAC"].OutputFullName;
string SummaryTargetB2BReport = MergedFiles["FunnelCNMini_Summary_TargetB2B"].OutputFullName;
Excel.Workbook bookDest = null;
Excel.Workbook tempWorkbook = 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.");
#region Summary
//Summary
summaryBook = excel.Workbooks.Open(SummaryDailyReport);
Excel.Worksheet summaryMonthlySheet = summaryBook.Worksheets[1];
summaryMonthlySheet.Name = "Summary";
//copy DetailNormalWeekly report to dest
//Column A - EG
summaryMonthlySheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet summaryDestSheet = tempWorkbook.Worksheets["Summary"];
int rowCount = this.GetSummaryRowCount(summaryDestSheet);
//Append summary monthly
this.AppendReport(tempWorkbook, summaryDestSheet, "Summary", "A2", "S25", "B" + rowCount, SummaryMonthlyReport, 1);
//Append target report
int targetStartRow = (rowCount + 2) * 2 + 4;
//set label
summaryDestSheet.Range["B" + (targetStartRow - 1)].Value = "B2C";
summaryDestSheet.Range["B" + (targetStartRow - 1)].Font.Bold = true;
this.AppendReport(tempWorkbook, summaryDestSheet, "Summary", "A1", "K" + (FunnelCNMiniMergeParameters.TotalCity + 4), "B" + targetStartRow, SummaryTargetReport, 2);
//Append target B2B
int targetB2BStartRow = targetStartRow + FunnelCNMiniMergeParameters.TotalCity + 2 + 4;
summaryDestSheet.Range["B" + (targetB2BStartRow - 1)].Value = "B2B";
summaryDestSheet.Range["B" + (targetB2BStartRow - 1)].Font.Bold = true;
this.AppendReport(tempWorkbook, summaryDestSheet, "Summary", "A1", "K" + (FunnelCNMiniMergeParameters.TotalCity + 4), "B" + targetB2BStartRow, SummaryTargetB2BReport, 3);
this.FormatSummary(summaryDestSheet);
#endregion
#region Summary Revenue by PAC
summaryRevenueByPac = excel.Workbooks.Open(SummaryRevenueByPACReport);
Excel.Worksheet revenueByPacSheet = summaryRevenueByPac.Worksheets[1];
revenueByPacSheet.Name = "Mini Center Rev By PAC";
revenueByPacSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet revenueByPacDestSheet = tempWorkbook.Worksheets["Mini Center Rev By PAC"];
this.FormatRevenueByPac(revenueByPacDestSheet);
#endregion
//Detail
foreach (var item in centerNormalWeeklyReports.Keys)
{
string header = item + " Details";
centerNormalWeeklyBook = excel.Workbooks.Open(centerNormalWeeklyReports[item]);
Excel.Worksheet centerNormalWeeklySheet = centerNormalWeeklyBook.Worksheets[1];
centerNormalWeeklySheet.Name = header;
//copy DetailNormalWeekly report to dest
//Column A - EG
centerNormalWeeklySheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[header];
ExcelUtilies.DeleteColumns(copyDestSheet, "A", "A");
//Append DetailSpecialWeekly
int normalWeeklyColumnCount = this.GetColumnHasValueEndIndex(copyDestSheet, 2, 1);
string specialWeeklyColumnStart = ExcelUtilies.ExcelColumnIndexToName(normalWeeklyColumnCount + 1);
this.AppendReport(tempWorkbook, copyDestSheet, header, "B1", "AR" + this.GetWeeks(CurrentDate), specialWeeklyColumnStart + "1", centerSpecialWeeklyReports[item], 1);
//if (item == "NJ2")
// this.AppendReport(tempWorkbook, copyDestSheet, header, "B1", "AR" + this.GetWeeks(CurrentDate), "CJ1", centerSpecialWeeklyReports[item], 1);
//else
// this.AppendReport(tempWorkbook, copyDestSheet, header, "B1", "AR" + this.GetWeeks(CurrentDate), "DR1", centerSpecialWeeklyReports[item], 1);
//Append Daily report
this.AppendReport(tempWorkbook, copyDestSheet, header, "A1", "EG" + this.GetDays(CurrentDate), "A" + (this.GetWeeks(CurrentDate) + 3), centerDailyReports[item], 2);
this.FormatDetail(copyDestSheet, item);
}
//Mini TS
foreach (var item in TSNormalWeeklyReports.Keys)
{
string header = "Mini TS " + item;
TSNormalWeeklyBook = excel.Workbooks.Open(TSNormalWeeklyReports[item]);
Excel.Worksheet TSNormalWeeklySheet = TSNormalWeeklyBook.Worksheets[1];
TSNormalWeeklySheet.Name = header;
//copy TSNormalWeekly report to dest
//Column A - EG
TSNormalWeeklySheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[header];
ExcelUtilies.DeleteColumns(copyDestSheet, "A", "A");
//Append Daily report
this.AppendReport(tempWorkbook, copyDestSheet, header, "A1", "EG" + this.GetDays(CurrentDate), "A" + (this.GetWeeks(CurrentDate) + 3), TSDailyReports[item], 2);
this.FormatMiniTS(copyDestSheet, item);
}
this.Logger.Message("Create dest book.");
bookDest = excel.Workbooks.Add(Missing.Value);
//create a new work sheet
//copy to dest from temp workbook
//=======================================================================================
//Why here copy all the sheets to another temp workbook?
//I want to delete the sheeets like sheet1, sheet2, US1 etc. But it doesn't work if deleting
// from tempWorkbook directly. So I copy to a new workbook which can fix the issue.
// I don't know why but it does work.
//=======================================================================================
this.Logger.Message("create a new work sheet from dest book.");
Excel.Worksheet sheetDest = bookDest.Worksheets[3] as Excel.Worksheet;
foreach (var item in tempWorkbook.Worksheets)
{
Excel.Worksheet sheet = item as Excel.Worksheet;
if (centerNormalWeeklyReports.Keys.Contains(sheet.Name.Replace(" Details", "")))
{
sheet.Copy(Missing.Value, sheetDest);
}
if (TSNormalWeeklyReports.Keys.Contains(sheet.Name.Replace("Mini TS ", "")))
{
sheet.Copy(Missing.Value, sheetDest);
}
if (sheet.Name == "Mini Center Rev By PAC")
{
sheet.Copy(Missing.Value, sheetDest);
}
if (sheet.Name == "Summary")
{
sheet.Copy(Missing.Value, sheetDest);
}
}
//clear the temp workbook
this.Logger.Message("clear the temp workbook");
FunnelReportHelper.SaveTempWorkbook(tempWorkbook);
//delete sheet1 sheet2 sheet3
this.Logger.Message("delete sheet1 sheet2 sheet3");
bookDest.Sheets["sheet1"].Delete();
bookDest.Sheets["sheet2"].Delete();
bookDest.Sheets["sheet3"].Delete();
//save the dest
this.Logger.Message("save the dest.");
mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.ToString("yyyyMMdd") + ".xls");
if (File.Exists(mergedFile))
{
File.Delete(mergedFile);
}
bookDest.SaveAs(mergedFile);
bookDest.Close();
//zip the mergefile
ZipHelper.ZipFile(mergedFile, mergedFile.Replace(".xls", ".zip"), 1);
mergedFile = mergedFile.Replace(".xls", ".zip");
}
catch (Exception ex)
{
this.Logger.Error(ex.Message + "\n" + ex.StackTrace);
FunnelReportHelper.SaveTempWorkbook(tempWorkbook);
FunnelReportHelper.SaveTempWorkbook(bookDest);
throw;
}
finally
{
FunnelReportHelper.CloseWorkingWorkbook(centerNormalWeeklyBook);
FunnelReportHelper.CloseWorkingWorkbook(TSNormalWeeklyBook);
FunnelReportHelper.CloseWorkingWorkbook(summaryBook);
FunnelReportHelper.CloseWorkingWorkbook(summaryRevenueByPac);
}
return(true);
}
private void CopyKFRRevenue2SheetDest(Excel.Workbook tempWorkbook, Dictionary <string, ReportContext> MergedFiles)
{
Dictionary <string, string> KFRRevenueReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "KFRRevenue");
if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_NotSet"))
{
ReportContext report = MergedFiles["FunnelRevenue_KFRRevenue_NotSet"];
Excel.Worksheet tempWorkSheet = tempWorkbook.Worksheets[1] as Excel.Worksheet;
totalBook = excel.Workbooks.Open(report.OutputFullName);
Excel.Worksheet totalSheet = totalBook.Worksheets[1];
totalSheet.Name = "KFR Revenue";
//copy month report to dest
//Column
totalSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets["KFR Revenue"];
//append TS reports
//Column
if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH157"))
{
report = MergedFiles["FunnelRevenue_KFRRevenue_CH157"];
TSBook = excel.Workbooks.Open(report.OutputFullName);
Excel.Worksheet weekSheet = TSBook.Worksheets[1];
weekSheet.Name = report.Description;
weekSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description];
//copy week report to dest
ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A21");
//ExcelUtilies.ReleaseComObject(weekSheet);
}
//append Online report
//Column
if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH157"))
{
report = MergedFiles["FunnelRevenue_KFRRevenue_CH159"];
onlineBook = excel.Workbooks.Open(report.OutputFullName);
Excel.Worksheet weekSheet = onlineBook.Worksheets[1];
weekSheet.Name = report.Description;
weekSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description];
//copy week report to dest
ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A38");
//ExcelUtilies.ReleaseComObject(weekSheet);
}
//append Partner report
//Column
if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH184"))
{
report = MergedFiles["FunnelRevenue_KFRRevenue_CH184"];
partnerBook = excel.Workbooks.Open(report.OutputFullName);
Excel.Worksheet weekSheet = partnerBook.Worksheets[1];
weekSheet.Name = report.Description;
weekSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description];
//copy week report to dest
ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A58");
//ExcelUtilies.ReleaseComObject(weekSheet);
}
//append SME report
//Column
if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH185"))
{
report = MergedFiles["FunnelRevenue_KFRRevenue_CH185"];
smeBook = excel.Workbooks.Open(report.OutputFullName);
Excel.Worksheet weekSheet = smeBook.Worksheets[1];
weekSheet.Name = report.Description;
weekSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description];
//copy week report to dest
ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A78");
//ExcelUtilies.ReleaseComObject(weekSheet);
}
copyDestSheet.Range["A1", WeeklyStatisticReportParameters.TotalColumn + "106"].Interior.ColorIndex = 0;
if (GeneralParameters.KFR2 == false && GeneralParameters.KFR1 == false && GeneralParameters.KFR3 == false)
{
ExcelUtilies.DeleteColumns(copyDestSheet, "S", "AA");
ExcelUtilies.DeleteColumns(copyDestSheet, "G", "L");
}
else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == false && GeneralParameters.KFR3 == false)
{
ExcelUtilies.DeleteColumns(copyDestSheet, "V", "AA");
ExcelUtilies.DeleteColumns(copyDestSheet, "I", "L");
}
else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == true && GeneralParameters.KFR3 == false)
{
ExcelUtilies.DeleteColumns(copyDestSheet, "Y", "AA");
ExcelUtilies.DeleteColumns(copyDestSheet, "K", "L");
}
//ExcelUtilies.ReleaseComObject(copyDestSheet);
this.CloseSourceReportsExcel();
}
}
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string outputFile, out string mergedFile)
{
if (this.Logger == null)
{
this.Logger = new QVConfigLog();
}
this.Logger.Message("Beigin to merge funnel reports.");
excel = new Excel.Application();
Dictionary <string, string> monthlyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "ByMonth");
Dictionary <string, string> YTDFunnelReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "YTDFunnel");
Dictionary <string, string> YTDKFROReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "YTDKFRx");
Dictionary <string, string> YTDACTVsKFROReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "YTDACTVsKFRx");
Dictionary <string, string> vs1011YTDReport = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "VS1011YTD");
Dictionary <string, string> YTD1011Report = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "1011YTD");
Excel.Workbook bookDest = null;
Excel.Workbook tempWorkbook = 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 monthlyReports.Keys)
{
monthBook = excel.Workbooks.Open(monthlyReports[item]);
Excel.Worksheet monthSheet = monthBook.Worksheets[1];
monthSheet.Name = item;
//copy month report to dest
//Column B - M
monthSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[item];
#region Append Reports
//append YTDFunnel report
//Column BO
if (YTDFunnelReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.YTDFunnel, FunnelMonthlyReportParameters.Header_YTDACT_CurrentFisicalYear, YTDFunnelReports[item], 1);
}
//append YTDKFRO
//Column BR
if (YTDKFROReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.YTDKFR0, FunnelMonthlyReportParameters.Header_YTDKFR_CurrentFisicalYear, YTDKFROReports[item], 2);
}
//append 1011YTD
//Column BP
if (YTD1011Report.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.YTD1011, FunnelMonthlyReportParameters.Header_YTDACT_BeforeFisicalYear, YTD1011Report[item], 3);
}
//append YTDACTVsKFRO report
//Column BS
if (YTDACTVsKFROReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.YTDACTVsKFR0, FunnelMonthlyReportParameters.Header_YTDACTVsKFR, YTDACTVsKFROReports[item], 4);
}
//append VS1011YTD
//Column BQ
if (vs1011YTDReport.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.VS1011YTD, FunnelMonthlyReportParameters.Header_YTDACTVs_BeforeFisicalYear, vs1011YTDReport[item], 5);
}
#endregion
this.FormatFinalReportSheet(copyDestSheet);
this.CloseSourceReportsExcel();
}
this.Logger.Message("Create dest book.");
bookDest = excel.Workbooks.Add(Missing.Value);
//create a new work sheet
//copy to dest from temp workbook
//=======================================================================================
//Why here copy all the sheets to another temp workbook?
//I want to delete the sheeets like sheet1, sheet2, US1 etc. But it doesn't work if deleting
// from tempWorkbook directly. So I copy to a new workbook which can fix the issue.
// I don't know why but it does work.
//=======================================================================================
this.Logger.Message("create a new work sheet from dest book.");
Excel.Worksheet sheetDest = bookDest.Worksheets[3] as Excel.Worksheet;
foreach (var item in tempWorkbook.Worksheets)
{
Excel.Worksheet sheet = item as Excel.Worksheet;
if (monthlyReports.Keys.Contains(sheet.Name))
{
sheet.Copy(Missing.Value, sheetDest);
}
}
//Append output files
FunnelMonthlyOutputHelper outputHelper = new FunnelMonthlyOutputHelper()
{
Logger = this.Logger
};
outputHelper.MergedOutputFiles(sheetDest, MergedFiles);
//clear the temp workbook
this.Logger.Message("clear the temp workbook");
FunnelReportHelper.SaveTempWorkbook(tempWorkbook);
//delete sheet1 sheet2 sheet3
this.Logger.Message("delete sheet1 sheet2 sheet3");
bookDest.Sheets["sheet1"].Delete();
bookDest.Sheets["sheet2"].Delete();
bookDest.Sheets["sheet3"].Delete();
//save the dest
this.Logger.Message("save the dest.");
mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.Year.ToString() + "Month" + DateTime.Now.Month + ".xls");
if (File.Exists(mergedFile))
{
File.Delete(mergedFile);
}
bookDest.SaveAs(mergedFile);
bookDest.Close();
}
catch (Exception ex)
{
this.Logger.Error(ex.Message + "\n" + ex.StackTrace);
this.CloseSourceReportsExcel();
FunnelReportHelper.SaveTempWorkbook(tempWorkbook);
FunnelReportHelper.SaveTempWorkbook(bookDest);
throw;
}
finally
{
excel.Application.Quit();
}
return(true);
}