JsonLayoutOptions()

Aspose.Cells Product Family
1

Hi Aspose,

I have been looking into the JsonLayoutOptions() for Converting JSON to Excel but there are a couple of properties that don’t appear to do anything to the output.

I have reviewed the following documents:

JsonLayoutOptions | Aspose.Cells for .NET API Reference

Import Data into Worksheet|Documentation (aspose.com)

I have implemented the following code:

                // Create a Worksheet and assign name
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = docMessage.JsonToExcelWorksheetName;

                // Read JSON File or data
                string jsonInput = File.ReadAllText(docMessage.FilePath);

                // Set Styles
                CellsFactory factory = new CellsFactory();
                Aspose.Cells.Style style = factory.CreateStyle();
                style.HorizontalAlignment = TextAlignmentType.Center;
                style.Font.Color = System.Drawing.Color.BlueViolet;
                style.Font.IsBold = true;

                // Set JsonLayoutOptions
                JsonLayoutOptions options = new JsonLayoutOptions();

                // I can see a visible difference to the output
                options.ArrayAsTable = true;
                options.IgnoreArrayTitle = true;
                options.TitleStyle = style;

                // I can't see any difference to the outputted Excel spreadsheet 
                options.IgnoreObjectTitle = true;
                options.ConvertNumericOrDate = true;
                options.IgnoreNull = true;
                options.DateFormat = "yyyy/MM/dd";

                //Import JSON Data
                JsonUtility.ImportData(jsonInput, worksheet.Cells, docMessage.JsonToExcelRow, 
                docMessage.JsonToExcelColumn, options);

                // Save Excel file
                workbook.Save(coreApiRDoc.FilePath);

Can you elaborate on what the effect is on the saved Excel document using the following properties, as they don’t seem to do anything?

                // I can't see any difference to the outputted excel spreadsheet 
                options.IgnoreObjectTitle = false;
                options.ConvertNumericOrDate = true;
                options.IgnoreNull = true;
                options.DateFormat = "yyyy/MM/dd";

To my mind, the DateFormat for example, should set the date format, but nothing changes to cells with a date in them.

Thanks in advance.

@djsomers

2

@djsomers,

See the description/details for the attributes for your reference.

  • ConvertNumericOrDate: Gets or sets a value that indicates whether the string in JSON is to be converted to numeric or date (if appropriate). For example, “123” is stored as string/text in JSON. When you specify this Boolean attribute to true, Aspose.Cells will automatically convert to numeric value. Please note, a value like “abc” won’t or cannot be converted to numeric value.
  • DateFormat: Gets and sets the format of the date value stored in JSON data.
  • IgnoreNull: Indicates whether the null value (e.g., some attributes have null values in JSON data) should be ignored or not while importing into Excel spreadsheet.
  • IgnoreObjectTitle: Indicates whether to ignore the title if the property of the object is an object. For example, you have main title in the JSON data and you want to hide it. You can specify it true to hide the header in the resulting Excel spreadsheet while import.

Hope, this helps a bit.

3

Hi @Amjad_Sahi,

Thanks for getting back to me.

I have had a play around with the properties but I don’t think that the ConvertNumericOrDate, DateFormat, IgnoreNull and IgnoreObjectTitle is working correctly.

  • ConvertNumericOrDate - does set cells to date format as long as the JSON date is in the following format “12/01/2023”. However, any int/number fields in JSON objects don’t get set to number, which is what I would expect if ConvertNumericOrDate is set to true, they remain as the general data type in the excel document.

  • After setting the ConvertNumericOrDate to true, I then try and reset the date format to something else i.e. “mm/dd/yyyy” or “MM/DD/YYYY” or “mm/dd/yy” or “MM/DD/YY” and nothing changes in the outputted excel document.

  • I also tested to see what happens with IgnoreNull and nothing visibly changes with that either. If I set all the fields in a JSON object to null then the row remains but is completely blank for both true and false.

  • The IgnoreObjectTitle doesn’t work either from what I can tell, as the object titles remain in the excel output when I set the property to true.

Below is my code:

                // Create a Worksheet and assign name
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = docMessage.JsonToExcelWorksheetName;

                // Read JSON File or data
                string jsonInput = File.ReadAllText(docMessage.FilePath);

                // Set Styles
                CellsFactory factory = new CellsFactory();
                Aspose.Cells.Style style = factory.CreateStyle();
                style.HorizontalAlignment = TextAlignmentType.Center;
                style.Font.Color = System.Drawing.Color.BlueViolet;
                style.Font.IsBold = true;

                // Set JsonLayoutOptions
                JsonLayoutOptions options = new JsonLayoutOptions();

                // I can see a visible difference to the output
                options.ArrayAsTable = true;
                options.IgnoreArrayTitle = true;
                options.TitleStyle = style;

                // I can't see any difference to the outputted Excel spreadsheet apart from the Date format being set for dates 
                options.IgnoreObjectTitle = true;
                options.ConvertNumericOrDate = true;
                options.IgnoreNull = true;
                options.DateFormat = "yyyy/MM/dd";

                //Import JSON Data
                JsonUtility.ImportData(jsonInput, worksheet.Cells, docMessage.JsonToExcelRow,
                docMessage.JsonToExcelColumn, options);

                // Save Excel file
                workbook.Save(coreApiRDoc.FilePath);

This is the JSON that I have been using:

WITHOUT OBJECT TITLES:

{
“users”: [
{
“userId”: null,
“firstName”: null,
“lastName”: null,
“phoneNumber”: null,
“emailAddress”: null,
“date”:null
},
{
“userId”: “2”,
“firstName”: “racks”,
“lastName”: “jacson”,
“phoneNumber”: 12345678,
“emailAddress”: “racks.jacson@learningcontainer.com”,
“date”:“12/01/2023”
},
{
“userId”: “3”,
“firstName”: “denial”,
“lastName”: “roast”,
“phoneNumber”: 12345678,
“emailAddress”: “denial.roast@learningcontainer.com”,
“date”:“12/01/2023”
},
{
“userId”: “4”,
“firstName”: “devid”,
“lastName”: “neo”,
“phoneNumber”: 12345678,
“emailAddress”: “devid.neo@learningcontainer.com”,
“date”:“12/01/2023”
},
{
“userId”: “5”,
“firstName”: “jone”,
“lastName”: “mac”,
“phoneNumber”: 12345678,
“emailAddress”: “jone.mac@learningcontainer.com”,
“date”:“12/01/2023”
}
]
}

WITH OBJECT TITLES:

{“games”:{
“featured”: [
{
“game_id”: “2”
},
{
“game_id”: “15”,
}
],
“popular”: [
{
“game_id”: “2”
}
],
“new”: [
{
“game_id”: “2”,
},
{
“game_id”: “15”,
},
{
“game_id”: “1”,
}
]
}}

Could you get someone to take a look and verify my findings? If my implementation is incorrect, then I would like to know as I need to expose the options for my employer.

Kind regards,
@djsomers

4

@djsomers
1, options.ConvertNumericOrDate works, it’s false, there will be some green triangle in Column A and the value of column F can not be formatted .
2, options.IgnoreObjectTitle,IgnoreNull ,DateFormat does not work, we log the issues with id CELLSNET-52690

5

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-52690

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.

6

@djsomers,

This is to let you know that your issue (“CELLSNET-52690”) has been resolved. This fix will be included in the next release (Aspose.Cells v23.2) scheduled in the second or third week of February 2023. We will notify you when the next release is available.

7

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

8

Thank you for such a speedy resolution to the bugs.

Regards,
djsomers

9

@djsomers,
You are welcome.