ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Perl Catalyst 倒出 Excel

Perl Catalyst 倒出 Excel

原创 Linux操作系统 作者:bulletming 时间:2019-03-14 16:27:05 0 删除 编辑
今天做一个Server端的自动生成Excel的功能

用了
Catalyst::Action::Serialize::SimpleExcel 这个模块,挺好的,用了她,我就不需要使用繁琐的Spreadsheet::WriteExcel了。
但是搞了半天正常之后,又出了乱码问题。

开头看WriteExcel文档以为是binmode,其实根本没有关系,MD

本来在Perl5.8以上,WriteExcel(worksheet里边的write_string)会自动把utf8转成utf16le的,可惜哦,SimpleExcel的例子代码里边用了DBIx::Class::ResultClass::HashRefInflator,记忆中用了这个DBIx::Class的强制utf8转换会失效的。后来我去掉这个,确实是就OK了。MMMD

摘录代码如下:
package CBS::Controller::XLS;

use parent 'Catalyst::Controller::REST';
use DBIx::Class::ResultClass::HashRefInflator ();
use POSIX 'strftime';
#use Data::Dumper;
use JSON;

__PACKAGE__->config->{map}{'application/vnd.ms-excel'} = 'SimpleExcel';

sub obj : Local ActionClass('REST') {}

sub obj_GET {
    my ($self, $c) = @_;
    $obj_name=$c->req->param('obj_name');
    $data=from_json($c->req->param('json'));

    $rs = $c->model("CBSDB::$obj_name")->search($data->{filter}, $data->{attrs});
    my @t = map {
        my %t_h = $_->get_columns();
        my @cols = @{$data->{attrs}->{columns}};
        [ @{%t_h}{@cols} ]
    } $rs->all;

    my $entity = {
        header => $data->{attrs}->{columns}, # will be bold
        rows => \@t,
# the part before .xls, which is automatically appended
        filename => "cbs-$obj_name-".strftime('%Y%m%d%H%M%S', localtime)
    };

    $self->status_ok(
        $c,
        entity => $entity
    );
}

1;

Excel太变态了,用utf816le这个鬼东西。我日他大爷。
不过今天也蛮爽的,跟以前的自己的CRUD基本结合在一起增加了一个通用的Excel倒出功能。
下边这片文章还是不错的。

===============================================================

利用Perl把数据库数据导出到Excel文件


from:http://blog.csdn.net/masson32/archive/2009/04/17/4086906.aspx

 
300) { text = text + "\r\n\n本文来自CSDN博客,转载请标明出处:" + location.href; clipboardData.setData("text", text); } }, 100); } }     最近,由于项目需要,需要利用PerlMYSQL数据库中的某些表的数据输出到Excel文件中方便打印,备份保存和数据移动。(由于之前未用过Perl,所以学了一下)。

    需求描述:使用Perl从数据库中取出数据,把数据输出到Excel中。

    问题解决方案:

1、利用Spreadsheet::WriteExcel实现对Excel文件的写入工作。

2、解决向Excel写入中文时产生乱码的问题

3、利用DBI对查询数据库,取出符合条件的数据。

4、解决从MYSQL数据库查询数据库返回结果的中文产生乱码问题

(说明:24两个问题是在开发过程中遇到的。)

 

第一步:利用Spreadsheet::WriteExcel实现对Excel文件的写入工作。

    上网查,在使用Perl的情况下,很多人都会说利用Spreadsheet::WriteExcel来实现对对Excel文件的写入功能。利用它的几个函数,就可以方便地把数据写入到Excel相应的位置中,同时还可以设置单元格的格式,如字体大小,单元格大小,是否加粗,底色等等。

