功能丰富的Perl:用Perl读写Excel文件

 

功能丰富的Perl:用Perl读写Excel文件

2001年08月31日 16:00 来源：ChinaUnix文档频道 作者：HonestQiao 编辑：周荣茂

级别： 初级

Teodor Zlatanov ([e-mail]), 程序员, Gold Software Systems

2001 年 9 月 01 日

直到最近才开启了通往 Microsoft Excel 这个最流行的桌面电子表格应用程序的大门。本文研究了在 Windows 和 Linux 中如何使用 Perl 和几个简单模块读写 Excel 文件。本文的作者 Teodor Zlatanov 是一名 Perl 专家，自 1992 年起，他一直在该社区中工作，在他涉足的各种领域中，他专长于文本解析中的开放源码工作。

解析 Excel 文件提出了一个无论怎样看都很困难的难题。直到去年，UNIX 模块还完全不可用，并且只能用 Win32::OLE 模块来检索来自 Windows Excel 文件的数据。但由于两位 Perl 高手和许多志愿者的帮助和奉献，情况最终得以改变！

Spreadsheet::WriteExcel 和 Spreadsheet::ParseExcel

在 2000 年，Takanori Kawai 和 John McNamara 编写出了 Spreadsheet::WriteExcel 和Spreadsheet::ParseExcel 模块并将它们张贴在 CPAN 上，这两个模块使得在任何平台上从 Excel 文件抽取数据成为可能（尽管不容易）。

正如我们在稍后将看到的，如果您正在使用 Windows， Win32::OLE 仍提供一个更简单、更可靠的解决方案，并且 Spreadsheet::WriteExcel 模块建议使用 Win32::OLE 来进行更强大的数据和工作表操纵。 Win32::OLE 带有 ActiveState Perl 工具箱，可以用来通过 OLE 驱动许多其它 Windows 应用程序。请注意，要使用此模块，您仍需要在机器上安装和注册一个 Excel 引擎（通常随 Excel 本身安装）。

需要解析 Excel 数据的应用程序数以千计，但是这里有几个示例：将 Excel 导出到 CSV、与存储在共享驱动器上的电子表格交互、将金融数据移至数据库以便形成报告以及在不提供任何其他格式的情况下分析数据。

要演示这里给出的示例，必须在您的系统上安装 Perl 5.6.0。您的系统最好是最近（2000 年或以后）的主流 UNIX 安装（Linux、Solaris 和 BSD）。虽然这些示例在以前版本的 Perl 和 UNXI 以及其他操作系统中也可以使用，但是您应该考虑到您将面对那些它们无法作为练习发挥作用的情况。

Windows 示例：解析

本节仅适用于 Windows 机器。所有其它各节适用于 Linux。

在进行之前，请安装 ActiveState Perl（这里使用版本 628）或 ActiveState Komodo IDE 以编辑和调试 Perl。Komodo 为家庭用户提供一个免费许可证，您大概在几分钟之内就可以得到它。（有关下载站点，请参阅本文后面的 参考资料。）

使用 ActiveState PPM 软件包管理器安装 Spreadsheet::ParseExcel 和 Spreadsheet::WriteExcel 模块是困难的。PPM 没有历史记录，难以设置选项，帮助会滚出屏幕并且缺省方式是忽略相关性而安装。您可以从命令行输入“ppm”然后发出以下命令来调用 PPM：

清单 1：安装 Excel 模块的 PPM 命令

ppm> install OLE::Storage_Liteppm> install Spreadsheet::ParseExcelppm> install Spreadsheet::WriteExcel

在这种情况下，该模块的安装将失败，因为 IO::Scalar 还不可用，因此，您可能想放弃 PPM 问题的查找，而转向内置的 Win32::OLE 模块。然而，在您阅读本文时，ActiveState 可能已经发布了该问题的修正。

有了 ActiveState 的 Win32::OLE ，您可以使用下面所列的代码逐个单元地转储工作表：

下载 http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/win32excel.pl

清单 2：http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/win32excel.pl

