前段时间刚好有个项目需要用到,所以在这里记录一下,话不多说直接上代码
use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\cell\Coordinate; /** * 提交EXECL表格带图片导入导入数据 */ public function upload_field_excel_post() { $file_url = $this->request->param('file_url'); //文件路径 if (!empty($file_url)) { $filePath = $_SERVER['DOCUMENT_ROOT'] . '/upload/' . $file_url; //如果文件存在 if (file_exists($filePath)) { //检查文件是否存在 $exts = pathinfo($filePath, PATHINFO_EXTENSION); //文件类型 // 有Xls和Xlsx格式两种 if ($exts == 'xlsx') { $objReader = IOFactory::createReader('Xlsx'); } else { //$objReader = IOFactory::createReader('Xls'); $info = "请使用'Xlsx'格式文件进行带图片上传,否则图片上传失败!"; $this->error(['code' => 0, 'msg' => $info]); } $imageFilePath1 = root_path() . '/public/upload/';//图片保存目录 $imageFilePath2 = 'admin/images/' . date("Ymd") . '/'; $imageFilePath = $imageFilePath1 . $imageFilePath2; if (!file_exists($imageFilePath)) { mkdir("$imageFilePath", 0777, true); } $objSpreadsheet = $objReader->load($filePath); $objWorksheet = $objSpreadsheet->getActiveSheet(0); //getSheet(0) $highestRow = $objWorksheet->getHighestRow(); // 取得总行数 $data = $objWorksheet->toArray(); //$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例; 仅仅支持xlsx格式文件 foreach ($objWorksheet->getDrawingCollection() as $drawing) { list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates()); $imageFileName = $drawing->getIndexedFilename(); //获取文件名称 switch ($drawing->getExtension()) { case 'jpg': case 'jpeg': // $imageFileName .= '.jpg'; $source = imagecreatefromjpeg($drawing->getPath()); imagejpeg($source, $imageFilePath . $imageFileName); break; case 'gif': $imageFileName .= '.gif'; $source = imagecreatefromgif($drawing->getPath()); imagegif($source, $imageFilePath . $imageFileName); break; case 'png': $imageFileName .= '.png'; $source = imagecreatefrompng($drawing->getPath()); imagepng($source, $imageFilePath . $imageFileName); break; } $startColumn = $this->ABC2decimal($startColumn); $data[$startRow - 1][$startColumn] = $imageFilePath2 . $imageFileName; } //从第二行开始 for ($i = 1; $i <= $highestRow - 1; $i++) { $add_data[$i]['name'] = $data[$i][0]; $add_data[$i]['image'] = $data[$i][1]; } $success_count = Db::name('test')->insertAll($add_data); //unlink($filePath); //删除文件 //判断导入成功数量 if ($success_count == $highestRow - 1) { $info = '导入成功!本次成功导入数量:' . $success_count . '条'; $this->success($info); } else { $info = '导入成功!本次成功导入字段数量:' . $success_count . '条,无效数据或已重复上传' . ($highestRow - 1) - $success_count . '条,与目标数不符!'; $this->success($info); } } else { $info = "文件不存在,文件上传失败!"; $this->error(['code' => 0, 'msg' => $info]); } } else { $info = "请上传文件!"; $this->error(['code' => 0, 'msg' => $info]); } } /** * 字母序列化为数字 */ public function ABC2decimal($abc) { $ten = 0; $len = strlen($abc); for ($i = 1; $i <= $len; $i++) { $char = substr($abc, 0 - $i, 1);//反向获取单个字符 $int = ord($char); $ten += ($int - 65) * pow(26, $i - 1); } return $ten; } /** * 下载数据EXECL表格导出 */ public function export() { $newExcel = new Spreadsheet(); //创建一个新的excel文档 $objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象 //设置宽度为true,不然太窄了 $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3); $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); //获取数据库的数据。 $List = Db::name('user')->select(); $objSheet->setTitle('班级表'); //设置当前sheet的标题 //设置第一栏的标题 $objSheet->setCellValue('A1', 'ID') ->setCellValue('B1', '图片'); foreach ($List as $k => $val) { $k = $k + 2; $objSheet->setCellValue('A' . $k, $val['id']); //数据行 // 获取本地文件夹路径 $dir = $_SERVER['DOCUMENT_ROOT'] . '/upload/'; $file_info = pathinfo($val['avatar']); // 过滤非文件类型 if (!empty($file_info['basename'])) { $basename = $file_info['basename']; // 进行检测文件是否存在 is_dir($dir) OR mkdir($dir, 0777, true); file_put_contents($dir . $basename, $val['avatar']); // 引入操作图片类 $drawing[$k] = new Drawing(); $drawing[$k]->setName('GoodsCode'); $drawing[$k]->setDescription('GoodsCode'); $drawing[$k]->setPath($dir . $val['avatar']); $drawing[$k]->setWidth(80); $drawing[$k]->setHeight(80); $drawing[$k]->setCoordinates('B' . $k); $drawing[$k]->setOffsetX(10); $drawing[$k]->setOffsetY(10); $drawing[$k]->setWorksheet($newExcel->getActiveSheet()); } // 每行高度设置 $objSheet->getRowDimension($k)->setRowHeight(80); } $this->downloadExcel($newExcel, '表1', 'Xls'); } /** * 下载数据EXECL表格导出 * 'newExcel' => 'EXECL表格', * 'filename' => '文件名称', * 'format'=> 文件后缀, */ function downloadExcel($newExcel, $filename, $format) { // $format只能为 Xlsx 或 Xls if ($format == 'Xlsx') { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); } elseif ($format == 'Xls') { header('Content-Type: application/vnd.ms-excel'); } header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format)); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($newExcel, $format); $objWriter->save('php://output'); //通过php保存在本地的时候需要用到 //$objWriter->save($dir.'/demo.xlsx'); //以下为需要用到IE时候设置 // 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 1997 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 exit; }
本站部分文章搜集整理于互联网或者网友提供,如有侵权请联系站长
如若转载,请注明出处:https://www.htmlbk.com/php/1187.html
温馨提示:该文档最后一次修改时间为2022-09-26 17:15:41,请注意相关的内容是否还可用!