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
Kevin Ashley