Import and Export from JSON string

DsExcel allows you to import and export below features from or to a json string.

Shape, Chart or Picture

Refer to the following example code which uses IShape.fromJson method to update a shape, chart and picture from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getRange("A1:D6").setValue(new Object[][]{
{null, "S1", "S2", "S3"},
{"Item1", 10, 25, 25},
{"Item2", -51, -36, 27},
{"Item3", 52, -85, -30},
{"Item4", 22, 65, 65},
{"Item5", 23, 69, 69}
});

// add a temp shape
IShape shape = worksheet.getShapes().addShape(AutoShapeType.Rectangle, 10, 10, 100, 100);
        
//update shape from json
shape.fromJson("{\"isLocked\":true,\"canPrint\":true,\"dynamicMove\":true,\"dynamicSize\":true," +
    "\"allowResize\":true,\"allowRotate\":true,\"allowMove\":true,\"showHandle\":true,\"alt\":\"\"," +
    "\"formulaItems\":{\"line\":{\"color\":\"rgb(31,79,122)\",\"lineStyle\":0,\"width\":1,\"capType\":2," +
    "\"joinType\":0,\"transparency\":0}},\"shapeData\":{\"anchorType\":0,\"startPoint\":{\"row\":1," +
    "\"col\":0,\"rowOffset\":11,\"colOffset\":38},\"endPoint\":{\"row\":8,\"col\":4,\"rowOffset\":2," +
    "\"colOffset\":27},\"editAs\":0,\"sp\":{\"shapeType\":5,\"nvSpPr\":{\"cNvPr\":{\"id\":2,\"name\":" +
    "\"rightArrowCallout 1\",\"hidden\":false,\"title\":\"\"},\"cNvSpPr\":{\"txBox\":false}},\"spPr\"" +
    ":{\"xfrm\":{\"flipH\":false,\"flipV\":false,\"rot\":0,\"off\":{\"x\":38,\"y\":31},\"ext\":{\"cx\"" +
    ":237,\"cy\":131}},\"prstGeom\":{\"prst\":56,\"avLst\":{}},\"extLst\":{\"ext\":[]},\"solidFill\":" +
    "{\"schemeClr\":{\"val\":9,\"lumMod\":[60000],\"lumOff\":[40000]}},\"ln\":{\"solidFill\":{\"srgbClr\"" +
    ":{\"val\":[31,79,122]}},\"w\":1,\"prstDash\":0,\"cap\":2,\"round\":true},\"effectLst\":{}},\"style\":" +
    "{\"fillRef\":{\"ColorProp\":{\"colorFillType\":0,\"schemeClr\":{\"val\":-4142}},\"idx\":1},\"lnRef\":" +
    "{\"ColorProp\":{\"colorFillType\":0,\"schemeClr\":{\"val\":-4142}},\"idx\":2},\"fontRef\":{" +
    "\"TextCharacterProperties\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":14.666666666666666," +
    "\"solidFill\":{\"srgbClr\":{\"val\":[255,255,255]}}},\"idx\":1},\"effectRef\":{\"idx\":0,\"ColorProp\":{" +
    "\"colorFillType\":0,\"schemeClr\":{\"val\":4}}}},\"txBody\":{\"p\":[{\"elements\":[{\"elementType\":0,\"t" +
    "\":\"\",\"rPr\":{\"latin\":{\"typeface\":\"Calibri\"},\"sz\":14.6667,\"b\":false,\"i\":false,\"solidFill" +
    "\":{\"srgbClr\":{\"val\":[255,255,255]}}}}],\"pPr\":{\"defRPr\":{\"latin\":{\"typeface\":\"Calibri\"},\"sz" +
    "\":14.6667,\"b\":false,\"i\":false,\"solidFill\":{\"srgbClr\":{\"val\":[255,255,255]}}},\"algn\":0}," +
    "\"endParaRPr\":{}}],\"bodyPr\":{\"anchor\":0,\"horzOverflow\":1,\"vertOverflow\":2},\"lstStyle\":{}}}}," +
    "\"name\":\"rightArrowCallout 1\",\"shapeType\":5}");
        
// add a temp chart
IShape chart = worksheet.getShapes().addChart(ChartType.ColumnClustered, 300, 10, 300, 300);

