Import and Export from JSON Without Worksheets

In This Topic

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.setIgnoreSheets method to true and then pass it as a parameter while calling the IWorkbook.toJson 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.

Java	Copy Code
// 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");

Java

Copy Code

// 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");