#!/usr/bin/perl -wuse strict;use Win32::OLE qw(in with);use Win32::OLE::Const 'Microsoft Excel';$Win32::OLE::Warn = 3;                                # die on errors...# get already active Excel application or open newmy $Excel = Win32::OLE->GetActiveObject('Excel.Application')    || Win32::OLE->new('Excel.Application', 'Quit');  # open Excel filemy $Book = $Excel->Workbooks->Open("c:/komodo projects/test.xls"); # You can dynamically obtain the number of worksheets, rows, and columns# through the Excel OLE interface.  Excel's Visual Basic Editor has more# information on the Excel OLE interface.  Here we just use the first# worksheet, rows 1 through 4 and columns 1 through 3.# select worksheet number 1 (you can also select a worksheet by name)my $Sheet = $Book->Worksheets(1);foreach my $row (1..4){ foreach my $col (1..3) {  # skip empty cells  next unless defined $Sheet->Cells($row,$col)->{'Value'}; # print out the contents of a cell    printf "At ($row, $col) the value is %s and the formula is %s\n",   $Sheet->Cells($row,$col)->{'Value'},   $Sheet->Cells($row,$col)->{'Formula'};         }}# clean up after ourselves$Book->Close;

请注意，您可以用以下方式很轻松地为单元分配值：

$sheet->Cells($row, $col)->{'Value'} = 1;

Linux 示例：解析

本节适用于 UNIX，特别适用于 Linux。没有在 Windows 中测试它。

很难给出一个比 Spreadsheet::ParseExcel 模块文档中所提供的示例更好的 Linux 解析示例，因此我将演示那个示例，然后解释其工作原理。

下载 http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/parse-excel.pl

清单 3：http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/parse-excel.pl

#!/usr/bin/perl -wuse strict;use Spreadsheet::ParseExcel;my $oExcel = new Spreadsheet::ParseExcel;die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;my $oBook = $oExcel->Parse($ARGV[0]);my($iR, $iC, $oWkS, $oWkC);print "FILE  :", $oBook->{File} , "\n";print "COUNT :", $oBook->{SheetCount} , "\n";print "AUTHOR:", $oBook->{Author} , "\n" if defined $oBook->{Author};for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++){ $oWkS = $oBook->{Worksheet}[$iSheet]; print "--------- SHEET:", $oWkS->{Name}, "\n"; for(my $iR = $oWkS->{MinRow} ;     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;     $iR++) {  for(my $iC = $oWkS->{MinCol} ;      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;      $iC++)  {   $oWkC = $oWkS->{Cells}[$iR][$iC];   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);  } }}

此示例是用 Excel 97 测试的。如果它不能工作，则试着将它转换成 Excel 97 格式。 Spreadsheet::ParseExcel 的 perldoc 页也声称了 Excel 95 和 2000 兼容性。

电子表格被解析成一个名为 $oBook 的顶级对象。$oBook 具有辅助程序的特性，例如“File”、“SheetCount”和“Author”。 Spreadsheet::ParseExcel 的 perldoc 页的工作簿一节中记载了这些特性。

该工作簿包含几个工作表：通过使用工作簿 SheetCount 特性迭代它们。每个工作表都有一个 MinRow 和 MinCol 以及相应的 MaxRow 和 MaxCol 特性，它们可以用来确定该工作簿可以访问的范围。Spreadsheet::ParseExcel perldoc 页的工作表一节中记载了这些特性。

可以通过 Cell 特性从工作表获得单元；那就是清单 3 中获得 $oWkC 对象的方式。 Spreadsheet::ParseExcel 的 perldoc 页的 Cell 一节中记载了 Cell 特性。根据文档，似乎没有一种方式能够获得特定单元中列出的公式。

Linux 示例：写入

本节适用于 UNIX，特别适用于 Linux。没有在 Windows 中测试它。

Spreadsheet::WriteExcel 在 Examples 目录中带有许多示例脚本，通常可以在 /usr/lib/perl5/site_perl/5.6.0/Spreadsheet/WriteExcel/examples 下找到这些脚本。它可能被安装在其它各处；如果找不到那个目录，请与您的本地 Perl 管理员联系。

坏消息是 Spreadsheet::WriteExcel 无法用于写入现有 Excel 文件。必须自己使用Spreadsheet::ParseExcel 从现有 Excel 文件导入数据。好消息是 Spreadsheet::WriteExcel 与 Excel 5 直至 Excel 2000 兼容。

这里有一个程序，它演示如何从一个 Excel 文件抽取、修改（所有数字都乘以 2）数据以及将数据写入新的 Excel 文件。只保留数据，不保留格式和任何特性。公式被丢弃。

