- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
- Features
- SpreadJS Designer
-
SpreadJS Designer Component
- Getting Started
- Quick Start
- Designer Interface
- JavaScript Frameworks
- Customizations
-
Work in Designer Component
- AutoSum Functions
- Set Range Template Cell
- Name Box
- Select Cells Using Go To
- Find and Replace
- Edit Font Size
- Manage Conditional Formatting Rules
- Shape Selection Pane
- Automatic Outline
- Format Painter
- Table Slicer
- TableSheet
- GanttSheet Designer
- ReportSheet Designer
- Data Chart Designer
- Text to Columns
- Remove Duplicates
- Pivot Table
- Toolbar Ribbon
- API Documentation
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
ReportSheet Designer
SpreadJS Designer Component provides the option to insert a ReportSheet in a workbook.
The ReportSheet ribbon button is available in the INSERT > Sheets tab group. Clicking on the ribbon button adds a Report1 tab to the open workbook tabstrip and opens the REPORT SHEET DESIGN tab.
The below image depicts the elements of a report sheet design page.
Follow the steps below to create a new ReportSheet.
1. Import the template.
You can import an existing Excel or SJS file as a template to create a report using REPORT SHEET DESIGN > Import Template.
Load an existing file and select OK to show the imported file in the worksheet. If there are no worksheets in the imported file, a “No Valid Worksheets” message appears.
Note: The imported template retains its values and styles when loaded into worksheets.
2. Add DataSource to the ReportSheet.
You can create a data source by clicking the DataSource ribbon button in the DATA > DATA Binding tab group. The DataSource (TableList) panel also appears on the left side of the panel when you add a ReportSheet.
The DataSource tab appears, where you can add a table and set the remote URL for CRUD (create, read, update, and delete) capabilities in the Table tab.
You can also fetch the table data through the Columns tab as well.
Go to the Report tab. You can double-click the table tree node or choose the expand button to view the column names.
You can also enter the table or column name using the Search box to quickly locate a specific table or column data. When a match is found, it expands the corresponding table tree, highlighting the matched columns to make navigation easier. This feature is particularly useful for working with large datasets, as it saves time and effort in locating specific data. However, if no matching columns are found, the system will display a message as "No Result!", indicating that the search query does not correspond to any existing columns.
To add the columns in the ReportSheet, select the column names one by one or select multiple columns using the Ctrl key. However, if you want to select continuous column nodes, use the Shift key. Then, drag the selected column nodes and drop them into one report template cell to set all the selected nodes in the tableList according to the fill direction.
Notes:
If the table column count is out of the sheet’s range, an error “Invalid Range” appears and cancels the drop.
The default cell type for the template cell is Group Cell.
3. Set data binding and template cell properties.
ReportSheet provides a Report Cell panel on the right side where you can select the cell in the ReportSheet to configure the required report-related settings for the report template.
DataBinding: To manage the template cell type in the template sheet, you must configure the type and binding option in the template cell.
Feature | Designer | Remarks |
|---|---|---|
| When a data setting is applied to a template cell, it only affects the current cell's data. For example, if the template cell is ListCell, only the current cell's data is shown in list mode. | |
Data Column |
| If the table name is changed, the column names are also changed. |
| You can set filter conditions in the template cell to filter data as you join multiple tables. You can configure the data filtering condition in the template sheet using the below-mentioned settings. • Filter by Condition • Filter by Formula | |
| The template cell can be used to create sort rules for sorting records. You can reorder or remove the sort rules by clicking up/down/remove. | |
Default Value |
| A defaultValue option is a formula that lets you configure how to generate the default value for a newly added record in the report sheet. |
Expansion: To set spill direction, collapse specific cells, and add context cells, you must configure the below expansion options.
Feature | Designer | Description |
|---|---|---|
| The Spill direction controls how the data spills in the ReportSheet. The default value is Vertical. | |
| The showCollapseButton option controls whether to show the button that collapses the children of the cell in the report. The default value is false. Additionally, you can configure the initial expansion state of all grouped cells to either expand or collapse by selecting the Expanded or Collapsed options in the Initial State settings dropdown. | |
| The context cells option filters the data for all the child cells by default and also affects the report layout. The default value for context is Default. However, if you switch to Custom, a range selection dialog appears. |
Pagination: To configure the pagination settings in the template sheet, you can select one of the below options in the template cell.
As the report is paginated, the pagination setting of the template cell and the paper size are combined on the ReportSheet.
4. Configure template sheet cell styles.
A cell in a ReportSheet can be styled according to the corresponding template cell. The style set in the template sheet is expanded with the current cell expansion.
You can configure the styles by clicking the REPORT SHEET DESIGN > Dirty Data Style button. It opens the Format Cells dialog with various cell formatting options.
ReportSheet also supports customizing dirty cells, where new data or formulas are added, and the sheet marks those cells as recalculation is required and calls them dirty cells. By default, dirty cells are marked by a red corner fold on the right.
Note: If the template sheet is protected, you can still edit ReportSheet in Design mode. A protected ReportSheet only works in Preview mode.
5. Set data entry, pagination, and layout settings.
ReportSheet supports data entry through the DataManager. Using the Data Entry Setting button, you can configure which cell is written back to which column. Additionally, you can choose the value of parameter defined for any column, if any, by using a dropdown in the Field Value Editor dialog box.
The ReportSheet also supports formulas and static value fields.
Follow the steps below to configure data entry settings for a ReportSheet.
Click on Data Entry Setting button from REPORT SHEET DESIGN tab in Ribbon bar. The Data Entry Setting dialog box will appear.
In the Data Entry Setting dialog box, click the Add button to create a new data entry setting rule. You can delete the rule created by using the Delete button. Use the up/down arrow keys to change the order of rules.
Double click the “Write Back Rule 1” option to update the rule name.
Select the data entry table name from table dropdown and add data entry fields by clicking the Add Field button. You can also click Smart Add Fields to add all current table column’s bound in the report’s TemplateSheet.
Click the value column to change the value data of the entered fields.
If no parameters are added, there will be no dropdown in the Field Value Editor.
If there are parameters added, click the dropdown button to select the parameter as the value of the field.
Note- You must set one or more fields primaryKey as true, otherwise the data entry setting goes invalid.
Additionally, you can click on the Pagination Setting button to paginate your report using the ReportSheet Designer ribbon.
However, you must adjust the pagination settings in the template sheet.
You can also set desired parameters for configuring the layout of a report using the Layout Setting button.
6. Check the preview.
You can click the Preview or Paginate button to change the render mode. The default render mode of a ReportSheet is Design.
Design: Shows the template in the design mode.
Preview: Shows the preview and also supports data entry. You can also enter data using different editors.
Paginated: Shows the paginated preview, but this mode is read-only so the special appearance of the cell type is removed.
