在Linux或者Unix上操作(生成)Excel,怎么说都是比较困难的困难的事情。但是有cpan,困难迎刃而解了!CPAN上提供了
Spreadsheet::WriteExcel 和 Spreadsheet::ParseExcel这两个模块。下面我们就来看看
Spreadsheet::WriteExcel 和 Spreadsheet::ParseExcel的使用方法:
2、使用读取EXCEL文件:
3、生成EXCEL文件:
1、首先,我们要在服务器上安装相应的模块
- ppm>
install OLE::Storage_Lite - ppm>
install Spreadsheet::ParseExcel - ppm>
install Spreadsheet::WriteExcel
- #!/usr/bin/perl
-w -
-
use strict; -
use Spreadsheet::ParseExcel; -
-
my $parser = Spreadsheet::ParseExcel->new(); -
my $workbook = $parser->Parse('Book1.xls'); -
-
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 "Unformatted = ", $cell->unformatted(), "\n"; -
print "\n"; -
} -
} -
}
- #!/usr/bin/perl
-w -
- use
Spreadsheet::WriteExcel; -
-
# 创建一个新的EXCEL文件 -
my $workbook = Spreadsheet::WriteExcel->new('test.xls'); -
-
# 添加一个工作表 -
$worksheet = $workbook->add_worksheet(); -
-
# 新建一个样式 -
$format = $workbook->add_format(); # Add a format -
$format->set_bold();#设置字体为粗体 -
$format->set_color('red');#设置单元格前景色为红色 -
$format->set_align('center');#设置单元格居中 -
- #使用行号及列号,向单元格写入一个格式化和末格式化的字符串
-
$col = $row = 0; -
$worksheet->write($row, $col, 'Hi Excel!', $format); -
$worksheet->write(1, $col, 'Hi Excel!'); -
-
# 使用单元格名称(例:A1),向单元格中写一个数字。 -
$worksheet->write('A3', 1.2345); -
$worksheet->write('A4', '=SIN(PI()/4)'); -
exit;
my $format = $workbook->add_format( border => 6, valign => 'vcenter', align => 'center', );$worksheet->merge_range('B3:D4', 'Vertical and horizontal', $format); 5、设定列宽 $worksheet->set_column($begin_loc,$end_loc,30); 6、设定格式,不改单元格内容 $worksheet->write_blank($iR,$iC,$format); 7、关闭表格$workbook->close();