// 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");