介绍两个perl中的包，分别可以读取Excel的文件，包括2003 的xls文件，和2007的xlsx的文件。Spreadsheet::ParseExcel;
Spreadsheet::ParseXLSX;
介绍两个perl中的两个包，分别可以读取Excel的文件，包括2003 的xls文件Spreadsheet::ParseExcel，和2007的xlsx的文件Spreadsheet::ParseXLSX。
use Spreadsheet::ParseExcel;
#读取Excel2003版本的excel文件
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('test.xls');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
my $worksheet = $workbook->worksheet('Sheet1');
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
printf "%15s %15s %15s",'first_row','second_row','third_row'."\n";
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
my $va=$cell->value();
printf "%15s",$va;
}
say;
}
use Spreadsheet::ParseXLSX;
#读取Excel2007版本的excel文件 用法差不多
my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse("file.xlsx");
my $worksheet = $workbook->worksheet('Sheet1');
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
printf "%15s %15s %15s",'first_row','second_row','third_row'."\n";
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
my $va=$cell->value();
printf "%15s",$va;
}
say;
}
对应还有写出excel的包：Spreadsheet::WriteExcel（2003） 和Excel::Writer::XLSX （2007）版本用法是一样的：
参考代码：
use Spreadsheet::WriteExcel;
# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new('perl.xls');
# Add a worksheet
$worksheet = $workbook->add_worksheet();
# Add and define a format
$format = $workbook->add_format(); # Add a format
$format->set_bold();
$format->set_color('red');
$format->set_align('center');
# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write($row, $col, 'Hi Excel!', $format);
$worksheet->write(1, $col, 'Hi Excel!');
# Write a number and a formula using A1 notation
$worksheet->write('A3', 1.2345);
$worksheet->write('A4', '=SIN(PI()/4)'); |
参考代码：
use Excel::Writer::XLSX;
# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
# Add a worksheet
$worksheet = $workbook->add_worksheet();
# Add and define a format
$format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$format->set_align( 'center' );
# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write( $row, $col, 'Hi Excel!', $format );
$worksheet->write( 1, $col, 'Hi Excel!' );
# Write a number and a formula using A1 notation
$worksheet->write( 'A3', 1.2345 );
$worksheet->write( 'A4', '=SIN(PI()/4)' );
$workbook->close(); |
https://metacpan.org/pod/Excel::Writer::XLSX#Excel::Writer::XLSX-and-Spreadsheet::WriteExcel
