Documents for Excel, Java Edition Documentation
File Operations / Import and Export JSON Stream
In This Topic
    Import and Export JSON Stream
    In This Topic

    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 GcExcel Java handles the import and export of json stream using Java.

    This topic includes the following tasks.

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

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

    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 GcExcel 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

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

    C#
    Copy Code
    Workbook workbook = new Workbook();
    
    // ToJson&FromJson can be used in combination with spread.sheets
    // product:http://spread.grapecity.com/spreadjs/sheets/
    
    // GrapeCity 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 } });
    
    // GrapeCity Documents for Excel export a worksheet to json string
    String json = worksheet.toJson();
    
    // You can use the json string to initialize spread.sheets product
    // Product spread.sheets will show the excel file contents
    // You can use spread.sheets product export a json string of worksheet
    
    // GrapeCity Documents for Excel use the json string to update content of the
    // corresponding worksheet
    new_workbook.getWorksheets().get(worksheet.getName()).fromJson(json);
    
    }
    // GrapeCity Documents for Excel export workbook to an excel file
    String export = "ExcelJsonOutput.xlsx";
    new_workbook.save(export);

    SpreadJS SSJSON Support

    GcExcel Java provides support for SpreadJS SSJSON. You can import a SSJSON file created with Spread.Sheets Designer and save it back after modifying it as per your preferences.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
            
    // Load SSJSON file
    try 
    {
        FileInputStream stream = new FileInputStream("test.ssjson");
        workbook.fromJson(stream);
    
    } 
    catch (Exception e) 
    {
        e.getMessage();
    }
            
    // Save file
    workbook.save("workbook-ssjson.xlsx");

    Import and Export SpreadJS JSON Files with Shapes

    GcExcel Java allows users to load and save Grapecity SpreadJS JSON files along with shapes. Besides supporting the import and export of SpreadJS JSON files containing shapes, users can also modify the exported SpreadJS JSON files with shapes and save them back to the original SpreadJS JSON files as and when required.

    Java
    Copy Code
    // Initialize workbook
    Workbook workbook = new Workbook();
            
    // Initialize another workbookWithShape
    Workbook workbookWithShape = new Workbook();
            
    // Fetch default worksheet of workbookWithShape
    IWorksheet worksheet = workbookWithShape.getWorksheets().get(0);
    
    // Add a shape in worksheet
    IShape shape = worksheet.getShapes().addShape(AutoShapeType.Parallelogram, 1, 1, 200, 100);
    shape.getLine().setDashStyle(LineDashStyle.Dash);
    shape.getLine().setStyle(LineStyle.Single);
    shape.getLine().setWeight(2);
    IColorFormat color = shape.getFill().getColor();
    shape.getLine().setTransparency(0.3);
    color.setObjectThemeColor(ThemeColor.Accent6);
    
    // Converting workbook containing shape to JsonString
    String jsonString = workbookWithShape.toJson();
    
    // GcExcel can load json string containing shapes
    workbook.fromJson(jsonString);
    
    // Saving workbook
    workbook.save("4-LoadAndSaveSSJSONContainingShapes.xlsx");
            

    Note: The following limitations must be kept in mind while exporting Excel files with vertical text to PDF -
    • The orientation can only be set to 0, 90, -90 and 255. Other values will be treated as 0 while rendering the PDF file.
    • If the font name starts with "@" and the orientation is 255, GcExcel will ignore the "@".

    Retreive Errors while Importing JSON Files

    GcExcel 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:

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

    Java
    Copy Code
       // 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.