下面代码实现一个简单的对Excel的写入工作:

  1. #!/usr/bin/perl  
  2. use Spreadsheet::WriteExcel;  
  3. #************生成Excel文档****************  
  4. my $xl = Spreadsheet::WriteExcel->new("TEST.xls");  
  5. #生成Excel表  
  6. my $xlsheet = $xl->add_worksheet(“TestSheet”);  
  7. #添加格式(表头)  
  8. $rptheader = $xl->add_format(); # Add a format  
  9. $rptheader->set_bold();  
  10. $rptheader->set_size('12');  
  11. $rptheader->set_align('center');  
  12. #添加格式(表内容)  
  13. $normcell = $xl->add_format(); # Add a format  
  14. $normcell->set_size('9');  
  15. $normcell->set_align('center');  
  16. $normcell->set_bg_color('22');  
  17. #设置列的宽度  
  18. $xlsheet->set_column('A:A',10);  
  19. $xlsheet->set_column('B:B',12);  
  20. $xlsheet->set_column('C:C',17);  
  21. #写表头(格式是使用上面添加的表头格式)  
  22. $xlsheet->write("A2","Number"$rptheader);  
  23. $xlsheet->write("B2","Name",$rptheader);  
  24. $xlsheet->write("C2","Language",$rptheader);  
  25. #写内容(格式是使用上面添加的表内容格式)  
  26. $xlsheet->write("A3","1"$normcell);  
  27. $xlsheet->write("B3","Test",$normcell);  
  28. $xlsheet->write("C3","Perl",$normcell);  
  29. #关闭操作excel的对象.  
  30. $xl->close();  
#!/usr/bin/perl use Spreadsheet::WriteExcel; #************生成Excel文档**************** my $xl = Spreadsheet::WriteExcel->new("TEST.xls"); #生成Excel表 my $xlsheet = $xl->add_worksheet(“TestSheet”); #添加格式(表头) $rptheader = $xl->add_format(); # Add a format $rptheader->set_bold(); $rptheader->set_size('12'); $rptheader->set_align('center'); #添加格式(表内容) $normcell = $xl->add_format(); # Add a format $normcell->set_size('9'); $normcell->set_align('center'); $normcell->set_bg_color('22'); #设置列的宽度 $xlsheet->set_column('A:A',10); $xlsheet->set_column('B:B',12); $xlsheet->set_column('C:C',17); #写表头(格式是使用上面添加的表头格式) $xlsheet->write("A2","Number", $rptheader); $xlsheet->write("B2","Name",$rptheader); $xlsheet->write("C2","Language",$rptheader); #写内容(格式是使用上面添加的表内容格式) $xlsheet->write("A3","1", $normcell); $xlsheet->write("B3","Test",$normcell); $xlsheet->write("C3","Perl",$normcell); #关闭操作excel的对象. $xl->close();

完成。通过上面的代码,可以看出,写入数据到Excel中并不难,主要是用newadd_worksheetadd_formatwrite这几个函数就可以了。

第二步:解决向Excel写入中文时产生乱码的问题

    在第一步中,虽然实现了向Excel写入数据,但只能写入英文数据,如果是写入中文数据,在Excel中会产生中文乱码。同样,表单的名称当插入中文也会有乱码,查了很久,很多人都说用Unicode::Map进行中文写入。但用了它还是解决不了。最后是使用Encode解决了问题。

下面代码实现一个简单的对Excel的写入中文:

  1. #!/usr/bin/perl  
  2. use Spreadsheet::WriteExcel;  
  3. use Encode;#(这里增加Encode)  
  4. #************生成Excel文档****************  
  5. my $xl = Spreadsheet::WriteExcel->new("TEST.xls");  
  6. #生成Excel表  
  7. my $xlsheet = $xl->add_worksheet(decode('utf8' ,“测试写入Excel”));#(中文名称)  
  8. #添加格式(表头)  
  9. $rptheader = $xl->add_format(); # Add a format  
  10. $rptheader->set_bold();  
  11. $rptheader->set_size('12');  
  12. $rptheader->set_align('center');  
  13. #添加格式(表内容)  
  14. $normcell = $xl->add_format(); # Add a format  
  15. $normcell->set_size('9');  
  16. $normcell->set_align('center');  
  17. $normcell->set_bg_color('22');  
  18. #设置列的宽度  
  19. $xlsheet->set_column('A:A',10);  
  20. $xlsheet->set_column('B:B',12);  
  21. $xlsheet->set_column('C:C',17);  
  22. #写表头(格式是使用上面添加的表头格式)(这里是输入中文)  
  23. $xlsheet->write("A2",decode(‘utf8’,"号码"), $rptheader);  
  24. $xlsheet->write("B2", decode(‘utf8’,"名称"),$rptheader);  
  25. $xlsheet->write("C2", decode(‘utf8’,"语言"),$rptheader);  
  26. #写内容(格式是使用上面添加的表内容格式)(这里是输入中文)  
  27. $xlsheet->write("A3", decode(‘utf8’,"1"), $normcell);  
  28. $xlsheet->write("B3", decode(‘utf8’,"测试"),$normcell);  
  29. $xlsheet->write("C3", decode(‘utf8’,"Perl"),$normcell);  
  30. #关闭操作excel的对象.  
  31. $xl->close();  
