参考与 http://www.jb51.net/article/33679.htm
先安装Spreadsheet-ParseExcel和Spreadsheet-
读取Excel
Book.xls sheet1
XML FPS CPU GPU Memory
001 12 3.9 19.3 14.5
002 17 10 17.2 25.7
003 15 28 51.7 36.4
004 25 62 77.1 36.9
005 22 74 85.4 35.5
parse_excel.pl
#!/usr/bin/env perl
use strict;
use warnings;
use 5.010;
use Spreadsheet::ParseExcel;
sub parse {
my $excel_name = "Book.xls";
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($excel_name);
if(! defined $workbook) {
die $parser->error();
}
my @worksheets = $workbook->worksheets();
my $booksheet = $worksheets[0];
my($row_min, $row_max) = $booksheet->row_range();
my($col_min, $col_max) = $booksheet->col_range();
say "[$row_min, $row_max]";
say "[$col_min, $col_max]";
for my $row ($row_min .. $row_max) {
for my $col ($col_min .. $col_max) {
my $cell = $booksheet->get_cell($row, $col);
next unless $cell;
say "($row, $col):".$cell->value();
}
}
}
sub main {
parse
}
main
写Excel
此方法会覆盖原来的数据。
#!/usr/bin/env perl
use warnings;
use strict;
use Spreadsheet::WriteExcel;
sub write_excel {
my $book_name = "Book.xls";
my $workbook = Spreadsheet::WriteExcel->new($book_name);
my $worksheet = $workbook->add_worksheet();
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color('red'); # 前景色
$format->set_align('center');
my($row, $col) = (0, 0);
$worksheet->write($row, $col, "Tom", $format); # 通过坐标方式(x, y)来写入
$worksheet->write('A2', "123456"); # 通过cell的名称(A2)来写入
}
sub main {
write_excel
}
main
更新Excel
此方法可能会丢失cell的格式信息
use Spreadsheet::ParseExcel::SaveParser;
sub update_excel {
my $book_name = "Book.xls";
my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $temp = $parser->Parse($book_name);
my $sheetname = 'Sheet1';
my $worksheet = $temp->worksheet($sheetname);
my $cell = $worksheet->get_cell(0, 0);
my $format = $cell->{FormatNo};
$worksheet->AddCell(0, 0, "xyz", $format);
$temp->SaveAs($book_name);
}