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