#!/usr/bin/perl use Spreadsheet::WriteExcel; use Encode;#(这里增加Encode) #************生成Excel文档**************** my $xl = Spreadsheet::WriteExcel->new("TEST.xls"); #生成Excel表 my $xlsheet = $xl->add_worksheet(decode('utf8' ,“测试写入Excel”));#(中文名称) #添加格式(表头) $rptheader = $xl->add_format(); # Add a format $rptheader->set_bold(); $rptheader->set_size('12'); $rptheader->set_align('center'); #添加格式(表内容) $normcell = $xl->add_format(); # Add a format $normcell->set_size('9'); $normcell->set_align('center'); $normcell->set_bg_color('22'); #设置列的宽度 $xlsheet->set_column('A:A',10); $xlsheet->set_column('B:B',12); $xlsheet->set_column('C:C',17); #写表头(格式是使用上面添加的表头格式)(这里是输入中文) $xlsheet->write("A2",decode(‘utf8’,"号码"), $rptheader); $xlsheet->write("B2", decode(‘utf8’,"名称"),$rptheader); $xlsheet->write("C2", decode(‘utf8’,"语言"),$rptheader); #写内容(格式是使用上面添加的表内容格式)(这里是输入中文) $xlsheet->write("A3", decode(‘utf8’,"1"), $normcell); $xlsheet->write("B3", decode(‘utf8’,"测试"),$normcell); $xlsheet->write("C3", decode(‘utf8’,"Perl"),$normcell); #关闭操作excel的对象. $xl->close();

 

第三步:利用DBI对查询数据库,取出符合条件的数据

      可以向Excel写入数据了,接下来就开始进行查询数据库操作。在Perl中进行数据查询,用的DBI。步骤是先连接数据库:$dbhA = DBI->connect;然后准备查询语句$sth = $dbhA->prepare; 执行SQL语句$sth ->execute();用fetchrow_array()取回数据。

  1. #!/usr/bin/perl  
  2. use Spreadsheet::WriteExcel;  
  3. use Encode;#  
  4. use DBI; (这里增加DBI)  
  5. #*****连接数据库:数据库名:db_Test ,IP及端口localhost:3306,用户:user ,密码:111****  
  6. my $dbhA = DBI->connect("DBI:mysql:db_Test:localhost:3306""user""111")  
  7.  or die "Couldn't connect to database: " . DBI->errstr;  
  8. #***准备查询语句  
  9. my $sth = $dbhA->prepare("select * from table1")||die $DBI::errstr;  
  10. #执行查询  
  11. $sth ->execute();  
  12.  
  13. #以下是对Excel操作  
  14. #************生成Excel文档****************  
  15. my $xl = Spreadsheet::WriteExcel->new("TEST.xls");  
  16. #生成Excel表  
  17. my $xlsheet = $xl->add_worksheet(decode('utf8' ,“测试写入Excel”));#(中文名称)  
  18. #添加格式(表头)  
  19. $rptheader = $xl->add_format(); # Add a format  
  20. $rptheader->set_bold();  
  21. $rptheader->set_size('12');  
  22. $rptheader->set_align('center');  
  23. #添加格式(表内容)  
  24. $normcell = $xl->add_format(); # Add a format  
  25. $normcell->set_size('9');  
  26. $normcell->set_align('center');  
  27. $normcell->set_bg_color('22');  
  28. #设置列的宽度  
  29. $xlsheet->set_column('A:A',10);  
  30. $xlsheet->set_column('B:B',12);  
  31. $xlsheet->set_column('C:C',17);  
  32. #写表头(格式是使用上面添加的表头格式)  
  33. $xlsheet->write("A2",decode(‘utf8’,"号码"), $rptheader);  
  34. $xlsheet->write("B2", decode(‘utf8’,"名称"),$rptheader);  
  35. $xlsheet->write("C2", decode(‘utf8’,"语言"),$rptheader);  
  36.  
  37. #循环输出到Excel  
  38. my $num=3;  #excel中的位置  
  39. my $record_num = 0;  #编号  
  40. while(my @row = $query_statement->fetchrow_array())  
  41. {     
  42.     $record_num++;  
  43.     $name = $row[1];  
  44.     $language = $row[2];  
  45.          $xlsheet->write("A$num", decode(‘utf8’,$record_num ), $normcell);  
  46.          $xlsheet->write("B$num", decode(‘utf8’, $name),$normcell);  
  47.          $xlsheet->write("C$num", decode(‘utf8’, $language),$normcell);  
  48.     $num++;  
  49. }  
  50.   
  51. $query_statement->finish();#完成  
  52. $dbhA->disconnect();  #断开数据库连接  
  53. $xl->close();   #关闭操作excel的对象.  
