在Windows上安装ActivePerl所需要的读取Excel文件一般用Win32::OLE,但对于跨平台来说,还是选择另外的
Spreadsheet::ParseExcel及Spreadsheet::WriteExcel最好。前者是读Excel文件用的,后者用于写Excel文件。
Spreadsheet::ParseExcel只能读95-2003格式的Excel文档,对于office 2007 Excel则要安装Spreadsheet::XLSX。1安装Spreadsheet模块:
- ppm install OLE::Storage_Lite #如果不安装这个,后面两个安装不了
- ppm install Spreadsheet::ParseExcel
- ppm install Spreadsheet::WriteExcel
此时在PERL_HOME/site/lib下多了一个Spreadsheet模块。
2 查看模块是否安装成功以及其说明文档
- perldoc Spreadsheet::ParseExcel
此时如果显示其说明文档,则安装成功,否则失败。
3小试牛刀
运行它的示例程序:
- use strict;
- use Spreadsheet::ParseExcel;
-
- my $parser = Spreadsheet::ParseExcel->new();
- my $workbook = $parser->parse('Book.xls');
-
- if ( !defined $workbook ) {
- die $parser->error(), ".\n";
- }
-
- 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 "\n";
- }
- }
- }
显示结果:
中文是乱码,这个问题如下解决:
4 安装字符编码模块Unicode::Map
5 程序中设置字符编码格式,按此格式解析,完整程序如下:
- use strict;
- use Spreadsheet::ParseExcel;
- use Spreadsheet::ParseExcel::FmtUnicode; #字符编码
-
- my $parser = Spreadsheet::ParseExcel->new();
- my $formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"CP936");#设置字符编码
- #my $workbook = $parser->parse('Book.xls');
- my $workbook = $parser->parse('Book.xls', $formatter);#按所设置的字符编码解析
-
- if ( !defined $workbook ) {
- die $parser->error(), ".\n";
- }
- 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 $cell->value()," ";
- }
- print "\n";
- }
- }
ok,显示正常:
阅读(8020) | 评论(2) | 转发(2) |