Laravel Excel | Rex's Personal Site

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;
        });
    }

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());
    }

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());

}

M	T	W	T	F	S	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31