Using Perl to preview in a Web Page and convert a JSON document into an Excel workbook

arober11
CERTIFIED EXPERT
Published:
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel.

I primarily created the Web based tool to eliminate the  "Can you pull...." requests appearing in my inbox, and as Mac fanboys there weren't any toys around that would allow them to easily get at the data. When I say easily, the requirement was for a Manager proof point and click GUI, that could filter and flatten JSON / BSON documents with, multiple levels of nesting, into an Excel workbook. The nesting even rulled out the likes of iReports.

Anyway a day or two of hacking, while sat monitoring a training session (explains the quality of the code), and I had a rough little toy (Perl CGI + JS + Dojo Grid) that offered a fairly primitive query builder, that would allow them to: Browse, Filter, aggregate and Export data from a  MongoDB.

Had a quiet life for a bit, till a requirement to do something similar for exported JSON files, from a couple of the Social Media sites cropped up. Had a quick think, simply dropped 80% of the code in an evening, and came up with a little toy that would: Upload, Preview OR Convert a JSON document into an Excel Workbook. When I say JSON document, essentially a text file containing an anonymous JSON array of documents e.g.
[
                      {"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"},
                      {"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"},
                      {"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}
                      ]

Open in new window

As similar requests have cropped up on a number of the Q&A sites I though I might as well dig out the minimal version of the toy, cut it down some more, and share.

So assuming you have a PC / Mac / Server with a CGI enabled web-server, if not there are numerous tutorials out there that explaing the basics e.g. 1 2. You'll want to start off by installing the necessary Perl libraries, enter, ideally as root (possibly via sudo):
cpan YAML Data::Dumper Spreadsheet::WriteExcel Excel::Writer::XLSX Tie::IxHash Encode Scalar::Util JSON File::Basename IO::File CGI

Open in new window

Note: If you hit problems with the install first check your firewall allows you to reach ftp.perl.org. You will spend an age thinking about then probably just going for the default options, and / or installing any missing binaries (If playing on a Mac you'll need Xcode from the App store and then the Command Line tools installed, from Xcode's preferences panel).

Once you have the required libraries you can have a stab at getting the simple Upload and preview / convert script running.

I've split the proof of concept logic below into 3:
The CGI Script - A simple Upload and Export form, which offers a very basic Dogo Grid based preview option, along with an optional filter to limit the numer of rows / documents previewed, and a very basic output column filter (Trivial to replace with a dual listbox set-up)
A UI library for the page - For this example just some empty common Header, Footer text, Style Sheet constants held externally
A JSON function library - To do the main work of parsing and converting the JSON, to the chosen output format

If your new to Perl please note the .cgi script needs to be placed in your web-servers cgi-bin directory, or in a directory with Exec permissions, and have a execute attribute set on it. The two libraries will either need to reside in the same directory as the CGI script, or in a directory that is in your @INC path, see bottom of the output from a: env -i perl -V

So first the CGI script:  json2Excel.cgi
#!/usr/bin/perl -w
                      # Purpose: Attempt to convert a file containing an anonymous JSON array in to an Excel  workbook, with a Dojo data Grid preview
                      #
                      #
                      # To-Do:  Chunk file reading / processing,
                      #         Add a proper UI / Fix CSS
                      #         Add some proper exception logic
                      #         Tidy Code
                      
                      use strict;
                      use JSON;
                      use CGI;
                      use CGI::Carp qw ( fatalsToBrowser ); 
                      use ARJsonLib;
                      use ARUiLib;
                      
                      sub process_form($);
                      sub build_form($$$$);
                      
                      #File upload limit 5MB
                      $CGI::POST_MAX = 1024 * 5000;
                      
                      my $cgi                          = new CGI;
                         $cgi->charset('UTF-8');
                      my $sStatusMsg                  = "";
                      my $sResults                    = "";
                      my $sFormFields                 = "";
                      
                      if ($cgi->param) {
                          ($sStatusMsg, $sResults, $sFormFields)=process_form($cgi);
                      }
                      # Re-build the page
                      build_form($cgi, $sStatusMsg, $sResults, $sFormFields);
                      exit;
                      
                      # -------------------------------------------------
                      sub process_form($) {
                        my ($cgi) = @_;
                        my ($sMsg, $sResults, $sFilename, @aOutFields, $sPageFields, $sOutFormat);
                        my ($sFields)                      = "";
                        my ($iPreviewRows)                 = 0;
                        my $sUploadDir                     = "/tmp";     # Change to suit !!!!!!
                        my $safe_filename_characters       = "a-zA-Z0-9_.-";
                      
                        if ($cgi->param('fields')){
                              ($sFields)                      = ($cgi->param('fields')  =~ /^([_a-z][a-z-_,.0-9~]+)$/i);
                              @aOutFields                     = split("~~", $sFields);
                        }
                        if ($cgi->param('previewRows')){
                              ($iPreviewRows)                 = ($cgi->param('previewRows')  =~ /^([0-9]+)$/)  if ( ($cgi->param('previewRows')  =~ /^[0-9]+$/) );
                        }
                        if ($cgi->param('file')){
                              $sFilename                      = $cgi->param('file');
                        }
                        ($sOutFormat) = ($cgi->param('outFormat')       =~ /^([a-z]{3,4})$/i)          if($cgi->param('outFormat'));
                        $sOutFormat = "xls"                                                                       if (! $sOutFormat);
                            
                        if ( !$sFilename ) { return("Error: Filename missing",$sResults); }
                      
                        my ( $sName, $sPath, $sExtension ) = fileparse ( $sFilename, '\..*' ); 
                        $sFilename                         = $sName . $sExtension;
                        $sFilename                         =~ tr/ /_/;
                        $sFilename                         =~ s/[^$safe_filename_characters]//g;
                        if ( $sFilename                    =~ /^([$safe_filename_characters]+)$/ ) { 
                          $sFilename = $1;
                        } else { 
                         return ("Error: Filename contains invalid characters",$sResults);
                        } 
                        if ( ! $sExtension =~ /^\.(json|txt|text)$/i ) {
                           return("Error: Invalid file extention only: .txt, .text and .json are supported - $sExtension",$sResults);
                        } 
                      
                        my $oUploadFilehandle              = $cgi->upload("file");
                        open ( UPLOADFILE, ">$sUploadDir/$sFilename" ) or die "$!";
                        binmode UPLOADFILE;
                        while ( <$oUploadFilehandle> ) { 
                              print UPLOADFILE;
                        } 
                        close UPLOADFILE;
                        my $sJsonFIle="$sUploadDir/$sFilename";
                      
                        # from file content
                        local $/;
                        open( my $fh, '<', "$sUploadDir/$sFilename" );
                        my $json_text   = <$fh>;
                        my $roPasedJson = JSON->new->utf8(1)->decode($json_text);
                      
                        #Get fields/keys in collection
                        my $raFields = findKeysInJsonColl($roPasedJson);
                        my @aFields  = @$raFields;
                        @aOutFields = @aFields  if ( @aOutFields eq 0 );
                      
                        if ($sFields) {
                              $sPageFields = " VALUE=\"$sFields\"";
                        }
                        else {
                              $sPageFields = " VALUE=\"".join('~~',@aFields)."\"";
                        }
                      
                        if ( $cgi->param('preview') =~ /true/ ){
                              $sResults .= convertToDojoGrid("", $roPasedJson, \@aOutFields, $iPreviewRows);
                        } else {
                              $sFilename                        = "$sName.$sOutFormat";
                              my $rows                          = convertToExcel("$sUploadDir/$sFilename", "", $roPasedJson, \@aOutFields, $sOutFormat);
                              $sMsg                             = "Rows/Docs written to Excel: $rows";
                              
                              open(DLFILE, "<$sUploadDir/$sFilename") || Error('open', 'file');
                      
                              print $cgi->header(
                                      -type            => 'application/x-download',
                                      -Content_length  => -s "$sUploadDir/$sFilename",
                                      -attachment      => $sFilename,
                              );
                      
                              binmode DLFILE;
                              print while <DLFILE>;
                              close (DLFILE) || Error ('close', 'file');
                              unlink("$sUploadDir/$sFilename");        
                        }
                      
                        unlink("$sJsonFIle");
                      
                        return ($sMsg, $sResults, $sPageFields);
                      }
                      
                      # --------------------------------------------
                      sub build_form($$$$){
                      
                        my ($cgi, $sStatusMsg, $sResults, $sShowFields) = @_;
                        my ($sStyle, $sMenu, $sHeader, $sFooter, $sSrvSelector) = get_page_head_and_tail('json2Excel.cgi');
                        my $sPage = "";
                      
                        my ($sPreview, $sFilename);
                        my $iPreviewRows=0;
                        if($cgi->param('preview')){
                              $sPreview = "checked=checked" if($cgi->param('preview') =~ /true/);
                        }
                        if($cgi->param('previewRows')){
                              $iPreviewRows = $iPreviewRows + ($cgi->param('previewRows') =~ /^([0-9]+)$/)[0];
                        }
                        if ($cgi->param('file')){
                              $sFilename = "<script type=\"text/javascript\" defer>document.getElementById('file').setAttribute('value','".$cgi->param('file')."');</script>";
                        }
                      
                        #$sStatusMsg =~ s/[\n\r\"]/\\n/gm;   #Escape any CR's / LN's that will upset the JavaScript;
                             
                        $sPage.=<<END_HTML;
                        <div class="wrapper">
                      $sHeader
                      $sMenu
                          <div class="clearer"></div>
                            <div class="content" style="width: auto;">
                              <div class="spacer"></div>
                                <h1>Convert a file containing an anonymous JSON array into a Excel (xls) workbook - example 1</h1>
                              <div class="spacer"></div>
                      <FORM ENCTYPE="multipart/form-data" METHOD="POST">
                      <p> Please select a file to upload (Expects files of type: .text, .txt, .js, or .json extensions will work, depending on OS / mime types defined locally): <br />
                      <INPUT TYPE="FILE" NAME="file" ID="file" ACCEPT='text/plain,text/javascript,application/json,application/javascript' >
                      </p>
                      <p> <strong>Optional:</strong>&nbsp;Please specify the <strong>fields</strong> to appear in the output (As a list and '~~' separated e.g. name~~age~~address.0.street): <br /> 
                      <INPUT TYPE="text" SIZE=80 NAME="fields" $sShowFields> 
                      </p>
                      <p> <input type="checkbox" name="preview" value="true" $sPreview/> Preview in Browser - max rows (0 = all): <INPUT TYPE="text" NAME="previewRows" value="$iPreviewRows" size=6><br />
                      </p>
                      <p>Output Format: <select name="outFormat">
                      <option selected="" value="xls">xls</option>
                      <option value="xlsx">xlsx</option>
                      </select>
                      </p>
                      <p> <INPUT TYPE="submit"> </p>
                      </FORM>
                       $sFilename
                      END_HTML
                        $sPage.="<script language=\"JavaScript\">\n<!--\nalert(\"$sStatusMsg\");\n//-->\n</script>\n" if($sStatusMsg);
                        $sPage.=$sResults;
                        $sPage.=<<END_HTML;
                      <hr />
                      <h2>Info</h2>
                      <p>This toy expects a file containing a set of JSON documents in an anonymous ARRAY, rather than a single document (row).<br /> So please ensure you wrap your JSON document(s) in square brackets. If parsing multiple documents simply comma separate them within the square brackets e.g.</p>
                      <p><strong style="color: red;">[</strong><br />
                      {"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}<strong style="color: red;">,</strong><br />
                      {"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}<strong style="color: red;">,</strong><br />
                      {"xxxx":123,"yyyyy":"mooooo","tstArray":[123,456,789],"last":"The End"}<br />
                      <strong style="color: red;">]</strong></p>
                      <p><strong>Note:</strong>&nbsp; Haven't paid much attention to detecting / handling non Latin encodings</p>
                            </div>
                          </div>
                          <div class="clearer"></div>
                      $sFooter
                        </div>
                      END_HTML
                      
                        print $cgi->header(),
                              $cgi->start_html(
                                              -title=>'Convert a file containing an anonymous JSON array into a Excel (xls) workbook - example 1',
                                              -style=>[
                                                              {-src=>'http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/dijit/themes/claro/claro.css'},
                                                              {-src=>'http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/dojox/grid/resources/Grid.css'},
                                                              {-src=>'http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/dojox/grid/resources/claroGrid.css'}
                                                      ],
                                              -class=>'claro'
                                              ),
                              $sPage,
                              $cgi->end_html;
                      
                      } 
                      # --------------------------------------------

Open in new window

Hopefully fairly self explanatory, but note:
 

1.

The 5MB upload limit ($CGI::POST_MAX) at the top. Feel free to up or remove, as an XLS sheet can handle 65,536 documents (rows) while an XLSX format sheet can handle 2 million. The above limit is primarily a hangover from the script being stuck on a Development server, that I'd prefer not be used for bulk data processing.

2.

The $sUploadDir variable specified the directory where the JSON files are uploaded to, and the resulting workbooks are written to, per the comment alter to suit.

3.

I've externalised the Dojo includes (http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/), if you want to play it may be an idea to grab your own copy, expand, and host locally.

The UI or "make it pretty" Library, as mentioned above is essentially empty for this example, so simply cut and paste and create a dummy style sheet for now e.g.
touch /path/to/web-server-root/style/ARstyle.css

The lib: ARUiLib
#
                      # Purpose: Assorted UI functions
                      #
                      # ---------------------------------------------------------------
                      # $Id:
                      # ---------------------------------------------------------------
                      # $Log:
                      #
                      # To-Do:  
                      #
                      
                      use strict;
                      use File::Basename;
                      
                      my $arUiLib = 1;
                      
                      # --------------------------------------------
                      sub get_page_head_and_tail($){
                      
                        my ($sStyle, $sMenu, $sHeader, $sFooter, $sSrvSelector);
                      
                      $sStyle=<<EOS;
                      <link href="/style/ARstyle.css" type="text/css" rel="stylesheet"/>
                      EOS
                      
                       $sMenu=<<EOS;
                          <div class="menu">
                            <div class="clearer"></div>
                          </div>
                      EOS
                      
                       $sHeader=<<EOS;
                          <div class="header-wrapper">
                            <div class="header-content" style="width: 98%;">
                              <div class="pageName">JSON to Excel</div>
                              <div class="clearer"></div>
                            </div>
                          </div>
                      EOS
                      
                      $sFooter=<<EOS;
                          <div class="footer-wrapper">
                              <div class="footer-content">
                                      <div>JSON to Excel - Style to your own taste</div>
                              </div>
                          </div>
                      EOS
                      
                        return ($sStyle, $sMenu, $sHeader, $sFooter, $sSrvSelector);
                      }

Open in new window

Next the JSON library:  ARJsonLib.pm
#
                      # Purpose: Assorted JSON / BSON functions
                      #
                      # ---------------------------------------------------------------
                      # $Id:
                      # ---------------------------------------------------------------
                      # $Log:
                      #
                      # To-Do:  Chunk file reading / processing,
                      #         Add some proper exception logic
                      #         Tidy Code
                      
                      use strict;
                      use Spreadsheet::WriteExcel;
                      use Excel::Writer::XLSX;
                      use Tie::IxHash;
                      use Scalar::Util 'reftype';
                      use JSON;
                      use IO::File;
                      
                      my $arjsonLib = 1;
                      
                      sub getKeysInHash($$);
                      sub getKeysInCollection($$);
                      sub getCollKeys($$);
                      sub getValue;
                      
                      # --------------------------------------------
                      sub getKeysInHash($$){
                        my ($rJsonHash, $sPrefix) = @_;
                        my (@aKeys, $refType, $raKeys);
                      
                        while( my ($k, $v) = each %$rJsonHash ) {
                                      $refType = ref($rJsonHash->{$k});
                                      if ( $refType && $refType eq 'DateTime' ) {
                                              push (@aKeys, "$sPrefix$k");
                                      }
                                      elsif ( $refType && $refType eq 'HASH' ) {
                                              $raKeys = getKeysInHash($v, "$sPrefix$k.");     
                                              push (@aKeys, @$raKeys);
                                      }
                                      elsif ( $refType && $refType eq 'ARRAY' ) {
                                              $raKeys = getKeysInCollection($v, "$sPrefix$k.");
                                              push (@aKeys, @$raKeys);
                                      }
                                      else {
                                              push (@aKeys, "$sPrefix$k")     if (isNotARef($rJsonHash->{$k}) );
                                      }
                        } # End key / value while
                        return \@aKeys;
                      }
                      
                      # --------------------------------------------
                      sub getKeysInCollection($$){
                        my ($rJsonCollection, $sPrefix) = @_;
                        my (@aKeys, $raKeys, $rhDoc, $refType, $iCnt);
                      
                      
                        if ($rJsonCollection) {
                              $refType = reftype($rJsonCollection);
                              if ( $refType && $refType eq 'HASH' ) {
                                      $raKeys = getKeysInHash($rJsonCollection, $sPrefix); 
                                      push (@aKeys, @$raKeys); 
                              } # End HASH REF logic
                              elsif ( $refType && $refType eq 'ARRAY' ) {
                                      $iCnt=0;
                                      foreach $rhDoc ( @{$rJsonCollection} ) {
                                              if ($sPrefix) { 
                                                      $raKeys = getKeysInCollection($rhDoc, "$sPrefix$iCnt.");
                                                      if ( (@$raKeys) ) {
                                                              push (@aKeys, @$raKeys); 
                                                      } else {
                                                              push (@aKeys, "$sPrefix$iCnt");
                                                      }
                                              }
                                              else {
                                                      $raKeys = getKeysInCollection($rhDoc, "");
                                                      push (@aKeys, @$raKeys); 
                                              }
                                      $iCnt++;
                                      } #End Foreach item (ARRAY object)
                              } # End ARRAY REF logic
                        }
                        return \@aKeys;
                      }
                      # --------------------------------------------
                      sub findKeysInJsonColl($){
                        my ($rJsonCollection) = @_;
                        my (@aKeys, $raFields,  $sKey, %hKeys);
                      
                        #Get ALL keys in collection
                        $raFields = getKeysInCollection($rJsonCollection, "");
                      
                        #Eliminate duplicates
                        foreach $sKey ( @{$raFields} ) {
                              $hKeys{$sKey} = 1;
                        }
                        while( my ($k, $v) = each %hKeys ) {
                              push(@aKeys, $k);
                        }
                        @aKeys = sort @aKeys; 
                        return \@aKeys;
                      }
                      # --------------------------------------------
                      sub convertToExcel($$$$$){
                        my ($sFilename, $rhMongoCursor, $oJsonData, $raOutFields, $sFileFormat)=@_;
                        my ($iRow,$iCol,$rhDoc,$oTitleFormat,$sKey,$workbook,$sVal,$oFormat);
                      
                        # Create a new workbook and add a worksheet.
                        $sFileFormat = 'xls' if ( ! $sFileFormat );
                        if ( $sFileFormat =~ /^xlsx$/i ){
                              $workbook  = Excel::Writer::XLSX->new("$sFilename") || die ("Couldn't create: $sFilename");
                        } 
                        elsif ( $sFileFormat =~ /^xls$/i ){
                              $workbook  = Spreadsheet::WriteExcel->new("$sFilename") || die ("Couldn't create: $sFilename");
                        }
                        else {
                              die ("Error: Unknown file format");
                        }
                        my $worksheet = $workbook->add_worksheet();
                      
                        # Define a few commonly used pre-set Formats /Styles that can be applied by NAME
                        # Title format
                        $oTitleFormat = $workbook->add_format(
                                                              bg_color => 22,   
                                                              color    => 8,   
                                                              pattern  => 1,
                                                              border   => 1,
                                                              bold     => 1
                                                            );
                        #Add any additional columns
                        $iCol=0;
                        $iRow=0;
                        foreach $sKey ( @{$raOutFields} ) {
                         $worksheet->write($iRow, $iCol++, $sKey, $oTitleFormat);
                        }
                        #Freeze the first Row
                        $worksheet->freeze_panes(1, 0);
                      
                        #Write any passed JSON Data 
                        if ($oJsonData){ 
                          foreach $rhDoc ( @{$oJsonData} ) {
                              #Add any additional columns, untangling the nesting in the field names (Frigged, should write something recursive)
                              $iRow++;
                              $iCol=0;
                              foreach $sKey ( @{$raOutFields} ) {
                                      $sVal = getValue($sKey, $rhDoc, 'true');
                                      if ( ref($sVal) eq 'HASH' ) {
                                              $oFormat = undef;
                                              if ( $sVal->{ 'format' } ) {
                                                      if ( ref($sVal->{ 'format' }) eq 'HASH') {
                                                              $oFormat = $workbook->add_format($sVal->{ 'format' });
                                                      } 
                                              }
                                              $worksheet->write($iRow, $iCol, $sVal->{ 'value' }, $oFormat );
                                      } else {
                                              $worksheet->write($iRow, $iCol, $sVal);   
                                      }
                                      $iCol++;
                              } # End - foreach $sKey
                          } #End Foreach row (JSON object)
                        }
                      } # End Sub
                      
                      # --------------------------------------------
                      sub convertToDojoGrid($$$$){
                        my ($rhMongoCursor, $oJsonData, $raOutFields, $iPreviewRows)=@_;
                      
                        my ($iRow,$iCol,$rhDoc,$titleFormat,$sKey,$sPage,$sData,$sVal);
                        $sPage="<h2>Preview</h2>\n";
                      
                        #Add any additional columns
                        $iCol=0;
                        $iRow=0;
                        $sData="";
                        foreach $sKey ( @{$raOutFields} ) {
                          $sData.="<th field=\"$sKey\" width=\"auto\">$sKey</th>\n";
                        }
                        $sPage.=<<END_HTML;
                          <div style="width: 90%; height: 200px">
                              <table id="jsonData" dojoType="dojox.grid.DataGrid">
                                  <thead>
                                      <tr>
                      END_HTML
                        $sPage.=$sData;
                        $sPage.=<<END_HTML;
                                      </tr>
                                  </thead>
                              </table>
                          </div>
                      <script type="text/javascript"
                       src="http://ajax.googleapis.com/ajax/libs/dojo/1.5/dojo/dojo.xd.js"
                          djConfig="parseOnLoad:true"></script>
                      
                      <script type="text/javascript">
                          dojo.require("dojox.grid.DataGrid");
                          dojo.require("dojo.data.ItemFileReadStore");
                      </script>
                      
                      <script type="text/javascript">
                      dojo.ready(function() {
                      END_HTML
                      
                        $sData="var oDataForGrid = {\nitems: [";
                      
                        #Read and write any data from the passed Json object
                        if ($oJsonData){
                          foreach $rhDoc ( @{$oJsonData} ) {
                              #Add any additional columns
                              $sData.=",\n"                                               if($iRow>0);
                              $iRow++;
                              $iCol=0;
                              $sData.="\t{\n";
                              foreach $sKey ( @{$raOutFields} ) {
                                      $sData.=",\n"                                           if($iCol>0);
                                      $sVal = (getValue($sKey, $rhDoc));
                                      $sVal =~ s/\x22/\\\"/g;
                                      $sData.="\t\"$sKey\":\"".$sVal."\"";
                                      $iCol++;
                              } # End - foreach $sKey
                              $sData.="\n\t}";
                              last if( $iPreviewRows && $iPreviewRows eq $iRow); 
                          } #End Foreach row (JSON object)
                        }
                      
                        #Read and write any data from the passed cursor
                        if ($rhMongoCursor){
                          while ($rhDoc = $rhMongoCursor->next) {
                              #Add any additional columns
                              $sData.=",\n"                                               if($iRow>0);
                              $iRow++;
                              $iCol=0;
                              $sData.="\t{\n";
                              foreach $sKey ( @{$raOutFields} ) {
                                      $sData.=",\n"                                           if($iCol>0);
                                      $sVal = (getValue($sKey, $rhDoc));
                                      $sVal =~ s/\x22/\\\"/g;
                                      $sData.="\t\"$sKey\":\"".$sVal."\"";
                                      $iCol++;
                              } # End - foreach $sKey
                              $sData.="\n\t}";
                              last if( $iPreviewRows && $iPreviewRows eq $iRow);
                          } #End Foreach row (JSON object)
                        }
                      
                        $sData.="\n]\n};\n";
                        $sPage.=$sData;
                        $sPage.=<<END_HTML;
                        var dataStore =
                          new dojo.data.ItemFileReadStore(
                              { data:oDataForGrid }
                          );
                          var grid = dijit.byId("jsonData");
                          grid.setStore(dataStore);
                      });
                      </script>
                      END_HTML
                        return $sPage;
                      } ## End sub
                      
                      # --------------------------------------------
                      sub getValue{
                        my ($sKey, $rhDoc,$bReturnRef)=@_;
                        my ($sKeyPt1,$sKeyPt2,$sKeyPt3,$refType);
                        $refType="";
                      
                        if ( ($sKey =~ /^[^.]*[.]/) ){
                                      ($sKeyPt1, $sKeyPt2) = ($sKey =~  /^([^.]+)\.(.+)$/ );
                                      $refType = reftype($rhDoc->{$sKeyPt1});
                                      if ( $refType && $refType eq 'HASH' ) {
                                              return getValue($sKeyPt2, $rhDoc->{$sKeyPt1});
                                      }
                                      elsif ( $refType && $refType eq 'ARRAY' ) {
                                              if ( ($sKeyPt2 =~ /^[0-9]+$/) ){
                                                      return ($rhDoc->{$sKeyPt1}[$sKeyPt2])           if ( isNotARef($rhDoc->{$sKeyPt1}[$sKeyPt2]));
                                              } else {
                                                      if ( ($sKeyPt2 =~ /^[0-9]+[.]/) ){
                                                              ($sKeyPt2, $sKeyPt3) = ($sKeyPt2 =~  /^([^.]+)\.(.+)$/ );
                                                              return getValue($sKeyPt3, $rhDoc->{$sKeyPt1}[$sKeyPt2]);
                                                      } else {  #Default to first element of ARRAY, if no index given
                                                              return getValue($sKeyPt2, $rhDoc->{$sKeyPt1}[0]);
                                                      }
                                              }
                                      }
                        } else {
                              if ( ref($rhDoc->{$sKey}) eq "DateTime") {
                                      my $dt = $rhDoc->{$sKey};
                                      $dt->set_time_zone('UTC');
                                      return ($dt->datetime);
                              }
                              return  $rhDoc->{$sKey}                                                              if ( $bReturnRef || isNotARef($rhDoc->{$sKey}));
                        }
                        return ("");
                      } ## End sub
                      
                      # --------------------------------------------
                      sub isNotARef($){
                        my ($refToTest)=@_;
                      
                              if( ! ref($refToTest) || ref($refToTest) eq 'boolean'){
                                      return 1;
                              } else {
                                      return 0;
                              }
                      }
                      # --------------------------------------------

Open in new window


If all goes well you should see a page along the following lines:Previewing the example JSON document
0
8,244 Views
arober11
CERTIFIED EXPERT

Comments (1)

CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
Possibly also of interest:

Java: Json to Excel
Python: StackOverflow thread
javaScript: jsFiddle - JSON to CSV Converter
Web based:  Json 2 HTML

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.