3 =head1 NAME $RCSfile: SpreadSheet.pm,v $
5 Object oriented interface to Excel Spreadsheets
13 Andrew DeFaria <Andrew@ClearSCM.com>
21 Tue Nov 20 10:40:53 PST 2012
25 $Date: 2012/11/21 02:53:06 $
31 Provides access to Excel Spreadsheets
33 # Create SpreadSheet object
34 my $ss = SpreadSheet->new ($file)
37 my @rows = $ss->getData ($sheetName);
43 display "$_: $row{$_}";
49 This module provides a simple, object oriented interface to a SpreadSheet.
53 The following routines are exported:
69 use Win32::OLE::Const 'Microsoft Excel';
72 my ($self, $errmsg, $error) = @_;
74 $self->{errmsg} = $errmsg;
75 $self->{error} = $error;
83 undef $self->{excel} if $self->{excel};
87 my ($class, $filename) = @_;
93 Construct a new SpreadSheet object.
97 =for html <blockquote>
103 Pathname to the spreadsheet file
107 =for html </blockquote>
111 =for html <blockquote>
115 =item SpreadSheet object
119 =for html </blockquote>
124 filename => $filename,
125 excel => Win32::OLE->new ('Excel.Application', 'Quit'),
128 # Excel needs a Windows based absolute path
129 if ($^O eq 'cygwin') {
130 my @output = `cygpath -wa $self->{filename}`;
133 $self->{filename} = $output[0];
137 Cwd->import ('abs_path');
139 $self->{filename} = abs_path ($self->{filename});
142 $self->{book} = $self->{excel}->Workbooks->Open ($self->{filename});
144 $self->_setError ("Unable to open spreadsheet $self->{filename}", 1)
145 unless $self->{book};
151 my ($self, $sheet) = @_;
157 Return the data in the sheet specified
161 =for html <blockquote>
167 The name of the sheet
171 =for html </blockquote>
175 =for html <blockquote>
181 Array of rows each represented by a hash. Note this assumes that the first row
182 are field headings and are used as the keys for the hash.
186 =for html </blockquote>
192 unless ($self->{book}) {
193 $self->_setError ("Failed to open SpreadSheet ($self->{filename})", 1);
199 $self->{sheet} = $self->{book}->Worksheets->Item ($sheet);
203 $self->{sheet} = $self->{book}->Worksheets (1);
206 unless ($self->{sheet}) {
207 $self->_setError ("Unable to get sheet $sheet from spreadsheet $self->{filename}", 1);
212 # Now parse the spreadsheet
213 my $lastRow = $self->{sheet}->UsedRange->Find ({
215 SearchDirection => xlPrevious,
216 SearchOrder => xlByRows,
218 my $lastColumn = $self->{sheet}->UsedRange->Find ({
220 SearchDirection => xlPrevious,
221 SearchOrder => xlByColumns,
224 # Find columns by headings
225 my (@fields, $row, $column);
227 for ($column = 1; $column <= $lastColumn; $column++) {
228 $fields[$column - 1] = $self->{sheet}->Cells (1, $column)->{Value};
232 for ($row = 2; $row <= $lastRow; $row++) {
235 for ($column = 1; $column <= $lastColumn; $column++) {
236 $row{$fields[$column - 1]} =
237 $self->{sheet}->Cells ($row, $column)->{Value};
239 $row{$fields[$column - 1]} ||= '';