Workbook: The Excel file itself.
Worksheet: The tabs that appear at the bottom of the sheet.
Cell: Each small square of the sheet. Advanced excel also allows you to apply styles like, for example, background colors.
Range: A range of cells and rows. NOTE: A range can consist of a single cell.
Dimension: Worksheet dimension structure. It contains the following dimension attributes of the worksheet:
Data Series: Data series to be used in graphs, the n-th cell of the value range will correspond to the n-th cell of the label range. It has the following attributes:
Color: Represents an ARGB (alpha, red, green, blue) color. The following attributes are available:
CellFormat: Structure to define all the formatting attributes that can apply to a cell. The following attributes are available:
Address: The Address structure used by Excel. It contains the following attributes:
NOTE:
Use the Workbook_Create action to create a new Excel workbook.
You will need to provide the following input parameters:
FirstSheetName: Specifying the name of the first sheet (defaults to “Sheet1”).
You will have access to the following output parameters from the action:
Workbook: An object that will be used later on as the input for some of the other actions.
Example 1: Creating a new workbook and specifying the name of the first sheet to be “Demo1”.
Use the Workbook_Open action to open an existing workbook.
You will need to provide only ONE of 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.
Binary_Data: This is the actual binary contents of a file and can be a file stored as a local resource, or a file uploaded with one of the file upload components/widgets. Set this to NullBinary() if you want to use the file name to load a file.
Example 1: Opening a file stored as a local resource in your espace.
Example 2: Open file from URL.
To download an Excel file that you have created or loaded, use the Workbook_GetBinaryData action to retrieve the binary content of the file from memory. You can then use the Download action to serve the file to the client browser.
Workbook: The workbook you want to get the binary data for.
Example 1: Create a new workbook, get the binary data, and download the file to the client.
Generally, the default mime type defined by OutSystems (“application/octet”) works fine. If you need a different mime type, please consider the following URL:
https://blogs.msdn.microsoft.com/vsofficedeveloper/2008/05/08/office-2007-file-format-mime-types-for-http-content-streaming-2/
NOTE: You cannot use the Download action in a screen action that contains an Ajax Refresh.
When you are done working with an Excel file you created or opened, be sure to use the Workbook_Close action to release any memory consumed by the Excel file you worked with.
Workbook: The workbook you want to close.
NOTE: Once you call this action, the file is unloaded from memory and can no longer be used by any action taking a workbook object as a parameter. If you want to use the file at a later point in your code, store the content in a local variable.
Example 1: Store the binary data in a local variable to be used later on in the code.
Example 2: Download the file from the local variable as opposed to the Workbook_GetBinaryData.BinaryData output parameter.
To add one or more worksheets to the Excel file, use the Workbook_AddSheet action.
Workbook: The workbook that you want to add the worksheet to.
WorksheetName:
Worksheet:
IndexWhereToAdd: The index where to add the new sheet. The default will be the highest sheet index, plus 1.
Example 1: Add a new sheet named “AddFirstIndex” in the first index (1).
Example 2: Add a new sheet named “AddLastIndex” in the last index.
To select a specific worksheet for use with one of the Worksheet_* actions, you will use the Worksheet_Select action.
Workbook: The workbook wherein the worksheet exists.
WorksheetIndex: The index of the worksheet to find. Indexes start at 1.
WorksheetName: The name of the worksheet to find.
The output parameter(s) for the action is:
Worksheet: The worksheet you were looking for.
Example 1: Select a worksheet by Name
Example 2: Select a worksheet by Index.
If you want to make a copy of a worksheet in an Excel file, use the Worksheet_Select action to get the worksheet that you want to copy. Once have the worksheet, use the Workbook_AddSheet action to insert a copy of the file.
Workbook: The workbook you want to add the copied worksheet to.
WorksheetName: A name for the “new” worksheet. If no name is specified, a name will automatically be assigned for the worksheet.
Worksheet: The worksheet object that you want to add.
Example 1: Copying a worksheet and not specifying a name.
You can copy a worksheet from one file to another by selecting the worksheet to be copied using the Worksheet_Select action, and then using the Workbook_AddSheet on the new workbook.
Workbook: The workbook that you want to add the sheet to.
WorksheetName: The name of the worksheet you want to add. If binary data is NullObject(), an empty sheet will be added.
Worksheet: The worksheet object that you want to add. Set to NullObject() if adding a new sheet by name.
Example 1: Copy a worksheet from an existing workbook (OpenBook) to a newly created workbook (CreateBook) specifying the name for the new sheet, as well as the index where to add the new sheet.
There are two ways to write data to the Worksheet. Both are described below.
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
Worksheet: The worksheet you want to work with.
RowStart: The row index where to start writing data from.
ColumnStart: The column index where to start writing data from.
DataSet: The data you want to populate in the sheet, starting at RowStart and ColumnStart intersection.NOTE: The dataset should be passed in as an object (use the ToObject() function). The dataset should be a Record List of a Structure type. A record list of an Entity type will not work.
CellFormat: CellFormat for the target cells.
ExportHeaders: True to include headers in the export file. Default value = False.
Example 1: Export without including headers. Headers have been provided in the template file. Specifying row 2 as the start row as to not overwrite the headers.
Example 2: Export including the headers. Specifying row 1 as the start row because no headers were provided. NOTE: Header names will not have spaces.
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
CellName: Name of the cell to write to, i.e. A4. Required if CellRow and CellColumn not set.
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.
CellType: The type can be one of the following:
text (default if nothing specified)
datetime
integer
decimal
boolean
CellFormat: CellFormat for the target cell
NOTE: Formulas can be written as “text” - when the Excel workbook is opened on your computer after download, it will convert the text to actual formulas and Recalculate them all!
Example 1: Write to a single cell with formatting.
Example 2: Write each element of a list, applying specific formatting for each cell.
It is very easy to hide a visible or show a hidden column. Simply use the Column_ShowHide action.
Column: The index of the column within the worksheet that you want to hide/show.
Hidden: A Boolean value, set to True to hide the column, and to False to show the column.
Example 1: Making a hidden column visible.
Example 2: Hiding a visible column.
Inserting a row in a Worksheet is very easy. You need to use the Worksheet_InsertRow action.
InsertAt: The row index where you want to insert the new row(s).
NrRows: The number of rows you want to insert.
CopyStyleFromRow: Copy Styles from this row. Applied to all inserted rows. 0 will not copy any styles.
Example 1: Inserting a new row, copying the style from another row.
Example 2: Inserting a new row without copying any style.
Use the Cell_Read action to read the value of any cell.
CellName: Name of the cell to read from, i.e. A4. Required if CellRow and CellNumber are set to 0.
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.
ReadText: If true always reads the cell value as text.
CellValue: The value in the cell, as text.
Example 1: Reading the value of a cell by index.
Example 2: Reading the value of a cell by name.
If you want to move a worksheet to a different position in the workbook, you need to use the Workbook_ChangeSheetIndex action.
Workbook: The workbook in which the change is to be made.
WorksheetCurrentIndex: The current index(position) of the sheet that you want to move.
WorksheetNewIndex: The new index for the sheet.
Example 1: Changing the index of a sheet in a workbook.
To retrieve all the properties of a workbook, use the Workbook_GetProperties action.
Workbook: The workbook you want to get the properties for.
Properties: A workbook record containing all the properties related to this workbook, including all properties of all the worksheets in this workbook.
Example 1: Get the properties of a workbook.
To retrieve all the properties of a worksheet, use the Worksheet_GetProperties action.
Worksheet: The worksheet that you want the properties for.
Properties: A worksheet record containing all the properties relating to a worksheet.
Example 1: Read the properties of a worksheet.
To apply specific formatting to one or more cells, use the Worksheet_ApplyCellFormatToRange action.
Worksheet: The worksheet that contains the cells where the formatting is to be applied.
CellFormat: The cell formatting options that you want to apply.
Range: The range of cell to apply the formatting to. Can be a single cell.
Example 1: Apply cell formatting to a range of cells.
To delete a worksheet from your workbook, use the Worksheet_Delete action.
Workbook: The workbook from which you want to delete the worksheet.
IndexToDelete: The index of the worksheet to delete. Set to 0 if using the worksheet name to delete.
NameToDelete: The name of the worksheet to delete. Set to an empty string if using the index to delete.
Example 1: Deleting a worksheet by index.
Example 2: Deleting a worksheet by name.
To find all cells containing a specific value, we use the Worksheet_CellsFindByValue.
ValueToFind: The value to search for.
ListOfCells: List of cells (ranges) where the value has been found.
Example 1.a: Open a workbook, select a worksheet, search for the value in the sheet.
Example 1.b: Resulting list of cells where the value was found.
To get the name of a Worksheet object, we can use the Worksheet_Getname action.
WorksheetName: The name of the worksheet.
Example 1: Retrieving the name of a worksheet.
To protect a Worksheet with a password, we use the Worksheet_Protect action.
Worksheet: The worksheet that you want to protect with a password
Password: The password to use for protecting the worksheet.
Example 1: Protecting a worksheet with a password
To rename a worksheet, make use of the Worksheet_Rename action.
Name: The new name for the worksheet.
Example 1: Renaming a worksheet:
To hide or show a worksheet in a workbook, use the Worksheet_Hide_Show action.
Worksheet: The worksheet to work with.
Hidden: An integer indicating one of the following options:
Visible = 0 - The worksheet is visible.
Hidden = 1 - The worksheet is hidden but can be shown by the user via the user interface.
VeryHidden = 2 - The worksheet is hidden and cannot be shown by the user via the user interface.
Example 1: Setting visibility on worksheets.
To show or hide a row in a worksheet, use the Row_Hide_Show action.
RowIndex: The index of the row to hide/show.
Hidden: A Boolean value, set to True to hide the row and to False to show the row.
Example 1: Set a row to hidden.
Example 2: Set a row to show.
To recalculate all formulas in a worksheet, use the Worksheet_Calculate action.
Example 1: Calculate all formulas in the worksheet.
To recalculate all formulas in an entire workbook, use the Workbook_Calculate action.
Workbook: The workbook to work with.
Example 1: Calculating all formulas in a workbook.