Hi, I want to convert a range I have in excel to a JSON and I want to capture the range as is along with all the formatting. The idea is that I want to use these JSON values to later set the range properties in another workbook so I can create that exact range. Let me know what can be done.
To convert a range from an Excel workbook to JSON while capturing all the formatting, you can utilize the ExportRangeToJsonOptions class provided by Aspose.Cells. Below is a sample code snippet that demonstrates how to achieve this:
using Aspose.Cells;
using Aspose.Cells.Utility;
using System;
namespace ExcelToJsonExample
{
public class ConvertRangeToJson
{
public static void Run()
{
// Load the workbook
Workbook workbook = new Workbook("path_to_your_excel_file.xlsx");
Cell lastCell = workbook.Worksheets[0].Cells.LastCell;
// Create a range that you want to convert to JSON
Range range = workbook.Worksheets[0].Cells.CreateRange(0, 0, lastCell.Row + 1, lastCell.Column + 1);
// Set options for exporting the range to JSON
ExportRangeToJsonOptions options = new ExportRangeToJsonOptions();
// Convert the range to JSON
string jsonData = JsonUtility.ExportRangeToJson(range, options);
// Output the JSON data
Console.WriteLine(jsonData);
}
}
}
Explanation:
- Load the Workbook: You need to load your Excel file using the
Workbookclass. - Create a Range: Define the range you want to convert by specifying the starting row and column, as well as the number of rows and columns.
- Export Options: Use
ExportRangeToJsonOptionsto set any specific options you might need for the export. - Convert to JSON: Call
JsonUtility.ExportRangeToJsonwith the defined range and options to get the JSON representation. - Output: Finally, print or use the JSON data as needed.
This approach will help you capture the range along with its formatting, which you can later use to set properties in another workbook. Make sure to adjust the path to your Excel file accordingly.
For more details, you can refer to the ConvertCsvToJson example provided in the Aspose.Cells documentation.
Sources:
[1]: ConvertCsvToJson.cs
@Adhirath
Please refer to the following example code.
// Load the workbook
Workbook workbook = new Workbook("sample.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
// Create a range that you want to convert to JSON
Range range = workbook.Worksheets[0].Cells.CreateRange(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
// Set options for exporting the range to JSON
JsonSaveOptions jsonSaveOptions = new JsonSaveOptions();
// Convert the range to JSON
string jsonData = JsonUtility.ExportRangeToJson(range, jsonSaveOptions);
// Output the JSON data
Console.WriteLine(jsonData);
In addition, JSON data only stores the text of cells and does not store formatting of cells. If you want to save the range format while copying data, you can use the range copying feature to meet the requirements. Please refer to the following document.
Hi John, I have come across the Copy method and am using that in my code already. But for the current scenario I need to capture all properties of the range as json and set the properties using the same json. I need the properties for setting the range using excel js api.
@Adhirath
Can you list your mentioned all properties of the range ?
The following codes exports the settings of the cells in the range:
JsonSaveOptions options = new JsonSaveOptions();
options.ToExcelStruct = true;
range.ToJson(options);
Hi, sorry for the delay in response. Took some time to collect all properties. Here is the list-
Range:
address
RowCount
ColumnCount
RowIndex
ColumnIndex
values
numberformat
formulas
mergedAreas
Table:
style
showFilterButton
showHeaders
showTotals
showBandedRows
showBandedColumns
highlightFirstColumn
highlightLastColumn
sort:
sort.matchcase
sort.fields
sort.fields.ascending
sort.fields.color
sort.fields.dataOption
sort.fields.Icon
sort.fields.Key
sort.fields.sortOn
sort.fields.subField
columns
column.filter.criteria
Cells-
cell.format.font.color
cell.format.fill.tintAndShade
cell.format.fill.patternTintAndShade
cell.format.fill.patternColor
cell.format.fill.pattern
cell.format.fill.color
cell.format.font.size
cell.format.font.bold
cell.format.font.italic
cell.format.font.underline
cell.format.font.strikethrough
cell.format.font.name
cell.format.horizontalAlignment
cell.format.verticalAlignment
cell.format.autoIndent
cell.format.indentLevel
cell.format.textOrientation
cell.format.wrapText
cell.format.shrinkToFit
cell.format.useStandardHeight
following border properties for(top,bottom,left,right,vertical,horizontal,both diagonals):
cell.format.borders.color
cell.format.borders.style
cell.format.borders.tintAndShade
cell.format.borders.weight
conditional formatting-
conditionalFormats
Iconset:
reverseIconOrder
showIconOnly
style
CellValue:
format.cellValue.rule.formula1
format.cellValue.rule.operator
format.cellValue.rule.formula2
format.cellValue.format.fill.color
format.cellValue.format.font.color
format.cellValue.format.font.bold
format.cellValue.format.font.italic
format.cellValue.format.font.underline
format.cellValue.format.font.strikethrough
format.cellValue.format.borders
foreach border index(EdgeTop,EdgeBottom,EdgeLeft,EdgeRight)
format.cellValue.format.border.(borderIndex)
format.cellValue.format.border.(borderIndex).color
format.cellValue.format.border.(borderIndex).style
ColorScale:
format.colorScale.threeColorScale
format.colorScale.criteria.minimum.formula
format.colorScale.criteria.minimum.type
format.colorScale.criteria.minimum.color
format.colorScale.criteria.midpoint.formula
format.colorScale.criteria.midpoint.type
format.colorScale.criteria.midpoint.color
format.colorScale.criteria.maximum.formula
format.colorScale.criteria.maximum.type
format.colorScale.criteria.maximum.color
DataBar:
format.dataBar.lowerBoundRule.formula
format.dataBar.lowerBoundRule.type
format.dataBar.upperBoundRule.formula
format.dataBar.upperBoundRule.type
format.dataBar.negativeFormat.borderColor
format.dataBar.negativeFormat.fillColor
format.dataBar.negativeFormat.matchPositibveBorderColor
format.dataBar.negativeFormat.matchPositibveFillColor
format.dataBar.axisColor
format.dataBar.axisFormat
format.dataBar.barDirection
format.dataBar.showDataBarOnly
TopBottom:
format.topBottom.rule
format.topBottom.format.borders
format.topBottom.format.borders.getItemAt(borderIndex).color
format.topBottom.format.borders.getItemAt(borderIndex).style
format.topBottom.format.fill.color
format.topBottom.format.font.color
format.topBottom.format.font.bold
format.topBottom.format.font.italic
format.topBottom.format.font.underline
format.topBottom.format.font.strikethrough
PresetCriteria:
format.preset.rule
format.preset.format.fill.color
format.preset.format.font.color
format.preset.format.font.bold
format.preset.format.font.italic
format.preset.format.font.underline
format.preset.format.font.strikethrough
format.preset.format.borders
format.preset.format.borders.getItemAt(borderIndex).color
format.preset.format.borders.getItemAt(borderIndex).style
textComparison:
format.textComparison.rule
format.textComparison.format.fill.color
format.textComparison.format.font.color
format.textComparison.format.font.bold
format.textComparison.format.font.italic
format.textComparison.format.font.underline
format.textComparison.format.font.strikethrough
format.textComparison.format.borders
format.textComparison.format.borders.getItemAt(borderIndex).color
format.textComparison.format.borders.getItemAt(borderIndex).style
Thank you for the list of properties. We need to investigate this requirement thoroughly and then give feedback. We have opened the following new ticket(s) in our internal issue tracking system to investigate this requirement and will deliver their fixes(if possible) according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58332
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
I didn’t get you,I require the above properties to set and get a range in excel.
@Adhirath
Thank you for your feedback. Are the attributes listed earlier customized by you? Do you want to store styles in JSON when exporting to JSON format, so that when importing JSON data into Excel, the original format can still be maintained?
@Adhirath
By creating a sample file and setting the style and font color, and then testing it using the following sample code, we found that the JSON data already contains formatting and font information. Please refer to the attachment. sample.zip (6.5 KB)
Workbook book = new Workbook(filePath + "sample.xlsx");
JsonSaveOptions options = new JsonSaveOptions();
options.ToExcelStruct = true;
Range range = book.Worksheets[0].Cells.CreateRange("A1:D10");
string json = range.ToJson(options);
Console.WriteLine(json);
The output:
[
{
"rowOffset" : 0,
"cell" : [{
"name" : "A1",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFF0000"
}
},
"type" : "string",
"value" : "A1"
},
{
"name" : "B1",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFF0000"
}
},
"type" : "string",
"value" : "B1"
},
{
"name" : "C1",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "C1"
},
{
"name" : "D1",
"style" : {
"font" : {
"name" : "Calibri",
"bold" : true,
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "D1"
}]
},
{
"rowOffset" : 1,
"cell" : [{
"name" : "A2",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFF0000"
}
},
"type" : "string",
"value" : "A2"
},
{
"name" : "B2",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFF0000"
}
},
"type" : "string",
"value" : "B2"
},
{
"name" : "C2",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "C2"
},
{
"name" : "D2",
"style" : {
"font" : {
"name" : "Calibri",
"bold" : true,
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "D2"
}]
},
{
"rowOffset" : 2,
"cell" : [{
"name" : "A3",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "A3"
},
{
"name" : "B3",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "B3"
},
{
"name" : "C3",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "C3"
},
{
"name" : "D3",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "D3"
}]
},
{
"rowOffset" : 3,
"cell" : [{
"name" : "A4",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "A4"
},
{
"name" : "B4",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "B4"
},
{
"name" : "C4",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "C4"
},
{
"name" : "D4",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "D4"
}]
},
{
"rowOffset" : 4,
"cell" : [{
"name" : "A5",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "A5"
},
{
"name" : "B5",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "B5"
},
{
"name" : "C5",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "C5"
},
{
"name" : "D5",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "D5"
}]
},
{
"rowOffset" : 5,
"cell" : [{
"name" : "A6",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "A6"
},
{
"name" : "B6",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "B6"
},
{
"name" : "C6",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "C6"
},
{
"name" : "D6",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "D6"
}]
},
{
"rowOffset" : 6,
"cell" : [{
"name" : "A7",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "A7"
},
{
"name" : "B7",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "B7"
},
{
"name" : "C7",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "C7"
},
{
"name" : "D7",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "D7"
}]
},
{
"rowOffset" : 7,
"cell" : [{
"name" : "A8",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "A8"
},
{
"name" : "B8",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "B8"
},
{
"name" : "C8",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FF00B050"
}
},
"type" : "string",
"value" : "C8"
},
{
"name" : "D8",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
}
},
"type" : "string",
"value" : "D8"
}]
},
{
"rowOffset" : 8,
"cell" : [{
"name" : "A9",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "A9"
},
{
"name" : "B9",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "B9"
},
{
"name" : "C9",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "C9"
},
{
"name" : "D9",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "D9"
}]
},
{
"rowOffset" : 9,
"cell" : [{
"name" : "A10",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "A10"
},
{
"name" : "B10",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "B10"
},
{
"name" : "C10",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "C10"
},
{
"name" : "D10",
"style" : {
"font" : {
"name" : "Calibri",
"size" : 11,
"color" : "#FF4472C4",
"family" : 2,
"scheme" : "minor"
},
"fill" : {
"patternStyle" : "solid",
"backgroundColor" : "#FFFFFF00"
}
},
"type" : "string",
"value" : "D10"
}]
}]
If you still have questions, please provide your sample file and detailed description of the properties that need to be exported. If you can take a screenshot and highlight the missing attributes, it will be very helpful for us to locate and solve the issue. We will check it soon.
okay, let me try it out
@Adhirath
Please take your time to try the suggested solutions. Hopefully, your issue will be sorted out. Please let us know your feedback.
1 Like
Hi I tried it out, and only getting cell values and missing the following-
conditional formatting-
conditionalFormats
Iconset:
reverseIconOrder
showIconOnly
style
CellValue:
format.cellValue.rule.formula1
format.cellValue.rule.operator
format.cellValue.rule.formula2
format.cellValue.format.fill.color
format.cellValue.format.font.color
format.cellValue.format.font.bold
format.cellValue.format.font.italic
format.cellValue.format.font.underline
format.cellValue.format.font.strikethrough
format.cellValue.format.borders
foreach border index(EdgeTop,EdgeBottom,EdgeLeft,EdgeRight)
format.cellValue.format.border.(borderIndex)
format.cellValue.format.border.(borderIndex).color
format.cellValue.format.border.(borderIndex).style
ColorScale:
format.colorScale.threeColorScale
format.colorScale.criteria.minimum.formula
format.colorScale.criteria.minimum.type
format.colorScale.criteria.minimum.color
format.colorScale.criteria.midpoint.formula
format.colorScale.criteria.midpoint.type
format.colorScale.criteria.midpoint.color
format.colorScale.criteria.maximum.formula
format.colorScale.criteria.maximum.type
format.colorScale.criteria.maximum.color
DataBar:
format.dataBar.lowerBoundRule.formula
format.dataBar.lowerBoundRule.type
format.dataBar.upperBoundRule.formula
format.dataBar.upperBoundRule.type
format.dataBar.negativeFormat.borderColor
format.dataBar.negativeFormat.fillColor
format.dataBar.negativeFormat.matchPositibveBorderColor
format.dataBar.negativeFormat.matchPositibveFillColor
format.dataBar.axisColor
format.dataBar.axisFormat
format.dataBar.barDirection
format.dataBar.showDataBarOnly
TopBottom:
format.topBottom.rule
format.topBottom.format.borders
format.topBottom.format.borders.getItemAt(borderIndex).color
format.topBottom.format.borders.getItemAt(borderIndex).style
format.topBottom.format.fill.color
format.topBottom.format.font.color
format.topBottom.format.font.bold
format.topBottom.format.font.italic
format.topBottom.format.font.underline
format.topBottom.format.font.strikethrough
PresetCriteria:
format.preset.rule
format.preset.format.fill.color
format.preset.format.font.color
format.preset.format.font.bold
format.preset.format.font.italic
format.preset.format.font.underline
format.preset.format.font.strikethrough
format.preset.format.borders
format.preset.format.borders.getItemAt(borderIndex).color
format.preset.format.borders.getItemAt(borderIndex).style
textComparison:
format.textComparison.rule
format.textComparison.format.fill.color
format.textComparison.format.font.color
format.textComparison.format.font.bold
format.textComparison.format.font.italic
format.textComparison.format.font.underline
format.textComparison.format.font.strikethrough
format.textComparison.format.borders
format.textComparison.format.borders.getItemAt(borderIndex).color
format.textComparison.format.borders.getItemAt(borderIndex).style
Table:
style
showFilterButton
showHeaders
showTotals
showBandedRows
showBandedColumns
highlightFirstColumn
highlightLastColumn
sort:
sort.matchcase
sort.fields
sort.fields.ascending
sort.fields.color
sort.fields.dataOption
sort.fields.Icon
sort.fields.Key
sort.fields.sortOn
sort.fields.subField
columns
column.filter.criteria
Range:
address
RowCount
ColumnCount
mergedAreas
Thanks for your feedback and providing further details.
We will evaluate you mentioned missing attributes in Range to JSON export and get back to you.
@Adhirath
1,
We will only add address property because it will include RowCount and ColumnCount.
2, we will check how to export conditional formats and table setting to json.
Hi guys I have a request can we have a json save option which says ToExcelJsApiStruct which can convert the range into a json acceptable by excel js api. here are all the properties it requires -
address
RowCount
ColumnCount
RowIndex
ColumnIndex
values
numberformat
formulas
mergedAreas
Table:
style
showFilterButton
showHeaders
showTotals
showBandedRows
showBandedColumns
highlightFirstColumn
highlightLastColumn
sort:
sort.matchcase
sort.fields
sort.fields.ascending
sort.fields.color
sort.fields.dataOption
sort.fields.Icon
sort.fields.Key
sort.fields.sortOn
sort.fields.subField
columns
column.filter.criteria
Cells-
cell.format.font.color
cell.format.fill.tintAndShade
cell.format.fill.patternTintAndShade
cell.format.fill.patternColor
cell.format.fill.pattern
cell.format.fill.color
cell.format.font.size
cell.format.font.bold
cell.format.font.italic
cell.format.font.underline
cell.format.font.strikethrough
cell.format.font.name
cell.format.horizontalAlignment
cell.format.verticalAlignment
cell.format.autoIndent
cell.format.indentLevel
cell.format.textOrientation
cell.format.wrapText
cell.format.shrinkToFit
cell.format.useStandardHeight
following border properties for(top,bottom,left,right,vertical,horizontal,both diagonals):
cell.format.borders.color
cell.format.borders.style
cell.format.borders.tintAndShade
cell.format.borders.weight
conditional formatting-
conditionalFormats
Iconset:
reverseIconOrder
showIconOnly
style
CellValue:
format.cellValue.rule.formula1
format.cellValue.rule.operator
format.cellValue.rule.formula2
format.cellValue.format.fill.color
format.cellValue.format.font.color
format.cellValue.format.font.bold
format.cellValue.format.font.italic
format.cellValue.format.font.underline
format.cellValue.format.font.strikethrough
format.cellValue.format.borders
foreach border index(EdgeTop,EdgeBottom,EdgeLeft,EdgeRight)
format.cellValue.format.border.(borderIndex)
format.cellValue.format.border.(borderIndex).color
format.cellValue.format.border.(borderIndex).style
ColorScale:
format.colorScale.threeColorScale
format.colorScale.criteria.minimum.formula
format.colorScale.criteria.minimum.type
format.colorScale.criteria.minimum.color
format.colorScale.criteria.midpoint.formula
format.colorScale.criteria.midpoint.type
format.colorScale.criteria.midpoint.color
format.colorScale.criteria.maximum.formula
format.colorScale.criteria.maximum.type
format.colorScale.criteria.maximum.color
DataBar:
format.dataBar.lowerBoundRule.formula
format.dataBar.lowerBoundRule.type
format.dataBar.upperBoundRule.formula
format.dataBar.upperBoundRule.type
format.dataBar.negativeFormat.borderColor
format.dataBar.negativeFormat.fillColor
format.dataBar.negativeFormat.matchPositibveBorderColor
format.dataBar.negativeFormat.matchPositibveFillColor
format.dataBar.axisColor
format.dataBar.axisFormat
format.dataBar.barDirection
format.dataBar.showDataBarOnly
TopBottom:
format.topBottom.rule
format.topBottom.format.borders
format.topBottom.format.borders.getItemAt(borderIndex).color
format.topBottom.format.borders.getItemAt(borderIndex).style
format.topBottom.format.fill.color
format.topBottom.format.font.color
format.topBottom.format.font.bold
format.topBottom.format.font.italic
format.topBottom.format.font.underline
format.topBottom.format.font.strikethrough
PresetCriteria:
format.preset.rule
format.preset.format.fill.color
format.preset.format.font.color
format.preset.format.font.bold
format.preset.format.font.italic
format.preset.format.font.underline
format.preset.format.font.strikethrough
format.preset.format.borders
format.preset.format.borders.getItemAt(borderIndex).color
format.preset.format.borders.getItemAt(borderIndex).style
textComparison:
format.textComparison.rule
format.textComparison.format.fill.color
format.textComparison.format.font.color
format.textComparison.format.font.bold
format.textComparison.format.font.italic
format.textComparison.format.font.underline
format.textComparison.format.font.strikethrough
format.textComparison.format.borders
format.textComparison.format.borders.getItemAt(borderIndex).color
format.textComparison.format.borders.getItemAt(borderIndex).style
Also there are issues when i do ToExcelStruct, sometimes i get an error saying- Invalid Horizontal AlignmentType value.
@Adhirath
Thank you for your feedback. We will notify you promptly of any updates to the exported properties. Additionally, regarding the error you encountered, we need your sample file to locate the issue. Would you like to provide your sample files and test code? We will check it soon.
nevermind the range was corrupted. Will I get accurate values if I loop through the cells to get cell properties?
@Adhirath
If you traverse all the cells yourself, you can obtain all the public information of the cells. Please refer to the following document.