// 新規ワークブックの作成 var workbook = new GrapeCity.Documents.Excel.Workbook(); // 反復計算のオプションを設定 workbook.Options.Formulas.EnableIterativeCalculation = true; workbook.Options.Formulas.MaximumIterations = 20; var worksheet = workbook.Worksheets[0]; // 値と数式を設定 worksheet.Range["B2"].Value = "初期投資金額"; worksheet.Range["C2"].Value = 10000000; worksheet.Range["B3"].Value = "利息"; worksheet.Range["C3"].Value = 0.0012; worksheet.Range["B5"].Value = "月"; worksheet.Range["C5"].Value = "合計金額"; 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 = "初期投資金額"; worksheet.Range["G2"].Value = 10000000; worksheet.Range["F3"].Value = "利息"; worksheet.Range["G3"].Value = 0.0012; worksheet.Range["F4"].Value = "21か月目の合計金額"; worksheet.Range["G4"].Formula = "=G2*(1+G3)"; // この数式を設定することにより、循環参照になります worksheet.Range["G2"].Formula = "=G4"; // スタイルを設定 worksheet.Range["A:A,D:E"].ColumnWidthInPixel = 40; worksheet.Range["B:C,F:G"].ColumnWidthInPixel = 150; worksheet.Range["C2,G2,G4,C6:C26"].NumberFormat = "¥#,##0"; 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); // 印刷設定 worksheet.PageSetup.PrintHeadings = true; // xlsx ファイルに保存 workbook.Save("IterativeCalculation.xlsx");
' 新規ワークブックの作成 Dim workbook As New Workbook ' 反復計算のオプションを設定 workbook.Options.Formulas.EnableIterativeCalculation = True workbook.Options.Formulas.MaximumIterations = 20 Dim worksheet = workbook.Worksheets(0) ' 値と数式を設定 With worksheet.Range !B2.Value = "初期投資金額" !C2.Value = 10000000 !B3.Value = "利息" !C3.Value = 0.0012 !B5.Value = "月" !C5.Value = "合計金額" .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 = "初期投資金額" !G2.Value = 10000000 !F3.Value = "利息" !G3.Value = 0.0012 !F4.Value = "21か月目の合計金額" !G4.Formula = "=G2*(1+G3)" ' この数式を設定することにより、循環参照になります !G2.Formula = "=G4" ' スタイルを設定 .Item("A:A,D:E").ColumnWidthInPixel = 40 .Item("B:C,F:G").ColumnWidthInPixel = 150 .Item("C2,G2,G4,C6:C26").NumberFormat = "¥#,##0" .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) ' 印刷設定 worksheet.PageSetup.PrintHeadings = True ' xlsx ファイルに保存 workbook.Save("IterativeCalculation.xlsx")