Perl之Spreadsheet::WriteExcel

一般导出excel功能是出现在后台管理系统中，运营人员为了便于统计，经常需要将大量数据导出。

本文主要描述如何将mongodb查询出来的数据写入excel表格。

 

一、安装相关模块：

1、MongoDB  -> 操作数据库

接口文档https://metacpan.org/pod/distribution/MongoDB/lib/MongoDB/Tutorial.pod

 

2、Spreadsheet::WriteExcel  -> 操作excel

接口文档https://metacpan.org/pod/Spreadsheet::WriteExcel

 

3、Try::Tiny  -> try-catch功能

4、Safe::Isa  -> try-catch功能

 

安装方法：

sudo perl -MCPAN -e 'install MongoDB'

sudo perl -MCPAN -e 'install Spreadsheet::WriteExcel'

 

或者 

perl -MCPAN -e shell

cpan[1]> install MongoDB

 

或者

下载源码包MongoDB-v1.4.5.tar.gz

解压tar xvf MongoDB-v1.4.5.tar.gz， 

cd MongoDB-v1.4.5/

perl Makefile.PL

make

make install

 

二、本文方案

web前端将运营者的输入条件POST给服务端的CGI脚本，CGI脚本解析请求后，根据规则查表，写入excel，同时将excel文件放到可下载的目录，完成后返回下载链接给web前端。

 

三、代码片段

对于一个后台管理系统，可能会在很多地方需要用到导出excel功能，而且运营者通常需要加入查询/过滤条件，同时只要求返回特定的字段。所以，本文尽量写个通用的版本，以供参考。

#excel.pl  - CGI脚本

use warnings;

use utf8;

binmode(STDIN, ':encoding(utf8)');

binmode(STDOUT, ':encoding(utf8)');

binmode(STDERR, ':encoding(utf8)');

==》 以上解决中文问题（要求数据库表里的内容也是utf8格式）

 

use CGI;

use JSON;    #解析web前端的post请求

use Encode;  #查询条件可能带中文，需要utf8编码

use MongoDB;

use Try::Tiny;

use Safe::Isa;

use Spreadsheet::WriteExcel;

use Time::HiRes qw(gettimeofday usleep);

use Data::Dumper;

 

my $OUTPUT = '{"file_path":"", "status":"failed"}';  #返回给web前端的结果

my $DOWNLOAD_ROOT = "/tmp/download"; #excel文件所在目录

my $DB_NAME = "TEST";

my $DB_OPTIONS = {

username => "db",

        password => "123"

};

my $DB_HOSTS = "mongodb://localhost";  # 默认mongodb装在本机，端口号27017， 也可以是ip加端口号"mongodb://116.5.100.152:27000"， 具体参考MongoDB的接口文档

 

$ROW_COLUM_SET = { #表格设置

abc_list => { #对应后台的某个功能

collection => "abc", #对应表名称

search => { #查询条件

title => {db_key => "title", regex => "true"}, #db_key对应数据库字段, format是否正则匹配方式

day => {db_key => "start_time", regex => "false"},

default_query => {tag=> "myself"}  #默认的查询条件

},

row_colum => { #表头设置

"ID" => {db_key => "_id", format => "string"}, #db_key对应数据库字段, format字段数据类型

"标题" => {db_key => "title", format => "string"},

"图片" => {db_key => "image_fid", format => "string"},

"链接" => {db_key => "url", format => "string"},

"时间" => {db_key => "start_time", format => "time"}

}

},

 

xyz_list => { #对应后台的某个功能

collection => "xyz", #对应表名称

search => { #查询条件

person_account => {db_key => "person_account", regex => "true"}, 

status => {db_key => "exchge_status", regex => "false"}

},

row_colum => {

"记录ID" => {db_key => "_id", format => "string"}

"日期" => {db_key => "et", format => "time"},

"用户号码" => {db_key => "person_account", format => "string"}

"状态" => {db_key => "status", format => "string"}

},

special_func => \&xyz_list #需要调用特定函数处理（函数指针方式）

}

 

};

 

###CGI处理POST请求####

# web前端post过来的数据： {"methond":"abc_list", "search_condition":{"title":"历史"}}

