Perl将Excel数据导入数据库并进行统计分析

##############################################################
#file : parseExceltoDB.pl
#author : Yusheng Jia
#date : 4.22.2012
#version: 1.0
#desc : this script will create a database,
# parse the spreadsheet and export the data into the database,
# and do some analyses based on the data.
##############################################################

##---------------------------------------------------------------
##利用perl DBI创建数据库stucourse,并创建student,course,grade表
##---------------------------------------------------------------
use DBI;
my $db_name = "stucourse"; #数据库名,如果与现有数据库冲突，可改为其他名字
my $db_host = "localhost"; #主机名
my $db_port = '3306'; #端口号
my $username = "root"; #用户名
my $password = "leo"; #密码
my $dsn = "dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#数据源

#获取驱动程序对象句柄
my $drh=DBI->install_driver("mysql");
#如果存在数据库$db_name,则删除之
if($rc = $drh->func("dropdb",$db_name ,$db_host,$username,$password,"admin") ){
print "drop database `",$db_name,"` successfully!n";
}
#创建数据库$db_name
$rc = $drh->func("createdb",$db_name ,$db_host,$username,$password,"admin")or
die "failed to create database ",$db_name,"!n";
print "create database `stucourse` successfully!n";

#获取数据库句柄
my $dbh = DBI -> connect ($dsn, $username, $password,{RaiseError => 1, PrintError => 0})or
die "failed to connect to the database!n",DBI->errstr();

#设置数据库字符集，防止中文乱码
my $charset = "set character_set_database=utf8";
my $sth = $dbh->prepare($charset);
$sth->execute();

#创建表course
my $query = "CREATE TABLE `course` ( "
."`cid` int(10) NOT NULL auto_increment,"
."`cno` varchar(20) NOT NULL, "
."`cname` varchar(20) default NULL, "
."PRIMARY KEY (`cid`)"
.")ENGINE=innodb DEFAULT CHARSET=utf8;";
my $sth = $dbh->prepare($query);
$sth->execute() or die "create table course error: ".$sth->errstr();
print "create table `course` successfully!n";

#创建表student
my $query = "CREATE TABLE `student` ("
."`sid` int(10) NOT NULL auto_increment,"
."`sno` varchar(20) NOT NULL,"
."`sname` varchar(20) default NULL,"
."PRIMARY KEY (`sid`)"
.")ENGINE=InnoDB DEFAULT CHARSET=utf8;";
my $sth = $dbh->prepare($query);
$sth->execute() or die "create table student error: ".$sth->errstr();
print "create table `student` successfully!n";

#创建表grade
my $query = "CREATE TABLE `grade` ("
 ."`gid` int(10) NOT NULL,"
 ."`cid` int(10) NOT NULL,"
 ."`sid` int(10) NOT NULL,"
 ."`grade` int(10) default NULL,"
 ."PRIMARY KEY (`gid`),"
 ."KEY `cid` (`cid`),"
 ."KEY `sid` (`sid`),"
 ."CONSTRAINT `cid` FOREIGN KEY (`cid`) REFERENCES `course`(`cid`),"
 ."CONSTRAINT `sid` FOREIGN KEY (`sid`) REFERENCES `student`(`sid`)"
 .") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
my $sth = $dbh->prepare($query);
$sth->execute() or die "create table grade error: ".$sth->errstr();
print "create table `grade` successfully!n";

#关闭数据库连接
$dbh->disconnect();

##-----------------------------------------------------------------------------
##利用Spreadsheet::ParseExcel解析Excel文件,并将其数据导入数据库stucourse.
##------------------------------------------------------------------------------
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::FmtUnicode; #字符编码

my $xlsFile = "stucourse.xls"; #Excel数据源文件，由于使用的ParseExcel,只支持97-2003，
   #请使用xls格式的数据源文件
