Convert Range to JSON

Professionalize.Discourse · April 28, 2025, 9:47am

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 Workbook class.
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 ExportRangeToJsonOptions to set any specific options you might need for the export.
Convert to JSON: Call JsonUtility.ExportRangeToJson with 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

John.He · April 28, 2025, 9:58am

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

Adhirath · April 28, 2025, 11:48am

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.

simon.zhao · April 28, 2025, 12:12pm

@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);

Adhirath · May 2, 2025, 2:38pm

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

johnson.shi · May 2, 2025, 3:25pm

@Adhirath

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.

simon.zhao · May 5, 2025, 9:46am

@Adhirath
Are these APIs customized by you or not?

Adhirath · May 6, 2025, 4:05am

I didn’t get you,I require the above properties to set and get a range in excel.

John.He · May 6, 2025, 5:40am

@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?

John.He · May 6, 2025, 6:33am

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

Adhirath · May 6, 2025, 7:49am

okay, let me try it out

John.He · May 6, 2025, 7:54am

@Adhirath
Please take your time to try the suggested solutions. Hopefully, your issue will be sorted out. Please let us know your feedback.

Adhirath · May 8, 2025, 12:27pm

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

amjad.sahi · May 8, 2025, 12:53pm

@Adhirath,

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.

simon.zhao · May 8, 2025, 1:15pm

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

Adhirath · May 12, 2025, 6:33am

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.

John.He · May 12, 2025, 7:04am

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

Adhirath · May 12, 2025, 7:40am

nevermind the range was corrupted. Will I get accurate values if I loop through the cells to get cell properties?

John.He · May 12, 2025, 7:44am

@Adhirath
If you traverse all the cells yourself, you can obtain all the public information of the cells. Please refer to the following document.

Adhirath · May 12, 2025, 1:07pm

Hi guys need your help here, I wrote code to convert Aspose to Json as accurately as I could there are still a few properties missing but I wanted someone to have a look and correct the code if they find any issues. Also there are a few properties that I havecommented out which are required but I cannot find those in Aspose. I am attaching the code in a file, I request you to do the same when responding. Thanks!
file-
AsposeTOExcelConverter.zip (3.2 KB)