// Create a new workbook Workbook workbook = new Workbook(); //-----------------------------Set Value------------------------------ Object[][] sourceData = new Object[][]{ {"ITEM", "AMOUNT"}, {"Income 1", 2500}, {"Income 2", 1000}, {"Income 3", 250}, {"Other", 250}, }; Object[][] sourceData1 = new Object[][]{ {"ITEM", "AMOUNT"}, {"Rent/mortgage", 800}, {"Electricity", 120}, {"Gas", 50}, {"Cell phone", 45}, {"Groceries", 500}, {"Car payment", 273}, {"Auto expenses", 120}, {"Student loans", 50}, {"Credit cards", 100}, {"Auto insurance", 78}, {"Personal care", 50}, {"Entertainment", 100}, {"Miscellaneous", 50}, }; IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("B3:C7").setValue(sourceData); worksheet.getRange("B10:C23").setValue(sourceData1); worksheet.setName("Tables"); worksheet.getRange("B2:C2").merge(); worksheet.getRange("B2").setValue("MONTHLY INCOME"); worksheet.getRange("B9:C9").merge(); worksheet.getRange("B9").setValue("MONTHLY EXPENSES"); worksheet.getRange("E2:G2").merge(); worksheet.getRange("E2").setValue("PERCENTAGE OF INCOME SPENT"); worksheet.getRange("E5:G5").merge(); worksheet.getRange("E5").setValue("SUMMARY"); worksheet.getRange("E3:F3").merge(); worksheet.getRange("E9").setValue("BALANCE"); worksheet.getRange("E6").setValue("Total Monthly Income"); worksheet.getRange("E7").setValue("Total Monthly Expenses"); //--------------------------------Set Height & Width-------------------------------- worksheet.setStandardHeight(26.25); worksheet.setStandardWidth(8.43); worksheet.getRange("2:24").setRowHeight(27); worksheet.getRange("A:A").setColumnWidth(2.855); worksheet.getRange("B:B").setColumnWidth(33.285); worksheet.getRange("C:C").setColumnWidth(25.57); worksheet.getRange("D:D").setColumnWidth(1); worksheet.getRange("E:F").setColumnWidth(25.57); worksheet.getRange("G:G").setColumnWidth(14.285); //------------------------------Set Table-------------------------------------- // Create the first table to show Income ITable incomeTable = worksheet.getTables().add(worksheet.getRange("B3:C7"), true); incomeTable.setName("tblIncome"); incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4")); // Create the second table to show Expenses ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B10:C23"), true); expensesTable.setName("tblExpenses"); expensesTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4")); //------------------------------Set Formulas----------------------------------- worksheet.getNames().add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])"); worksheet.getNames().add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])"); worksheet.getRange("E3").setFormula("=TotalMonthlyExpenses"); worksheet.getRange("G3").setFormula("=TotalMonthlyExpenses/TotalMonthlyIncome"); worksheet.getRange("G6").setFormula("=TotalMonthlyIncome"); worksheet.getRange("G7").setFormula("=TotalMonthlyExpenses"); worksheet.getRange("G9").setFormula("=TotalMonthlyIncome-TotalMonthlyExpenses"); //----------------------------Set Styles------------------------- IStyle currencyStyle = workbook.getStyles().get("Currency"); currencyStyle.setIncludeAlignment(true); currencyStyle.setHorizontalAlignment(HorizontalAlignment.Left); currencyStyle.setVerticalAlignment(VerticalAlignment.Bottom); currencyStyle.setNumberFormat("$#,##0.00"); IStyle heading1Style = workbook.getStyles().get("Heading 1"); heading1Style.setIncludeAlignment(true); heading1Style.setHorizontalAlignment(HorizontalAlignment.Center); heading1Style.setVerticalAlignment(VerticalAlignment.Center); heading1Style.setIncludeFont(true); heading1Style.getFont().setName("Century Gothic"); heading1Style.getFont().setBold(true); heading1Style.getFont().setSize(11); heading1Style.getFont().setColor(Color.GetWhite()); heading1Style.setIncludeBorder(false); heading1Style.setIncludePatterns(true); heading1Style.getInterior().setColor(Color.FromArgb(255, 32, 61, 64)); IStyle percentStyle = workbook.getStyles().get("Percent"); percentStyle.setIncludeAlignment(true); percentStyle.setHorizontalAlignment(HorizontalAlignment.Center); percentStyle.setIncludeFont(true); percentStyle.getFont().setColor(Color.FromArgb(255, 32, 61, 64)); percentStyle.getFont().setName("Century Gothic"); percentStyle.getFont().setBold(true); percentStyle.getFont().setSize(14); worksheet.getSheetView().setDisplayGridlines(false); worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").setStyle(currencyStyle); worksheet.getRange("B2, B9, E2, E5").setStyle(heading1Style); worksheet.getRange("G3").setStyle(percentStyle); worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium); worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(255,32, 61, 64)); worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium); worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(255,32,61,64)); worksheet.getRange("E9:G9").getInterior().setColor(Color.FromArgb(255,32,61,64)); worksheet.getRange("E9:G9").setHorizontalAlignment(HorizontalAlignment.Left); worksheet.getRange("E9:G9").setVerticalAlignment(VerticalAlignment.Center); worksheet.getRange("E9:G9").getFont().setName("Century Gothic"); worksheet.getRange("E9:G9").getFont().setBold(true); worksheet.getRange("E9:G9").getFont().setSize(11); worksheet.getRange("E9:G9").getFont().setColor(Color.GetWhite()); worksheet.getRange("E3:F3").getBorders().setColor(Color.FromArgb(255,32,61,64)); //----------------------------Set Conditional Format------------------------- IDataBar dataBar = worksheet.getRange("E3").getFormatConditions().addDatabar(); dataBar.getMinPoint().setType(ConditionValueTypes.Number); dataBar.getMinPoint().setValue(1); dataBar.getMaxPoint().setType(ConditionValueTypes.Number); dataBar.getMaxPoint().setValue("=TotalMonthlyIncome"); dataBar.setBarFillType(DataBarFillType.Gradient); dataBar.getBarColor().setColor(Color.GetRed()); dataBar.setShowValue(false); //--------------------------------Create pivot table-------------------------------- IWorksheet worksheet2 = workbook.getWorksheets().add(); worksheet2.setName("Pivot Table"); sourceData = new Object[][]{ {"Order ID", "Product", "Category", "Amount", "Date", "Country"}, {1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2022, 9, 6), "United States"}, {2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2022, 8, 7), "United States"}, {3, "Banana", "Fruit", 617, new GregorianCalendar(2022, 10, 18), "United States"}, {4, "Banana", "Fruit", 8384, new GregorianCalendar(2022, 11, 10), "Canada"}, {5, "Beans", "Vegetables", 2626, new GregorianCalendar(2022, 10, 10), "Germany" }, {6, "Orange", "Fruit", 3610, new GregorianCalendar(2022, 11, 11), "United States"}, {7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2022, 10, 11), "Australia"}, {8, "Banana", "Fruit", 6906, new GregorianCalendar(2022, 10, 16), "New Zealand"}, {9, "Apple", "Fruit", 2417, new GregorianCalendar(2022,11,16), "France"}, {10, "Apple", "Fruit", 7431, new GregorianCalendar(2022, 11, 16), "Canada"}, {11, "Banana", "Fruit", 8250, new GregorianCalendar(2022, 10, 16), "Germany"}, {12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2022, 10, 18), "United States"}, {13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2022, 11, 20), "Germany"}, {14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2022, 9, 24), "Canada"}, {15, "Apple", "Fruit", 6946, new GregorianCalendar(2022, 11, 24), "France"}, }; worksheet2.getRange("A1:F16").setValue(sourceData); worksheet2.getRange("A:F").setColumnWidth(15); IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet2.getRange("A1:F16")); IPivotTable pivotTable = worksheet2.getPivotTables().add(pivotCache, worksheet2.getRange("H7"), "pivotTable1"); //--------------------------------Configure pivot table's fields-------------------------------- IPivotField fieldCategory = pivotTable.getPivotFields().get("Category"); fieldCategory.setOrientation(PivotFieldOrientation.RowField); IPivotField fieldProduct = pivotTable.getPivotFields().get("Product"); fieldProduct.setOrientation(PivotFieldOrientation.ColumnField); IPivotField fieldAmount = pivotTable.getPivotFields().get("Amount"); fieldAmount.setOrientation(PivotFieldOrientation.DataField); fieldAmount.setNumberFormat("$#,##0"); IPivotField fieldCountry = pivotTable.getPivotFields().get("Country"); fieldCountry.setOrientation(PivotFieldOrientation.PageField); //--------------------------------Add chart-------------------------------- IWorksheet worksheet3 = workbook.getWorksheets().add(); worksheet3.setName("Chart"); IShape shape = worksheet3.getShapes().addChart(ChartType.ColumnClustered, 300, 10, 300, 300); shape.getChart().getChartTitle().setText("Sales Increases Over Previous Quarter"); worksheet3.getRange("A1:D6").setValue(new Object[][]{ {null, "Q1", "Q2", "Q3"}, {"Belgium", 10, 25, 25}, {"France", -51, -36, 27}, {"Greece", 52, -85, -30}, {"Italy", 22, 65, 65}, {"UK", 23, 69, 69}, }); shape.getChart().getSeriesCollection().add(worksheet3.getRange("A1:D6"), RowCol.Columns, true, true); worksheet3.getRange("B1:D1").setHorizontalAlignment(HorizontalAlignment.Right); worksheet3.getRange("B1:D1").getFont().setBold(true); worksheet3.getRange("B2:D6").setNumberFormat("€#,##0"); IAxis valueAxis = shape.getChart().getAxes().item(AxisType.Value); valueAxis.getTickLabels().setNumberFormat("€#,##0");
// Create a new workbook var workbook = Workbook() //-----------------------------Set Value------------------------------ var sourceData = arrayOf( arrayOf("ITEM", "AMOUNT"), arrayOf("Income 1", 2500), arrayOf("Income 2", 1000), arrayOf("Income 3", 250), arrayOf("Other", 250) ) val sourceData1 = arrayOf( arrayOf("ITEM", "AMOUNT"), arrayOf("Rent/mortgage", 800), arrayOf("Electricity", 120), arrayOf("Gas", 50), arrayOf("Cell phone", 45), arrayOf("Groceries", 500), arrayOf("Car payment", 273), arrayOf("Auto expenses", 120), arrayOf("Student loans", 50), arrayOf("Credit cards", 100), arrayOf("Auto insurance", 78), arrayOf("Personal care", 50), arrayOf("Entertainment", 100), arrayOf("Miscellaneous", 50) ) val worksheet: IWorksheet = workbook.getWorksheets().get(0) worksheet.getRange("B3:C7").value = sourceData worksheet.getRange("B10:C23").value = sourceData1 worksheet.name = "Tables" worksheet.getRange("B2:C2").merge() worksheet.getRange("B2").value = "MONTHLY INCOME" worksheet.getRange("B9:C9").merge() worksheet.getRange("B9").value = "MONTHLY EXPENSES" worksheet.getRange("E2:G2").merge() worksheet.getRange("E2").value = "PERCENTAGE OF INCOME SPENT" worksheet.getRange("E5:G5").merge() worksheet.getRange("E5").value = "SUMMARY" worksheet.getRange("E3:F3").merge() worksheet.getRange("E9").value = "BALANCE" worksheet.getRange("E6").value = "Total Monthly Income" worksheet.getRange("E7").value = "Total Monthly Expenses" //--------------------------------Set Height & Width-------------------------------- worksheet.standardHeight = 26.25 worksheet.standardWidth = 8.43 worksheet.getRange("2:24").rowHeight = 27.0 worksheet.getRange("A:A").columnWidth = 2.855 worksheet.getRange("B:B").columnWidth = 33.285 worksheet.getRange("C:C").columnWidth = 25.57 worksheet.getRange("D:D").columnWidth = 1.0 worksheet.getRange("E:F").columnWidth = 25.57 worksheet.getRange("G:G").columnWidth = 14.285 //------------------------------Set Table-------------------------------------- // Create the first table to show Income val incomeTable: ITable = worksheet.tables.add(worksheet.getRange("B3:C7"), true) incomeTable.name = "tblIncome" incomeTable.tableStyle = workbook.tableStyles.get("TableStyleMedium4") // Create the second table to show Expenses val expensesTable: ITable = worksheet.tables.add(worksheet.getRange("B10:C23"), true) expensesTable.name = "tblExpenses" expensesTable.tableStyle = workbook.tableStyles.get("TableStyleMedium4") //------------------------------Set Formulas----------------------------------- worksheet.names.add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])") worksheet.names.add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])") worksheet.getRange("E3").formula = "=TotalMonthlyExpenses" worksheet.getRange("G3").formula = "=TotalMonthlyExpenses/TotalMonthlyIncome" worksheet.getRange("G6").formula = "=TotalMonthlyIncome" worksheet.getRange("G7").formula = "=TotalMonthlyExpenses" worksheet.getRange("G9").formula = "=TotalMonthlyIncome-TotalMonthlyExpenses" //----------------------------Set Styles------------------------- val currencyStyle: IStyle = workbook.styles.get("Currency") currencyStyle.includeAlignment = true currencyStyle.horizontalAlignment = HorizontalAlignment.Left currencyStyle.verticalAlignment = VerticalAlignment.Bottom currencyStyle.numberFormat = "$#,##0.00" val heading1Style: IStyle = workbook.styles.get("Heading 1") heading1Style.includeAlignment = true heading1Style.horizontalAlignment = HorizontalAlignment.Center heading1Style.verticalAlignment = VerticalAlignment.Center heading1Style.includeFont = true heading1Style.font.name = "Century Gothic" heading1Style.font.bold = true heading1Style.font.size = 11.0 heading1Style.font.color = Color.GetWhite() heading1Style.includeBorder = false heading1Style.includePatterns = true heading1Style.interior.color = Color.FromArgb(255, 32, 61, 64) val percentStyle: IStyle = workbook.styles.get("Percent") percentStyle.includeAlignment = true percentStyle.horizontalAlignment = HorizontalAlignment.Center percentStyle.includeFont = true percentStyle.font.color = Color.FromArgb(255, 32, 61, 64) percentStyle.font.name = "Century Gothic" percentStyle.font.bold = true percentStyle.font.size = 14.0 worksheet.getSheetView().setDisplayGridlines(false) worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").style = currencyStyle worksheet.getRange("B2, B9, E2, E5").style = heading1Style worksheet.getRange("G3").style = percentStyle worksheet.getRange("E6:G6").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Medium worksheet.getRange("E6:G6").borders.get(BordersIndex.EdgeBottom).color = Color.FromArgb(255, 32, 61, 64) worksheet.getRange("E7:G7").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Medium worksheet.getRange("E7:G7").borders.get(BordersIndex.EdgeBottom).color = Color.FromArgb(255, 32, 61, 64) worksheet.getRange("E9:G9").interior.color = Color.FromArgb(255, 32, 61, 64) worksheet.getRange("E9:G9").horizontalAlignment = HorizontalAlignment.Left worksheet.getRange("E9:G9").verticalAlignment = VerticalAlignment.Center worksheet.getRange("E9:G9").font.name = "Century Gothic" worksheet.getRange("E9:G9").font.bold = true worksheet.getRange("E9:G9").font.size = 11.0 worksheet.getRange("E9:G9").font.color = Color.GetWhite() worksheet.getRange("E3:F3").borders.color = Color.FromArgb(255, 32, 61, 64) //----------------------------Set Conditional Format------------------------- val dataBar: IDataBar = worksheet.getRange("E3").formatConditions.addDatabar() dataBar.minPoint.type = ConditionValueTypes.Number dataBar.minPoint.value = 1 dataBar.maxPoint.type = ConditionValueTypes.Number dataBar.maxPoint.value = "=TotalMonthlyIncome" dataBar.barFillType = DataBarFillType.Gradient dataBar.barColor.color = Color.GetRed() dataBar.showValue = false //--------------------------------Create pivot table-------------------------------- val worksheet2: IWorksheet = workbook.worksheets.add() worksheet2.name = "Pivot Table" sourceData = arrayOf( arrayOf("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "Carrots", "Vegetables", 4270, GregorianCalendar(2022, 9, 6), "United States"), arrayOf(2, "Broccoli", "Vegetables", 8239, GregorianCalendar(2022, 8, 7), "United States"), arrayOf(3, "Banana", "Fruit", 617, GregorianCalendar(2022, 10, 18), "United States"), arrayOf(4, "Banana", "Fruit", 8384, GregorianCalendar(2022, 11, 10), "Canada"), arrayOf(5, "Beans", "Vegetables", 2626, GregorianCalendar(2022, 10, 10), "Germany"), arrayOf(6, "Orange", "Fruit", 3610, GregorianCalendar(2022, 11, 11), "United States"), arrayOf(7, "Broccoli", "Vegetables", 9062, GregorianCalendar(2022, 10, 11), "Australia"), arrayOf(8, "Banana", "Fruit", 6906, GregorianCalendar(2022, 10, 16), "New Zealand"), arrayOf(9, "Apple", "Fruit", 2417, GregorianCalendar(2022, 11, 16), "France"), arrayOf(10, "Apple", "Fruit", 7431, GregorianCalendar(2022, 11, 16), "Canada"), arrayOf(11, "Banana", "Fruit", 8250, GregorianCalendar(2022, 10, 16), "Germany"), arrayOf(12, "Broccoli", "Vegetables", 7012, GregorianCalendar(2022, 10, 18), "United States"), arrayOf(13, "Carrots", "Vegetables", 1903, GregorianCalendar(2022, 11, 20), "Germany"), arrayOf(14, "Broccoli", "Vegetables", 2824, GregorianCalendar(2022, 9, 24), "Canada"), arrayOf(15, "Apple", "Fruit", 6946, GregorianCalendar(2022, 11, 24), "France") ) worksheet2.getRange("A1:F16").value = sourceData worksheet2.getRange("A:F").columnWidth = 15.0 val pivotCache: IPivotCache = workbook.pivotCaches.create(worksheet2.getRange("A1:F16")) val pivotTable: IPivotTable = worksheet2.pivotTables.add(pivotCache, worksheet2.getRange("H7"), "pivotTable1") //--------------------------------Configure pivot table's fields-------------------------------- val fieldCategory: IPivotField = pivotTable.pivotFields.get("Category") fieldCategory.orientation = PivotFieldOrientation.RowField val fieldProduct: IPivotField = pivotTable.pivotFields.get("Product") fieldProduct.orientation = PivotFieldOrientation.ColumnField val fieldAmount: IPivotField = pivotTable.pivotFields.get("Amount") fieldAmount.orientation = PivotFieldOrientation.DataField fieldAmount.numberFormat = "$#,##0" val fieldCountry: IPivotField = pivotTable.pivotFields.get("Country") fieldCountry.orientation = PivotFieldOrientation.PageField //--------------------------------Add chart-------------------------------- val worksheet3: IWorksheet = workbook.worksheets.add() worksheet3.name = "Chart" val shape: IShape = worksheet3.shapes.addChart(ChartType.ColumnClustered, 300.0, 10.0, 300.0, 300.0) shape.chart.chartTitle.text = "Sales Increases Over Previous Quarter" worksheet3.getRange("A1:D6").value = arrayOf( arrayOf(null, "Q1", "Q2", "Q3"), arrayOf("Belgium", 10, 25, 25), arrayOf("France", -51, -36, 27), arrayOf("Greece", 52, -85, -30), arrayOf("Italy", 22, 65, 65), arrayOf("UK", 23, 69, 69) ) shape.chart.seriesCollection.add(worksheet3.getRange("A1:D6"), RowCol.Columns, true, true) worksheet3.getRange("B1:D1").horizontalAlignment = HorizontalAlignment.Right worksheet3.getRange("B1:D1").font.bold = true worksheet3.getRange("B2:D6").numberFormat = "€#,##0" val valueAxis: IAxis = shape.chart.axes.item(AxisType.Value) valueAxis.tickLabels.numberFormat = "€#,##0"