// chart from json
chart.fromJson("{\"name\":\"Chart 1\",\"x\":145,\"y\":133,\"width\":480,\"height\":300," +
    "\"startRow\":6,\"startRowOffset\":13,\"startColumn\":2,\"startColumnOffset\":21," +
    "\"endRow\":21,\"endRowOffset\":13,\"endColumn\":10,\"endColumnOffset\":5," +
    "\"isSelected\":true,\"typeName\":\"2\",\"chartSpace\":{\"typeName\":" +
    "\"chartSpace\",\"roundedCorners\":false,\"chart\":{\"title\":{\"txPr" +
    "\":{\"p\":[{\"elements\":[{\"elementType\":0,\"t\":\"\",\"rPr\":{" +
    "\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":18.67,\"b\":false,\"solidFill" +
    "\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff\":[35000]}}}}]," +
    "\"pPr\":{\"defRPr\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":18.67,\"b" +
    "\":false,\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff" +
    "\":[35000]}}}},\"endParaRPr\":{}}],\"bodyPr\":{},\"lstStyle\":{}},\"overlay" +
    "\":false,\"spPr\":{\"noFill\":true,\"ln\":{\"noFill\":true},\"effectLst\":{}}}," +
    "\"autoTitleDeleted\":false,\"plotArea\":{\"axes\":[{\"axisType\":0,\"axId\":31410946," +
    "\"delete\":false,\"majorTickMark\":2,\"minorTickMark\":2,\"tickLblPos\":2,\"axPos\":0," +
    "\"scaling\":{\"orientation\":1},\"spPr\":{\"ln\":{\"solidFill\":{\"schemeClr\":{\"val" +
    "\":1,\"lumMod\":[15000],\"lumOff\":[85000]}}}},\"numFmt\":{\"formatCode\":\"General\"}," +
    "\"txPr\":{\"p\":[{\"elements\":[{\"elementType\":0,\"t\":\"\",\"rPr\":{\"latin\":{\"typeface" +
    "\":\"+mn-lt\"},\"sz\":12,\"b\":false,\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000]," +
    "\"lumOff\":[35000]}}}}],\"pPr\":{\"defRPr\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":12,\"b" +
    "\":false,\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff\":[35000]}}}}," +
    "\"endParaRPr\":{}}]},\"auto\":true,\"lblOffset\":0,\"tickMarkSkip\":1,\"noMultiLvlLbl\":true," +
    "\"AxisGroup\":0,\"AxisType\":0,\"crosses\":1,\"crossAx\":38384719},{\"axisType\":3,\"axId\":38384719," +
    "\"delete\":false,\"majorTickMark\":2,\"minorTickMark\":2,\"tickLblPos\":2,\"axPos\":1,\"scaling" +
    "\":{\"orientation\":1},\"spPr\":{\"ln\":{\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[15000]," +
    "\"lumOff\":[85000]}}}},\"numFmt\":{\"formatCode\":\"General\"},\"txPr\":{\"p\":[{\"elements\":[{" +
    "\"elementType\":0,\"t\":\"\",\"rPr\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":12,\"b\":false," +
    "\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff\":[35000]}}}}],\"pPr\":{\"defRPr" +
    "\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":12,\"b\":false,\"solidFill\":{\"schemeClr\":{\"val\":1," +
    "\"lumMod\":[65000],\"lumOff\":[35000]}}}},\"endParaRPr\":{}}]},\"majorGridlines\":{\"spPr\":{\"ln\":{" +
    "\"solidFill\":{\"srgbClr\":{\"val\":[217,217,217]}},\"w\":1},\"effectLst\":{}}},\"AxisGroup\":0," +
    "\"AxisType\":1,\"crosses\":1,\"crossBetween\":0,\"crossAx\":31410946}],\"chartGroups\":[{\"chartType" +
    "\":6,\"ser\":[{\"seriesType\":0,\"idx\":0,\"order\":0,\"tx\":{\"strRef\":{\"f\":\"Sheet1!$A$2\"}}," +
    "\"cat\":{\"strRef\":{\"f\":\"Sheet1!$B$1:$D$1\"}},\"val\":{\"numRef\":{\"f\":\"Sheet1!$B$2:$D$2\"," +
    "\"numCache\":{\"formatCode\":\"General\"}}},\"shape\":2,\"invertIfNegative\":false},{\"seriesType" +
    "\":0,\"idx\":1,\"order\":1,\"tx\":{\"strRef\":{\"f\":\"Sheet1!$A$3\"}},\"cat\":{\"strRef\":{\"f\":" +
    "\"Sheet1!$B$1:$D$1\"}},\"val\":{\"numRef\":{\"f\":\"Sheet1!$B$3:$D$3\",\"numCache\":{\"formatCode" +
    "\":\"General\"}}},\"shape\":2,\"invertIfNegative\":false},{\"seriesType\":0,\"idx\":2,\"order\":2," +
    "\"tx\":{\"strRef\":{\"f\":\"Sheet1!$A$4\"}},\"cat\":{\"strRef\":{\"f\":\"Sheet1!$B$1:$D$1\"}},\"val" +
    "\":{\"numRef\":{\"f\":\"Sheet1!$B$4:$D$4\",\"numCache\":{\"formatCode\":\"General\"}}},\"shape\":2," +
    "\"invertIfNegative\":false},{\"seriesType\":0,\"idx\":3,\"order\":3,\"tx\":{\"strRef\":{\"f\":" +
    "\"Sheet1!$A$5\"}},\"cat\":{\"strRef\":{\"f\":\"Sheet1!$B$1:$D$1\"}},\"val\":{\"numRef\":{\"f\":" +
    "\"Sheet1!$B$5:$D$5\",\"numCache\":{\"formatCode\":\"General\"}}},\"shape\":2,\"invertIfNegative" +
    "\":false},{\"seriesType\":0,\"idx\":4,\"order\":4,\"tx\":{\"strRef\":{\"f\":\"Sheet1!$A$6\"}}," +
    "\"cat\":{\"strRef\":{\"f\":\"Sheet1!$B$1:$D$1\"}},\"val\":{\"numRef\":{\"f\":\"Sheet1!$B$6:$D$6\"," +
    "\"numCache\":{\"formatCode\":\"General\"}}},\"shape\":2,\"invertIfNegative\":false}],\"axId\":[31410946," +
    "38384719],\"barDir\":1,\"grouping\":1,\"gapWidth\":150,\"varyColors\":false,\"overlap\":-27}],\"spPr\":{" +
    "\"noFill\":true,\"ln\":{\"noFill\":true}}},\"legend\":{\"legendPos\":4,\"spPr\":{\"noFill\":true,\"ln\":{" +
    "\"noFill\":true}},\"txPr\":{\"p\":[{\"elements\":[{\"elementType\":0,\"t\":\"\",\"rPr\":{\"latin\":{\"typeface" +
    "\":\"+mn-lt\"},\"sz\":12,\"b\":false,\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff" +
    "\":[35000]}}}}],\"pPr\":{\"defRPr\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"sz\":12,\"b\":false,\"solidFill" +
    "\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff\":[35000]}}}},\"endParaRPr\":{}}]}},\"plotVisOnly" +
    "\":true,\"dispBlanksAs\":1,\"dispNaAsBlank\":false},\"spPr\":{\"solidFill\":{\"schemeClr\":{\"val\":0}}," +
    "\"ln\":{\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[15000],\"lumOff\":[85000]}},\"w\":1}},\"txPr" +
    "\":{\"p\":[{\"elements\":[{\"elementType\":0,\"t\":\"\",\"rPr\":{\"latin\":{\"typeface\":\"+mn-lt\"}," +
    "\"b\":false,\"solidFill\":{\"schemeClr\":{\"val\":1,\"lumMod\":[65000],\"lumOff\":[35000]}}}}],\"pPr" +
    "\":{\"defRPr\":{\"latin\":{\"typeface\":\"+mn-lt\"},\"b\":false,\"solidFill\":{\"schemeClr\":{\"val" +
    "\":1,\"lumMod\":[65000],\"lumOff\":[35000]}}}},\"endParaRPr\":{}}]}},\"useAnimation\":false}");

