PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Creating a Spreadsheet control in a window
  • Creating the control
  • Notes
  • Characteristics of Spreadsheet control in a window
  • Description window of Spreadsheet control
  • Initializing the Spreadsheet control in the window editor
  • Customizing a Spreadsheet control
  • Customizing the toolbar
  • Customizing the formula bar
  • Customizing the ribbon
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Creating a Spreadsheet control in a window

Creating the control

To create a Spreadsheet control:
  1. On the "Creation" pane, in the "Data" group, expand "Table and list box" and select "Spreadsheet". On the "Creation" pane, in the "Graphic controls" group, expand "Office" and select "Spreadsheet".
  2. Click the position where the control will be created in the window.
Versions 20 and later
Note: The dimensions of created control are optimized in order to occupy the available space at the specified position. If the control size does not suit your, press [CTRL Z]: the default control size will be restored.
New in version 20
Note: The dimensions of created control are optimized in order to occupy the available space at the specified position. If the control size does not suit your, press [CTRL Z]: the default control size will be restored.
Note: The dimensions of created control are optimized in order to occupy the available space at the specified position. If the control size does not suit your, press [CTRL Z]: the default control size will be restored.
To display the control characteristics, select "Description" from the popup menu of control.

Notes

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

Description window of Spreadsheet control

The main options of Spreadsheet control are defined in the "Details" tab of the description window of control.
These options are as follows:
  • Input options:
    • Allow the input of values: The end user will be able to enter values in the cells of Spreadsheet control. This option can be modified by programming with ..InputValue.
    • Allow the input of formulas: The end user will be able to enter formulas in the cells of Spreadsheet control. This option can be modified by programming with ..InputFormula.
  • Display options:
    • Display the headers of rows and columns (3): The names of the cells are displayed in the Spreadsheet control (letters for the columns, digits for the rows). This option can be modified by programming with ..HeaderVisible.
    • With toolbar (1): A toolbar is displayed at the top of control. This toolbar is used to format the selected cells. This option can be modified by programming with ..ToolbarVisible.
    • With formula input bar (2): A calculation bar is displayed at the top of control. This bar is used to type formulas. This option can be modified by programming with ..FormulaBarVisible.
    Display options:
    • Display the headers of rows and columns (3): The names of the cells are displayed in the Spreadsheet control (letters for the columns, digits for the rows). This option can be modified by programming with ..HeaderVisible.
    • With formula input bar (2): A calculation bar is displayed at the top of control. This bar is used to type formulas. This option can be modified by programming with ..FormulaBarVisible.
    • Toolbar (1): Used to display a toolbar at the top of control. This toolbar is used to format the selected cells. You have the ability to display:
      • No bar.
      • A toolbar (compatibility with version 22): Displays the menu in toolbar format. This option can be modified by programming with ..ToolbarVisible. This toolbar can be customized (see Customizing the toolbar).
      • Versions 23 and later
        A ribbon (by default for a control created from version 23). This ribbon can be customized (see Customizing the ribbon).
        New in version 23
        A ribbon (by default for a control created from version 23). This ribbon can be customized (see Customizing the ribbon).
        A ribbon (by default for a control created from version 23). This ribbon can be customized (see Customizing the ribbon).
  • Other parameters:
    • Allow the management of several worksheets (4): This option is used to manage several worksheets in the Spreadsheet control. The tabs for managing the worksheets are displayed at the bottom left of Spreadsheet control. A popup menu allows the user to manage the worksheets. This option can be modified by ..MultiWorksheet.
      Note: This option is available from version 200057.

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 display the popup menu of 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. 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 Spreadsheet control, you must use specific internal windows.
To customize the toolbar:
  1. On the "Project" pane, in the "Project" group, expand "Import" and select "WINDEV elements and their dependencies".
  2. Select the "Programs\Data\Preset windows\AAF" directory.
  3. Select the windows:
    • WinDevAaf_SelBorderPSHEET.wdw: Window used to select the border options for the cells found in the Spreadsheet control.
    • WinDevAaf_ColorSel.wdw: Window used to select the color of the font.
    • IW_WinDevAaf_BarPSHEET.wdw: Window corresponding to the toolbar.
  4. Validate.
  5. Customize the "IW_WinDevAaf_BarPSHEET.wdw" window according to your needs.
    Caution: the names of the controls must not be modified.
