Hello guys!
We are building a POC where a user may create an xlsx file from JSON data, do some changes and import it back.
The problem we have right now that the JSON structure is being corrupted.
Original JSON:
[
{
"RootProperty": "RootPropertyValue",
"Payload":
{
"Array":
[
{
"ArrayProperty": "a1"
},
{
"ArrayProperty": "a2"
},
{
"ArrayProperty": "a3"
}
],
"PayloadProperty": "PayloadPropertyValue",
"PayloadStruct":
{
"PayloadProperty1": "val1",
"PayloadProperty2": "val2"
}
}
}
]
JSON I get when I import from xlsx:
{
"1":[{
"RootProperty":"RootPropertyValue",
"Payload":{
"Array":{
"ArrayProperty":["a1","a2","a3"
]
},
"PayloadProperty":"PayloadPropertyValue",
"PayloadStruct":{
"PayloadProperty1":"val1",
"PayloadProperty2":"val2"
}
}
}]
}
please note that Payload.Array is not an array of objects but an object that contains an array of strings which is wrong.
Is there any way to preserve the JSON schema, do something during deserialization or something else?
Thanks!
Kseniya
Data structure:
public class Data
{
public string RootProperty { get; set; }
public Payload Payload { get; set; }
}
public class Payload
{
public ArrayData[] Array { get; set; }
public string PayloadProperty { get; set; }
public PayloadStruct PayloadStruct { get; set; }
}
public class PayloadStruct
{
public string PayloadProperty1 { get; set; }
public string PayloadProperty2 { get; set; }
}
public class ArrayData
{
public string ArrayProperty { get; set; }
}
Export code:
public Stream ExportToXlsx ()
{
var workbook = new Workbook ();
workbook.Worksheets.Clear ();
var worksheet = workbook.Worksheets.Add ("1");
var data1 = new Data ()
{
RootProperty = "RootPropertyValue",
Payload = new Payload ()
{
Array = new[]
{
new ArrayData () { ArrayProperty = "a1" },
new ArrayData () { ArrayProperty = "a2" },
new ArrayData () { ArrayProperty = "a3" },
},
PayloadProperty = "PayloadPropertyValue",
PayloadStruct = new PayloadStruct ()
{
PayloadProperty1 = "val1",
PayloadProperty2 = "val2"
}
}
};
var serializeObject = JsonConvert.SerializeObject (new[] { data1 });
JsonUtility.ImportData (serializeObject, worksheet.Cells, 0, 0, new JsonLayoutOptions ());
var stream = new MemoryStream ();
workbook.Save (stream, SaveFormat.Xlsx);
stream.Position = 0;
return stream;
}
Import code:
public IReadOnlyCollection<Data> ReadFromXlsx2 (Stream file)
{
var workbook = new Workbook (file);
// TODO: remove in PROD
var trialSheet = workbook.Worksheets.SingleOrDefault (i => i.Name == "Evaluation Warning");
if (trialSheet != null)
{
workbook.Worksheets.RemoveAt (trialSheet.Name);
}
using var jsonStream = new MemoryStream ();
workbook.Save (jsonStream, new JsonSaveOptions ()
{
ExportNestedStructure = true,
SkipEmptyRows = true,
AlwaysExportAsJsonObject = true,
ValidateMergedAreas = true,
});
jsonStream.Position = 0;
using var reader = new StreamReader (jsonStream);
var text = reader.ReadToEnd ();
var deserializeObject = JsonConvert.DeserializeObject<JObject> (text);
var data = new List<Data> ();
foreach (var jProperty in deserializeObject.Properties ())
{
data.AddRange (jProperty.Value.ToObject<Data[]> ());
}
return data.ToArray ();
}