PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • Handling the Spreadsheet control by programming
  • Handling the Spreadsheet control
  • Handling the cells of the Spreadsheet control
  • Tip: Merging cells by programming
  • Initializing a Spreadsheet control
  • Managing the names of cells in a Spreadsheet control
  • Spreadsheet control: Handling a file containing several worksheets by 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview
A Spreadsheet control found in a window can be handled by 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 by programming.
Note: The method for handing a Spreadsheet control found in a report is different. See Handling a Spreadsheet control by programming (in a report) for more details.
Handling the Spreadsheet control by programming

Handling the Spreadsheet control

To handle a Spreadsheet control by programming, all you have to do is specify its name.
For example:
// Displays the formula bar
PSHEET_Spreadsheet..FormulaBarVisible = True
Several WLanguage functions and properties can be used to handle the Spreadsheet control.

Handling the cells of the Spreadsheet control

To handle a cell of a Spreadsheet control by programming, use the following notations:
<Name of Spreadsheet Control>[<Row>, <Column>]
or
<Name of Spreadsheet Control>["<CellName>"]
Examples:
// Colors the C1 cell in red
PSHEET_Spreadsheet[1,3]..BrushColor = LightRed

// Colors the B1 cell in green
PSHEET_Spreadsheet["B1"]..BrushColor = LightGreen
To assign a value to a cell by programming, you have the ability to use:
  • SpreadsheetAddData.
    Example: Adding a value into the "A2" cell:
    SpreadsheetAddData(PSHEET_Spreadsheet, 2, 1, 12)
  • the direct assignment:
    • via the cell.
      Example: Adding a value into the "A2" cell:
      PSHEET_Spreadsheet["A2"] = 12
    • via the column and row of the cell.
      Example: Adding a value into the "A2" cell:
      PSHEET_Spreadsheet["A"][2] = 12
Notes:
  • 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:
// Browse the cells of a Spreadsheet control
sMyString is string
FOR i = 1 TO PSHEET_MySpreadsheet..NumberRow
FOR y = 1 TO PSHEET_MySpreadsheet..NumberColumn
sMyString += PSHEET_MySpreadsheet[i][y] + TAB
END
Trace(sMyString + CR)
sMyString = ""
END

Tip: Merging cells by programming

The end user has the ability to merge the selected cells via the toolbar of the Spreadsheet control.
To merge the selected cells by programming, all you have to do is use ExecuteAAF associated with the aafSpreadsheetMerge constant:
SpreadsheetSelectPlus(PSHEET_MySpreadsheet, "A1", "C5")
ExecuteAAF(PSHEET_MySpreadsheet, aafSpreadsheetMerge)
Note: SpreadsheetGetMerge is used to find out whether a cell is merged with other cells and to retrieve the range of merged cells.

Initializing a Spreadsheet control

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

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.
Spreadsheet control: Handling a file containing several worksheets by programming
From version 200057, the management of files containing several worksheets has become available.
To enable the management of several worksheets:
  • In the editor: In the "Details" tab of the description window of the control, check "Allow the management of several 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 by programming.
Versions 20 and later
CurrentWorksheet
New in version 20
CurrentWorksheet
CurrentWorksheet
..CurrentWorksheet is used to find out and modify the current worksheet in a Spreadsheet control.
Versions 23 and later
FilePath
New in version 23
FilePath
FilePath
..FilePath is used to find out:
  • the name of xlsx file associated with a Spreadsheet control. This name is initialized by SpreadsheetLoad and SpreadsheetSave.
  • the name of file associated with an Image Editor control. This name is initialized by PicOpen and PicSave.
  • the name of PDF file associated with a PDF Reader control. This name is initialized by PDFReaderOpen.
  • the name of 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
..FormulaBarVisible 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
..GridlinesVisible 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
..HeaderVisible 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
..InputFormula is used to:
  • find out whether the end user can enter or modify formulas in a Spreadsheet control.
  • allow (or not) the end user to enter or modify formulas in a Spreadsheet control.
Versions 20 and later
InputValue
New in version 20
InputValue
InputValue
..InputValue is used to:
  • find out whether the end user can enter or modify values in a Spreadsheet control.
  • allow or not the end user to enter or modify values in a Spreadsheet control.
Versions 20 and later
MultiWorksheet
New in version 20
MultiWorksheet
MultiWorksheet
..MultiWorksheet is used to:
  • Find out whether a Spreadsheet control manages several worksheets.
  • Modify the mode for managing the worksheets in a Spreadsheet control.
Versions 20 and later
NbWorksheet
New in version 20
NbWorksheet
NbWorksheet
..NbWorksheet is used to find out the number of worksheets in a Spreadsheet control.
Versions 20 and later
ToolbarVisible
New in version 20
ToolbarVisible
ToolbarVisible
..ToolbarVisible is used to
  • in a Spreadsheet control
    • Find out whether the toolbar is displayed
    • Display (or not) the toolbar.
  • in a Word Processing control
    • Find out whether the ribbon is displayed
    • Display (or not) a ribbon.
Versions 20 and later
WorksheetName
New in version 20
WorksheetName
WorksheetName
..WorksheetName is used to find out and modify 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 required version
  • Version 20
Comments
Click [Add] to post a comment