- 论坛徽章:
- 0
|
本帖最后由 yiten 于 2010-12-23 15:30 编辑
我这边有现成的,做下好人
- # parse excel 2003 file and return the data
- # examples:
- # 1. read the first sheet data: read_excel_2003('abc.xls'), return reference of sheet data
- # 2. read all the sheet data: read_excel_2003( 'abc.xls', 1 ), return array reference contains reference of every sheet data
- # 3. read specified sheet data: read_excel_2003( 'abc.xls', [ 0, 1, 4 ] ), return hash reference contains reference of the specified sheet data
- sub read_excel_2003 {
- my ( $file, $sheet_var ) = @_;
- die "File is not found: $file." unless -e $file;
- my $parser = Spreadsheet::ParseExcel->new();
- my $fmt = Spreadsheet::ParseExcel::FmtUnicode->new( Unicode_Map => 'CP936' );
- my $workbook = $parser->Parse( $file, $fmt );
- die $parser->error() unless $workbook;
- unless ( $sheet_var ) {
- return _parse_sheet( $workbook->worksheet(0) );
- }
- elsif ( !ref $sheet_var ) {
- my @result_array = ();
- for my $worksheet ( $workbook->worksheets() ) {
- push @result_array, _parse_sheet($worksheet);
- }
- return \@result_array;
- }
- elsif ( ref $sheet_var eq 'ARRAY' ) {
- my %result_hash = ();
-
- for my $sheet_num ( @$sheet_var ) {
- my $worksheet = $workbook->worksheet($sheet_num);
- $result_hash{$sheet_num} = _parse_sheet($worksheet) if $worksheet;
- }
- return \%result_hash;
- }
- }
- sub _parse_sheet {
- my $worksheet = shift;
- my @sheet_array = ();
- my ( $row_min, $row_max ) = $worksheet->row_range();
- my ( $col_min, $col_max ) = $worksheet->col_range();
- for my $row ( $row_min .. $row_max ) {
- my @row_array = ();
- for my $col ( $col_min .. $col_max ) {
- my $cell = $worksheet->get_cell( $row, $col );
- if ($cell) {
- if ( $cell->type() eq 'Date' ) {
- push @row_array, ExcelFmt( 'yyyy-mm-dd HH:MM:SS', $cell->unformatted() );
- }
- else {
- push @row_array, $cell->value();
- }
- }
- else {
- push @row_array, undef;
- }
- }
- push @sheet_array, \@row_array;
- }
- return \@sheet_array;
- }
复制代码 |
|