Phpspreadsheet: (phpexcel is deprecated):
https://github.com/PHPOffice/PhpSpreadsheet
Don’t use heading as data key:
/config/excel.php: heading => false;
Setting a cell value by column and row
|
|
// Set cell A5 with a string value $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet'); |
Retrieving a cell value by column and row
|
|
// Get the value from cell B5 $cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue(); |
Reference:
Column Configs for import and export
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//對應匯入的excel欄位 'product_import' => [ 'A' => ['source', '來源', true, 'int'], 'B' => ['iccid', 'ICCID', true, 'string'], 'C' => ['phone', '電話號碼', true, 'string'], 'D' => ['item', '品項', true, 'int'], 'E' => ['customer', '客戶名稱', false, 'string'], 'F' => ['house', '倉庫地點', true, 'int'], 'G' => ['purchase_date', '進貨日', true, 'date'], 'H' => ['ship_date', '出貨日', true, 'date'], 'I' => ['enabled_start', '啟用開始日', true, 'date'], 'J' => ['enabled_end', '啟用結束日', true, 'date'], 'K' => ['enabled_end_buff', '啟用結束日回朔天數', false, 'int'], 'L' => ['user_enabled', '客戶啟用日', false, 'date'], 'M' => ['status', '狀態', true, 'int'], 'N' => ['last_at', '最後使用日期', false, 'date'], 'O' => ['note', '備註', false, 'string'] ], |
Export Excel
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 |
use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Spreadsheet; public function products_export(Request $req){ $this->check_login(); $options = [ 'source' => $req->input('source'), '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'); //logg($fields); $file_name = 'Products.xlsx'; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); //$excel->setActiveSheetIndex(0); //Write headers $row_index = 1; $col_index = 1; foreach ($fields as $key => $field) { $sheet->setCellValueByColumnAndRow($col_index, $row_index, $field[1]); $col_index++; } //Write data $row_index++; foreach ($data as $key => $item) { $col_index = 1; foreach ($fields as $key => $field) { $value = val($item, $field[0]); $sheet->setCellValueByColumnAndRow($col_index, $row_index, $value); $col_index++; } $row_index++; } // Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $file_name . '"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 2019 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); } |
Update Excel and 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 |
use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; public function download_upload_template(Request $req){ $file_name = 'upload_template.xlsx'; $tpl = storage_path('app/upload_template.xlsx'); $excel = IOFactory::load($tpl); //Choose second sheet $sheet = $excel->setActiveSheetIndex(1); //Write sources $source_data = SourceM::orderBy('id')->get(); $row_index = 2; foreach ($source_data as $key => $item) { $col_index = 1; $sheet->setCellValueByColumnAndRow($col_index, $row_index, $item->id); $sheet->setCellValueByColumnAndRow($col_index+1, $row_index, $item->name); $row_index++; } $excel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $file_name . '"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 2019 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($excel, 'Xlsx'); $writer->save('php://output'); } |
Import:
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 73 74 75 76 77 78 79 80 81 82 83 84 |
public function import_handler(Request $req){ $file = $req->file('file'); //logg($file->getClientOriginalName()); //$this->excel_handler($target_file); $file_name = 'import.xlsx'; $dest = storage_path('app'); $file->move($dest, $file_name); //$path = $file->store('app/public'); //logg($dest); $spreadsheet = IOFactory::load($dest . '/' . $file_name); $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); //dd($sheetData); $fields = Config::get('admin.product_import'); //logg($fields); $success = 0; $failed = 0; foreach ($sheetData as $row_index => $row) { if($row_index == 1) continue; //logg($row); $data = []; $row_errs = []; foreach ($fields as $key => $field) { $value = val($row, $key); //logg($value); if($value == false){ if($field[2] == true){ $row_errs[] = $field[1] . ' 為必填'; break; }else{ continue; } } //If value is date, the value will be converted to Carbon. if($field[3] == 'date' && $value != false){ $value = Carbon::parse($value)->toDateString(); //logg($value); } 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 . '已經存在'; } } } if($field[0] == 'enabled_end_buff'){ //Get index of enabled_end if($data['enabled_end'] != false && $value != false && $value > 0){ $d = Carbon::parse($data['enabled_end']); $data['enabled_end'] = $d->subDays($value)->toDateString(); } } $data[$field[0]] = $value; } //logg($data); if($row_errs == false){ $result = ProductM::new_item($data); if($result == false){ echo '第' . ($row_index) . '列: Failed. 新增資料失敗<br/>'; $failed++; }else{ echo '第' . ($row_index) . '列: Success.<br/>'; $success++; } }else{ echo '第' . ($row_index) . '列: Failed. ' . implode(';', $row_errs) . '<br/>'; $failed++; } } echo '完成, 成功:' . $success . '筆, 失敗: ' . $failed; } |
Form Example (modal)
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 |
<div class="modal fade" tabindex="-1" role="dialog" id="md_import"> <div class="modal-dialog" role="document"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button> <h4 class="modal-title">Import Dealers</h4> </div> <div class="modal-body"> <form name="gform" id="gform" action="/admin/{{$controller}}/import" class="modal-form" method="POST" enctype="multipart/form-data"> <div class="form-group"> <label for="name" class="col-lg-2 control-label">File</label> <div class="col-lg-10 filled"> <input type="file" name="file"> </div> </div> <input type="hidden" name="country" value=""> {{ csrf_field() }} </form> </div> <div class="modal-footer"> <button type="button" class="btn btn-primary bt_submit">Submit</button> <button type="button" class="btn btn-default" data-dismiss="modal">Close</button> </div> </div><!-- /.modal-content --> </div><!-- /.modal-dialog --> </div><!-- /.modal --> |
Javascript Example
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 |
var import_app = function(){ var $c = null; var $form = null; function init(){ $c = $('#md_import'); $form = $c.find('form'); $c.find('.bt_submit').on('click', function(e){ e.preventDefault(); $form.submit(); }); } function show(country_code){ clear(); $c.modal(); set_value('country', country_code, $c); } function clear(){ set_value('country', '', $c); } return { init: init, show: show } }(); |