// picture from json
IShape picture = worksheet.getShapes().addPicture( null, 0, 0, 100, 100);
//update picture from json
picture.fromJson("{\"name\":\"Picture1\",\"x\":350,\"y\":10,\"width\":25,\"height\":25,\"startRow" +
    "\":0,\"startRowOffset\":10,\"startColumn\":5,\"startColumnOffset\":40,\"endRow\":6,\"endRowOffset" +
    "\":3,\"endColumn\":13,\"endColumnOffset\":22,\"isSelected\":true,\"typeName\":\"1\",\"src\":" +
    "\"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAA" +
    "RnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnQAABJ0Ad5mH3gAAAGASURBVDhPY/wPBAwUACYojRX8+/cPysINaOsCYgBWA0" +
    "4dvsyQ5FjNYM4ZBsbJTtUMp49chspCwIe3n8E0hhea0qcxrJy1jYGNgZWBk48DKPKf4funnwy/Gf4wRGR4MTAyMzCsmLq" +
    "dQVZJgmHb3VlAaSRw/ui1/5oMXv8NGAP+b1mxHyr6H8g+8F8fKGYrEP3fiifyvwVn+H8uBgOwHIoXtiw7yMDPycugZ6XO4" +
    "B3uABVlALLtGQyAYoyMjAzCkgIMlbPTGL7+Pw+WgxuwecV+htO7LjGwcrAyMELFkAEjEyPDrx+/GcxcdRn8op2golADuBg" +
    "NGdqSZjK8ef4B5CeGC8duMmxffQisAAR2rDnMcP7IdYaP3z8zeEbaQUUhAByIXirpDI/uPmMIy/RkYPzHyLBy5jYGFgZmBl5B" +
    "brCiz++/AoPwL0NwihtD4+xcsBgcgAx49/oDiIKDU4cu/U90qPpvwhr835gl+H+8XcX/4wfOQ2VRwSBNiaQACg1gYAAANcPHor" +
    "58W6sAAAAASUVORK5CYII=\",\"backColor\":\"#FFFFFF\",\"borderRadius\":3,\"borderStyle\":" +
    "\"solid\",\"borderColor\":\"#000000\",\"originalWidth\":15,\"originalHeight\":15}");
        
//save to an excel file
workbook.save("ShapeChartPictureFromJson.xlsx");

Please note:

Shape, chart and picture use the same IShape interface for importing or exporting json string. However, it is necessary that the json information matches the caller's type. For example, if IShape is a chart, and json contains a picture, using IShape.FromJson could cause some unexpected error.
When the shape type is a slicer or comment, the fromJson and ToJson methods of ISlicer and IComment interface should be used.

Range

