- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
-
Features
- Workbook
- Worksheet
- Rows and Columns
- Headers
- Cells
- Data Binding
- TableSheet
- GanttSheet
- ReportSheet
- JSON Schema with SpreadJS
- SpreadJS File Format
- Data Validation
- Conditional Formatting
- Sort
- Group
- Formulas
- Serialization
- Keyboard Actions
- Shapes
- Form Controls
- Floating Objects
- Barcodes
- Charts
- Sparklines
- Tables
- Pivot Table
- Slicer
- Theme
- Culture
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
Parameter UI
ReportSheet can use a host to set values for parameters to display and organize parameters in the user interface. The parameters UI allows users to input values or make selections that affect the data displayed in the report. You can customize the appearance and behavior of the parameters UI for a ReportSheet using the setParametersUI method in the GC.Spread.Report.ReportSheet class.
The setParametersUI method takes three parameters:
parameterUIHost: Represents the host element or host ID where the parameters UI will be displayed.
initParametersUI: Represents the callback function that modifies the parameter UI sheet.
onChanged: Represents the callback function that is called when the parameter UI value changes or a button is clicked.
Note: Before setting up the parameter UI, you must create a parameter value and set the filter condition with the parameter in the ReportSheet.
You can use the following code sample to set the parameterUI.
// Set parametersUI
let parameterUIHost = document.getElementById("ss");
reportSheet.setParametersUI(parameterUIHost, initParametersUI, onChanged);
function onChanged (sheet, changedArgs) {
if (changedArgs.tag === "submitButton") { // on "Submit" button click, refresh report.
sheet.refresh();
}
};
function initParametersUI (sheet) {
sheet.suspendPaint();
sheet.suspendEvent();
initSheetStyle(sheet);
addComboBox(sheet);
addDataTimePicker(sheet);
addButton(sheet);
sheet.resumeEvent();
sheet.resumePaint();
}
function initSheetStyle (sheet) {
sheet.getCell(-1, -1).backColor("rgb(225, 225, 225)");
sheet.setColumnWidth(0, 5);
sheet.setColumnWidth(1, 220);
sheet.setColumnWidth(2, 10);
sheet.setColumnWidth(3, 220);
sheet.setColumnWidth(4, 10);
sheet.setColumnWidth(5, 220);
sheet.setColumnWidth(6, 10);
sheet.setColumnWidth(7, 105);
sheet.setRowHeight(0, 10);
sheet.setRowHeight(1, 30);
sheet.setRowHeight(2, 30);
let row1Style = new GC.Spread.Sheets.Style();
row1Style.font = '12px "Open Sans"';
let row2Style = new GC.Spread.Sheets.Style();
row2Style.textIndent = 1;
row2Style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
row2Style.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
row2Style.font = '12px "Open Sans"';
sheet.setStyle(1, -1, row1Style);
sheet.setStyle(2, -1, row2Style);
sheet.setValue(1, 1, "CustomerID:");
sheet.setValue(1, 3, "StartDate:");
sheet.setValue(1, 5, "EndDate:");
}
function addComboBox (sheet) {
let combo = new GC.Spread.Sheets.CellTypes.ComboBox();
combo.items([
{ text: "VINET", value: "VINET" },
{ text: "TOMSP", value: "TOMSP" },
{ text: "SUPRD", value: "SUPRD" },
]);
combo.editable(false);
sheet.getCell(2, 1).backColor("rgb(214, 214, 214)").bindingPath("customerId").tag("customerId").cellType(combo);
}
function addDataTimePicker (sheet) {
let startDateStyle = new Style();
startDateStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
command: "openDateTimePicker",
useButtonStyle: false,
buttonBackColor: "rgb(214, 214, 214)",
hoverBackColor: "rgb(214, 214, 214)",
}
];
style.backColor = "rgb(214, 214, 214)";
style.formatter = "yyyy-mm-dd";
sheet.getCell(2, 3).bindingPath("startDate").tag("startDate").setStyle(startDateStyle);
let endDateStyle = new Style();
endDateStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
command: "openDateTimePicker",
useButtonStyle: false,
buttonBackColor: "rgb(214, 214, 214)",
hoverBackColor: "rgb(214, 214, 214)",
}
];
style.backColor = "rgb(214, 214, 214)";
style.formatter = "yyyy-mm-dd";
sheet.getCell(2, 5).bindingPath("endDate").tag("endDate").setStyle(endDateStyle);
}
function addButton (sheet) {
let submitButton = new GC.Spread.Sheets.CellTypes.Button(); // add submit button
submitButton.text("Submit");
submitButton.buttonBackColor("#00897B");
submitButton.buttonClickColor("#004D40");
// Add tag into this cellButton cell to distinguish the different cell button.
sheet.getCell(2, 7).tag("submitButton").foreColor("white").vAlign(GC.Spread.Sheets.VerticalAlign.center).hAlign(GC.Spread.Sheets.HorizontalAlign.center).cellType(submitButton);
}Using Designer
Follow the steps below to design the parameter UI in ReportSheet.
Insert a ReportSheet, create a parameter, and set the filter condition with the created parameter.
Click on Design ParameterUI from the REPORT SHEET DESIGN tab.
The PARAMETER PANEL DESIGN tab opens with the ParameterUI area.
Switch to the HOME tab ribbon and set styles for ParameterUI, including cellButton (DateTimePicker), cellType (Button, Combo, Checkbox), etc.
Apply styles to ParameterUI Cell.
Select a cell and click on the cell corner SmartTag to access cell options.
Change the cell type to CellButton and enable the Submit OnChange checkbox.
In ReportSheet preview mode, click the Submit button to regenerate the report.
Click on the ReportSheet Area to switch activeSpread to ReportSheet spread.
Click the Preview button to switch to preview mode.
Change values for CustomerID, StartDate, EndDate and click the Submit button to regenerate the report with the new parameter values.
