phpspreadsheet 引入
由于PHPExcel早就停止更新维护,所以适用phpspreadsheet。不知道如何通过composer拉取项目包的同学,可以查看Composer学习一文。引入方法:
composer require phpoffice/phpspreadsheet
注:可能会出现报错:Your requirements could not be resolved to an installable set of packages,这是因为不匹配composer.json要求的版本,可以在命令后面加上 –ignore-platform-req 来忽略版本匹配 或 加上具体版本号
PhpSpreadsheet的基本使用
注:我使用的是thinkphp5.0框架
引入命名空间(可按需引入)
namespace app\admin\controller;use PhpOffice\PhpSpreadsheet\IOFactory;use PhpOffice\PhpSpreadsheet\Spreadsheet;use PhpOffice\PhpSpreadsheet\Writer\Xlsx;use PhpOffice\PhpSpreadsheet\Reader\Xlsx;use PhpOffice\PhpSpreadsheet\Reader\Xls;
Excel导入操作 (例子)
public function import_excel(){ //获取表格的大小,限制上传表格的大小5M $file_size = $_FILES['file']['size']; if ($file_size > 5 * 1024 * 1024) { $this->error('文件大小不能超过5M'); exit(); } //限制上传表格类型 $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1); if (!in_array($fileExtendName, ['xls','xlsx'])) { $this->error('必须为excel表格,且必须为xlsx或xls格式!'); exit(); } if (is_uploaded_file($_FILES['file']['tmp_name'])) { // 有Xls和Xlsx格式两种 $objReader = IOFactory::createReader('Xlsx'); $file_name = $_FILES['file']['name']; $filename = $_FILES['file']['tmp_name']; if (!file_exists($filename)) { die('no file!'); } $extension = strtolower( pathinfo($file_name, PATHINFO_EXTENSION) ); if ($extension =='xlsx') { $objReader = IOFactory::createReader('Xlsx'); $objPHPExcel = $objReader ->load($filename); } else if ($extension =='xls') { $objReader = new \PhpOffice\PhpSpreadsheet\Reader\Xls(); $objPHPExcel = $objReader ->load($filename); } else if ($extension=='csv') { $PHPReader = new \PhpOffice\PhpSpreadsheet\Reader\Csv(); $PHPReader->setInputEncoding('GBK'); //默认输入字符集 $PHPReader->setDelimiter(','); //默认的分隔符 $objPHPExcel = $PHPReader->load($filename); //载入文件 } $objReader->setReadDataOnly(true);// 如果不需要获取特殊操作,则只读内容,可以大幅度提升读取Excel效率 $sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet //已有用户列表 $had_userlist = db('userinfo')->where($where)->select(); $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 $sheetData = $objPHPExcel->getActiveSheet()->ToArray(); // 转成数组 // 测试获取值的方式(可忽略) // $sheetData = $objPHPExcel->getActiveSheet()->rangeToArray("A1:G".$highestRow); // $sheetData= $objPHPExcel->getActiveSheet()->getCell("E3")->getValue(); // $sheetData= $objPHPExcel->getActiveSheet()->getCell("E3")->getCalculatedValue(); // $sheetData= $objPHPExcel->getActiveSheet()->getCell("D3")->getOldCalculatedValue(); // return $sheetData; //数据的顺序排列(为了获取对应字段的值) $sort = ['name' => '', 'stu' => '', 'sex' => '', 'number' => '', 'phone' => '', 'class' => '']; for($i=0; $i < 2; $i++){ for($key = 0; $key < count($sheetData[$i]); $key++){ $str = $sheetData[$i][$key]; if( in_array($str, ['姓名','名字','学生姓名']) ){ $sort['name'] = $key; }elseif( in_array($str, ['工号','学号']) ){ $sort['stu'] = $key; }elseif( in_array($str, ['性别']) ){ $sort['sex'] = $key; }elseif( in_array($str, ['手机','手机号','手机号码']) ){ $sort['phone'] = $key; }elseif( in_array($str, ['身份证号','身份证','身份证号码','证件号码']) ){ $sort['number'] = $key; }elseif( in_array($str, ['班级','院系']) ){ $sort['class'] = $key; } } } // return $sort; // 定义$usersExits,循环表格的时候,找出已存在的用户。 $usersExits = []; foreach($sheetData as $k => $v){ if($k == 0){ unset($sheetData[0]);continue; } $id_number = empty($sort['number']) ? '' : $v[$sort['number']]; if(!$id_number || !validateIDCard($id_number)){ unset($sheetData[$k]);continue; } //其他数据处理。。。 $data[$k] = [ 'stu_number' => empty($sort['stu']) ? '' : $v[$sort['stu']], 'username' => empty($sort['name']) ? '' : $v[$sort['name']], 'id_number' => $id_number, 'phone' => empty($sort['phone']) ? '' : $v[$sort['phone']],'create_time' => time() ]; //看下用户是否存在。将存在的用户保存在数组里。 foreach($had_userlist as $k2=>$v2){ if($id_number == $v2['id_number']){ $data[$k]['id'] = $v2['id']; array_push($usersExits, $data[$k]); unset($data[$k]); break; } } } //如果有已存在的用户名,就不插入数据库了,直接更新。 if ($usersExits != []) { foreach($usersExits as $k3=>$v3){ $udata = [ 'stu_number' => $v3['stu_number'], 'username' => $v3['username'], 'id_number' => $v3['id_number'], 'phone' => $v3['phone'], 'level' => $v3['level'], 'sex' => $v3['sex'], 'classroom_id' => $v3['classroom_id'], ]; db('userinfo')->where($where)->update($udata); } } if($data){ $res = db('userinfo')->insertAll($data); //全部插入数据库 } return writeJson(200, '', '导入成功!'); }}
Excel导出操作 (例子)
public function excelExport($fileName = '', $headArr = [], $data = [], $auto = true, $format="Xlsx"){ $dir = rtrim(str_replace('\\', '/', $_SERVER['DOCUMENT_ROOT']), '/') . '/public/excel'; if (!is_dir($dir)) { mkdir($dir, 0777, true); } $fileName .= ".".$format; $objPHPExcel = new Spreadsheet(); $objPHPExcel->getProperties(); $key = ord("A"); // 设置表头 $key2 = ord("@"); //超过26列会报错的解决方案 // 居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); // 设置表头 foreach ($headArr as $v) { // 超过26列会报错的解决方案 if ($key > ord("Z")) { $key2 += 1; $key = ord("A"); $colum = chr($key2) . chr($key); //超过26个字母时才会启用 } else { if ($key2 >= ord("A")) { $colum = chr($key2) . chr($key); } else { $colum = chr($key); } } // 写入表头 $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v); // 自适应宽度 if ($auto) { // $len = strlen(iconv('utf-8','gb2312',$v));//会报错 $len = strlen(iconv('utf-8', 'gbk', $v)); $len = $len > 25 ? 30 : 20; $objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth($len+5); } $key += 1; } $column = 2; $objActSheet = $objPHPExcel->getActiveSheet(); // 写入行数据 foreach ($data as $key => $rows) { $span = ord("A"); $span2 = ord("@"); // 按列写入 foreach ($rows as $keyName => $value) { // 超过26列会报错的解决方案 if ($span > ord("Z")) { $span2 += 1; $span = ord("A"); $tmpSpan = chr($span2) . chr($span); //超过26个字母时才会启用 } else { if ($span2 >= ord("A")) { $tmpSpan = chr($span2) . chr($span); } else { $tmpSpan = chr($span); } } // 写入数据 $objActSheet->setCellValue($tmpSpan . $column, $value); $span++; } $column++; } // 自动加边框 $styleThinBlackBorderOutline = array( 'borders' => array( 'allborders' => array( //设置全部边框 'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //粗的是thick ), ), ); $objPHPExcel->getActiveSheet()->getStyle('A1:' . $colum . --$column)->applyFromArray($styleThinBlackBorderOutline); // 重命名表 $fileName = iconv("utf-8", "gbk", $fileName); // 设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); // header("Content-Type: application/octet-stream"); 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"); header('Cache-Control: max-age=0'); //以下为需要用到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 $writer = IOFactory::createWriter($objPHPExcel, 'Xlsx'); // $writer = new Xlsx($objPHPExcel); // $writer->save('php://output'); // 文件通过浏览器下载(不知为何直接打印出来一大堆乱码,不能直接下载) $writer->save($dir.'/'.$fileName);// 文件下载到服务器 $result = [ 'fileurl' => 'http://'.$_SERVER['HTTP_HOST'].'/public/excel/'.$fileName, 'filename' => $fileName, ]; return $result; exit();}
更多的Excel操作可以去查文档或百度┗( ▔, ▔ )┛
官网文档:https://phpspreadsheet.readthedocs.io/en/stable/ (注:这是英文文档,中文的暂未找到)
来源地址:https://blog.csdn.net/BlizzardWu/article/details/128147444