#!/usr/bin/perl use Spreadsheet::WriteExcel; use Encode;# use DBI; (这里增加DBI) #*****连接数据库:数据库名:db_Test ,IP及端口localhost:3306,用户:user ,密码:111**** my $dbhA = DBI->connect("DBI:mysql:db_Test:localhost:3306", "user", "111") or die "Couldn't connect to database: " . DBI->errstr; #***准备查询语句 my $sth = $dbhA->prepare("select * from table1")||die $DBI::errstr; #执行查询 $sth ->execute(); #以下是对Excel操作 #************生成Excel文档**************** my $xl = Spreadsheet::WriteExcel->new("TEST.xls"); #生成Excel表 my $xlsheet = $xl->add_worksheet(decode('utf8' ,“测试写入Excel”));#(中文名称) #添加格式(表头) $rptheader = $xl->add_format(); # Add a format $rptheader->set_bold(); $rptheader->set_size('12'); $rptheader->set_align('center'); #添加格式(表内容) $normcell = $xl->add_format(); # Add a format $normcell->set_size('9'); $normcell->set_align('center'); $normcell->set_bg_color('22'); #设置列的宽度 $xlsheet->set_column('A:A',10); $xlsheet->set_column('B:B',12); $xlsheet->set_column('C:C',17); #写表头(格式是使用上面添加的表头格式) $xlsheet->write("A2",decode(‘utf8’,"号码"), $rptheader); $xlsheet->write("B2", decode(‘utf8’,"名称"),$rptheader); $xlsheet->write("C2", decode(‘utf8’,"语言"),$rptheader); #循环输出到Excel my $num=3; #excel中的位置 my $record_num = 0; #编号 while(my @row = $query_statement->fetchrow_array()) { $record_num++; $name = $row[1]; $language = $row[2]; $xlsheet->write("A$num", decode(‘utf8’,$record_num ), $normcell); $xlsheet->write("B$num", decode(‘utf8’, $name),$normcell); $xlsheet->write("C$num", decode(‘utf8’, $language),$normcell); $num++; } $query_statement->finish();#完成 $dbhA->disconnect(); #断开数据库连接 $xl->close(); #关闭操作excel的对象.

以上代码可以实现从数据库查询数据,并放进Excel中。但,如果数据库中有中文,输出到Excel中还是会有乱码。这就要第四步进行解决。

 

第四步:解决从MYSQL数据库查询数据库返回结果的中文产生乱码问题

