最近使用Perl进行Excel文档的生成和读取操作,总结如下。总的说来以下几个模块都可以实现:
1: Win32::OLE ---- 专用于Windows,可以读取修改已存在的Excel文档.
2:Excel::Writer::XLSX 和 Spreadsheet::WriteExcel ---- 只能生成新的Excel文档
3: Spreadsheet::ParseExcel ---- 解析Excel(2003)文档
一:Win32::OLE
Win32::OLE 是调用Windows的VBA接口进行Excel操作,所以只能在安装了Excel的Windows上使用。
Code:
#! C:\perl\bin
use Win32::OLE;
# use existing instance if Excel is already running
eval {$Excel = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $Excel) {
$Excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# 关掉Excel的提示,比如是否保存之类的。
# $Book = $Excel->Workbooks->Add; 生成新Excel
# $Book->SaveAs('H:\Temp\Test.xls'); 保存名为Test.xls
# 打开已存在的Excel文档
$Book = $Excel->Workbooks->Open('H:\Temp\configure.xls');
# 使用该Excel文档中名为"Run"的Sheet
$Sheet = $Book->Worksheets("Run");
$Sheet_Count = $Book->Worksheets->Count; #Excel 中sheet的个数
$Sheet2_Name = $Book->Worksheets(2)->Name; #Excel 中sheet2的Name
foreach my $row (1..4)
{
foreach my $col (1..3)
{
# 读取单元格内的值
next unless defined $Sheet->Cells($row,$col)->{'Value'};
printf "At ($row, $col) the value is %s .\n",$Sheet->Cells($row,$col)->{'Value'};
}
}
# 设置单元格A1的值为"Test"
$Sheet->Cells(1,1)->{'Value'} = "Test";
$Book->Close;
上述只是简单的读取单元格值和修改,还有很多其他功能设置字体和插入表格之类的。可以仔细研究VBA中的接口。
二:Excel::Writer::XLSX 和 Spreadsheet::WriteExcel
该模块可以用于Linux或者Windows上,且要求Perl的版本必须是10.0以上。缺点:只能生成新的无法读取以存在的Excel。
Code:
#! C:\perl\bin
use Excel::Writer::XLSX;
# 新建Excel表格"Test.xlsx"
my $workbook = Excel::Writer::XLSX->new('H:\Temp\Test.xlsx');
# 新增名为"Test"的Sheet
$worksheet = $workbook->add_worksheet( "Test" );
# 设置列的宽度
$worksheet->set_column( 'A:I', 13 );
# 设置单元格格式
my $head_format = $workbook->add_format(
font => 'Verdana',
bold => 1,
size => 11,
align => 'center',
);
# 合并单元格输入内容
$worksheet->merge_range("B1:K1","Use Excel Write XLSX Module Create Excel.",$head_format);
my $format1 = $workbook->add_format(
font => 'Verdana',
bold => 1,
size => 9,
underline => 1,
);
# 在单元格B3中输入内容
$worksheet->write( 'B3',"Test:",$format1 );
# 设置单元格颜色
# set_custom_color($index,$R,$G,$B);
# RGB三原色值
my $bg_color1 = $workbook->set_custom_color( 40, 71, 170, 197 );
my $format3 = $workbook->add_format(
font => 'Verdana',
bold => 1,
size => 9,
align => 'center',
bg_color =>$bg_color1, # <=> bg_color =>40,
bottom =>1,
top =>1,
left =>1,
right =>1,
);
$worksheet->merge_range( 'C4:K4', "Use Excel Write XLSX Module Create Excel.", $format3 );
三:Spreadsheet::ParseExcel
该模块用于读取Excel文档中内容,一般和Spreadsheet::WriteExcel 模块配套使用。Linux+Windows都适用。
Code:
#! C:\perl\bin
my $parser = Spreadsheet::ParseExcel->new();
# 打开Excel文档Test.xls
my $workbook = $parser->parse('H:\Temp\Test.xls');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
# 读取Excel中所有sheet中内容
for my $worksheet ( $workbook->worksheets() ) {
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";
}
}
}
1: Win32::OLE ---- 专用于Windows,可以读取修改已存在的Excel文档.
2:Excel::Writer::XLSX 和 Spreadsheet::WriteExcel ---- 只能生成新的Excel文档
3: Spreadsheet::ParseExcel ---- 解析Excel(2003)文档
一:Win32::OLE
Win32::OLE 是调用Windows的VBA接口进行Excel操作,所以只能在安装了Excel的Windows上使用。
Code:
#! C:\perl\bin
use Win32::OLE;
# use existing instance if Excel is already running
eval {$Excel = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $Excel) {
$Excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# 关掉Excel的提示,比如是否保存之类的。
$Excel->{DisplayAlerts} = 'False';
# $Book = $Excel->Workbooks->Add; 生成新Excel
# $Book->SaveAs('H:\Temp\Test.xls'); 保存名为Test.xls
# 打开已存在的Excel文档
$Book = $Excel->Workbooks->Open('H:\Temp\configure.xls');
# 使用该Excel文档中名为"Run"的Sheet
$Sheet = $Book->Worksheets("Run");
$Sheet_Count = $Book->Worksheets->Count; #Excel 中sheet的个数
$Sheet2_Name = $Book->Worksheets(2)->Name; #Excel 中sheet2的Name
foreach my $row (1..4)
{
foreach my $col (1..3)
{
# 读取单元格内的值
next unless defined $Sheet->Cells($row,$col)->{'Value'};
printf "At ($row, $col) the value is %s .\n",$Sheet->Cells($row,$col)->{'Value'};
}
}
# 设置单元格A1的值为"Test"
$Sheet->Cells(1,1)->{'Value'} = "Test";
$Book->Close;
上述只是简单的读取单元格值和修改,还有很多其他功能设置字体和插入表格之类的。可以仔细研究VBA中的接口。
二:Excel::Writer::XLSX 和 Spreadsheet::WriteExcel
该模块可以用于Linux或者Windows上,且要求Perl的版本必须是10.0以上。缺点:只能生成新的无法读取以存在的Excel。
Code:
#! C:\perl\bin
use Excel::Writer::XLSX;
# 新建Excel表格"Test.xlsx"
my $workbook = Excel::Writer::XLSX->new('H:\Temp\Test.xlsx');
# 新增名为"Test"的Sheet
$worksheet = $workbook->add_worksheet( "Test" );
# 设置列的宽度
$worksheet->set_column( 'A:I', 13 );
# 设置单元格格式
my $head_format = $workbook->add_format(
font => 'Verdana',
bold => 1,
size => 11,
align => 'center',
);
# 合并单元格输入内容
$worksheet->merge_range("B1:K1","Use Excel Write XLSX Module Create Excel.",$head_format);
my $format1 = $workbook->add_format(
font => 'Verdana',
bold => 1,
size => 9,
underline => 1,
);
# 在单元格B3中输入内容
$worksheet->write( 'B3',"Test:",$format1 );
# 设置单元格颜色
# set_custom_color($index,$R,$G,$B);
# RGB三原色值
my $bg_color1 = $workbook->set_custom_color( 40, 71, 170, 197 );
my $format3 = $workbook->add_format(
font => 'Verdana',
bold => 1,
size => 9,
align => 'center',
bg_color =>$bg_color1, # <=> bg_color =>40,
bottom =>1,
top =>1,
left =>1,
right =>1,
);
$worksheet->merge_range( 'C4:K4', "Use Excel Write XLSX Module Create Excel.", $format3 );
该模块功能强大,可以参考CPAN上的模块介绍。
Spreadsheet::WriteExcel 模块和 Excel::Writer::XLSX 功能一样,作者是同一个人。唯一区别一个可以生成2007+Excel,另一个针对老版本2003 Excel三:Spreadsheet::ParseExcel
该模块用于读取Excel文档中内容,一般和Spreadsheet::WriteExcel 模块配套使用。Linux+Windows都适用。
Code:
#! C:\perl\bin
use Spreadsheet::ParseExcel;
# 创建解析Objectmy $parser = Spreadsheet::ParseExcel->new();
# 打开Excel文档Test.xls
my $workbook = $parser->parse('H:\Temp\Test.xls');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
# 读取Excel中所有sheet中内容
for my $worksheet ( $workbook->worksheets() ) {
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";
}
}
}