Refer to the following example code which uses IRange.fromJson method to update a range from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getRange("B2:D4").fromJson("{\"0\":{\"0\":{\"value\":1},\"1\":{\"value\":2}},\"1\":{\"0\":{\"value\":\"aaa\",\"style\":{" +
    "\"backColor\":\"rgb(173,216,230)\",\"font\":\"normal normal 11pt Calibri,sans-serif\",\"foreColor\":\"Text 1\",\"themeFont\":" +
    "\"Body\",\"borderLeft\":{\"color\":null,\"style\":0},\"borderTop\":{\"color\":null,\"style\":0},\"borderRight\":{\"color" +
    "\":null,\"style\":0},\"borderBottom\":{\"color\":null,\"style\":0},\"borderHorizontal\":{\"color\":null,\"style\":0}," +
    "\"borderVertical\":{\"color\":null,\"style\":0},\"locked\":true,\"hAlign\":3,\"vAlign\":2,\"textIndent\":0,\"wordWrap\":false," +
    "\"shrinkToFit\":false,\"formatter\":\"General\",\"quotePrefix\":false}},\"1\":{\"value\":\"bbb\",\"style\":{\"backColor\":" +
    "\"rgb(173,216,230)\",\"font\":\"normal normal 11pt Calibri,sans-serif\",\"foreColor\":\"Text 1\",\"themeFont\":\"Body\"," +
    "\"borderLeft\":{\"color\":null,\"style\":0},\"borderTop\":{\"color\":null,\"style\":0},\"borderRight\":{\"color\":null,\"style" +
    "\":0},\"borderBottom\":{\"color\":null,\"style\":0},\"borderHorizontal\":{\"color\":null,\"style\":0},\"borderVertical\":{\"color" +
    "\":null,\"style\":0},\"locked\":true,\"hAlign\":3,\"vAlign\":2,\"textIndent\":0,\"wordWrap\":false,\"shrinkToFit\":false,\"formatter" +
    "\":\"General\",\"quotePrefix\":false}},\"2\":{\"style\":{\"backColor\":\"rgb(173,216,230)\",\"font\":\"normal normal 11pt Calibri," +
    "sans-serif\",\"foreColor\":\"Text 1\",\"themeFont\":\"Body\",\"borderLeft\":{\"color\":null,\"style\":0},\"borderTop\":{\"color\":null," +
    "\"style\":0},\"borderRight\":{\"color\":null,\"style\":0},\"borderBottom\":{\"color\":null,\"style\":0},\"borderHorizontal\":{\"color" +
    "\":null,\"style\":0},\"borderVertical\":{\"color\":null,\"style\":0},\"locked\":true,\"hAlign\":3,\"vAlign\":2,\"textIndent\":0," +
    "\"wordWrap\":false,\"shrinkToFit\":false,\"formatter\":\"General\",\"quotePrefix\":false}}},\"2\":{\"0\":{\"style\":{\"backColor" +
    "\":\"rgb(173,216,230)\",\"font\":\"normal normal 11pt Calibri,sans-serif\",\"foreColor\":\"Text 1\",\"themeFont\":\"Body\",\"borderLeft" +
    "\":{\"color\":null,\"style\":0},\"borderTop\":{\"color\":null,\"style\":0},\"borderRight\":{\"color\":null,\"style\":0},\"borderBottom" +
    "\":{\"color\":null,\"style\":0},\"borderHorizontal\":{\"color\":null,\"style\":0},\"borderVertical\":{\"color\":null,\"style\":0}," +
    "" +
    "" +
    "\"locked\":true,\"hAlign\":3,\"vAlign\":2,\"textIndent\":0,\"wordWrap\":false,\"shrinkToFit\":false,\"formatter\":\"General\"," +
    "\"quotePrefix\":false}},\"1\":{\"style\":{\"backColor\":\"rgb(173,216,230)\",\"font\":\"normal normal 11pt Calibri,sans-serif\"," +
    "\"foreColor\":\"Text 1\",\"themeFont\":\"Body\",\"borderLeft\":{\"color\":null,\"style\":0},\"borderTop\":{\"color\":null,\"style" +
    "\":0},\"borderRight\":{\"color\":null,\"style\":0},\"borderBottom\":{\"color\":null,\"style\":0},\"borderHorizontal\":{\"color" +
    "\":null,\"style\":0},\"borderVertical\":{\"color\":null,\"style\":0},\"locked\":true,\"hAlign\":3,\"vAlign\":2,\"textIndent\":0," +
    "\"wordWrap\":false,\"shrinkToFit\":false,\"formatter\":\"General\",\"quotePrefix\":false}},\"2\":{\"style\":{\"backColor\":" +
    "\"rgb(173,216,230)\",\"font\":\"normal normal 11pt Calibri,sans-serif\",\"foreColor\":\"Text 1\",\"themeFont\":\"Body\",\"borderLeft" +
    "\":{\"color\":null,\"style\":0},\"borderTop\":{\"color\":null,\"style\":0},\"borderRight\":{\"color\":null,\"style\":0},\"borderBottom" +
    "\":{\"color\":null,\"style\":0},\"borderHorizontal\":{\"color\":null,\"style\":0},\"borderVertical\":{\"color\":null,\"style\":0}," +
    "\"locked\":true,\"hAlign\":3,\"vAlign\":2,\"textIndent\":0,\"wordWrap\":false,\"shrinkToFit\":false,\"formatter\":\"General\",\"quotePrefix" +
    "\":false}}}}");

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

Please note:

