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