[]
        
(Showing Draft Content)

Import and Export SpreadJS Files

DsExcel supports SSJSON and SJS I/O of SpreadJS files. You can import, modify, and export files created with the SpreadJS designer.

SJS and SSJSON File Format

SpreadJS v16 introduced a new file format, .sjs, to work with large and complex files faster and generate smaller files (in size) when saved. The new .sjs format is a zipped file that contains multiple smaller JSON files and is structured similarly to the Excel XML structure. DsExcel allows you to import and export the new .sjs file format just like the XLSX, CSV, and other file formats.

Below, you will find detailed information about SJS Options and SSJSON Options to help you work with these formats and customize their behavior to suit your needs.

SJS Options

You can import a .sjs file using the Open method of Workbook class. Once loaded in DsExcel, it can be exported to Excel (XLSX) or back to .sjs file using the Save method of Workbook class. While loading or saving a .sjs file, you can use the new option "Sjs" in OpenFileFormat and SaveFileFormat enums.

Refer to the following example code to import and export a .sjs file from the file name:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open .sjs file.
workbook.Open("ProjectPlan.sjs", OpenFileFormat.Sjs);

// Save .sjs file.
workbook.Save("SaveProjectPlan.sjs", SaveFileFormat.Sjs);

Refer to the following example code to import and export a .sjs file from a file stream:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open a .sjs file from stream.
var importStream = new FileStream("ProjectPlan.sjs", FileMode.Open);
workbook.Open(importStream, OpenFileFormat.Sjs);

// Save the .sjs file to stream.
var exportStream = new FileStream("SaveProjectPlan.sjs", FileMode.Create);
workbook.Save(exportStream, SaveFileFormat.Sjs);

In addition, DsExcel provides SjsOpenOptions and SjsSaveOptions classes to customize the import and export of a .sjs file. These options are especially useful in dealing with large files, such as those containing many formulas, styles, or unused names. These options are listed below:


Class

Option

Description

Import Options

SjsOpenOptions

IncludeStyles

Indicates whether the style can be included when loading .sjs files. By default, it is true.

IncludeFormulas

Indicates whether the formula can be included when loading .sjs files. By default, it is true.

Export Options

SjsSaveOptions

IncludeStyles

Indicates whether the style can be included when saving files. By default, the value is true.

IncludeFormulas

Indicates whether the formula can be included when saving the file. By default, the value is true.

IncludeUnusedNames

Indicates whether the unused custom name can be included when saving the file. By default, the value is true.

IncludeEmptyRegionCells

Indicates whether any empty cells outside the used data range can be included while saving the file. By default, the value is true.

IgnoreRangeOutOfRowCoulumnCount

Indicates whether to ignore data out of RowCount and ColumnCount while saving the file. By default, the value is false.

IncludeAutoMergedCells

Indicates whether to include the automatically merged cells. By default, the value is false.

IncludeBindingSource

Indicates whether to include the binding source when saving the file. By default, the value is true.

Refer to the following example code to import and export a .sjs file using SjsOpenOptions and SjsSaveOptions:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open a .sjs file with formulas.
SjsOpenOptions openOptions = new SjsOpenOptions();
openOptions.IncludeFormulas = false;
openOptions.IncludeStyles = false;
workbook.Open("ProjectPlan.sjs", openOptions);

// Save the .sjs file with styles.
SjsSaveOptions saveOptions = new SjsSaveOptions();
saveOptions.IncludeStyles = false;
saveOptions.IncludeFormulas = true;
saveOptions.IncludeUnusedNames = false;
saveOptions.IncludeEmptyRegionCells = false;
workbook.Save("SaveProjectPlan.sjs", saveOptions);

DsExcel provides ToSjsJson method that integrates all JSON files from the .sjs file into a single string or stream. Furthermore, DsExcel also provides FromSjsJson, which loads a string or stream of all JSON files generated from .sjs file. These methods also support SjsOpenOptions and SjsSaveOptions.

Methods

Description

ToSjsJson()

Generates a JSON string from a workbook. It integrates all JSON files from the .sjs file into a single string.

ToSjsJson(SjsSaveOptions options)

Generates a JSON string from a workbook using save options. It integrates all JSON files from the .sjs file into a single string.

ToSjsJson(Stream stream)

