用 Perl 讀寫 Excel 檔案

標籤：

轉載：http://www.ibm.com/developerworks/cn/linux/sdk/perl/culture-8/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 ，您可以使用下面所列的代碼逐個單元地轉儲工作表：

清單 2：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 解析樣本，因此我將示範那個樣本，然後解釋其工作原理。

下載 parse-excel.pl

清單 3：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 檔案。只保留資料，不保留格式和任何特性。公式被丟棄。

下載 excel-x2.pl

清單 4：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 資料進行編程訪問，則它們很有用。這兩個應用程式我都用過，我發現它們對於日常工作很不錯。

用 Perl 讀寫 Excel 檔案