perl 写excel，支持中文

最新推荐文章于 2024-06-16 17:46:30 发布

bing_bing

最新推荐文章于 2024-06-16 17:46:30 发布

阅读量2.1k

点赞数 1

分类专栏： perl 文章标签： perl excel 中文

本文链接：https://blog.csdn.net/bing_bing/article/details/42144723

版权

perl 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

e warnings;
use DBI;
use Spreadsheet::WriteExcel;
use Encode; #support Chinese
# connect
my $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 xlsx
		my $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

支持中文