Integrates all JSON files from the .sjs file into a single string, then puts the string into the stream.

ToSjsJson(Stream stream, SjsSaveOptions options)

Integrates all JSON files from the .sjs file into a single string using save options, then puts the string into the stream.

FromSjsJson(string json)

Generates a workbook from a JSON string containing the contents of .sjs file format.

FromSjsJson(string json, SjsOpenOptions options)

Generates a workbook from a JSON string using open options containing the contents of .sjs file format.

FromSjsJson(Stream stream)

Generates a workbook from a JSON stream containing the contents of .sjs file format.

FromSjsJson(Stream stream, SjsOpenOptions options)

Generates a workbook from a JSON stream using open options containing the contents of .sjs file format.

Refer to the following example code to export and import a single JSON string generated from .sjs file:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open workbook.
workbook.Open("12-month cash flow statement1.sjs");

// Generate a JSON string for .sjs file and save it to a stream.
var exportStream = new FileStream("CashFlow.json", FileMode.Create);
workbook.ToSjsJson(exportStream);
exportStream.Close();


// Import a JSON string from the stream and save it as an Excel file.
var inputStream = new FileStream("CashFlow.json", FileMode.Open);
workbook.FromSjsJson(inputStream);

// Save workbook.
workbook.Save("CashFlow.xlsx");

SSJSON Options

In SpreadJS, while importing or exporting custom data from or to a JSON object, you can set several serialization or deserialization options. DsExcel API supports some of these options for workbook and worksheet JSON I/O. The below table explains the supported options in SpreadJS and DsExcel.


SpreadJS (toJSON and fromJSON)

DsExcel (ToJSON and FromJSON)

Serialization

ignoreStyle ignoreFormula rowHeadersAsFrozenColumns columnHeadersAsFrozenRows

IgnoreStyle IgnoreFormula IgnoreSheets IgnoreColumnRowInfoOutOfUsedRange IgnoreRangeOutOfRowColumnCount ExportSharedFormula IncludeAutoMergedCells IncludeBindingSource

Deserialization

ignoreStyle ignoreFormula frozenColumnsAsRowHeaders frozenRowsAsColumnHeaders doNotRecalculateAfterLoad

IgnoreStyle IgnoreFormula DoNotRecalculateAfterLoad

DsExcel provides SerializationOptions and DeserializationOptions classes in API with above-mentioned supported properties.

The following example code serializes a workbook to JSON with options in DsExcel.

// ignore style and formula when serialize workbook to json
string jsonWithOption = workbook.ToJson(new SerializationOptions() { IgnoreFormula = true, IgnoreStyle = true });

workbook.FromJson(jsonWithOption);

//save to an excel file
workbook.Save("tojsonwithoptions.xlsx");

The following example code deserializes a workbook from JSON with options in DsExcel.

// ignore style and formula when deserialize workbook from json.
workbook.FromJson(json, new DeserializationOptions() { IgnoreFormula = true, IgnoreStyle = true });

//save to an excel file
workbook.Save("fromjsonwithoptions.xlsx");

You can control the size of exported JSON file by choosing whether you want to keep the style and size of rows and columns which are out of the used range. The IgnoreColumnRowInfoOutOfUsedRange property is provided in SerializationOptions class which:

  • When set to true (default value), does not export the style and size of rows and columns which are out of the used range and hence, the file size is smaller.

  • When set to false, exports the style and size of rows and columns which are out of the used range and hence, the file size is larger.

The following example code shows how the size of JSON file is impacted by setting the above mentioned property.

var book = new Workbook();
IWorksheet worksheet = book.Worksheets[0];
//Add custom name style
IStyle style = book.Styles.Add("testStyle1");
 
style.Font.ThemeColor = ThemeColor.Accent1;
style.Font.TintAndShade = 0.8;
style.Font.Italic = true;
style.Font.Bold = true;
style.Font.Name = "LiSu";
style.Font.Size = 28;
style.Font.Strikethrough = true;
style.Font.Subscript = true;
style.Font.Superscript = false;
style.Font.Underline = UnderlineType.Double;
 
object[,] data = new object[,]{
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
};
 
worksheet.Range["B2:E6"].Value = data;
worksheet.Range["A:XFD"].Style = book.Styles["testStyle1"];
worksheet.Range["A:XFD"].ColumnWidth = 20;
    