If the Spreadsheet control is using a toolbar ("With toolbar" option in the "Details" tab of control description or ..ToolbarVisible), the custom toolbar will be used.
Note: This feature is available from version 200057.

Customizing the formula bar

To customize the formula bar, all you have to do is re-create it.
To create a custom formula bar:
  1. Create two controls:
    • A Static 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. By programming:
    • Specify the role of first two controls (viewing the coordinates or typing the formula) via SpreadsheetControlFormula. For example:
      // -- Initialization code of Spreadsheet control
      // Removes the default formula bar
      PSHEET_MySpreadsheet..FormulaBarVisible = False
      // Defines the control used to view
      // the coordinates of the cell selected in the Spreadsheet control
      SpreadsheetControlFormula(PSHEET_MySpreadsheet, psheetfSelection, STC_Selection)
      // Defines the control used to enter the formulas in the Spreadsheet control
      SpreadsheetControlFormula(PSHEET_MySpreadsheet, psheetfFormula, EDT_Formula)
    • Define the actions of these two buttons with ExecuteAAF. For example:Define the actions of these two buttons with AAFExecute. For example:
      // -- Click code of button used to validate the formula
      ExecuteAAF(PSHEET_MySpreadsheet, aafValidateInputFunc)
      // -- Click code of button used to cancel the formula
      ExecuteAAF(PSHEET_MySpreadsheet, aafCancelInputFunc)
      // -- Click code of button used to validate the formula
      // Use ExecuteAAF or AAFExecute
      AAFExecute(PSHEET_MySpreadsheet, aafValidateInputFunc)
      // -- Click code of button used to cancel the formula
      // Use ExecuteAAF or AAFExecute
      AAFExecute(PSHEET_MySpreadsheet, aafCancelInputFunc)
Caution: To use a custom formula bar, the default formula bar must be disabled:
  • in the editor: "With formula input bar" option in the "Details" tab of control description.
  • by programming: ..FormulaBarVisible.
Note: This feature is available from version 200057.
Versions 23 and later

Customizing the ribbon

To customize the ribbon of Spreadsheet control, you must use specific internal windows.
To customize the ribbon:
  1. Display the "Details" tab in the description window of Spreadsheet control.
  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_SpreadsheetBar" window is automatically included in your project. This internal window (available in English and in French) can be modified according to your needs. This window contains the code required to manage the Spreadsheet control. We advise you to check the GUI errors as well as the operating mode of window after any modification.
Note: You also have the ability to use an internal window to propose specific ribbon. Any internal window of your project can be used to create this bar.
New in version 23

Customizing the ribbon

To customize the ribbon of Spreadsheet control, you must use specific internal windows.
To customize the ribbon:
  1. Display the "Details" tab in the description window of Spreadsheet control.
  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_SpreadsheetBar" window is automatically included in your project. This internal window (available in English and in French) can be modified according to your needs. This window contains the code required to manage the Spreadsheet control. We advise you to check the GUI errors as well as the operating mode of window after any modification.
Note: You also have the ability to use an internal window to propose specific ribbon. Any internal window of your project can be used to create this bar.

Customizing the ribbon

To customize the ribbon of Spreadsheet control, you must use specific internal windows.
To customize the ribbon:
  1. Display the "Details" tab in the description window of Spreadsheet control.
  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_SpreadsheetBar" window is automatically included in your project. This internal window (available in English and in French) can be modified according to your needs. This window contains the code required to manage the Spreadsheet control. We advise you to check the GUI errors as well as the operating mode of window after any modification.
Note: You also have the ability to use an internal window to propose 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 required version
  • Version 20
Comments
Click [Add] to post a comment