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 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);
}
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 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 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;
}