//Export sizes/styles of only used range to json
using FileStream jsonFile = new FileStream("TestJson_true.json", FileMode.Create);
book.ToJson(jsonFile, new SerializationOptions() { IgnoreColumnRowInfoOutOfUsedRange = true }); // Size of output file is 9KB
    
//Export all sizes/styles to json
using FileStream jsonFile2 = new FileStream("TestJson_false.json", FileMode.Create);
book.ToJson(jsonFile2, new SerializationOptions() { IgnoreColumnRowInfoOutOfUsedRange = false }); // Size of output file is 793KB
    
//Default behavior (same as true option)
book.ToJson(new FileStream("TestJson_default.json", FileMode.Create)); // Size of output file is 9KB

You can also control whether to export formulas as shared formulas when exporting to a JSON file using the ExportSharedFormula property in SerializationOptions class. This enables you to export the formulas as shared formulas when it is set to true (the default value). However, if the value is set to false, the formulas will be exported as individual formulas.

In DsExcel v6.0.1 and higher versions, the formula is exported as a shared formula to a JSON file (or SSJSON file). Because the shared formula is not compatible with DsExcel versions less than or equal to v5 and SpreadJS versions less than or equal to v15, you can use this option for backward compatibility and skip shared formulas in the exported JSON file.

The following example code exports formulas as shared formulas:

// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Set options for iterative calculation.
workbook.Options.Formulas.EnableIterativeCalculation = true;
workbook.Options.Formulas.MaximumIterations = 20;
var worksheet = workbook.Worksheets[0];

// Set values and formulas.
worksheet.Range["B2"].Value = "Initial Cash";
worksheet.Range["C2"].Value = 10000;
worksheet.Range["B3"].Value = "Interest";
worksheet.Range["C3"].Value = 0.0125;

worksheet.Range["B5"].Value = "Month";
worksheet.Range["C5"].Value = "Total Cash";

worksheet.Range["B6:B17"].Value = new double[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };

worksheet.Range["C6"].Formula = "=C2*(1+$C$3)";
worksheet.Range["C7:C17"].Formula = "=C6*(1+$C$3)";

// Initialize SerializationOptions and set ExportSharedFormula to true.
SerializationOptions options = new SerializationOptions();
options.ExportSharedFormula = true;

// Save the JSON file.
workbook.ToJson(File.Create("ExportSharedFormulas.json"), options);

// Initialize SerializationOptions and set ExportSharedFormula to false.
SerializationOptions options2 = new SerializationOptions();
options2.ExportSharedFormula = false;

// Save the JSON file.
workbook.ToJson(File.Create("ExportIndividualFormulas.json"), options2);

Note: SpreadJS supports multi-level row or column headers but DsExcel does not. However, you can still retain the header information in DsExcel by following the below steps:

  1. Use SpreadJS to export JSON with 'rowHeadersAsFrozenColumns or columnHeadersAsFrozenRows' option as true to convert multi-header to frozen area, and use DsExcel to load the JSON file.

  2. Manipulate the frozen area in DsExcel.

  3. Use DsExcel to export JSON file, and use SpreadJS to load JSON file with 'frozenColumnsAsRowHeaders or frozenRowsAsColumnHeaders ' option as true to convert frozen area to header.

DsExcel allows you to convert skeleton of the workbook into a JSON stream. That is, you can export a workbook with just the worksheet without any data in them. This can be implemented by setting the SerializationOptions.IgnoreSheets to true and then pass it as a parameter while calling IWorkbook.ToJson interface method. You can also export the worksheet only to a separate JSON stream by using the IWorksheet.ToJson(Stream stream) method. Similarly, IWorkSheet.FromJson(Stream stream) lets you import the worksheets from JSON stream when required.

This feature is especially useful in optimizing the performance while loading large workbooks with many worksheets containing complex formula. Using the above-mentioned methods, you can load an empty workbook first with worksheet names and load the worksheet data when user selects a worksheet tab.

The example below demonstrates how you can export the workbook and worksheet to separate JSON streams and load them in a new workbook instance when required.