When IRange.fromJson is used, the range can only be a single area (like getRange["*A1:B2*"]). Otherwise, a NotSupportedException would be thrown (when getRange["*A1:B2, C3:D4*"]).
The cell position in json is treated as a relative position when using IRange.fromJson. If range is "B2:C3", the first cell data in json would be set to "B2" regardless of the cell index in json.
If the position of cell in json is out of the range, the data is lost.

Slicer

Refer to the following example code which uses ISlicer.fromJson method to update a slicer from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1:F16").setValue(new Object[][]{
    {"Order ID", "Product", "Category", "Amount", "Date", "Country" },
    {1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 1, 6), "United States"  },
    {2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 1, 7), "United Kingdom"},
    {3, "Banana","Fruit", 617, new GregorianCalendar(2018, 1, 8), "United States" },
    {4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 1, 10), "Canada" },
    {5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 1, 10), "Germany" },
    {6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 1, 11), "United States" },
    {7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 1, 11), "Australia" },
    {8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 1, 16), "New Zealand" },
    {9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 1, 16), "France" },
    {10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 1, 16), "Canada" },
    {11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 1, 16), "Germany" },
    {12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 1, 18), "United States" },
    {13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 1, 20), "Germany" },
    {14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 1, 22), "Canada" },
    {15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 1, 24), "France" },
});
        
ITable table = worksheet.getTables().add(worksheet.getRange("A1:F16"), true);

ISlicerCache cache = workbook.getSlicerCaches().add(table, "Category");

ISlicer slicer1 = cache.getSlicers().add(worksheet, "cate1", "Category", 200, 200, 100, 200);
//update slicer from json
slicer1.fromJson("("{\"name\":\"cate2\",\"x\":400,\"y\":100,\"width\":133.33333333333334,\"height" +
    "\":266.66666666666663,\"dynamicMove\":false,\"dynamicSize\":false,\"sourceName\":\"Product\"," +
    "\"nameInFormula\":\"Slicer_Category\",\"captionName\":\"Category\",\"columnCount\":1,\"itemHeight" +
    "\":23.666666666666668,\"showHeader\":true,\"sortState\":2,\"style\":{\"name\":\"SlicerStyleLight2\"}," +
    "\"tableName\":\"Table1\",\"columnName\":\"Category\"}");");

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

Please note:

ISlicer.fromJson method cannot be used for filtering because the filter information is not stored in slicer's json (based on SpreadJS design)
If the slicer in json has the same name as an existing slicer, an exception is thrown.

Comments

Refer to the following example code which uses IComment.fromJson method to update a comment from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
        
IComment comment = worksheet.getRange("A1").addComment("Comment1");
        
//update comment from json
comment.fromJson("{\"text\":\"Comment Test\",\"location\":{\"x\":595.666666666667,\"y\":259.666666666667},\"width" +
    "\":100,\"height\":80,\"fontFamily\":\"Tahoma\",\"fontWeight\":\"bold\",\"foreColor\":\"rgb(165,165,165)\",\"backColor\":" + 
    "\"rgb(255,255,225)\",\"dynamicMove\":false,\"dynamicSize\":false,\"borderWidth\":1.33333333333333,\"borderStyle\":\"solid\"," + 
    "\"borderColor\":\"rgb(0,0,0)\",\"zIndex\":0,\"rowIndex\":0,\"colIndex\":0}");
        
//save to an excel file
workbook.save("CommentFromJson.xlsx");

Defined Names

Refer to the following example code which uses IName.fromJson method to generate the defined names from a json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
        
//generate INames from Json
workbook.getNames().fromJson("[{\"name\":\"Test\",\"formula\":\"100\",\"row\":0,\"col\":0},{\"name\":" +
    "\"Test2\",\"formula\":\"200\",\"row\":0,\"col\":0}]");
    
//IName
IName name = worksheet.getNames().add("temp", "test");
name.fromJson("{\"name\":\"Test3\",\"formula\":\"Sheet1!$H$8\",\"row\":0,\"col\":0}");
        
//save to an excel file
workbook.save("DefinedNamesFromJson.xlsx");

Page Setup

Refer to the following example code which uses IPageSetup.fromJson method to update page setup from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
        
//update page setup from json
worksheet.getPageSetup().fromJson("{\"bestFitRows\":true,\"bestFitColumns\":true,\"showBorder" +
    "\":false,\"showColumnHeader\":33,\"showRowHeader\":17,\"headerLeft\":23,\"headerCenter" +
    "\":14,\"headerRight\":66,\"footerLeft\":22,\"footerCenter\":11,\"footerRight\":12,\"headerLeftImage" +
    "\":51,\"headerCenterImage\":23,\"headerRightImage\":12,\"footerLeftImage\":63,\"footerCenterImage" +
    "\":21,\"footerRightImage\":12,\"margin\":{\"top\":80,\"bottom\":80,\"left\":30,\"right\":30,\"header" +
    "\":20,\"footer\":40},\"paperSize\":{\"width\":850,\"height\":1100,\"kind\":1}}");
        
//save to an excel file
workbook.save("PageSetupFromJson.xlsx");

Protection Options

Refer to the following example code which uses IProtectionSettings.fromJson method to update protection settings of a worksheet from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
        
//update protection settings from json
worksheet.getProtectionSettings().fromJson("{\"allowSelectLockedCells\":true,\"allowSelectUnlockedCells" +
    "\":true,\"allowSort\":true,\"allowFilter\":true,\"allowResizeRows\":true,\"allowResizeColumns" +
    "\":true,\"allowEditObjects\":true,\"allowDragInsertRows\":true,\"allowDragInsertColumns\":true," +
    "\"allowInsertRows\":true,\"allowInsertColumns\":true,\"allowDeleteRows\":true,\"allowDeleteColumns\":true}");
        
        
//save to an excel file
workbook.save("ProtectionOptionsFromJson.xlsx");

Data Validation

Refer to the following example code which uses IValidation.fromJson method to update a validation from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1:B2").setValue(new Object[][]{
{1, 10 },
{5, 20 }
});

