- 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 Spreadsheet controls
Handling a Spreadsheet control through programming (in a window)
A Spreadsheet control found in a window can be handled through programming. This help page explains how to handle a Spreadsheet control found in a window through programming. 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:
// 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 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:
<Spreadsheet control name>[<Row>, <Column>]
or
<Spreadsheet control name>["<CellName>"]
Examples:
// Colors the C1 cell in red
PSHEET_Spreadsheet[1,3].BackgroundColor = LightRed
// Colors the B1 cell in green
PSHEET_Spreadsheet["B1"].BackgroundColor = LightGreen
To assign a value to a cell through programming, you can use: - the SpreadsheetAddData function.
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
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:
// 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 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(PSHEET_MySpreadsheet, "A1", "C5")
ExecuteAAF(PSHEET_MySpreadsheet, aafSpreadsheetMerge)
Managing the names of cells in a Spreadsheet control The Spreadsheet control proposes several functions for handling the names of cells:
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 Comment advanced property. This property is used to find out and modify the comments associated to a cell via the following properties:
| | | Position | Name of the associated cell. This property is read-only. | | Rectangle | Rectangle variable. Defines the rectangle in which the comment is displayed. The Width and Height properties determine the size of the rectangle (in millimeters), while X and Y determine the position of the comment (in millimeters with respect to the beginning of the current sheet. | | Text | Comment text. | | Visible | - True to show the comment,
- False to make the comment invisible. In this case, a red triangle in the upper-right corner of the cell will let the user know that the cell is associated with a comment.
|
Example:
// Create a comment
PSHEET_MySpreadsheet["B1"].Comment.Text = "Text of my comment"
PSHEET_MySpreadsheet["B1"].Comment.Visible = True
PSHEET_MySpreadsheet["B1"].Comment.Rectangle.Height = 50
PSHEET_MySpreadsheet["B1"].Comment.Rectangle.Width = 50
PSHEET_MySpreadsheet["B1"].Comment.Rectangle.X = 50
PSHEET_MySpreadsheet["B1"].Comment.Rectangle.Y = 50
- via different WLanguage functions:
| | | SpreadsheetListComment | Lists all the comments on the current worksheet of a Spreadsheet control. | | 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".
- Through programming, use the MultiWorksheet property.
Once the management of several worksheets was enabled: - You have the ability to load the different worksheets of an XLSX file (SpreadsheetLoad).
- Several functions are used to handle the different worksheets:
- Several properties are used to handle the different worksheets:
| | | CurrentWorksheet | The CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control. | | 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.
| | NbWorksheet | The NbWorksheet property is used to get the number of worksheets in a Spreadsheet control. | | WorksheetName | The WorksheetName property is used to get or change the name of the current worksheet in a Spreadsheet control. |
- Several AAFs (Automatic Application Features) allows the user to manage the different worksheets.
Properties specific to Spreadsheet controls The following properties are used to manage a Spreadsheet control through programming.
| | | CurrentWorksheet | The CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control. | | FilePath | The FilePath property is used to identify: - the name of the xlsx file associated with a Spreadsheet control.
- the name of the file associated with an Image Editor control.
- the name of the PDF file associated with a PDF Reader control.
- the name of the DOCX file associated with a Word Processing control.
- the name of the wddiag file associated with a Diagram Editor control.
| | 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.
| | GridlinesVisible | The GridlinesVisible property is used to: - Determine whether or not gridlines are visible in a control.
- Show or hide gridlines in a control.
| | HeaderVisible | The HeaderVisible property is used to: - Determine if the row and column headers are displayed in a Spreadsheet control.
- Show or hide the row and column headers in a Spreadsheet control.
| | 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.
| | 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.
| | 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.
| | NbWorksheet | The NbWorksheet property is used to get the number of worksheets in a Spreadsheet control. | | 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.
| | WorksheetName | The WorksheetName property is used to get or change the name of the current worksheet in a Spreadsheet control. |
Related Examples:
|
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, - ...
|
|
|
|
|