Exporting Large Data Using Laravel Excel And Laravel Chunk

In this post, we will use laravel db chunk to query the larag dataset and export them in excel file using Laravel Excel.

Posted by Author At January 21st, 2017

Laravel excel is a great package which lets us import and export the excel files in the laravel application, it not only ease the process of importing excel and csv files with few lines of codes, but also make it breeze to export the data, from the eloquent collection. But sometimes, we have huge data in our database, and we need an effective way to chunk the data and export them, in this post we will see how we can use laravel chunk with laravel Excel to export the big dataset from the database.

Let’s query the user model and chunk the user data and export them using Laravel excel.

public function exportUser($data)
    {
       $user = new User;
        $data = $user->query();
        try{
            $filedate = str_replace(' ', '_', Carbon::now());
            return Excel::create('UserList'.$filedate, function($excel) use($data){
 
            $data->chunk(100, function ($users) use($excel) {
            $collection = $this->transformCollection($users);

            $excel->sheet('OrgSubUserList', function($sheet) use($collection){
                $sheet->fromModel($collection, null, 'A1', true);
            });
            });
            
        })->export('xls');
        }
        catch(Exception $e)
        {
            return false;
        }
       
    }

 

Let’s define the transformCollection method, through which we will modify the column name of the collection that we queried from database.

private function transformCollection($collection){

        return array_map([$this, 'transform'], $collection->toArray());
    }


private function transform($collection){
        return [
            'Full Name' => $collection['first_name']. ' '. $collection['middle_name']. ' '.         $collection['last_name'],
            'Phone No' => $collection['phone'],
            'Full Address' => $collection['addr_line1']. ' '. $collection['addr_line2'],
            'City' => $collection['city'],
            'Zip Code' => $collection['country'],
            'User Status' => $collection['status'],
            'Last Login' => $collection['last_login'],
            'Created At' => $collection['created_at'],
            'Updated At' => $collection['updated_at'],

        ];
    }

 

The exportUser method retrieves the 100 users in one chunk and creates a sheet, and for another 100 numbers of users in the users table, yet another sheet, and so on. It’s effective method when we have large number of rows in our database, since we retrieves the data in chunk and export them.


Posted Under