Import and Export from JSON string

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

var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["A1:D6"].Value = 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}
};

var shape = worksheet.Shapes.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}");


var chart = worksheet.Shapes.AddChart(ChartType.Line, 10, 10, 300, 300);
//update 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}");


var picture = worksheet.Shapes.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}");

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
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["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 Range["*A1:B2*"]). Otherwise, a NotSupportedException would be thrown (when Range["*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.

var workbook = new GrapeCity.Documents.Excel.Workbook();

object[,] sourceData = new object[,] {
    { "Order ID", "Product",  "Category",   "Amount", "Date",                    "Country" },
    { 1,          "Carrots",  "Vegetables",  4270,    new DateTime(2018, 1, 6),  "United States" },
    { 2,          "Broccoli", "Vegetables",  8239,    new DateTime(2018, 1, 7),  "United Kingdom" },
    { 3,          "Banana",   "Fruit",       617,     new DateTime(2018, 1, 8),  "United States" },
    { 4,          "Banana",   "Fruit",       8384,    new DateTime(2018, 1, 10), "Canada" },
    { 5,          "Beans",    "Vegetables",  2626,    new DateTime(2018, 1, 10), "Germany" },
    { 6,          "Orange",   "Fruit",       3610,    new DateTime(2018, 1, 11), "United States" },
    { 7,          "Broccoli", "Vegetables",  9062,    new DateTime(2018, 1, 11), "Australia" },
    { 8,          "Banana",   "Fruit",       6906,    new DateTime(2018, 1, 16), "New Zealand" },
    { 9,          "Apple",    "Fruit",       2417,    new DateTime(2018, 1, 16), "France" },
    { 10,         "Apple",    "Fruit",       7431,    new DateTime(2018, 1, 16), "Canada" },
    { 11,         "Banana",   "Fruit",       8250,    new DateTime(2018, 1, 16), "Germany" },
    { 12,         "Broccoli", "Vegetables",  7012,    new DateTime(2018, 1, 18), "United States" },
    { 13,         "Carrots",  "Vegetables",  1903,    new DateTime(2018, 1, 20), "Germany" },
    { 14,         "Broccoli", "Vegetables",  2824,    new DateTime(2018, 1, 22), "Canada" },
    { 15,         "Apple",    "Fruit",       6946,    new DateTime(2018, 1, 24), "France" },
};

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F16"].Value = sourceData;
ITable table = worksheet.Tables.Add(worksheet.Range["A1:F16"], true);

ISlicerCache cache = workbook.SlicerCaches.Add(table, "Category");

ISlicer slicer1 = cache.Slicers.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.

var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

IComment comment = worksheet.Range["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.

var workbook = new Workbook();
var worksheet = workbook.ActiveSheet;

//generate INames from json
workbook.Names.FromJson("[{\"name\":\"Test\",\"formula\":\"100\",\"row\":0,\"col\":0},{\"name\":" +
    "\"Test2\",\"formula\":\"200\",\"row\":0,\"col\":0}]");

//IName
IName name = worksheet.Names.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.

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];

//update pagesetup from json
sheet.PageSetup.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.

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];

//update protection settings from json
sheet.ProtectionSettings.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
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[,] {
    {1, 10 },
    {5, 20 }
};

worksheet.Range["A1:B2"].Value = data;

//update validation from json
worksheet.Range["A1:B2"].Validation.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
MemoryStream outputStream = new MemoryStream();

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var worksheet = workbook.ActiveSheet;

// Create a validation
worksheet.Range["C2:E4"].Validation.Add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 8);

