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

     parseExceltoDB.pl

实现的功能为：

         （1）创建数据库stucourse,包括三个表:student,course,grade.

（2）将stucourse.xls的内容导入到数据库stucourse中，将数据分别导入到三个表中。

（3）从数据库中读取数据，统计选课人数超过10的课程名称及各个学生的平均成绩。

 

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