Excel API Library for Java - Sample Browser | Document Solutions | Iterative Calculation

[フレーム] src="bundle.js"> Refer to the following example code to see how to set the options for iterative calculation. In general, Iterative calculation is the repeated calculation of a worksheet until a definite numeric condition is met. It is used when the formula in a cell directly or indirectly refers to its cell in spreadsheets to recalculate the formula until a specific result is found. Note that when Excel opens this file, it will execute the iterative calculation again, which will cause the content you see to be different from what you expect. // 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+$C3ドル)"); worksheet.getRange("C7:C26").setFormula("=C6*(1+$C3ドル)"); 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+\$C3ドル)" worksheet.getRange("C7:C26").formula = "=C6*(1+\$C3ドル)" 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")