perl模块推荐20—读写EXCEL

摘要

excel是一种常见数据存储的表格，被大家广泛。如何利用perl快速读写excel,是挖掘excel中信息的前提条件。excel分为2种版本，一种是03版本，xls后缀，另一种是07版本，xlsx后缀。对应模块Spreadsheet::ParseExcel，Spreadsheet::WriteExcel；以及Spreadsheet::XLSX和Excel::Writer::XLSX.

cpanm安装上述模块

如果安装中在test过程中报错，可以尝试强制安装。

cpanm Spreadsheet::XLSX -f

读excel
  • xls 版本 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!perl -w
use strict;

use Spreadsheet::ParseExcel;  #for xls

#创建一个excel 解释器
my $parser   = Spreadsheet::ParseExcel->new();
# 解释xls文件，得到xls文件对象
my $workbook = $parser->parse('2F.xls');
#得到excel 中所有的sheet
my @sheet_objs=$workbook->worksheets();
#指定要操作的sheet,可以通过sheet名字或者index进行指定
#index 从0开始计数
my $worksheet_name=$workbook->worksheet('Worker name');
my $worksheet_1=$workbook->worksheet(0);

#获取Worker name中的详细信息
my ( $row_min, $row_max ) = $worksheet_name->row_range();
my ( $col_min, $col_max ) = $worksheet_name->col_range();

for my $row ( $row_min .. $row_max )
{
    for my $col ( $col_min .. $col_max )
    {


        my $cell = $worksheet_name->get_cell( $row, $col );
        next unless $cell;

        print "Row, Col    = ($row, $col) ";
        print "Value       = ", $cell->value(),       "\n";
        #print "\n";

    }
}
  • xlsx 版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#!/usr/perl -w
use strict;
use Spreadsheet::XLSX;     #xlsx

my $workbook=Spreadsheet::XLSX->new('2F.xlsx');
my $worksheet_1=$workbook->worksheet(0);

#获取Worker name中的详细信息
my ( $row_min, $row_max ) = $worksheet_1->row_range();
my ( $col_min, $col_max ) = $worksheet_1->col_range();

for my $row ( $row_min .. $row_max )
{
    for my $col ( $col_min .. $col_max )
    {


        my $cell = $worksheet_1->get_cell( $row, $col );
        next unless $cell;

        print "Row, Col    = ($row, $col) ";
        print "Value       = ", $cell->value(),       "\n";
        #print "\n";

    }
}
写excel
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

#!/usr/perl -w
use strict;
use Spreadsheet::ParseExcel;  #for xls
use Spreadsheet::XLSX;     #xlsx
use Spreadsheet::WriteExcel;
use Excel::Writer::XLSX;

my $workbook=Spreadsheet::XLSX->new('2F.xlsx');

#for write
#分别创建xls xlsx格式文件进行写入
my $workbook_w = Excel::Writer::XLSX->new( '07.xlsx' );
my $workbook_w03=Spreadsheet::WriteExcel->new('03.xls');

#read  and write
for my $worksheet ( $workbook->worksheets() ) {
    my $worksheet_w=$workbook_w->add_worksheet();
    my $worksheet_w03=$workbook_w03->add_worksheet();


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

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {


            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

            print "Row, Col    = ($row, $col)\n";
            print "Value       = ", $cell->value(),       "\n";
            print "Unformatted = ", $cell->unformatted(), "\n";
            print "\n";
            $worksheet_w->write( $row, $col, $cell->value() );
            $worksheet_w03->write( $row, $col, $cell->value() );
        }
    }
}

注意

xlsx 和xls 读写模块内部操作接口是相同的。只要在创建文件和读入文件的时候接口不同而已。

参考

更多使用方法，请参看metacpan中相应的模块。