my $head = 1; #如果sheet有表头则为1，否则设置为0
my $dbh = DBI -> connect ($dsn, $username, $password,{RaiseError => 1, PrintError => 0});
unless($dbh){
print "Error opening database: $DBI::errstr n";
exit;
}
#设置连接字符集
my $charset = "set character_set_connection=utf8";
my $sth = $dbh->prepare($charset);
$sth->execute();

my $parser = Spreadsheet::ParseExcel->new();
my $formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"CP936");#设置字符编码
my $workbook = $parser->parse($xlsFile, $formatter);#按所设置的字符编码解析
if ( !defined $workbook ) {
die $parser->error(), ".n";
}

print "nThere are ".$workbook->{SheetCount}." sheets in $xlsFile.n";

my $stuSheet = $workbook->{Worksheet}[0];
my $couSheet = $workbook->{Worksheet}[1];
my $graSheet = $workbook->{Worksheet}[2];

#将student表中的数据插入到数据库，一次查询语句，插入多条记录
$query = "insert into `student`(sid,sno,sname) values";
my ( $row_min, $row_max ) = $stuSheet->row_range();
my ( $col_min, $col_max ) = $stuSheet->col_range();

for my $row ( $row_min+$head .. $row_max ) {
$query .="(".$stuSheet->get_cell($row,0)->value().",'"
.$stuSheet->get_cell($row,1)->value()."','"
.$stuSheet->get_cell($row,2)->value()."'),"
}
$query = substr($query,0,length($query)-1).";";
#print $query;
my $rv = $dbh->do($query)or die "insert failed!";
print "import ".$stuSheet->{Name}." successfully!n";

#将course表中的数据插入到数据库，一次查询语句，插入多条记录
$query = "insert into `course`(cid,cno,cname) values";
my ( $row_min, $row_max ) = $couSheet->row_range();
my ( $col_min, $col_max ) = $couSheet->col_range();

for my $row ( $row_min+$head .. $row_max ) {
$query .="(".$couSheet->get_cell($row,0)->value().",'"
.$couSheet->get_cell($row,1)->value()."','"
.$couSheet->get_cell($row,2)->value()."'),"
}
$query = substr($query,0,length($query)-1).";";
my $rv = $dbh->do($query)or die "insert failed!";
print "import ".$couSheet->{Name}." successfully!n";

#将grade表中的数据插入到数据库，一次查询语句，插入多条记录
$query = "insert into `grade`(gid,cid,sid,grade) values";
my ( $row_min, $row_max ) = $graSheet->row_range();
my ( $col_min, $col_max ) = $graSheet->col_range();

for my $row ( $row_min+$head .. $row_max ) {
$query .="(".$graSheet->get_cell($row,0)->value().","
.$graSheet->get_cell($row,1)->value().","
.$graSheet->get_cell($row,2)->value().","
.$graSheet->get_cell($row,3)->value()."),"
}
$query = substr($query,0,length($query)-1).";";
my $rv = $dbh->do($query)or die "insert failed!";
print "import ".$graSheet->{Name}." successfully!n";

##-------------------------------------------------------
##利用perl读取数据库中的内容，对其中的数据进行分析
##-------------------------------------------------------

#查询统计选课人数超过10的课程
$query = "select cid, cname"
." from course"
." where cid in("
." select cid"
." from grade"
." group by cid"
." having count(cid)>10"
.");";
$sth = $dbh->prepare($query);
$sth->execute() or die "query error!n";
print "nThe courses that be token by at least 10 students are:n";
while(my @result=$sth->fetchrow_array){
print "$result[0] $result[1] n";
}
$sth->finish();

#查询各个学生的平均成绩
$query = "select s.sid, s.sname,avg(grade)"
." from student s, grade g"
." where s.sid=g.sid"
." group by g.sid;";
$sth = $dbh->prepare($query);
$sth->execute() or die "query error!n";
print "nThe average grade of every student:n";
while(my @result=$sth->fetchrow_array){
print "$result[0] $result[1] $result[2]n";
}
$sth->finish();
#关闭数据库连接
$dbh->disconnect();
####################################