Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to export JSON to Excel

Posted on 2014-08-20
4
Medium Priority
?
17,502 Views
1 Endorsement
Last Modified: 2016-11-23
How can I export JSON to Excel. Click a button create the JSON (i can do this) export to EXCEL send to browser/download?
Can't seem to find a good example.
thx
1
Comment
Question by:JElster
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
Brad Groux earned 2000 total points
ID: 40275023
I have attached an HTML and JavaScript file to this message (code below). Download the HTML file, and rename your JSON file to json.js and put it in the same folder as the HTML file. Navigate to the HTML file in your browser, and it should prompt you to save a .CSV of your JSON.

Create a .HTML file utilizing this HTML and JavaScript below:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>JSON to CSV</title>
    <script src="scripts/json.js" type="text/javascript"></script>
    <script type="text/javascript">
    var json3 = { "d": "[{\"Id\":1,\"UserName\":\"Sam Smith\"},{\"Id\":2,\"UserName\":\"Fred Frankly\"},{\"Id\":1,\"UserName\":\"Zachary Zupers\"}]" }

    DownloadJSON2CSV(json3.d);

    function DownloadJSON2CSV(objArray)
    {
        var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;

        var str = '';

        for (var i = 0; i < array.length; i++) {
            var line = '';

            for (var index in array[i]) {
                line += array[i][index] + ',';
            }

            // Here is an example where you would wrap the values in double quotes
            // for (var index in array[i]) {
            //    line += '"' + array[i][index] + '",';
            // }

            line.slice(0,line.Length-1); 

            str += line + '\r\n';
        }
        window.open( "data:text/csv;charset=utf-8," + escape(str))
    }

    </script>

</head>
<body>
    <h1>This page does nothing....</h1>
</body>
</html>

Open in new window

D--Temp-json.html
0
 
LVL 1

Author Comment

by:JElster
ID: 40275114
How can I include the Column names? Like Username on the first row
thx!
0
 
LVL 1

Author Comment

by:JElster
ID: 40275117
Also, how can I include the file extension
0
 

Expert Comment

by:Vijay Soni
ID: 41900122
can i add style for excel cell like font color, background color?
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The most up-to-date version of this article is on my Blog https://iconoun.com/blog/
How to build a simple, quick and effective accordion menu using just 15 lines of jQuery and 2 css classes
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question