Spreadsheet Control

• Apr 02, 2024
• 4 minutes to read

The ExpressSpreadSheet Control (the Spreadsheet control, for short) implemented as the TdxSpreadSheet component, is designed to work natively with spreadsheet documents in both the Microsoft Excel® (XLSX, XLTX, XLS, and XLT) and LibreOffice/OpenOffice Calc (ODS) formats and provide a friendly and familiar cell data editing and processing environment to end-users. In addition, the control can export spreadsheet data to the web page (HTML, HTM, and XML) and text-based (CSV and TXT) file formats.

The Spreadsheet control consists of the following visual elements:

• The View area that displays the currently visible portion of the active worksheet;

• The caption bar that lists all worksheets in the opened document as named tabs and provides the sheet management functionality;

• The horizontal and vertical scrollbars for the View area. The scrollbar positioning and appearance depends on the current scrollbar mode selected either via the LookAndFeel.ScrollbarMode property or globally. The splitter delimits the caption bar and the horizontal scrollbar located to the right of it in “Classic” mode. In both “Touch” and “Hybrid” modes, the horizontal scrollbar, when visible, is positioned on top of the caption bar, within the View area.

Each of the control’s visual elements, except for scrollbars, provides a built-in context menu with content-dependent commands currently available to end-users. You can duplicate most of these commands by linking the corresponding actions to specific elements of your UI either manually or automatically. The automatic UI generation functionality is designed for use together with the Spreadsheet and Formula Bar controls to quickly create a fully functional spreadsheet-based application from scratch as explained in the “How to Create a Ribbon or Toolbar UI for your Spreadsheet-based Application“ topic.

In addition to the extensive end user functionality available out-of-the-box, the Spreadsheet control provides a comprehensive public API that you can use to:

• Customize and localize the control’s built-in UI and dialogs;

• Create, load, edit, print, and save spreadsheet documents in supported formats;

• List, format, arrange, group, process, and analyze cell data;

• Manage defined names, floating containers, and comments;

• Apply custom formatting to data cells based on specific criteria by using the formula and conditional formatting functionality;

• Implement and register custom cell data processing functions and additional spreadsheet document formats.

The following table lists the control’s key API members corresponding to specific categories:

Contents	The Sheets and VisibleSheets properties provide indexed access to worksheets created in the Spreadsheet control. The ActiveSheet and ActiveSheetAsTable properties provide access to the currently active worksheet. The OptionsBehavior.History property enables the History functionality. Call the AddSheet function to create an additional sheet in the currently opened workbook.
Appearance	The LookAndFeel, DialogsLookAndFeel, PageControl, Styles, and OptionsView, and OptionsLockedStateImage properties. The LayoutChanged procedure. You can override individual Tabke View worksheets’ global appearance settings using the following options: The Options and OptionsPrint property sets; The Groups, Columns and Rows properties; Individual column and row objects’ Visible and Style properties. You can also assign individual cell objects‘s Style settings within table items.
Behavior	The OptionsBehavior and OptionsProtection property sets affect the Spreadsheet’s behavior on the global level. You can use its OptionsProtection property set for an individual worksheet. You can also customize a worksheet’s printing settings using the OptionsPrint property.
Layout	The PageControl, OptionsView.HorizontalScrollBar, OptionsView.VerticalScrollBar properties. The Table View worksheet’s Columns.Groups and Rows.Groups properties.
HitTest Information	The Table View worksheet’s HitTest property. The caption bar‘s HitTest property.

Refer to the following topics to get acquainted with the basic concepts of the Spreadsheet control’s most prominent features:

• Appearance Customization;

• Worksheet Management;

• History;

• Cells and Cell Objects;

• Cell Management;

• Cell Style Customization;

• Table Item and Table Items;

• Working with Rows and Coumns;

• Cell Data Grouping;

• Clipboard Operations;

• Operators and Functions;

• Array Formulas;

• Creating Formulas and Performing Calculations;

• Floating Containers;

• Comments;

• Supported Commands List;

• Supported Keystrokes;

• Password Protection;

• Export and Import Data.

The following topics include step-by-step instructions for specific Spreadsheet control use scenarios:

• How to Copy, Cut, and Paste Cell Data without the Clipboard;

• How to Create Custom Functions;

• How to Use Spreadsheet Control Commands;

• How to Work with Rich Formatted Content;

• How to Work with Defined Names;

• How to Custom Paint a Worksheet.