//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); // set options for iterative calculation workbook.Options.Formulas.EnableIterativeCalculation = true; workbook.Options.Formulas.MaximumIterations = 20; var worksheet = workbook.Worksheets[0]; // set values and formulas worksheet.Range["B2"].Value = "Initial Cash"; worksheet.Range["C2"].Value = 10000; worksheet.Range["B3"].Value = "Interest"; worksheet.Range["C3"].Value = 0.0125; worksheet.Range["B5"].Value = "Month"; worksheet.Range["C5"].Value = "Total Cash"; worksheet.Range["B6:B26"].Value = new double[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21 }; worksheet.Range["C6"].Formula = "=C2*(1+$C$3)"; worksheet.Range["C7:C26"].Formula = "=C6*(1+$C$3)"; worksheet.Range["F2"].Value = "Initial Cash"; worksheet.Range["G2"].Value = 10000; worksheet.Range["F3"].Value = "Interest"; worksheet.Range["G3"].Value = 0.0125; worksheet.Range["F4"].Value = "Total Cash at 21th Month"; worksheet.Range["G4"].Formula = "=G2*(1+G3)"; // this formula will generate circle reference. worksheet.Range["G2"].Formula = "=G4"; // set styles worksheet.Range["A:A,D:E"].ColumnWidthInPixel = 40; worksheet.Range["B:C,F:G"].ColumnWidthInPixel = 100; worksheet.Range["C2,G2,G4,C6:C26"].NumberFormat = "$#,##0.00"; worksheet.Range["C3,G3"].NumberFormat = "0.00%"; worksheet.Range["B2:C3,F2:G4,B5:C26"].Borders.LineStyle = BorderLineStyle.Thin; worksheet.Range["C2:C3,G2:G4"].Interior.ThemeColor = ThemeColor.Accent1; worksheet.Range["C2:C3,G2:G4"].Interior.TintAndShade = 0.8; worksheet.Tables.Add(worksheet.Range["B5:C26"], true); // set print settings worksheet.PageSetup.PrintHeadings = true; // Save to an excel file workbook.Save("IterativeCalculation.xlsx");
' Create a new Workbook Dim workbook As New Workbook ' set options for iterative calculation workbook.Options.Formulas.EnableIterativeCalculation = True workbook.Options.Formulas.MaximumIterations = 20 Dim worksheet = workbook.Worksheets(0) ' set values and formulas With worksheet.Range !B2.Value = "Initial Cash" !C2.Value = 10000 !B3.Value = "Interest" !C3.Value = 0.0125 !B5.Value = "Month" !C5.Value = "Total Cash" .Item("B6:B26").Value = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21} !C6.Formula = "=C2*(1+$C$3)" .Item("C7:C26").Formula = "=C6*(1+$C$3)" !F2.Value = "Initial Cash" !G2.Value = 10000 !F3.Value = "Interest" !G3.Value = 0.0125 !F4.Value = "Total Cash at 21th Month" !G4.Formula = "=G2*(1+G3)" ' this formula will generate circle reference. !G2.Formula = "=G4" ' set styles .Item("A:A,D:E").ColumnWidthInPixel = 40 .Item("B:C,F:G").ColumnWidthInPixel = 100 .Item("C2,G2,G4,C6:C26").NumberFormat = "$#,##0.00" .Item("C3,G3").NumberFormat = "0.00%" .Item("B2:C3,F2:G4,B5:C26").Borders.LineStyle = BorderLineStyle.Thin .Item("C2:C3,G2:G4").Interior.ThemeColor = ThemeColor.Accent1 .Item("C2:C3,G2:G4").Interior.TintAndShade = 0.8 End With worksheet.Tables.Add(worksheet.Range("B5:C26"), True) ' set print settings worksheet.PageSetup.PrintHeadings = True ' save to an excel file workbook.Save("IterativeCalculation.xlsx")