- 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
Data Entry Events
ReportSheet provides data entry events that can be bound when report data is changed or submitted. Using the data entry events, SpreadJS enables you to validate all the data changes or cancel operations during data entry. You can also receive the feedback result of the server and then see the result in the UI.
You can use the getChanges method to get ReportSheet updates and insert and delete record changes in data entry mode. The following code sample returns the update and delete changes of a ReportSheet.
const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
binding: "Orders[orderId]",
type: "Group",
});
templateSheet.setTemplateCell(0, 1, {
binding: "Orders[customerId]",
type: "Group",
});
templateSheet.setDataEntrySetting([ {
name: "Write Back Rule 1",
tableName: "Orders",
fields: [
{ dbColumnName: "orderId", formula: "A1", isPrimary: true },
{ dbColumnName: "customerId", formula: "B1" },
],
includeUnmodified: false,
skipRecordWithEmptyValue: false
} ]);
report.renderMode("Preview");
// Performing below operations in UI will trigger events.
report.updateCellValue(0, 1, "test");
report.addRecordAt(1, 0);
report.updateCellValue(2, 0, 111);
report.updateCellValue(2, 1, "test2");
report.deleteRecordAt(3, 0);
report.getChanges(); // one delete record and two update records with "Write Back Rule 1" dataEntrySetting.Note:
The getChanges method only collects the cell value in the data entry WriteBackRule. The info will collect the value from the ReportSheet cell. However, if the field value of the WriteBackRule is not a cell, the field info will be undefined, and the entity will show the collected whole record value.
The getChanges method returns all records collected by WriteBackRule. If there is no change, it returns the writeBackRule with a blank record and deletedRecord.
ReportSheet supports the below data entry events:
ReportSheetDataChanging and ReportSheetDataChanged
ReportSheetDataChanging: This event occurs when the report sheet is changing by update, insert or delete. It allows validation or cancellation of the data operation.
ReportSheetDataChanged: This event occurs when the report sheet data has changed by update, insert or delete. It allows the submission of the data changes to the server.
This example uses the ReportSheetDataChanging and ReportSheetDataChanged events.
const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
binding: "Orders[orderId]",
type: "Group",
showCollapseButton: true
});
templateSheet.setTemplateCell(0, 1, {
binding: "Orders[customerId]",
type: "Group",
});
templateSheet.setDataEntrySetting([ {
name: "Write Back Rule 1",
tableName: "Orders",
fields: [
{ dbColumnName: "orderId", formula: "A1", isPrimary: true },
{ dbColumnName: "customerId", formula: "B1" },
],
includeUnmodified: false,
skipRecordWithEmptyValue: false
} ]);
report.bind(GC.Spread.Sheets.Events.ReportSheetDataChanging, (event, args) => {
let { type, row, col, oldValue, newValue } = args;
if (allowChange(oldValue, newValue)) { // user validate this changing operation here.
console.log(`Changing row: ${row}, col: ${col} from ${oldValue} to ${newValue}`);
} else {
args.cancel = true; // user cancel this operation here.
}
});
report.bind(GC.Spread.Sheets.Events.ReportSheetDataChanged, (event, args) => {
let changes = report.getChanges(); // user can get all report changes here.
if (needSubmit(changes)) {
report.submit(); // submit changes.
} else {
report.refresh(); // restore report.
}
});
// Data entry operations by UI.
report.renderMode("Preview");
report.updateCellValue(0, 1, "test");
report.addRecordAt(1, 0);
report.updateCellValue(2, 0, 111);
report.updateCellValue(2, 1, "test2");
report.deleteRecordAt(3, 0);Note:
If the oldValue is the same as the newValue while editing the cell value, the ReportSheetDataChanging and ReportSheetDataChanged events will not trigger.
The actions, such as updating a cell (e.g., pressing the delete key, pasting, resetting the value), adding records, or deleting records in the UI will trigger the ReportSheetDataChanging and ReportSheetDataChanged events.
ReportSheetRecordsSubmitting and ReportSheetRecordsSubmitted
ReportSheetRecordsSubmitting: This event occurs before the report sheet submits changes to the server. It allows for final validation of all data changes or cancellation of the operation.
ReportSheetRecordsSubmitted: This event occurs after the report sheet submitted the changes to the server. It allows user to provide UI feedback of the submit results from the server.
This example uses the ReportSheetRecordsSubmitting and ReportSheetRecordsSubmitted events.
const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
binding: "Orders[orderId]",
type: "Group",
showCollapseButton: true
});
templateSheet.setTemplateCell(0, 1, {
binding: "Orders[customerId]",
type: "Group",
});
templateSheet.setDataEntrySetting([ {
name: "Write Back Rule 1",
tableName: "Orders",
fields: [
{ dbColumnName: "orderId", formula: "A1", isPrimary: true },
{ dbColumnName: "customerId", formula: "B1" },
],
includeUnmodified: false,
skipRecordWithEmptyValue: false
} ]);
// For some users want to validate the data before submitting to server. They can use the ReportSheetRecordsSubmitting event.
reportsheet.bind(GC.Spread.Sheets.Events.ReportSheetRecordsSubmitting, (event, args) => {
let changes = args.sheet.getChanges(); // get reportSheet all changes.
for (let change of changes) {
let { records, deleteRecords, rule } = change;
let updateRecordsData = records.map((r) => r.entity), deleteRecordsData = deleteRecords.map((r) => r.entity);
let tableName = rule.tableName;
if (isInvalidData(updateRecordsData, tableName)) { // users validate data.
//cancel the reportsheet submit operation.
args.cancel = true;
break;
}
}
});
// For some users want to customize the submit result callback, they can use the ReportSheetRecordsSubmitted event to get the successed and failed result.
reportsheet.bind(GC.Spread.Sheets.Events.ReportSheetRecordsSubmitted, (event, args) => {
let { updateSuccessRecords, deleteSuccessRecords, updateFailedRecords, deleteFailedRecords } = args;
for (let record of updateFailedRecords) {
for (let fieldKey in record.info) {
if (record.info.hasOwnproperty(fieldKey)) {
let recordDetail = record.info[fieldKey];
if (recordDetail.state === "updated") {
// user console the failed detail info.
console.log(`Updated failed in row: ${recordDetail.row} col: ${recordDetail.col}, oldValue: ${recordDetail.oldValue}, reason is ${record.reason}`);
}
}
}
}
});
// Data entry operations by UI.
report.renderMode("Preview");
report.updateCellValue(0, 1, "test");
report.addRecordAt(1, 0);
report.updateCellValue(2, 0, 111);
report.updateCellValue(2, 1, "test2");
report.deleteRecordAt(3, 0);
report.submit();Note: While submitting a report, the ReportSheetRecordsSubmitting event will always trigger. However, if both the changed records and deleted records are blank, the ReportSheetRecordsSubmitted event will not trigger.