Document Solutions for Excel, .NET 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.

    C#
    Copy Code 
    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:

    Range

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

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

    Slicer

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

    C#
    Copy Code 
    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:

    Comments

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

    C#
    Copy Code 
    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.

    C#
    Copy Code 
    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.

    C#
    Copy Code 
    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.

    C#
    Copy Code 
    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.

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

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

    Conditional Formatting

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

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

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

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

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

    Named Style

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

    C#
    Copy Code 
    //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 = "mescius";
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.

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

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

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

    Table

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

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

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