ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Developing an application or website / Controls, windows and pages / Controls: Available types / Spreadsheet control
  • Overview
  • "Home" tab
  • Performing a Find/Replace
  • "Insert" tab
  • Inserting an image
  • Inserting, browsing and viewing comments
  • "Formula" tab
  • Formula library
  • Using defined names
  • "Layout" tab
  • "View" tab
  • Encryption of xlsx files using a password
  • Protect the current worksheet
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
The ribbon of the Spreadsheet control proposes several features and it allows you to define several settings.
"Home" tab
The "Home" tab allows you to perform the most common operations on a ".xlsx" file.
Ribbon of Spreadsheet control
This tab contains the following groups:
  • Document: Used to open, create, save or print a ".xlsx" file.
    Options of the Document group
    You can:
    • Create a new workbook.
    • Open an existing workbook.
    • open a workbook template ("xltx" and "xltm" files). In this case, a new workbook is automatically created using this template.
    • Save (or save as) a workbook.
    • Print a workbook. The current worksheet is previewed.
  • Clipboard: Used to manage the clipboard (copy/paste).
    Options of the Clipboard group
  • Font: Used to manage the font of current text.
    Options of the Font group
    You can:
    • manage the font name and size.
    • manage the formatting characteristics of font: Bold, Italic, Underlined, Strikeout, Superscript, Subscript.
    • manage the font color: text color and highlighting color.
    • manage the cell borders.
    • manage the background color of cell.
  • Alignment: Used to manage the alignment of different elements:
    Options of the Alignment group
    • : Used to manage the vertical alignment of text in the cell.
    • : Used to manage the text rotation in the cell.
    • Automatic line wrap: used to manage the line wrap when the text exceeds the width specified for the cell.
    • : Used to manage the horizontal alignment of text in the cell.
    • Merge and center: Used to merge cells and to center the merge result.
  • Number: Used to manage the selection characteristics:
    Options of the Number group
    • Style to apply: standard, short date, currency, time, ...
    • display the currency unit,
    • display a percentage,
    • use the thousand separator,
    • add or remove a decimal.
  • Styles: Used to display a range of cells in array format.
  • Cells: Used to insert, delete or erase cells.
    You can:
    • insert cells, rows, columns or worksheets.
    • delete cells, rows, columns or worksheets.
    • erase everything, the formatting only or the content only.
  • Edit: Used to perform searches and replacements, and to sort data.

Performing a Find/Replace

To search in a spreadsheet:
  1. On the "Home" tab, in the "Editing" group, click "Find" (or press Ctrl + F).
  2. In the window that appears, specify:
    • the word, phrase or value to be found,
    • the search options: match case, in all worksheets, in the formulas.
  3. Start the search:
    • When you click "Find", the occurrences are highlighted and the first occurrence is selected.
      The "Next" button highlights the next element that matches the search.
    • When you click "Find all", the list of cells that match the search is displayed.
      When you select one of the occurrences in the search window, it is also selected in the worksheet.
To find and replace text in a document:
  1. On the "Home" tab, in the "Editing" group, click "Replace" (or press Ctrl + H).
  2. In the window that appears, specify:
    • the word, phrase or value to be found,
    • the search options: match case, in all worksheets, in the formulas.
    • the new text.
  3. Start the search.
    • When you click "Find", the different occurrences are selected and the first occurrence is highlighted. The "Next" button highlights the next element that matches the search.
    • When you click "Find all", the list of cells that match the search is displayed.
  4. Choose "Replace" to replace only the selected occurrence.
  5. Choose "Replace all" to replace all the occurrences.
"Insert" tab
The "Insert" tab allows you to:
  • insert an image.
  • manage comments.

Inserting an image

To insert an image:
  1. Select a cell.
  2. On the "Insert" tab, in the "Content" group, click "Image".
  3. Select the desired image file and validate.
  4. The image is inserted at the cursor position. It can be selected to be moved or enlarged using the selection handles.
A context menu is available on the selected image. This context menu offers options specific to the selected image:
  • Image Z-order management (moving to the foreground or background).
  • Image change (from a file or image in the clipboard).
  • Image editing, using the image editor. A toolbar with the main features of the image editor appears above the image. It is also possible to open the image in the full image editor.

Inserting, browsing and viewing comments

In an excel sheet, you can associate a comment to each cell. This comment is a simple text displayed as a "tooltip".
A small red triangle in the upper-right corner of the cell lets the user know that a comment is associated with the cell. When the user clicks or hovers over the cell, the comment will be displayed as a tooltip.
Remark: The Spreadsheet control only manages "simple" comments (also called "Notes" in some versions of Excel).
To add a comment:
  1. Select a cell.
  2. On the "Insert" tab, in the "Comments" group, click "New comment".
  3. Enter the text of your comment in the area that appears. You can also resize and reposition the comment display area.
  4. To validate the comment, click another area of the spreadsheet.
