1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

regarding writing excel with more than 7mb size data

Discussion in 'Perl' started by sriv, Dec 18, 2007.

  1. sriv

    sriv New Member

    Dec 18, 2007
    Likes Received:
    Trophy Points:
    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?
  2. naveen

    naveen New Member

    Jun 2, 2005
    Likes Received:
    Trophy Points:
    Calcutta, India
    Home Page:
    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=use+Spreadsheet;&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.
  3. sriv

    sriv New Member

    Dec 18, 2007
    Likes Received:
    Trophy Points:
    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
    Last edited by a moderator: Dec 19, 2007

Share This Page