Import and Export JSON Stream

The sole purpose of facilitating users in importing and exporting to and from json stream is to enable them to exchange and organize object data as and when required. This reference summarizes how DsExcel Java handles the import and export of json stream using Java.

This topic includes the following tasks.

Import and Export JSON Stream for workbook
Import and Export JSON Stream for worksheet
Retreive Errors while Importing JSON Files

Import and Export JSON Stream for workbook

You can export a workbook to a json string/stream using the toJson method of the IWorkbook interface. The method even lets you export the workbooks having formulas with external reference. You can also import a json string or stream to your workbook using the fromJson method of the IWorkbook interface.

Refer to the following example code to import and export json stream.

// Create workbook 
 Workbook workbook = new Workbook();
 Workbook workbook1 = new Workbook();
 
 // Import an excel file 
 workbook.open("test.xlsx");

 // Import or Export from or to a JSON string
 OutputStream outputStream = new ByteArrayOutputStream();
 workbook.toJson(outputStream);
 ByteArrayOutputStream buffer = (ByteArrayOutputStream) outputStream;
 byte[] bytes = buffer.toByteArray();
 InputStream inputStream = new ByteArrayInputStream(bytes);
 workbook1.fromJson(inputStream);
 
// Export workbook to an excel file
 workbook1.save("json_out.xlsx");

!type=note
Note: To get better performance in case of large workbooks with many worksheets having complex formula, you can export and import the workbook and worksheets to separate JSON streams. For more information, see Import and Export from JSON Without Worksheets.

Import and Export JSON Stream for worksheet

You can export the information in a worksheet to a json string using the toJson method of the IWorksheet interface. Similarly, you can also import a json string to your worksheet using the fromJson of the IWorksheet interface. The worksheet can also be exported or imported to the same or another workbook.

It also enables you to view a large Excel file in SpreadJS. The Excel file can be opened in DsExcel and the json string of a worksheet can be exported using the toJson method. Further, the json string of the worksheet can be transfered to client to be loaded in SpreadJS.

Limitations

Importing worksheet json to another workbook on server might cause data loss or conflict
Cell styles used in SpreadJS ssjson are lost in Excel after using Worksheet.toJSON()
SpreadJS doesn't support all the page settings of Excel. Hence, DsExcel does not get all the settings when imported from ssjson.

Refer to the following example code to export and import json string of a worksheet.

Workbook workbook = new Workbook();

// ToJson&FromJson can be used in combination with spreadjs product.

// Documents for Excel import an excel file
String source = "ExcelJsonInput.xlsx";
workbook.open(source);

// Open the file
IWorkbook new_workbook = new Workbook();
new_workbook.open(source);
        
for (IWorksheet worksheet : workbook.getWorksheets()) {
worksheet.getRange("A1:C4").setValue(new Object[][] { { "Device", "Quantity", "Unit Price" },
{ "T540p", 12, 9850 }, { "T570", 5, 7460 }, { "Y460", 6, 5400 }, { "Y460F", 8, 6240 } });

// Documents for Excel export a worksheet to json string
String json = worksheet.toJson();

// You can use the json string to initialize spreadjs product
// Product spreadjs will show the excel file contents
// You can use spreadjs product export a json string of worksheet

// Documents for Excel use the json string to update content of the
// corresponding worksheet
new_workbook.getWorksheets().get(worksheet.getName()).fromJson(json);

}
// Documents for Excel export workbook to an excel file
String export = "ExcelJsonOutput.xlsx";
new_workbook.save(export);

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 getErrorMessage 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.

// create a new workbook
Workbook workbook = new Workbook();
// Open JSON file contaning JSON errors
InputStream stream = new FileInputStream("ErrorJson.json");

List<JsonError> errors = workbook.fromJson(stream);
for (JsonError item : errors) {
    if (item instanceof FormulaJsonError) {
        FormulaJsonError fError = (FormulaJsonError) item;
        System.out
                .println(fError.getErrorMessage() + " "
                        + workbook.getWorksheets().get(fError.getWorksheetName())
                                .getRange(fError.getRow(), fError.getColumn()).toString()
                        + " " + fError.getFormula());
    }

    if (item instanceof DataValidationJsonError) {
        DataValidationJsonError dError = (DataValidationJsonError) item;
        System.out.println(dError.getErrorMessage() + " "
                + workbook.getWorksheets().get(dError.getWorksheetName()).getRange(dError.getRange().toString())
                + " " + dError.getErrorContent());
    }

Limitation

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

Import and Export JSON Stream for workbook
Import and Export JSON Stream for worksheet
Retreive Errors while Importing JSON Files