PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
This content has been translated automatically. Click here to view the French version.
  • Overview
  • Handling the Spreadsheet control through programming
  • Initializing a Spreadsheet control
  • Handling the Spreadsheet control
  • Handling the cells of a Spreadsheet control through programming
  • Handling the cells of the Spreadsheet control
  • Tip: Merging cells through programming
  • Managing the names of cells in a Spreadsheet control
  • Managing comments in a Spreadsheet control
  • Spreadsheet control: Handling a file containing several worksheets through programming
  • Properties specific to the Spreadsheet controls
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
A Spreadsheet control found in a window can be handled through 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 through programming.
Remark: The method for handing a Spreadsheet control found in a report is different. For more details, see Handling a Spreadsheet control through programming (in a report).
Handling the Spreadsheet control through programming

Initializing a Spreadsheet control

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

Handling the Spreadsheet control

To handle 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 through programming

Handling the cells of the Spreadsheet control

To handle a cell of a Spreadsheet control through programming , use the following notations:
<Nom Champ Tableur>[<Ligne>, <Colonne>]
or
<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 function SpreadsheetAddData.
    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: Merging cells through programming

The end user can merge the selected cells using the ribbon (or the toolbar) of the Spreadsheet control.
To merge the selected cells through programming, simply use ExecuteAAF associated with the constant aafSpreadsheetMerge:
SpreadsheetSelectPlus(TBLR_MonTableur, "A1", "C5")
ExecuteAAF(TBLR_MonTableur, aafSpreadsheetMerge)
Remark: SpreadsheetGetMerge 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
SpreadsheetAddName
New in version 22
SpreadsheetAddName
SpreadsheetAddName
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 advanced property comment. This property is used to find out and modify the comments associated to a cell via the following properties:
    PositionName of the associated cell.
    This property is available in read-only mode.
    RectangleRectangle variable.
    Allows you 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.
    TextComment text.
    Visible
    • True to display the comment,
    • False to make the 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:
    // Création d'un commentaire
    TBLR_MonTableur["B1"].Commentaire.Texte = "Texte de mon commentaire"
    TBLR_MonTableur["B1"].Commentaire.Visible = True
    TBLR_MonTableur["B1"].Commentaire.Rectangle.Hauteur = 50
    TBLR_MonTableur["B1"].Commentaire.Rectangle.Largeur = 50
    TBLR_MonTableur["B1"].Commentaire.Rectangle.X = 50
    TBLR_MonTableur["B1"].Commentaire.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 advanced property comment. This property is used to find out and modify the comments associated to a cell via the following properties:
    PositionName of the associated cell.
    This property is available in read-only mode.
    RectangleRectangle variable.
    Allows you 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.
    TextComment text.
    Visible
    • True to display the comment,
    • False to make the 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:
    // Création d'un commentaire
    TBLR_MonTableur["B1"].Commentaire.Texte = "Texte de mon commentaire"
    TBLR_MonTableur["B1"].Commentaire.Visible = True
    TBLR_MonTableur["B1"].Commentaire.Rectangle.Hauteur = 50
    TBLR_MonTableur["B1"].Commentaire.Rectangle.Largeur = 50
    TBLR_MonTableur["B1"].Commentaire.Rectangle.X = 50
    TBLR_MonTableur["B1"].Commentaire.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.

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 advanced property comment. This property is used to find out and modify the comments associated to a cell via the following properties:
    PositionName of the associated cell.
    This property is available in read-only mode.
    RectangleRectangle variable.
    Allows you 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.
    TextComment text.
    Visible
    • True to display the comment,
    • False to make the 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:
    // Création d'un commentaire
    TBLR_MonTableur["B1"].Commentaire.Texte = "Texte de mon commentaire"
    TBLR_MonTableur["B1"].Commentaire.Visible = True
    TBLR_MonTableur["B1"].Commentaire.Rectangle.Hauteur = 50
    TBLR_MonTableur["B1"].Commentaire.Rectangle.Largeur = 50
    TBLR_MonTableur["B1"].Commentaire.Rectangle.X = 50
    TBLR_MonTableur["B1"].Commentaire.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.
Spreadsheet control: Handling a file containing several worksheets through programming
To enable the management of several worksheets:
  • In the editor: In the "Details" tab of the control description window, check "Allow multiple worksheets".
  • By programming, use MultiWorksheet.
Once the management of several worksheets was enabled:
Properties specific to the Spreadsheet controls
The following properties are used to manage a Spreadsheet control through programming.
Versions 20 and later
CurrentWorksheet
New in version 20
CurrentWorksheet
CurrentWorksheet
The CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control.
Versions 23 and later
FilePath
New in version 23
FilePath
FilePath
The FilePath property is used to identify:
  • the name of the xlsx file associated with a Spreadsheet control. This name is initialized by SpreadsheetLoad and SpreadsheetSave.
  • the name of the file associated with an Image Editor control. This name is initialized by PicOpen and PicSave.
  • the name of the PDF file associated with a PDF Reader control. This name is initialized by PDFReaderOpen.
  • the name of the DOCX file associated with a Word Processing control. This name is initialized by DocOpen and DocSave.
Versions 20 and later
FormulaBarVisible
New in version 20
FormulaBarVisible
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.
Versions 23 and later
GridlinesVisible
New in version 23
GridlinesVisible
GridlinesVisible
The GridlinesVisible property 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
The HeaderVisible property 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
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.
Versions 20 and later
InputValue
New in version 20
InputValue
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.
Versions 20 and later
MultiWorksheet
New in version 20
MultiWorksheet
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.
Versions 20 and later
NbWorksheet
New in version 20
NbWorksheet
NbWorksheet
The NbWorksheet property is used to get the number of worksheets in a Spreadsheet control.
Versions 20 and later
ToolbarVisible
New in version 20
ToolbarVisible
ToolbarVisible
The ToolbarVisible property is used to:
  • know if the toolbar or the ribbon is displayed in a control.
  • show or hide the toolbar or the ribbon in a control.
Versions 20 and later
WorksheetName
New in version 20
WorksheetName
WorksheetName
The WorksheetName property is used to get or change 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:
The Spreadsheet control 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,
- ...
Minimum version required
  • Version 20
Comments
Click [Add] to post a comment