Go4Expert (http://www.go4expert.com/)
-   Perl (http://www.go4expert.com/forums/perl/)
-   -   regarding writing excel with more than 7mb size data (http://www.go4expert.com/forums/regarding-writing-excel-7mb-size-data-t7910/)

sriv 18Dec2007 16:19

regarding writing excel with more than 7mb size data
Hi ,
i am using Spreadsheet module for write/read excel files.
but when the data is more than 7 mb then it is not writing the content to the excel sheet.
how can i write content with more than 7 mb size ?
please can anyone help in this issue?

naveen 18Dec2007 18:10

Re: regarding writing excel with more than 7mb size data
1) There is no Spreadsheet module at CPAN. There are 100s of other modules such as Spreadsheet::Perl, Spreadsheet::Read etc. which do the job. Check out http://search.cpan.org/search?query=...et%3B&mode=all

So, it maybe that you are using a non-standard module which is not present at CPAN.

2) The problem which you state, seems to be a result of inefficient coding, rather than a module bug (I may be wrong though). You need to post the code which you are using and also the module (or a link to it), so that we can try to review and see where lies the problem.

In the meantime, you may try to optimize your code by reading from/writing to excel files within while loops, instead of holding them in the memory - if it is not essential.

sriv 19Dec2007 18:28

Re: regarding writing excel with more than 7mb size data
Hi naveen,
it is working fine for me if the number of loops from database lessthan 30000.
if the limit increase then file itself is not creating.

following is the coding...... i am getting the problem at saving the file at last line.
i have posted just part of the code.....which is using for generating the excel file..
please look at once and let me know if anything wrong..

use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::SaveParser;
sub generate {
        my($dbc, $cgi, $authstring, $LINK, $username, $FUNC2, $LINK2) = @_;
        my ($pi_id) = $cgi->param('pi_select');
        my($iFmt, $debug);

        my $assetpi_xls = 'Report/repor.xls';
        $debug = 0;
    my $oExcel = new Spreadsheet::ParseExcel::SaveParser;

        my $oBook = $oExcel->Parse($assetpi_xls)  || die("Parse error: $assetpi_xls");
        if(!($oBook)) {
                $oBook = Spreadsheet::ParseExcel::Workbook->Parse("$assetpi_xls");

        if(!($oBook)) {
                die ("Error: $assetpi_xls read error");

        my $time = time;

        my $date = localtime($time);

        my $oWkS = $oBook->{Worksheet}[0];
        my $name = $oWkS->{Name};
        my $q;

        my $skip = 4;
        my($row, $col);
        my $query = qq{$query};
        my($rows, $count) = $dbc->fetchAll($query);

    for my $i (0 .. $#$rows) {

                $row = $skip + $i;
                $col = -1;

                $iFmt = $oBook->{Worksheet}[0]->{Cells}[$row][0];
                $oBook->AddCell(0, $row, 0, $row, $iFmt);
        foreach my $val (@{ $rows }[$i]) {
            $iFmt = $oBook->{Worksheet}[0]->{Cells}[$row][$col];
            $oBook->AddCell(0, $row, $col, $val, $iFmt);
    $iFmt = $oBook->{Worksheet}[0]->{Cells}[1][7];
    $oBook->AddCell(0, 1, 1, $date, $iFmt);
    $iFmt = $oBook->{Worksheet}[0]->{Cells}[1][1];
        $oExcel->SaveAs($oBook, qq{/tmp/temp.xls.$time}); # time stamped. the streaming funtion will delete this file once complete
        // here the file is saving with 0 size and deleting automatically thi is the error i am getting

All times are GMT +5.5. The time now is 13:55.