//create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();

    //The old workbook.
    var oldWorkbook = new GrapeCity.Documents.Excel.Workbook();

    var fileStream = GetResourceStream("xlsx\\12-month cash flow statement1.xlsx");
    oldWorkbook.Open(fileStream);

    using (Stream workbookStream = new FileStream("workbook.json", FileMode.Create))
    {
        //Export the skeleton of the workbook without worksheets to json stream. 
        oldWorkbook.ToJson(workbookStream, new SerializationOptions { IgnoreSheets = true });

        //Import the workbook stream.
        workbook.FromJson(workbookStream);
    }

    using (Stream worksheetStream = new FileStream("worksheet.json", FileMode.Create))
    {
        //Export the worksheet to json stream. 
        oldWorkbook.ActiveSheet.ToJson(worksheetStream);

        //Import the worksheet stream.
        workbook.ActiveSheet.FromJson(worksheetStream);
    }

    // Save to an excel file
    workbook.Save("workbooktojsonwithoutsheets.xlsx");

}

static Stream GetResourceStream(string resourcePath)
{
    string resource = "WorkbookToJsonWithoutSheets.Resource." + resourcePath.Replace("\\", ".");
    var assembly = typeof(JSONWithoutSheets).GetTypeInfo().Assembly;
    return assembly.GetManifestResourceStream(resource);
}

Control Bound Data Export

DsExcel enables you to control whether to export the bound data source by using the IncludeBindingSource property—when exporting to a .sjs file, use the property in SjsSaveOptions; when exporting to a .ssjson file, use the property in SerializationOptions.

Refer to the following example code to exclude the binding source when exporting to a .sjs file or a .ssjson file :

// Create a new workbook.
var workbook = new Workbook();

// Define a JSON data source.
var dataSource = "{ \"ds\": " +
                 "[\n" +
                 "   {\"Area\": \"North America\",\"City\": \"Chicago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" +
                 "   {\"Area\": \"North America\",\"City\": \"New York\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" +
                 "   {\"Area\": \"South America\",\"City\": \"Santiago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 19550},\n" +
                 "   {\"Area\": \"Europe\",\"City\": \"Berlin\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 30000},\n" +
                 "   {\"Area\": \"Asia\",\"City\": \"Tokyo\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 45000},\n" +
                 "   {\"Area\": \"North America\",\"City\": \"Los Angeles\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 60000},\n" +
                 "   {\"Area\": \"Europe\",\"City\": \"Paris\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 55000},\n" +
                 "   {\"Area\": \"Asia\",\"City\": \"Seoul\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 40000},\n" +
                 "   {\"Area\": \"South America\",\"City\": \"Buenos Aires\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 35000},\n" +
                 "   {\"Area\": \"North America\",\"City\": \"Toronto\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 50000}\n" +
                 " ]" +
                 "}";

// Add data source to worksheet.
var dataSourceSheet = workbook.Worksheets.Add();
dataSourceSheet.Name = "DataSource";
var table = dataSourceSheet.Tables.Add(dataSourceSheet.Range["A1:E4"], true);

// Set binding path.
table.BindingPath = "ds";
table.Columns[0].DataField = "Area";
table.Columns[1].DataField = "City";
table.Columns[2].DataField = "Category";
table.Columns[3].DataField = "Name";
table.Columns[4].DataField = "Revenue";

// Set data source.
dataSourceSheet.DataSource = new JsonDataSource(dataSource);

// Create pivot table sheet.
var pivotSheet = workbook.Worksheets[0];
pivotSheet.Name = "PivotSheet";

// Create pivot table.
var pivotCache = workbook.PivotCaches.Create(table);
var pivotTable = pivotSheet.PivotTables.Add(pivotCache, pivotSheet.Range["A1"], "pivottable1");

// Configure pivot table fields.
var fieldArea = pivotTable.PivotFields["Area"];
fieldArea.Orientation = PivotFieldOrientation.RowField;
var fieldCity = pivotTable.PivotFields["City"];
fieldCity.Orientation = PivotFieldOrientation.RowField;
var fieldName = pivotTable.PivotFields["Name"];
fieldName.Orientation = PivotFieldOrientation.ColumnField;
var fieldRevenue = pivotTable.PivotFields["Revenue"];
fieldRevenue.Orientation = PivotFieldOrientation.DataField;
pivotSheet.UsedRange.AutoFit();
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
pivotTable.Refresh();

var saveOptions = new SjsSaveOptions();

// If exporting in SSJSON format, uncomment this line.
//var saveOptions = new SerializationOptions();