IValidation validation = worksheet.Range["C2:E4"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "Input a value between 1 and 8, please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "input value does not between 1 and 8";
validation.ShowInputMessage = true;
validation.ShowError = true;

//export validation to json
string json = worksheet.Range["C2:E4"].Validation.ToJson();

StreamWriter writer = new StreamWriter(outputStream);
writer.Write(json);
writer.Flush();

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.Range["A1:B2"].Validation.FromJson("...\"ranges\":\"C3:D4\"...");
where GcExcel 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
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

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

worksheet.Range["B:C"].ColumnWidthInPixel = 80;
worksheet.Range["A1:F7"].Value = data;

//update conditional formats from json
worksheet.Range["E2:E7"].FormatConditions.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("conditionalformatsfromjson.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
MemoryStream outputStream = new MemoryStream();

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

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

//weight between 66 and 70, set its interior color to LightGreen.
IFormatCondition condition = worksheet.Range["E2:E7"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Between, 66, 70) as IFormatCondition;
condition.Interior.Color = System.Drawing.Color.LightGreen;

//icon set rule.
IIconSetCondition condition2 = worksheet.Range["E2:E7"].FormatConditions.AddIconSetCondition();
condition2.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
condition2.IconCriteria[1].Operator = FormatConditionOperator.GreaterEqual;
condition2.IconCriteria[1].Value = 30;
condition2.IconCriteria[1].Type = ConditionValueTypes.Percent;
condition2.IconCriteria[2].Operator = FormatConditionOperator.GreaterEqual;
condition2.IconCriteria[2].Value = 70;
condition2.IconCriteria[2].Type = ConditionValueTypes.Percent;

//export conditional formats to json
string json = worksheet.Range["E2:E7"].FormatConditions.ToJson();

StreamWriter writer = new StreamWriter(outputStream);
writer.Write(json);
writer.Flush();

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

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

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

worksheet.Range["B:C"].ColumnWidthInPixel = 80;
worksheet.Range["A1:F7"].Value = data;

//update top 10 rule from json
ITop10 top10 = worksheet.Range["F2:F7"].FormatConditions.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
MemoryStream outputStream = new MemoryStream();

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

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

ITop10 top10 = worksheet.Range["F2:F7"].FormatConditions.AddTop10();

top10.Rank = 3;
top10.NumberFormat = "0.00";
top10.Interior.Color = System.Drawing.Color.FromArgb(91, 155, 213);

//export top 10 rule to json
string json = top10.ToJson();

StreamWriter writer = new StreamWriter(outputStream);
writer.Write(json);
writer.Flush();

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.
GcExcel 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
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

// Create a temp style
var style = workbook.Styles.Add("test");

////update named styles from json
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.Range["D4"].Value = "grapecity";
worksheet.Range["D4"].Style = 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
MemoryStream outputStream = new MemoryStream();

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Create a temp style
var style = workbook.Styles.Add("CustomStyle1");
style.Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196);
style.Font.Color = System.Drawing.Color.White;
style.Font.Italic = true;
style.Font.Size = 18;
style.Borders.Color = System.Drawing.Color.DarkOrange;
style.Borders.LineStyle = BorderLineStyle.Medium;

//export style to json
string json = style.ToJson();

StreamWriter writer = new StreamWriter(outputStream);
writer.Write(json);
writer.Flush();

Sparkline

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

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[,]
{
    {"Number", "Date", "Customer", "Description", "Trend", "0-30 Days", "30-60 Days", "60-90 Days", ">90 Days", "Amount"},
    {"1001", new DateTime(2017, 5, 21), "Customer A", "Invoice 1001", null, 1200.15, 1916.18, 1105.23, 1806.53, null},
    {"1002", new DateTime(2017, 3, 18), "Customer B", "Invoice 1002", null, 896.23, 1005.53, 1800.56, 1150.49, null},
    {"1003", new DateTime(2017, 6, 15), "Customer C", "Invoice 1003", null, 827.63, 1009.23, 1869.23, 1002.56, null}
};

worksheet.Range["B2:K5"].Value = data;
worksheet.Range["B:K"].ColumnWidth = 15;

worksheet.Tables.Add(worksheet.Range["B2:K5"], true);
worksheet.Tables[0].Columns[9].DataBodyRange.Formula = "=SUM(Table1[@[0-30 Days]:[>90 Days]])";
worksheet.Range["F3:F5"].SparklineGroups.Add(SparkType.Line, "G3:J5");

//update sparkline from json
worksheet.Range["F3"].SparklineGroups[0][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
MemoryStream outputStream = new MemoryStream();

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

object[,] data = new object[,]
{
    {"Number", "Date", "Customer", "Description", "Trend", "0-30 Days", "30-60 Days", "60-90 Days", ">90 Days", "Amount"},
    {"1001", new DateTime(2017, 5, 21), "Customer A", "Invoice 1001", null, 1200.15, 1916.18, 1105.23, 1806.53, null},
    {"1002", new DateTime(2017, 3, 18), "Customer B", "Invoice 1002", null, 896.23, 1005.53, 1800.56, 1150.49, null},
    {"1003", new DateTime(2017, 6, 15), "Customer C", "Invoice 1003", null, 827.63, 1009.23, 1869.23, 1002.56, null}
};

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["B2:K5"].Value = data;
worksheet.Range["B:K"].ColumnWidth = 15;

worksheet.Tables.Add(worksheet.Range["B2:K5"], true);
worksheet.Tables[0].Columns[9].DataBodyRange.Formula = "=SUM(Table1[@[0-30 Days]:[>90 Days]])";
worksheet.Range["F3:F5"].SparklineGroups.Add(SparkType.Line, "G3:J5");

//export sparkline to json
string json = worksheet.Range["F3:F5"].SparklineGroups[0].ToJson();

StreamWriter writer = new StreamWriter(outputStream);
writer.Write(json);
writer.Flush();

Please note:

SpreadJS has two kinds of sparkline, one is consistent with Excel, and the other is extended by SpreadJS. GcExcel 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.
GcExcel 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
var workbook = new GrapeCity.Documents.Excel.Workbook();

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

worksheet.Range["A1:F7"].Value = data;
worksheet.Range["B:C"].ColumnWidth = 10;
worksheet.Range["D:D"].ColumnWidth = 11;

//generate tables from json
worksheet.Tables.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
MemoryStream outputStream = new MemoryStream();

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var worksheet = workbook.ActiveSheet;

// Create table
worksheet.Tables.Add(worksheet.Range["A1:F7"], true);
worksheet.Tables[0].Columns[0].Name = "Name";
worksheet.Tables[0].Columns[1].Name = "City";
worksheet.Tables[0].Columns[2].Name = "Birthday";
worksheet.Tables[0].Columns[3].Name = "Eye color";
worksheet.Tables[0].Columns[4].Name = "Weight";
worksheet.Tables[0].Columns[5].Name = "Height";

//export table to json
string json = worksheet.Tables.ToJson();

StreamWriter writer = new StreamWriter(outputStream);
writer.Write(json);
writer.Flush();

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.