|
|
|
|
|
|
|
|
|
|
- Overview
- Manipulating Spreadsheet controls programmatically
- Initializing a Spreadsheet control
- Handling the Spreadsheet control
- Handling the cells of a Spreadsheet control programmatically
- Handling the cells of the Spreadsheet control
- Tip: Merge cells programmatically
- Managing the names of cells in a Spreadsheet control
- Managing comments in a Spreadsheet control
- Spreadsheet control: programmed handling of a file containing several spreadsheets
- Properties specific to Spreadsheet controls
Manipulating Spreadsheet controls programmatically (in a window) (prefix syntax)
Spreadsheet controls can be manipulated programmatically. This help page explains how to manipulate a Spreadsheet control in a window from the code. Manipulating Spreadsheet controls programmatically Initializing a Spreadsheet control Spreadsheet control fields can be initialized with values, formulas, etc. as soon as they are displayed, using various methods: Handling the Spreadsheet control To manipulate a Spreadsheet control through programming, all you have to do is specify its name. For example: Handling the cells of a Spreadsheet control programmatically Handling the cells of the Spreadsheet control To manipulate a cell in a Spreadsheet control by programming, use the following notations: <Spreadsheet control name>[<Row>, <Column>] or <Spreadsheet control name>["<CellName>"] Examples:
PSHEET_Spreadsheet[1,3].BackgroundColor = LightRed
PSHEET_Spreadsheet["B1"].BackgroundColor = LightGreen
To assign a value to a cell through programming, you can use: - the <Spreadsheet>.AddData function.
Example: Adding a value into the "A2" cell:
TBLR_Tableur.AjouteDonnée(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: 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: Merge cells programmatically The end user can merge the selected cells using the ribbon (or the toolbar) of the Spreadsheet control. To merge cells selected by programming, simply use function ExecuteAAF with constant faaTableurFusion:
TBLR_MonTableur.SelectPlus("A1", "C5")
ExecuteAAF(TBLR_MonTableur, 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 gets and sets 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:
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:
| | | <Spreadsheet>.ListComment | Crea una lista de todos los comentarios de la hoja actual de un control Hoja de cálculo. | | <Spreadsheet>.SelectComment | Permite: - saber si se seleccionó un comentario en un control Hoja de cálculo,
- seleccionar o deseleccionar un comentario en un control Hoja de cálculo.
|
Spreadsheet control: programmed handling of a file containing several spreadsheets To enable the management of several worksheets: - In the editor: In the "Detail" tab of the control description window, check the "Permitir varias hojas de trabajo" option.
- 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 (<Spreadsheet>.Load).
- Several functions are used to handle the different worksheets:
- Several properties are used to handle the different worksheets:
| | | CurrentWorksheet | La propiedad CurrentWorksheet obtiene y establece la hoja de trabajo actual en un control Hoja de cálculo. | | MultiWorksheet | La propiedad MultiWorksheet permite: - Averigüe si un control Hoja de cálculo gestiona varias hojas de cálculo..
- Cambiar el modo de gestión de las hojas de cálculo de un control Hoja de cálculo.
| | 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 specific to programmatic Spreadsheet control..
| | | Comment | The Comment property gets and sets the different characteristics of comments in a cell of a Spreadsheet control. | | CurrentWorksheet | La propiedad CurrentWorksheet obtiene y establece la hoja de trabajo actual en un control Hoja de cálculo. | | FilePath | La propiedad FilePath permite obtener: - el nombre del archivo xlsx asociado a un control Hoja de cálculo.
- el nombre del archivo asociado a un control Editor de imágenes.
- el nombre del archivo PDF asociado a un control Lector PDF.
- el nombre del archivo DOCX asociado a un control Procesador de texto.
- el nombre del archivo wddiag asociado a un control Editor de diagramas.
| | FormulaBarVisible | La propiedad FormulaBarVisible permite: - Determinar si se muestra una barra de fórmulas en un control Hoja de cálculo.
- Mostrar u ocultar una barra de fórmulas en un control Hoja de cálculo.
| | GridlinesVisible | La propiedad GridlinesVisible se utiliza para: - Determina si las líneas de cuadrícula son visibles o no en un control..
- Mostrar u ocultar líneas de cuadrícula en un control.
| | HeaderVisible | La propiedad HeaderVisible permite: - Determinar si se muestran las cabeceras de fila y columna en un control Hoja de cálculo.
- Muestra u oculta las cabeceras de fila y columna en un control Hoja de cálculo.
| | 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 | La propiedad MultiWorksheet permite: - Averigüe si un control Hoja de cálculo gestiona varias hojas de cálculo..
- Cambiar el modo de gestión de las hojas de cálculo de un control Hoja de cálculo.
| | NbWorksheet | The NbWorksheet property is used to get the number of worksheets in a Spreadsheet control. | | ToolbarVisible | La propiedad ToolbarVisible permite: - determinar si la barra de herramientas o la cinta de opciones se muestra en un control.
- mostrar u ocultar la barra de herramientas o la cinta de opciones en un control.
| | WorksheetName | The WorksheetName property is used to get or change the name of the current worksheet in a Spreadsheet control. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|