Handling a Spreadsheet control by programming (in a window) (prefixed syntax)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help

Editors

Controls (window/page/report)

Controls

Spreadsheet control

Handling a Spreadsheet control by programming (in a window) (prefixed syntax)

Overview

Handling the Spreadsheet control by programming

Handling the cells of a Spreadsheet control by programming

Spreadsheet control: Handling a file containing several worksheets by programming

Properties specific to the Spreadsheet controls

Related examples

See also

Handling a Spreadsheet control by programming (in a window) (prefixed syntax)

Overview

A Spreadsheet control found in a window can be handled by programming.

To handle a Spreadsheet control by programming, WINDEV proposes the Spreadsheet functions.

This help page explains how to handle a Spreadsheet control found in a window by programming.

Remark: The method for handing a Spreadsheet control found in a report is different. See Handling a Spreadsheet control by programming (in a report) for more details.

Handling the Spreadsheet control by programming

Initializing a Spreadsheet control

The Spreadsheet control can be initialized with values, formulas, ... as soon as it is displayed according to different methods:

By programming, via the following functions:


Versions 20 and later <Spreadsheet>.AddData New in version 20 <Spreadsheet>.AddData <Spreadsheet>.AddData	Adds a data into a Spreadsheet control found in a window.
Versions 20 and later <Spreadsheet>.AddFormula New in version 20 <Spreadsheet>.AddFormula <Spreadsheet>.AddFormula	Adds a calculation formula into a Spreadsheet control found in a window.
Versions 20 and later <Spreadsheet>.AllowProcedure New in version 20 <Spreadsheet>.AllowProcedure <Spreadsheet>.AllowProcedure	Allows the use of a WLanguage function/procedure in a formula of a Spreadsheet control.

By loading an Excel file (in XLSX format) when initializing the control (<Spreadsheet>.Load).
Via direct input in the window editor. See Initializing the Spreadsheet control in the window editor for more details.

Handling the Spreadsheet control

To handle a Spreadsheet control by programming, all you have to do is specify its name.

For example:

// Displays the formula bar
PSHEET_Spreadsheet..FormulaBarVisible = True

Several WLanguage functions and properties can be used to handle the Spreadsheet control.

Handling the cells of a Spreadsheet control by programming

Handling the cells of the Spreadsheet control

To handle a cell of a Spreadsheet control by programming , use the following notations:

<Spreadsheet control name>[<Row>, <Column>]

<Spreadsheet control name>["<CellName>"]

Examples:

// Colors the C1 cell in red
PSHEET_Spreadsheet[1,3]..BackgroundColor = LightRed

// Colors the B1 cell in green
PSHEET_Spreadsheet["B1"]..BackgroundColor = LightGreen

To assign a value to a cell by programming, you can use:

<Spreadsheet>.AddData.
Example: Adding a value into the "A2" cell:

PSHEET_Spreadsheet.AddData(2, 1, 12)
the direct assignment:
- via the cell.
  Example: Adding a value into the "A2" cell:
  
  PSHEET_Spreadsheet["A2"] = 12
- via the column and row of the cell.
  Example: Adding a value into the "A2" cell:
  
  PSHEET_Spreadsheet["A"][2] = 12

Remarks:

Assigning a value in 'YYYYMMDD' format to a cell in Date format automatically changes the value into date.
Assigning a value in 'HHMM' format to a cell in Time format automatically changes the value into time.

Example: Browse the cells of a Spreadsheet control:

// Browse the cells of a Spreadsheet control
sMyString is string
FOR i = 1 TO PSHEET_MySpreadsheet..NumberRow
FOR y = 1 TO PSHEET_MySpreadsheet..NumberColumn
sMyString += PSHEET_MySpreadsheet[i][y] + TAB
END
Trace(sMyString + CR)
sMyString = ""
END

Tip: Merging cells by programming

The end user can merge the selected cells using the ribbon (or the toolbar) of the Spreadsheet control.

To merge the selected cells by programming, simply use ExecuteAAF associated with the constant aafSpreadsheetMerge:

PSHEET_MySpreadsheet.SelectPlus("A1", "C5")
ExecuteAAF(PSHEET_MySpreadsheet, aafSpreadsheetMerge)

Remark: <Spreadsheet>.GetMerge is used to find out whether a cell is merged with other cells and to retrieve the range of merged cells.

Managing the names of cells in a Spreadsheet control

The Spreadsheet control proposes several functions for handling the names of cells:


Versions 22 and later <Spreadsheet>.AddName New in version 22 <Spreadsheet>.AddName <Spreadsheet>.AddName	Specifies a name to identify a cell found in a Spreadsheet control.
Versions 23 and later SpreadsheetDeleteName New in version 23 SpreadsheetDeleteName SpreadsheetDeleteName	Deletes one of the names used to identify a cell found in a Spreadsheet control.
Versions 23 and later SpreadsheetListName New in version 23 SpreadsheetListName SpreadsheetListName	Returns the list of names that identify a cell in a Spreadsheet control.
Versions 23 and later SpreadsheetModifyName New in version 23 SpreadsheetModifyName SpreadsheetModifyName	Modifies the name used to identify a cell in a Spreadsheet control.

