New JavaScript Spreadsheet File Formats in SpreadJS v16

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: https://www.grapecity.com/spreadjs/demos/features/spreadjs-file-format/overview/purejs

 

Be sure to download a trial of SpreadJS today

 

Tags:

comments powered by Disqus