功能豐富的Perl:用Perl讀寫Excel文件

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_Lite

ppm install Spreadsheet::ParseExcel

ppm install Spreadsheet::WriteExcel

在這種情況下,該模塊的安裝將失敗,因爲 IO::Scalar 還不可用,因此,您可能想放棄 PPM 問題的查找,而轉向內置的 Win32::OLE 模塊。然而,在您閱讀本文時,ActiveState 可能已經發布了該問題的修正。

有了 ActiveState 的 Win32::OLE,您可以使用下面所列的代碼逐個單元地轉儲工作表:

下載 win32excel.pl

清單 2:win32excel.pl

#!/usr/bin/perl -w

use 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 new

my $Excel = Win32::OLE-GetActiveObject('Excel.Application')

|| Win32::OLE-new('Excel.Application', 'Quit');

# open Excel file

my $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 -w

use 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 {SheetCount} ; $iSheet++)

{

$oWkS = $oBook-{Worksheet}[$iSheet];

print "--------- SHEET:", $oWkS-{Name}, "\n";

for(my $iR = $oWkS-{MinRow} ;

defined $oWkS-{MaxRow} && $iR {MaxRow} ;

$iR++)

{

for(my $iC = $oWkS-{MinCol} ;

defined $oWkS-{MaxCol} && $iC {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 -w

use strict;

use Spreadsheet::ParseExcel;

use Spreadsheet::WriteExcel;

use Data::Dumper;

# cobbled together from examples for the Spreadsheet::ParseExcel and

# Spreadsheet::WriteExcel modules

my $sourcename = shift @ARGV;

my $destname = shift @ARGV or die "invocation: $0 ";

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}

 
功能豐富的Perl:輕松調試Perl的技巧
  錯誤所帶來的麻煩  軟件開發人員通常都低估了軟件測試的重要性。這一現象的根本原因很簡單:處理錯誤很困難!因爲錯誤往往暴露了代碼的根本缺陷,所以有時候開發人員甚至會爲了幾個錯誤而從頭開始重新編寫項目...查看完整版>>功能豐富的Perl:輕松調試Perl的技巧
 
功能豐富的Perl:Perl自動化系統管理
  UNIX 系統管理總是一個棘手的問題,運用正確的工具會使這個問題變得容易。在這一部分中,Teodor 提出了關于使用 Perl 來簡化和牢固系統管理的想法。在這種環境中,系統配置引擎 cfengine 是一個極其重要的工具。...查看完整版>>功能豐富的Perl:Perl自動化系統管理
 
功能豐富的Perl:JustAnotherPerlHacker
  JAPH:“Just another Perl hacker”  據我們所知,JAPH 格式是在二十世紀九十年代由 Randal Schwartz 推廣的(好幾處信息來源都同意這個說法)。今天,JAPH 到處可見,它們是由該流派的那些不知疲倦的藝術家們...查看完整版>>功能豐富的Perl:JustAnotherPerlHacker
 
功能豐富的Perl:Perl用于實現遺傳算法
  如果您的機器上已經安裝了 Perl 5.005 或者更高的版本,您可以運行一下文章中的例子。您的系統最好應該是安裝了最近的(2000 年或者更遲些)主流的 UNIX(Linux,Solaris,BSD),但其它種類的操作系統可能也可以...查看完整版>>功能豐富的Perl:Perl用于實現遺傳算法
 
功能豐富的Perl:編寫說英語的Perl程序
  設計程序的用戶界面可能很困難而且耗時。Teodor Zlatanov 討論了如何使用 Parse::RecDescent 模塊來用簡單的英語創建用戶界面文法。他還展示了向程序添加功能或從程序除去功能時,更改文法是如何的方便。另外還與...查看完整版>>功能豐富的Perl:編寫說英語的Perl程序
 
· 臨近國慶,李小璐與女兒在天安門前合影

http://image.wangchao.net.cn/users/...

· 震驚!原來酒駕處罰這麽嚴重

酒駕處罰標准 不要有任何一絲僥幸心理! http://image....

· 把年齡相仿的獅虎熊放一起,誰更厲害?結果出人意料

很多人都想知道獅子、老虎和熊打起來誰最厲害,于是便有好事之人把這三種動物關在一起...