// Create a new workbook Workbook workbook = new Workbook(); workbook.getOptions().getFormulas().setEnableIterativeCalculation(true); workbook.getOptions().getFormulas().setMaximumIterations(20); IWorksheet worksheet = workbook.getWorksheets().get(0); // set values and formulas worksheet.getRange("B2").setValue("Initial Cash"); worksheet.getRange("C2").setValue(10000); worksheet.getRange("B3").setValue("Interest"); worksheet.getRange("C3").setValue(0.0125); worksheet.getRange("B5").setValue("Month"); worksheet.getRange("C5").setValue("Total Cash"); worksheet.getRange("B6:B26").setValue(new double[] {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21 }); worksheet.getRange("C6").setFormula("=C2*(1+$C$3)"); worksheet.getRange("C7:C26").setFormula("=C6*(1+$C$3)"); worksheet.getRange("F2").setValue("Initial Cash"); worksheet.getRange("G2").setValue(10000); worksheet.getRange("F3").setValue("Interest"); worksheet.getRange("G3").setValue(0.0125); worksheet.getRange("F4").setValue("Total Cash at 21th Month"); worksheet.getRange("G4").setFormula("=G2*(1+G3)"); // this formula will generate circle reference. worksheet.getRange("G2").setFormula("=G4"); // set styles worksheet.getRange("A:A,D:E").setColumnWidthInPixel(40); worksheet.getRange("B:C,F:G").setColumnWidthInPixel(100); worksheet.getRange("C2,G2,G4,C6:C26").setNumberFormat("$#,##0.00"); worksheet.getRange("C3,G3").setNumberFormat("0.00%"); worksheet.getRange("B2:C3,F2:G4,B5:C26").getBorders().setLineStyle(BorderLineStyle.Thin); worksheet.getRange("C2:C3,G2:G4").getInterior().setThemeColor(ThemeColor.Accent1); worksheet.getRange("C2:C3,G2:G4").getInterior().setTintAndShade(0.8); worksheet.getTables().add(worksheet.getRange("B5:C26"), true); // set print settings worksheet.getPageSetup().setPrintHeadings(true); // Save to an excel file workbook.save("IterativeCalculation.xlsx");
// Create a new workbook var workbook = Workbook() workbook.options.formulas.enableIterativeCalculation = true workbook.options.formulas.maximumIterations = 20 val worksheet = workbook.worksheets[0] // set values and formulas worksheet.getRange("B2").value = "Initial Cash" worksheet.getRange("C2").value = 10000 worksheet.getRange("B3").value = "Interest" worksheet.getRange("C3").value = 0.0125 worksheet.getRange("B5").value = "Month" worksheet.getRange("C5").value = "Total Cash" worksheet.getRange("B6:B26").value = doubleArrayOf(1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0, 20.0, 21.0) worksheet.getRange("C6").formula = "=C2*(1+\$C$3)" worksheet.getRange("C7:C26").formula = "=C6*(1+\$C$3)" worksheet.getRange("F2").value = "Initial Cash" worksheet.getRange("G2").value = 10000 worksheet.getRange("F3").value = "Interest" worksheet.getRange("G3").value = 0.0125 worksheet.getRange("F4").value = "Total Cash at 21th Month" worksheet.getRange("G4").formula = "=G2*(1+G3)" // this formula will generate circle reference. worksheet.getRange("G2").formula = "=G4" // set styles worksheet.getRange("A:A,D:E").columnWidthInPixel = 40.0 worksheet.getRange("B:C,F:G").columnWidthInPixel = 100.0 worksheet.getRange("C2,G2,G4,C6:C26").numberFormat = "$#,##0.00" worksheet.getRange("C3,G3").numberFormat = "0.00%" worksheet.getRange("B2:C3,F2:G4,B5:C26").borders.lineStyle = BorderLineStyle.Thin worksheet.getRange("C2:C3,G2:G4").interior.themeColor = ThemeColor.Accent1 worksheet.getRange("C2:C3,G2:G4").interior.tintAndShade = 0.8 worksheet.tables.add(worksheet.getRange("B5:C26"), true) // set print settings worksheet.pageSetup.printHeadings = true // Save to an excel file workbook.save("IterativeCalculation.xlsx")