Advanced Excel

Stable Version 2.1.23 (O11)
Also available for 10, 9.1, 9, 6
Published on 27 August 2019 by 
Created on 13 May 2013
Documentation


Glossary

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:

  • Address: Address of the cell as text.
  • Columns: Number of columns in the worksheet.
  • Rows: Number of rows in the worksheet.
  • Start: Start location as an Address.
  • End: End location as an Address.


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:

  • Name: The name of the series.
  • ValueRange: Range of cells for the values of the series.
  • LabelRange: Range of cells for the labels of the series.


Color: Represents an ARGB (alpha, red, green, blue) color. The following attributes are available:

  • IsKnownColor: Gets a value indicating whether this System.Drawing.Color structure is a predefined color. Predefined colors are represented by the elements of the System.Drawing.KnownColor enumeration.
  • IsNamedColor: Gets a value indicating whether this System.Drawing.Color structure is a named color or a member of the System.Drawing.KnownColor enumeration.
  • IsSystemColor: Gets a value indicating whether this System.Drawing.Color structure is a system color. A system color is a color that is used in a Windows display element. System colors are represented by elements of the System.Drawing.KnownColor enumeration.
  • A: Alpha.
  • R: Red.
  • G: Green.
  • B: Blue.
  • Name: The name of this System.Drawing.Color.


CellFormat: Structure to define all the formatting attributes that can apply to a cell. The following attributes are available:

  • FontName: Name of the font to use.
  • FontSize: Size of the font to use.
  • BackgroundColor: Background color in hex format.
  • AutofitColumn: True to autofit column, false to leave as is.
  • Bold: True to set the text to Bold.
  • NumberFormat: Any number format to be applied to the cell.
  • BorderStyle: 
    • None = 0,
    • Hair = 1,
    • Dotted = 2,
    • DashDot = 3,
    • Thin = 4,
    • DashDotDot = 5,
    • Dashed = 6,
    • MediumDashDotDot = 7,
    • MediumDashed = 8,
    • MediumDashDot = 9,
    • Thick = 10,
    • Medium = 11,
    • Double = 12
  • BorderColor: Color of the border in hex format


Address: The Address structure used by Excel. It contains the following attributes:

  • Address: The address as text. 
  • Row: The row index of the address.
  • Column: The column index of the address.
  • IsRef: If the address is an invalid reference (#REF!)


NOTE:

  1. For sample data, use the OutSystems Sample Data component found here: https://www.outsystems.com/forge/component-overview/4145/outsystems-sample-data
  2. One-based numbering is used for row and column indexes in worksheets.
  3. One-based numbering is used for worksheet indexing within a workbook.
  4. Example OML will be made available in the forge.


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:

  • 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”.

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.

  • 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.

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: Opening a file stored as a local resource in your espace.

Example 2: Open file from URL.

Download an Excel file you created

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.

You will need to provide the following input parameters:

  • 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.

Dispose of/Close an Excel file

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.

You will need to provide the following input parameters:

  • 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.

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 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.

Select a specific Worksheet

To select a specific worksheet for use with one of the Worksheet_* actions, you will use the Worksheet_Select action.

You will need to provide the following input parameters:

  • 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.

Copy a worksheet in an Excel file

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.

You will need to provide the following input parameters:

  • 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.

  • IndexWhereToAdd: The index where to add the new sheet. The default will be the highest sheet index, plus 1.

Example 1: Copying a worksheet and not specifying a name.

Copy a worksheet to a new Excel file

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.

You will need to provide the following input parameters:

  • 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.

  • IndexWhereToAdd: The index where to add the new sheet. The default will be the highest sheet index, plus 1.

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.

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:

  • 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.

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:

  • Worksheet: The worksheet you want to work with.

  • 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.

Hide/Show a Column in a Worksheet

It is very easy to hide a visible or show a hidden column. Simply use the Column_ShowHide action. 

You will need to provide the following input parameters:

  • Worksheet: The worksheet you want to work with.

  • 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.

Insert a row in a Worksheet

Inserting a row in a Worksheet is very easy. You need to use the Worksheet_InsertRow action. 

You will need to provide the following input parameters:

  • Worksheet: The worksheet you want to work with.

  • 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.

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:

  • Worksheet: The worksheet you want to work with.

  • 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.

The output parameter(s) for the action is:

  • 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.

Change the index of a Worksheet

If you want to move a worksheet to a different position in the workbook, you need to use the Workbook_ChangeSheetIndex action.

You will need to provide the following input parameters:

  • 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.

Get properties of the Workbook

To retrieve all the properties of a workbook, use the Workbook_GetProperties action.

You will need to provide the following input parameters:

  • Workbook: The workbook you want to get the properties for.

The output parameter(s) for the action is:

  • 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.

Get properties of the Worksheet

To retrieve all the properties of a worksheet, use the Worksheet_GetProperties action.

You will need to provide the following input parameters:

  • Worksheet: The worksheet that you want the properties for.

The output parameter(s) for the action is:

  • Properties: A worksheet record containing all the properties relating to a worksheet.

Example 1: Read the properties of a worksheet.

Apply a specific format to a range

To apply specific formatting to one or more cells, use the Worksheet_ApplyCellFormatToRange action.

You will need to provide the following input parameters:

  • 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.

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.

  • 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.

Find all Cells containing the specified value

To find all cells containing a specific value, we use the Worksheet_CellsFindByValue. 

You will need to provide the following input parameters:

  • Worksheet: The worksheet you want to work with.

  • ValueToFind: The value to search for.

The output parameter(s) for the action is:

  • 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.

Get the name of a Worksheet

To get the name of a Worksheet object, we can use the Worksheet_Getname action.

You will need to provide the following input parameters:

  • Worksheet: The worksheet you want to work with.

The output parameter(s) for the action is:

  • WorksheetName:  The name of the worksheet.

Example 1: Retrieving the name of a worksheet.

Protect a Worksheet

To protect a Worksheet with a password, we use the Worksheet_Protect action. 

You will need to provide the following input parameters:

  • 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

Rename a Worksheet

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

You will need to provide the following input parameters:

  • Worksheet: The worksheet you want to work with.

  • Name: The new name for the worksheet.

Example 1: Renaming a worksheet:

Show / Hide a Worksheet

To hide or show a worksheet in a workbook, use the Worksheet_Hide_Show action.

You will need to provide the following input parameters:

  • 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.

Show / Hide a Row in a Worksheet

To show or hide a row in a worksheet, use the Row_Hide_Show action.

You will need to provide the following input parameters:

  • Worksheet: The worksheet to work with.

  • 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.

Calculate all formulae in a Worksheet

To recalculate all formulae in a worksheet, use the Worksheet_Calculate action.

You will need to provide the following input parameters:

  • Worksheet: The worksheet to work with.

Example 1: Calculate all formulae in the worksheet.

Calculate all formulas in all Worksheets in a Workbook

To recalculate all formulae in an entire workbook, use the Workbook_Calculate action.

You will need to provide the following input parameters:

  • Workbook: The workbook to work with.

Example 1: Calculating all formulae in a workbook.


Support Options
This component is not supported by OutSystems. You may use the discussion forums to leave suggestions or obtain best-effort support from the community, including from Carlos Freitas who created this component.
Dependencies
Advanced Excel has no dependencies.