I am generating a report in excel from php, I use laravel and a package called phpspreadsheet, the data I get is dynamic, that is, in a query there can be n number of columns, at the moment I can only go through some records, I don't know if it stops this package there is some way to make the report dynamic. I have tried with the package function setCellValueByColumnAndRow but without result.

This is the function I use to generate the report:

function exportExcelDownload($name, $data)
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setCellValue('A1', 'Column 1');
    $sheet->setCellValue('B1', 'Column 2');
    $sheet->setCellValue('C1', 'Column 3');
    $sheet->setCellValue('D1', 'Column 4');
    $sheet->setCellValue('E1', 'Column 5');
    $sheet->setCellValue('F1', 'Column 6');

    $i = 2;
    foreach ($data as $value) {
        $sheet->setCellValue('A' . $i, $value->client);
        $sheet->setCellValue('B' . $i, $value->number);
        $sheet->setCellValue('C' . $i, $value->order_id);
        $sheet->setCellValue('D' . $i, $value->hr_id);
        $sheet->setCellValue('E' . $i, $value->document);
        $sheet->setCellValue('F' . $i, $value->date);            
        $i  ;
    }

    $fileName = $name . '.xlsx';
    $writer = new Xlsx($spreadsheet);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . urlencode($fileName) . '"');
    $writer->save('php://output');
}

CodePudding user response：

Using laravel, use the excellent package https://laravel-excel.com/

With this you can use collections, arrays or the direct result of a query.

But rather than accepting an unknown number of columns, its quick and easy to create classes that represent specific data sets for export.

From the docs:

Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but elegant wrapper around PhpSpreadsheet with the goal of simplifying exports and imports.