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

君子不器 2016年05月04日 编程世界 3795次阅读 查看评论

 

实现的功能为:

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

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

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



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

« 上一篇 下一篇 » 君子不器原创文章,转载请注明出处! 标签:perl

相关日志:

博主介绍
乌云蔽月,人迹踪绝,说不出如斯寂寞。
控制面板
您好,欢迎到访网站!
  [查看权限]
站点信息
  • 文章总数:1279
  • 页面总数:2
  • 分类总数:9
  • 标签总数:61
  • 评论总数:331
标签列表
友情链接