How to export millions of data in xlsx using ajax without job queue?

How to export millions of data in xlsx using ajax and without having timeout or memory problem? I tried this:

public function export(Request $request)
    {
        $fileName = 'transfer_export_'.$this->todayDate().'.csv';
        $merchant_code = Auth::user()->merchant_code;
        $transfers = DrcSendMoneyTransfertsRequests::where('merchant_code',$merchant_code)->get();

        $headers = array(
            "Content-type"        => "text/xlsx",
            "Content-Disposition" => "attachment; filename=$fileName",
            "Pragma"              => "no-cache",
            "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
            "Expires"             => "0"
        );

        $columns = array('Channel', 'Amount', 'Currency', 'Requested At', 'Processeed At', 'Transfer from', 'Transfer to', 'Status');

        $callback = function() use($transfers, $columns) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $columns);

            foreach ($transfers as $transfer) {
                $row['Channel']  = $transfer["channel"];
                $row['Amount']    = $transfer["amount"];
                $row['Currency']    = $transfer["currency"];
                $row['Requested At']  = $transfer["requested_at"];
                $row['Processeed At']  = $transfer["processed_at"];
                $row['Transfer from']  = $transfer["transfer_from"];
                $row['Transfer to']  = $transfer["transfer_to"];
                $row['Status']  = $transfer["status"];

                fputcsv($file, array($row['Channel'], $row['Amount'], $row['Currency'], $row['Requested At'], $row['Processeed At'], $row['Transfer from'], $row['Transfer to'], $row['Status']));
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

LaryAI

3 years ago

Best Answer

Level 58

Exporting millions of data in xlsx using ajax without job queue can be a challenging task. One solution is to use pagination to limit the number of records being fetched at a time. This can be achieved by modifying the query to fetch a limited number of records at a time and then using AJAX to fetch the next set of records until all records have been fetched.

Another solution is to use a library like Spout to generate the xlsx file. Spout is a PHP library that allows for the reading and writing of spreadsheet files (CSV, XLSX and ODS), without the need for any external libraries. It is optimized for performance and memory usage, making it ideal for exporting large datasets.

Here's an example of how to use Spout to export data to an xlsx file:

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Row;

public function export(Request $request)
{
    $fileName = 'transfer_export_'.$this->todayDate().'.xlsx';
    $merchant_code = Auth::user()->merchant_code;
    $transfers = DrcSendMoneyTransfertsRequests::where('merchant_code',$merchant_code)->get();

    $headers = array(
        "Content-type"        => "text/xlsx",
        "Content-Disposition" => "attachment; filename=$fileName",
        "Pragma"              => "no-cache",
        "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
        "Expires"             => "0"
    );

    $columns = array('Channel', 'Amount', 'Currency', 'Requested At', 'Processeed At', 'Transfer from', 'Transfer to', 'Status');

    $writer = WriterEntityFactory::createXLSXWriter();
    $writer->openToBrowser($fileName);

    // Write headers
    $headerRow = WriterEntityFactory::createRowFromArray($columns);
    $writer->addRow($headerRow);

    // Write data
    foreach ($transfers as $transfer) {
        $rowData = [
            $transfer["channel"],
            $transfer["amount"],
            $transfer["currency"],
            $transfer["requested_at"],
            $transfer["processed_at"],
            $transfer["transfer_from"],
            $transfer["transfer_to"],
            $transfer["status"]
        ];

        $row = WriterEntityFactory::createRowFromArray($rowData);
        $writer->addRow($row);
    }

    $writer->close();
}

This code uses Spout to create an xlsx file and write the data to it. The WriterEntityFactory is used to create the writer and the rows. The openToBrowser method is used to open the file in the browser for download. The data is written to the file row by row using the addRow method.

Please or to participate in this conversation.