//create a new workbook Workbook workbook = new Workbook(); Object data = new Object[][]{ {"Cost Center", "Annual Cost", "Percent of Total", "Cumulative Percent"}, {"Parts and materials", 1325000, null, null}, {"Manufacturing equipment", 900500, null, null}, {"Salaries", 575000, null, null}, {"Maintenance", 395000, null, null}, {"Office lease", 295000, null, null}, {"Warehouse lease", 250000, null, null}, {"Insurance", 180000, null, null}, {"Benefits and pensions", 130000, null, null}, {"Vehicles", 125000, null, null}, {"Research", 75000, null, null}, }; IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.setName("COST DATA and CHART"); worksheet.setTabColor(Color.FromArgb(63, 94, 101)); worksheet.getSheetView().setDisplayGridlines(false); //Set Value. worksheet.getRange("B2").setValue("COST ANALYSIS - PARETO"); worksheet.getRange("B39").setValue("COST ANALYSIS"); worksheet.getRange("B41:E51").setValue(data); //Set NumberFormat. worksheet.getRange("C42:C52").setNumberFormat("\"$\"#,##0.00_);[Red](\"$\"#,##0.00)"); worksheet.getRange("D42:E52").setNumberFormat("0.00%"); //Change the range's RowHeight and ColumnWidth. worksheet.setStandardHeight(15.75); worksheet.setStandardWidth(8.43); worksheet.getRange("1:1").setRowHeight(12.75); worksheet.getRange("2:2").setRowHeight(20.25); worksheet.getRange("3:3").setRowHeight(16.5); worksheet.getRange("39:39").setRowHeight(20.25); worksheet.getRange("40:40").setRowHeight(16.5); worksheet.getRange("41:41").setRowHeight(29.25); worksheet.getRange("42:51").setRowHeight(20.1); worksheet.getRange("A:A").setColumnWidth(1.44140625); worksheet.getRange("B:B").setColumnWidth(25.88671875); worksheet.getRange("C:C").setColumnWidth(18.5546875); worksheet.getRange("D:D").setColumnWidth(17.77734375); worksheet.getRange("E:E").setColumnWidth(20.77734375); //Apply one build in name style on the ranges. worksheet.getRange("B2:J2").setStyle(workbook.getStyles().get("Heading 1")); worksheet.getRange("B39:E39").setStyle(workbook.getStyles().get("Heading 1")); //Create a table and apply one build in table style. ITable table = worksheet.getTables().add(worksheet.getRange("B41:E51"), true); table.setName("tblData"); table.setTableStyle(workbook.getTableStyles().get("TableStyleLight9")); table.setShowTotals(true); table.setShowTableStyleRowStripes(true); //Use table formula in table range. worksheet.getRange("D42:D51").setFormula("=[@[Annual Cost]]/SUM([[Annual Cost]])"); worksheet.getRange("E42:E51").setFormula("=SUM(INDEX([Percent of Total],1):[@[Percent of Total]])"); table.getColumns().get(1).setTotalsCalculation(TotalsCalculation.Sum); table.getColumns().get(2).setTotalsCalculation(TotalsCalculation.Sum); table.getColumns().get(3).setTotalsCalculation(TotalsCalculation.None); //Add chart. IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 9.75, 48, 597, 472.5); shape.setName("Pareto Chart"); //Add Series. ISeries series_ColumnClustered = shape.getChart().getSeriesCollection().newSeries(); series_ColumnClustered.setFormula("=SERIES('COST DATA and CHART'!$C$41,'COST DATA and CHART'!$B$42:$B$51,'COST DATA and CHART'!$C$42:$C$51,1)"); series_ColumnClustered.getFormat().getFill().twoColorGradient(GradientStyle.Horizontal, 1); series_ColumnClustered.getFormat().getFill().setGradientAngle(90); series_ColumnClustered.getFormat().getFill().getGradientStops().get(0).getColor().setObjectThemeColor(ThemeColor.Accent1); series_ColumnClustered.getFormat().getFill().getGradientStops().get(0).getColor().setBrightness(0); series_ColumnClustered.getFormat().getFill().getGradientStops().get(0).setPosition(0); series_ColumnClustered.getFormat().getFill().getGradientStops().get(1).getColor().setObjectThemeColor(ThemeColor.Accent1); series_ColumnClustered.getFormat().getFill().getGradientStops().get(1).getColor().setBrightness(-0.16); series_ColumnClustered.getFormat().getFill().getGradientStops().get(1).setPosition(1); series_ColumnClustered.getFormat().getLine().getColor().setObjectThemeColor(ThemeColor.Light1); ISeries series_Line = shape.getChart().getSeriesCollection().newSeries(); series_Line.setChartType(ChartType.Line); series_Line.setFormula("=SERIES('COST DATA and CHART'!$E$41,,'COST DATA and CHART'!$E$42:$E$51,2)"); series_Line.getFormat().getLine().setWeight(2.25); series_Line.setAxisGroup(AxisGroup.Secondary); //Change the secondary's maxinumscale. IAxis secondary_axis = shape.getChart().getAxes().item(AxisType.Value, AxisGroup.Secondary); secondary_axis.setMaximumScale(1.0); //Set the chart's title format. shape.getChart().getChartTitle().setText("Cost Center"); shape.getChart().getChartTitle().getFont().setThemeFont(ThemeFont.Minor); shape.getChart().getChartTitle().getFont().getColor().setRGB(Color.FromArgb(89, 89, 89)); shape.getChart().getChartTitle().getFont().setSize(18); //Set the chart has no legend. shape.getChart().setHasLegend(false); //Set the char group's Overlap and GapWidth. shape.getChart().getColumnGroups().get(0).setOverlap(0); shape.getChart().getColumnGroups().get(0).setGapWidth(0); //Set chart area's format. shape.getChart().getChartArea().getFormat().getFill().presetGradient(GradientStyle.Horizontal, 1, PresetGradientType.EarlySunset); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().delete(3); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().delete(3); shape.getChart().getChartArea().getFormat().getFill().setGradientAngle(90); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(0).getColor().setObjectThemeColor(ThemeColor.Light1); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(0).getColor().setBrightness(0); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(0).setPosition(0); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(1).getColor().setObjectThemeColor(ThemeColor.Light1); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(1).getColor().setBrightness(-0.15); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(1).setPosition(0.68); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(2).getColor().setObjectThemeColor(ThemeColor.Light1); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(2).getColor().setBrightness(0); shape.getChart().getChartArea().getFormat().getFill().getGradientStops().get(2).setPosition(1); //Create customize theme. ITheme theme = new Theme("test"); theme.getThemeColorScheme().get(ThemeColor.Dark1).setRGB(Color.FromArgb(0, 0, 0)); theme.getThemeColorScheme().get(ThemeColor.Light1).setRGB(Color.FromArgb(255, 255, 255)); theme.getThemeColorScheme().get(ThemeColor.Dark2).setRGB(Color.FromArgb(96, 89, 88)); theme.getThemeColorScheme().get(ThemeColor.Light2).setRGB(Color.FromArgb(241, 246, 246)); theme.getThemeColorScheme().get(ThemeColor.Accent1).setRGB(Color.FromArgb(63, 94, 101)); theme.getThemeColorScheme().get(ThemeColor.Accent2).setRGB(Color.FromArgb(224, 170, 83)); theme.getThemeColorScheme().get(ThemeColor.Accent3).setRGB(Color.FromArgb(179, 29, 66)); theme.getThemeColorScheme().get(ThemeColor.Accent4).setRGB(Color.FromArgb(162, 67, 162)); theme.getThemeColorScheme().get(ThemeColor.Accent5).setRGB(Color.FromArgb(120, 59, 101)); theme.getThemeColorScheme().get(ThemeColor.Accent6).setRGB(Color.FromArgb(55, 120, 169)); theme.getThemeColorScheme().get(ThemeColor.Hyperlink).setRGB(Color.FromArgb(71, 166, 181)); theme.getThemeColorScheme().get(ThemeColor.FollowedHyperlink).setRGB(Color.FromArgb(120, 59, 101)); theme.getThemeFontScheme().getMajor().get(FontLanguageIndex.Latin).setName("Constantia"); theme.getThemeFontScheme().getMinor().get(FontLanguageIndex.Latin).setName("Helvetica"); //Apply the above custom theme. workbook.setTheme(theme); //Set active cell. worksheet.getRange("B43").activate(); //save to an excel file workbook.save("CostAnalysisWithParetoChart.xlsx");
//create a new workbook var workbook = Workbook() val data = arrayOf(arrayOf("Cost Center", "Annual Cost", "Percent of Total", "Cumulative Percent"), arrayOf("Parts and materials", 1325000, null, null), arrayOf("Manufacturing equipment", 900500, null, null), arrayOf("Salaries", 575000, null, null), arrayOf("Maintenance", 395000, null, null), arrayOf("Office lease", 295000, null, null), arrayOf("Warehouse lease", 250000, null, null), arrayOf("Insurance", 180000, null, null), arrayOf("Benefits and pensions", 130000, null, null), arrayOf("Vehicles", 125000, null, null), arrayOf("Research", 75000, null, null)) val worksheet = workbook.worksheets.get(0) worksheet.name = "COST DATA and CHART" worksheet.tabColor = Color.FromArgb(63, 94, 101) worksheet.sheetView.displayGridlines = false //Set Value. worksheet.getRange("B2").value = "COST ANALYSIS - PARETO" worksheet.getRange("B39").value = "COST ANALYSIS" worksheet.getRange("B41:E51").value = data //Set NumberFormat. worksheet.getRange("C42:C52").numberFormat = "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)" worksheet.getRange("D42:E52").numberFormat = "0.00%" //Change the range's RowHeight and ColumnWidth. worksheet.standardHeight = 15.75 worksheet.standardWidth = 8.43 worksheet.getRange("1:1").rowHeight = 12.75 worksheet.getRange("2:2").rowHeight = 20.25 worksheet.getRange("3:3").rowHeight = 16.5 worksheet.getRange("39:39").rowHeight = 20.25 worksheet.getRange("40:40").rowHeight = 16.5 worksheet.getRange("41:41").rowHeight = 29.25 worksheet.getRange("42:51").rowHeight = 20.1 worksheet.getRange("A:A").columnWidth = 1.44140625 worksheet.getRange("B:B").columnWidth = 25.88671875 worksheet.getRange("C:C").columnWidth = 18.5546875 worksheet.getRange("D:D").columnWidth = 17.77734375 worksheet.getRange("E:E").columnWidth = 20.77734375 //Apply one build in name style on the ranges. worksheet.getRange("B2:J2").style = workbook.styles.get("Heading 1") worksheet.getRange("B39:E39").style = workbook.styles.get("Heading 1") //Create a table and apply one build in table style. val table = worksheet.tables.add(worksheet.getRange("B41:E51"), true) table.name = "tblData" table.tableStyle = workbook.tableStyles.get("TableStyleLight9") table.showTotals = true table.showTableStyleRowStripes = true //Use table formula in table range. worksheet.getRange("D42:D51").formula = "=[@[Annual Cost]]/SUM([[Annual Cost]])" worksheet.getRange("E42:E51").formula = "=SUM(INDEX([Percent of Total],1):[@[Percent of Total]])" table.columns.get(1).totalsCalculation = TotalsCalculation.Sum table.columns.get(2).totalsCalculation = TotalsCalculation.Sum table.columns.get(3).totalsCalculation = TotalsCalculation.None //Add chart. val shape = worksheet.shapes.addChart(ChartType.ColumnClustered, 9.75, 48.0, 597.0, 472.5) shape.name = "Pareto Chart" //Add Series. val series_ColumnClustered = shape.chart.seriesCollection.newSeries() series_ColumnClustered.formula = "=SERIES('COST DATA and CHART'!\$C$41,'COST DATA and CHART'!\$B$42:\$B$51,'COST DATA and CHART'!\$C$42:\$C$51,1)" series_ColumnClustered.format.fill.twoColorGradient(GradientStyle.Horizontal, 1) series_ColumnClustered.format.fill.gradientAngle = 90.0 series_ColumnClustered.format.fill.gradientStops.get(0).color.objectThemeColor = ThemeColor.Accent1 series_ColumnClustered.format.fill.gradientStops.get(0).color.brightness = 0.0 series_ColumnClustered.format.fill.gradientStops.get(0).position = 0.0 series_ColumnClustered.format.fill.gradientStops.get(1).color.objectThemeColor = ThemeColor.Accent1 series_ColumnClustered.format.fill.gradientStops.get(1).color.brightness = -0.16 series_ColumnClustered.format.fill.gradientStops.get(1).position = 1.0 series_ColumnClustered.format.line.color.objectThemeColor = ThemeColor.Light1 val series_Line = shape.chart.seriesCollection.newSeries() series_Line.chartType = ChartType.Line series_Line.formula = "=SERIES('COST DATA and CHART'!\$E$41,,'COST DATA and CHART'!\$E$42:\$E$51,2)" series_Line.format.line.weight = 2.25 series_Line.axisGroup = AxisGroup.Secondary //Change the secondary's maxinumscale. val secondary_axis = shape.chart.axes.item(AxisType.Value, AxisGroup.Secondary) secondary_axis.maximumScale = 1.0 //Set the chart's title format. shape.chart.chartTitle.text = "Cost Center" shape.chart.chartTitle.font.themeFont = ThemeFont.Minor shape.chart.chartTitle.font.color.rgb = Color.FromArgb(89, 89, 89) shape.chart.chartTitle.font.size = 18.0 //Set the chart has no legend. shape.chart.hasLegend = false //Set the char group's Overlap and GapWidth. shape.chart.columnGroups.get(0).overlap = 0 shape.chart.columnGroups.get(0).gapWidth = 0 //Set chart area's format. shape.chart.chartArea.format.fill.presetGradient(GradientStyle.Horizontal, 1, PresetGradientType.EarlySunset) shape.chart.chartArea.format.fill.gradientStops.delete(3) shape.chart.chartArea.format.fill.gradientStops.delete(3) shape.chart.chartArea.format.fill.gradientAngle = 90.0 shape.chart.chartArea.format.fill.gradientStops.get(0).color.objectThemeColor = ThemeColor.Light1 shape.chart.chartArea.format.fill.gradientStops.get(0).color.brightness = 0.0 shape.chart.chartArea.format.fill.gradientStops.get(0).position = 0.0 shape.chart.chartArea.format.fill.gradientStops.get(1).color.objectThemeColor = ThemeColor.Light1 shape.chart.chartArea.format.fill.gradientStops.get(1).color.brightness = -0.15 shape.chart.chartArea.format.fill.gradientStops.get(1).position = 0.68 shape.chart.chartArea.format.fill.gradientStops.get(2).color.objectThemeColor = ThemeColor.Light1 shape.chart.chartArea.format.fill.gradientStops.get(2).color.brightness = 0.0 shape.chart.chartArea.format.fill.gradientStops.get(2).position = 1.0 //Create customize theme. val theme = Theme("test") theme.themeColorScheme.get(ThemeColor.Dark1).rgb = Color.FromArgb(0, 0, 0) theme.themeColorScheme.get(ThemeColor.Light1).rgb = Color.FromArgb(255, 255, 255) theme.themeColorScheme.get(ThemeColor.Dark2).rgb = Color.FromArgb(96, 89, 88) theme.themeColorScheme.get(ThemeColor.Light2).rgb = Color.FromArgb(241, 246, 246) theme.themeColorScheme.get(ThemeColor.Accent1).rgb = Color.FromArgb(63, 94, 101) theme.themeColorScheme.get(ThemeColor.Accent2).rgb = Color.FromArgb(224, 170, 83) theme.themeColorScheme.get(ThemeColor.Accent3).rgb = Color.FromArgb(179, 29, 66) theme.themeColorScheme.get(ThemeColor.Accent4).rgb = Color.FromArgb(162, 67, 162) theme.themeColorScheme.get(ThemeColor.Accent5).rgb = Color.FromArgb(120, 59, 101) theme.themeColorScheme.get(ThemeColor.Accent6).rgb = Color.FromArgb(55, 120, 169) theme.themeColorScheme.get(ThemeColor.Hyperlink).rgb = Color.FromArgb(71, 166, 181) theme.themeColorScheme.get(ThemeColor.FollowedHyperlink).rgb = Color.FromArgb(120, 59, 101) theme.themeFontScheme.major.get(FontLanguageIndex.Latin).name = "Constantia" theme.themeFontScheme.minor.get(FontLanguageIndex.Latin).name = "Helvetica" //Apply the above custom theme. workbook.theme = theme //Set active cell. worksheet.getRange("B43").activate() //save to an excel file workbook.save("CostAnalysisWithParetoChart.xlsx")