> -----Original Message-----
> From: Stewart Anderson
> Sent: 22 July 2008 09:34
> To: ANJAN PURKAYASTHA; beginners@perl.org
> Cc: Stewart Anderson
> Subject: RE: parsing a large excel file
>
>
> > -----Original Message-----
> > From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED]
> > Sent: 22 July 2008 02:41
> > To: beginners@perl.org
> > Subject: parsing a large excel file
> >
> > to all,
> > i have installed Spreadshee::ParseExcel to parse some large excel
data
> > files.
> > Here is the problem I'm facing. I need to parse data from columns M
to P
> > and
> > rows 10 to 43000. Now I know that there is a PrintArea method that
can
> > print
> > an area of a worksheet specified in (start row, start col, end row,
end
> > col). However I'm having difficulty in specifying these parameters
> > correctly
> > for the PrintArea method. I'm also not sure what the output is going
to
> > look
> > like.
> > does anyone in this forum have any pointers?
> > all advice will be appreciated.
> > tia,
> > anjan
> >
> I have only used the Simple version previously to parse and entire
row
> but the method Cell ( ROW, COL ) In the docs for the module you
> showed suggests you can get at the data directly.
>
> Stu
>
>
Maybe I spoke to soon, that method seem to return the iobject, not
100% sure what that gives you without trying it.
But the sample script in the doc looks like a good place to start,
just set your row min/max and col min/max and you should be close to
getting something.
However, the sample program in the docs work as is. It should be
easy to adapt it for your needs. Try this as a start. The first bit is
pretty much out of the box and the last bit just shows you can extract
what row/column you want.
The data is at the end, load it into excel.
#! /usr/bin/perl
use warnings;
use strict ;
use Data::Dumper;
use Spreadsheet::ParseExcel;
my $excel =
Spreadsheet::ParseExcel::Workbook->Parse('c:\temp\filetest.xls');
foreach my $sheet (@{$excel->{Worksheet}}) {
printf("Sheet: %s\n", $sheet->{Name});
$sheet->{MaxRow} ||= $sheet->{MinRow};
foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
$sheet->{MaxCol} ||= $sheet->{MinCol};
foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
my $cell = $sheet->{Cells}[$row][$col];
if ($cell) {
printf("( %s , %s ) => %s\n", $row, $col,
$cell->{Val});
}
}
}
}
print "Extract specific row/cell row 2, col b \n" ;
foreach my $sheet (@{$excel->{Worksheet}}) {
my $row = 2;
my $col = 2;
my $cell = $sheet->{Cells}[$row][$col];
printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
}
Data to load in excel
1a,1b,1c,1d,1e,1f
2a,2b,3c,4d,2e,2f
3a,3b,3c,3d,3e,3f
4a,4b,4c,4d,4e,4f
Information in this email including any attachments may be privileged,
confidential and is intended exclusively for the addressee. The views expressed
may not be official policy, but the personal views of the originator. If you
have received it in error, please notify the sender by return e-mail and delete
it from your system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and
are used under licence. British Sky Broadcasting Limited (Registration No.
2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home
Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of British Sky
Broadcasting Group plc (Registration No. 2247735). All of the companies
mentioned in this paragraph are incorporated in England and Wales and share the
same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
