Perl 操作 excel

参考与 http://www.aspzz.cn/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);

}