Remark: You can also add a comment via "Add a comment", in the context menu of a cell (right click).
To edit a comment:
  1. Select a cell associated with a comment (with a small red triangle).
  2. On the "Insert" tab, in the "Comments" group, click "Edit comment".
  3. The comment is displayed. You can change the text, size and position of the comment.
  4. To validate the changes, click another area of the spreadsheet.
Remark: You can also edit a comment via "Edit comment", in the context menu of a cell (right click).
To browse through comments:
  1. Edit a comment:
    • Select a cell associated with a comment (with a small red triangle).
    • On the "Insert" tab, in the "Comments" group, click "Edit comment".
  2. Click:
    • "Previous comment", to display the previous comment in edit mode.
    • "Next comment", to display the next comment in edit mode.
To delete a comment:
  1. Select a cell associated with a comment (with a small red triangle).
  2. On the "Insert" tab, in the "Comments" group, click "Delete".
  3. The comment is deleted.
Remark: You can also delete a comment via "Delete comment", in the context menu of a cell (right click).
To show all the comments:
  1. On the "Insert" tab, in the "Comments" group, click "Show comments".
  2. All comments in the current worksheet are displayed.
To hide them, click "Show comments" again.
Remark: You can also hide or show a comment via "Show/Hide comment", in the context menu of a cell (right click).
"Formula" tab
The "Formulas" tab allows you to access all available calculations and formulas. This pane can also be used to manage the naming of cells.
This tab contains the following groups:
  • Library: This group proposes libraries of formulas, grouped by theme.
  • Defined names: This group allows you to manage the names given to the different cells.

Formula library

The Spreadsheet control offers many formulas that can be used in the cells of a Spreadsheet control.
To use the "Automatic sum" option, simply:
  • Select the range of cells to be added.
  • Click "Automatic sum": the sum is automatically calculated in the last empty cell of the selection.
To use one of the library formulas, simply:
  • Position yourself on an empty cell.
  • Click on the desired library to view the different formulas available.
  • Select the formula to use. The formula appears in the cell and the formula bar.
  • Complete the formula by clicking on the cells that must be taken into account.

Using defined names

The Spreadsheet control allows you to define names for the cells. It is thus possible to use these named cells in the formulas (instead of the traditional identification of the cells).
To name a cell:
  1. Select cell.
  2. Click "Define a name".
  3. In the window that appears, specify:
    • The name of the cell.
    • The scope of the name, i.e. in which elements this name can be used (sheet or workbook).
    • The cell description.
  4. Validate.
The name manager allows you to see all the named cells of the current xlsx file. to use the name manager:
  1. Click "Name manager".
  2. The window that appears shows the different named cells of the current document with their characteristics.
    You can:
    • Create a named cell.
    • Modify a named cell.
    • Delete a named cell.
"Layout" tab
The "Layout" tab allows you to set the layout of the xlsx file displayed in the Spreadsheet control. This layout is used especially when printing the file.
This tab contains the following groups:
  • "Layout": xlsx file layout management.
  • "Worksheet options": management of current sheet layout options:
    • Display and print the grid (or not).
    • Display and print the headers (or not).
The options in the "Layout" group allow you to:
  • Know the page format and customize it if necessary ( "Margins" option):
  • Modify the page orientation. The "Orientation" option allows you to switch from Portrait to Landscape mode.
  • Configure scaling.
"View" tab
The "Display" tab contains the display options of the xlsx file in the Spreadsheet control.
This tab contains the following groups:
  • Display: management of the formula bar, headers and grid.
  • Zoom: zoom management to display the xlsx file in the Spreadsheet control.
  • Freeze panes: management of a display with panes (useful for xlsx files displaying a large amount of data).
  • Encryption: file protection/encryption options.

Encryption of xlsx files using a password

It is possible to encrypt an xlsx file with a password.
To encrypt an xlsx file using a password:
  1. Click "Password".
  2. In the window that appears, enter and confirm the password.
  3. Validate. The password will be used the next time the file is saved.
Remark: When the xlsx file opens, the password will be automatically requested before the file is displayed.

Protect the current worksheet

You can prevent users from making changes to worksheet of an xlsx file with a password.
To protect the current worksheet of the xlsx file with a password:
  1. Click "Protect worksheet".
  2. In the window that appears, enter and confirm the password.
  3. Validate. The worksheet is protected.
To unprotect the current worksheet in an xlsx file:
  1. Click "Unprotect worksheet".
  2. In the window that appears, enter the protection password.
  3. Validate. The worksheet can be modified.
Minimum version required
  • Version 23
Comments
Click [Add] to post a comment

Last update: 06/30/2023

Send a report | Local help