Aspose.Cells get wrong json content when convert xlsx to json

Aspose.Cells Product Family
1

Description

Aspose get wrong json content result when save a xlsx as json format.

Env

  • Windows 10 .NET 4.7
  • Aspose.Cells for .NET 22.12

Q1

Json’s structure is not consistent when a xlsx file has only one sheet and the other xlsx file has two sheets.

  • Only one sheet output (Array):
[{....},{....},{....}]
  • Two or more sheets output (Object):
{"Sheet1":[{...},{...}], "Sheet2":[{...},{...}]}
  • Both of them expected result should be (keep the json structure is consistent):
{"Sheet1":[{...},{...}]}
{"Sheet1":[{...},{...}], "Sheet2":[{...},{...}]}

Code:

    for (var i = 1; i <= 2; i++)
    {
        var doc = new Aspose.Cells.Workbook(@"C:\Users\XCL\Desktop\test\" + i + ".xlsx");
        var ops = new Aspose.Cells.JsonSaveOptions();
        ops.ExportNestedStructure = true;
        doc.Save(@"C:\Users\XCL\Desktop\test\" + i + ".json", ops);
    }

Test Files:
test.zip (14.6 KB)

Q2

Json’s value is missing when a sheet is empty.

Code:

    var doc = new Aspose.Cells.Workbook(@"C:\Users\XCL\Desktop\test\1.xlsx");
    var ops = new Aspose.Cells.JsonSaveOptions();
    ops.ExportNestedStructure = true;
    doc.Save(@"C:\Users\XCL\Desktop\test\1.json", ops);

Received Json Result:

{
"Sheet1":[
 {
  "ID-1": 1
 },
 {
  "ID-1": 2
 },
 {
  "ID-1": 3
 },
 {
  "ID-1": 4
 },
 {
  "ID-1": 5
 },
 {
  "ID-1": 6
 },
 {
  "ID-1": 7
 },
 {
  "ID-1": 8
 },
 {
  "ID-1": 9
 },
 {
  "ID-1": 10
 }
],
"Sheet2":
}

Expected Result:

{
"Sheet1":[
 {
  "ID-1": 1
 },
 {
  "ID-1": 2
 },
 {
  "ID-1": 3
 },
 {
  "ID-1": 4
 },
 {
  "ID-1": 5
 },
 {
  "ID-1": 6
 },
 {
  "ID-1": 7
 },
 {
  "ID-1": 8
 },
 {
  "ID-1": 9
 },
 {
  "ID-1": 10
 }
],
"Sheet2":[] //It should be a empty array if a sheet is empty.
}

Test Files:
test.zip (7.7 KB)

Q3

JsonSaveOptions.SkipEmptyRows is not working.
Code:

    var doc = new Aspose.Cells.Workbook(@"C:\Users\XCL\Desktop\test\1.xlsx");
    var ops = new Aspose.Cells.JsonSaveOptions();
    ops.ExportNestedStructure = true;
    ops.SkipEmptyRows = true;
    doc.Save(@"C:\Users\XCL\Desktop\test\1.json", ops);

Received Result:

[
 {
  "ID-1": 1
 },
 {
  "ID-1": 2
 },
 {
  "ID-1": 3
 },
 null,
 null,
 {
  "ID-1": 4
 },
 {
  "ID-1": 5
 },
 {
  "ID-1": 6
 },
 {
  "ID-1": 7
 },
 {
  "ID-1": 8
 },
 {
  "ID-1": 9
 },
 {
  "ID-1": 10
 }
]

Expected Result:

[
 {
  "ID-1": 1
 },
 {
  "ID-1": 2
 },
 {
  "ID-1": 3
 },
 {
  "ID-1": 4
 },
 {
  "ID-1": 5
 },
 {
  "ID-1": 6
 },
 {
  "ID-1": 7
 },
 {
  "ID-1": 8
 },
 {
  "ID-1": 9
 },
 {
  "ID-1": 10
 }
]

Test Files:
test.zip (7.0 KB)

2

@xucongli1989,

This is by design. If the workbook has one (only) worksheet, then we will not render sheet name in the output JSON.

I reproduced the issue as you mentioned by using your sample file. I noticed JSON’s value is missing when a sheet is empty in the workbook. I have logged a ticket with an id “CELLSNET-52499” for your issue. We will look into it soon.

I reproduced the issue as you mentioned by using your sample file. I noticed JsonSaveOptions.SkipEmptyRows is not working properly. I have logged a ticket with an id “CELLSNET-52500” for your issue. We will look into it soon.

We will look into the details of your mentioned issues. Once we have an update on any of the issues, we will let you know.

3

@xucongli1989,

We are pleased to inform you that your issues (logged earlier as “CELLSNET-52499” and “CELLSNET-52500”) have been resolved now. The fix will be included in our upcoming release (Aspose.Cells v23.1) which is scheduled in the second week of January 2023. You will be notified once the next release is published.

1 Like
4

@xucongli1989
For Q1, we will add JsonSaveOptions.AlwaysExportAsJsonObject property to support your needs in the next release version 23.1 with the following codes:

Workbook workbook = new Workbook( "CellsNet52502.xlsx");
JsonSaveOptions saveOptions = new JsonSaveOptions();
saveOptions.AlwaysExportAsJsonObject = true;
saveOptions.ExportNestedStructure = true;
workbook.Save(Constants.destPath + "CellsNet52502.json", saveOptions);
1 Like
5

The issues you have found earlier (filed as CELLSNET-52499,CELLSNET-52500,CELLSNET-52502) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi