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