problem with maatwebsite/excel and \DB::commit();

Question

Im trying store some data in db, and immediately after i'm triying to download and excel file with that data. So I've notice that excel package block my commit, and obviusly it doesnt allow me to store data in DB. This is my code. - I'm using Laravel 5.5 -"maatwebsite/excel": "~2.1.0",

public function refundTicketAndGenerateExcel($transactions, $table)
{   
    try 
    {   
        \DB::beginTransaction();

            $this->storeRefundData($transactions);
            $response = $this->generateExcel($table);

        \DB::commit();
        return $response;
    } 
    catch (\Exception $e) 
    {   
        \DB::rollback();
        \Log::error($e);
        $result['message'] = $e->getMessage();
        return response()->json($result, 500);
    }
}

public function generateExcel($table)
{   
    Excel::create('Reembolsos', function ($excel) use ($table) {
                    $excel->sheet('Reembolsos', function ($sheet) use ($table) {

                        $FontStyle = array(
                            'font' => array(
                                'name' => 'Arial',
                                'color' => array('rgb' => '000000'),
                                'size' => 11
                            ),
                        );

                        $sheet->loadView($this->path . '.partials.excel', ['table'=>$table]);
                        $sheet->getStyle('A1:K1000')->applyFromArray($FontStyle);
                    });

                })->export('xls');
}

PD: If I just comment \DB::beginTransaction() and \DB::commit(), everything works fine; On the other hand if I just comment Excel::create block, everything works fine too; That's why I sait that excel package blocks my commit.

Thanks in advance.

Answer 1

When you call export(), it kills your script. Pretty lame design to be honest:

protected function _download(Array $headers = [])
{
    $filename = $this->filename;
    $userAgent = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
    // Just for Microsoft Explore
    if (preg_match('/Trident|Edge/i', $userAgent)) {
        $filename = rawurlencode($filename);
    }
    // Set the headers
    $this->_setHeaders(
        $headers,
        [
            'Content-Type'        => $this->contentType,
            'Content-Disposition' => 'attachment; filename="' . $filename . '.' . $this->ext . '"',
            'Expires'             => 'Mon, 26 Jul 1997 05:00:00 GMT', // Date in the past
            'Last-Modified'       => Carbon::now()->format('D, d M Y H:i:s'),
            'Cache-Control'       => 'cache, must-revalidate',
            'Pragma'              => 'public'
        ]
    );
    // Check if writer isset
    if (!$this->writer)
        throw new LaravelExcelException('[ERROR] No writer was set.');
    // Download
    $this->writer->save('php://output');
    // End the script to prevent corrupted xlsx files
    exit;
}

https://github.com/Maatwebsite/Laravel-Excel/blob/2.1/src/Maatwebsite/Excel/Writers/LaravelExcelWriter.php#L347-L377

Fix this by returning the writer object that Excel::create() returns, and remove the ->export() part.

$writer = Excel::create(blah blah blah); // no->export() now!
return $writer;

Then do the export affter your commit.

 $writer = $this->generateExcel($table);
 \DB::commit();
 return $writer->export();