PHPExcel - Set cells with same value at one time

Question

I'm trying to write 18000 rows x 42 cols. The values from col 'E' to 'DD' must set with value 0. I'm trying to use increment but I found that is caused memory exhausted.

My Code is like this :

$col = $lastcol;
foreach ($diag0 as $key) {
    $sheet->setCellValue('A'.(string)($col + 1), '0');
    $sheet->setCellValue('B'.(string)($col + 1), $key->cdDiag);
    $sheet->setCellValue('D'.(string)($col + 1), $key->nmDiag);

    for ($char='E'; $char <= 'J' ; $char++) { 
        $sheet->setCellValue($char.(string)($col + 1), '0');
    }

    $col++;
}

I'd trying the PHPExcel cached config, but it still give the same result. I think since the col 'E' to 'DD' is have the same value, can I make it like set 'E' to 'DD' with 0 at one loop?

I'm sorry but to make a xls is requirement from my office and it need to use some feature like counting and styling — Ignatius Chandra, Aug 30 '18 at 10:30

Erubiel · Accepted Answer · 2018-08-30T11:31:03.163

1

You can set the format for a range of cells:

$objPHPExcel->getActiveSheet()    // THIS SHOULD BE THE SAME AS $sheet
    ->getStyle('E2:DD18000')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

A list with the available formats:

http://apigen.juzna.cz/doc/ouardisoft/PHPExcel/class-PHPExcel_Style_NumberFormat.html

Also, maybe this way of inserting rows works better:

$objPHPExcel->getActiveSheet()->fromArray(array(
    0, //COL A
    $key->cdDiag,  //COL B
    '', 
    $key->nmDiag,
    0,0,0,0,0, ... 0,0,0,0   // your 26 COLUMNS E to DD, 
),NULL,'A' . $row + 1);

You could also set the columns as empty string '' since you added the setColumnsFormat. And your $col variable, not that it matters, but it should be called $row... you are moving through rows not cols...

I never tried this but:

you can use $objPHPExcel->garbageCollect(); after inserting each row to solve your memory allocation problem, if this doesn't fix the problem try giving a little more memory with:

ini_set('memory_limit', '256M');

default is 128, just be sure you don't consume too much resources...

edited Aug 30 '18 at 11:31

answered Aug 30 '18 at 06:35

Erubiel

2,934
14
32

I'd trying your answer but the program became error and say that `setColumnFormat` is undefined. And I didn't find any documentations about it. My `$sheet` is `$objPHPExcel->getActiveSheet()` and I'm using Codeigniter. Any advice? and `$sheet->row()` is also undefined. – Ignatius Chandra Aug 30 '18 at 10:27
sorry that's the laravel wrapper code i guess.. let me edit my answer – Erubiel Aug 30 '18 at 10:29
I'd trying both of your code and its worked like a charm. thank you. but it still cannot handle the memory exhausted problem. Any sugestion? – Ignatius Chandra Aug 30 '18 at 11:00
https://stackoverflow.com/questions/561066/fatal-error-allowed-memory-size-of-134217728-bytes-exhausted-codeigniter-xml – Erubiel Aug 30 '18 at 11:20
`ini_set()` is work for me and for my case it need to set to 1024MB and set time limit to 1200. Thank you – Ignatius Chandra Aug 31 '18 at 09:49
My god thats a lot of time! hahaha glad it worked! just don't be too comfortable changing this parameters everywhere unless you know you have enough resources to back em up! – Erubiel Aug 31 '18 at 09:59

PHPExcel - Set cells with same value at one time

1 Answers1