Issue when exporting to/from JSON

Aspose.Cells Product Family
,
1

hello guys!
thank you for the update, I tried exporting with the new version, and the initial issue is solved, thanks for that!
however when I performed further testing I noticed another 2 issues where data was corrupted, it may not be related to the initial issue though, let me know if I should create another topic for that.
there are 2 issues (It happened with both XLSX and CSV exports):

  1. if the array contains only one element it will be treated as a property - see the difference between initial.json and output.json CurrencyConfigurations1
    image.png (44.0 KB)
    image.png (57.9 KB)

  2. the initial.json contains 2 objects with the array of objects CurrencyConfigurations2, the output.json has merged the arrays for some reason:
    the first object contains 5 items:
    image.png (38.6 KB)
    the second object contains 1 item:
    image.png (57.1 KB)

but both in the ouput.json and exported xlsx file they are getting merged:
image.png (35.0 KB)

I am attaching the files I mentioned
issue.zip (9.3 KB)

I appreciate any help you can provide.

Kseniya

2

@kkolisnichenko,

Thank you for providing the screenshots and template file. In order to accurately assess your issue, could you please zip and attach a standalone console application (source code without compilation errors) that reproduces the issues on our end? Additionally, please provide the expected Excel files and JSON data files (after processing) for reference. We will investigate your issue(s) promptly.

PS. Please note that I have created a new topic by splitting your post for better evaluation and management.

3

@kkolisnichenko
We can reproduce the issues by testing on the latest version v24.7 using sample file and the following sample code. Found that data misplacement and loss occurred when importing JSON into Excel, and single node is exported as attributes when exporting file to JSON.

The sample code as follows:

Workbook workbook = new Workbook(filePath + "initial.json");
            
workbook.Save(filePath + "out_net.xlsx");

JsonSaveOptions options = new JsonSaveOptions();
options.HasHeaderRow = true;
options.ExportNestedStructure = true;
workbook.Save(filePath + "out_net.json", options);

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): 
CELLSNET-56240: Data misplacement and loss occurred when importing JSON into Excel
CELLSNET-56241: Single node is exported as attributes when exporting file to JSON

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.

5

Thanks for the response.
Attaching the assets requested in case it helps with the investigation. We’re evaluating your product for adoption but will need to pause until this is fixed.
Please let me know when it is fixed, thanks!

aspose-cells-issue.zip (21.3 KB)

Kseniya

6

@kkolisnichenko,

Thank you for providing your expected Excel file and your desired JSON data file (after processing). We have logged the archives with the tickets. This will help us to evaluate and investigate the issue and support your needs. Once we have an update on any of the logged tickets, we will notify you here.

7

@kkolisnichenko
24.7.1.zip (7.9 KB)

The above attched file is generated by inner hot fix 24.7.1 with the following codes:

JsonLoadOptions loadOptions = new JsonLoadOptions();
loadOptions.KeptSchema = true;

Workbook workbook = new Workbook(dir + "initial.json", loadOptions);

workbook.Save(dir + "dest.xlsx");
//workbook =new Workbook(dir + "dest.xlsx");
workbook.Save(dir + "dest.json", new JsonSaveOptions()
{
    
    ExportNestedStructure = true,
    SkipEmptyRows = true,
  //  AlwaysExportAsJsonObject = true
});
8

@kkolisnichenko
If we open xlsx file, we can not know the original JSON Schema.
So in order to support your need, would you like to provode a sample JSON to help convering to original JSON?
If yes, we will add JsonSaveOpions.Schemas[] property to accept it.

9

hi @simon.zhao
sure, it was in the zip file I provided earlier but I am attaching it again
intial-json.zip (749 Bytes)

I also want to let you know that my full use case is exporting different schemas per sheet:
so let’s say I have 10 objects of type1 and 5 objects of type2
and I wrote the code to group these objects by type, so that I save 10 objects of type1 into the sheet “1” and then 5 objects pf type2 into the sheet “2”
will it be possible to provide multiple JSON schemas so that when I read from xlsx file I’ll be able to resolve each sheet?

let me know if you need a code example for this

10

@kkolisnichenko
We can support a worksheet contains one json file. Each element of Array JsonSaveOpions.Schemas[] is corresponds to each sheet. But we can not support convert the sheet to json if the sheet contains multi json files .

11

But we can not support convert the sheet to json if the sheet contains multi json files .

you mean here multiple schemas per sheet? if so then it’s okay

We can support a worksheet contains one json file.

basically I need one schema per sheet and I think that’s what you mentioned here^

12

@kkolisnichenko
Thanks for your confirmation.
We will support one schema per sheet later.

13

hi guys!
Can you share that hotfix 24.7.1 version with us so we can continue our evaluation?

thanks

14

@kkolisnichenko,

We do not provide interim versions of hotfixes in normal support forums here. Both tickets are resolved, and fixes for the issues will be included in our upcoming release (Aspose.Cells v24.8) that we plan to release in the first half of August 2024. You will be notified when the next version is released.

By the way, you may consider purchasing Paid support to get a quick fix for the issues for your needs.

1 Like
15

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

16

hello guys!
could you please share an example of how I should provide a JSON schema per sheet when I read from xlsx?
I have a Newtonsoft.Json.Schema.JSchema of a type I want to read, I then serialized it using JsonConvert.SerializeObject and then I passed it here and it didn’t work:

workbook.Save (jsonStream, new JsonSaveOptions ()
{
    Schemas = schemas.ToArray ()
});

looks like I did smth wrong, greatly appreciate any help, thanks!

17

@kkolisnichenko
Please set a simple json file with all structs as the schema.

18

thanks, could you share an example of a schema, please?
so we can align

19

@kkolisnichenko

CELLSNET56240.zip (741 Bytes)

Please check the following codes:

  public void CellsNet56241()
  {
      JsonLoadOptions loadOptions = new JsonLoadOptions();
      loadOptions.KeptSchema = true;

      Workbook workbook = new Workbook(Constants.sourcePath + "CELLSNET56240.json", loadOptions);

      workbook.Save(Constants.destPath + "CellsNet56241.xlsx");
        workbook = new Workbook(Constants.destPath + "CellsNet56241.xlsx");
      workbook.Save(Constants.destPath + "CELLSNET56240.json", new JsonSaveOptions()
      {

          ExportNestedStructure = true,
          SkipEmptyRows = true,
            Schemas = new string[] { File.ReadAllText(Constants.sourcePath + "CELLSNET56240.json") }
          //  AlwaysExportAsJsonObject = true
      });
     

  }

After carefully looking into Jschema, I think the current implement is wrong.
I will look into how to support such schema:

JSchema schema = JSchema.Parse(@"{
  'type': 'object',
  'properties': {
    'name': {'type':'string'},
    'roles': {'type': 'array'}
  }
}");
20

@kkolisnichenko
Could you generate schema as the following:

JSchemaGenerator generator = new JSchemaGenerator();
JSchema schema1 = generator.Generate(typeof(YourRootClass));
File.WriteAllText(dir + "my.json", schema1.ToString());

It will help us test this issue.

21

@kkolisnichenko
Please try the latest version v24.9