Document Solutions for Excel, .NET Edition | Document Solutions
File Operations / Import and Export SpreadJS Files / Import and Export JSON Files
In This Topic
    Import and Export JSON Files
    In This Topic

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

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

    C#
    Copy Code
    //Create a new workbook
    Workbook workbook = new Workbook();                      
                
    //Load SSJSON file 
    var stream = new System.IO.FileStream("Spread.ssjson", System.IO.FileMode.Open);
    workbook.FromJson(stream);
                
    //Save file 
    workbook.Save("workbook_ssjson.xlsx");

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


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

    Shapes

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

    Barcodes

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

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

    Cell Buttons

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

    Cell Dropdowns

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

    Form Controls

    DsExcel supports JSON I/O of SpreadJS files containing form controls like button, dropdown, checkbox, etc., allowing you to import and export form controls to an ssjson file. You can also download the JSON file containing form controls from here.

    Validation Styles

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

    Text Ellipsis

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

    Limitation

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

    Range Template

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

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

    Format String

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

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

    JSON Options

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

    SpreadJS (toJSON and fromJSON) DsExcel (ToJSON and FromJSON)
    Serialization

    ignoreStyle

    ignoreFormula

    rowHeadersAsFrozenColumns

    columnHeadersAsFrozenRows

    IgnoreStyle

    IgnoreFormula

    IgnoreColumnRowInfoOutOfUsedRange

    IgnoreRangeOutOfRowColumnCount

    ExportSharedFormula

    Deserialization

    ignoreStyle

    ignoreFormula

    frozenColumnsAsRowHeaders

    frozenRowsAsColumnHeaders

    doNotRecalculateAfterLoad

    ignoreStyle

    ignoreFormula

    doNotRecalculateAfterLoad


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

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

    C#
    Copy Code
    // ignore style and formula when serialize workbook to json
    string jsonWithOption = workbook.ToJson(new SerializationOptions() { IgnoreFormula = true, IgnoreStyle = true });
    
    workbook.FromJson(jsonWithOption);
    
    //save to an excel file
    workbook.Save("tojsonwithoptions.xlsx");

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

    C#
    Copy Code
    // ignore style and formula when deserialize workbook from json.
    workbook.FromJson(json, new DeserializationOptions() { IgnoreFormula = true, IgnoreStyle = true });
    
    //save to an excel file
    workbook.Save("fromjsonwithoptions.xlsx");

    You can control the size of exported JSON file by choosing whether you want to keep the style and size of rows and columns which are out of the used range. The IgnoreColumnRowInfoOutOfUsedRange property is provided in SerializationOptions class which:

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

    C#
    Copy Code
    var book = new Workbook();
    IWorksheet worksheet = book.Worksheets[0];
    //Add custom name style
    IStyle style = book.Styles.Add("testStyle1");
     
    style.Font.ThemeColor = ThemeColor.Accent1;
    style.Font.TintAndShade = 0.8;
    style.Font.Italic = true;
    style.Font.Bold = true;
    style.Font.Name = "LiSu";
    style.Font.Size = 28;
    style.Font.Strikethrough = true;
    style.Font.Subscript = true;
    style.Font.Superscript = false;
    style.Font.Underline = UnderlineType.Double;
     
    object[,] data = new object[,]{
        {"test", "test", "test", "test" },
        {"test", "test", "test", "test" },
        {"test", "test", "test", "test" },
        {"test", "test", "test", "test" },
        {"test", "test", "test", "test" },
    };
     
    worksheet.Range["B2:E6"].Value = data;
    worksheet.Range["A:XFD"].Style = book.Styles["testStyle1"];
    worksheet.Range["A:XFD"].ColumnWidth = 20;
        
    //Export sizes/styles of only used range to json
    using FileStream jsonFile = new FileStream("TestJson_true.json", FileMode.Create);
    book.ToJson(jsonFile, new SerializationOptions() { IgnoreColumnRowInfoOutOfUsedRange = true }); // Size of output file is 9KB
        
    //Export all sizes/styles to json
    using FileStream jsonFile2 = new FileStream("TestJson_false.json", FileMode.Create);
    book.ToJson(jsonFile2, new SerializationOptions() { IgnoreColumnRowInfoOutOfUsedRange = false }); // Size of output file is 793KB
        
    //Default behavior (same as true option)
    book.ToJson(new FileStream("TestJson_default.json", FileMode.Create)); // Size of output file is 9KB

    You can also control whether to export formulas as shared formulas when exporting to a JSON file using the ExportSharedFormula property in SerializationOptions class. This enables you to export the formulas as shared formulas when it is set to true (the default value). However, if the value is set to false, the formulas will be exported as individual formulas.

    In DsExcel v6.0.1 and higher versions, the formula is exported as a shared formula to a JSON file (or SSJSON file). Because the shared formula is not compatible with DsExcel versions less than or equal to v5 and SpreadJS versions less than or equal to v15, you can use this option for backward compatibility and skip shared formulas in the exported JSON file.

    The following example code exports formulas as shared formulas:

    C#
    Copy Code
    // Create a new workbook.
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    // Set options for iterative calculation.
    workbook.Options.Formulas.EnableIterativeCalculation = true;
    workbook.Options.Formulas.MaximumIterations = 20;
    var worksheet = workbook.Worksheets[0];
    
    // Set values and formulas.
    worksheet.Range["B2"].Value = "Initial Cash";
    worksheet.Range["C2"].Value = 10000;
    worksheet.Range["B3"].Value = "Interest";
    worksheet.Range["C3"].Value = 0.0125;
    
    worksheet.Range["B5"].Value = "Month";
    worksheet.Range["C5"].Value = "Total Cash";
    
    worksheet.Range["B6:B17"].Value = new double[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
    
    worksheet.Range["C6"].Formula = "=C2*(1+$C$3)";
    worksheet.Range["C7:C17"].Formula = "=C6*(1+$C$3)";
    
    // Initialize SerializationOptions and set ExportSharedFormula to true.
    SerializationOptions options = new SerializationOptions();
    options.ExportSharedFormula = true;
    
    // Save the JSON file.
    workbook.ToJson(File.Create("ExportSharedFormulas.json"), options);
    
    // Initialize SerializationOptions and set ExportSharedFormula to false.
    SerializationOptions options2 = new SerializationOptions();
    options2.ExportSharedFormula = false;
    
    // Save the JSON file.
    workbook.ToJson(File.Create("ExportIndividualFormulas.json"), options2);

    Note: SpreadJS supports multi-level row or column headers but DsExcel does not. However, you can still retain the header information in DsExcel by following the below steps:

    1. Use SpreadJS to export JSON with 'rowHeadersAsFrozenColumns or columnHeadersAsFrozenRows' option as true to convert multi-header to frozen area, and use DsExcel to load the JSON file.
    2. Manipulate the frozen area in DsExcel.
    3. Use DsExcel to export JSON file, and use SpreadJS to load JSON file with 'frozenColumnsAsRowHeaders or frozenRowsAsColumnHeaders ' option  as true to convert frozen area to header.

    Checkbox or Radiobutton List Cell Type

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

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

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    
    CheckBoxListCellType cellType = new CheckBoxListCellType
    {
        Direction = CellTypeDirection.Horizontal,
        TextAlign = CellTypeTextAlign.Right,
        IsFlowLayout = false,
        MaxColumnCount = 2,
        MaxRowCount = 1,
        HorizontalSpacing = 20,
        VerticalSpacing = 5,
    };
    cellType.Items.Add(new SelectFieldItem("sample1", "1"));
    cellType.Items.Add(new SelectFieldItem("sample2", "2"));
    cellType.Items.Add(new SelectFieldItem("sample3", "3"));
    cellType.Items.Add(new SelectFieldItem("sample4", "4"));
    cellType.Items.Add(new SelectFieldItem("sample5", "5"));
    worksheet.Range["A1"].RowHeight = 60;
    worksheet.Range["A1"].ColumnWidth = 25;
    worksheet.Range["A1"].CellType = cellType;
            
    //check multiple options in the check box list
     worksheet.Range["A1"].Value = new object[,]
    {
        {new object[]{"1", "3", "5" } }
    };
    
    //save to a pdf file
    workbook.Save("addcheckboxlistcelltype.pdf");

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    GrapeCity.Documents.Excel.Workbook.ValueJsonSerializer = new CustomObjectJsonSerializer();
    IWorksheet worksheet = workbook.Worksheets[0];
    
    CheckBoxListCellType cellType = new CheckBoxListCellType
    {
        Direction = CellTypeDirection.Horizontal,
        TextAlign = CellTypeTextAlign.Right,
        IsFlowLayout = false,
        MaxColumnCount = 2,
        MaxRowCount = 1,
        HorizontalSpacing = 20,
        VerticalSpacing = 5,
    };
    cellType.Items.Add(new SelectFieldItem("player1", new People { Name = "Tom", Age = 5 }));
    cellType.Items.Add(new SelectFieldItem("player2", new People { Name = "Jerry", Age = 3 }));
    cellType.Items.Add(new SelectFieldItem("player3", new People { Name = "Mario", Age = 6 }));
    cellType.Items.Add(new SelectFieldItem("player4", new People { Name = "Luigi", Age = 4 }));
    worksheet.Range["A1"].RowHeight = 60;
    worksheet.Range["A1"].ColumnWidth = 25;
    worksheet.Range["A1"].CellType = cellType;
    
    worksheet.Range["A1"].Value = new object[,]
    {
    {new object[]{ new People { Name = "Tom", Age = 5 }, new People { Name = "Mario", Age = 6 }} }
    };
    
    //save to a pdf file
    workbook.Save("addcheckboxlistcelltypecustomobject.pdf");
    }
    class CustomObjectJsonSerializer : IJsonSerializer
    {
        public object Deserialize(string json)
        {
            return Newtonsoft.Json.JsonConvert.DeserializeObject<People>(json);
        }
    
        public string Serialize(object value)
        {
            if (value is People)
            {
                return Newtonsoft.Json.JsonConvert.SerializeObject(value);
            }
            return null;
        }
    }
    class People
    {
        private int age;
        private string name;
    
        public int Age { get => age; set => age = value; }
        public string Name { get => name; set => name = value; }
    
        public override bool Equals(object obj)
        {
            return obj is People people &&
                   age == people.age &&
                   name == people.name;
        }
    
        public override int GetHashCode()
        {
            return age.GetHashCode() ^ name.GetHashCode();
        }
    }

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    
    RadioButtonListCellType cellType = new RadioButtonListCellType
    {
        Direction = CellTypeDirection.Horizontal,
        TextAlign = CellTypeTextAlign.Right,
        IsFlowLayout = false,
        MaxColumnCount = 2,
        MaxRowCount = 1,
        HorizontalSpacing = 20,
        VerticalSpacing = 5,
    };
    cellType.Items.Add(new SelectFieldItem("sample1", "1"));
    cellType.Items.Add(new SelectFieldItem("sample2", "2"));
    cellType.Items.Add(new SelectFieldItem("sample3", "3"));
    cellType.Items.Add(new SelectFieldItem("sample4", "4"));
    cellType.Items.Add(new SelectFieldItem("sample5", "5"));
    worksheet.Range["A1"].RowHeight = 60;
    worksheet.Range["A1"].ColumnWidth = 25;
    worksheet.Range["A1"].CellType = cellType;
    worksheet.Range["A1"].Value = 1;
            
    //check multiple options in the radio button list
     worksheet.Range["A1"].Value = new object[,]
    {
        {new object[]{"1", "3", "5" } }
    };
    
    //save to a pdf file
    workbook.Save("addradiobuttonlistcelltype.pdf");

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    
    GrapeCity.Documents.Excel.Workbook.ValueJsonSerializer = new CustomObjectJsonSerializer();
    IWorksheet worksheet = workbook.Worksheets[0];
    
    RadioButtonListCellType cellType = new RadioButtonListCellType
    {
        Direction = CellTypeDirection.Horizontal,
        TextAlign = CellTypeTextAlign.Right,
        IsFlowLayout = false,
        MaxColumnCount = 2,
        MaxRowCount = 1,
        HorizontalSpacing = 20,
        VerticalSpacing = 5,
    };
    cellType.Items.Add(new SelectFieldItem("player1", new People { Name = "Tom", Age = 5 }));
    cellType.Items.Add(new SelectFieldItem("player2", new People { Name = "Jerry", Age = 3 }));
    cellType.Items.Add(new SelectFieldItem("player3", new People { Name = "Mario", Age = 6 }));
    cellType.Items.Add(new SelectFieldItem("player4", new People { Name = "Luigi", Age = 4 }));
    worksheet.Range["A1"].RowHeight = 60;
    worksheet.Range["A1"].ColumnWidth = 25;
    worksheet.Range["A1"].CellType = cellType;
    
    worksheet.Range["A1"].Value = new object[,]
    {
    {new People { Name = "Tom", Age = 5 } }
    };
    
    //save to a pdf file
    workbook.Save("addradiobuttoncelltypecustomobject.pdf");
    }
    
    class CustomObjectJsonSerializer : IJsonSerializer
    {
        public object Deserialize(string json)
        {
            return Newtonsoft.Json.JsonConvert.DeserializeObject<People>(json);
        }
    
        public string Serialize(object value)
        {
            if (value is People)
            {
                return Newtonsoft.Json.JsonConvert.SerializeObject(value);
            }
            return null;
        }
    }
    class People
    {
        private int age;
        private string name;
    
        public int Age { get => age; set => age = value; }
        public string Name { get => name; set => name = value; }
    
        public override bool Equals(object obj)
        {
            return obj is People people &&
                   age == people.age &&
                   name == people.name;
        }
    
        public override int GetHashCode()
        {
            return age.GetHashCode() ^ name.GetHashCode();
        }
    }

    Cell Padding and Labels

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

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

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

    C#
    Copy Code
    // create a new workbook
    Workbook workbook = new Workbook();
    // get the sheet
    IWorksheet worksheet = workbook.Worksheets[0];
    // set row height
    worksheet.Range["A:A"].RowHeight=40;
    // set column width
    worksheet.Range["A:A"].ColumnWidth=25;
    // set watermark
    worksheet.Range["A1"].Watermark="JAVA";
    // set cell padding
    worksheet.Range["A1"].CellPadding=new CellPadding(50, 0, 0, 0);
    // set label options
    worksheet.Range["A1"].LabelOptions.Visibility = LabelVisibility.visible;
    worksheet.Range["A1"].LabelOptions.ForeColor = Color.Green;
    worksheet.Range["A1"].LabelOptions.Margin=new Margin(15, 0, 0, 0);
    worksheet.Range["A1"].LabelOptions.Font.Size=14;
    worksheet.Range["A1"].LabelOptions.Font.Name="Calibri";
    worksheet.Range["A1"].Borders.LineStyle=BorderLineStyle.Thin;
    
    // save to a pdf file
    workbook.Save("CellPaddingAndLabels.pdf");

    Numbers Fit Mode

    In MS Excel, when a number or date does not fit in the available cell width, it masks the cell value and displays "####" in the cell. To overcome this, DsExcel provides NumbersFitMode enumeration so that you can choose to either mask or show entire number or date value when cell is not wide enough to accommodate the entire value. The enumeration can be accessed through IWorkbookView.NumbersFitMode property and can have "Mask" or "Overflow" values. To avoid displaying "####", you can set the property to "Overflow" so that overflowing value occupies the space of blank neighboring cell. No overflow happens and only partial value is displayed in case the cell itself or the neighboring cell is a merged cell or has value in it.

    NumbersFitMode = Mask NumbersFitMode = Overflow
    C#
    Copy Code
    // Set numbersFitMode is overflow.
    workbook.BookView.NumbersFitMode = GrapeCity.Documents.Excel.NumbersFitMode.Overflow;

    This overflow behavior and direction vary according to the horizontal alignment and orientation of the cell values. The following table displays a value longer than the available width and its overflow behavior with different horizontal alignment and orientation.

    Horizontal Alignment/Orientation Overflow Behavior
    General or right alignment
    Left alignment
    Center Alignment
    Orientation greater than zero
    Orientation less than zero
    Note: As MS Excel does not support the NumbersFitMode, IWorkbookView.NumbersFitMode = Overflow is not effective on exporting the worksheet to MS Excel.

    Filter First Row as Data

    DsExcel enables you to add a filter above the first row containing data using an overload of AutoFilter method of IRange interface, comprising isFirstRowData boolean type parameter, so that you can filter the first row.

    Refer to the following example code to add a filter to the first row containing data using isFirstRowData parameter of AutoFilter method:

    C#
    Copy Code
    // Create a new workbook.
    var workbook = new Workbook();
    
    IWorksheet worksheet = workbook.Worksheets[0];
    
    // Add data to the range.
    object[,] data = new object[,]{
    {"Luigi", "New York", new DateTime(1998, 4, 7), "Blue", 67, 165},
    {"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;
    
    // Set column width.
    worksheet.Range["A:F"].ColumnWidth = 15;
                
    // Apply filter to first row.
    worksheet.Range["A1:F7"].AutoFilter(true, 4, "<72");
    
    // Create a file stream to export ssjson file.
    FileStream outputStream = new FileStream("HeadersAsData.ssjson", FileMode.Create);
    
    // Export the ssjson file.
    workbook.ToJson(outputStream);
    
    // Close the stream.
    outputStream.Close();
    Note: isFirstRowData parameter will work when exporting the workbook to .sjs and ssjson files, as Excel does not support this feature.

    Background Image

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

    DsExcel also provides BackgroundPictures property in IWorksheet interface to add background pictures in DsExcel. For more information, refer Support Sheet Background Image.

    The following example code sets background image in DsExcel worksheet.

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    IWorksheet worksheet = workbook.Worksheets[0];
    
    FileStream stream = File.Open(@"Logo.png", FileMode.Open, FileAccess.Read);
    
    //Add background picture
    IBackgroundPicture picture = worksheet.BackgroundPictures.AddPictureInPixel(stream, ImageType.PNG, 10, 10, 500, 370);
    //Set image layout
    picture.BackgroundImageLayout = ImageLayout.Zoom;
    
    //Set options
    workbook.ActiveSheet.PageSetup.PrintGridlines = true;
    
    //save to a pdf file
    workbook.Save("backgroundimage.pdf");

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

    C#
    Copy Code
    Workbook workbook = new Workbook();
    
    string ssjson = string.Empty;
    try
    {
        var jsonFile = @"D:\bgimage.ssjson";
    
        using (StreamReader sr = System.IO.File.OpenText(jsonFile))
        {
            ssjson = sr.ReadToEnd();
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }
    
    //Importing from ssjson
    workbook.FromJson(ssjson);
    
    //Set options
    workbook.ActiveSheet.PageSetup.PrintGridlines = true;
    
    //Exporting to PDF
    workbook.Save("bgimage.pdf");

    Limitations

    Background Color

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

    DsExcel also provides BackColor and GrayAreaBackColor properties in IWorkbookView interface to set background color in DsExcel. 

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    //Set background color
    workbook.BookView.BackColor = Color.LightSkyBlue;
    workbook.BookView.GrayAreaBackColor = Color.Gray;
    
    //Set value to a cell
    IWorksheet worksheet = workbook.ActiveSheet;
    worksheet.Range["H20"].Value = "The text";
    
    //Set page options
    worksheet.PageSetup.PrintGridlines = true;
    worksheet.PageSetup.PrintHeadings = true;
    
    //save to a pdf file
    workbook.Save("backgroundcolor.pdf");

    Limitation

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

    Row and Column Count

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

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

    C#
    Copy Code
    IWorkbook workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    worksheet.Range["A1"].Value = 1;
    worksheet.Range["A11"].Value = 2;
    
    // Modify the row count and column count of the worksheet.
    worksheet.RowCount = 10;
    worksheet.ColumnCount = 10;
    
    // Save to a json file.
    // Open this json file with spreadjs, you will find that the row count is 10, and the column count is 10.
    FileStream file = new FileStream("RowColumnCount.json", FileMode.Create);
    workbook.ToJson(file, new SerializationOptions { IgnoreRangeOutOfRowColumnCount = true});

    Limitation

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

    Pixel Based Column-Width

    DsExcel allows you to render column width based on pixels instead of characters using PixelBasedColumnWidth property of WorkbookOptions class when exporting an SSJSON file as a PDF or image.

    Refer to the following example code to render column width based on pixels when exporting as a PDF or image:

    C#
    Copy Code
    // Initialize WorkbookOptions.
    WorkbookOptions workbookOptions = new WorkbookOptions();
    
    // Enable pixel-based column width for the workbook.
    workbookOptions.PixelBasedColumnWidth = true;
    var workbook = new Workbook(workbookOptions);
    
    // Open SSJSON file.
    workbook.Open("Event budget.ssjson");
    IWorksheet worksheet = workbook.Worksheets[0];
    
    // Save to a PDF and PNG file.
    workbook.Save("SavePDFWithPixelBasedColumnWidth.pdf");
    worksheet.ToImage("SavePDFWithPixelBasedColumnWidth.png");

    Set Tab Strip Position

    DsExcel allows you to set various properties of Tab Strip like its position, width, display new tab button, editing of worksheet name etc. while performing JSON I/O. The IWorkbook interface provides properties like TabNavigationVisible, NewTabVisible, AllowSheetReorder, TabStripWidth, TabStripPosition etc.

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new Workbook();
    workbook.Worksheets.Add();
    
    workbook.BookView.AllowSheetReorder = false;
    workbook.BookView.TabEditable = false;
    workbook.BookView.TabNavigationVisible = false;
    workbook.BookView.TabStripPosition = SpreadJSTabStripPosition.Left;
    workbook.BookView.TabStripWidth = 150;
    workbook.BookView.NewTabVisible = false;
    using var file = new FileStream("sheettabposition.json", FileMode.Create);
    workbook.ToJson(file);

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

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

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

    C#
    Copy Code
    //create a new workbook
    var workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    
    CheckBoxListCellType cellType = new CheckBoxListCellType
    {
        Direction = CellTypeDirection.Horizontal,
        TextAlign = CellTypeTextAlign.Right,
        IsFlowLayout = false,
        MaxColumnCount = 2,
        MaxRowCount = 1,
        HorizontalSpacing = 20,
        VerticalSpacing = 5,
        BoxSize = 40,
        AutoBoxSize = true
    };
    cellType.Items.Add(new SelectFieldItem("sample1", "1"));
    cellType.Items.Add(new SelectFieldItem("sample2", "2"));
    cellType.Items.Add(new SelectFieldItem("sample3", "3"));
    cellType.Items.Add(new SelectFieldItem("sample4", "4"));
    cellType.Items.Add(new SelectFieldItem("sample5", "5"));
    worksheet.Range["A1:C3"].ColumnWidth = 25;
    worksheet.Range["A1:C3"].CellType = cellType;
    worksheet.Range["A1:C3"].Value = new object[,]
    {
    {new object[]{"1", "3", "5" } }
    };
    using var file = new FileStream("checkboxlistsize.json", FileMode.Create);
    workbook.ToJson(file);
    }

    Get Picture URL

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

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

    C#
    Copy Code
    static void Main(string[] args)
    {
        Workbook workbook = new Workbook();
        workbook.Open("Picture.json");
        var pic = workbook.ActiveSheet.Shapes[0];
        //Get URL of picture from json file
        string url = pic.PictureFormat.Url;
        byte[] picByte = GetPictureFromUrl(url);
        //Set byte array of picture
        pic.PictureFormat.Fill = picByte;
        //Save to PDF and Excel 
        workbook.Save("PicturePDF.pdf");
        workbook.Save("PictureExcel.xlsx");
    }
    
    private static byte[] GetPictureFromUrl(string url)
    {
        WebClient wc = new WebClient();
        byte[] imageBytes = wc.DownloadData(url);
        return imageBytes;
    }