John Davidson

Message:

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');

}

---

My Answer:
> One way to make the report dynamic is to loop through the data and set the cell values dynamically based on the column index. Here's an example of how you can achieve this using PhpSpreadsheet:

php
function exportExcelDownload($name, $data) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set column headers
$columnIndex = 1;
foreach ($data[0] as $key => $value) {
$sheet->setCellValueByColumnAndRow($columnIndex, 1, $key);
$columnIndex++;
}

// Set data rows
$rowIndex = 2;
foreach ($data as $row) {
$columnIndex = 1;
foreach ($row as $value) {
$sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value);
$columnIndex++;
}
$rowIndex++;
}

// Set headers for download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
}


In this function, we first set the column headers by looping through the keys of the first row of data. Then, we loop through each row of data and set the cell values based on the column index. Finally, we set the headers for downloading the Excel file and save the spreadsheet to the output stream.

This way, the report will be generated dynamically based on the data provided to the function.