// Set IncludeBindingSource property to false to exclude the binding source from being exported.
saveOptions.IncludeBindingSource = false;

// Save the workbook.
workbook.Save("IncludeBindingSourceOption11.sjs", saveOptions);

// If exporting in SSJSON format, uncomment this line.
//workbook.ToJson(new FileStream("IncludeBindingSourceOption.ssjson", FileMode.Create), saveOptions);

Note: IncludeBindingSource property will not revert the table to its original size after the DataBinding has changed its size. This property only controls whether the data is exported.

Row and Column Count

DsExcel allows you to set the count of rows and columns in a worksheet while performing .sjs or JSON I/O. RowCount and ColumnCount properties of the IWorksheet interface enable you to achieve the same. RowCount and ColumnCount properties also increase or decrease the row and column count when you insert or delete the rows or columns.

You can also use the IgnoreRangeOutOfRowColumnCount property of SjsSaveOptions class and IgnoreRangeOutOfRowColumnCount property of SerializationOptions class to choose whether to ignore the data outside the range of the specified row and column count. The default value of this property is false, which exports the data outside the range of the specified row and column count to .sjs or JSON.

Refer to the following example code to set the row and column count in a worksheet and export it to a .sjs file or a .ssjson file:

// Create a new workbook.
var workbook = new Workbook();

// --------------- For .sjs Export ---------------
/*
// Open an existing .sjs file.
workbook.Open("LoanDetails.sjs", OpenFileFormat.Sjs);

// Access first worksheet.
var worksheet = workbook.Worksheets[0];

// Adjust row and column count to 4.
worksheet.RowCount = 4;
worksheet.ColumnCount = 4;

// Control the exclusion of content outside the row or column count from being exported.
SjsSaveOptions saveOptions = new SjsSaveOptions
{
    IgnoreRangeOutOfRowColumnCount = true
};

// Save the workbook in .sjs file format.
workbook.Save("IgnoreDataOption.sjs", saveOptions);
*/


// --------------- For SSJSON Export ---------------
/*
// If exporting in SSJSON format, use the following code:

IWorkbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 1;
worksheet.Range["A11"].Value = 2;

// Modify the row count and column count of the worksheet.
worksheet.RowCount = 10;
worksheet.ColumnCount = 10;

// Save to a json file.
// Open this json file with SpreadJS, you will find that the row count is 10, and the column count is 10.
using (FileStream file = new FileStream("RowColumnCount.json", FileMode.Create))
{
    workbook.ToJson(file, new SerializationOptions { IgnoreRangeOutOfRowColumnCount = true });
}
*/

Limitations

  • The row and column count setting is only supported for .sjs and JSON I/O and cannot be exported to Excel or PDF file.

  • RowCount and ColumnCount cannot exceed the maximum row limit of 1,048,576 and the column limit of 16,384 in MS Excel.

Retreive Errors while Importing JSON Files

DsExcel provides the option to get JSON errors, if any, while importing the JSON file using FromJson method of IWorkbook interface. The error message is displayed by the ErrorMessage property of JsonError class. Two types of error messages are supported:

  • Formula JSON Error - Implemented using the FormulaJsonError class and can be raised in case of a formula error in JSON file

  • Data Validation JSON Error - Implemented using the DataValidationJsonError class and can be raised in case of a data validation error in JSON file

Refer to the below example code which will display a formula JSON error as the JSON file containing formula error is imported in DsExcel.

Workbook workbook = new Workbook();
IList<JsonError> errors = workbook.FromJson(File.OpenRead("ErrorJson.json"));
foreach (JsonError item in errors)
{
    if (item is FormulaJsonError)
    {
        FormulaJsonError fError = item as FormulaJsonError;
        Console.WriteLine(fError.ErrorMessage + " " + workbook.Worksheets[fError.WorksheetName].Range[fError.Row, fError.Column].ToString() + " " + fError.Formula);
    }
    if (item is DataValidationJsonError)
    {
        DataValidationJsonError dError = item as DataValidationJsonError;
        Console.WriteLine(dError.ErrorMessage + " " + workbook.Worksheets[dError.WorksheetName].Range[dError.Range.ToString()] + " " + dError.ErrorContent);
    }
}

Limitation

If the data validation in JSON file has error in its formula, Data Validation JSON error will be generated.