fatal error when generating borders in a spreadsheet after searching the database

Question

I'm really new to the spreadsheet, I built the entire table and managed to search for items in the database, however, I have to put the borders on the lines of items searched in the database, as I don't know how many product lines have, I used the following code below:

ERROR =

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 4096 bytes) in /home2/eekrep60/public_html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php on line 1262

ini_set('memory_limit', -1);

ini_set("max_execution_time", 0);

I tried to use these 2 commands but when using them the server presented an error after 3 minutes, without the borders it generates the spreadsheet in 5 seconds

CODE...

'<?php

require_once('../../vendor/autoload.php');
include("../includes/db.php");

//ini_set('memory_limit', -1);
//ini_set("max_execution_time", 0);

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Worksheet\Iterator;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Pdf;

$id_pedido = $_GET['id'];

$pedido = query("SELECT * FROM pedidos WHERE id = ".$id_pedido);
if (!empty($pedido)) {
    $pedido = $pedido[0];
    $data = explode(' ', $pedido['data']);
    $data = explode('-', $data[0]);
    $pedido['data'] = $data[2]."/".$data[1]."/".$data[0];
    $desconto_categoria = query("SELECT mensagem_carrinho FROM categorias_subcat WHERE id = ".$pedido['empresa']);

    $itens = query("SELECT * FROM pedidos_itens WHERE id_pedido = ".$pedido['id']);

    $cliente = query("SELECT c.*, ci.dsc_cidade, e.dsc_estado FROM clientes c LEFT JOIN estados e ON (e.id_estado = c.estado) LEFT JOIN cidades ci ON (ci.id_cidade = c.cidade) WHERE c.id = ".$pedido['id_cliente']);
    $cliente = $cliente[0];
}   
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT); 
$sheet->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4); 
$sheet->getPageMargins()->setTop(0.40); 
$sheet->getPageMargins()->setRight(0.40);
$sheet->getPageMargins()->setLeft(0.40); 
$sheet->getPageMargins()->setBottom(0.40); 
$sheet->getPageMargins()->setHeader(0.0); 
$sheet->getPageMargins()->setFooter(0.0); 

$sheet->getColumnDimension('A')->setWidth(13,5);
$sheet->getColumnDimension('B')->setWidth(47,5);
$sheet->getColumnDimension('C')->setWidth(5);
$sheet->getColumnDimension('D')->setWidth(6);
$sheet->getColumnDimension('E')->setWidth(10);
$sheet->getColumnDimension('F')->setWidth(13);

$styleArray1 = [
'borders' => [
    'allBorders' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],
    ],
];

$styleArray2 = [
'borders' => [
    'top' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],
    'left' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],  
    'right' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],
    ],
];

$styleArray3 = [
'borders' => [
    'bottom' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],
    'left' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],  
    'right' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],
    ],
];

$styleArray4 = [
'borders' => [
    'left' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],  
    ],
];

$styleArray5 = [
'borders' => [
    'right' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        'color' => ['argb' => '00000000'],
        ],  
    ],
];



$sheet->mergeCells('A19:F19')->getRowDimension('19')->setRowHeight(3);
$sheet->getStyle('A19:F19')->applyFromArray($styleArray1);
$sheet->getStyle('A20:F20')->applyFromArray($styleArray1); 

$sheet->setCellValue('A20', "REFERÊNCIA")->getstyle('A20')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getstyle('A20')->getFont()->setBold(true);

$sheet->setCellValue('B20', "DESCRIÇÃO DO PRODUTO")->getstyle('B20')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getstyle('B20')->getFont()->setBold(true);

$sheet->setCellValue('C20', "Q.CX")->getstyle('C20')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getstyle('C20')->getFont()->setBold(true);

$sheet->setCellValue('D20', "Q.UN")->getstyle('D20')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getstyle('D20')->getFont()->setBold(true);

$sheet->setCellValue('E20', "V.UNIT")->getstyle('E20')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getstyle('E20')->getFont()->setBold(true);

$sheet->setCellValue('F20', "TOTAL")->getstyle('F20')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getstyle('F20')->getFont()->setBold(true);

$i=21;
foreach($itens as $item): 
    
    //$sheet->getStyle('A:F')->applyFromArray($styleArray1);**//HERE ERROR OCCURS****

    $sheet->getStyle('A:F')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    
    $sheet->getStyle('E:F')->getNumberFormat()->setFormatCode('$ #,##0.00'); 
    
    $qCaixa = query("SELECT quantidade_caixa FROM produtos WHERE cod = '".$item['item_codigo']."'");
    $sheet->setCellValue('A'.$i, $item['item_codigo']);
    $sheet->setCellValue('B'.$i, $item['item_nome']);
    $sheet->setCellValue('C'.$i, $item['item_quantidade']);
    $sheet->setCellValue('D'.$i, $qCaixa[0]['quantidade_caixa']);
    $sheet->setCellValue('E'.$i, $item['item_valor']);
    $sheet->setCellValue('F'.$i, $item['valor_total']);
$i++;
endforeach;
    
$sheet->getRowDimension($i)->setRowHeight(26);
$sheet->getStyle('A'.$i.':F'.$i)->applyFromArray($styleArray1); 

$sheet->mergeCells('A'.$i.':D'.$i)->setCellValue('A'.$i, 'TOTALR$');
$sheet->getstyle('A'.$i)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);;
$sheet->getstyle('A'.$i)->getFont()->setSize(16);
$sheet->getstyle('A'.$i)->getFont()->setBold(true);

$sheet->getStyle('E'.$i)->getNumberFormat()->setFormatCode('R$ #,##0.00'); 
$sheet->mergeCells('E'.$i.':F'.$i)->setCellValue('E'.$i, $pedido['valor_total']);
$sheet->getstyle('E'.$i)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);;
$sheet->getstyle('E'.$i)->getFont()->setSize(16);
$sheet->getstyle('E'.$i)->getFont()->setBold(true);


    $filename = ''.utf8_decode($cliente['razao']).'.xlsx';
    // Redirect output to a client's web browser (Xlsx)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.
    
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');'

The code appears to be failing at around 256M. So obviously the `ini_set('memory_limit', -1);` is not working or not getting executed — RiggsFolly, Jun 25 '20 at 20:36
Obviously we need more detail and clarity to be able to hel pyou — RiggsFolly, Jun 25 '20 at 20:37
Be warned that the given query is widely open for SQL injection - please have a look at prepared statements! — Nico Haase, Dec 02 '21 at 13:14

score 0 · Answer 1 · answered Jun 26 '20 at 04:36

0

i found problem, I just marked the cells to receive borders with the value of $ i and it worked

$sheet->getStyle('A'.$i.':F'.$i)->applyFromArray($styleArray1);

answered Jun 26 '20 at 04:36

EKowalski

1
1

fatal error when generating borders in a spreadsheet after searching the database

1 Answers1