Manipulating Spreadsheet controls programmatically (in a window)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / Developing an application or website / Controls, windows and pages / Controls: Available types / Spreadsheet control

Overview
Manipulating Spreadsheet controls programmatically
Initializing a Spreadsheet control
Handling the Spreadsheet control
Handling the cells of a Spreadsheet control programmatically
Handling the cells of the Spreadsheet control
Tip: Merge cells programmatically
Managing the names of cells in a Spreadsheet control
Managing comments in a Spreadsheet control
Spreadsheet control: Handling a file containing several worksheets programmatically
Properties specific to Spreadsheet controls

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Manipulating Spreadsheet controls programmatically (in a window)

Overview

Spreadsheet controls can be manipulated programmatically.

To programmatically manipulate Spreadsheet controls, WINDEV includes the Spreadsheet functions.

This help page explains how to manipulate a Spreadsheet control in a window from the code.

Remark: The method for handing a Spreadsheet control found in a report is different. For more details, see Manipulating Spreadsheet controls programmatically (in a report).

Manipulating Spreadsheet controls programmatically

Initializing a Spreadsheet control

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

Programmatically, via the following functions:


SpreadsheetAddData	Adds a data into a Spreadsheet control found in a window.
SpreadsheetAddFormula	Adds a calculation formula into a Spreadsheet control found in a window.
SpreadsheetAllowProcedure	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 (SpreadsheetLoad).
Via direct input in the window editor. For more details, see Initializing the Spreadsheet control in the window editor.

Handling the Spreadsheet control

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

For example:

// Affiche la barre de formule
TBLR_Tableur.BarreDeFormuleVisible = True

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

Handling the cells of a Spreadsheet control programmatically

Handling the cells of the Spreadsheet control

To manipulate a cell of a Spreadsheet control through programming , use the following notations:

<Nom Champ Tableur>[<Ligne>, <Colonne>]

<Nom Champ Tableur>["<NomCellule>"]

Examples:

// Colore en rouge la cellule C1
TBLR_Tableur[1,3].CouleurFond = LightRed

// Colore en vert la cellule B1
TBLR_Tableur["B1"].CouleurFond = LightGreen

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

the SpreadsheetAddData function.
Example: Adding a value into the "A2" cell:
```
SpreadsheetAddData(TBLR_Tableur, 2, 1, 12)
```
the direct assignment:
- via the cell.
  Example: Adding a value into the "A2" cell:
  TBLR_Tableur["A2"] = 12
- via the column and row of the cell.
  Example: Adding a value into the "A2" cell:
  TBLR_Tableur["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:

// Parcours des cellules d'un champ Tableur
sMaChaîne is string
FOR i = 1 TO TBLR_MonTableur.NombreLigne
	FOR y = 1 TO TBLR_MonTableur.NombreColonne
		sMaChaîne += TBLR_MonTableur[i][y] + TAB
	END
	Trace(sMaChaîne + CR)
	sMaChaîne = ""
END

Tip: Merge cells programmatically

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

To programmatically merge the selected cells, simply use ExecuteAAF with the aafSpreadsheetMerge constant:

SpreadsheetSelectPlus(TBLR_MonTableur, "A1", "C5")
ExecuteAAF(TBLR_MonTableur, aafSpreadsheetMerge)

Remark: SpreadsheetGetMerge determines whether a cell has been merged with other cells and gets 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:


SpreadsheetAddName	Specifies a name to identify a cell found in a Spreadsheet control.
SpreadsheetDeleteName	Deletes one of the names used to identify a cell found in a Spreadsheet control.
SpreadsheetListName	Returns the list of names that identify a cell in a Spreadsheet control.
SpreadsheetModifyName	Modifies the name used to identify a cell 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 gets and sets the comments associated to a cell via the following properties:


Position	Name of the associated cell. This property is read-only.
Rectangle	Rectangle variable. Defines the rectangle in which the comment is displayed. The Width and Height properties determine the size of the rectangle (in millimeters), while X and Y determine the position of the comment (in millimeters with respect to the beginning of the current sheet.
Text	Comment text.
Visible	True to show the comment, False to make the comment invisible. In this case, a red triangle in the upper-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:


SpreadsheetListComment	Lists all the comments on the current worksheet of a Spreadsheet control.
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 programmatically

To enable the management of several worksheets:

In the editor: In the "Details" tab of the control description window, check "Allow multiple worksheets".
Through programming, use the MultiWorksheet property.

Once the management of several worksheets was enabled:

You have the ability to load the different worksheets of an XLSX file (SpreadsheetLoad).

Several functions are used to handle the different worksheets:


SpreadsheetAddWorksheet	Adds a worksheet into a Spreadsheet control found in a window.
SpreadsheetDeleteWorksheet	Deletes a worksheet from a Spreadsheet control found in a window.
SpreadsheetInsertWorksheet	Inserts a worksheet into a Spreadsheet control found in a window.
SpreadsheetListWorksheet	Returns the list of worksheets found in a Spreadsheet control.
SpreadsheetSelectWorksheet	Selects a worksheet (identified by its name) in a Spreadsheet control.

Several properties are used to handle the different worksheets:


CurrentWorksheet	The CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control.
MultiWorksheet	The MultiWorksheet property is used to: Find out whether a Spreadsheet control manages several worksheets. Change the worksheet management mode of a Spreadsheet control.
NbWorksheet	The NbWorksheet property is used to get the number of worksheets in a Spreadsheet control.
WorksheetName	The WorksheetName property is used to get or change 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 Spreadsheet controls

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


Comment	The Comment property gets and sets the different characteristics of comments in a cell of a Spreadsheet control.
CurrentWorksheet	The CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control.
FilePath	The FilePath property gets: the name of the xlsx file associated with a Spreadsheet control. the name of the file associated with an Image Editor control. the name of the PDF file associated with a PDF Reader control. the name of the DOCX file associated with a Word Processing control. the name of the wddiag file associated with a Diagram Editor control.
FormulaBarVisible	The FormulaBarVisible property 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.
GridlinesVisible	The GridlinesVisible property is used to: Determine whether or not gridlines are visible in a control. Show or hide gridlines in a control.
HeaderVisible	The HeaderVisible property is used to: Determine if the row and column headers are displayed in a Spreadsheet control. Show or hide the row and column headers in a Spreadsheet control.
InputFormula	The InputFormula property is used to: find out whether the end user can enter or change formulas in a Spreadsheet control. allow or prevent the end user from entering or changing formulas in a Spreadsheet control.
InputValue	The InputValue property is used to: find out whether the end user can enter or change values in a Spreadsheet control. allow or prevent the end user from entering or changing values in a Spreadsheet control.
MultiWorksheet	The MultiWorksheet property is used to: Find out whether a Spreadsheet control manages several worksheets. Change the worksheet management mode of a Spreadsheet control.
NbWorksheet	The NbWorksheet property is used to get the number of worksheets in a Spreadsheet control.
ToolbarVisible	The ToolbarVisible property is used to: determine if the toolbar or the ribbon is displayed in a control. show or hide the toolbar or the ribbon in a control.
WorksheetName	The WorksheetName property is used to get or change the name of the current worksheet in a Spreadsheet control.

For a complete list of WLanguage properties that can be used with a Spreadsheet control and with its cells, see Spreadsheet control properties.

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