if ($cgi->param("POSTDATA")) {

my $post_data = $cgi->param("POSTDATA");

my $input_json = $json->decode($post_data);

my $methond = $input_json->{methond};

if (length($methond) ) {

if (!$ROW_COLUM_SET->{$methond}) {

write_log("input.log", "Not implement: $methond");

print_result($OUTPUT);

}

#连接数据库

my $client;

my $db;

try {

$client = MongoDB->connect($DB_HOSTS, $DB_OPTIONS);

} catch {

if ( $_->$_isa("MongoDB::AuthError" ) ) {

write_log("db.log", "MongoDB::AuthError");

} elsif ( $_->$_isa("MongoDB::ConnectionError" ) ) {

write_log("db.log", "MongoDB::ConnectionError");

}

print_result($OUTPUT);

};

 

try {

$db = $client->get_database($DB_NAME );

} catch {

if ( $_->$_isa("MongoDB::DatabaseError" ) ) {

write_log("db.log", "MongoDB::DatabaseError");

}

print_result($OUTPUT);

};

 

#获取数据

my $collection = $ROW_COLUM_SET->{$methond}->{collection};

my $search = $ROW_COLUM_SET->{$methond}->{search};

 

write_log("db.log", "Set Query hash of $collection");

my $queryHash;

foreach my $cod (keys %{$search}) {

if ($cod eq "default_query") { #默认查询条件赋值

foreach my $k (keys %{ $search->{$cod} }) {

$queryHash->{$k} = $search->{$cod}->{$k};

}

} elsif (length($input_json->{search_condition}->{$cod})) { #前端输入的查询条件

#处理中文

my $value = Encode::decode("utf-8", $input_json->{search_condition}->{$cod}); 

my $db_key = $search->{$cod}->{db_key};

$queryHash->{$db_key} = $value;

if ($search->{$cod}->{db_key} eq "true") { #需要正则匹配

$queryHash->{$db_key} = {'$regex'=> $value};

}

}

}

my @result = ();  #数据放入数组中

               #是否需要调用特定函数来查询， 利用了函数指针

my $func_ref = $ROW_COLUM_SET->{$methond}->{special_func};

if ((ref($func_ref) eq "CODE") && defined(&{$func_ref}) ) {

@result = &{$func_ref}($db, $queryHash); #调用特定函数查询

} else {

my $mocl = $db->get_collection($collection)->find($queryHash);

@result = $mocl->all();

}

 

#填充表格

               my $FILE_ID = time().int(rand(10000)).".xls";  #生成一个随机名字

my $xls_name = "$DOWNLOAD_ROOT/$FILE_ID";

 

my $workbook = Spreadsheet::WriteExcel->new($xls_name);

$worksheet = $workbook->add_worksheet();

 

write_log("db.log", "---Start write---\n");

#写标题

my $row_colum = $ROW_COLUM_SET->{$methond}->{row_colum};

my $colum_id = 0;

foreach my $k (keys %{ $row_colum }) {

$worksheet->write(0, $colum_id, $k); #写入第一行

$colum_id++; #列递增

}

 

write_log("db.log", "Finish wirte title\n");

#写内容

my $row_id = 1; #从第二行开始

foreach my $obj (@result) {

next if(!$obj);

$colum_id = 0;

foreach my $k (keys %{ $row_colum }) {

my $db_key = $row_colum->{$k}->{db_key};

my $content = $obj->{$db_key};

if (length($content) == 0) {

$content = "NULL";

} elsif ($row_colum->{$k}->{format} eq  "time") { #数据类型处理

$content = formateTime($content);

} elsif ($row_colum->{$k}->{format} eq  "float") {#数据类型处理

$content = formateFloat($content);

}

$worksheet->write($row_id, $colum_id, $content);

$colum_id++;

}

$row_id++;

}

 

$workbook->close();

my $file_path = "http://xxx/$FILE_ID";

$OUTPUT = '{"file_path":"'.$file_path.'", "status":"success"}'; #输出结果

print_result($OUTPUT);

}

}

 

exit;

 

#########################相关子函数#####################

sub print_result {

my $output = $_[0];

print "Access-Control-Allow-Origin: *\r\n";

print "Content-Type: application/json\r\n\r\n";

print $output;

exit;

}

 

sub formateTime { 

my $time1 = $_[0] + 0;

my ($sec,$min,$hour,$day,$month,$year,$wday,$yday,$isdst) = localtime($time1);

$year = $year + 1900;

$month = $month + 1;

return "$year/$month/$day $hour:$min:$sec";

}

 

sub formateFloat { 

my $float = $_[0];

 

$float = sprintf("%.2f", $float);

return $float;

}

 

sub xyz_list {

my ($db, $query_hash) = @_;

 

my @result_list = ();

....

return @result_list;

}