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
Shamina MaharajSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eddie ShipmanAll-around developerCommented:
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.
0
Dirk StraussSenior Full Stack DeveloperCommented:
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:
  1. Deserialize the JSON string back into an object
  2. Use EPPlus to create an Excel file from the deserialized JSON object
0
Eddie ShipmanAll-around developerCommented:
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

0
Dirk StraussSenior Full Stack DeveloperCommented:
Don't use var table. To clarify, from the article link I posted:

Here is an example of JSON
[
  {
    "user":"12345678-4321-1234-4321-123456789123",
    "entity_type":"user",
    "datetime":"2012-04-07T22:26:10.947Z"
  }
]

Open in new window

To deserialize that, you need to create a class (the object):
public class myJsonClass
{
    public string user { get; set; }
    public string entity_type { get; set; }
    public string datetime { get; set; }
}

Open in new window

I usually use extension methods, but you can use any method. This is the extension method
public static class ExtensionMethods
{
    public static T CreateObjectFrom_JSON<T>(this string value)
    {
        return JsonConvert.DeserializeObject<T>(value);
    }
}

Open in new window

You can then deserialize the JSON string back into the object (the class you created earlier) as follows:
myJsonClass myjsonObj = jsonString.CreateObjectFrom_JSON<myJsonClass>();

Open in new window

Basically, your JSON is a string. You are creating an object from that string via deserialization. You can then use that object to grab the data and use EPPlus to create an Excel document.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MacroShadowCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.