One of the biggest and most important features introduced in v16 is the new SpreadJS File Format, which can make working with large files much faster with smaller resulting file sizes when saved. This blog will cover the details of how this new feature works.
Be sure to download a trial of SpreadJS today!
Basic Functionality
As more and more customers use SpreadJS, we have seen larger and more complex files being used by our customers. With these larger files comes the concern over performance, which we have sought to address with a new file format. This new .sjs format is a zipped file that contains multiple smaller JSON files, similar to the Excel XML structure.
This new structure allows you to support large Excel files and export them to a smaller size. In addition, if there are a lot of worksheets in an Excel file, you can load only the required worksheets quickly. To utilize this new format, you no longer need to import the ExcelIO module but rather the IO plugin:
<script src="plugins/gc.spread.sheets.io.xxx.js"></script>This new format can be imported and exported just like the SpreadJS SSJSON files, and once loaded in SpreadJS, it can be exported to Excel as an XLSX file. It should be noted that this new format is optional, and you can still use .SSJSON files. If you want smaller file sizes and faster performance, then it is recommended to use the .SJS file format.
TableSheets are also supported for opening and saving the new file format, in addition to exporting to Excel converted to a worksheet.
Performance Enhancement
Internally this change results in faster performance and smaller file size, as it eliminates the middle point of exporting to SSJSON and then translating to an Excel model. Instead, SpreadJS will now put data in a zipped .SJS file with pieces of smaller SSJSON files, similar to the Excel XML structure.
Previously, the ExcelIO Import and Export essentially relied on a special JSON and Excel model to convert to Excel XML. With this new format, SpreadJS converts to .SJS, which is a special JSON schema that is similar to the Excel XML and can therefore be directly converted:
Here are some performance numbers that compare example files in .SSJSON and .SJS:
The first point is the time it takes to open and save files:
Time to open files (in milliseconds)
File
v15.2.5 (.SSJSON)
v16.0.0 Import with Default Options (.SJS)
v16.0.0 Import with Lazy Open Mode (.SJS)
File with 5 million values
16959
11983
7148
Test File 1
92778
18904
4004
Test File 2
59290
27001
3554
Test File 3
69053
25145
2103
Time to save files (in milliseconds)
File
v15.2.5 (.SSJSON)
v16.0.0 Export with Default Options (.SJS)
v16.0.0 Export with Lazy Options (.SJS)
File with 5 million values
26588
9016
3307
Test File 1
18269
10628
1057
Test File 2
14318
4037
733
Test File 3
26701
17191
2216
In addition to time improvements, the new file format includes file size improvements:
Exporting File Size
File
v15.2.5 (.XLSX)
v15.2.5 (.SSJSON)
v16.0.0 Default Options (.SJS)
v16.0.0 (.XLSX)
10 million values: 100 sheets with 1000 rows and 100 columns each, containing dates/numbers/strings/formulas
31 MB
267 MB
3.36 MB
31 MB
10 million values: 100 sheets with 1000 rows and 100 rows each, every cell has a set style
Crash (only supports a max of 40 columns)
Crash (only supports a max of 40 columns)
3.07 MB
29.5 MB
5 million values
15.71 MB
150.19 MB
1.90 MB
15.71 MB
Test File 1
4.80 MB
68.25 MB
0.52 MB
2.73 MB
Test File 2
1.44 MB
19.56 MB
0.31 MB
0.97 MB
Test File 3
6.66 MB
81.31 MB
2.86 MB
5.75 MB
Exporting File Size with Options
File
v15.2.5 (.XLSX)
v15.2.5 (.SSJSON)
v16.0.0 Default Options (.SJS)
v16.0.0 Specified Options (.SJS)
3 million unused custom names: 100 sheets with 30,000 custom names each
7.92 MB
176 MB
8.09 MB
92.0 KB
5 million styles but only 50k values
9.96 MB
493 MB
1.08 MB
140 KB
Options
This new file type comes with a few different options to choose from when saving or opening the new SJS file. These include:
Save Options
- includeBindingSource
- includeStyles
- includeFormulas
- saveAsView
- includeAutoMergedCells
- includeCalcModelCache
- includeUnusedNames
- includeEmptyRegionCells
Open Options
- includeStyles
- includeFormulas
- fullRecalc
- dynamicReferences
- calcOnDemand
- includeUnusedStyles
- openMode
- Normal - When opening a file, UI and UI events can be refreshed and will respond at specific time points.
- Lazy - When opening a file, only the active sheet will be loaded directly, and other sheets will be loaded only when used.
- Incremental - When opening a file, UI and UI events can be refreshed and will respond immediately.
There are also specific options for importing and exporting the different file types' options:
- ImportXlsxOptions
- ImportSSJsonOptions
- ImportCsvOptions
- ExportXlsxOptions
- ExportSSJsonOptions
- ExportCsvOptions
Designer
This new file format is also supported in the Designer (both Component and Desktop) under the File > Save menu:
You can also open this new file format as well:
In addition, the old file format is available for importing and exporting, but it is obsolete and deprecated:
To test out this new file format for yourself, be sure to check out our demos here: /spreadjs/demos/features/spreadjs-file-format/overview/purejs
Be sure to download a trial of SpreadJS today!
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources