Convert Json data to Excel File in the Specific Format

mounika ymr

I'm new to C# I've tried so many ways to tackle this scenario, but i couldn't find a way to achieve this task, I've a string data I wanted to Write into Excel file in a specific format. I've attached Excel Format and Json data in zip file. Please find the attachment. I want to split the string in such a way that it should be appear as given excel format Please Provide me a solution ASAP. Thanks in Advance.

//Note: Strings with Same Column Name should write under that column and if that column is not present in the particular string it should write null

Code I've tried so far:

void StreamExcelFile(Stream Stream1)

{

//StreamWriter writer1 = new StreamWriter(Stream1);

Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

Excel.Workbook xlWorkBook;

Excel.Worksheet xlWorkSheet1;

object misValue = System.Reflection.Missing.Value;

xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

var count = response.Where(x => x == ':').Count();

// response = response.Replace('"', ' ').Trim();

response = response.Replace('{', ' ').Trim();

response = response.Replace('}', ' ').Trim();

//response = response.Replace(':', ' ').Trim();

//response = response.Replace(',', ' ').Trim();

//response = response.Replace(" ", "").Trim();

int j = 1, k = 2;

for (int i = 1; i <= 60; i++)

{

// string[] coldata = response.Split(new char[] { ':' }, 2);

// string rowdata = response.Split(':', ',')[i];

string rowdata;

rowdata = response.Split(':', ',')[i];

//if(rowdata.Contains(""))

//{

// rowdata.Split(' ').Skip(1);

// i;

//}

if (i % 2 == 0)

{

if (i == 2)

xlWorkSheet1.Cells[2, i - 1].value = rowdata;

else

{

xlWorkSheet1.Cells[2, i - (1 * k)].value = rowdata;

k = k + 1;

}

else if (i == 1)

xlWorkSheet1.Cells[1, i].value = rowdata;

else

{

xlWorkSheet1.Cells[1, i - (1 * j)].value = rowdata;

j = j + 1;

}

Excel.Worksheet xlWorkSheet2;

xlWorkSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Add(System.Reflection.Missing.Value, xlWorkBook.Worksheets[xlWorkBook.Worksheets.Count], System.Reflection.Missing.Value, System.Reflection.Missing.Value);

if (System.IO.File.Exists("D:\\ExcellData.xls"))

{

File.Delete("D:\\ExcellData.xls");

}

xlWorkBook.SaveAs("D:\\ExcellData.xls");

xlWorkBook.Close(true, misValue, misValue);

xlApp.Quit();

Marshal.ReleaseComObject(xlWorkSheet1);

Marshal.ReleaseComObject(xlWorkBook);

Marshal.ReleaseComObject(xlApp);

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Debasis Saha8y ago
I will revert you soon regarding this....
0
mounika ymr8y ago
I've tried ur datatable method but getting exception:After parsing a value an unexpected character was encountered: I. Path 'EntityMetadata[0].PropertyMetaDataList[0]', line 7, position 13.'I'm not using Windows forms, can u please suggest a way to write this json data into excel in the format i send.
0
Debasis Saha8y ago
U can use the below code snippet for convert json data into Datatable

For using C#
public DataTable GetDataTableFromJsonString(string json)
{
    var jsonLinq = JObject.Parse(json);

    // Find the first array using Linq
    var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
    var trgArray = new JArray();
    foreach (JObject row in srcArray.Children())
    {
        var cleanRow = new JObject();
        foreach (JProperty column in row.Properties())
        {
            // Only include JValue types
            if (column.Value is JValue)
            {
                cleanRow.Add(column.Name, column.Value);
            }
        }
        trgArray.Add(cleanRow);
    }

    return JsonConvert.DeserializeObject(trgArray.ToString());
}
For using Vb.Net
Public Function GetDataTableFromJsonString(json As String) As DataTable
    Dim jsonLinq = JObject.Parse(json)

    ' Find the first array using Linq
    Dim srcArray = jsonLinq.Descendants().Where(Function(d) TypeOf d Is JArray).First()
    Dim trgArray = New JArray()
    For Each row As JObject In srcArray.Children(Of JObject)()
        Dim cleanRow = New JObject()
        For Each column As JProperty In row.Properties()
            ' Only include JValue types
            If TypeOf column.Value Is JValue Then
                cleanRow.Add(column.Name, column.Value)
            End If
        Next
        trgArray.Add(cleanRow)
    Next

    Return JsonConvert.DeserializeObject(Of DataTable)(trgArray.ToString())
End Function
I am using the below sample Json File for the above code -
{
    "isSuccess": true,
    "serviceData":{
        "quality_measures":[
            {
                "measure_name": "string",
                "measure_description": "string",
                "quality_id": "string",
                "emeasure_id": "string",
                "emeasure_nqf": "string",
                "nqf": "string",
                "nqs_domain": "string",
                "primary_measure_steward": "string",
                "measure_type": "string",
                "high_priority": "string",
                "claims_submission": "string",
                "csv_submission": "string",
                "ehr_submission": "string",
                "cms_web_interface_submission": "string",
                "registry_submission": "string",
                "specialty_measure_set": "string"
            }
        ]
    }
}
Hope this will help you.
0
mounika ymr8y ago
Here is the sample data. Please find the attached file
+1
Debasis Saha8y ago
Can U Send us any sample Data. There should some separate between different field data so that we can identify that.
0