Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mayur937's avatar

How to Export large amount of data around 15 lacks in excel export with laravel?

Hello, I am exporting data with a total record of 15 lacks. It takes about 28-30 minutes. I want to optimize to reduce the time to export. I have used "avadim\FastExcelWriter\Excel"

use \avadim\FastExcelWriter\Excel; public function exportExcelWithFastExcel() {

    set_time_limit(3000); //3000 seconds = 50 minutes
    ini_set('memory_limit', -1);
    $Abc_details = Abc::query()
    ->with(['AbcDetails' => function ($query) {
        $query->select('id', 'Abc_id', 'description','rate', 'qty', 'per');
    }])
    ->take(1000000)
    ->get();
    
    if (!empty($Abc_details)) {
        foreach ($Abc_details as $Abc_value) {
            $grand_total = 0;
            foreach($Abc_value->AbcDetails as $Abc_details_data)
            {
                $line_total = 0;
                $row_rate = $Abc_details_data->rate ? $Abc_details_data->rate : 0;
                $row_qty = $Abc_details_data->qty ? $Abc_details_data->qty : 0;
                $row_per = $Abc_details_data->per ? $Abc_details_data->per : 0;

                if (!empty($row_per)) {
                    $line_total = (((float) $row_rate + (((float) $row_rate * $row_per) / 100)) * $row_qty);
                } else {
                    $line_total = ((float) $row_rate * $row_qty);
                }
                $grand_total += $line_total;
            }
            $Abc_arr = [];
            $Abc_arr['job_number'] = $Abc_value->job_id ? $Abc_value->job_id : '';
            $Abc_arr['doc_no'] = $Abc_value->doc_no ? $Abc_value->doc_no : '';
            $Abc_arr['app_no'] = $Abc_value->app_no ? $Abc_value->app_no : '';
            $Abc_arr['total_with_per'] = $grand_total;
            $Abc_arr['finalised'] = $Abc_value->is_final ? $Abc_value->is_final : '';

            $collect_arr[] = $Abc_arr;
        }
    }

    # Create header style
    $headStyle = [
        'font' => [
            'style' => 'bold',
            'color' => 'ffffff',
        ],
        'fill' => [
            'background-color' => '#00008b'
        ],
        // 'text-align' => 'center',
        // 'vertical-align' => 'center',
        'border' => 'thin',
    ];
    # Create header title
    $head = ['JOB NUMBER', 'DOC NO', 'APP NO', 'TOTAL WITH per', 'FINALIZED'];
    $excel = Excel::create(['Sheet1']);
    $sheet = $excel->getSheet();
    $sheet->writeHeader($head, $headStyle);

    // Write data
    foreach($collect_arr as $row_data) {
        $sheet->writeRow(array_values($row_data));
    }
    
    # Add filter
    $sheet->setAutofilter(1);

    # Add Width
    $sheet->setColWidths(['A' => 16, 'B' => 13, 'C' => 18, 'D' => 22, 'E' => 13]);
    $excel->output('Abc-details.xlsx');
}
0 likes
1 reply
mayur937's avatar

Also, I have implemented "Chunk" use Maatwebsite\Excel\Facades\Excel;

public function exportExcelWithChunk() { $Abc_details = Abc::query() ->with(['AbcDetails' => function ($query) { $query->select('id', 'Abc_id', 'description','rate', 'qty', 'per'); }]) ->take(100000) ->get();

    if (!empty($Abc_details)) {
        foreach ($Abc_details as $Abc_value) {
            $grand_total = 0;
            foreach($Abc_value->AbcDetails as $Abc_details_data)
            {
                $line_total = 0;
                $row_rate = $Abc_details_data->rate ? $Abc_details_data->rate : 0;
                $row_qty = $Abc_details_data->qty ? $Abc_details_data->qty : 0;
                $row_per = $Abc_details_data->per ? $Abc_details_data->per : 0;

                if (!empty($row_per)) {
                    $line_total = (((float) $row_rate + (((float) $row_rate * $row_per) / 100)) * $row_qty);
                } else {
                    $line_total = ((float) $row_rate * $row_qty);
                }
                $grand_total += $line_total;
            }
            $Abc_arr = [];
            $Abc_arr['job_number'] = $Abc_value->job_id ? $Abc_value->job_id : '';
            $Abc_arr['doc_no'] = $Abc_value->doc_no ? $Abc_value->doc_no : '';
            $Abc_arr['app_no'] = $Abc_value->app_no ? $Abc_value->app_no : '';
            $Abc_arr['total_with_per'] = $grand_total;
            $Abc_arr['finalised'] = $Abc_value->is_final ? $Abc_value->is_final : '';

            $collect_arr[] = $Abc_arr;
        }
    }

  
    ini_set('max_execution_time',600);
    ini_set('memory_limit',"4096");



    Excel::create('Report', function ($excel) use ($collect_arr) {
        $excel->sheet('report', function ($sheet) use ($collect_arr) {
            $sheet->appendRow($this->columns());
            $query->chunk(1000, function ($rows) use ($sheet) {
                foreach ($rows as $row) {
                    $sheet->appendRow($this->rows($row));

        }
            });
        });
    })->download('xlsx');

}

Please or to participate in this conversation.