Home > Archive > PERL Beginners > July 2007 > Spreadsheet::WriteExcel problem with large file
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Spreadsheet::WriteExcel problem with large file
|
|
| A_arya2000 2007-07-16, 6:59 pm |
| Hi, the problem is, when write large spreadsheets of
20,000 rows or so, the module finishes without error,
but when I try to open the spreadsheet with Excel I
get error messages about the spreadsheet being
'unrecoverably scrambled'. I've never had a problem
with it except for these very large spreadsheets. It
works great on smaller ones.
Is there any work around for this? Or Is there any
other module that will do that work?
Thank you very much.
Arya
________________________________________
________________________________________
____
8:00? 8:25? 8:40? Find a flick in no time
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news
| |
| Shrinivasrao P. Mane 2007-07-16, 6:59 pm |
| Rob Dixon wrote:
> a_arya2000 wrote:
>
>
>
> Try calling the 'close' method on your workbook, and see the module's
> documentation on this - particularly with regard to garbage collection.
>
> $workbook->close;
>
> HTH,
>
> Rob
>
Are you using Spreadsheet::WriteExcel::Big ?
I have written ~95K rows with no problem.
As rob suggested worksheet->close() might fix your problem.
SPM
| |
| A_arya2000 2007-07-16, 6:59 pm |
| Thank you very much for suggestions guys, I tried
using Spreadsheet::WriteExcel::Big but still having
the same issue. I am not sure, what I am doing wrong.
My file size is indeed more than 7 MB.
Thank you,
Partha Dhar
--- Raja Vadlamudi <rvadlamudi@cedardoc.com> wrote:
> Use Spreadsheet::Big module if the file size happens
> to be larger than 7 MB.
> The latest Spreadsheet module will have this Big
> module integrated, but if
> you are using older version, then you need to use
> this Big module.
>
>
> On 7/16/07 8:46 AM, "Rob Dixon" <rob.dixon@350.com>
> wrote:
>
> of 20,000 rows or
> try to open the
> the spreadsheet
> problem with it
> works great on smaller
> any other module that
> and see the module's
> to garbage collection.
>
>
________________________________________
________________________________________
____
Get the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolba.../mail/index.php
| |
| Chas Owens 2007-07-16, 6:59 pm |
| On 7/16/07, a_arya2000 <a_arya2000@yahoo.com> wrote:
> Thank you very much for suggestions guys, I tried
> using Spreadsheet::WriteExcel::Big but still having
> the same issue. I am not sure, what I am doing wrong.
> My file size is indeed more than 7 MB.
> Thank you,
> Partha Dhar
>
Did you try the suggestion about calling $workbook->close before the
end of the program?
You can ensure it is called before the end of the program by saying
my $workbook = Spreadsheet::WriteExcel->new("file.xls");
END { $workbook->close}
This can be useful if you have many possible exits from the program.
| |
| A_arya2000 2007-07-16, 6:59 pm |
| By the way, my file size around 22 MB so, is it
possible that Spreadsheet::WriteExcel::Big is not good
enough for this size?
--- a_arya2000 <a_arya2000@yahoo.com> wrote:
> Thank you very much for suggestions guys, I tried
> using Spreadsheet::WriteExcel::Big but still having
> the same issue. I am not sure, what I am doing
> wrong.
> My file size is indeed more than 7 MB.
> Thank you,
> Partha Dhar
>
> --- Raja Vadlamudi <rvadlamudi@cedardoc.com> wrote:
>
> happens
> <rob.dixon@350.com>
> spreadsheets
> I
> about
> a
>
>
>
>
>
________________________________________
________________________________________
____
> Get the Yahoo! toolbar and be alerted to new email
> wherever you're surfing.
>
http://new.toolbar.yahoo.com/toolba.../mail/index.php
>
> --
> To unsubscribe, e-mail:
> beginners-unsubscribe@perl.org
> For additional commands, e-mail:
> beginners-help@perl.org
> http://learn.perl.org/
>
>
>
________________________________________
________________________________________
____
Get the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolba.../mail/index.php
| |
| Wagner, David --- Senior Programmer Analyst --- WG 2007-07-16, 6:59 pm |
| > -----Original Message-----
> From: a_arya2000 [mailto:a_arya2000@yahoo.com]=20
> Sent: Monday, July 16, 2007 14:06
> To: beginners@perl.org
> Cc: a_arya2000
> Subject: Re: Spreadsheet::WriteExcel problem with large file
>=20
> By the way, my file size around 22 MB so, is it
> possible that Spreadsheet::WriteExcel::Big is not good
> enough for this size?
how many rows are you talking about? If over 65k, then need to
make multiple worksheets as you can not go over 65k rows per worksheet.
Wags ;)
David R Wagner
Senior Programmer Analyst
FedEx Freight
1.408.323.4225x2224 TEL
1.408.323.4449 FAX
http://fedex.com/us=20
>=20
> --- a_arya2000 <a_arya2000@yahoo.com> wrote:
>=20
> ________________________________________
______________________
> ______________________
> http://new.toolbar.yahoo.com/toolba.../mail/index.php
>=20
>=20
>=20
> =20
> ________________________________________
______________________
> ______________________
> Get the Yahoo! toolbar and be alerted to new email wherever=20
> you're surfing.
> http://new.toolbar.yahoo.com/toolba.../mail/index.php
>=20
> --=20
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> http://learn.perl.org/
>=20
>=20
>=20
****************************************
******************************
This message contains information that is confidential and proprietary to F=
edEx Freight or its affiliates. It is intended only for the recipient name=
d and for the express purpose(s) described therein. Any other use is proh=
ibited.
****************************************
******************************
| |
| A_arya2000 2007-07-16, 6:59 pm |
| I have like around 24K rows. Thank you.
Arya
--- "Wagner, David --- Senior Programmer Analyst ---
WGO" <David.Wagner@freight.fedex.com> wrote:
> large file
> good
> how many rows are you talking about? If over 65k,
> then need to
> make multiple worksheets as you can not go over 65k
> rows per worksheet.
>
> Wags ;)
> David R Wagner
> Senior Programmer Analyst
> FedEx Freight
> 1.408.323.4225x2224 TEL
> 1.408.323.4449 FAX
> http://fedex.com/us
>
> tried
> having
> wrote:
> Big
> use
> when
> had
> It
> there
> workbook,
> regard
>
________________________________________
______________________
> email
>
http://new.toolbar.yahoo.com/toolba.../mail/index.php
>
________________________________________
______________________
> wherever
>
http://new.toolbar.yahoo.com/toolba.../mail/index.php
> beginners-unsubscribe@perl.org
> beginners-help@perl.org
>
>
****************************************
******************************
> This message contains information that is
> confidential and proprietary to FedEx Freight or its
> affiliates. It is intended only for the recipient
> named and for the express purpose(s) described
> therein. Any other use is prohibited.
>
****************************************
******************************
>
>
> --
> To unsubscribe, e-mail:
> beginners-unsubscribe@perl.org
> For additional commands, e-mail:
> beginners-help@perl.org
> http://learn.perl.org/
>
>
>
________________________________________
________________________________________
____Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
| |
| Chas Owens 2007-07-16, 6:59 pm |
| On 7/16/07, a_arya2000 <a_arya2000@yahoo.com> wrote:
> By the way, my file size around 22 MB so, is it
> possible that Spreadsheet::WriteExcel::Big is not good
> enough for this size?
snip
What follows is a test script you can use to determine if it is your
code or Spreadsheet::WriteExcel::Big (or possibly Excel) that is at
fault. When run it will create a file that is roughly 21 megs in
size. The size can be increased or decreased by modifying
$string_size. If Excel can open the file then your code is at fault,
if it gives you the same error then your version of either
Spreadsheet::WriteExcel::Big or Excel has a bug.
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel::Big;
my $string_size = 300;
my $wb = Spreadsheet::WriteExcel::Big->new('test.xls');
my $ws = $wb->add_worksheet();
for my $row (1 .. 65536) { #every row in an excel spread sheet
$ws->write("A$row", rand_str($string_size));
}
$wb->close;
sub rand_str {
my $size = shift;
my @a = ('A' .. 'Z', 'a' .. 'z', 0 .. 9);
return join '', map { $a[int rand @a] } 1 .. $size
}
| |
| Ken Foskey 2007-07-16, 10:00 pm |
| On Mon, 2007-07-16 at 14:23 -0700, a_arya2000 wrote:
> I have like around 24K rows. Thank you.
Sounds like a design problem. Use a database and connect the excel
spreadsheet to that data source. Seems a lot easier.
--
Ken Foskey
FOSS developer
| |
| Chas Owens 2007-07-16, 10:00 pm |
| On 7/16/07, Chas Owens <chas.owens@gmail.com> wrote:
> On 7/16/07, a_arya2000 <a_arya2000@yahoo.com> wrote:
> snip
>
> What follows is a test script you can use to determine if it is your
> code or Spreadsheet::WriteExcel::Big (or possibly Excel) that is at
> fault. When run it will create a file that is roughly 21 megs in
> size. The size can be increased or decreased by modifying
> $string_size. If Excel can open the file then your code is at fault,
> if it gives you the same error then your version of either
> Spreadsheet::WriteExcel::Big or Excel has a bug.
snip
I didn't like the program, so I changed it to be faster and more predictable.
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel::Big;
my $string_size = 300;
my $wb = Spreadsheet::WriteExcel::Big->new('test.xls');
my $ws = $wb->add_worksheet();
my $data = 'a' x $string_size;
for my $row (1 .. 65536) { #every row in an excel spread sheet
$ws->write("A$row", scalar reverse $data++);
}
$wb->close;
| |
| Chas Owens 2007-07-16, 10:00 pm |
| On 7/16/07, a_arya2000 <a_arya2000@yahoo.com> wrote:
> Thanks for your extra ordinary help guys.
> Actually, I already run similar type of script which
> created a spreadsheet with maximum member of rows and
> columns, the file size was 136 MB, it worked fine.
> Now you can conclude there is something wrong with my
> script. However, when the script read a txt file with
> around 19K/20K rows, and create a spreadsheet out of
> it (with same number of rows & columns) worked
> perfectly, but when I use a txt file with around 24K
> rows, that's when the problem occurs. It doesn't give
> any error message while running, but when I try to
> open the generated spreadsheet file, it opened with
> some error message. I really don't have any clue
> what's going on.
Without the code we are limited in the help we can provide. If you
could provide the code and a description of the data being fed into it
we may be able to help more.
| |
| A_arya2000 2007-07-16, 10:00 pm |
| Thanks for your extra ordinary help guys.
Actually, I already run similar type of script which
created a spreadsheet with maximum member of rows and
columns, the file size was 136 MB, it worked fine.
Now you can conclude there is something wrong with my
script. However, when the script read a txt file with
around 19K/20K rows, and create a spreadsheet out of
it (with same number of rows & columns) worked
perfectly, but when I use a txt file with around 24K
rows, that’s when the problem occurs. It doesn’t give
any error message while running, but when I try to
open the generated spreadsheet file, it opened with
some error message. I really don’t have any clue
what’s going on.
--- Chas Owens <chas.owens@gmail.com> wrote:
> On 7/16/07, Chas Owens <chas.owens@gmail.com> wrote:
> wrote:
> not good
> determine if it is your
> Excel) that is at
> roughly 21 megs in
> modifying
> your code is at fault,
> of either
> snip
>
> I didn't like the program, so I changed it to be
> faster and more predictable.
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
>
> use Spreadsheet::WriteExcel::Big;
>
> my $string_size = 300;
>
> my $wb =
> Spreadsheet::WriteExcel::Big->new('test.xls');
> my $ws = $wb->add_worksheet();
> my $data = 'a' x $string_size;
> for my $row (1 .. 65536) { #every row in an excel
> spread sheet
> $ws->write("A$row", scalar reverse $data++);
> }
> $wb->close;
>
________________________________________
________________________________________
____
Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/ya..._invite.asp?a=7
|
|
|
|
|