Handling JSON Array as Single Cell in CSV Using Aspose

Aspose.Cells Product Family
,
1

Hello Aspose Support Team,

I am exploring the possibility of converting a JSON array property into CSV format using Aspose APIs. I already have a sample payload, template, and generated output, but my expected output is different. I would like the JSON array to be handled so that all elements of the array appear together in a single CSV cell, rather than splitting into multiple rows.

Refer:
Original Output - output.xlsx
Expected Outout - output_Expected.xlsx

I have attached all the required documents, including the sample JSON payload, current template, and generated CSV output. Please review them and let me know if anything else is needed.

Thank you in advance for your support.

Sample.7z (27.9 KB)

Best regards,
Rupali

2

@rupali14

To handle a JSON array as a single cell in a CSV file using Aspose.Cells, follow these steps:

  1. Prepare Your JSON Data: Ensure your JSON array is formatted correctly. For example:

    {
    "data": [
        {
            "name": "John",
            "hobbies": ["Reading", "Traveling", "Swimming"]
        },
        {
            "name": "Jane",
            "hobbies": ["Cooking", "Dancing"]
        }
    ]
}

  2. Convert JSON to CSV: Use Aspose.Cells to read the JSON and format the array elements into a single string for each cell. You can join the array elements with a delimiter (like a comma) before writing to the CSV.

  3. Sample Code: Here’s a minimal example in C#:

    using Aspose.Cells;
using Newtonsoft.Json;
using System.Collections.Generic;

// Load JSON data
string json = System.IO.File.ReadAllText("data.json");
var jsonData = JsonConvert.DeserializeObject<RootObject>(json);

// Create a new workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

// Write data to the worksheet
for (int i = 0; i < jsonData.data.Count; i++)
{
    worksheet.Cells[i, 0].PutValue(jsonData.data[i].name);
    worksheet.Cells[i, 1].PutValue(string.Join(", ", jsonData.data[i].hobbies));
}

// Save as CSV
workbook.Save("output.csv", SaveFormat.CSV);

  4. Define the RootObject Class: You need to define the structure of your JSON data:

    public class RootObject
{
    public List<Data> data { get; set; }
}

public class Data
{
    public string name { get; set; }
    public List<string> hobbies { get; set; }
}

This approach ensures that all elements of the JSON array appear together in a single CSV cell. If you encounter any specific issues with your implementation or need further assistance, feel free to ask!

3

@rupali14,

Thanks for the template Excel file and JSON data.

I tested your scenario/case using your template Excel file and JSON data. I could not handle JSON array as Single cell for Smart Markers in output CSV/XLSX using Aspose.Cells APIs.

Workbook workbook = new Workbook("d:\\files\\SampleData_Template.xlsx");
WorkbookDesigner workbookDesigner = new WorkbookDesigner(workbook);
String jsonData = new String(Files.readAllBytes(Paths.get("d:\\files\\Sample.json")));
workbookDesigner.setJsonDataSource(null, jsonData);
workbookDesigner.process();

workbook.save("d:\\files\\out1.xlsx");

We require thorough evaluation if we could enhance arrays in Smart Markers processing as per your desired output. 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): CELLSJAVA-46535

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.

4

Thank you for your prompt response and for creating the internal ticket (CELLSJAVA-46535) to evaluate this enhancement request.

I appreciate your team’s efforts in reviewing the possibility of improving array handling in Smart Markers. I understand that the fix will be delivered according to your Free Support Policies.

Please keep me updated on any progress or expected timelines regarding this issue.

Thanks again for your continued support.

5

@rupali14,

We will review the issue/requirements and work on figuring it. Rest assured, we will keep you informed with updates as soon as they are available.

6

@rupali14
It’s better that you can update json data source as: “SizeRange”: “L, M, S, XL”.
If you want handle JSON Array with a new smart marker by us , there are two issues:
1, How to handle an object array if the array is not a simple value array？
2, Which separator should be used or How to layout array data？

7

@simon.zhao Thank you for responding…

Our JSON payload is fully dynamic and cannot be modified or pre-processed before binding. We need any JSON array — whether simple value arrays or object arrays — to be displayed entirely within a single Excel cell as plain text, rather than being split into multiple rows.

Examples:

  1. Simple Array

{“SizeRange”: [“L”,“M”,“S”,“XL”]}

Expected Output (single cell):
[“L”,“M”,“S”,“XL”]

  1. Object Array

{“Products”: [{“Name”:“Shirt”,“Price”:500},{“Name”:“Jeans”,“Price”:900}]}

Expected Output (single cell):
[{“Name”:“Shirt”,“Price”:500},{“Name”:“Jeans”,“Price”:900}]

Addressing the two points raised:

1. Handling Object Arrays:

Object arrays should be treated as a single value and displayed as a complete JSON string in one cell, regardless of complexity or nesting.

This avoids the need to flatten or pre-process the JSON, keeping the data intact.

2. Separator / Layout:

Since the full array or object array is displayed in a single cell, no additional separator is required.

For simple arrays, a custom separator can be used if needed, but object arrays must preserve the original JSON structure.

This approach ensures that JSON arrays are rendered exactly as received, in a single cell, without modifying the original data, while maintaining both readability and structure.

We sincerely appreciate your guidance on implementing this approach effectively with the new Smart Marker.

8

@rupali14
Thank you for your feedback and detailed explanation. We will further analyze your issue and notify you promptly once there are any updates.

9

@rupali14
Do you mean we should just simply process JSON Array as a single value ,not a struct?
My current design : Smart Marker : “&=node.SizeRange(ArrayAsSingle:,)” result : “L,M,S,XL”
If you just want original value of SizeRange property, I think we can try to support as “&=node.SizeRange(ArrayAsSingle:original )” or “&=node.SizeRange(ArrayAsSingle)”. If the value of ArrayAsSingle is “original” , we just simply export a JSON string
BTW, though we can export a JSON String to the cell, is it accepted for your customers?