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:
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
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:
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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()); } |