ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

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
  • Creating a Spreadsheet control in a window
  • Creating the control
  • Remarks
  • Characteristics of Spreadsheet controls in a window
  • Spreadsheet control description window
  • Initializing the Spreadsheet control in the window editor
  • Customizing a Spreadsheet control
  • Customizing the toolbar
  • Customize the formula bar
  • Customizing the ribbon
WINDEV
WindowsLinuxJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac Catalyst
Others
Stored procedures
Creating a Spreadsheet control in a window

Creating the control

To create a Spreadsheet control:
  1. On the "Creation" tab, in the "Graphic controls" group, expand "Office" and select "Spreadsheet".
  2. Click at the desired location to create the control.
Note: The dimensions of the control are optimized to take up the available space at the specified position. If you are not happy with the size of the control, press Ctrl + Z key to restore the control to its default size.
To view the characteristics of the control, select "Description" in the context menu.

Remarks

  • The data displayed by a Spreadsheet control can:
    • be determined by programming (opening a file recognized by the Spreadsheet control, for example).
    • be entered in the window editor.
    • come from a variable. This variable must be of buffer type and contain the Excel sheet.
    • come from a data file or query. In this case, the item linked to the control can correspond to a memo item containing the desired Excel worksheet.
  • A specific AAF menu is automatically associated with the Spreadsheet control. For more details, see Automatic Application Features (AAF) of Spreadsheet controls.
  • The maximum values of the Spreadsheet control are:
    • 16384 columns.
    • 1048576 rows.
    • 4096 worksheets.
Characteristics of Spreadsheet controls in a window
In the window editor, you can:
  • describe the different characteristics of the Spreadsheet control via its description window.
  • initialize the Spreadsheet control by typing the values found in the different cells.

Spreadsheet control description window

The main options of the Spreadsheet control can be defined in the "Details" tab of the control description window.
These options are as follows:
  • Input options:
    • Allow the input of values The end user will be able to enter values in Spreadsheet control cells. You can change this option programmatically, using the InputValue property.
    • Allow the input of formulas The end user will be able to enter formulas in Spreadsheet control cells. You can change this option programmatically, using the InputFormula property.
  • Display options:
    • Display headers of rows and columns (3): Cell names are displayed in the Spreadsheet control (letters for columns, numbers for rows). You can change this option programmatically, using the HeaderVisible property.
    • With formula input bar (2): A calculation bar is displayed at the top of the field.. This bar is used to type formulas. You can change this option programmatically, using the FormulaBarVisible property.
    • toolbar (1): displays a toolbar at the top of the field. This toolbar is used to format the selected cells. You have the ability to display:
      • No bar.
      • A toolbar (compatibility 22): Displays the menu as a toolbar. You can change this option programmatically, using the ToolbarVisible property. This toolbar can be customized (see Customizing the toolbar).
      • A ribbon (by default for a control created from version 23). This ribbon can be customized (see Customizing the ribbon).
  • Other parameters:
    • Row height: Defines the height of a row in the spreadsheet.. The "Margins" button allows you to set the cell margins, if necessary.
    • Allow multiple worksheets (4): This option allows multiple sheets to be managed in the Spreadsheet control field. The tabs for managing the worksheets are displayed at the bottom left of the Spreadsheet control. A context menu allows the user to manage the worksheets. This option can be modified by the MultiWorksheet property.

Initializing the Spreadsheet control in the window editor

To initialize the Spreadsheet control in the window editor:
  1. Select the Spreadsheet control.
  2. Click the control twice or open the context menu of the control and select "Edit the content".
  3. A yellow border appears around the Spreadsheet control to indicate that the control content is in edit.
  4. Type the different values in the control cells. You have the ability to type values or formulas, to use all the options found in the formatting toolbar or ribbon.
  5. The values typed are automatically saved with the control.
Customizing a Spreadsheet control

Customizing the toolbar

To customize the toolbar of the Spreadsheet control, you must use specific internal windows.
To customize the toolbar:
  1. On the "Project" tab, in the "Project" group, expand "Import" and select "WINDEV elements and their dependencies".
  2. Select the "Programs\Data\Preset Windows\EN\AAF" directory.
  3. Select the windows:
    • WinDevFaa_SelCadreTBLR.wdw: Window for selecting Spreadsheet control cell frame options.
    • WinDevFaa_SelCouleur.wdw: Window for selecting font color.
    • FI_WinDevFaa_BarreTBLR.wdw: Window corresponding to the toolbar.
  4. Validate.
  5. Customize the "IW_WinDevAaf_BarPSHEET.wdw" window according to your needs.
    Warning: field names must not be modified.
If the Spreadsheet control uses a toolbar ("With toolbar" option in the "Details" tab of the control description or ToolbarVisible), the custom toolbar will be used.

Customize the formula bar

To customize the formula bar, simply recreate it.
To create a custom formula bar:
  1. Create two controls:
    • A Static Text control for example, used to view the coordinates of selected cell (or range of cells).
    • An edit control used to type the formula.
  2. Create two buttons:
    • A button used to validate the input of formula.
    • A button used to cancel the input of formula.
  3. Through programming:
    • Indicate the role of the first two controls (viewing the coordinates or typing the formula) via SpreadsheetControlFormula. For example:
      // -- Code d'initialisation du champ Tableur
      // Enlève la barre de formule par défaut
      TBLR_MonTableur.BarreDeFormuleVisible = False
      // Définit le champ permettant de visualiser 
      // les coordonnées de la cellule sélectionnée dans le champ Tableur
      SpreadsheetControlFormula(TBLR_MonTableur, psheetfSelection, LIB_Sélection)
      // Définit le champ permettant de saisir les formules dans le champ Tableur
      SpreadsheetControlFormula(TBLR_MonTableur, psheetfFormula, SAI_Formule)
    • Define the actions of these two buttons with AAFExecute. For example:
      // -- Code de clic du bouton permettant de valider la formule
      // Utilisation de la fonction FAAExécute
      AAFExecute(TBLR_MonTableur, aafValidateInputFunc)
      // -- Code de clic du bouton permettant d'annuler la formule
      // Utilisation de la fonction FAAExécute
      AAFExecute(TBLR_MonTableur, aafCancelInputFunc)
Attention: To use a custom formula bar, the default formula bar must be deactivated:
  • in the editor: option "With formula input bar" in the "Detail" tab of the field description..
  • by programming: property FormulaBarVisible.

Customizing the ribbon

To customize the ribbon of the Spreadsheet control, you must use specific internal windows.
To customize the ribbon:
  1. Go to the "Details" tab in the Spreadsheet control description window.
  2. In the "Toolbar" area, make sure that the "Ribbon" option is selected. .
  3. In the "IW source of ribbon" area, select the default preset window.
  4. The "IW_WINDEVAAF_SpreadsheetRibbon" window is automatically included in your project, in the WDAAF internal component. This internal window can be modified as required. This window contains all the code required to manage the Spreadsheet control. It is recommended to check for UI errors and make sure the window works properly after any change is made.
Note: It is also possible to use an internal window to propose a specific ribbon.. Any internal window of your project can be used to create this bar.
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

Last update: 04/06/2025

Send a report | Local help