Import and Export SpreadJS Files

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

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

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

// Create a new workbook.
var streamworkbook = new Workbook();
// Create a new file stream to open a file.
InputStream openFile;
try {
    openFile = new FileInputStream("ProjectPlan.sjs");
    // Open xltx file.
    streamworkbook.open(openFile, OpenFileFormat.Sjs);
} catch (FileNotFoundException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
}
  
// Create a new file stream to save a file.
OutputStream out;
try {
    out = new FileOutputStream("SaveProjectPlan.sjs");
    // Save workbook as xltx file.
    streamworkbook.save(out, SaveFileFormat.Sjs);
} catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

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

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

// Save the .sjs file with styles.
SjsSaveOptions saveOptions = new SjsSaveOptions();
saveOptions.setIncludeStyles(false);
saveOptions.setIncludeFormulas(true);
saveOptions.setIncludeUnusedNames(false);
saveOptions.setIncludeEmptyRegionCells(false);
workbook.save("SaveProjectPlan.sjs", saveOptions);

// 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.
OutputStream outputStream;
try {
    outputStream = new FileOutputStream("CashFlow.json");
    workbook.toSjsJson(outputStream);
    outputStream.close();
} catch (FileNotFoundException e) {
    e.printStackTrace();        
}

// Import a JSON string from the stream and save it as an Excel file.
InputStream inputStream;
try {
    inputStream = new FileInputStream("CashFlow.json");
    workbook.fromSjsJson(inputStream);
} catch (FileNotFoundException e) {
    e.printStackTrace();        
}

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

// Ignore style and formula when deserialize workbook from json.
DeserializationOptions deserializationOptions = new DeserializationOptions();
deserializationOptions.setIgnoreStyle(true);
deserializationOptions.setIgnoreFormula(true);
workbook.fromJson(json, deserializationOptions);

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

// Ignore style and formula when serialize workbook to json.
SerializationOptions serializationOptions = new SerializationOptions();
serializationOptions.setIgnoreStyle(true);
serializationOptions.setIgnoreFormula(true);

String jsonWithOption = workbook.toJson(serializationOptions);

workbook.fromJson(jsonWithOption);

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

Workbook book = new Workbook();
 
IWorksheet worksheet = book.getWorksheets().get(0);
//Add custom name style.
IStyle style = book.getStyles().add("testStyle1");
     
style.getFont().setThemeColor(ThemeColor.Accent1);
style.getFont().setTintAndShade(0.8);
style.getFont().setItalic(true);
style.getFont().setBold(true);
style.getFont().setName("LiSu");
style.getFont().setSize(28);
style.getFont().setStrikethrough(true);
style.getFont().setSubscript(true);
style.getFont().setSuperscript(false);
style.getFont().setUnderline(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.getRange("B2:E6").setValue(data);
worksheet.getRange("A:XFD").setStyle(style);
worksheet.getRange("A:XFD").setColumnWidthInPixel(20);
        
//Export sizes/styles of only used range to json
SerializationOptions options = new SerializationOptions();
options.setIgnoreColumnRowInfoOutOfUsedRange(true);
        
try {
    book.toJson(new FileOutputStream("TestJson_true.json"), options);    // Size of output file is 9KB
} catch (FileNotFoundException e1) {
    e1.printStackTrace();
}
        
//Export all sizes/styles to json
SerializationOptions options2 = new SerializationOptions();
options2.setIgnoreColumnRowInfoOutOfUsedRange(false);
        
try {
    book.toJson(new FileOutputStream("TestJson_false.json"), options2);    // Size of output file is 809KB
} catch (FileNotFoundException e1) {
    e1.printStackTrace();
}
        
//Default behavior (same as true option)
try {
    book.toJson(new FileOutputStream("TestJson_default.json"));    // Size of output file is 9KB
} catch (FileNotFoundException e) {
    e.printStackTrace();
}

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

 // Set options for iterative calculation.
 workbook.getOptions().getFormulas().setEnableIterativeCalculation(true);
 workbook.getOptions().getFormulas().setMaximumIterations(20);
 var worksheet = workbook.getWorksheets().get(0);

 // Set values and formulas.
 worksheet.getRange("B2").setValue("Initial Cash");
 worksheet.getRange("C2").setValue(10000);
 worksheet.getRange("B3").setValue("Interest");
 worksheet.getRange("C3").setValue(0.0125);

 worksheet.getRange("B5").setValue("Month");
 worksheet.getRange("C5").setValue("Total Cash");  

 worksheet.getRange("B6:B17").setValue(new double[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 });  

 worksheet.getRange("C6").setFormula("=C2*(1+$C$3)");  
 worksheet.getRange("C7:C17").setFormula("=C6*(1+$C$3)");  

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

 // Save the JSON file.
 PrintWriter out1;
try {
    out1 = new PrintWriter("ExportSharedFormulas.json");
    out1.println(workbook.toJson(options));
} catch (FileNotFoundException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
}     

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

 // Save the JSON file.
 PrintWriter out2;
try {
    out2 = new PrintWriter("ExportIndividualFormulas.json");
    out2.println(workbook.toJson(options));
} catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

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

// The old workbook.
IWorkbook oldWorkbook = new com.grapecity.documents.excel.Workbook();

InputStream fileStream = getResourceStream("xlsx\\12-month cash flow statement1.xlsx");
oldWorkbook.open(fileStream);

FileOutputStream workbookOutputJson = null;
FileInputStream workbookInputJson = null;
FileOutputStream worksheetOutputJson = null;
FileInputStream worksheetInputJson = null;

try {
    SerializationOptions serializationOptions = new SerializationOptions();
    serializationOptions.setIgnoreSheets(true);
    
    // Export the skeleton of the workbook without worksheets to json stream. 
    workbookOutputJson = new FileOutputStream("workbookJava.json");
    oldWorkbook.toJson(workbookOutputJson, serializationOptions);
    
    // Import the workbook stream.
    workbookInputJson = new FileInputStream("workbookJava.json");
    workbook.fromJson(workbookInputJson);

    // Export the worksheet to json stream. 
    worksheetOutputJson = new FileOutputStream("worksheetJava.json");
    oldWorkbook.getActiveSheet().toJson(worksheetOutputJson);

    // Import the worksheet stream.
    worksheetInputJson = new FileInputStream("worksheetJava.json");
    workbook.getActiveSheet().fromJson(worksheetInputJson);
} catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} finally {
    // Close streams.
    try {
        if (workbookOutputJson != null) {
            workbookOutputJson.close();
        }
        
        if (workbookInputJson != null) {
            workbookInputJson.close();
        }
        
        if (worksheetOutputJson != null) {
            worksheetOutputJson.close();
        }
        
        if (worksheetInputJson != null) {
            worksheetInputJson.close();
        }
    } catch (Exception e2) {
        // TODO: handle exception
    }
}
    
