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