1
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new('test.xlsx');
$workbook->set_optimization();
my $row = 0;
my $col = 0;
$row++;
my $worksheet = $workbook->add_worksheet();
foreach ( sort { lc($a) cmp lc($b); } keys %hash ) {
    my $value = $_;
    $col = 0;
    my @array = split( '\t', $value );
    foreach my $val (@array) {
        $worksheet->write( $row, $col, $val );
        $col++;
    }
    $row++;
}
$workbook->close();

I have stored huge data (>70MB) in a hash and by using above code I am gonna write data to excel file. But its showing out of memory error. Kindly help me to fix the this issue.thanks

Miller
  • 34,962
  • 4
  • 39
  • 60
Bharu
  • 19
  • 3
  • How are you generating the hash that is giving you the 'out of memory' error? If using Excel::Writer::XLSX is causing the out-of-memory error, why don't you create a CSV or tab-delimited file and then import it into Excel? – i alarmed alien Sep 25 '14 at 08:18
  • 1
    You might want to do memory profiling. http://stackoverflow.com/a/1360142/223226 – mpapec Sep 25 '14 at 08:27
  • I read data from excel and done some calculation and stored in hash. now i have to write the data to excel with required information[Template] – Bharu Sep 25 '14 at 08:51
  • 1
    Please post the code that is creating the hash, too. – i alarmed alien Sep 25 '14 at 09:09
  • Below code will create hash for my $row (1..$max_rows) { my @array=(); for my $col(1..$max_cols){ my $value= $worksheet->{'cell'}[$col][$row]; if($col>2&$col<15){ $value=$value*"1000000"; } #print $value."\t"; push @array,$value; } my $string = join("\t",@array); $hash{$string}=1; } – Bharu Sep 25 '14 at 09:46

1 Answers1

1

Excel::Writer::XLSX shouldn't be causing an "out of memory" issue since you are using set_optimization() mode which reduces memory usage down to a small and constant value.

However, loading a 70 MB file into a hash might (depending on your system).

Perhaps you can restructure your program to read (and then write) the data one row at a time without loading everything into memory.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • Looking at the code snippet that you posting in the comments to your question above it looks like you are also reading the data from an Excel file. A few comments on that: 1. It is probably the Excel reading and hash building that is burning up your memory. 2. If you are using Spreadsheet::ParseExcel to read the Excel file then that can consume a lot of memory. See the docs for information on how to reduce that. 3. Why bother storing the information in a hash. Why not just write it to the Excel file as you read it (in combination with item 2). – jmcnamara Sep 25 '14 at 16:00