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中相应的模块。