public void MergedOutputFilesTest()
{
FunnelMonthlyOutputHelper target = new FunnelMonthlyOutputHelper(); // TODO: Initialize to an appropriate value
Dictionary<string, ReportContext> MergedFiles = new Dictionary<string, ReportContext>(); // TODO: Initialize to an appropriate value
this.PopulateOutputReport(MergedFiles);
string outputFile = @"D:\ReportExport\UnitTest\FunnelMonthly_Output" + DateTime.Now.Second + ".xls"; ; // TODO: Initialize to an appropriate value
target.MergedOutputFilesUnitTest(MergedFiles, outputFile);
//Assert.Inconclusive("A method that does not return a value cannot be verified.");
}
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;
}
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);
}