下载 http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/excel-x2.pl

清单 4：http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/excel-x2.pl

#!/usr/bin/perl -wuse strict;use Spreadsheet::ParseExcel;use Spreadsheet::WriteExcel;use Data::Dumper;# cobbled together from examples for the Spreadsheet::ParseExcel and# Spreadsheet::WriteExcel modulesmy $sourcename = shift @ARGV;my $destname = shift @ARGV or die "invocation: $0 <source file> <destination file>";my $source_excel = new Spreadsheet::ParseExcel;my $source_book = $source_excel->Parse($sourcename) or die "Could not open source Excel file $sourcename: $!";my $storage_book;foreach my $source_sheet_number (0 .. $source_book->{SheetCount}-1){ my $source_sheet = $source_book->{Worksheet}[$source_sheet_number]; print "--------- SHEET:", $source_sheet->{Name}, "\n"; # sanity checking on the source file: rows and columns should be sensible next unless defined $source_sheet->{MaxRow}; next unless $source_sheet->{MinRow} <= $source_sheet->{MaxRow}; next unless defined $source_sheet->{MaxCol}; next unless $source_sheet->{MinCol} <= $source_sheet->{MaxCol}; foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {  foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol})  {   my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];   if ($source_cell)   {    print "( $row_index , $col_index ) =>", $source_cell->Value, "\n";    if ($source_cell->{Type} eq 'Numeric')    {  $storage_book->{$source_sheet->{Name}}->{$row_index}->{$col_index} = $source_cell->Value*2;    }    else    {  $storage_book->{$source_sheet->{Name}}->{$row_index}->{$col_index} = $source_cell->Value;    } # end of if/else   } # end of source_cell check  } # foreach col_index } # foreach row_index} # foreach source_sheet_numberprint "Perl recognized the following data (sheet/row/column order):\n";print Dumper $storage_book;my $dest_book  = Spreadsheet::WriteExcel->new("$destname") or die "Could not create a new Excel file in $destname: $!";print "\n\nSaving recognized data in $destname...";foreach my $sheet (keys %$storage_book){ my $dest_sheet = $dest_book->addworksheet($sheet); foreach my $row (keys %{$storage_book->{$sheet}}) {  foreach my $col (keys %{$storage_book->{$sheet}->{$row}})  {   $dest_sheet->write($row, $col, $storage_book->{$sheet}->{$row}->{$col});  } # foreach column } # foreach row} # foreach sheet$dest_book->close();print "done!\n";

值得注意的是，程序的数据抽取和存储部分必须要分开。它们本来可以同时进行，但是通过将它们分开，可以轻松地进行错误修复和改进。

对于上述问题，一个好得多的解决方案可能是通过 XML::Excel CPAN 模块实现，但是必须编写将 XML 转换回 Excel 的特殊转换器。 如果要以那种方式导入数据，还可以通过DBD::Excel 模块使用 DBI 接口。最后，Spreadsheet::ParseExcel 带有 Spreadsheet::ParseExcel::SaveParser 模块，它声称可以在两个 Excel 文件之间转换，但是没有文档和示例。我的网站（请参阅 参考资料）演示了一个使用SaveParser 的示例。事先警告：那是个实验型程序，极易出问题。

结束语

如果您正在使用 Windows 机器，请坚持使用 Win32::OLE 模块，除非您的机器上根本没有 Excel。虽然 Spreadsheet::WriteExcel 和 Spreadsheet::ParseExcel 模块的功能正不断完善，但Win32::OLE 是目前获得 Excel 数据的最简便方式。

在 UNIX，特别是 Linux 上，请使用 Spreadsheet::WriteExcel 和 Spreadsheet::ParseExcel 模块对 Excel 数据进行编程访问。但是事先警告：它们还是相当不成熟的模块，如果您需要稳定性，则它们可能不适合您。

您还可以考虑象 Gnumeric 和 StarOffice（请参阅 参考资料）这样的软件包，可以免费获得它们，而且它们提供一个完整的 GUI 界面和 Excel 文件的导入／导出能力。如果您不需要对 Excel 数据进行编程访问，则它们很有用。这两个应用程序我都用过，我发现它们对于日常工作很不错。