// validation from json
worksheet.getRange("A1:B2").getValidation().fromJson("{\"inputTitle\":\"tip\",\"inputMessage\"" +
    ":\"Value must be between 5 and 20.\",\"type\":1,\"condition\":{\"conType\":0,\"compareType\":1,\"item1" +
    "\":{\"conType\":1,\"compareType\":3,\"expected\":\"5\",\"integerValue\":true},\"item2\":{\"conType\":1," +
    "\"compareType\":5,\"expected\":\"20\",\"integerValue\":true},\"ignoreBlank\":true},\"ranges\":\"A1\"," +
    "\"highlightStyle\":\"{\\\"type\\\":0,\\\"color\\\":\\\"red\\\"}\"}");

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

Refer to the following example code which uses IValidation.toJson method to export the validation to json string.

//create a memory stream to store json
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

// Create a validation
worksheet.getRange("C2:E4").getValidation().add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 8);

IValidation validation = worksheet.getRange("C2:E4").getValidation();
validation.setIgnoreBlank(true);
validation.setInputTitle("Tips");
validation.setInputMessage("Input a value between 1 and 8, please");
validation.setErrorTitle("Error");
validation.setErrorMessage("input value does not between 1 and 8");
validation.setShowError(true);
validation.setShowInputMessage(true);

// validation to json
String json = validation.toJson();
        
try{
    outputStream.write(json.getBytes(Charset.forName("UTF-8")));
} catch (IOException e){
    e.printStackTrace();
}

Please note:

When IValidation.fromJson method is used, data validation in the current range is cleared first and new data validation is then applied at the current range.
The usual usage of IValidation.fromJson method is like:

sheet.getRange["A1:B2"].getValidation().fromJson("...\"ranges\":\"C3:D4\"...");

where DsExcel API and json data both provide the range information. When applying data validation, the former is applied and the latter is ignored.

Conditional Formatting

Refer to the following example code which uses IFormatConditions.fromJson method to update conditional formats in a range from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

