今天做一個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); } } 最近，由於專案需要，需要利用Perl把MYSQL資料庫中的某些表的資料輸出到Excel檔案中方便列印，備份儲存和資料移動。（由於之前未用過Perl，所以學了一下）。

需求描述：使用Perl從資料庫中取出資料，把資料輸出到Excel中。

問題解決方案：

1、利用Spreadsheet::WriteExcel實現對Excel檔案的寫入工作。

2、解決向Excel寫入中文時產生亂碼的問題

3、利用DBI對查詢資料庫，取出符合條件的資料。

4、解決從MYSQL資料庫查詢資料庫返回結果的中文產生亂碼問題

（說明：2和4兩個問題是在開發過程中遇到的。）

第一步：利用Spreadsheet::WriteExcel實現對Excel檔案的寫入工作。

上網查，在使用Perl的情況下，很多人都會說利用Spreadsheet::WriteExcel來實現對對Excel檔案的寫入功能。利用它的幾個函式，就可以方便地把資料寫入到Excel相應的位置中，同時還可以設定單元格的格式，如字型大小，單元格大小，是否加粗，底色等等。

下面程式碼實現一個簡單的對Excel的寫入工作：

view plain copy to clipboard print ?

#!/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();

#!/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中並不難，主要是用new，add_worksheet，add_format，write這幾個函式就可以了。

第二步：解決向Excel寫入中文時產生亂碼的問題

在第一步中，雖然實現了向Excel寫入資料，但只能寫入英文資料，如果是寫入中文資料，在Excel中會產生中文亂碼。同樣，表單的名稱當插入中文也會有亂碼，查了很久，很多人都說用Unicode::Map進行中文寫入。但用了它還是解決不了。最後是使用Encode解決了問題。

下面程式碼實現一個簡單的對Excel的寫入中文：

view plain copy to clipboard print ?

#!/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();

#!/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()取回資料。

view plain copy to clipboard print ?

#!/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的物件.

#!/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資料庫查詢資料庫返回結果的中文產生亂碼問題

要這個問題，就要讓返回的結果集支援utf8。set character_set_results=utf8 ，程式碼如下：

view plain copy to clipboard print ?

#!/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的物件.

#!/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中。同時解決中文亂碼問題。

以上是個人解決這個需求的方法，大家可以參考並一起交流。

Perl Catalyst 倒出 Excel

利用Perl把資料庫資料匯出到Excel檔案

相關文章