Creating the control

1. On the "Creation" pane, in the "Graphic controls" group, expand "Office" and select "Spreadsheet".
2. Click where the control will be created in the window.

Versions 20 and later
Remark: The dimensions of the control are optimized to take up the available space at the specified position. If the control size does not suit you, press Ctrl + Z: the control size will be reset to default.

New in version 20
Remark: The dimensions of the control are optimized to take up the available space at the specified position. If the control size does not suit you, press Ctrl + Z: the control size will be reset to default.

Remark: The dimensions of the control are optimized to take up the available space at the specified position. If the control size does not suit you, press Ctrl + Z: the control size will be reset to default.

Remarks

• 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 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. See Automatic Application Features (AAF) of Spreadsheet control for more details.
• The maximum values of Spreadsheet control are:
  • 16384 columns.
  • 1048576 rows.
  • 4096 worksheets.

• 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

• 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 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:
  • Height of row: Used to set the height of a row in the worksheet. The "Margins" button allows you to set the cell margins, if necessary.
  • Allow 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.

Initializing 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 or ribbon.
5. The values typed are automatically saved with the control.

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

Customizing the 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:
   • Indicate the role of the 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 selected cell 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 AAFExecute. For example:
     // -- Click code of button used to validate the formula
     // Use of AAFExecute
     AAFExecute(PSHEET_MySpreadsheet, aafValidateInputFunc)
     
     // -- Click code of button used to cancel the formula
     // Use of AAFExecute
     AAFExecute(PSHEET_MySpreadsheet, aafCancelInputFunc)
     

• in the editor: "With formula input bar" option in the "Details" tab of the control description.
• by programming: ..FormulaBarVisible property.