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?

10

Yes, that’s exactly what we mean.
We want the JSON array (whether simple or object array) to be treated as a single value — not expanded or split.

If you can support ArrayAsSingle:original (or simply ArrayAsSingle as you suggested) to export the full JSON array as a plain string into one cell, that would perfectly meet our requirement.

And yes, exporting the full JSON string in the cell is completely acceptable for our customers. In fact, this is the preferred behavior since they need to preserve the original JSON structure for downstream processing.

Thank you for considering this enhancement — this approach would solve our use case completely.

11

@rupali14
Thanks for you confirm.
We have generated the following file with ArrayAsSingle if the value of the array is simple.
JsonArrayAsSingle.zip (12.6 KB)
We will work on JSON object Array later.
As your customers need to preserve the full JSON string , will they need to export full JSON object string to the cell? For example : &=node.Book, the book is an object.

12

Thanks for sharing the file. Should I test it with the existing SmartMarker tags, or do I need to modify them for this version?

13

@rupali14,

You will need to modify markers (we will share relevant details or provide template Excel file) in the template Excel file with the upcoming release, i.e., Aspose.Cells v25.11 that we plan to release in the first half (approximately between the 10th and 15th) of November 2025.

15

Thank you for the heads-up regarding the upcoming Aspose.Cells v25.11 release. Once you share the updated details or the template Excel file, we’ll proceed with modifying the markers accordingly and ensure compatibility.

16

@rupali14
Thank you for your feedback. You are welcome. We will notify you promptly once there are any updates.

17

The issues you have found earlier (filed as CELLSJAVA-46535) have been fixed in Aspose.Cells for Java 25.11.

18

I’m not able to identify which smart marker needs to be used, and I also can’t find the published document for this issue in the new version. Could you please provide it as well? It would be very helpful for me.

19

@rupali14,

We will evaluate and may provide you sample document with details soon. In the meantime, you may try to use “ArrayAsSingle” as parameter for the marker in which you want to render JSON array as single cell value. e.g., &=node.SizeRange(ArrayAsSingle).

20

@rupali14,

I tested using the template Excel file (attached) with Aspose.Cells for Java 25.11 using the following sample code and it works fine. Please note, I updated your original template Excel file, I just replaced the smart marker “&=node.SizeRange” with “&=node.SizeRange(ArrayAsSingle)”–> I used “ArrayAsSingle” parameter.
e.g.,
Sample code:

Workbook workbook = new Workbook("d:\\files\\SampleData_Template1.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");

Please find attached the zipped archive containing the input and output Excel file for your reference.
files1.zip (24.7 KB)

22

Hi Team,

We would like to request an enhancement/fix regarding the rendering of arrays in a single cell in Aspose.Cells templates. Currently, for scalar and object arrays, the output formatting does not consistently respect the desired delimiter.

We require the output to behave as follows:

Scalar Array Input

Delimiter Input JSON Desired Output (Single Cell)
Comma , ["L","M","S","XL"] “L”,“M”,“S”,“XL”
Comma + Space , ["L","M","S","XL"] “L”, “M”, “S”, “XL”
Space ["L","M","S","XL"] “L” “M” “S” “XL”
Semicolon ; ["L","M","S","XL"] “L”;“M”;“S”;“XL”
New Line \n ["L","M","S","XL"] “L”\n"M"\n"S"\n"XL"

Object Array Input

Delimiter Input JSON Desired Output (Single Cell)
Comma , [{"Size":"L"},{"Size":"M"},{"Size":"S"},{"Size":"XL"}] “L”,“M”,“S”,“XL”
Comma + Space , [{"Size":"L"},{"Size":"M"},{"Size":"S"},{"Size":"XL"}] “L”, “M”, “S”, “XL”
Space [{"Size":"L"},{"Size":"M"},{"Size":"S"},{"Size":"XL"}] “L” “M” “S” “XL”
Semicolon ; [{"Size":"L"},{"Size":"M"},{"Size":"S"},{"Size":"XL"}] `“L”;“M”;“S”;“XL”
New Line \n [{"Size":"L"},{"Size":"M"},{"Size":"S"},{"Size":"XL"}] “L”\n"M"\n"S"\n"XL"

  • For object arrays, only the Size property should be extracted for single-cell output.

  • The fix should ensure the delimiter is correctly applied for all array types, including new line for multi-line cells.

  • This update is necessary to ensure consistent behavior across dynamic template inputs.

We would greatly appreciate your support in implementing this behavior. Please let us know if any additional information or sample files are needed.

Thank you in advance.!