使用Perl读取excel文件，并导入mysql数据库

使用Perl读取excel文件，这里只限于.xls文件，新的.xlsx有新的方法。

PERL的Spreadsheet::ParseExcel模块支持Excel的读操作：
在命令行下输入：cpan Spreadsheet::ParseExcel，即可自动安装；

安装后，使用perldoc Spreadsheet::ParseExcel检查安装是否成功。

#!/usr/bin/perl -w
 
use warnings;
use strict;
use Spreadsheet::ParseExcel;
use DBI;
 
my $filename = $ARGV[0] or die "Must specify filename to parse.\n";
my $parser   = Spreadsheet::ParseExcel->new();
my $workprotein = $parser->parse( $filename );

if ( !defined $workprotein ) {
    die "Parsing error: ", $parser->error(), ".\n";
}


my $host = "localhost";         # 主机地址
my $driver = "mysql";           # 接口类型 默认为 localhost
my $database = "perltest";        # 数据库，改成自己数据库名字
# 驱动程序对象的句柄
my $dsn = "DBI:$driver:database=$database:$host";  
my $userid = "root";            # 数据库用户名
my $password = "******";        # 数据库密码，改成自己的密码
 
# 连接数据库
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

#插入操作
for my $worksheet ( $workprotein->worksheets() ) {

    print "Worksheet name: ", $worksheet->get_name(), "\n\n";

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    my @value;#定义数组存储每行的数据
	#数据库的列表头
    my $ID;
    my $UniprotID;
    my $Name;
    my $AllName;
    my $Gene;
    my $SourceOfSpecies;
    my $Family;
    my $TypeInhibited;
    my $Function;
    my $Reference;
    my $Sequence;
    my $Length;
    my $MolecularWeight;
    my $StructureValidate;
    my $Picture;
    my $Homology;
    my $PhylogeneticTree;
    my $HomologyNetwork;
    my $InsertTime;
    my $UpdateTime;

    my $sth;

	
	#按行读取excel文件，如果按列则自己改一下row和col
     for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
            my $cell = $worksheet->get_cell( $row, $col ); #获取几行几列的内容
            next unless $cell;
            push(@value, $cell->value());				#表格内容进栈
        }
        #表格内容出栈并赋值给每个变量
        $ID = shift(@value);
        $UniprotID = shift(@value);
        $Name = shift(@value);
        $AllName = shift(@value);
        $Gene = shift(@value);
        $SourceOfSpecies = shift(@value);
        $Family = shift(@value);
        $TypeInhibited = shift(@value);
        $Function = shift(@value);
        $Reference = shift(@value);
        $Sequence = shift(@value);
        $Length = shift(@value);
        $MolecularWeight = shift(@value);
        $StructureValidate = shift(@value);
        $Picture = shift(@value);
        $Homology = shift(@value);
        $PhylogeneticTree = shift(@value);
        $HomologyNetwork = shift(@value);
        $InsertTime = shift(@value);
        $UpdateTime = shift(@value);

        #Function是sql中的关键字，所以不可以用，要加``，上一篇写了^ ^
        #数据库查询语句
        $sth = $dbh->prepare("INSERT INTO protein 
            (ID, UniprotID, Name, AllName, Gene, 
            SourceOfSpecies, Family, TypeInhibited, `Function`, Reference, 
            Sequence, Length, MolecularWeight, StructureValidate, Picture, 
            Homology, PhylogeneticTree, HomologyNetwork, InsertTime, UpdateTime) 
            values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $sth->execute($ID, $UniprotID, $Name, $AllName, $Gene, 
            $SourceOfSpecies, $Family, $TypeInhibited, $Function, $Reference, 
            $Sequence, $Length, $MolecularWeight, $StructureValidate, $Picture, 
            $Homology, $PhylogeneticTree, $HomologyNetwork, $InsertTime, $UpdateTime) 
                    or die $DBI::errstr;
        $sth->finish();
    }
}
$dbh->commit or die $DBI::errstr;

运行格式：perl perl的名称.pl 文件名.xls

参考：
使用Perl读取Excel：https://www.cnblogs.com/pangxiaodong/archive/2012/01/30/2331887.html
Perl连接数据库：https://www.runoob.com/perl/perl-database-access.html