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

    GcExcel Java supports the JSON I/O of SpreadJS files. You can also import an ssjson file created with SpreadJS Designer and save it back after modifying it as per your preferences.

    The below example code loads an ssjson file and then saves it to xlsx format.

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

    Note: Upon loading the SpreadJS JSON file, if users get the getColorIndex method of the IBorder interface in order to set an index color, it will return a valid value only if the getColor property of the IBorder interface is set to any rgb color; else, it will return -2 as an invalid flag. Usually, an index color can be converted to rgb color but vice a versa is not possible. 


    The below mentioned features are supported for JSON I/O by GcExcel. You can use fromJson and toJson methods for the same, as is also demonstrated in the sample code above.

    Shapes

    GcExcel Java allows you to perform JSON I/O of SpreadJS files containing shapes. You can also download the JSON file containing shape from here

    Barcodes

    GcExcel supports JSON I/O and PDF export of SpreadJS files containing barcodes. However while exporting to PDF, partial SpreadJS barcode properties are supported. To know more about unsupported properties, refer Export Barcodes.

    You can also download the JSON file containing barcodes from here.

    Cell Buttons

    SpreadJS files containing cell buttons are supported by GcExcel for JSON I/O, HTML, image and PDF exporting. You can also download the JSON file containing cell buttons from here.


    Cell Dropdowns

    GcExcel supports JSON I/O of SpreadJS files containing cell dropdowns like calculator, color picker, time picker etc. You can also download the JSON file containing cell dropdowns from here.


    Validation Styles

    Validation styles can be used to highlight invalid data in a worksheet. GcExcel supports JSON I/O, image and PDF exporting of SpreadJS files containing validation styles. You can also download the JSON file containing validation style from here.

    Text Ellipsis

    When text in a cell is longer than the column width, SpreadJS allows you to show ellipsis instead of overflowing text in the other cell. The SpreadJS files containing text ellipsis are supported for JSON I/O and PDF exporting in GcExcel. You can also download the JSON file containing text ellipsis from here.

    Limitation

    SpreadJS allows different types of text alignment composed with text ellipsis but GcExcel does not. Hence, text ellipsis is only shown at the end of text in exported PDF.

    Range Template

    In SpreadJS, you can create a range cell type which can be used to specify a cell range in the worksheet as a template. You can modify the display mode and appearance of the resultant data just by changing the template. GcExcel supports JSON I/O and PDF exporting of SpreadJS files containing Range templates.

    You can also download the JSON file containing range template from here.

    Format String

    SpreadJS supports Format string feature which allows cells to have both formulas and text as a part of text value templates. GcExcel supports JSON I/O of SpreadJS files containing format strings. 

    You can also download the JSON file containing format string from here.

    JSON Options

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

    SpreadJS (toJSON and fromJSON) GcExcel (toJSON and fromJSON)
    Serialization

    ignoreStyle

    ignoreFormula

    rowHeadersAsFrozenColumns

    columnHeadersAsFrozenRows

    ignoreStyle

    ignoreFormula

    getIgnoreColumnRowInfoOutOfUsedRange

    setIgnoreColumnRowInfoOutOfUsedRange

    getIgnoreRangeOutOfRowColumnCount 

    setIgnoreRangeOutOfRowColumnCount 

    Deserialization

    ignoreStyle

    ignoreFormula

    frozenColumnsAsRowHeaders

    frozenRowsAsColumnHeaders

    doNotRecalculateAfterLoad

    ignoreStyle

    ignoreFormula

    doNotRecalculateAfterLoad


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

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

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    worksheet.getRange("B3:C16").setValue(new Object[][] { { "ITEM", "AMOUNT" }, { "Rent/mortgage", 800 },
            { "Electric", 120 }, { "Gas", 50 }, { "Cell phone", 45 }, { "Groceries", 500 }, { "Car payment", 273 },
            { "Auto expenses", 120 }, { "Student loans", 50 }, { "Credit cards", 100 }, { "Auto Insurance", 78 },
            { "Personal care", 50 }, { "Entertainment", 100 }, { "Miscellaneous", 50 }, });
    
    // Create a table
    ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B3:C16"), true);
    expensesTable.setName("tblExpenses");
    worksheet.getRange("C3:C16").setNumberFormat("$#,##0_);($#,##0)");
    
    worksheet.getRange("B2:C2").merge();
    worksheet.getRange("B2").setValue("MONTHLY EXPENSES");
    worksheet.getRange("B2").getInterior().setColor(Color.FromArgb(219, 219, 219));
    worksheet.getRange("E2").setValue("Total Monthly Expenses");
    worksheet.getRange("E3").setFormula("SUM(tblExpenses[AMOUNT])");
    worksheet.getRange("E3").setNumberFormat("$#,##0_);($#,##0)");
    
    worksheet.getRange("B:B").setColumnWidth(15);
    worksheet.getRange("C:C").setColumnWidth(15);
    worksheet.getRange("E:F").setColumnWidth(15);
    
    String json = workbook.toJson();
    
    // 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");
    

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

    Java
    Copy Code
     // Create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    worksheet.getRange("B3:C16").setValue(new Object[][] { { "ITEM", "AMOUNT" }, { "Rent/mortgage", 800 },
            { "Electric", 120 }, { "Gas", 50 }, { "Cell phone", 45 }, { "Groceries", 500 }, { "Car payment", 273 },
            { "Auto expenses", 120 }, { "Student loans", 50 }, { "Credit cards", 100 }, { "Auto Insurance", 78 },
            { "Personal care", 50 }, { "Entertainment", 100 }, { "Miscellaneous", 50 }, });
    
    // Create a table
    ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B3:C16"), true);
    expensesTable.setName("tblExpenses");
    worksheet.getRange("C3:C16").setNumberFormat("$#,##0_);($#,##0)");
    
    worksheet.getRange("B2:C2").merge();
    worksheet.getRange("B2").setValue("MONTHLY EXPENSES");
    worksheet.getRange("B2").getInterior().setColor(Color.FromArgb(219, 219, 219));
    worksheet.getRange("E2").setValue("Total Monthly Expenses");
    worksheet.getRange("E3").setFormula("SUM(tblExpenses[AMOUNT])");
    worksheet.getRange("E3").setNumberFormat("$#,##0_);($#,##0)");
    
    worksheet.getRange("B:B").setColumnWidth(15);
    worksheet.getRange("C:C").setColumnWidth(15);
    worksheet.getRange("E:F").setColumnWidth(15);
    
    // 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");
    

    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 setIgnoreColumnRowInfoOutOfUsedRange method is provided in SerializationOptions class which:

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

    Java
    Copy Code
    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();
    }
    

    Note: SpreadJS supports multi-level row or column headers but GcExcel does not. However, you can still retain the header information in GcExcel 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 GcExcel to load the JSON file.
    2. Manipulate the frozen area in GcExcel.
    3. Use GcExcel to export JSON file, and use SpreadJS to load JSON file with 'frozenColumnsAsRowHeaders or frozenRowsAsColumnHeaders ' option  as true to convert frozen area to header.

    Checkbox or Radiobutton List Cell Type

    GcExcel supports JSON I/O and PDF exporting of SpreadJS files containing checkbox list and radiobutton list cell types. You can also download the JSON file containing radiobutton list and checkbox list cell type from here.

    GcExcel also provides RadioButtonListCellType and CheckBoxListCellType classes in its API to add these cell types.

    The following example code creates a checkbox list cell type for a cell in GcExcel.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    CheckBoxListCellType cellType = new CheckBoxListCellType();
    cellType.setDirection(CellTypeDirection.Horizontal);
    cellType.setTextAlign(CellTypeTextAlign.Right);
    cellType.setIsFlowLayout(false);
    cellType.setMaxColumnCount(2);
    cellType.setMaxRowCount(1);
    cellType.setHorizontalSpacing(20);
    cellType.setVerticalSpacing(5);
    
    cellType.getItems().add(new SelectFieldItem("sample1", "1"));
    cellType.getItems().add(new SelectFieldItem("sample2", "2"));
    cellType.getItems().add(new SelectFieldItem("sample3", "3"));
    cellType.getItems().add(new SelectFieldItem("sample4", "4"));
    cellType.getItems().add(new SelectFieldItem("sample5", "5"));
    
    worksheet.getRange("A1").setRowHeight(60);
    worksheet.getRange("A1").setColumnWidth(25);
    
    worksheet.getRange("A1").setCellType(cellType);
            
    //check multiple options in the check box list
    worksheet.getRange("A1").setValue(new Object[][]{
    {new Object[]{"1", "3", "5"}}
    });
            
    
    //save to an pdf file
    workbook.save("AddCheckBoxListCellType.pdf");    
    

    The following example code creates checkbox list cell type and sets the value of the option as a custom object.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    
    Workbook.setValueJsonSerializer(new CustomObjectJsonSerializer());
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    CheckBoxListCellType cellType = new CheckBoxListCellType();
    cellType.setDirection(CellTypeDirection.Horizontal);
    cellType.setTextAlign(CellTypeTextAlign.Right);
    cellType.setIsFlowLayout(false);
    cellType.setMaxColumnCount(2);
    cellType.setMaxRowCount(1);
    cellType.setHorizontalSpacing(20);
    cellType.setVerticalSpacing(5);
    
    cellType.getItems().add(new SelectFieldItem("player1", new People(5, "Tom")));
    cellType.getItems().add(new SelectFieldItem("player2", new People(5, "Jerry")));
    cellType.getItems().add(new SelectFieldItem("player3", new People(6, "Mario")));
    cellType.getItems().add(new SelectFieldItem("player4", new People(4, "Luigi")));
    
    worksheet.getRange("A1").setRowHeight(40);
    worksheet.getRange("A1").setColumnWidth(25);
    
    worksheet.getRange("A1").setCellType(cellType);
    worksheet.getRange("A1").setValue(new Object[][]{
    {new Object[]{new People(5, "Tom"), new People(6, "Mario")}}
    });
            
    //save to an pdf file
    workbook.save("AddCheckBoxListCellTypeCustomObject.pdf");
        
    }
    class CustomObjectJsonSerializer implements IJsonSerializer {
    Gson gson = new Gson();
    public final Object deserialize(String json) {
        return this.gson.fromJson(json, JsonElement.class);
    }
        
    public final String serialize(Object value) {
        return this.gson.toJson(value);
    }
    }
        
    class People {
    private int age;
    private String name;
            
    public int getAge() {
        return age;
    }
            
    public void setAge(int age) {
        this.age = age;
    }
            
    public String getName() {
        return name;
    }
            
    public void setName(String name) {
        this.name = name;
    }
            
    public People(int age, String name){
        this.age = age;
        this.name = name;
    }
            
    @Override
    public boolean equals(Object obj){
        return obj instanceof People && age == ((People)obj).getAge() && name.equals(((People)obj).getName());
    }
            
    @Override
    public int hashCode() {
        int hashCode = 17;
        
        hashCode = 31 * hashCode + this.age;
        hashCode = 31 * hashCode + (this.name == null ? 0 : this.name.hashCode());
        
        return hashCode;
    }
    }
    

    The following example code creates a radio list cell type for a cell in GcExcel.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
     IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    RadioButtonListCellType cellType = new RadioButtonListCellType();
    
    cellType.setDirection(CellTypeDirection.Horizontal);
    cellType.setTextAlign(CellTypeTextAlign.Right);
    cellType.setIsFlowLayout(false);
    cellType.setMaxColumnCount(2);
    cellType.setMaxRowCount(1);
    cellType.setHorizontalSpacing(20);
    cellType.setVerticalSpacing(5);
    
    cellType.getItems().add(new SelectFieldItem("sample1", "1"));
    cellType.getItems().add(new SelectFieldItem("sample2", "2"));
    cellType.getItems().add(new SelectFieldItem("sample3", "3"));
    cellType.getItems().add(new SelectFieldItem("sample4", "4"));
    cellType.getItems().add(new SelectFieldItem("sample5", "5"));
    
    worksheet.getRange("A1").setRowHeight(60);
    worksheet.getRange("A1").setColumnWidth(25);
    
    worksheet.getRange("A1").setCellType(cellType);
    worksheet.getRange("A1").setValue("1");
            
    //check multiple options in the radio button list
    worksheet.getRange("A1").setValue(new Object[][]{
    {
        new Object[]{"1", "3", "5"}}
    });
            
    //save to an pdf file
    workbook.save("AddRadioListCellType.pdf");
    

    The following example code creates radiobutton cell type and sets the value of the option as a custom object.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    
    Workbook.setValueJsonSerializer(new CustomObjectJsonSerializer());
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    RadioButtonListCellType cellType = new RadioButtonListCellType();
    cellType.setDirection(CellTypeDirection.Horizontal);
    cellType.setTextAlign(CellTypeTextAlign.Right);
    cellType.setIsFlowLayout(false);
    cellType.setMaxColumnCount(2);
    cellType.setMaxRowCount(1);
    cellType.setHorizontalSpacing(20);
    cellType.setVerticalSpacing(5);
    
    cellType.getItems().add(new SelectFieldItem("player1", new People(5, "Tom")));
    cellType.getItems().add(new SelectFieldItem("player2", new People(5, "Jerry")));
    cellType.getItems().add(new SelectFieldItem("player3", new People(6, "Mario")));
    cellType.getItems().add(new SelectFieldItem("player4", new People(4, "Luigi")));
    
    worksheet.getRange("A1").setRowHeight(40);
    worksheet.getRange("A1").setColumnWidth(25);
    
    worksheet.getRange("A1").setCellType(cellType);
    worksheet.getRange("A1").setValue(new People(6, "Mario"));
    
    //save to an pdf file
    workbook.save("AddRadioButtonCellTypeCustomObject.pdf");
    }
        
    class CustomObjectJsonSerializer implements IJsonSerializer {
    Gson gson = new Gson();
    public final Object deserialize(String json) {
        return this.gson.fromJson(json, JsonElement.class);
    }
        
    public final String serialize(Object value) {
        return this.gson.toJson(value);
    }
    }
        
    class People {
    private int age;
    private String name;
            
    public int getAge() {
        return age;
    }
            
    public void setAge(int age) {
        this.age = age;
    }
            
    public String getName() {
        return name;
    }
            
    public void setName(String name) {
        this.name = name;
    }
            
    public People(int age, String name){
        this.age = age;
        this.name = name;
    }
        
    @Override
    public boolean equals(Object obj){
        return obj instanceof People && age == ((People)obj).getAge() && name.equals(((People)obj).getName());
    }
            
    @Override
    public int hashCode() {
        int hashCode = 17;
        
        hashCode = 31 * hashCode + this.age;
        hashCode = 31 * hashCode + (this.name == null ? 0 : this.name.hashCode());
        
        return hashCode;
    }
    }
    

    Cell Padding and Labels

    GcExcel allows you to perform JSON I/O and PDF exporting for SpreadJS files containing cell padding and labels. You can also download the JSON file containing cell padding and labels from here.

    In addition to this, GcExcel also provides CellPadding and Margin class, ILabelOptions interface, LabelAlignment and LabelVisibility enumerations to support cell padding and labels in GcExcel.

    The following example code adds cell padding and labels in a GcExcel worksheet.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    // Get the sheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    // Set row height
    worksheet.getRange("A:A").setRowHeight(40);
    // Set column width
    worksheet.getRange("A:A").setColumnWidth(25);
    // Set watermark
    worksheet.getRange("A1").setWatermark("GcExcel JAVA");
    // Set cell padding
    worksheet.getRange("A1").setCellPadding(new CellPadding(50, 0, 0, 0));
    // Set label options
    worksheet.getRange("A1").getLabelOptions().setVisibility(LabelVisibility.visible);
    worksheet.getRange("A1").getLabelOptions().setForeColor(Color.GetGreen());
    worksheet.getRange("A1").getLabelOptions().setMargin(new Margin(15, 0, 0, 0));
    worksheet.getRange("A1").getLabelOptions().getFont().setSize(14);
    worksheet.getRange("A1").getLabelOptions().getFont().setName("Calibri");
    worksheet.getRange("A1").getBorders().setLineStyle(BorderLineStyle.Thin);
    
    // Save to a pdf file
    workbook.save("CellPaddingAndLabels.pdf");
    

    Background Image

    GcExcel supports JSON I/O and PDF exporting of SpreadJS files containing background images. You can also download the JSON file containing background image from here.

    GcExcel also provides getBackgroundPictures method in IWorksheet interface to add background pictures in GcExcel. For more information, refer Support Sheet Background Image.

    The following example code sets background image in GcExcel worksheet.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    // Get the sheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    // Load an image from a specific file in input stream
    InputStream stream = new FileInputStream("grapecity.png");
    // Add background picture
    IBackgroundPicture picture = worksheet.getBackgroundPictures().addPictureInPixel(stream, ImageType.PNG, 10, 10,
            500, 370);
    // Set image layout
    picture.setBackgroundImageLayout(ImageLayout.Zoom);
    // Set options
    workbook.getActiveSheet().getPageSetup().setPrintGridlines(true);
    // Save to a pdf file
    workbook.save("BackgroundImage.pdf");
    

     The following example code imports background image from JSON and exports to PDF document.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    // Load JSON file
    FileInputStream stream = new FileInputStream("BackgroundImage.json");
    workbook.fromJson(stream);
    // Save file
    workbook.save("BackgroundImage.pdf");
    

    Limitations

    Background Color

    GcExcel supports JSON I/O and PDF exporting of SpreadJS files containing background color. You can also download the JSON file containing background color from here

    GcExcel also provides setBackColor and setGrayAreaBackColor methods in IWorkbookView interface to set background color in GcExcel. 

    The following code example sets background color for all the worksheets in GcExcel.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    // Get the sheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    
    // Set background color
    workbook.getBookView().setBackColor(Color.GetLightSkyBlue());
    workbook.getBookView().setGrayAreaBackColor(Color.GetGray());
    
    worksheet.getRange("H20").setValue("The text");
    
    // Set page options
    worksheet.getPageSetup().setPrintGridlines(true);
    worksheet.getPageSetup().setPrintHeadings(true);
    
    // Save to a pdf file
    workbook.save("BackgroundColor.pdf");
    

    Limitation

    In SpreadJS, background image always overrides the background color. Thus, the background image needs to be removed for the background color to take effect while exporting to PDF documents.

    Row and Column Count

    GcExcel allows you to set the count of rows and columns in a worksheet while performing JSON I/O. The setRowCount and setColumnCount methods of the IWorksheet interface can be used to achieve the same. You can also use the setIgnoreRangeOutOfRowColumnCount method of SerializationOptions class to choose whether to export the data outside the range of specified row and column count or not. The default value of this method is false which exports the data outside the range of specified row and column count to JSON. 

    Refer to the following example code which sets the row and column count in a worksheet and exports it to a JSON file.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    worksheet.getRange("A1").setValue(1);
    worksheet.getRange("A11").setValue(2);
            
    // Modify the row count and column count of the worksheet.
    worksheet.setRowCount(10);
    worksheet.setColumnCount(10);
            
    SerializationOptions options = new SerializationOptions();
    options.setIgnoreRangeOutOfRowColumnCount(true);
            
    // 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.
            
    try {
        workbook.toJson(new FileOutputStream("RowColumnCount.json"), options);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    

    Limitation

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

    Set Tab Strip Position

    GcExcel allows you to set various properties of Tab Strip like its position, width, display new tab button, editing of worksheet name etc. while performing JSON I/O. The IWorkbook interface provides methods like setTabNavigationVisible, setNewTabVisible, setAllowSheetReorder, setTabStripWidth, setTabStripPosition etc.

    Refer to the following example code which sets the position of tab strip to left and other tab strip properties.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    workbook.getWorksheets().add();
            
    workbook.getBookView().setAllowSheetReorder(false);
    workbook.getBookView().setTabEditable(false);
    workbook.getBookView().setTabNavigationVisible(false);
    workbook.getBookView().setTabStripPosition(SpreadJSTabStripPosition.Left);
    workbook.getBookView().setTabStripWidth(150);
    workbook.getBookView().setNewTabVisible(false);
            
    try {
        workbook.toJson(new FileOutputStream("sheettabposition.json"));
    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    }
    

    Set Size of Check Box, Check Box List and Radio Box List Cells

    GcExcel supports setting the size of Check Box, Check Box List and Radio Box List Cells while performing JSON I/O. The setBoxSize and setAutoBoxSize methods are provided in the CheckBoxCellType, CheckBoxListCellType and RadioButtonListCellType classes. The setBoxSize method can be used to set the size of cell whereas the setAutoBoxSize method can be used to enable whether the box size should change with font size.

    Refer to the following example code which sets the box size and setAutoBoxSize method to true for Check Box List cell.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    CheckBoxListCellType celltype = new CheckBoxListCellType();
    celltype.setTextAlign(CellTypeTextAlign.Right);
    celltype.setIsFlowLayout(false);
    celltype.setMaxColumnCount(2);
    celltype.setMaxRowCount(1);
    celltype.setHorizontalSpacing(20);
    celltype.setVerticalSpacing(5);
    celltype.setBoxSize(40);
    celltype.setAutoBoxSize(true);
            
    celltype.getItems().add(new SelectFieldItem("sample1", "1"));
    celltype.getItems().add(new SelectFieldItem("sample2", "2"));
    celltype.getItems().add(new SelectFieldItem("sample3", "3"));
    celltype.getItems().add(new SelectFieldItem("sample4", "4"));
    celltype.getItems().add(new SelectFieldItem("sample5", "5"));
            
    worksheet.getRange("A1:C3").setColumnWidth(25);
    worksheet.getRange("A1:C3").setCellType(celltype);
    worksheet.getRange("A1:C3").setValue(new Object[][]{
        {new Object[]{"1", "3", "5"}}
    });
            
    try {
        workbook.toJson(new FileOutputStream("checkboxlistsize.json"));
    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    }
    

    Get Picture URL

    GcExcel allows you to get the URL of a picture from a json file using getUrl method in the IPictureFormat interface. This URL is then converted to byte array and set to the picture by using setFill method of the IPictureFormat interface. This allows you to export the json file containing picture URL to an Excel or PDF file.

    Refer to the following example code which gets the URL of a picture from JSON file and exports it to Excel and PDF formats.

    Java
    Copy Code
    private static byte[] GetPicFromUrl(String urlString) throws MalformedURLException, UnsupportedEncodingException {
    
        URL url = new URL(encode(urlString));
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try (InputStream inputStream = url.openStream()) {
            int n = 0;
            byte[] buffer = new byte[1024];
            while (-1 != (n = inputStream.read(buffer))) {
                baos.write(buffer, 0, n);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    
        return baos.toByteArray();
    }
    
    private static String encode(String url) throws UnsupportedEncodingException {
        char[] charArray = url.toCharArray();
        StringBuilder sb = new StringBuilder();
        for (char c : charArray) {
            if (c >= 0 && c < 255) {
                sb.append(c);
            } else {
                sb.append(URLEncoder.encode(String.valueOf(c), "UTF-8"));
            }
        }
        return sb.toString();
    }