Laravel Excel

Official:

https://laravel-excel.maatwebsite.nl/

Note: This lib works with PHPExcel which is deprecated already.

Note: When title row is in Chinese, need to change setting in config/excel.php: to_ascii: false;

https://laravel-china.org/topics/2029/laravel-excel-import-excel-file-incomplete-solution

Phpspreadsheet: (phpexcel is deprecated):

https://github.com/PHPOffice/PhpSpreadsheet

Don’t use heading as data key:

/config/excel.php: heading => false;

Example Code:

    public function import(Request $req){
        $this->check_login();
        $data = $this->view_data();

        $data['page_title'] = '設備匯入';

        return view('admin.import', $data);
    }

    public function import_handler(Request $req){
        $file = $req->file('file');
        //logg($file->getClientOriginalName());
        //$this->excel_handler($target_file);

        $dest = storage_path('app');
        $file->move($dest, 'import.xlsx');
        //$path = $file->store('app/public');
        //logg($dest);

        Excel::load($dest . '/import.xlsx', function($reader) {
            //$data = $reader->get();
            $sheet = $reader->first()->toArray();
            //logg($sheet);

            $fields = Config::get('admin.product_import');
            //logg($fields);

            $success = 0;
            $failed = 0;
            foreach ($sheet as $row_index => $row) {
                //logg($row);
                $data = [];
                $row_errs = [];
                foreach ($fields as $key => $field) {
                    $value = $row[$key];
                    if($field[3] == 'datetime' && $value != false){
                        $value = $value->toDateTimeString();
                    }

                    if($field[0] == 'iccid'){
                        if($value == false){
                            $row_errs[] = $field[1] . '為必填';
                        }else{
                            $current_iccid = ProductM::item_by_iccid($value);
                            if($current_iccid != false){
                                $row_errs[] = $value . '已經存在';
                            }
                        }
                    }

                    $data[$field[0]] = $value;
                }
                //logg($data);

                if($row_errs == false){
                    $result = ProductM::new_item($data);
                    if($result == false){
                        echo '第' . ($row_index+2) . '列: Failed. 新增資料失敗<br/>';
                        $failed++;
                    }else{
                        echo '第' . ($row_index+2) . '列: Success.<br/>';
                        $success++;
                    }
                }else{
                    echo '第' . ($row_index+2) . '列: Failed. ' . implode(';', $row_errs) . '<br/>';
                    $failed++;
                }
            }

            echo '完成, 成功:' . $success . '筆, 失敗: ' . $failed;
        });
    }

Export:

use Excel;

   public function export(Request $req){
        $this->check_login();

        $options = [
            'keyword' => $req->input('keyword'), 
            'status' => $req->input('status'), 
            'house' => $req->input('house')
        ];
        //logg($options);

        $data = ProductM::items($options);
        //logg($data);
        $fields = Config::get('admin.product_export');

        $new_data = [];

        //Write headings
        $values = [];
        foreach ($fields as $key => $field) {
            $values[] = $field[1];
        }
        $new_data[] = $values;

        foreach ($data as $key => $item) {
            $values = [];
            foreach ($fields as $key => $field) {
                $values[] = $item->{$field[0]};
            }

            $new_data[] = $values;
        }
        //logg($new_data);

        $file_url = asset('storage/products.xls');
        //logg($file_url);
        Excel::create('products', function($excel) use ($new_data){
            // Set the title
            $excel->setTitle('VIVV Product Report');

            // Chain the setters
            $excel->setCreator('Rex')
                  ->setCompany('Rex');

            // Call them separately
            $excel->setDescription('This is test version.');

            $excel->sheet('Data', function($sheet) use ($new_data){
                $sheet->fromArray($new_data);
            });
        })->store('xls');

        $this->success_response($file_url . '?v=' . time());
    }