asked on

Convert Json String to Excel

I am currently developing a credit check app which uses web services and the response I get is in Json string format. I tried converting this to a Json object... however I was unsuccessful. I need to store this json result into an excel document. The json result is in the following format after desterilizing:

[
{
"Key": "CreditResult",
"Value": [
[
{
"Key": "ConsumerInfo",
"Value": {
"RecordSeq": "01",
"Part": "001",
"PartSeq": "01",
"ConsumerNo": "943599475",
"Surname": "TEST SURNAME",
"Forename1": "TEST NAME",
"Forename2": "",
"Forename3": "",
"Title": "MS",
"Gender": "F",
"NameInfoDate": "20170129",
"DateOfBirth": "20170101",
"IdentityNo1": "1234567891234",
"IdentityNo2": "",
"MaritalStatusCode": "",
"MaritalStatusDesc": "",
"Dependants": "00",
"SpouseName1": "",
"SpouseName2": "",
"TelephoneNumbers": "H(000)0000000 B(0000)0000",
"DeceasedDate": "00000000"
}
},
{
"Key": "LastAddress",
"Value": {
"ConsumerNo": "943599475",
"InformationDate": "20170911",
"Line1": "ABC",
"Line2": "",
"Suburb": "ABC",
"City": "ABC",
"PostalCode": "0000",
"ProvinceCode": "A",
"Province": "ABC",
"AddressPeriod": "00",
"OwnerTenant": "",
"AddressChanged": "Y"
}
}
],
[
{
"Key": "CreditScore",
"Value": {
"ConsumerNo": "943599475",
"PolicyFilters": [
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"Indicators": [
{
"Type": "00",
"Score": "000"
},
{
"Type": "00",
"Score": "0"
},
{
"Type": "00",
"Score": "0"
},
{
"Type": "00",
"Score": "0"
},
{
"Type": "00",
"Score": "0"
},
{
"Type": "00",
"Score": "0"
},
{
"Type": "00",
"Score": "0"
}
],
"ScoreReasons": [
"00",
"00",
"00",
"00",
"00"
],
"RiskBand": "0",
"ReferRiskBand": "0",
"Recommended": "0",
"RuleDescription": ""
}
}
],
[
{
"Key": "FraudPermission",
"Value": true
}
],
[
{
"Key": "Date Requested",
"Value": "2017/11/27 02:06:43 PM"
}
],
[
{
"Key": "User Email",
"Value": "Shamina@oficinagroup.com"
}
],
[
{
"Key": "AuditLog",
"Value": "116315"
}
]
]
}
]

THE CODE ON C# TO REQUEST INFORMATION:

public string doQuickcreditAndFraud(bool doFraud, bool doCredit, string username, string password, string contactFirstName, string contactLastName, string contactID, string clientNumber, string clientEmail, string apiKey)
{
string Item = null;
try
{
Item = this.Proxy.doQuickCreditAndFraud(doFraud, doCredit, username, password, contactFirstName, contactLastName, contactID, clientNumber, clientEmail, apiKey);
}
catch (Exception ex)
{
this.ErrorMessage = ex.Message;
}
var table = JsonConvert.DeserializeObject(Item);
return Item.ToString();

}

Please assist.
Many thanks

Eddie Shipman

After converting to a POCO, you need to use something like a mapper to convert it to Excel.
This is the first one I found: https://github.com/mganss/ExcelMapper

But, based on the JSON you provided, I don't see how that will map to Excel. please provide the definition of the POCO.

Note, next time, please use the code tags to surround your JSON and code.

Dirk Strauss

The first step is to successfully deserialize the JSON back into an object. Have a look at the answered question JSON object to C# on how to do this. You will need to install Newtonsoft.Json from NuGet to do this.

Secondly, whenever I need to create any Excel document, I always use EPPlus. Have a look at How To Create An Excel File (Development) Using EPPlus .NET Library (C#) - Part One to see how that is done.

So basically:

Deserialize the JSON string back into an object
Use EPPlus to create an Excel file from the deserialized JSON object

Eddie Shipman

If you look at his code, he's already deserializing it, I just don't see the definition of the object.

var table = JsonConvert.DeserializeObject(Item);

Open in new window

ASKER CERTIFIED SOLUTION

Dirk Strauss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joe Howard

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Dirk Strauss (https:#a42393187)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer