Laravel-admin is a quick tool to help you build your background management. It provides page components and form elements and other features to help you achieve a full function of the background management function with very little code.

Laravel-admin is a plug-in based on Laravel, which can help us quickly build background management system. Today we will introduce the use of Laravel-admin to export Excel files, based on laravel-admin version 1.5.

Laravel-admin built a simple tool to export the data in the model, but the style and format is relatively simple, and Chinese garbled, not quite meet our requirements. So we’re going to export by definition.

In the Laravel-Admin document – Data Export section, you can see that you can customize the export method using a third-party class library. A simple example is also written in the documentation, so let’s optimize the example in the documentation to make it more generic.

Let’s look at the documentation example first

<? php namespace App\Admin\Extensions; use Encore\Admin\Grid\Exporters\AbstractExporter; use Maatwebsite\Excel\Facades\Excel; class ExcelExpoter extends AbstractExporter { publicfunction export()
    {
        Excel::create('Filename'.function($excel) {

            $excel->sheet('Sheetname'.function($sheet) {// This is the logic that fetches the fields to be exported from the table data$rows = collect($this->getData())->map(function ($item) {
                    return array_only($item['id'.'title'.'content'.'rate'.'keywords']);
                });

                $sheet->rows($rows); }); }) - >export('xls'); }}Copy the code

As you can see, he defines the file name and the exported field, so there is no flexibility, can’t define an exported class for every model? So let’s optimize it

Add export file names and custom export fields

  1. We define two variables:filename 和 fieldsAnd initialized in the constructor
    private $filename;  // The name of the file to export
    private $fields;    // Fields in the exported database
    public function __construct(String $filename, Array $fields)
    {
        parent::__construct();
        $this->filename = $filename;
        $this->fields = $fields;
    }
Copy the code
  1. Then modify the export function to replace the dead part with these two functions

    /** * export *@return mixed|void
     */
    public function export(a)
    {
        Excel::create($this->filename, function ($excel){
            $excel->sheet('Shee1'.function($sheet) {
                // This logic fetches the fields to be exported from the table data
                $rows = collect($this->getData())->map(function ($item) {
                    return array_only($item, $this->fields);
                });
                $sheet->rows($rows);
            });

        })->export('xls');
    }
    
Copy the code
  1. Use export functions in the controller

    protected function grid()
    {
        return Admin::grid(User::class, function (Grid $grid) {

            $grid->id('ID')->sortable();
            $grid->mobile('Mobile phone Number');
            $grid->real_name('name');
            $grid->privilege('level')->display(function ($privilege) {return User::$privilegeInfo[$privilege];
            });
            $grid->department('department')->display(function ($department) {return Department::where('id'.$department)->value('name');
            });

            $fields = ['id'.'mobile'.'real_name'.'privilege'.'department'];
            $grid->exporter(new ExcelExpoter('User List'.$fields));
        });
    }
Copy the code
  1. Perform the export operation on the page

Adding headers

As before, we add a title field to represent the table header field as follows


    private $title;     // Export the header field
    private $filename;  // The name of the file to export
    private $fields;    // Fields in the exported database
    public function __construct(String $filename, Array $title, Array $fields)
    {
        parent::__construct();
        $this->filename = $filename;
        $this->title = $title;
        $this->fields = $fields;
    }

    /** * export *@return mixed|void
     */
    public function export(a)
    {
        Excel::create($this->filename, function ($excel){
            $excel->sheet('Shee1'.function($sheet) {
                // Set the first line
                $sheet->row(1.$this->title);
                // This logic fetches the fields to be exported from the table data
                $rows = collect($this->getData())->map(function ($item) {
                    return array_only($item, $this->fields);
                });
                });
                $sheet->rows($rows);
            });

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

Copy the code

Then add the header field where the call is made


    protected function grid(a)
    {
        return Admin::grid(User::class, function (Grid $grid) {... $title = ['ID'.'Mobile phone Number'.'name'.'level'.'department'];
            $fields = ['id'.'mobile'.'real_name'.'privilege'.'department'];
            $grid->exporter(new ExcelExpoter('User List', $title, $fields));
        });
    }

Copy the code

Re-export and you can see that we already have the header field in our file.

Field sorting

One problem, if we look closely, is that we export the fields in the order in the database, and there is no way to specify the order of the fields. As a result, we must write the table header fields in the same order as the database, otherwise the fields and names will not match. Here’s an example:

The order in the database is as follows:

  'id'.'mobile'.'real_name'.'privilege'.'department'
Copy the code

If we do not know the order of the fields in the database or for other reasons, we write the title field in the following format

'name'.'ID'.'Mobile phone Number'.'level'.'department'
Copy the code

The exported file is an error.

To solve this problem, we need to sort the results in a certain order. So I’m sort by fields, but you could sort by anything else, but I’m just kind of thinking about it.


    /** * export *@return mixed|void
     */
    public function export(a)
    {
        Excel::create($this->filename, function ($excel){
            $excel->sheet('Shee1'.function($sheet) {
                $sheet->row(1.$this->title);
                // This logic fetches the fields to be exported from the table data
                $rows = collect($this->getData())->map(function ($item) {
                    $item = array_only($item, $this->fields);
                    $row = [];
                    foreach ($this->fields as $field) {
                        $row[$field] = $item[$field];
                    }
                    return $row;
                });
                $sheet->rows($rows);
            });

        })->export('xls');

Copy the code

The code is relatively simple, which is to iterate through the fields array, fetching the corresponding value from each column, to achieve the effect of sorting.

Optimization of style

If you have requirements for excel file styles, you can refer to the Laravel-Excel documentation for related operations.


    Excel::create($this->filename, function ($excel){
        $excel->sheet('Shee1'.function($sheet) {
            // Set the width
            $sheet->setWidth(array(
                'A'= >5.'B'= >12,
                ...
            ));
            $sheet->row(1.$this->title);
            // This logic fetches the fields to be exported from the table data
            $rows = collect($this->getData())->map(function ($item) {
                $item = array_only($item, $this->fields);
                $row = [];
                foreach ($this->fields as $field) {
                    $row[$field] = $item[$field];
                }
                return $row;
            });
            $sheet->rows($rows);
        });
    })->export('xls');
Copy the code

conclusion

Often visit nuggets, the first time to write an article, write the content is relatively simple, to provide a way of thinking for everyone’s reference. If there is a mistake, you are welcome to spend, not stingy comments.