New Features

Password-protected (encrypted) workbooks

• Workbook_SaveWithPassword — save a workbook as an AES-encrypted, password-to-open file. Excel requires the password before showing any content. This is different from Workbook_Protect, which only locks the workbook structure while the data stays readable.
• Workbook_Open and Workbook_Open_BinaryData now accept an optional Password to open encrypted files. Leave it blank to open normal files exactly as before. A missing or incorrect password gives a clear error.

Upgrade notes

Workbook_Open and Workbook_Open_BinaryData gained a new optional Password input. After upgrading, refresh the Advanced Excel reference in your app and republish. Existing calls keep working unchanged (leave Password blank) — the reference refresh is the only required step.

New feature:

Worksheet_AddPivotTable creates a pivot table from a range of source data.

• Place fields on rows, columns, values, or filters via the new PivotField structure (FieldName, Axis, Function).
• Value fields support Sum, Count, Average, Max, Min, CountNums, Product, StdDev, StdDevP, Var and VarP.
• Row and column grand totals are configurable.
• The pivot recalculates automatically when the workbook is opened in Excel.

Added the Chart_Create action which allows for creation of charts in a specific worksheet. 

Define the ranges of cells from which you want to feed your series into the chart and choose from a number of types by passing in the name of the type.

Check out an example here:

https://www.outsystems.com/forums/discussion/36870/advanced-excel-chart-creation-example/

A new action has been added to the "Advanced Excel" extension called “Workbook_AddOsDbSheet”.

This action will need a few input parameters, like the WorkbookInObj, the WorksheetName, the OsDbDataSet, the RowStart, the ColumStart and IncludingHeader.

When this action is used for the first time in a row, the WorkbookInObj  should be nullObject().

In that case the action will automatically open a workbook object, which reference will be returned via WorkbookOutObj afterwards.

The next time the action is used the WorkbookInObj will be populated with the previous WorkbookOutObj reference. The use of the other fields is clear, I guess.

• Fixed a bug in Cell_WriteByIndex function;
• Added function Worksheet_GetName that returns, as text, the name of the worksheet provided;
• Added function Worksheet_DeleteByIndex to delete sheets by index;
• Added function Worksheet_DeleteByName to delete sheets by name;
• Added function WorkBook_AddSheet to add new sheets by name;
• Added function Cell_MergeRange to merge multiple cells.

Upgrade to version 9