Article Details
Id:15154
Product:finPOWER Connect
Type:NEW
Version:3.01.00
Opened:04/01/2018
Closed:12/01/2018
Released:23/02/2018
Job: J020055

ISExcel object; now supports a basic calculation engine

The ISExcelWorkbook and Worksheet objects now supports a calculation engine.

This allows either the entire Workbook or a single Worksheet to be calculated.

With finBL.CreateExcelWorkbook
  ' Add a Worksheet
  .Worksheets.Add("TEST")

  With .Worksheets(0)
    .Cell("A1").SetDecimal(100)
    .Cell("A2").SetDecimal(200)
    .Cell("A3").Formula = "=A1+A2"
    .Cell("A4").Formula = "=A3*2"
    .Cell("A5").Formula = "=SUM(A1:A4)"

    .Cell("A7").Formula = "=A8"
    .Cell("A8").Formula = "=100"
  
    ' Create a circular reference formula
    .Cell("A10").Formula = "=A1+A10"
    .Cell("A11").Formula = "=A10/A1"
  End With
  
  ' Calculate Workbook (note we have a circular reference so set Iterative option to True
  .CalculationOptions.EnableIterativeCalculation=True
  .Calculate()

  ' Save
  .Save("c:\temp\test.xlsx")
  .Save("c:\temp\test.pdf")
End With

One of the benefits of using the Calculation facilities is that a PDF document can be generated within finPOWER Connect, as per the example above.

Note, the Calculation Engine is beta, and may not support all formulas found in Microsoft Excel.