("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" ``` Create Tables, Apply Formulas, and Set Row Heights and Column Widths Add the following code to create two tables, Income and Expenses, apply built-in table styles to each, then set custom names TotalMonthlyIncome and TotalMonthlyExpenses, cell formulas, row heights and column widths: Java ```java // 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")); 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"); 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); ``` Kotlin ```kotlin // 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") 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" 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 ``` Apply Styles and Borders Document Solutions for Excel Java can apply changes to range styles directly on each element, or using a built-in named style, or using a custom named style, which can copy one or more built-in styles to initialize then set individual style properties to customize the style, and re-use the style in multiple ranges to optimize memory. Built-in named styles can also be customized – this example shows how to modify the built-in Currency, Heading 1, and Percent styles. The changes will affect all cells in the workbook using those built-in styles, and the changes will be saved with the workbook in the .XLSX: Java ```java 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)); ``` Kotlin ```kotlin 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) ``` Add Conditional Formatting Document Solutions for Excel Java supports all types of conditional formatting rules. This example creates a data bar rule to show the percentage of income spent, without showing a value: Java ```java 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); ``` Kotlin ```kotlin 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 ``` Add pivot table Document Solutions for Excel java supports pivot tables, which enable quick aggregation and subtotals for analysis of complex data. This example creates a new worksheet and creates a new pivot table referencing data in a range on the worksheet: Java ```java 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"); ``` Kotlin ```kotlin 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") ``` To configure the pivot table layout, specify the row field, column field, data field, and page field: Java ```java 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); ``` Kotlin ```kotlin 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 Document Solutions for Excel Java supports many types of charts for visualizing various kinds of data. This example creates a new worksheet, then creates a new chart referencing data in a range on the worksheet: Java ```java 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"); ``` Kotlin ```kotlin 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" ``` Save to Excel After all other changes are complete, save the resulting workbook to a new .XLSX file using the IWorkbook.save method: Java ```java workbook.save("DsExcelFeatures.xlsx"); ``` Kotlin ```kotlin workbook.save("DsExcelFeatures.xlsx") ``` You can download and view the saved DsExcelFeatures.xlsx. If you prefer to download the Tutorial Source Project and run the code yourself."/>