Object data = new Object[][]{
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
{"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
{"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
{"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
{"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
};

worksheet.getRange("B:C").setColumnWidthInPixel(80);
worksheet.getRange("A1:F7").setValue(data);

worksheet.getRange("E2:E7").getFormatConditions().fromJson("{\"rules\":[{\"ruleType\":13,\"ranges\":[{\"row" +
    "\":1,\"rowCount\":6,\"col\":4,\"colCount\":1}],\"iconSetType\":5,\"iconCriteria\":[{\"isGreaterThanOrEqualTo" +
    "\":true,\"iconValueType\":4,\"iconValue\":33},{\"isGreaterThanOrEqualTo\":true,\"iconValueType\":4,\"iconValue" +
    "\":67}],\"priority\":2,\"icons\":[{\"iconSetType\":5,\"iconIndex\":0},{\"iconSetType\":5,\"iconIndex\":1},{\"iconSetType" +
    "\":5,\"iconIndex\":2}]},{\"ruleType\":1,\"operator\":6,\"stopIfTrue\":true,\"ranges\":[{\"row\":1,\"rowCount\":6,\"col" +
    "\":4,\"colCount\":1}],\"value1\":\"66\",\"value2\":\"70\"}]}");

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

Refer to the following example code which uses IFormatConditions.toJson method to export conditional formats to json string.

//create a memory stream to store json
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

Object data = new Object[][]{
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
{"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
{"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
{"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
{"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
};

worksheet.getRange("A1:F7").setValue(data);

// weight between 66 and 70, set its interior color to LightGreen.
IFormatCondition condition = (IFormatCondition) worksheet.getRange("E2:E7").getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Between, 66, 70);
condition.getInterior().setColor(Color.GetLightGreen());

// icon set rule.
IIconSetCondition condition2 = worksheet.getRange("E2:E7").getFormatConditions().addIconSetCondition();
condition2.setIconSet(workbook.getIconSets().get(IconSetType.Icon3Symbols));
condition2.getIconCriteria().get(1).setOperator(FormatConditionOperator.GreaterEqual);
condition2.getIconCriteria().get(1).setValue(30);
condition2.getIconCriteria().get(1).setType(ConditionValueTypes.Percent);
condition2.getIconCriteria().get(2).setOperator(FormatConditionOperator.GreaterEqual);
condition2.getIconCriteria().get(2).setValue(70);
condition2.getIconCriteria().get(2).setType(ConditionValueTypes.Percent);

// conditional formats to json
String json = worksheet.getRange("E2:E7").getFormatConditions().toJson();
        
try{
    outputStream.write(json.getBytes(Charset.forName("UTF-8")));
} catch (IOException e){
    e.printStackTrace();
}

Refer to the following example code which uses ITop10.fromJson method to update top 10 conditional format from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

Object data = new Object[][]{
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
{"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
{"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
{"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
{"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
};

worksheet.getRange("B:C").setColumnWidthInPixel(80);
worksheet.getRange("A1:F7").setValue(data);

ITop10 top10 = worksheet.getRange("F2:F7").getFormatConditions().addTop10();
top10.fromJson("{\"ruleType\":5,\"style\":{\"backColor\":\"Accent 5\",\"hAlign\":3,\"vAlign" +
    "\":0,\"locked\":true,\"textIndent\":null,\"cellButtons\":null},\"type\":0,\"rank\":\"3" +
    "\",\"ranges\":[{\"row\":1,\"rowCount\":6,\"col\":5,\"colCount\":1}]}");

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

Refer to the following example code which uses ITop10.toJson method to export the top 10 conditional format to json string.

//create a memory stream to store json
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

Object data = new Object[][]{
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
{"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
{"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
{"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
{"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
};

worksheet.getRange("A1:F7").setValue(data);

ITop10 top10 = worksheet.getRange("F2:F7").getFormatConditions().addTop10();

top10.setRank(3);
top10.setNumberFormat("0.00");
top10.getInterior().setColor(Color.FromArgb(91, 155, 213));

// top10 to json
String json = top10.toJson();


try{
outputStream.write(json.getBytes(Charset.forName("UTF-8")));
} catch (IOException e){
e.printStackTrace();
}

Please note:

When IFormatConditions.fromJson is used, the format conditions in the range are cleared first and new format conditions are then applied from json string.
When the fromJson method of IFormatCondition, ITop10, IAboveAverage, IUniqueValues, IColorScale, IDataBar and IIconSetCondition interface is used, the FormatConditionType in json must be the same type as the caller. Otherwise, an InvalidOperationException is thrown.
DsExcel uses the caller's range to generate the new conditional formats and the range information in json is lost.

Named Style

Refer to the following example code which uses IStyle.fromJson method to update an existing named style from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

// Create a temp style
IStyle style = workbook.getStyles().add("test");

// FromJson
style.fromJson("{\"backColor\":\"#4472c4\",\"foreColor\":\"#ffffff\",\"hAlign\":3,\"vAlign" +
    "\":0,\"font\":\"italic 11pt Calibri\",\"borderLeft\":{\"color\":\"Accent 2\",\"style\":5}," +
    "\"borderTop\":{\"color\":\"Accent 2\",\"style\":5},\"borderRight\":{\"color\":\"Accent 2\"," +
    "\"style\":5},\"borderBottom\":{\"color\":\"Accent 2\",\"style\":5},\"locked\":true,\"textIndent" +
    "\":null,\"cellButtons\":[]}");
worksheet.getRange("D4").setValue("Google");
worksheet.getRange("D4").setStyle(style);

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

Refer to the following example code which uses IStyle.toJson method to export the named style to json string.

//create a memory stream to store json
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

//create a new workbook
Workbook workbook = new Workbook();
// Create a temp style
IStyle style = workbook.getStyles().add("CustomStyle1");
style.getInterior().setColor(Color.FromArgb(68, 114, 196));
style.getFont().setColor(Color.GetWhite());
style.getFont().setItalic(true);
style.getFont().setSize(18);
style.getBorders().setColor(Color.GetDarkOrange());
style.getBorders().setLineStyle(BorderLineStyle.Medium);

// style to json
String json = style.toJson();
        
try{
    outputStream.write(json.getBytes(Charset.forName("UTF-8")));
} catch (IOException e){
    e.printStackTrace();
}

Sparkline

Refer to the following example code which uses ISparkline.fromJson method to update a sparkline from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

worksheet.getRange("B2:K5").setValue(new Object[][] {
{"Number", "Date", "Customer", "Description", "Trend", "0-30 Days", "30-60 Days", "60-90 Days", ">90 Days", "Amount"},
{"1001", new GregorianCalendar(2017, 5, 21), "Customer A", "Invoice 1001", null, 1200.15, 1916.18, 1105.23, 1806.53, null},
{"1002", new GregorianCalendar(2017, 3, 18), "Customer B", "Invoice 1002", null, 896.23, 1005.53, 1800.56, 1150.49, null},
{"1003", new GregorianCalendar(2017, 6, 15), "Customer C", "Invoice 1003", null, 827.63, 1009.23, 1869.23, 1002.56, null}
});

worksheet.getRange("B:K").setColumnWidth(15);

worksheet.getTables().add(worksheet.getRange("B2:K5"), true);
worksheet.getTables().get(0).getColumns().get(9).getDataBodyRange().setFormula("=SUM(Table1[@[0-30 Days]:[>90 Days]])");
worksheet.getRange("F3:F5").getSparklineGroups().add(SparkType.Line, "G3:J5");

worksheet.getRange("F3").getSparklineGroups().get(0).get(0).fromJson("{\"row\":2,\"col\":5,\"orientation\":1," +
    "\"data\":{\"row\":2,\"col\":6,\"rowCount\":1,\"colCount\":5}}");

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

Refer to the following example code which uses ISparkline.toJson method to export a sparkline to json string.

//create a memory stream to store json
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

worksheet.getRange("B2:K5").setValue(new Object[][]
{
{"Number", "Date", "Customer", "Description", "Trend", "0-30 Days", "30-60 Days", "60-90 Days", ">90 Days", "Amount"},
{"1001", new GregorianCalendar(2017, 5, 21), "Customer A", "Invoice 1001", null, 1200.15, 1916.18, 1105.23, 1806.53, null},
{"1002", new GregorianCalendar(2017, 3, 18), "Customer B", "Invoice 1002", null, 896.23, 1005.53, 1800.56, 1150.49, null},
{"1003", new GregorianCalendar(2017, 6, 15), "Customer C", "Invoice 1003", null, 827.63, 1009.23, 1869.23, 1002.56, null}
});
worksheet.getRange("B:K").setColumnWidth(15);

worksheet.getTables().add(worksheet.getRange("B2:K5"), true);
worksheet.getTables().get(0).getColumns().get(9).getDataBodyRange().setFormula("=SUM(Table1[@[0-30 Days]:[>90 Days]])");
worksheet.getRange("F3:F5").getSparklineGroups().add(SparkType.Line, "G3:J5");

// sparkline to json
String json = worksheet.getRange("F3:F5").getSparklineGroups().get(0).toJson();

        
try{
    outputStream.write(json.getBytes(Charset.forName("UTF-8")));
} catch (IOException e){
    e.printStackTrace();
}

Please note:

SpreadJS has two kinds of sparkline, one is consistent with Excel, and the other is extended by SpreadJS. DsExcel supports the former's toJson and fromJson. The latter can be set through formula.
If you want to use toJson and fromJson methods of sparklineGroup and sparkline, there must exist a sparklineGroup or sparkline in the current range, otherwise an out-of-bounds array exception is thrown.
Location range information applies the same rules as data validation.
Data range is updated by the data range from json string.
DsExcel uses sparkline from json data as much as possible, but if the data size exceeds the size of selected range, it is discarded.

Table

Refer to the following example code which uses ITables.fromJson method to generate a table from json string.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

worksheet.getRange("A1:F7").setValue(new Object[][] {
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new GregorianCalendar(1968, 6, 8), "Blue", 67, 165},
{"Nia", "New York", new GregorianCalendar(1972, 7, 3), "Brown", 62, 134},
{"Jared", "New York", new GregorianCalendar(1964, 3, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new GregorianCalendar(1972, 8, 8), "Blue", 66, 163},
{"Damon", "Washington", new GregorianCalendar(1986, 2, 2), "Hazel", 76, 176},
{"Angela", "Washington", new GregorianCalendar(1993, 2, 15), "Brown", 68, 145}
});

worksheet.getRange("B:C").setColumnWidth(10);
worksheet.getRange("D:D").setColumnWidth(11);

// tables from json
worksheet.getTables().fromJson("[{\"name\":\"Table1\",\"row\":0,\"col\":0,\"rowCount\":7,\"colCount" +
    "\":6,\"style\":{\"buildInName\":\"Medium2\"},\"rowFilter\":{\"range\":{\"row\":1,\"rowCount" +
    "\":6,\"col\":0,\"colCount\":6},\"typeName\":\"HideRowFilter\",\"dialogVisibleInfo\":{}," +
    "\"filterButtonVisibleInfo\":{\"0\":true,\"1\":true,\"2\":true,\"3\":true,\"4\":true,\"5" +
    "\":true},\"showFilterButton\":true},\"columns\":[{\"id\":1,\"name\":\"Name\"},{\"id\":2," +
    "\"name\":\"City\"},{\"id\":3,\"name\":\"Birthday\"},{\"id\":4,\"name\":\"Eye color\"},{\"id" +
    "\":5,\"name\":\"Weight\"},{\"id\":6,\"name\":\"Height\"}]}]");

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

Refer to the following example code which uses ITables.toJson method to export a table to json string.

//create a memory stream to store json
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

// Create table
worksheet.getTables().add(worksheet.getRange("A1:F7"), true);
worksheet.getTables().get(0).getColumns().get(0).setName("Name");
worksheet.getTables().get(0).getColumns().get(1).setName("City");
worksheet.getTables().get(0).getColumns().get(2).setName("Birthday");
worksheet.getTables().get(0).getColumns().get(3).setName("Eye color");
worksheet.getTables().get(0).getColumns().get(4).setName("Weight");
worksheet.getTables().get(0).getColumns().get(5).setName("Height");

// table to json
String json = worksheet.getTables().toJson();

        
try{
    outputStream.write(json.getBytes(Charset.forName("UTF-8")));
} catch (IOException e){
    e.printStackTrace();
}

Please note:

When ITables.fromJson and ITable.fromJson are used, the table(s) is cleared first to apply new table(s) from json string.
When ITables.fromJson and ITable.fromJson are used, the value of cell is not cleared.

Shape, Chart or Picture
Range
Slicer
Comments
Defined Names
Page Setup
Protection Options
Data Validation
Conditional Formatting
Named Style
Sparkline
Table