13

I want to convert .json file to excel . iam not able to find the solution anywhere for these issue using c# language . Could any one help me out with these along with exact solution.

Improve this question
4
  • First deserialize your json using a library(JSON.NET) and then iterate your data and create the excel rows. msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx If you have any problems post the code here and we will help Commented Sep 7, 2014 at 8:57
  • 2
    @GeorgeVovos Using interop is problematic (requires Office installed and can't be used on webpages due to licensing), good alternative is OpenXML or its wrapper - ClosedXML. Commented Sep 7, 2014 at 9:08
  • PTwr is right.When you do it, don't use interop.I also had trouble with it before Commented Sep 7, 2014 at 9:12
  • Thanks a lot . i will try these procedure as u all said. Commented Sep 7, 2014 at 9:27

4 Answers 4

Reset to default
14

Here is another partial solution, that doesn't require making a json definition, as long as the json is in a table format.

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

From here, you can use any of the numerous datatable -> excel solutions available on the internet.

Many are posted here: How to export DataTable to Excel

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

It depends on the data and what you want to achieve on the Excel side. I would tackle this with Json.NET and output to a CSV file, which may be opened in Excel.

Consider the following JSON... a simple array

[{
    "foo": "bar"
}]

With Json.NET we can do the following to get it into C#

var jsonData = "[{ \"foo\": \"bar\" }]";

var jsonDefinition = new object[]
{
    new {foo = ""}
};

var result = JsonConvert.DeserializeAnonymousType(jsonData, jsonDefinition);

We can then loop through the structure, outputting to a CSV file.

var sb = new StringBuilder();
foreach (dynamic o in result)
{
    sb.AppendLine(o.foo);
}
File.WriteAllText(@"c:\code\test.csv", sb.ToString());
Improve this answer

3 Comments

Hi Krisc, In " sb.AppendLine(o.foo); " im getting an error " The best overloaded method match for 'System.Text.StringBuilder.AppendLine(string)' has some invalid arguments " could you check once what is issue in these. Thanks
You'll have to paste your code somewhere as it's compiling for me :( Sorry!
Sounds like you just need to cast the dynamic into a string before adding it to the reader.
2

It can be done with the help of EPPlus, check below code for complete answer.

  1. Create And write Json Data to Excel

                using (StreamReader r = new StreamReader(file))
            {
                string json = r.ReadToEnd();
                var obj = JsonConvert.DeserializeObject<JObject>(json);

                List<JsonData> dataList = GetDataList(obj);

                var fileName = file.Split('\\').Last().Split('.')[0].Trim().ToString();

                using (ExcelPackage excel = new ExcelPackage())
                {
                    excel.Workbook.Worksheets.Add(fileName);

                    var headerRow = new List<string[]>()
                        {
                                new string[] { "Key", "Value"}
                            };

                    string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

                    var worksheet = excel.Workbook.Worksheets[fileName];

                    worksheet.Cells[headerRange].LoadFromArrays(headerRow);

                    worksheet.Cells[2, 1].LoadFromCollection(dataList);

                    FileInfo excelFile = new FileInfo($"D:\\JsonFilesToExcel\\{folder}\\{fileName}.xlsx");
                    excel.SaveAs(excelFile);
                }

            }

  2. Create C# Object from Reading Json Key Value pairs

    public static List<JsonData> GetDataList(JObject obj)
{
    var listResult = new List<JsonData>();

    foreach (JProperty item in (JToken)obj)
    {
        listResult.Add(new JsonData { Key = item.Name, Value = item.Value.ToString() });
    }
    return listResult;
}
Improve this answer

1 Comment

That's a paid plugin that costs around 379 dollars a year.
2

I used ExcelMapper. This app is great because I just have to send it a C# list and ExcelMapper converts it to Excel. Wow!! It is available via Nuget Manager.

ExcelMapper em = new ExcelMapper();

em.Save(stExportFileFullPath, items, "Totals", true); 
/*
items is a list composed of the below class and fields.

public class CarrierRevenueLOBReport
{  
    public int Year { get; set; }
    public int Month { get; set; }       
    public string Carrier { get; set; }
    public string LOBName { get; set; }
    public string NewOrRenewal { get; set; }
    public double SumPremium { get; set; }
    public double SumCommision { get; set; }
 }    
*/
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.