private static void FillStaticDataForSLTemplateTotal(ExcelHelper excelHelper, string sheetName) { string cell1; string cell2; string cell3; cell1 = "O7"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=SAP!{0}+'Oracle EBS'!{0}+'Oracle NEBS'!{0}+BTS!O7+EIS!{0}", cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O9"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=SAP!{0}+'Oracle EBS'!{0}+'Oracle NEBS'!{0}+BTS!O7+EIS!{0}", cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O8"; cell2 = "O7"; cell3 = "O9"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=IF({0}=0,0,{1}/{0})", cell2, cell3), cell1); excelHelper.SetCellNumberFormat(sheetName, "0.0%", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); cell2 = ExcelHelper.GetNextCellinRow(cell2); cell3 = ExcelHelper.GetNextCellinRow(cell3); } cell1 = "O10"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=SAP!{0}+'Oracle EBS'!{0}+'Oracle NEBS'!{0}+BTS!O7+EIS!{0}", cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O11"; cell2 = "O7"; cell3 = "O10"; for (int i = 0; i < 12; i++) { excelHelper.SetCellValue(sheetName, string.Format("=IF({0}=0,0,{1}/{0})", cell2, cell3), cell1); excelHelper.SetCellNumberFormat(sheetName, "0.0%", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); cell2 = ExcelHelper.GetNextCellinRow(cell2); cell3 = ExcelHelper.GetNextCellinRow(cell3); } cell1 = "O12"; cell2 = "O9"; cell3 = "O10"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("={0}+{1}", cell2, cell3), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); cell2 = ExcelHelper.GetNextCellinRow(cell2); cell3 = ExcelHelper.GetNextCellinRow(cell3); } cell1 = "O13"; cell2 = "O7"; cell3 = "O12"; for (int i = 0; i < 12; i++) { excelHelper.SetCellValue(sheetName, string.Format("=IF({0}=0,0,{1}/{0})", cell2, cell3), cell1); excelHelper.SetCellNumberFormat(sheetName, "0.0%", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); cell2 = ExcelHelper.GetNextCellinRow(cell2); cell3 = ExcelHelper.GetNextCellinRow(cell3); } excelHelper.SetCellFormula(sheetName, "=SUM(B7:M7)", "AB7"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB7"); excelHelper.SetCellFormula(sheetName, "=IF(AB7=0,0,AB9/AB7)", "AB8"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB8"); excelHelper.SetCellFormula(sheetName, "=SUM(B9:M9)", "AB9"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB9"); excelHelper.SetCellFormula(sheetName, "=SUM(B10:M10)", "AB10"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB10"); excelHelper.SetCellFormula(sheetName, "=IF(AB7=0,0,AB10/AB7)", "AB11"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB11"); excelHelper.SetCellFormula(sheetName, "=SUM(B12:M12)", "AB12"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB12"); excelHelper.SetCellFormula(sheetName, "=IF(AB7=0,0,AB12/AB7)", "AB13"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB13"); cell1 = "AC7"; cell2 = "O7"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell2 = ExcelHelper.GetNextCellinRow(cell2); for (int i = 7; i < 14; i++) { excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell2), cell1); excelHelper.CopyCellNumberFormat(sheetName, cell2, cell1); cell1 = ExcelHelper.GetNextCellinColumn(cell1); cell2 = ExcelHelper.GetNextCellinColumn(cell2); } excelHelper.SetCellFormula(sheetName, "=AB7/AC7-1", "AD7"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD7"); excelHelper.SetCellFormula(sheetName, "=AB9/AC9-1", "AD9"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD9"); excelHelper.SetCellFormula(sheetName, "=AB10/AC10-1", "AD10"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD10"); excelHelper.SetCellFormula(sheetName, "=AB12/AC12-1", "AD12"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD12"); excelHelper.SetCellFormula(sheetName, "=SUM(O7:Z7)", "AE7"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE7"); excelHelper.SetCellFormula(sheetName, "=IF(AE7=0,0,AE9/AE7)", "AE8"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AE8"); excelHelper.SetCellFormula(sheetName, "=SUM(O9:Z9)", "AE9"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE9"); excelHelper.SetCellFormula(sheetName, "=SUM(O10:Z10)", "AE10"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE10"); excelHelper.SetCellFormula(sheetName, "=IF(AE7=0,0,AE10/AE7)", "AE11"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AE11"); excelHelper.SetCellFormula(sheetName, "=AE9+AE10", "AE12"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE12"); excelHelper.SetCellFormula(sheetName, "=IF(AE7=0,0,AE12/AE7)", "AE13"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AE13"); excelHelper.SetCellFormula(sheetName, "=AB7/AE7", "AF7"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF7"); excelHelper.SetCellFormula(sheetName, "=IF(AB8>AE8,\"Over \"&ROUND((ABS(AB8-AE8)*100),1)&\" pts\",\"Under \"&ROUND((ABS(AB8-AE8)*100),1)&\" pts\")", "AF8"); excelHelper.SetCellFormula(sheetName, "=AB9/AE9", "AF9"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF9"); excelHelper.SetCellFormula(sheetName, "=AB10/AE10", "AF10"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF10"); excelHelper.SetCellFormula(sheetName, "=IF(AB11>AE11,\"Over \"&ROUND((ABS(AB11-AE11)*100),1)&\" pts\",\"Under \"&ROUND((ABS(AB11-AE11)*100),1)&\" pts\")", "AF11"); excelHelper.SetCellFormula(sheetName, "=AB12/AE12", "AF12"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF12"); excelHelper.SetCellFormula(sheetName, "=IF(AB13>AE13,\"Over \"&ROUND((ABS(AB13-AE13)*100),1)&\" pts\",\"Under \"&ROUND((ABS(AB13-AE13)*100),1)&\" pts\")", "AF13"); }
private static void FillStaticDataForSectorTemplateTotal(ExcelHelper excelHelper, string sheetName) { string cell1; string cell2; string cell3; cell1 = "O6"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=EUC!{0}+'MRD'!{0}+Others!{0}",cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O8"; for (int i = 0; i < 12; i++) { excelHelper.SetCellValue(sheetName, budgetSoldCMPercent, cell1); excelHelper.SetCellNumberFormat(sheetName, "0.0%", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O7"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=EUC!{0}+'MRD'!{0}+Others!{0}", cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O9"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=EUC!{0}+'MRD'!{0}+Others!{0}", cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O10"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=EUC!{0}+'MRD'!{0}+Others!{0}", cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "#,###", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O11"; for (int i = 0; i < 12; i++) { excelHelper.SetCellValue(sheetName, string.Format("=EUC!{0}",cell1), cell1); excelHelper.SetCellNumberFormat(sheetName, "0.0%", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); } cell1 = "O12"; cell2 = "O7"; cell3 = "O10"; for (int i = 0; i < 12; i++) { excelHelper.SetCellFormula(sheetName, string.Format("=IF({1}=0,0,({0}+{1})/{1})", cell2, cell3), cell1); excelHelper.SetCellNumberFormat(sheetName, "0.00", cell1); cell1 = ExcelHelper.GetNextCellinRow(cell1); cell2 = ExcelHelper.GetNextCellinRow(cell2); cell3 = ExcelHelper.GetNextCellinRow(cell3); } excelHelper.SetCellFormula(sheetName, "=SUM(B6:M6)", "AB6"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB6"); excelHelper.SetCellFormula(sheetName, "=SUM(B7:M7)", "AB7"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB7"); excelHelper.SetCellFormula(sheetName, "=IF(AB6=0,0,AB7/AB6)", "AB8"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB8"); excelHelper.SetCellFormula(sheetName, "=SUM(B9:M9)", "AB9"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB9"); excelHelper.SetCellFormula(sheetName, "=SUM(B10:M10)", "AB10"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB10"); cell1 = "B11"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell1 = ExcelHelper.GetNextCellinRow(cell1); excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell1), "AB11"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB11"); cell1 = "B12"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell1 = ExcelHelper.GetNextCellinRow(cell1); excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell1), "AB12"); excelHelper.SetCellNumberFormat(sheetName, "0.00", "AB12"); cell1 = "AC6"; cell2 = "O6"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell2 = ExcelHelper.GetNextCellinRow(cell2); for (int i = 6; i < 13; i++) { excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell2), cell1); excelHelper.CopyCellNumberFormat(sheetName, cell2, cell1); cell1 = ExcelHelper.GetNextCellinColumn(cell1); cell2 = ExcelHelper.GetNextCellinColumn(cell2); } excelHelper.SetCellFormula(sheetName, "=AB6/AC6-1", "AD6"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD6"); excelHelper.SetCellFormula(sheetName, "=AB7/AC7-1", "AD7"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD7"); excelHelper.SetCellFormula(sheetName, "=IF($AB$8>AC8,\"Over \"&ROUND((ABS($AB$8-AC8)*100),1)&\" pts\",\"Under \"&ROUND((ABS($AB$8-AC8)*100),1)&\" pts\")", "AD8"); excelHelper.SetCellFormula(sheetName, "=AB10/AC10-1", "AD10"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AD10"); excelHelper.SetCellFormula(sheetName, "=IF($AB$11>AC11,\"Over \"&ROUND((ABS($AB$11-AC11)*100),0)&\" pts\",\"Under \"&ROUND((ABS($AB$11-AC11)*100),0)&\" pts\")", "AD11"); excelHelper.SetCellFormula(sheetName, "=IF($AB$12>AC12,\"Over \"&ROUND((ABS($AB$12-AC12)*100),0)&\" pts\",\"Under \"&ROUND((ABS($AB$12-AC12)*100),0)&\" pts\")", "AD12"); excelHelper.SetCellFormula(sheetName, "=SUM(O6:Z6)", "AE6"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE6"); excelHelper.SetCellFormula(sheetName, "=SUM(O7:Z7)", "AE7"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE7"); excelHelper.SetCellFormula(sheetName, budgetSoldCMPercent, "AE8"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AE8"); excelHelper.SetCellFormula(sheetName, "=SUM(O10:Z10)", "AE10"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AE10"); excelHelper.SetCellFormula(sheetName, budgetBDPercentofRevenue, "AE11"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AE11"); excelHelper.SetCellFormula(sheetName, "=IF(AE10=0,0,(AE7+AE10)/AE10)", "AE12"); excelHelper.SetCellNumberFormat(sheetName, "0.00", "AE12"); excelHelper.SetCellFormula(sheetName, "=AB6/AE6", "AF6"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF6"); excelHelper.SetCellFormula(sheetName, "=AB7/AE7", "AF7"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF7"); excelHelper.SetCellFormula(sheetName, "=IF($AB$8>AE8,\"Over \"&ROUND((ABS($AB$8-AE8)*100),1)&\" pts\",\"Under \"&ROUND((ABS($AB$8-AE8)*100),1)&\" pts\")", "AF8"); excelHelper.SetCellFormula(sheetName, "=(AB10)/AE10", "AF10"); excelHelper.SetCellNumberFormat(sheetName, "0%", "AF10"); excelHelper.SetCellFormula(sheetName, "=IF($AB$11>AE11,\"Over \"&ROUND((ABS($AB$11-AE11)*100),0)&\" pts\",\"Under \"&ROUND((ABS($AB$11-AE11)*100),0)&\" pts\")", "AF11"); excelHelper.SetCellFormula(sheetName, "=IF($AB$12>AE12,\"Over \"&ROUND((ABS($AB$12-AE12)*100),0)&\" pts\",\"Under \"&ROUND((ABS($AB$12-AE12)*100),0)&\" pts\")", "AF12"); }
private static void FillStaticDataForSLTemplate2(ExcelHelper excelHelper, string sheetName) { string cell1 = "AB6"; string cell2 = "B6"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell2 = ExcelHelper.GetNextCellinRow(cell2); for (int i = 6; i < 14; i++) { excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell2), cell1); cell1 = ExcelHelper.GetNextCellinColumn(cell1); cell2 = ExcelHelper.GetNextCellinColumn(cell2); } }
private static void FillStaticDataForSectorTemplate2(ExcelHelper excelHelper, string sheetName) { string cell1; excelHelper.SetCellFormula(sheetName, "=SUM(B6:M6)", "AB6"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB6"); excelHelper.SetCellFormula(sheetName, "=SUM(B7:M7)", "AB7"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB7"); excelHelper.SetCellFormula(sheetName, "=IF(AB6=0,0,AB7/AB6)", "AB8"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB8"); excelHelper.SetCellFormula(sheetName, "=SUM(B9:M9)", "AB9"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB9"); excelHelper.SetCellFormula(sheetName, "=SUM(B10:M10)", "AB10"); excelHelper.SetCellNumberFormat(sheetName, "#,###", "AB10"); cell1 = "B11"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell1 = ExcelHelper.GetNextCellinRow(cell1); excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell1), "AB11"); excelHelper.SetCellNumberFormat(sheetName, "0.0%", "AB11"); cell1 = "B12"; for (int i = 0; i < InputSheetMetadataConfigs.ReportMonth - 1; i++) cell1 = ExcelHelper.GetNextCellinRow(cell1); excelHelper.SetCellFormula(sheetName, string.Format("={0}", cell1), "AB12"); excelHelper.SetCellNumberFormat(sheetName, "0.00", "AB12"); }