要这个问题,就要让返回的结果集支持utf8set character_set_results=utf8 ,代码如下:

  1. #!/usr/bin/perl  
  2. use Spreadsheet::WriteExcel;  
  3. use Encode;#  
  4. use DBI;   
  5. #*****连接数据库:数据库名:db_Test ,IP及端口localhost:3306,用户:user ,密码:111****  
  6. my $dbhA = DBI->connect("DBI:mysql:db_Test:localhost:3306""user""111")  
  7.  or die "Couldn't connect to database: " . DBI->errstr;  
  8. #在结果集中增加中文支持  
  9. my $before = "set character_set_results=utf8";  
  10. my $sth = $dbhA->prepare($before);  
  11. $sth->execute();  
  12.   
  13. #***准备查询语句  
  14. $sth = $dbhA->prepare("select * from table1")||die $DBI::errstr;  
  15. #执行查询  
  16. $sth ->execute();  
  17.   
  18. #以下是对Excel操作  
  19. #************生成Excel文档****************  
  20. my $xl = Spreadsheet::WriteExcel->new("TEST.xls");  
  21. #生成Excel表  
  22. my $xlsheet = $xl->add_worksheet(decode('utf8' ,“测试写入Excel”));#(中文名称)  
  23. #添加格式(表头)  
  24. $rptheader = $xl->add_format(); # Add a format  
  25. $rptheader->set_bold();  
  26. $rptheader->set_size('12');  
  27. $rptheader->set_align('center');  
  28. #添加格式(表内容)  
  29. $normcell = $xl->add_format(); # Add a format  
  30. $normcell->set_size('9');  
  31. $normcell->set_align('center');  
  32. $normcell->set_bg_color('22');  
  33. #设置列的宽度  
  34. $xlsheet->set_column('A:A',10);  
  35. $xlsheet->set_column('B:B',12);  
  36. $xlsheet->set_column('C:C',17);  
  37. #写表头(格式是使用上面添加的表头格式)  
  38. $xlsheet->write("A2",decode(‘utf8’,"号码"), $rptheader);  
  39. $xlsheet->write("B2", decode(‘utf8’,"名称"),$rptheader);  
  40. $xlsheet->write("C2", decode(‘utf8’,"语言"),$rptheader);  
  41.   
  42. #循环输出到Excel  
  43. my $num=3;  #excel中的位置  
  44. my $record_num = 0;  #编号  
  45. while(my @row = $query_statement->fetchrow_array())  
  46. {     
  47.     $record_num++;  
  48.     $name = $row[1];  
  49.     $language = $row[2];  
  50.          $xlsheet->write("A$num", decode(‘utf8’,$record_num ), $normcell);  
  51.          $xlsheet->write("B$num", decode(‘utf8’, $name),$normcell);  
  52.          $xlsheet->write("C$num", decode(‘utf8’, $language),$normcell);  
  53.     $num++;  
  54. }  
  55.   
  56. $query_statement->finish();#完成  
  57. $dbhA->disconnect();  #断开数据库连接  
  58. $xl->close();   #关闭操作excel的对象.  
#!/usr/bin/perl use Spreadsheet::WriteExcel; use Encode;# use DBI; #*****连接数据库:数据库名:db_Test ,IP及端口localhost:3306,用户:user ,密码:111**** my $dbhA = DBI->connect("DBI:mysql:db_Test:localhost:3306", "user", "111") or die "Couldn't connect to database: " . DBI->errstr; #在结果集中增加中文支持 my $before = "set character_set_results=utf8"; my $sth = $dbhA->prepare($before); $sth->execute(); #***准备查询语句 $sth = $dbhA->prepare("select * from table1")||die $DBI::errstr; #执行查询 $sth ->execute(); #以下是对Excel操作 #************生成Excel文档**************** my $xl = Spreadsheet::WriteExcel->new("TEST.xls"); #生成Excel表 my $xlsheet = $xl->add_worksheet(decode('utf8' ,“测试写入Excel”));#(中文名称) #添加格式(表头) $rptheader = $xl->add_format(); # Add a format $rptheader->set_bold(); $rptheader->set_size('12'); $rptheader->set_align('center'); #添加格式(表内容) $normcell = $xl->add_format(); # Add a format $normcell->set_size('9'); $normcell->set_align('center'); $normcell->set_bg_color('22'); #设置列的宽度 $xlsheet->set_column('A:A',10); $xlsheet->set_column('B:B',12); $xlsheet->set_column('C:C',17); #写表头(格式是使用上面添加的表头格式) $xlsheet->write("A2",decode(‘utf8’,"号码"), $rptheader); $xlsheet->write("B2", decode(‘utf8’,"名称"),$rptheader); $xlsheet->write("C2", decode(‘utf8’,"语言"),$rptheader); #循环输出到Excel my $num=3; #excel中的位置 my $record_num = 0; #编号 while(my @row = $query_statement->fetchrow_array()) { $record_num++; $name = $row[1]; $language = $row[2]; $xlsheet->write("A$num", decode(‘utf8’,$record_num ), $normcell); $xlsheet->write("B$num", decode(‘utf8’, $name),$normcell); $xlsheet->write("C$num", decode(‘utf8’, $language),$normcell); $num++; } $query_statement->finish();#完成 $dbhA->disconnect(); #断开数据库连接 $xl->close(); #关闭操作excel的对象.

 

    至此,四个步骤已经完成,也完成了需求中所述内容。可以从数据库中查询数据,并写入到Excel中。同时解决中文乱码问题。

    以上是个人解决这个需求的方法,大家可以参考并一起交流。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/82392/viewspace-611707/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-05-31

  • 博文量
    73
  • 访问量
    50918