perl 写excel，支持中文

来源：互联网发布：人工智能不会代替人类编辑：程序博客网时间：2024/06/05 20:46

e warnings;use DBI;use Spreadsheet::WriteExcel;use Encode; #support Chinese# connectmy $dbh = DBI->connect("DBI:mysql:database=newbbk;host=127.0.0.1;port=3306", "root", "root", {'RaiseError' => 1,AutoCommit => 0});$dbh->do('set names utf8');$dbh->commit();&fix_school_by_city();$dbh->disconnect();sub fix_school_by_city(){my $statement = "SELECT province_id, province_name from d_province;";my $sth = $dbh->prepare($statement);unless($sth){print "Can't prepare $statement: $dbh->errstr/n";return;}   my $rv = $sth->execute();unless($rv){print "can't execute the query: $sth->errstr";return;}   while(my @row = $sth->fetchrow_array) {my ($pid,$pname) = @row[0..1];next unless($pid =~/^(\d)+$/ and $pname);my $dest_pbook  = Spreadsheet::WriteExcel->new("$pname.xls"); # not support xlsxmy $cstatement = "SELECT city_id, city_name from d_city where province_id=$pid;";my $csth = $dbh->prepare($cstatement);my $crv = $csth->execute();return unless($crv);while(my @crow = $csth->fetchrow_array) {my ($cid,$cname) = @crow[0..1];next unless($cid =~/^(\d)+$/ and $cname);my $worksheet=$dest_pbook->add_worksheet(decode('UTF8',"$cname"));my $sstatement = "SELECT school_id,school_name from d_school where region_id=$cid;";my $ssth = $dbh->prepare($sstatement);my $srv = $ssth->execute();return unless($srv);my $i=1;$worksheet->write(0,0,decode('UTF8','学校id'));$worksheet->write(0,1,decode('UTF8',"学校名称"));while(my @srow = $ssth->fetchrow_array) {my ($sid,$sname) = @srow[0..1];next unless($sid =~/^(\d)+$/ and $sname);$worksheet->write($i,0,decode('UTF8',$sid));$worksheet->write($i,1,decode('UTF8',"$sname"));$i++;}}$dest_pbook->close();}}

说明：将数据库中的学校信息，按省分成excel文件，按市分成worksheet

支持中文

0 0