PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
  • 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
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
A Spreadsheet control found in a window can be handled through programming.
To handle a Spreadsheet control through programming, WINDEV proposes the Spreadsheet functions.
This help page explains how to handle a Spreadsheet control found in a window through programming.
Remark: The method for handing a Spreadsheet control found in a report is different. For more details, see Handling a Spreadsheet control through programming (in a report).
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 <Spreadsheet>.AddData function.
    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 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:
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:
<Spreadsheet>.AddNameSpecifies a name to identify a cell found in a Spreadsheet control.
SpreadsheetListNameReturns the list of names that identify a cell in a Spreadsheet control.
SpreadsheetModifyNameModifies the name used to identify a cell in a Spreadsheet control.
SpreadsheetDeleteNameDeletes one of the names used to identify a cell found 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:
    PositionName of the associated cell.
    This property is read-only.
    RectangleRectangle 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.
    TextComment 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:
    SpreadsheetListCommentLists all the comments on the current worksheet of a Spreadsheet control.
    SpreadsheetSelectCommentAllows 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 (<Spreadsheet>.Load).
  • Several functions are used to handle the different worksheets:
    <Spreadsheet>.AddWorksheetAdds a worksheet into a Spreadsheet control found in a window.
    <Spreadsheet>.InsertWorksheetInserts a worksheet into a Spreadsheet control found in a window.
    SpreadsheetListWorksheetReturns the list of worksheets found in a Spreadsheet control.
    <Spreadsheet>.SelectWorksheetSelects a worksheet (identified by its name) in a Spreadsheet control.
    <Spreadsheet>.DeleteWorksheetDeletes a worksheet from a Spreadsheet control found in a window.
  • Several properties are used to handle the different worksheets:
    CurrentWorksheetThe CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control.
    MultiWorksheetThe MultiWorksheet property is used to:
    • Find out whether a Spreadsheet control manages several worksheets.
    • Change the worksheet management mode of a Spreadsheet control.
    NbWorksheetThe NbWorksheet property is used to get the number of worksheets in a Spreadsheet control.
    WorksheetNameThe 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.
FormulaBarVisibleThe 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.
ToolbarVisibleThe 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.
FilePathThe 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.
HeaderVisibleThe 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.
CurrentWorksheetThe CurrentWorksheet property is used to get and change the current worksheet in a Spreadsheet control.
MultiWorksheetThe MultiWorksheet property is used to:
  • Find out whether a Spreadsheet control manages several worksheets.
  • Change the worksheet management mode of a Spreadsheet control.
NbWorksheetThe NbWorksheet property is used to get the number of worksheets in a Spreadsheet control.
WorksheetNameThe WorksheetName property is used to get or change the name of the current worksheet in a Spreadsheet control.
GridlinesVisibleThe GridlinesVisible property is used to:
  • Determine whether or not gridlines are visible in a control.
  • Show or hide gridlines in a control.
InputFormulaThe 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.
InputValueThe 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.
For a complete list of WLanguage properties that can be used with a Spreadsheet control and with its cells, see Properties associated with the Spreadsheet control.
Minimum version required
  • Version 23
Comments
Click [Add] to post a comment