// Save to an excel file
workbook.save("WorkbookToJsonWithoutSheets.xlsx");

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

// Define a JSON data source.
String 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.
IWorksheet dataSourceSheet = workbook.getWorksheets().add();
dataSourceSheet.setName("DataSource");
ITable table = dataSourceSheet.getTables().add(dataSourceSheet.getRange("A1:E4"), true);

// Set binding path.
table.setBindingPath("ds");
table.getColumns().get(0).setDataField("Area");
table.getColumns().get(1).setDataField("City");
table.getColumns().get(2).setDataField("Category");
table.getColumns().get(3).setDataField("Name");
table.getColumns().get(4).setDataField("Revenue");

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

// Create pivot table sheet.
IWorksheet pivotSheet = workbook.getWorksheets().get(0);
pivotSheet.setName("PivotSheet");

// Create pivot table.
IPivotCache pivotcache = workbook.getPivotCaches().create(table);
IPivotTable pivottable = pivotSheet.getPivotTables().add(pivotcache, pivotSheet.getRange("A1"), "pivottable1");

// Configure pivot table fields.
IPivotField fieldArea = pivottable.getPivotFields().get("Area");
fieldArea.setOrientation(PivotFieldOrientation.RowField);
IPivotField fieldCity = pivottable.getPivotFields().get("City");
fieldCity.setOrientation(PivotFieldOrientation.RowField);
IPivotField fieldName = pivottable.getPivotFields().get("Name");
fieldName.setOrientation(PivotFieldOrientation.ColumnField);
IPivotField fieldRevenue = pivottable.getPivotFields().get("Revenue");
fieldRevenue.setOrientation(PivotFieldOrientation.DataField);
pivotSheet.getUsedRange().autoFit();
pivottable.setColumnGrand(false);
pivottable.setRowGrand(false);
pivottable.refresh();

SjsSaveOptions saveOptions = new SjsSaveOptions();

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

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

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

// If you want to export SSJSON, uncomment the following code.
/*
try 
{
    // Save the workbook.
    workbook.toJson(new FileOutputStream("IncludeBindingSourceOption.ssjson"), saveOptions);
} 
catch (FileNotFoundException e1) {
    e1.printStackTrace();
}
*/

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

// --------------- For .sjs Export ---------------

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

// Access the first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Set row and column count to 4.
worksheet.setRowCount(4);
worksheet.setColumnCount(4);

// Configure export to ignore data outside the row/column count.
SjsSaveOptions sjsSaveOptions = new SjsSaveOptions();
sjsSaveOptions.setIgnoreRangeOutOfRowColumnCount(true);

// Save as .sjs file.
workbook.save("IgnoreDataOption.sjs", sjsSaveOptions);

// --------------- For SSJSON Export ---------------
// If you want to export as SSJSON, uncomment the following code.

/*
IWorksheet worksheet2 = workbook.getWorksheets().get(0);

// Example of setting cell data
worksheet2.getRange("A1").setValue(1);
worksheet2.getRange("A11").setValue(2);

// Set row and column count to 10.
worksheet2.setRowCount(10);
worksheet2.setColumnCount(10);

// Configure export to ignore data outside the row/column count.
SerializationOptions ssjsonOptions = new SerializationOptions();
ssjsonOptions.setIgnoreRangeOutOfRowColumnCount(true);

// Save as SSJSON file (JSON).
// Open this JSON file with SpreadJS to see that row/column count is 10.
try {
    workbook.toJson(new FileOutputStream("RowColumnCount.json"), ssjsonOptions);
} catch (FileNotFoundException e) {
    e.printStackTrace();
}
*/

// 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());
    }