Overview

Advanced Excel integration. This enables writing of binary multi-sheet excel-files without the use of an office installation. Feature List: - Write of data-sets or single cells - Reading of each cell, interpreting different data types - Open an existing excel workbook - Create new workbooks - Manage worksheets - Manage style - Set filters - Convert list into excel object - Convert excel object into list

Documentation

Create a basic Excel file

Use the Workbook_Create action to create a new Excel workbook.

You will need to provide the following input parameters:

• Workbook: an Advanced_Excel.AdvancedExcel object where the Excel file will be stored

Open an existing Excel file

Use the Workbook_Open action to open an existing workbook.

You will need to provide the following input parameters:

• FileName: This can be the path to a physical local file (probably only relevant in on-premise hosting) or a URL to a file. Set this to an empty string if you want to use the binary data option defined below.
• Workbook: an Advanced_Excel.AdvancedExcel object where the Excel file will be stored

Add a new worksheet to an Excel file

To add one or more worksheets to the Excel file, use the Workbook_AddSheet action. 

You will need to provide the following input parameters:

• Workbook: : the Advanced_Excel.AdvancedExcel object that you want to add the worksheet to.
• SheetName: name of the worksheet.

Get Worksheet Name/Index

To get a specific worksheet Name/Index to use with one of the Worksheet_* actions, you will use the Worksheet_GetIndexByName / Worksheet_GetNameByIndex action.

You will need to provide the following input parameters:

• Workbook: the Advanced_Excel.AdvancedExcel object wherein the worksheet exists.
• Index: The index of the worksheet to find. Indexes start at 0.
• SheetName: The name of the worksheet to find.

The output parameter(s) for the action is:

• The index of the worksheet found (Indexes start at 0) or The name of the worksheet found.

Writing data to cells in a Worksheet

There are two ways to write data to the Worksheet. Both are described below.

Adding a list of data

When you want to add multiple rows from a dataset, you will use the Cell_WriteRange action. 

Scenarios where you would use this action:

• Writing

You will need to provide the following input parameters:

• Workbook: the Advanced_Excel.AdvancedExcel object that you want to add the worksheet to.
• SheetName: name of the worksheet you want to work with.
• DataSet: The data you want to populate in the sheet
  NOTE: The dataset should be a List of Advanced_Excel.Data type.

• CellFormat: Advanced_Excel.CellFormat object for the target cells.

Adding a single value or formula to a cell

To write a single value to worksheet, you will use the Cell_Write action. 

Some scenarios where you would use this action:

• You have discrete values to write to a cell
• You have a dataset with multiple rows and columns that you want to apply different formatting to

You will need to provide the following input parameters:

• Workbook: the Advanced_Excel.AdvancedExcel Excel object that you want to add the worksheet to.
• SheetName: name of the worksheet you want to work with.
• CellRow: Row number of the cell to write to. Required if CellName not set.

• CellColumn: Column number of the cell to write to. Required if CellName not set.
• CellValue: The value to write to the cell or the formula
• CellType: The type can be one of the following:
  • text (default if nothing specified)
  • datetime
  • integer
  • decimal
  • boolean
  • formula
• CellFormat: Advanced_Excel.CellFormat object for the target cell

Read the value of a Cell

Use the Cell_Read action to read the value of any cell.

You will need to provide the following input parameters:

• Workbook: the Advanced_Excel.AdvancedExcel object that you want to add the worksheet to.
• SheetName: name of the worksheet you want to work with.
• CellRow: Row number of the cell to read from. Required if CellName not set.

• CellColumn: Column number of the cell to read from. Required if CellName not set.

• CellValue: The value in the cell, as text.

Delete a Worksheet

To delete a worksheet from your workbook, use the Worksheet_Delete action.

You will need to provide the following input parameters:

• Workbook: The workbook from which you want to delete the worksheet.
• Index: The index of the worksheet to delete. Set to 0 if using the worksheet name to delete.
• Name: The name of the worksheet to delete. Set to an empty string if using the index to delete.

Rename a Worksheet

To rename a worksheet, make use of the Worksheet_Rename action.

You will need to provide the following input parameters:

• Workbook: the Advanced_Excel.AdvancedExcel object that you want to add the worksheet to.
• Index: index of the worksheet you want to delete.
• NewName: The new name for the worksheet.