參考:https://blog.csdn.net/jackbon8/article/details/107940638
1.匯出表格
<?php namespace app # 給類檔案的名稱空間起個別名 use PhpOffice\PhpSpreadsheet\Spreadsheet; # Xlsx類 將電子表格儲存到檔案 use PhpOffice\PhpSpreadsheet\Writer\Xlsx; # 例項化 Spreadsheet 物件 $spreadsheet = new Spreadsheet(); # 獲取活動工作薄 $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1','10'); $sheet->setCellValue('B1','15'); $sheet->setCellValue('C1','20'); $sheet->setCellValue('D1','25'); $sheet->setCellValue('E1','30'); $sheet->setCellValue('G1','35'); $sheet->setCellValue('A2', '總數:'); $sheet->setCellValue('B2', '=SUM(A1:G1)'); $sheet->setCellValue('A3', '平均數:'); $sheet->setCellValue('B3', '=AVERAGE(A1:G1)'); $sheet->setCellValue('A4', '最小數:'); $sheet->setCellValue('B4', '=MIN(A1:G1)'); $sheet->setCellValue('A5', '最大數:'); $sheet->setCellValue('B5', '=MAX(A1:G1)'); $sheet->setCellValue('A6', '最大數:'); $sheet->setCellValue('B6', '\=MAX(A1:G1)'); // 使用跳脫字元 // 批次賦值 $sheet->setCellValue('A1','ID'); $sheet->setCellValue('B1','姓名'); $sheet->setCellValue('C1','年齡'); $sheet->setCellValue('D1','身高'); $sheet->fromArray( [ [1,'歐陽克','18歲','188cm'], [2,'黃蓉','17歲','165cm'], [3,'郭靖','21歲','180cm'] ], 3, 'A2' ); // 合併單元格 合併後,賦值只能給A1,開始的座標。 $sheet->mergeCells('A1:B5'); $sheet->getCell('A1')->setValue('歐陽克'); # Xlsx類 將電子表格儲存到檔案 $writer = new Xlsx($spreadsheet); $writer->save('1.xlsx'); // 客戶端檔案下載 header('Content-Type:application/vnd.ms-excel'); header('Content-Disposition:attachment;filename=1.xls'); header('Cache-Control:max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls'); $writer->save('php://output');
2.讀取表格
<?php namespace app; # 建立讀操作 $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); # 開啟檔案、載入excel表格 $spreadsheet = $reader->load('1.xlsx'); # 獲取活動工作薄 $sheet = $spreadsheet->getActiveSheet(); # 獲取 單元格值 和 座標 $cellC1 = $sheet->getCell('B2'); echo '值: ', $cellC1->getValue(),PHP_EOL; echo '座標: ', $cellC1->getCoordinate(),PHP_EOL; $sheet->setCellValue('B2','歐陽鋒'); # 獲取 單元格值 和 座標 $cellC2 = $sheet->getCell('B2'); echo '值: ', $cellC2->getValue(),PHP_EOL; echo '座標: ', $cellC2->getCoordinate();
3.匯入功能
<?php $file = $_FILES['file']['tmp_name']; # 載入composer自動載入檔案 require 'vendor/autoload.php'; # 載入方法庫 require 'function.php'; # 建立讀操作 $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); # 開啟檔案、載入excel表格 $spreadsheet = $reader->load($file); # 獲取活動工作薄 $sheet = $spreadsheet->getActiveSheet(); # 獲取總列數 $highestColumn = $sheet->getHighestColumn(); # 獲取總行數 $highestRow = $sheet->getHighestRow(); # 列數 改為數字顯示 $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); $log = []; for($a=2;$a<$highestRow;$a++){ $title = $sheet->getCellByColumnAndRow(1,$a)->getValue(); $cat_fname = $sheet->getCellByColumnAndRow(2,$a)->getValue(); $cat_name = $sheet->getCellByColumnAndRow(3,$a)->getValue(); $price = $sheet->getCellByColumnAndRow(4,$a)->getValue(); $img = $sheet->getCellByColumnAndRow(5,$a)->getValue(); $cat_fid = find('shop_cat','id','name="'.$cat_fname.'"'); $cat_id = find('shop_cat','id','name="'.$cat_name.'"'); $data = [ 'title' => $title, 'cat_fid' => $cat_fid['id'], 'cat_id' => $cat_id['id'], 'price' => $price, 'img' => $img, 'add_time' => time(), ]; $ins = insert('shop_list',$data); if($ins){ $log[] = '第'.$a.'條,插入成功'; }else{ $log[] = '第'.$a.'條,插入失敗'; } } echo json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);