I'm trying to write a large Excel file using Spreadsheet::ParseExcel::SaveParser. But I'm getting an error that says I can't write a file over 7MB.

Error:

Maximum Spreadsheet::WriteExcel filesize, 7087104 bytes, exceeded. To 
+create files bigger than this limit please refer to the "Spreadsheet:
+:WriteExcel::Big" documentation.
[download]

I see it uses OLE::Storage_Lite in place of depreciated Spreadsheet::WriteExcel::Big to overcome the 7MB limit. I don't see how to apply the OLE::Storage_Lite from the Docs.

I'm reading a 150kb file, saving it as another name, and then writing to the new file about 55,000 rows with 26 columns.

Can someone kindly show me how I get this to work?

#!/usr/bin/perl -w

use strict;
use My::DB;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::ParseExcel::SaveParser::Workbook;

#  make new connection to database
my $dbh = My::DB->new( { server => 'my_server', db => 'my_database' } 
+);

#  data query
my $sth_query = $dbh->prepare("
         SELECT
                Data_1,
                Data_2
        FROM
                My_Table
        WHERE 1
");

# execute query
$sth_query->execute;


# Open the template with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new;
my $template = $parser->Parse('./data/worksheet_11_21_2008.xls');


my $workbook;
{
        # SaveAs generates a lot of harmless warnings about unset
        # Worksheet properties. You can ignore them if you wish.
        local $^W = 0;

        # Rewrite the file or save as a new file
        $workbook = $template->SaveAs('./data/new_worksheet_11_21_2008
+.xls');
}


# get the first worksheet
my $worksheet  = $workbook->sheets(0);

#  start writing here
my $row = 7;

while ( my @row = $sth_query->fetchrow ) {

     my ( $data_1, $data_2 ) = @row;

     #  write the data to the excel file
     $worksheet->write( $row, 1, $data_1 );
     $worksheet->write( $row, 2, $data_2 );

     #  increment to next excel row
     $row++;

}

#  close workbook
$workbook->close();
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.