将大量数据导出到xlsx中

Question

I need to export a huge dataset from a MySQL database table with MYISAM engine into a .xlsx file in Laravel.

I'm using the maatwebsite/laravel-excel package, which is based on PHPExcel.

The datased consits of about 500,000 rows with 93 columns (around 46,500,000 cells), and quite a few calculations regarding the header structure.

This is the code I'm currently using:

// $excel_data contains some data regarding the project, nothing relevant here
$output = Excel::create('myproject-' . $excel_data->project->name . '-'.date('Y-m-d H:i:s') . '-export', function($excel) use($excel_data) {

        // Set the title
        $excel->setTitle($excel_data->project->name . ' Export');

        $excel->sheet('Data', function($sheet) use($excel_data) {

            $rowPointer = 1;

            $query = DB::table('task_metas')
                ->where([
                    ['project_id', '=', $excel_data->project->id],
                    ['deleted_at', '=', null]
                ])
                ->orderBy('id');

            $totalRecords = $query->count();
            // my server can't handle a request that returns more than 20k rows so I am chunking the results in batches of 15000 to be on the safe side
            $query->chunk(15000, function($taskmetas) use($sheet, &$rowPointer, $totalRecords) {
                // Iterate over taskmetas
                foreach ($taskmetas as $taskmeta) {
                    // other columns and header structure omitted for clarity
                    $sheet->setCellValue('A' . $rowPointer, $rowPointer);
                    $sheet->setCellValue('B' . $rowPointer, $taskmeta->id);
                    $sheet->setCellValue('C' . $rowPointer, $taskmeta->url);

                    // Move on to the next row
                    $rowPointer++;
                }
                // logging the progress of the export
                activity()
                    ->log("wrote taskmeta to row " . $rowPointer . "/" . $totalRecords);

                unset($taskmetas);
            });
        });

    });

    $output->download('xlsx');

According to the log, the rows are successfully written into the file, however the file creation itself takes a long time. So long in fact, that it doesn't finish it in 1 hour (that's the max execution time of this function).

Exporting it to csv works great, in about 10 minutes it compiles the file & downloads it, however I can't work with that - the output file needs to be xlsx.

What can I do to speed up the file creation process? I'm also open to other alternatives as long as I can achieve the same results.

Answer 1

I have 3 suggestions:

1. Using cursor (although till today, I have not found out if its better than chunk - maybe your case might validate this) - Sincerely I only used this one and its with Eloquent.

2. reduce the size of the chunk. I think loading 15000 records in memory is already an issue.

3. Create the excel file first, then use rows() method on the sheet to append multiple rows. (this might not work well as it requires an array)