Versions 25 and later

Managing comments in a Spreadsheet control

The Spreadsheet control allows you to:

add comments,
display existing comments in an XLSX file.

Comments can be managed:

via the ..Comment advanced property. This property is used to find out and modify the comments associated to a cell via the following properties:


Position	Name of the associated cell. This property is available in read-only.
Rectangle	Variable of type Rectangle allowing to define the rectangle in which the comment is displayed.. The properties ..Width and ..Height determine the size of the rectangle (in millimeters), and the properties ..X and ..Y determine the position of the comment (in millimeters) relative to the beginning of the current sheet.
Text	Comment text.
Visible	True to display the comment, False to make comment invisible. In this case, a red triangle in the top right corner of the cell will let the user know that the cell is associated with a comment.

Example:

// Create a comment
PSHEET_MySpreadsheet["B1"]..Comment..Text = "Text of my comment"
PSHEET_MySpreadsheet["B1"]..Comment..Visible = True
PSHEET_MySpreadsheet["B1"]..Comment..Rectangle..Height = 50
PSHEET_MySpreadsheet["B1"]..Comment..Rectangle..Width = 50
PSHEET_MySpreadsheet["B1"]..Comment..Rectangle..X = 50
PSHEET_MySpreadsheet["B1"]..Comment..Rectangle..Y = 50

via different WLanguage functions:


Versions 25 and later SpreadsheetListComment New in version 25 SpreadsheetListComment SpreadsheetListComment	Lists all the comments on the current worksheet of a Spreadsheet control.
Versions 25 and later SpreadsheetSelectComment New in version 25 SpreadsheetSelectComment SpreadsheetSelectComment	Allows you to: know if a comment is selected in a Spreadsheet control, select or deselect a comment in a Spreadsheet control.

New in version 25

Managing comments in a Spreadsheet control

The Spreadsheet control allows you to:

add comments,
display existing comments in an XLSX file.

Comments can be managed:

via the ..Comment advanced property. This property is used to find out and modify the comments associated to a cell via the following properties:


Position	Name of the associated cell. This property is available in read-only.
Rectangle	Variable of type Rectangle allowing to define the rectangle in which the comment is displayed.. The properties ..Width and ..Height determine the size of the rectangle (in millimeters), and the properties ..X and ..Y determine the position of the comment (in millimeters) relative to the beginning of the current sheet.
Text	Comment text.
Visible	True to display the comment, False to make comment invisible. In this case, a red triangle in the top right corner of the cell will let the user know that the cell is associated with a comment.

Example:

via different WLanguage functions:


Versions 25 and later SpreadsheetListComment New in version 25 SpreadsheetListComment SpreadsheetListComment	Lists all the comments on the current worksheet of a Spreadsheet control.
Versions 25 and later SpreadsheetSelectComment New in version 25 SpreadsheetSelectComment SpreadsheetSelectComment	Allows you to: know if a comment is selected in a Spreadsheet control, select or deselect a comment in a Spreadsheet control.

Managing comments in a Spreadsheet control

The Spreadsheet control allows you to:

add comments,
display existing comments in an XLSX file.

Comments can be managed:

via the ..Comment advanced property. This property is used to find out and modify the comments associated to a cell via the following properties:


Position	Name of the associated cell. This property is available in read-only.
Rectangle	Variable of type Rectangle allowing to define the rectangle in which the comment is displayed.. The properties ..Width and ..Height determine the size of the rectangle (in millimeters), and the properties ..X and ..Y determine the position of the comment (in millimeters) relative to the beginning of the current sheet.
Text	Comment text.
Visible	True to display the comment, False to make comment invisible. In this case, a red triangle in the top right corner of the cell will let the user know that the cell is associated with a comment.

Example:

via different WLanguage functions:


Versions 25 and later SpreadsheetListComment New in version 25 SpreadsheetListComment SpreadsheetListComment	Lists all the comments on the current worksheet of a Spreadsheet control.
Versions 25 and later SpreadsheetSelectComment New in version 25 SpreadsheetSelectComment SpreadsheetSelectComment	Allows you to: know if a comment is selected in a Spreadsheet control, select or deselect a comment in a Spreadsheet control.

Spreadsheet control: Handling a file containing several worksheets by programming

To enable the management of several worksheets:

In the editor: In the "Details" tab of the control description window, check "Allow management of several worksheets".
By programming, use ..MultiWorksheet.

Once the management of several worksheets was enabled:

You have the ability to load the different worksheets of an XLSX file (<Spreadsheet>.Load).

Several functions are used to handle the different worksheets:


