private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheet, string sheetName, string sourceBeginCell, string sourceEndCell, string destBeginCell, string reportFile, int sequence)
{
if (File.Exists(reportFile))
{
Excel.Workbook sourceBook = null;
try
{
sourceBook = excel.Workbooks.Open(reportFile);
Excel.Worksheet workingSheet = sourceBook.Worksheets[1];
workingSheet.Name = sheetName + sequence;
Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet;
workingSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence];
ExcelUtilies.CopyRange(copySourceSheet, sourceBeginCell, sourceEndCell, copyDestSheet, destBeginCell);
}
catch (Exception ex)
{
this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace));
throw;
}
finally
{
FunnelReportHelper.CloseWorkingWorkbook(sourceBook);
}
}
}
private void FormatRevenueByPac(Worksheet revenueByPacDestSheet)
{
if (revenueByPacDestSheet.Cells[1, 4].Text == "DataSource")
{
ExcelUtilies.DeleteColumns(revenueByPacDestSheet, "D", "D");
}
if (revenueByPacDestSheet.Cells[1, 1].Text == "YearMonth")
{
ExcelUtilies.DeleteColumns(revenueByPacDestSheet, "A", "A");
}
if (revenueByPacDestSheet.Cells[1, 2].Text == "CityName")
{
revenueByPacDestSheet.Range["B1"].Value = "";
}
if (revenueByPacDestSheet.Cells[1, 1].Text == "ProductCategory")
{
revenueByPacDestSheet.Range["A1"].Value = "MTD";
int endRow = 2;
string label = revenueByPacDestSheet.Cells[endRow, 1].Text;
while (!string.IsNullOrWhiteSpace(label))
{
endRow++;
label = revenueByPacDestSheet.Cells[endRow, 1].Text;
}
endRow--;
Range range = revenueByPacDestSheet.Range["A2", "A" + endRow];
this.MergeCells(range, "Mini Center");
}
revenueByPacDestSheet.Range["A1", "H12"].Interior.ColorIndex = 0;
//revenueByPacDestSheet.Range["A1", "F1"].Interior.Color = this.ColumnColor;
revenueByPacDestSheet.Range["A1", "F1"].Font.Bold = true;
ExcelUtilies.InsertRow(revenueByPacDestSheet, "A1");
ExcelUtilies.InsertRow(revenueByPacDestSheet, "A1");
ExcelUtilies.InsertRow(revenueByPacDestSheet, "A1");
revenueByPacDestSheet.Range["A1"].Value = "Currency";
revenueByPacDestSheet.Range["A2"].Value = "RMB";
revenueByPacDestSheet.Range["A2"].Font.Bold = true;
revenueByPacDestSheet.Range["A2"].Interior.Color = this.ColumnColor;
this.MergeCells(revenueByPacDestSheet.Range["A3", "C3"], "Mini Center Rev not include Mini TS and B2B");
}
public static void CloseWorkingWorkbook(Excel.Workbook workbook)
{
if (workbook != null)
{
if (!workbook.Saved)
{
workbook.Save();
}
workbook.Close();
ExcelUtilies.ReleaseComObject(workbook);
}
}
private void FormatSummary(Excel.Worksheet summaryDestSheet)
{
summaryDestSheet.Range["A1", "AZ100"].Interior.ColorIndex = 0;
this.MergeCellsForSummary(summaryDestSheet);
ExcelUtilies.InsertRow(summaryDestSheet, "A1");
ExcelUtilies.InsertRow(summaryDestSheet, "A1");
ExcelUtilies.InsertRow(summaryDestSheet, "A1");
summaryDestSheet.Range["A4", "AX4"].Font.Bold = true;
summaryDestSheet.Range["A3", "AX3"].Merge();
summaryDestSheet.Range["A3", "AX3"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
summaryDestSheet.Range["A3", "AX3"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
summaryDestSheet.Range["A3", "AX3"].Value = "Funnel Performance";
summaryDestSheet.Range["A3", "AX3"].Font.Color = Color.White;
summaryDestSheet.Range["A3", "AX3"].Font.Bold = true;
summaryDestSheet.Range["A3", "AX3"].Interior.Color = Color.DarkBlue;
summaryDestSheet.Range["A1", "C1"].Merge();
summaryDestSheet.Range["A1", "C1"].Value = "Center Month To Date Summary as";
summaryDestSheet.Range["A1", "K1"].Font.Bold = true;
summaryDestSheet.Range["F1", "H1"].Merge();
summaryDestSheet.Range["F1", "H1"].Value = "Total days of this month";
summaryDestSheet.Range["D1"].Value = DateTime.Today.ToString("yyyy/MM/dd");
summaryDestSheet.Range["D1"].Font.Color = Color.Red;
summaryDestSheet.Range["D1"].ColumnWidth = 10;
DateTime date = DateTime.Today;
summaryDestSheet.Range["I1"].Value = DateTime.DaysInMonth(date.Year, date.Month);
summaryDestSheet.Range["I1"].Font.Color = Color.Red;
summaryDestSheet.Range["K1", "L1"].Merge();
summaryDestSheet.Range["K1", "L1"].Value = "Time Ratio";
summaryDestSheet.Range["M1", "N1"].Merge();
summaryDestSheet.Range["M1", "N1"].Value = string.Format("{0:N2}%", ((float)date.Day / (float)DateTime.DaysInMonth(date.Year, date.Month)) * 100);
summaryDestSheet.Range["M1", "N1"].Font.Color = Color.Red;
ExcelUtilies.FreezePanes(summaryDestSheet, 2, 4);
}
private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheet, string sheetName, string column, string columnHeader, string reportFile, int sequence)
{
if (File.Exists(reportFile))
{
Excel.Workbook sourceBook = null;
try
{
sourceBook = excel.Workbooks.Open(reportFile);
Excel.Worksheet workingSheet = sourceBook.Worksheets[1];
workingSheet.Name = sheetName + sequence;
Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet;
workingSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence];
//copy week report to dest
if (copySourceSheet.Cells[1, 1].Text == "Spot Rate")
{
ExcelUtilies.CopyRange(copySourceSheet, "B1", "B" + FunnelMonthlyReportParameters.RowCount, copyDestSheet, column + "2");
}
else
{
ExcelUtilies.CopyRange(copySourceSheet, "B1", "B" + FunnelMonthlyReportParameters.RowCount, copyDestSheet, column + "1");
}
}
catch (Exception ex)
{
this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace));
throw;
}
finally
{
FunnelReportHelper.CloseWorkingWorkbook(sourceBook);
}
}
copyDestSheet.Cells[1, column] = columnHeader;
copyDestSheet.Cells[1, column].Font.Bold = true;
copyDestSheet.Cells[1, column].Font.Size = 10;
copyDestSheet.Columns[column].ColumnWidth = 15;
}
public static void SaveTempWorkbook(Excel.Workbook tempWorkbook)
{
if (tempWorkbook != null)
{
//string folder = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Remove(System.Reflection.Assembly.GetExecutingAssembly().CodeBase.LastIndexOf("/"));
//folder = folder.Replace(@"file:///", "");
//if (!Directory.Exists(folder + "/temp"))
// Directory.CreateDirectory(folder + "/temp");
//tempWorkbook.SaveAs(folder + "/temp/book1.xls");
tempWorkbook.SaveAs("book1.xls");
string fullname = tempWorkbook.FullName;
tempWorkbook.Close();
if (File.Exists(fullname))
{
File.Delete(fullname);
}
ExcelUtilies.ReleaseComObject(tempWorkbook);
}
}
private void FormatSummary(Excel.Worksheet summaryDestSheet)
{
summaryDestSheet.Range["A1", "T100"].Interior.ColorIndex = 0;
this.MergeCellsForSummary(summaryDestSheet);
ExcelUtilies.InsertRow(summaryDestSheet, "A1");
ExcelUtilies.InsertRow(summaryDestSheet, "A1");
ExcelUtilies.InsertRow(summaryDestSheet, "A1");
summaryDestSheet.Range["A4", "T4"].Font.Bold = true;
summaryDestSheet.Range["A3", "T3"].Merge();
summaryDestSheet.Range["A3", "T3"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
summaryDestSheet.Range["A3", "T3"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
summaryDestSheet.Range["A3", "T3"].Value = "Funnel Performance";
summaryDestSheet.Range["A3", "T3"].Font.Color = Color.White;
summaryDestSheet.Range["A3", "T3"].Font.Bold = true;
summaryDestSheet.Range["A3", "T3"].Interior.Color = Color.DarkBlue;
summaryDestSheet.Range["A1", "C1"].Merge();
summaryDestSheet.Range["A1", "C1"].Value = "Mini Center Month To Date Summary as";
summaryDestSheet.Range["A1", "H1"].Font.Bold = true;
summaryDestSheet.Range["F1", "G1"].Merge();
summaryDestSheet.Range["F1", "G1"].Value = "Total days of this month";
summaryDestSheet.Range["D1"].Value = summaryDestSheet.Cells[5, 1].Text;
summaryDestSheet.Range["D1"].Font.Color = Color.Red;
DateTime date;
if (DateTime.TryParse(summaryDestSheet.Cells[1, 4].Text, out date))
{
summaryDestSheet.Range["H1"].Value = DateTime.DaysInMonth(date.Year, date.Month);
}
else
{
summaryDestSheet.Range["H1"].Value = DateTime.DaysInMonth(DateTime.Now.AddDays(-1).Year, DateTime.Now.AddDays(-1).Month);
}
summaryDestSheet.Range["H1"].Font.Color = Color.Red;
ExcelUtilies.FreezePanes(summaryDestSheet, 3, 4);
}
private void CalculateRententionRate(Excel.Worksheet sheet)
{
sheet.Cells[FunnelMonthlyReportParameters.RententionRateRow, 2].NumberFormat = "##,##0.00%";
sheet.Cells[FunnelMonthlyReportParameters.FTPPrice, 2] = string.Format("={0}{1}/{0}{2}",
"B", FunnelMonthlyReportParameters.OnlineFTPRevenues, FunnelMonthlyReportParameters.ChargedFTPRow);
for (int i = 14; i <= 25; i++)
{
string column = ExcelUtilies.ExcelColumnIndexToName(i);
string columnBefore = ExcelUtilies.ExcelColumnIndexToName(i - 1);
//Retention Rate
sheet.Cells[FunnelMonthlyReportParameters.RententionRateRow, i] = string.Format("={0}{1}/({2}{1} + {2}{3})",
column, FunnelMonthlyReportParameters.RenewalRow, columnBefore, FunnelMonthlyReportParameters.ChargedFTPRow);
sheet.Cells[FunnelMonthlyReportParameters.RententionRateRow, i].NumberFormat = "##,##0.00%";
//FTP Price
sheet.Cells[FunnelMonthlyReportParameters.FTPPrice, i] = string.Format("={0}{1}/{0}{2}",
column, FunnelMonthlyReportParameters.OnlineFTPRevenues, FunnelMonthlyReportParameters.ChargedFTPRow);
}
}
private void FormatFinalReportSheet(Excel.Worksheet sheet)
{
//Insert a row to set the title
ExcelUtilies.InsertRow(sheet, "A1");
sheet.Cells[1, 1] = sheet.Name;
//insert two rows, total excel start with Row 3
ExcelUtilies.InsertRow(sheet, "A1");
this.SetBackgroundColor(sheet);
this.CalculateRententionRate(sheet);
string endColumn = ExcelUtilies.ExcelColumnIndexToName(ExcelUtilies.ExcelColumnNameToIndex(FunnelMonthlyReportParameters.MonthEnd));
string startColumn = this.GetCurrentMonthExcelColumn();
if (ExcelUtilies.ExcelColumnNameToIndex(startColumn) <= ExcelUtilies.ExcelColumnNameToIndex(endColumn))
{
ExcelUtilies.DeleteColumns(sheet, startColumn, endColumn);
}
//for Europe remove the values of Spot Rate and KFR Rate
if (sheet.Name == "Europe" || sheet.Name == "Mexico+US")
{
int endMonthColumn = ExcelUtilies.ExcelColumnNameToIndex(this.GetCurrentMonthExcelColumn());
for (int i = 2; i < endMonthColumn; i++)
{
//Spot Rate
sheet.Cells[4, i] = string.Empty;
//KFR Rate
sheet.Cells[5, i] = string.Empty;
}
}
this.FormatCells(sheet);
this.FreezePanes(sheet);
}
private void DeleteEmptyRows(Worksheet copyDestSheet)
{
int row = 1;
string str = copyDestSheet.Cells[row, 1].Text;
while (!string.IsNullOrWhiteSpace(str))
{
row++;
str = copyDestSheet.Cells[row, 1].Text;
}
copyDestSheet.Range["A" + row].Value = "Total";
row = row + 4;
str = copyDestSheet.Cells[row, 1].Text;
while (string.IsNullOrWhiteSpace(str))
{
ExcelUtilies.DeleteRow(copyDestSheet, "A" + row);
str = copyDestSheet.Cells[row, 1].Text;
}
}
private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheetBefore, string sheetName, string column, string reportFile, int sequence)
{
if (File.Exists(reportFile))
{
Excel.Workbook sourceBook = null;
try
{
sourceBook = destWorkbook.Application.Workbooks.Open(reportFile);
Excel.Worksheet workingSheet = sourceBook.Worksheets[1];
workingSheet.Name = sheetName + sequence;
Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet;
workingSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence];
//copy week report to dest
if (string.IsNullOrWhiteSpace(copySourceSheet.Cells[1, 1].Text))
{
ExcelUtilies.CopyRange(copySourceSheet, "B2", "B" + FunnelMonthlyReportParameters.OutputRowCount, copyDestSheetBefore, column + "1");
}
else
{
ExcelUtilies.CopyRange(copySourceSheet, "B1", "B" + FunnelMonthlyReportParameters.OutputRowCount, copyDestSheetBefore, column + "1");
}
}
catch (Exception ex)
{
this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace));
throw;
}
finally
{
FunnelReportHelper.CloseWorkingWorkbook(sourceBook);
}
}
}
private void FormatMiniTS(Excel.Worksheet copyDestSheet, string header)
{
copyDestSheet.Range["A1", "GI500"].Interior.ColorIndex = 0;
int endColum = this.GetColumnHasValueEndIndex(copyDestSheet, 1, 2);
this.MergeHeader(copyDestSheet, 1, 2, endColum - 1, "Mini TS " + header);
Range range = copyDestSheet.Range["A1", copyDestSheet.Cells[2, endColum - 1]];
range.Borders.Weight = 2;
range.Borders.Color = Color.Black;
//color column A
copyDestSheet.Range["A1"].EntireColumn.Interior.ColorIndex = 0;
int begin = this.GetWeeks(CurrentDate) + 3;
int end = begin + this.GetDays(CurrentDate);
ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin);
ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin);
copyDestSheet.Range["A" + begin, "A" + end].Interior.Color = this.ColumnColor;
this.DeleteEmptyRows(copyDestSheet);
//Set currency label
ExcelUtilies.InsertRow(copyDestSheet, "A1");
ExcelUtilies.InsertRow(copyDestSheet, "A1");
ExcelUtilies.InsertRow(copyDestSheet, "A1");
copyDestSheet.Range["A1"].Value = "Currency";
copyDestSheet.Range["A2"].Value = "RMB";
copyDestSheet.Range["A2"].Font.Bold = true;
copyDestSheet.Range["A2"].Interior.Color = this.ColumnColor;
ExcelUtilies.FreezePanes(copyDestSheet, 1, 5);
}
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);
}
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> 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 FormatDetail(Excel.Worksheet copyDestSheet, string header)
{
copyDestSheet.Range["A1", "GI500"].Interior.ColorIndex = 0;
this.FormatDetailHeaders(copyDestSheet, header);
//Range range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Online_BeginCell, FunnelCNMiniMergeParameters.Online_EndCell];
//this.MergeCells(range, header + " Online");
//range = copyDestSheet.Range[FunnelCNMiniMergeParameters.IB_BeginCell, FunnelCNMiniMergeParameters.IB_EndCell];
//this.MergeCells(range, header + " IB");
//range = copyDestSheet.Range[FunnelCNMiniMergeParameters.WI_BeginCell, FunnelCNMiniMergeParameters.WI_EndCell];
//this.MergeCells(range, header + " WI");
//range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Offline_BeginCell, FunnelCNMiniMergeParameters.Offline_EndCell];
//this.MergeCells(range, header + " Offline");
//if (copyDestSheet.Name == "NJ2 Details")
//{
// //No B2B and Others
// //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell];
// //this.MergeCells(range, header + " B2B");
// //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Others_BeginCell, FunnelCNMiniMergeParameters.Others_EndCell];
// //this.MergeCells(range, header + " Others");
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell];
// this.MergeCells(range, header + " Total");
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Online_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell.Replace("1", "2")];
// range.Interior.Color = this.HeaderColor;
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell.Replace("1", "2")];
// range.Interior.Color = Color.Gray;
// //merge cells for special weekly
// int startColumn = ExcelUtilies.ExcelColumnNameToIndex("CK");
// int endColum = this.MergeHeader(copyDestSheet, 1, startColumn, header);
// range = copyDestSheet.Range["A1", copyDestSheet.Cells[2, endColum - 2]];
// range.Borders.Weight = 2;
// range.Borders.Color = Color.Black;
//}
//else
//{
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell];
// this.MergeCells(range, header + " B2B");
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Others_BeginCell, FunnelCNMiniMergeParameters.Others_EndCell];
// this.MergeCells(range, header + " Others");
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Online_BeginCell, FunnelCNMiniMergeParameters.Total_EndCell.Replace("1", "2")];
// range.Interior.Color = this.HeaderColor;
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Total_BeginCell, FunnelCNMiniMergeParameters.Total_EndCell];
// this.MergeCells(range, header + " Total");
// range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Total_BeginCell, FunnelCNMiniMergeParameters.Total_EndCell.Replace("1", "2")];
// range.Interior.Color = Color.Gray;
// //merge cells for special weekly
// int startColumn = ExcelUtilies.ExcelColumnNameToIndex("DS");
// int endColum = this.MergeHeader(copyDestSheet, 1, startColumn, header);
// range = copyDestSheet.Range["A1", copyDestSheet.Cells[2, endColum - 2]];
// range.Borders.Weight = 2;
// range.Borders.Color = Color.Black;
//}
//color column A
copyDestSheet.Range["A1"].EntireColumn.Interior.ColorIndex = 0;
int begin = this.GetWeeks(CurrentDate) + 3;
int end = begin + this.GetDays(CurrentDate);
//delete daily headers
ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin);
ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin);
copyDestSheet.Range["A" + begin, "A" + end].Interior.Color = this.ColumnColor;
this.DeleteEmptyRows(copyDestSheet);
//Set currency label
ExcelUtilies.InsertRow(copyDestSheet, "A1");
ExcelUtilies.InsertRow(copyDestSheet, "A1");
ExcelUtilies.InsertRow(copyDestSheet, "A1");
copyDestSheet.Range["A1"].Value = "Currency";
copyDestSheet.Range["A2"].Value = "RMB";
copyDestSheet.Range["A2"].Font.Bold = true;
copyDestSheet.Range["A2"].Interior.Color = this.ColumnColor;
ExcelUtilies.FreezePanes(copyDestSheet, 1, 5);
}
private Excel.Workbook MergedOutputFiles(Excel.Application excel, Dictionary <string, ReportContext> MergedFiles)
{
Dictionary <string, string> thisMonthReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "ThisMonth");
Dictionary <string, string> monthReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "1011Month");
Dictionary <string, string> monthDiffReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "MonthDiff");
Dictionary <string, string> actualReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "Actuals");
Dictionary <string, string> KFRxReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "KFRx");
Dictionary <string, string> DiffReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "Diff");
Dictionary <string, string> PriorYearReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "PriorYear");
Dictionary <string, string> PriorYearDiffReport = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "PriorYearDiff");
if (thisMonthReports.Count == 0)
{
return(null);
}
Excel.Workbook tempWorkbook = null;
try
{
this.Logger.Message("Create tempWorkbook.");
tempWorkbook = excel.Workbooks.Add(Missing.Value);
//this.AddCategoryOutputSheet(tempWorkbook);
this.Logger.Message("Create Output Sheets.");
Excel.Worksheet outputTotalSheet = tempWorkbook.Sheets.Add();
outputTotalSheet.Name = OutputTotal;
Excel.Worksheet outputJapanSheet = tempWorkbook.Sheets.Add();
outputJapanSheet.Name = OutputJPKR;
Excel.Worksheet outputBrazilSheet = tempWorkbook.Sheets.Add();
outputBrazilSheet.Name = OutputBrazil;
Excel.Worksheet outputEuropeSheet = tempWorkbook.Sheets.Add();
outputEuropeSheet.Name = OutputEurope;
Excel.Worksheet outputMXUSSheet = tempWorkbook.Sheets.Add();
outputMXUSSheet.Name = OutputMXUS;
Excel.Worksheet outputOthersSheet = tempWorkbook.Sheets.Add();
outputOthersSheet.Name = OutputOthers;
//create a new work sheet
Excel.Worksheet tempWorkSheet = tempWorkbook.Worksheets[1] as Excel.Worksheet;
this.Logger.Message("Copy to tempwork.");
foreach (var item in actualReports.Keys)
{
thisMonthBook = excel.Workbooks.Open(thisMonthReports[item]);
Excel.Worksheet thisMonthSheet = thisMonthBook.Worksheets[1];
thisMonthSheet.Name = item;
//copy month report to dest
//Column B - M
thisMonthSheet.Copy(Missing.Value, tempWorkSheet);
Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[item];
if (string.IsNullOrWhiteSpace(copyDestSheet.Cells[1, 1].Text))
{
ExcelUtilies.DeleteRow(copyDestSheet, "A1");
}
//month
if (monthReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputMonth, monthReports[item], 1);
}
//monthDiff
if (monthDiffReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputMonthDiff, monthDiffReports[item], 2);
}
//Actuals
if (actualReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputActuals, actualReports[item], 3);
}
//KFR1
if (KFRxReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputKFR1, KFRxReports[item], 4);
}
//Diff
if (DiffReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputDiff, DiffReports[item], 5);
}
//PriorYear
if (PriorYearReports.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputPriorYear, PriorYearReports[item], 6);
}
//PriorYearDiff
if (PriorYearDiffReport.ContainsKey(item))
{
this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputPriorYearDiff, PriorYearDiffReport[item], 7);
}
if (!string.IsNullOrWhiteSpace(copyDestSheet.Cells[1, 1].Text))
{
ExcelUtilies.InsertRow(copyDestSheet, "A1");
}
this.SetHeaderTitle(copyDestSheet);
this.CloseOutputSourceReportsExcel();
#region Category output
Logger.Message("Category output " + item);
this.FormatOutputCells(copyDestSheet);
copyDestSheet.Activate();
if (item == "Japan")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputJapanSheet, "A3");
outputJapanSheet.Cells[2, 1] = item;
}
else if (item == "Korea")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputJapanSheet, "A21");
outputJapanSheet.Cells[20, 1] = item;
}
else if (item == "Total")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputTotalSheet, "A3");
outputTotalSheet.Cells[2, 1] = item;
}
else if (item == "Brazil")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputBrazilSheet, "A3");
outputBrazilSheet.Cells[2, 1] = item;
}
else if (item == "Europe")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A3");
outputEuropeSheet.Cells[2, 1] = item;
}
else if (item == "Spain")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A19");
outputEuropeSheet.Cells[18, 1] = item;
}
else if (item == "MEAST")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A37");
outputEuropeSheet.Cells[36, 1] = item;
}
else if (item == "ROE")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A55");
outputEuropeSheet.Cells[54, 1] = item;
}
else if (item == "Italy")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A73");
outputEuropeSheet.Cells[72, 1] = item;
}
else if (item == "Germany")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A91");
outputEuropeSheet.Cells[90, 1] = item;
}
else if (item == "France")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A109");
outputEuropeSheet.Cells[108, 1] = item;
}
else if (item == "Mexico+US+ROLA")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A3");
outputMXUSSheet.Cells[2, 1] = item;
}
else if (item == "US")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A19");
outputMXUSSheet.Cells[18, 1] = item;
}
else if (item == "Mexico")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A37");
outputMXUSSheet.Cells[36, 1] = item;
}
else if (item == "ROLA")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A55");
outputMXUSSheet.Cells[54, 1] = item;
}
else if (item == "ROA")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputOthersSheet, "A3");
outputOthersSheet.Cells[2, 1] = item;
}
else if (item == "ROW")
{
ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputOthersSheet, "A19");
outputOthersSheet.Cells[18, 1] = item;
}
//else if (item == "Thailand")
//{
// ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputOthersSheet, "A37");
// outputOthersSheet.Cells[36, 1] = item;
//}
#endregion
}
this.FormatOutputColumnWidth(outputJapanSheet);
this.FormatOutputColumnWidth(outputTotalSheet);
this.FormatOutputColumnWidth(outputMXUSSheet);
this.FormatOutputColumnWidth(outputOthersSheet);
this.FormatOutputColumnWidth(outputBrazilSheet);
this.FormatOutputColumnWidth(outputEuropeSheet);
}
catch (Exception ex)
{
this.CloseOutputSourceReportsExcel();
throw;
}
return(tempWorkbook);
}
private void InsertBlankRow(Excel.Worksheet sheet, string cell)
{
ExcelUtilies.InsertRow(sheet, cell);
sheet.Range[cell].EntireRow.Interior.ColorIndex = -1;
sheet.Range[cell].EntireRow.RowHeight = sheet.Range[cell].EntireRow.RowHeight / 2;
}
private void FormatCells(Excel.Worksheet sheet)
{
//Line name - Example
//Visits(in '000)
sheet.Range["A7"].EntireRow.NumberFormat = "#,";
string rateFormat = "#,##0%";
//Pay rate % - 40%
sheet.Range["A11"].EntireRow.NumberFormat = rateFormat;
//Appt Rate% - 45%
sheet.Range["A18"].EntireRow.NumberFormat = rateFormat;
//ShowUp% - 80%
sheet.Range["A20"].EntireRow.NumberFormat = rateFormat;
//Retention Tate% - 40%
sheet.Range["A15"].EntireRow.NumberFormat = rateFormat;
//Close rate% - 40%
sheet.Range["A24"].EntireRow.NumberFormat = rateFormat;
//Direct Costs% - 15%
sheet.Range["A41"].EntireRow.NumberFormat = rateFormat;
//Total Marketin Costs% - 14%
sheet.Range["A43"].EntireRow.NumberFormat = rateFormat;
//Sales Costs/Net Telesales revenue% - 24%
sheet.Range["A46"].EntireRow.NumberFormat = rateFormat;
//Sales Costs/Total Net revenue% - 21%
sheet.Range["A47"].EntireRow.NumberFormat = rateFormat;
//Cancellation Provision% - 14.0%
sheet.Range["A54"].EntireRow.NumberFormat = rateFormat;
//Bad debt Provision% - 7.3%
sheet.Range["A56"].EntireRow.NumberFormat = rateFormat;
//insert two columns before the first column
sheet.Range["A1"].EntireColumn.Insert();
sheet.Range["A1"].EntireColumn.Interior.ColorIndex = -1;
sheet.Range["A1"].EntireColumn.ColumnWidth = 20;
sheet.Range["A1"].EntireColumn.Insert();
sheet.Range["A1"].EntireColumn.Interior.ColorIndex = -1;
sheet.Range["A1"].EntireColumn.ColumnWidth = 15;
//# of CR - Other Costs
this.InsertTitleRow(sheet, "Other Costs", 48);
//# of Sales Staff - Salse Costs
this.InsertTitleRow(sheet, "Sales Costs", 44);
//Total Marketing Costs - Martketing Costs
this.InsertTitleRow(sheet, "Martketing Costs", 42);
this.InsertBlankRow(sheet, "A41");
//Revenue(KFR rate)
this.InsertTitleRow(sheet, "Revenue(KFR rate)", 38);
//Revenue(Spot rate)
this.InsertTitleRow(sheet, "Revenue(Spot rate)", 25);
//Funnel KPI
ExcelUtilies.DeleteRow(sheet, "A6");
this.InsertTitleRow(sheet, "Funnel KPI", 6);
//Set sheet title
sheet.Range["A7"].Value = sheet.Name;
sheet.Range["A7"].Font.Bold = true;
//Copy header above on Funnuel KPI
sheet.Range["A3"].EntireRow.Cut();
sheet.Range["A7"].Insert(Excel.XlInsertShiftDirection.xlShiftDown);
//Delete the title line added before
ExcelUtilies.DeleteRow(sheet, "A2");
sheet.Range["A2"].EntireRow.Interior.ColorIndex = -1;
sheet.Range["A3"].EntireRow.Interior.ColorIndex = -1;
sheet.Range["A4"].EntireRow.RowHeight = sheet.Range["A5"].EntireRow.RowHeight * 2;
sheet.Range["A5"].EntireRow.RowHeight = sheet.Range["A5"].EntireRow.RowHeight * 2;
sheet.Range["A5"].Value = "Country";
sheet.Range["A5"].Font.Bold = true;
sheet.Range["C5"].Value = "Funnel performance";
sheet.Range["C5"].Font.Bold = true;
sheet.Range["C5"].Interior.ColorIndex = -1;
sheet.Range["C5"].EntireColumn.ColumnWidth = 25;
//Add Border line
sheet.Range["B5"].EntireRow.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = 2;
sheet.Range["B5"].EntireRow.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
sheet.Range["B5"].EntireColumn.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = 2;
sheet.Range["B5"].EntireColumn.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
}
