Document Solutions for Excel, Java Edition | Document Solutions
File Operations / Import and Export JSON Stream / Import and Export from JSON string
In This Topic
    Import and Export from JSON string
    In This Topic

    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.

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

    Range

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

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

    Slicer

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

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

    Comments

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

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

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

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

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

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

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

    Conditional Formatting

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

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

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

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

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

    Named Style

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

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

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

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

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

    Table

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

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

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