废话不多说，直接上代码
用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', '请上传需转换文件');
}
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');//设置以Excel5格式(Excel97-2003工作簿)
$phpExcel = $reader->load($filePath . $fileName);// 载入excel文件
$excelSheet = $phpExcel->getSheet(0);// 读取第一个工作表
$highestRow = $excelSheet->getHighestRow();// 取得总行数
$highestColumn = $excelSheet->getHighestColumn();// 取得总列数
$bool = $this->verifyTemplateGraduationInfo($excelSheet, $highestColumn);
// 获取单元格数据
$recordData = []; // 行号从1开始
for ($row = 2; $row <= $highestRow; $row++)
{
// 列数是以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');
// 重组数据
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);
// 将重组数据导成excel文件
$newFileName = $this->entrustExport($recordData);
if (file_exists($filePath . $newFileName)) {
return redirect($this->getUrlPrefixConfig() . '/record/fileDownload/' . $newFileName)->withInput()->with('FileEntrustSuccess', '转换成功');
}
return back()->withInput()->with('FileEntrustError', '转换失败');
}
这是例子用到的处理单元格方法：
private function handleStr($str)
{
$str = trim($str); //清除字符串两边的空格
$str = preg_replace("/\t/","",$str); //使用正则表达式替换内容，如：空格，换行，并将替换为空。
$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); //返回字符串
}
这是例子用到的重组数据方法
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;
}
