public static void PrepareOutput()
{
var excelHelper = new ExcelHelper(InputSheetMetadataConfigs.WorkbookPath);
excelHelper.InitializeAllStyles();
Worksheet sheetEIS = excelHelper.AddWorksheet("EIS", excelHelper.GetWorkSheetByName("Bookings(YTD)"), null, null);
excelHelper.StyleFormat(sheetEIS, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "EIS");
Worksheet sheetBTS = excelHelper.AddWorksheet("BTS", excelHelper.GetWorkSheetByName("EIS"), null, null);
excelHelper.StyleFormat(sheetBTS, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "BTS");
Worksheet sheetOracleIND = excelHelper.AddWorksheet("Oracle IND", excelHelper.GetWorkSheetByName("BTS"), null, null);
excelHelper.StyleFormat(sheetOracleIND, ExcelHelper.SheetStyle.SheetStyle2, "Service Line", "Oracle IND");
Worksheet sheetOracleBI = excelHelper.AddWorksheet("Oracle BI", excelHelper.GetWorkSheetByName("Oracle IND"), null, null);
excelHelper.StyleFormat(sheetOracleBI, ExcelHelper.SheetStyle.SheetStyle2, "Service Line", "Oracle BI");
Worksheet sheetOracleSIEBEL = excelHelper.AddWorksheet("Oracle SIEBEL", excelHelper.GetWorkSheetByName("Oracle BI"), null, null);
excelHelper.StyleFormat(sheetOracleSIEBEL, ExcelHelper.SheetStyle.SheetStyle2, "Service Line", "Oracle SIEBEL");
Worksheet sheetOracleHyperion = excelHelper.AddWorksheet("Oracle Hyperion", excelHelper.GetWorkSheetByName("Oracle SIEBEL"), null, null);
excelHelper.StyleFormat(sheetOracleHyperion, ExcelHelper.SheetStyle.SheetStyle2, "Service Line", "Oracle Hyperion");
Worksheet sheetOracleHCM = excelHelper.AddWorksheet("Oracle HCM", excelHelper.GetWorkSheetByName("Oracle Hyperion"), null, null);
excelHelper.StyleFormat(sheetOracleHCM, ExcelHelper.SheetStyle.SheetStyle2, "Service Line", "Oracle HCM");
Worksheet sheetOracleNEBS = excelHelper.AddWorksheet("Oracle NEBS", excelHelper.GetWorkSheetByName("Oracle HCM"), null, "#1F3E62");
excelHelper.StyleFormat(sheetOracleNEBS, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "Oracle NEBS");
Worksheet sheetOracleEBS = excelHelper.AddWorksheet("Oracle EBS", excelHelper.GetWorkSheetByName("Oracle NEBS"), null, null);
excelHelper.StyleFormat(sheetOracleEBS, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "Oracle EBS");
Worksheet sheetOracleAll = excelHelper.AddWorksheet("Oracle All", excelHelper.GetWorkSheetByName("Oracle EBS"), null, "#1F3E62");
excelHelper.StyleFormat(sheetOracleAll, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "Oracle");
Worksheet sheetSAP = excelHelper.AddWorksheet("SAP", excelHelper.GetWorkSheetByName("Oracle All"), null, null);
excelHelper.StyleFormat(sheetSAP, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "SAP");
Worksheet sheetSLsTotal = excelHelper.AddWorksheet("SLs Total", excelHelper.GetWorkSheetByName("SAP"), null, "#1F3E62");
excelHelper.StyleFormat(sheetSLsTotal, ExcelHelper.SheetStyle.SheetStyle1, "Service Line", "All SLs");
Worksheet sheetOthers = excelHelper.AddWorksheet("Others", excelHelper.GetWorkSheetByName("SLs Total"), null, null);
excelHelper.StyleFormat(sheetOthers, ExcelHelper.SheetStyle.SheetStyle5, "Sector", "Others");
Worksheet sheetMRD = excelHelper.AddWorksheet("MRD", excelHelper.GetWorkSheetByName("Others"), null, null);
excelHelper.StyleFormat(sheetMRD, ExcelHelper.SheetStyle.SheetStyle4, "Sector", "MRD");
Worksheet sheetSectorTotal = excelHelper.AddWorksheet("EUC", excelHelper.GetWorkSheetByName("MRD"), null, null);
excelHelper.StyleFormat(sheetSectorTotal, ExcelHelper.SheetStyle.SheetStyle4, "Sector", "EUC");
Worksheet sheetEUC = excelHelper.AddWorksheet("Sector Total", excelHelper.GetWorkSheetByName("EUC"), null, "#1F3E62");
excelHelper.StyleFormat(sheetEUC, ExcelHelper.SheetStyle.SheetStyle4, "Sector", "All Sectors");
Worksheet sheetSummary = excelHelper.AddWorksheet("Summary", excelHelper.GetWorkSheetByName("Sector Total"), null, "#1F3E62");
excelHelper.StyleFormat(sheetSummary, ExcelHelper.SheetStyle.SheetStyle3, null, null);
excelHelper.SaveAs(FinaChanCore.TempPath);
excelHelper.Close();
}
public static void Read()
{
var excelHelper = new ExcelHelper(InputSheetMetadataConfigs.WorkbookPath);
#region Fill Bookings(YTD)
string sheetNameBookingsYTD = InputSheetMetadataConfigs.GetSheetMetadata("Bookings(YTD)").SheetName;
KPIReport.BookingsYTD = new BookingsYTD
{
Title = excelHelper.GetCellValue(sheetNameBookingsYTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameBookingsYTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameBookingsYTD, 3, 2)
};
DataTable sheetDataBookingsYTD = KPIReport.BookingsYTD.Data;
excelHelper.GetRangeValue(sheetNameBookingsYTD, 6, 1, sheetDataBookingsYTD.Columns.Count, ref sheetDataBookingsYTD);
Reader.Finished++;
#endregion
#region Fill Project Contribution(MTD)
string sheetNameProjectContributionMTD = InputSheetMetadataConfigs.GetSheetMetadata("Project Contribution(MTD)").SheetName;
KPIReport.ProjectContributionMTD = new ProjectContributionMTD()
{
Title = excelHelper.GetCellValue(sheetNameProjectContributionMTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameProjectContributionMTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameProjectContributionMTD, 3, 2)
};
DataTable sheetDataProjectContributionMTD = KPIReport.ProjectContributionMTD.Data;
excelHelper.GetRangeValue(sheetNameProjectContributionMTD, 7, 1, sheetDataProjectContributionMTD.Columns.Count, ref sheetDataProjectContributionMTD);
Reader.Finished++;
#endregion
#region Fill Project Contribution(YTD)
string sheetNameProjectContributionYTD = InputSheetMetadataConfigs.GetSheetMetadata("Project Contribution(YTD)").SheetName;
KPIReport.ProjectContributionYTD = new ProjectContributionYTD()
{
Title = excelHelper.GetCellValue(sheetNameProjectContributionYTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameProjectContributionYTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameProjectContributionYTD, 3, 2)
};
DataTable sheetDataProjectContributionYTD = KPIReport.ProjectContributionYTD.Data;
excelHelper.GetRangeValue(sheetNameProjectContributionYTD, 7, 1, sheetDataProjectContributionYTD.Columns.Count, ref sheetDataProjectContributionYTD);
Reader.Finished++;
#endregion
#region BD(YTD)
string sheetNameBDYTD = InputSheetMetadataConfigs.GetSheetMetadata("BD(YTD)").SheetName;
KPIReport.BDYTD = new BDYTD
{
Title = excelHelper.GetCellValue(sheetNameBDYTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameBDYTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameBDYTD, 3, 2)
};
DataTable sheetDataBDYTD = KPIReport.BDYTD.Data;
excelHelper.GetRangeValue(sheetNameBDYTD, 7, 1, sheetDataBDYTD.Columns.Count, ref sheetDataBDYTD);
Reader.Finished++;
#endregion
#region Sector IDC(YTD)
string sheetNameSectorIDCYTD = InputSheetMetadataConfigs.GetSheetMetadata("Sector IDC(YTD)").SheetName;
KPIReport.SectorIDCYTD = new SectorIDCYTD()
{
Title = excelHelper.GetCellValue(sheetNameSectorIDCYTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameSectorIDCYTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameSectorIDCYTD, 3, 2)
};
DataTable sheetDataSectorIDCYTD = KPIReport.SectorIDCYTD.Data;
excelHelper.GetRangeValue(sheetNameSectorIDCYTD, 7, 1, sheetDataSectorIDCYTD.Columns.Count, ref sheetDataSectorIDCYTD);
Reader.Finished++;
#endregion
#region SL IDC(YTD)
string sheetNameSLIDCYTD = InputSheetMetadataConfigs.GetSheetMetadata("SL IDC(YTD)").SheetName;
KPIReport.SLIDCYTD = new SLIDCYTD()
{
Title = excelHelper.GetCellValue(sheetNameSLIDCYTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameSLIDCYTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameSLIDCYTD, 3, 2)
};
DataTable sheetDataSLIDCYTD = KPIReport.SLIDCYTD.Data;
excelHelper.GetRangeValue(sheetNameSLIDCYTD, 7, 1, sheetDataSLIDCYTD.Columns.Count, ref sheetDataSLIDCYTD);
Reader.Finished++;
#endregion
#region Oracle IDC by employee(YTD)
string sheetNameOracleIDCbyemployeeYTD = InputSheetMetadataConfigs.GetSheetMetadata("Oracle IDC by employee(YTD)").SheetName;
KPIReport.OracleIDCbyemployeeYTD = new OracleIDCbyemployeeYTD()
{
Title = excelHelper.GetCellValue(sheetNameOracleIDCbyemployeeYTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameOracleIDCbyemployeeYTD, 2, 2),
Currency = excelHelper.GetCellValue(sheetNameOracleIDCbyemployeeYTD, 3, 2)
};
DataTable sheetDataOracleIDCbyemployeeYTD = KPIReport.OracleIDCbyemployeeYTD.Data;
excelHelper.GetRangeValue(sheetNameOracleIDCbyemployeeYTD, 5, 1, sheetDataOracleIDCbyemployeeYTD.Columns.Count, ref sheetDataOracleIDCbyemployeeYTD);
Reader.Finished++;
#endregion
#region Cash Collection(MTD)
string sheetNameCashCollectionMTD = InputSheetMetadataConfigs.GetSheetMetadata("Cash Collection(MTD)").SheetName;
KPIReport.CashCollectionMTD = new CashCollectionMTD()
{
Title = excelHelper.GetCellValue(sheetNameCashCollectionMTD, 1, 1),
Period = excelHelper.GetCellValue(sheetNameCashCollectionMTD, 2, 2),
};
DataTable sheetDataCashCollectionMTD = KPIReport.CashCollectionMTD.Data;
excelHelper.GetRangeValue(sheetNameCashCollectionMTD, 6, 1, sheetDataCashCollectionMTD.Columns.Count, ref sheetDataCashCollectionMTD);
Reader.Finished++;
#endregion
excelHelper.Close();
}
private static void FillStaticDataForSummary(ExcelHelper excelHelper, string sheetName)
{
}
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 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 FillStaticData()
{
var excelHelper = new ExcelHelper(FinaChanCore.TempPath);
foreach (var sheetname in KPIReport.generatedSheetnames)
{
if (sheetname == "Summary")
{
FillStaticDataForSummary(excelHelper, sheetname);
continue;
}
excelHelper.SetCellValue(sheetname
, string.Format("{0}-{1}"
, CultureInfo.GetCultureInfo("en-us").DateTimeFormat.GetMonthName(InputSheetMetadataConfigs.ReportMonth)
, InputSheetMetadataConfigs.ReportYear)
, 3, 3);
if (sheetname == "EUC")
{
FillStaticDataForSectorTemplate1(excelHelper, sheetname, "B3");
continue;
}
if (sheetname == "MRD")
{
FillStaticDataForSectorTemplate1(excelHelper, sheetname, "B11");
continue;
}
if (sheetname == "Others")
{
FillStaticDataForSectorTemplate2(excelHelper, sheetname);
continue;
}
if (sheetname == "Sector Total")
{
FillStaticDataForSectorTemplateTotal(excelHelper, sheetname);
continue;
}
if (sheetname == "EIS")
{
FillStaticDataForSLTemplate1(excelHelper, sheetname, "B40");
continue;
}
if (sheetname == "BTS")
{
FillStaticDataForSLTemplate1(excelHelper, sheetname, "B35");
continue;
}
if (sheetname == "Oracle IND")
{
FillStaticDataForSLTemplate2(excelHelper, sheetname);
continue;
}
if (sheetname == "Oracle BI")
{
FillStaticDataForSLTemplate2(excelHelper, sheetname);
continue;
}
if (sheetname == "Oracle SIEBEL")
{
FillStaticDataForSLTemplate2(excelHelper, sheetname);
continue;
}
if (sheetname == "Oracle Hyperion")
{
FillStaticDataForSLTemplate2(excelHelper, sheetname);
continue;
}
if (sheetname == "Oracle HCM")
{
FillStaticDataForSLTemplate2(excelHelper, sheetname);
continue;
}
if (sheetname == "Oracle NEBS")
{
FillStaticDataForSLTemplate1(excelHelper, sheetname, "B29");
continue;
}
if (sheetname == "Oracle EBS")
{
FillStaticDataForSLTemplate1(excelHelper, sheetname, "B24");
continue;
}
if (sheetname == "Oracle All")
{
FillStaticDataForSLTemplateOracleAll(excelHelper, sheetname);
continue;
}
if (sheetname == "SAP")
{
FillStaticDataForSLTemplate1(excelHelper, sheetname, "B19");
continue;
}
if (sheetname == "SLs Total")
{
FillStaticDataForSLTemplateTotal(excelHelper, sheetname);
continue;
}
}
excelHelper.SaveAs(OutputPath);
excelHelper.Close();
}
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 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");
}