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.
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.
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.
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.
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.
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 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.
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.
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.
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.
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:
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(); } } |
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"); |
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 |
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(); |
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
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.
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.
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"); |
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); |
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); } |
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; } |