|
- Overview
- Handling the Spreadsheet control by programming
- Initializing a Spreadsheet control
- Handling the Spreadsheet control
- Handling the cells of a Spreadsheet control by programming
- Handling the cells of the Spreadsheet control
- Tip: Merging cells by programming
- Managing the names of cells in a Spreadsheet control
- Managing comments in a Spreadsheet control
- Spreadsheet control: Handling a file containing several worksheets by programming
- Properties specific to the Spreadsheet controls
Handling a Spreadsheet control by programming (in a window) (prefixed syntax)
A Spreadsheet control found in a window can be handled by programming. This help page explains how to handle a Spreadsheet control found in a window by programming. Handling the Spreadsheet control by 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 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 a Spreadsheet control by programming Handling the cells of the Spreadsheet control To handle a cell of a Spreadsheet control by 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 by programming, you can use: - <Spreadsheet>.AddData.
Example: Adding a value into the "A2" cell:
PSHEET_Spreadsheet.AddData(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 by programming The end user can merge the selected cells using the ribbon (or the toolbar) of the Spreadsheet control. To merge the selected cells by programming, simply use ExecuteAAF associated with the constant aafSpreadsheetMerge:
PSHEET_MySpreadsheet.SelectPlus("A1", "C5") ExecuteAAF(PSHEET_MySpreadsheet, aafSpreadsheetMerge)
Remark: <Spreadsheet>.GetMerge is used to find out whether a cell is merged with other cells and to retrieve the range of merged cells. Managing the names of cells in a Spreadsheet control The Spreadsheet control proposes several functions for handling the names of cells:
Versions 25 and laterManaging 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 available in read-only. | Rectangle | Variable of type Rectangle allowing to define the rectangle in which the comment is displayed.. The properties ..Width and ..Height determine the size of the rectangle (in millimeters), and the properties ..X and ..Y determine the position of the comment (in millimeters) relative to the beginning of the current sheet. | Text | Comment text. | Visible | - True to display the comment,
- False to make comment invisible. In this case, a red triangle in the top 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.
|
New in version 25Managing 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 available in read-only. | Rectangle | Variable of type Rectangle allowing to define the rectangle in which the comment is displayed.. The properties ..Width and ..Height determine the size of the rectangle (in millimeters), and the properties ..X and ..Y determine the position of the comment (in millimeters) relative to the beginning of the current sheet. | Text | Comment text. | Visible | - True to display the comment,
- False to make comment invisible. In this case, a red triangle in the top 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.
|
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 available in read-only. | Rectangle | Variable of type Rectangle allowing to define the rectangle in which the comment is displayed.. The properties ..Width and ..Height determine the size of the rectangle (in millimeters), and the properties ..X and ..Y determine the position of the comment (in millimeters) relative to the beginning of the current sheet. | Text | Comment text. | Visible | - True to display the comment,
- False to make comment invisible. In this case, a red triangle in the top 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 by programming To enable the management of several worksheets: - In the editor: In the "Details" tab of the control description window, check "Allow management of several worksheets".
- By programming, use ..MultiWorksheet.
Once the management of several worksheets was enabled: - You have the ability to load the different worksheets of an XLSX file (<Spreadsheet>.Load).
- Several functions are used to handle the different worksheets:
- Several properties are used to handle the different worksheets:
| | CurrentWorksheet | ..CurrentWorksheet is used to find out and modify the current worksheet in a Spreadsheet control. | 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.
| NbWorksheet | ..NbWorksheet is used to find out the number of worksheets in a Spreadsheet control. | WorksheetName | ..WorksheetName is used to find out and modify 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 the Spreadsheet controls The following properties are used to manage a Spreadsheet control by programming.
| | CurrentWorksheet | ..CurrentWorksheet is used to find out and modify the current worksheet in a Spreadsheet control. | FilePath | ..FilePath is used to find out: | 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.
| 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.
| 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.
| 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.
| 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.
| 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.
| NbWorksheet | ..NbWorksheet is used to find out the number of worksheets in a Spreadsheet control. | ToolbarVisible | ..ToolbarVisible is used to: - Find out whether the control ribbon is displayed.
- Display (or not) a ribbon for the control.
| WorksheetName | ..WorksheetName is used to find out and modify 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, - ...
|
|
|
|
| |
| Click [Add] to post a comment |
|
| |
|
| |
| |
| |
| |
| |
| |
| | |
| |