none
Write Json To Excel RRS feed

  • Question

  • Dear experts

    i use following code to write a Excel file from .json file

                string ofilepath = @"E:\temp\Gst2-error.json";
                OpenFileDialog fd = new OpenFileDialog();
                fd.FileName = @"E:\temp\Gst2-error.json";
                fd.Filter = "(*.json)|*.json";
                if (fd.ShowDialog() == DialogResult.OK)
                {
                    ofilepath = Path.ChangeExtension(fd.FileName, "xls");
                }
                else
                {
                    MessageBox.Show("Json File Not Selected");
                    return;
                }
                if (!Directory.Exists(@"c:\gstreturns"))
                {
                    Directory.CreateDirectory(@"c:\gstreturns");
                }
    
                using (FileStream s = File.Open(fd.FileName, FileMode.Open))
                using (StreamReader sr = new StreamReader(s))
                using (JsonReader reader = new JsonTextReader(sr))
                {
                    JsonSerializer serializer = new JsonSerializer();
                    GSTR1 o;
                    while (reader.Read())
                    {
                        if (reader.TokenType == JsonToken.StartObject)
                        {
                            //dynamic jr = serializer.Deserialize<dynamic>(reader);
    
                            o = serializer.Deserialize<GSTR1>(reader);
    
                            string swFileName = @"c:\gstreturns\" + GetUniqueName("b2b.csv", @"c:\gstreturns\");
                            StreamWriter sw = new StreamWriter(swFileName, cbIsAppend.Checked);
    
                            string monthyear = new DateTime(2018,Convert.ToInt16(o.fp.Substring(0, 2)), 1).ToString("MMM", CultureInfo.InvariantCulture);
                            monthyear += " " + o.fp.Substring(2);
    
                            #region b2b
                            string s1 = monthyear +"," + o.gstin,
                                s2 = "", s3 = "", s4 = "";
                            if (o.b2b != null)
                            {
                                b2bcsv(o, swFileName, sw, s1, ref s2, ref s3, ref s4);
                            }
                            #endregion b2b
    
                            if (o.cdn != null)
                            {
                                cdncsv(o, out swFileName, out sw, monthyear, out s1, out s2, out s3, out s4);
                            }
    
                            if (o.cdnr != null)
                            {
                                cdnrcsv(o, out swFileName, out sw, monthyear, out s1, out s2, out s3, out s4);
                            }
                        }
                    }
                }

    here i need first to create json class ( GSTR1) .

    can i write excel without creating class dynamically as below. i am unable to think how i can do using following , can some body help me ?

    dynamic jr = serializer.Deserialize<dynamic>(reader);



    Wednesday, October 24, 2018 6:34 AM

All replies

  • Hi Sushil Agarwal,

    Welcome to the MSDN forum.

    Refer to your description, it seems your issue is about the C# development. Since our forum is to discuss the VS IDE, I will help you move this thread to the appropriate forum for a better support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 24, 2018 8:45 AM
  • Hi Sushil Agarwal,

    Maybe you could Deserialize Json to Datatable

    string json = @"{
      'Table1': [
        {
          'id': 0,
          'item': 'item 0'
        },
        {
          'id': 1,
          'item': 'item 1'
        }
      ]
    }";
    
    DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);
    
    DataTable dataTable = dataSet.Tables["Table1"];
    
    Console.WriteLine(dataTable.Rows.Count);
    // 2
    
    foreach (DataRow row in dataTable.Rows)
    {
        Console.WriteLine(row["id"] + " - " + row["item"]);
    }
    // 0 - item 0
    // 1 - item 1

    Then you could export datatable to excel

    var lines = new List<string>();
    
    string[] columnNames = dataTable.Columns.Cast<DataColumn>().
                                      Select(column => column.ColumnName).
                                      ToArray();
    
    var header = string.Join(",", columnNames);
    lines.Add(header);
    
    var valueLines = dataTable.AsEnumerable()
                       .Select(row => string.Join(",", row.ItemArray));            
    lines.AddRange(valueLines);
    
    File.WriteAllLines("excel.csv",lines);

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 25, 2018 2:33 AM
    Moderator
  • Thanks Zhanglong Wu,

    This is a sample json file with two objects Jw2m, m2jw

    to convert json to excel ,

    can schema be not read from the json file it self, what i mean is for all nested data with in json object generate a cross join single row of related table and convert it to excel, CSV etc.

    i dont know if it can me done or not.

    i thought 

    dynamic jr = serializer.Deserialize<dynamic>(reader);

    will be able to do it.

    but i could not go ahead as how to go further to generate excel or csv

    json sample


    • Edited by Sushil Agarwal Thursday, October 25, 2018 9:33 AM better readable
    Thursday, October 25, 2018 4:59 AM
  • Error reading json to table
    Thursday, October 25, 2018 9:27 AM
  • Hi Sushil Agarwal,

    Could you please share related JSON files, you could create a simple file with a litter records, which could reproduce the issue.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 25, 2018 9:33 AM
    Moderator
  • sir ,

    you can download file from here

    sample Json file

    Thursday, October 25, 2018 9:36 AM
  • Hi Sushil Agarwal,

    Based on yours JSON file, I would suggest that you could deserialize to JObject, like this:

    string jsonstring = File.ReadAllText("Test.json");
    JObject result = JsonConvert.DeserializeObject<JObject>(jsonstring);

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 26, 2018 7:28 AM
    Moderator
  • dynamic jr = serializer.Deserialize<dynamic>(reader);

    why not use this ?

    may be i am wrong, but both seams to be functionally same to me .

    once i get object by any of the method , i want to know how can i convert each anonymous object in to a csv or excel file


    Friday, October 26, 2018 7:54 AM
  • string jsonstring = File.ReadAllText("Test.json");
    JObject result = JsonConvert.DeserializeObject<JObject>(jsonstring);

    and 

    dynamic jr = serializer.Deserialize<dynamic>(reader)

    appears to be same.

    i have got stuck how to write a recursion function to read each objects records in nested json object to a csv rows per object


    Sunday, October 28, 2018 12:15 PM
  • Hi Sushil Agarwal,

    >>appears to be same.

    Yes, based on your Json structure, it could not Deserialize to datatable object that you need to read each objects records in nested json object to a csv rows per object

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 29, 2018 5:22 AM
    Moderator