Convert Range to JSON

Aspose.Cells Product Family
,
17

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.

18

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

19

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

20

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

21

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)

22

@Adhirath
We will check them soon.

23

@Adhirath
Please refer to the attached sample code. AsposeToExcelJsHelper.zip (3.7 KB)

Also, please check the following four attributes. Which setting in the Excel interface did you mean to correspond to? If you can provide a screenshot and highlight the location, it will be very helpful for us to locate the attribute parameters. We will check it soon.

//FillTintAndShade = null,
//AutoIndent = false, 
//UseStandardHeight = true, 
//UseStandardWidth = true,
24

Hi guys I tried it out. first of all really appreciate the help. secondly, I ran into an issue with number formats. So excel js api uses the following formats and more(custom) , the question is how can I get the same using aspose-
“numberFormat”: [
[
“General”
],
[
“0.00”
],
[
“$ #,##0.00”
],
[
“($* #,##0.00);($* (#,##0.00);($* "-"??);(@_)”
],
[
“m/d/yyyy”
],
[
“[$-x-sysdate]dddd, mmmm dd, yyyy”
],
[
“[$-x-systime]h:mm:ss AM/PM”
],
[
“0.00%”
],
[
“# ?/?”
],
[
“0.00E+00”
],
[
“@”
],
[
“#,##0.00”
]
],
the sequence is the following-
general,
number,
currency,
accounting,
short date,
long date,
time,
percentage,
fraction,
scientific,
Text,
Custom(I have only used one custom format there are quite a few available, let me know if more info is required)

25

@Adhirath
Aspose.Cells component offers some built-in number formats to configure the display formats of the numbers and dates. These built-in number formats can be setted and getted by using the Number property of the Style object. All built-in number formats are given unique numeric values. Please refer to the following document.

Of course, if you are not satisfied with the built-in number format, you can also set and get a custom number format using Style.Custom property. Please refer to the following document.

Regarding the several numerical formats you have listed, you can also refer to the following relevant documents.

1 Like
26

is there a way for me to get the format string(3rd column) mentioned here for a given cell-
Number Settings|Documentation.

27

@Adhirath
You need to traverse the built-in number format to obtain the number format string. Please refer to the following example code.

private string GetBuiltinNumberFormatsString(Style style)
{
    int number = style.Number;
    switch (number)
    {
        case 0:
            {
                if (style.Custom == "")
                {
                    return "General";
                }
                else
                {
                    return style.Custom;
                }
            }
        case 1: return "0"; 
        case 2: return "0.00";
        case 3: return "#,##0";
        case 4: return "#,##0.00";
        case 5: return "$#,##0;-$#,##0";
        case 6: return "$#,##0;[Red]$-#,##0";
        case 7: return "$#,##0.00;-$#,##0.00";
        case 8: return "$#,##0.00;[Red]$-#,##0.00";
        case 9: return "0%";
        case 10: return "0.00%";
        case 11: return "0.00E+00";
        case 12: return "# ?/?";
        case 13: return "# /";
        case 14: return "m/d/yyyy";
        case 15: return "d-mmm-yy";
        case 16: return "d-mmm";
        case 17: return "mmm-yy";
        case 18: return "h:mm AM/PM";
        case 19: return "h:mm:ss AM/PM";
        case 20: return "h:mm";
        case 21: return "h:mm:ss";
        case 22: return "m/d/yy h:mm";
        case 37: return "#,##0;-#,##0";
        case 38: return "#,##0;[Red]-#,##0";
        case 39: return "#,##0.00;-#,##0.00";
        case 40: return "#,##0.00;[Red]-#,##0.00";
        case 41: return "_ * #,##0_ ;_ * “_ ;_ @_";
        case 42: return "_ $* #,##0_ ;_ $* “_ ;_ @_";
        case 43: return "_ * #,##0.00_ ;_ * “??_ ;_ @_";
        case 44: return "_ $* #,##0.00_ ;_ $* “??_ ;_ @_";
        case 45: return "mm:ss";
        case 46: return "h :mm:ss";
        case 47: return "mm:ss.0";
        case 48: return "##0.0E+00";
        case 49: return "@";
        
        default: return style.Custom;
    }
}

Hope helps a bit.

28

got you, thanks!

29

AutoIndent - Specifies if text is automatically indented when text alignment is set to equal distribution.

FillTintAndShade - Specifies a double that lightens or darkens a color for the range fill. The value is between -1 (darkest) and 1 (brightest), with 0 for the original color. A null value indicates that the range doesn’t have uniform tintAndShade settings.

UseStandardHeight - Determines if the row height of the Range object equals the standard height of the sheet. Returns true if the row height of the Range object equals the standard height of the sheet. Returns null if the range contains more than one row and the rows aren’t all the same height. Returns false otherwise. Note: This property is only intended to be set to true . Setting it to false has no effect.

UseStandardWidth - Specifies if the column width of the Range object equals the standard width of the sheet. Returns true if the column width of the Range object equals the standard width of the sheet. Returns null if the range contains more than one column and the columns aren’t all the same height. Returns false otherwise. Note: This property is only intended to be set to true . Setting it to false has no effect.

30

@Adhirath
Thank you for your feedback and detailed explanation. We will further investigate your issue and notify you immediately once there are any updates.

1 Like
32

Hi guys I was able to make progress with the above, am able to get number format and borders with 100% accuracy. Need help with table properties. I am attaching my current code it has all the properties I require and definitions for all the ones that I could not find. Again, really appreciate the help and thanks in advance-
AsposeToExcelJsHelper.zip (4.8 KB)

33

@Adhirath,

Thanks for the sample code. We have logged it with your existing ticket “CELLSNET-58332” into our database. We will evaluate your requirements thoroughly and assist you on your desired table properties. Once we have an update on it, we will let you know here.

1 Like
34

@Adhirath
Please refer to the attached sample code. AsposeToExcelJsHelper.zip (5.8 KB)

Also, please check the following three attributes. Which setting in the Excel interface did you mean to correspond to? If you can provide a screenshot and highlight the location, it will be very helpful for us to locate the attribute parameters. We will check it soon.

Console.WriteLine($"Key:");
Console.WriteLine($"subField:");
Console.WriteLine($"SubFieldId:");
35

Thanks you guys!
I needed one more help. I created a table then converted it back to a range, now when I am trying to use my cellstyle code it is not giving me any cellstyles even though the table has it. I am attaching the table below.
refrence excel.zip (10.1 KB)
Current Code-
AsposeToExcelJsHelper.zip (34.4 KB)

36

Nevermind it was an issue at my end

37

@Adhirath,

Thanks for the update. It seems you are have sorted it out now. Please feel free to write us back if you have further queries or comments.

1 Like