perl json_使用Perl预览网页并将JSON文档转换为Excel工作簿

perl json

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:

在遥远的过去（去年），我凑在一起玩了一个小玩具，它允许几个Manager类型查询，预览并从许多MongoDB实例中提取数据，并将其提取到他们选择的工具中：

Excel. 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.

我最初创建了基于Web的工具，以消除收件箱中出现的“ Can you pull ....”请求，而作为Mac迷，周围没有任何玩具可以让他们轻松获取数据。 轻松地说，要求是要有一个Manager证明点并单击GUI，它可以通过多层嵌套将JSON / BSON文档过滤和展平到Excel工作簿中。 嵌套甚至消除了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.

无论如何，一两天的黑客攻击，同时坐着监视培训课程（解释代码的质量），我有一个粗糙的小玩具（Perl CGI + JS + Dojo Grid），提供了一个相当原始的查询构建器，这将允许它们用于：从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.

曾经有过一段平静的生活，直到需要从几个社交媒体网站中对导出的JSON文件执行类似的操作。 很快就想到了，只是在一个晚上丢掉了80％的代码，然后想到了一个小玩具：上传，预览或将JSON文档转换为Excel工作簿。 当我说JSON文档时，本质上是一个文本文件，其中包含文档的匿名JSON数组，例如

[
{"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"}
]

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):

因此，假设您有一台启用了CGI的Web服务器的PC / Mac /服务器，那么如果没有的话，这里会有大量的教程来介绍基本知识，例如1 2 。 您首先需要安装必要的Perl库，然后输入，最好以root身份输入（可能通过sudo）：

cpan YAML Data::Dumper Spreadsheet::WriteExcel Excel::Writer::XLSX Tie::IxHash Encode Scalar::Util JSON File::Basename IO::File CGI

ftp.perl.org 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).

。 您将花费很多时间思考，然后可能只是选择默认选项，和/或安装任何缺少的二进制文件（如果在Mac上播放，则需要从App Store中购买Xcode，然后从Xcode的首选项面板中安装命令行工具。 ）。

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)

CGI脚本-一个简单的“上载和导出”表单，它提供了一个非常基本的基于Dogo Grid的预览选项，以及一个可选的过滤器（用于限制预览的行/文档的数量）以及一个非常基本的输出列过滤器（用Trivial替换为双重列表框设置）

A UI library for the page - For this example just some empty common Header, Footer text, Style Sheet constants held externally

页面的UI库-在此示例中，仅外部保留了一些空的公共页眉，页脚文本，样式表常量

A JSON function library - To do the main work of parsing and converting the JSON, to the chosen output format

JSON函数库-完成解析并将JSON转换为所选输出格式的主要工作

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

如果您是Perl的新手，请注意.cgi脚本需要放置在Web服务器的cgi-bin目录或具有Exec权限的目录中，并在其上设置了execute属性。 这两个库要么需要与CGI脚本位于同一目录中，要么位于@INC路径中的目录中，请参见以下内容的输出： env -i perl -V

So first the CGI script:  json2Excel.cgi

所以首先是CGI脚本： 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;

} 
# --------------------------------------------

note:

注意 ：

1。 (1. )

顶部的5MB上传限制（$ CGI :: POST_MAX）。 随时可以上载或删除，因为XLS图纸可以处理65,536个文档（行），而XLSX格式图纸可以处理200万个文档。 上面的限制主要是由于脚本滞留在开发服务器上而造成的，我不希望将其用于批量数据处理。

2。 (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.

$ sUploadDir变量根据注释的更改指定了将JSON文件上传到的目录以及将生成的工作簿写入的目录。

3。 (3. )

我已经将Dojo包含的内容（ 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. http://ajax.googleapis.com/ajax/libs/dojo/1.7.3/ ）外部化了，如果您想玩它，可以尝试获取自己的副本，扩展并托管当地。

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.

如上所述，UI或“使它漂亮”库在此示例中基本上是空的，因此现在只需剪切和粘贴并创建一个虚拟样式表即可，例如

touch /path/to/web-server-root/style/ARstyle.css

触摸/ path / to / web-server-root / s tyty / ARsty 勒

The lib: ARUiLib

库： 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);
}

ARJsonLib.pm

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;
        }
}
# --------------------------------------------

If all goes well you should see a page along the following lines:

如果一切顺利，您应该在以下几行中看到一个页面：

翻译自: https://www.experts-exchange.com/articles/10413/Using-Perl-to-preview-in-a-Web-Page-and-convert-a-JSON-document-into-an-Excel-workbook.html

perl json