We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Import JSON string to Excel file using Aspose.Cells for .NET

Problem Statement: I want to import a JSON string as an excel file using Aspose.Cells for .NET.

JSON details:
JSON string serialized based on object - ResponseMessage,

public class ResponseMessage
{
public List LstMyCustomDatatype { get; set; }

    public List<string> LstErrorMessages { get; set; }
}

public class MyCustomDatatype
{
public int Prop1{ get; set; }
public string Prop2{ get; set; }
public string Prop3{ get; set; }
public string Prop4{ get; set; }
}

Where, No. of items in first and second list will differ and need not match. For ex, MyCustomDatatype can include 4 MyCustomDatatype kind of list items and LstErrorMessages can include any number of strings like zero or two or ten etc.

MyCustomDatatype is balanced a JSON internally. For 4 MyCustomDatatype objects, it will have value for each property.

Now, I am able to import JSON string to excel only if LstErrorMessages has no items, otherwise it fails with the exception - Invalid end column index.

My current code details:

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

            JsonLayoutOptions options = new JsonLayoutOptions();
            options.TitleStyle = SetStyles();
            options.ArrayAsTable = true;

            // Import JSON Data
            JsonUtility.ImportData(jsonContent, worksheet.Cells, 0, 0, options);

            // Auto-fit all the columns
            worksheet.AutoFitColumns();

            // Save Excel file   
            workbook.Save(reportFileName);

Notes:

  1. code based on: https://docs.aspose.com/display/cellsnet/Import+Data+into+Worksheet#ImportDataintoWorksheet-ImportingDatafromJSON
  2. jsonContent is a valid JSON. Able to view in JSON Visualizer in Visual Studio.

Could not locate more information for this issue on support.
Kindly, let me know if more details required. Kindly help me out.

Regards,
Apurva

Not marking this as answer, because such kind of JSON has issue if it needs to be imported to excel directly.

I have used following Work-around in my case.

Instead of JSON string (this was in sync with flow of the actual project) used ResponseMessage object.

Then, imported list LstMyCustomDatatype as excel file using

worksheet.Cells.ImportCustomObjects(responseMessage.LstMyCustomDatatype, 0, 0, importTableOptions);

Will import LstErrorMessages either as a separate log file or update the excel.

@apurvab,

Thanks for the details.

I think Cells.ImportCustomObjects will suit your needs. And, good to know that you have already devised the approach and using Cells.ImportCustomObjects method for your scenario/case.

Thanks for quick turn-around and letting know that I went to the correct path :slight_smile:

@apurvab,

You are welcome.