/[webpac2]/trunk/lib/WebPAC/Input/Excel.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /trunk/lib/WebPAC/Input/Excel.pm

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 498 by dpavlin, Sun May 14 19:45:45 2006 UTC revision 728 by dpavlin, Fri Sep 29 19:52:26 2006 UTC
# Line 3  package WebPAC::Input::Excel; Line 3  package WebPAC::Input::Excel;
3  use warnings;  use warnings;
4  use strict;  use strict;
5    
 use WebPAC::Input;  
6  use Spreadsheet::ParseExcel;  use Spreadsheet::ParseExcel;
7  use Spreadsheet::ParseExcel::Utility qw/int2col/;  use Spreadsheet::ParseExcel::Utility qw/int2col/;
8    use base qw/WebPAC::Common/;
9    
10  =head1 NAME  =head1 NAME
11    
# Line 13  WebPAC::Input::Excel - support for Micro Line 13  WebPAC::Input::Excel - support for Micro
13    
14  =head1 VERSION  =head1 VERSION
15    
16  Version 0.01  Version 0.04
17    
18  =cut  =cut
19    
20  our $VERSION = '0.01';  our $VERSION = '0.04';
21    
22    
23  =head1 SYNOPSIS  =head1 SYNOPSIS
# Line 25  our $VERSION = '0.01'; Line 25  our $VERSION = '0.01';
25  Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org  Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org
26  or Gnuemeric) in C<.xls> format.  or Gnuemeric) in C<.xls> format.
27    
  my $isis = new WebPAC::Input::Excel();  
  $isis->open( path => '/path/to/workbook.xls' );  
   
28  =head1 FUNCTIONS  =head1 FUNCTIONS
29    
30  =head2 open_db  =head2 new
31    
32  Returns handle to database and size  Returns handle to database and size
33    
34    my ($db,$size) = $open_db(    my $excel = new WebPAC::Input::Excel(
35          path => '/path/to/workbook.xls'          path => '/path/to/workbook.xls'
36          worksheet => 'name of sheet',          worksheet => 'name of sheet',
37            from => 42,
38            to => 9999,
39    }    }
40    
41  C<worksheet> is case and white-space insensitive name of worksheet in Excel  C<worksheet> is case and white-space insensitive name of worksheet in Excel
42  file to use. If not specified, it will use first worksheet in file.  file to use. If not specified, it will use first worksheet in file.
43    
44  =cut  C<from> and C<to> specify row numbers to start and finish import.
   
 my $sheet;  
45    
46  sub open_db {  =cut
         my $self = shift;  
47    
48          my $arg = {@_};  sub new {
49            my $class = shift;
50            my $self = {@_};
51            bless($self, $class);
52    
53          my $log = $self->_get_logger();          my $log = $self->_get_logger();
54    
55          $log->logdie("can't open excel file $arg->{path}: $!") unless (-r $arg->{path} && -f $arg->{path});          $log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path});
56    
57          my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($arg->{path});          my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path});
58    
59          my ($size, $wanted_worksheet);          my $sheet;
60            my $wanted_worksheet;
61    
62          if ($wanted_worksheet = $arg->{worksheet}) {          if ($wanted_worksheet = $self->{worksheet}) {
63                  my $name;                  my $name;
64                  do {                  do {
65                          $sheet = shift @{ $workbook->{Worksheet} };                          $sheet = shift @{ $workbook->{Worksheet} };
66                            $log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet));
67                          $name = $sheet->{Name};                          $name = $sheet->{Name};
68                          $name =~ s/\s\s+/ /g;                          $name =~ s/\s\s+/ /g;
69                  } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);                  } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);
# Line 73  sub open_db { Line 74  sub open_db {
74                    
75          }          }
76    
77          $size = $sheet->{MaxRow} - $sheet->{MinRow};          $self->{sheet} = $sheet;
78    
79            $self->{from} ||= $sheet->{MinRow};
80            $self->{to} ||= $sheet->{MaxRow};
81    
82            my $size = $self->{to} - $self->{from};
83            $self->{size} = $size;
84    
85          $log->warn("opening Excel file '$arg->{path}', using ",          $log->warn("opening Excel file '$self->{path}', using ",
86                  $wanted_worksheet ? '' : 'first ',                  $wanted_worksheet ? '' : 'first ',
87                  "worksheet: $sheet->{Name} [$size rows]"                  "worksheet: $sheet->{Name} [$size rows]"
88          );          );
89    
90          return (42, $size);          $self ? return $self : return undef;
91  }  }
92    
93  =head2 fetch_rec  =head2 fetch_rec
94    
95  Return record with ID C<$mfn> from database  Return record with ID C<$mfn> from database
96    
97    my $rec = $self->fetch_rec( $db, $mfn );    my $rec = $self->fetch_rec( $mfn );
98    
99  }  }
100    
# Line 96  Return record with ID C<$mfn> from datab Line 103  Return record with ID C<$mfn> from datab
103  sub fetch_rec {  sub fetch_rec {
104          my $self = shift;          my $self = shift;
105    
106          my (undef, $mfn) = @_;          my $mfn = shift;
107    
108          my $log = $self->_get_logger();          my $log = $self->_get_logger();
109    
110            my $sheet = $self->{sheet};
111          $log->logdie("can't find sheet hash") unless (defined($sheet));          $log->logdie("can't find sheet hash") unless (defined($sheet));
112          $log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));          $log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));
113    
114          my $rec;          my $rec;
115    
116          my $row = $sheet->{MinRow} + $mfn - 1;          my $row = $self->{from} + $mfn - 1;
117    
118          $log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});          $log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});
119    
# Line 116  sub fetch_rec { Line 124  sub fetch_rec {
124          }          }
125    
126          # add mfn only to records with data          # add mfn only to records with data
127          $rec->{'000'} = $mfn if ($rec);          $rec->{'000'} = [ $mfn ] if ($rec);
128                    
129          return $rec;          return $rec;
130  }  }
131    
132    =head2 size
133    
134    Return number of records in database
135    
136      my $size = $isis->size;
137    
138    =cut
139    
140    sub size {
141            my $self = shift;
142            return $self->{size};
143    }
144  =head1 AUTHOR  =head1 AUTHOR
145    
146  Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>  Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>
Legend:
Removed from v.498  
changed lines
  Added in v.728
  ViewVC Help
Powered by ViewVC 1.1.26