Versions 20 and later <Spreadsheet>.AddWorksheet New in version 20 <Spreadsheet>.AddWorksheet <Spreadsheet>.AddWorksheet	Adds a worksheet into a Spreadsheet control found in a window.
Versions 20 and later <Spreadsheet>.DeleteWorksheet New in version 20 <Spreadsheet>.DeleteWorksheet <Spreadsheet>.DeleteWorksheet	Deletes a worksheet from a Spreadsheet control found in a window.
Versions 20 and later <Spreadsheet>.InsertWorksheet New in version 20 <Spreadsheet>.InsertWorksheet <Spreadsheet>.InsertWorksheet	Inserts a worksheet into a Spreadsheet control found in a window.
Versions 23 and later SpreadsheetListWorksheet New in version 23 SpreadsheetListWorksheet SpreadsheetListWorksheet	Returns the list of worksheets found in a Spreadsheet control.
Versions 20 and later <Spreadsheet>.SelectWorksheet New in version 20 <Spreadsheet>.SelectWorksheet <Spreadsheet>.SelectWorksheet	Selects a worksheet (identified by its name) in a Spreadsheet control.

Several properties are used to handle the different worksheets:


Versions 20 and later CurrentWorksheet New in version 20 CurrentWorksheet CurrentWorksheet	..CurrentWorksheet is used to find out and modify the current worksheet in a Spreadsheet control.
Versions 20 and later MultiWorksheet New in version 20 MultiWorksheet MultiWorksheet	..MultiWorksheet is used to: Find out whether a Spreadsheet control manages several worksheets. Modify the mode for managing the worksheets in a Spreadsheet control.
Versions 20 and later NbWorksheet New in version 20 NbWorksheet NbWorksheet	..NbWorksheet is used to find out the number of worksheets in a Spreadsheet control.
Versions 20 and later WorksheetName New in version 20 WorksheetName WorksheetName	..WorksheetName is used to find out and modify the name of the current worksheet in a Spreadsheet control.

Several AAFs (Automatic Application Features) allows the user to manage the different worksheets.

Properties specific to the Spreadsheet controls

The following properties are used to manage a Spreadsheet control by programming.


Versions 20 and later CurrentWorksheet New in version 20 CurrentWorksheet CurrentWorksheet	..CurrentWorksheet is used to find out and modify the current worksheet in a Spreadsheet control.
Versions 23 and later FilePath New in version 23 FilePath FilePath	..FilePath is used to find out: the name of xlsx file associated with a Spreadsheet control. This name is initialized by <Spreadsheet>.Load and <Spreadsheet>.Save. the name of file associated with an Image Editor control. This name is initialized by <Image Editor>.Open and <Image Editor>.Save. the name of PDF file associated with a PDF Reader control. This name is initialized by <PDF Reader>.Open. the name of DOCX file associated with a Word Processing control. This name is initialized by <Word Processing>.Open and <Word Processing>.Save.
Versions 20 and later FormulaBarVisible New in version 20 FormulaBarVisible FormulaBarVisible	..FormulaBarVisible is used to: Find out whether a formula bar is displayed in a Spreadsheet control. Display (or not) a formula bar in a Spreadsheet control.
Versions 23 and later GridlinesVisible New in version 23 GridlinesVisible GridlinesVisible	..GridlinesVisible is used to: Find out whether the gridlines are visible or not in a Spreadsheet control. Modify the visibility of gridlines in a Spreadsheet control.
Versions 20 and later HeaderVisible New in version 20 HeaderVisible HeaderVisible	..HeaderVisible is used to: Find out whether the row and column headers are displayed in a Spreadsheet control. Display (or not) the row and column headers in a Spreadsheet control.
Versions 20 and later InputFormula New in version 20 InputFormula InputFormula	..InputFormula is used to: find out whether the end user can enter or modify formulas in a Spreadsheet control. allow (or not) the end user to enter or modify formulas in a Spreadsheet control.
Versions 20 and later InputValue New in version 20 InputValue InputValue	..InputValue is used to: find out whether the end user can enter or modify values in a Spreadsheet control. allow or not the end user to enter or modify values in a Spreadsheet control.
Versions 20 and later MultiWorksheet New in version 20 MultiWorksheet MultiWorksheet	..MultiWorksheet is used to: Find out whether a Spreadsheet control manages several worksheets. Modify the mode for managing the worksheets in a Spreadsheet control.
Versions 20 and later NbWorksheet New in version 20 NbWorksheet NbWorksheet	..NbWorksheet is used to find out the number of worksheets in a Spreadsheet control.
Versions 20 and later ToolbarVisible New in version 20 ToolbarVisible ToolbarVisible	..ToolbarVisible is used to: Find out whether the control ribbon is displayed. Display (or not) a ribbon for the control.
Versions 20 and later WorksheetName New in version 20 WorksheetName WorksheetName	..WorksheetName is used to find out and modify the name of the current worksheet in a Spreadsheet control.

To find out the entire list of WLanguage properties that can be used with a Spreadsheet control and with its cells, see Properties associated with the Spreadsheet control.

Related Examples:

Unit examples (WINDEV): The Spreadsheet control

[ + ] Using the Spreadsheet control.
This example explains how to:
- load an xlsx file in a spreadsheet control,
- save the spreadsheet in a file,
- fill the control with data coming from the database,
- insert rows, columns,
- access the cells and handle them (modify their value, their style, ...),
- enter formulas,
- ...