|
- 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: - the function <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 cells selected by programming, simply use the ExecuteAAF function with the constant aafSpreadsheetMerge:
PSHEET_MySpreadsheet.SelectPlus("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:
 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 advanced property comment. 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 mode. | | 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 the 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 advanced property comment. 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 mode. | | 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 the 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 advanced property comment. 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 mode. | | 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 the 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 | The Property CurrentSheet allows to know and modify the current sheet of an Spreadsheet control. | | MultiWorksheet | The Property MultiFeuille allows to: - Find out whether a Spreadsheet control manages several worksheets.
- Modify the mode for managing the worksheets in a Spreadsheet control.
| | NbWorksheet | The Property NbSheet allows to know the number of worksheets in an Spreadsheet control. | | WorksheetName | The Property SheetName allows you to know or modify the name of the current worksheet in an 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 | The Property CurrentSheet allows to know and modify the current sheet of an Spreadsheet control. | | FilePath | The Property FilePath allows you to know: | | 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 Property GridVisible allows to: - Find out whether the gridlines are visible or not in a Spreadsheet control.
- Modify the visibility of gridlines in a Spreadsheet control.
| | HeaderVisible | The Property HeaderVisible allows 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 | The Property Formula Input allows you 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 | The Property Value Entry allows you 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 | The Property MultiFeuille allows to: - Find out whether a Spreadsheet control manages several worksheets.
- Modify the mode for managing the worksheets in a Spreadsheet control.
| | NbWorksheet | The Property NbSheet allows to know the number of worksheets in an Spreadsheet control. | | ToolbarVisible | The ToolbarVisible property is used to: - Find out whether the control ribbon is displayed.
- Display (or not) a ribbon for the control.
| | WorksheetName | The Property SheetName allows you to know or modify the name of the current worksheet in an 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 |
|
| |
|
| |
| |
| |
| |
| |
| |
| | |
| |