廢話不多說，直接上代碼
用composer安裝PhpOffice\PhpSpreadsheet包
composer?require?phpoffice/phpspreadsheet
例子
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
public?function?importToExport(Request?$request) ????
{ ????????
????//?判斷文件格式 ????????
????$file?=?$request->file('import'); ????????
????if?(empty($file))?{ ????????????
????????return?back()->withInput()->with('questionError',?'請上傳需轉(zhuǎn)換文件'); ????????
????}? ????????
????if?(!in_array($file->getClientOriginalExtension(),?['xls',?'xlsx']))?{ ????????????
????????return?back()->withErrors(['import'?=>?'文件僅支持xls、xlsx'])->withInput(); ????????
????} ????????
????//?獲取文件 ????????
????$fileName?=?time().'.'?.?$file->getClientOriginalExtension();? ? ? ?????????????$filePath?=?public_path('uploads/admin/recode/'); ????????
????$file->move($filePath,?$fileName); ????????
????//?phpExcel初始化 ????????
????$reader??=?IOFactory::createReader('Xlsx');//設(shè)置以Excel5格式(Excel97-2003工作簿)? ? ??
????$phpExcel?=?$reader->load($filePath?.?$fileName);//?載入excel文件 ????????
????$excelSheet?=?$phpExcel->getSheet(0);//?讀取第一個工作表 ????????
????$highestRow?=?$excelSheet->getHighestRow();//?取得總行數(shù) ????????
????$highestColumn?=?$excelSheet->getHighestColumn();//?取得總列數(shù) ????????
????$bool?=?$this->verifyTemplateGraduationInfo($excelSheet,?$highestColumn); ????????
????//?獲取單元格數(shù)據(jù) ????????
????$recordData?=?[]; ????????//?行號從1開始 ????????
????for?($row?=?2;?$row?<=?$highestRow;?$row++) ????????
????{ ???????????
?????//?列數(shù)是以A列開始 ????????????
????????for?($col?=?'A';?$col?<=?$highestColumn;?$col++) ????????????
????????{ ????????????????
????????????$cell?=?$excelSheet->getCell(?$col?.?$row)->getValue(); ????????????????
????????????if(is_object($cell))?{ ????????????????????
????????????????$cell=?$cell->__toString(); ????????????????
????????????}; ????????????????
????????????$recordData[$row][$col]?=?$this->handleStr($cell); ????????????
????????} ????????
????} ???????? ???????
?????$time?=?date('Y-m-d?H:i:s'); ????????
?????//?重組數(shù)據(jù) ????????
????foreach?($recordData?as?$key=>$value)?{
????????$recordInfo?=?RecordDataModel::where('sge_order_no',?$value['sge_order_no'])->first(); ???????? ????????????if?(!$recordInfo)?{ ????????????????
????????????????$recordData[$key]['uid']?=?'未查詢到'; ????????????
????????????}else{ ????????????????
????????????????$recordData[$key]['uid']?=?(string)$recordInfo->channel_user_id; ????????????
????????????} ????????
?????} ????????
????//?刪除文件 ????????
????File::delete($filePath?.?$fileName); ????????
????//?將重組數(shù)據(jù)導(dǎo)成excel文件 ????????
????$newFileName?=?$this->entrustExport($recordData); ????????
????if?(file_exists($filePath?.?$newFileName))?{ ????????????
????????return?redirect($this->getUrlPrefixConfig()?.?'/record/fileDownload/'?.?$newFileName)->withInput()->with('FileEntrustSuccess',?'轉(zhuǎn)換成功'); ???????
?????} ????????
????return?back()->withInput()->with('FileEntrustError',?'轉(zhuǎn)換失敗'); ????
}
這是例子用到的處理單元格方法：
? ? private?function?handleStr($str) ????
????{ ????????
????????$str?=?trim($str);?//清除字符串兩邊的空格 ????????
????????$str?=?preg_replace("/\t/","",$str);?//使用正則表達式替換內(nèi)容，如：空格，換行，并將替換為空。
????????$str?=?preg_replace("/\r\n/","",$str); ????????
????????$str?=?preg_replace("/\r/","",$str); ????????
????????$str?=?preg_replace("/\n/","",$str); ????????
????????$str?=?preg_replace("/?/","",$str); ????????
????????$str?=?preg_replace("/??/","",$str);??//匹配html中的空格 ????????
????????return?trim($str);?//返回字符串 ????
????}
這是例子用到的重組數(shù)據(jù)方法
?private?function?entrustExport($recordData) ????
{? ? ? ? ?
????$spreadsheet?=?new?Spreadsheet(); ????????
????$sheet???????=?$spreadsheet->getActiveSheet(); ????????
????$sheet->setCellValue('A1',?'成交日期'); ????????
????$sheet->setCellValue('B1',?'成交時間'); ????????
????$sheet->setCellValue('C1',?'客戶號'); ????????
????$sheet->setCellValue('D1',?'銀行賬號'); ????????
????$sheet->setCellValue('E1',?'客戶簡稱'); ????????
????$sheet->setCellValue('F1',?'UID'); ????????
????$i?????=?1; ????????
????foreach?($recordData?as?$data)?{ ????????????
????????$i++; ????????????
????????$sheet->setCellValue('A'?.?$i,?$data['A']); ????????????
????????$sheet->setCellValue('B'?.?$i,?$data['B']); ????????????
????????$sheet->setCellValue('C'?.?$i,?$data['C']); ????????????
????????$sheet->setCellValue('D'?.?$i,?$data['D']); ????????????
????????$sheet->setCellValue('E'?.?$i,?$data['E']); ????????????
????????$sheet->setCellValue('F'?.?$i,?$data['uid']); ????????
????} ????????
????//?保存文件 ????????
????$fileName?=?'Transaction-'?.?date('YmdHmi')?.?'.xlsx';? ? ?????????$filePath?=?public_path('uploads/admin/recode/'); ????????
????$writer?=?new?Xlsx($spreadsheet); ????????
????$fileUrl?=?$filePath.$fileName; ????????
????$writer->save($fileUrl); ????????
????return?$fileName; ???????? ????
}
