【PHP】导入计算表格列数的两个方法对比 /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/4 14:35 * @param string $col * @throws Exception * @Description：根据字母获取字段列长 根据列数的最大值返回最大范围内的列值 */ public function getColumnArrByMaxCol($col = ''){ if(empty($col)) throw new Exception("不能为空~"); if(strlen($col) > 2) throw new Exception("导入列数超出最大值~"); $col = strtoupper($col); $column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; $colArr = []; if(in_array($col,$column_word)){ for ($i=0;$i<=25;$i++){ if($column_word[$i] == $col) { array_push($colArr,$column_word[$i]); return $colArr; } array_push($colArr,$column_word[$i]); } } $firstColumn = substr($col,0,1); $twoColumn = substr($col,1,1); if(!in_array($firstColumn,$column_word)) throw new Exception("错误的参数"); if(!in_array($twoColumn,$column_word)) throw new Exception("错误的参数"); $firstColumnNum = array_keys($column_word,$firstColumn); // $twoColumnNum = array_keys($column_word,$twoColumn); $colArr = $column_word; for($a = 0; $a <= $firstColumnNum[0]; $a++){ for($b = 0; $b <= 25; $b++){ if($twoColumn == $column_word[$b] && $firstColumnNum[0] == $a){ array_push($colArr,$column_word[$a].$column_word[$b]); return $colArr; } array_push($colArr,$column_word[$a].$column_word[$b]); } } } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/9/9 13:40 * @param string $col * @return mixed * @throws Exception * @Description：根据字母获取字段列长 根据列数的最大值返回最大范围内的列值 上面的方法在业务上有点慢，这里是重新改了一下 */ public function getColumnArrByMaxColtwo($col = ''){ if(empty($col)) throw new Exception("不能为空~"); $col= strtoupper($col); //转大写 $strLength = strlen($col); //获取长度 $column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; //基础数据 $column_word_vk = array_flip($column_word); //数组kv掉换 $b = $strLength; $maxNumb = []; //验证并计算循环次数 for($strl = 0; $strl < $strLength; $strl++){ $strAloneWord = substr($col,$strl,1); if(!in_array($strAloneWord,$column_word)){ throw new Exception("错误的参数"); } $columnNumber[] = $column_word_vk[$strAloneWord]+1; $maxNum = pow(26,$b-$strl-1); $maxNumb[$strl] = $maxNum*$columnNumber[$strl]; } $totolTimes = ceil(array_sum($maxNumb)/26);//向上取整 循环次数 //数据拼接 限制在两位字母最大ZZ for($c = 0; $c < $totolTimes; $c++) { $first_word = $column_word[$c-1]; foreach($column_word as $key => $val){ if($c >= 1){ $word = $first_word.$column_word[$key]; }else{ $word = $column_word[$key]; } $column[] = $word; } } for($a = 0; $a < array_sum($maxNumb); $a++){ $new_column[] = $column[$a]; } return $new_column; }

【PHP】 使用PHPoffice实现普通的导出功能（二） 第二版 使用PHPoffice实现普通的导出功能<?php namespace app\index\controller; use app\index\controller\Comm; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use think\Exception; use think\Request; class Importsheet extends comm{ private $sheet_filename; private $sheet_name; private $sheet_firstline = []; private $sheet_info = []; private $imgSwitch; private $switch; private $path = "uploads/excel/"; /** * Importsheet constructor. * @param $filename 文件名 * @param $name sheet名 * @param $firstline 表头 * @param $info 表内容 * @param $imgSwitch 图片开关 * @param $switch 返回下载地址还是直接返回文件 */ public function __construct($filename='Default',$name='sheet1',$firstline = [],$info = [],$imgSwitch = false, $switch = false) { parent::__construct(); $this->sheet_filename = $filename; $this->sheet_name = $name; $this->sheet_firstline = $firstline; $this->sheet_info = $info; $this->imgSwitch = $imgSwitch; $this->switch = $switch; } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2020/12/23 16:08 * @param int $column_num * @return mixed * @Description：获取表格列数的字母 */ public function getMaxColumn(int $column_num) { try{ if(empty($column_num)){ throw new Exception('column_num:列数为空~'); } if(!is_int($column_num)){ throw new Exception('column_num:参数类型错误~'); } if($column_num > 26*26 || $column_num < 0){ throw new Exception('最大列数：676列,最小列数：1列'); } $column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; //生成循环次数 $num = ceil($column_num/26); for($c = 0; $c < $num; $c++) { $first_word = $column_word[$c-1]; foreach($column_word as $key => $val){ if($c >= 1){ $word = $first_word.$column_word[$key]; }else{ $word = $column_word[$key]; } $column[] = $word; } } for($a = 0; $a < $column_num; $a++){ $new_column[] = $column[$a]; } return $new_column; }catch (Exception $e){ returnResponse(100,$e->getMessage()); } } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2020/12/23 17:54 * @Description：输出表 */ public function outputSheet() { try{ $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); //设置sheet的名字 $sheet->setTitle($this->sheet_name); //默认表头第一行 $k = 1; //生成列的个数，根据表头个数来定 $column_num = count($this->sheet_firstline); $info_field_num = count($this->sheet_info[0]); if($column_num != $info_field_num){ if($column_num > $info_field_num){ $better_column_info = '表头多'; }else{ $better_column_info = '数据列多'; } throw new Exception('结果集列数和表头列数不一致~'.$better_column_info); } //生成表头上方的字母（最大676，最小1） $column_word = $this->getMaxColumn($column_num); //设置表头 for($i=0;$i<$column_num;$i++){ $sheet->setCellValue($column_word[$i].$k, $this->sheet_firstline[$i]); } //第二行开始插入数据 $k = 2; //插入表格数据 foreach ($this->sheet_info as $key => $value) { $b = 0; for($a = 0; $a < $column_num; $a++){ $getvalbykey = array_values($value); if($this->imgSwitch){ /*写入图片*/ $files_arr = explode('.', $getvalbykey[$b]); if(!empty($files_arr)){ $file_suffix = array_pop($files_arr); strtolower($file_suffix); $suffix = ['jpg', 'jpeg', 'gif', 'bmp', 'png','pdf','doc','docx','xlsx','xls']; if(in_array($file_suffix,$suffix)){ $str_thumb = str_replace($this->request->domain(),'',$getvalbykey[$b]); $thumb = '/home/wwwroot/xphbk/public'.$str_thumb; if($thumb){ $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing ->setName('图片'); $drawing ->setDescription('图片'); $drawing ->setPath($thumb); $drawing ->setWidth(80); $drawing ->setHeight(80); $drawing ->setCoordinates($column_word[$a].$k); $drawing ->setOffsetX(0); $drawing ->setOffsetY(0); $drawing ->setWorksheet($spreadsheet->getActiveSheet()); } }else{ $sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]); } $b++; } }else{ $sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]); $b++; } } $k++; } //文件名 $file_name = date('Y-m-d H:i:s', time()).'-'.rand(1000, 9999).'_'. $this->sheet_filename . ".xlsx"; //下载 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$file_name.'"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); if($this->switch == false){ $path = self::createPath($this->path); $writer->save($path.$file_name); // $url = 'http://'.$_SERVER['SERVER_NAME'].'/public/'.$path.$filename; $url = 'http://'.$_SERVER['SERVER_NAME'].'/'.$path.$file_name; }else{ return $writer->save('php://output'); } return $url; }catch (Exception $e){ returnResponse(100,$e->getMessage()); } } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/4/12 13:36 * @param $path * @Description：设置路径判断是否存在，不存在创建 */ private function createPath($path){ $Month = date('Ym',time()); $Day = date('d',time()); $path = $this->path.$Month.'/'.$Day.'/'; if(!is_dir($path)){ header("Content-type:text/html;charset=utf-8"); $res = mkdir(iconv("UTF-8", "GBK", $path),0777,true); if(!$res){ throw new Exception("创建目录失败"); } } return $path; } }使用图片导出的时候注意限制条数，图片导出比较费时间，在我自己的项目中，每次导出大概有五百条数据，是没有问题，具体的还要各位使用者再测使用方法：复制粘贴+稍微改改，就能用了

【PHP】使用PHPoffice实现普通的导入功能 解决普通导入的问题，暂不能解决导入图片相关问题，后续遇到相关业务在处理<?php namespace app\index\lib\xlsx; use app\index\lib\xlsx\import as aaa; use think\Controller; use think\Exception; use think\exception\ErrorException; use think\Log; class import extends Controller { /** * 注意事项： * 1. $fileSize已经转换成字节，无需再次转换，所以单位（M），默认5M； * 2. 如果需要更 文件路径，扩展名，记录表名，在公共定义的地方修改即可； * 3. 导入记录默认开启，如果关闭，将不再有数据表的记录任何信息（不包含相关日志）； * 4. 关闭导入记录 将true改为false; * 5. 使用前需要引入phpoffice扩展； * 6. 如果需要重新处理表格数据， * 6-1. 将$dealData 传值 true; * 6-2. 需要重新调用批量添加的方法$this->insertAllData($data); * * 使用方法： * 1. 实例化当前类 * 2. 同时传参(*：必须；-：非必须)： *表名 *表字段 *上传文件接收的名字 -是否需要返回表格数据 -文件限制大小 * 3. 调用upload方法即可 * * * 示例： * namespace app\index\controller; * use think\Controller; * use app\index\lib\xlsx\import as aaa; * class A extends Controller * { * public function aa(){ * $tableFile = ['a','b','c','d','e','f','g','h']; * $imports = new aaa('test',$tableFile,'file'); * if($this->request->file('file')){ * return $imports->upload(); * }else{ * return "<form action='/public/index.php/index/a/aa' enctype='multipart/form-data' method='post'> * <input type='file' name='file' /> <br> * <input type='submit' value='上传' /> * </form> "; * } * } * } */ public $fileSize; //文件大小 注意：已经转换成字节，所以单位（M） public $fileName; //接受文件的名字 public $filePath = 'uploads/importxlsx'; //文件路径 public $suffix; //文件后缀 public $suffixArr = ['xlsx', 'xls', 'Xlsx', 'Xls']; //文件后缀范围 public $tableName; //数据表名 public $tableField; //数据表字段 public $switchLog = true; //导入记录 public $logTableName = 'xlsximportlog'; //记录表名称 public $dealData; //是否返回数据重新处理 /** * import constructor. 实例化自动执行 * @param string $tableName 表名 * @param array $tableField 数据库字段 * @param string $fileName 上传文件的名字 * @param string $fileSize 长传文件的大小 注意：已经转换成字节，所以单位（M） */ public function __construct($tableName = '', $tableField = [], $fileName = '', $dealData = false, $fileSize = '5') { parent::__construct(); $this->tableName = $tableName; $this->tableField = $tableField; $this->fileName = $fileName; $this->dealData = $dealData; $this->fileSize = $fileSize*1024*1024; } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/2 15:07 * @Description：单文件上传 * 成功上传后 获取上传信息 *print_r($file->getInfo()); *[ * ['name'] => '2021-07-31 15_50_18-5822_商务部在办企业进度及时限表.xlsx', * ['type'] => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', * ['tmp_name'] => 'C:\Users\EDZ\AppData\Local\Temp\phpEAA4.tmp', * ['error'] => '0', * ['size'] => '132892' *]; *echo $info->getExtension(); //输出 jpg *echo $info->getSaveName(); //输出 20160820/42a79759f284b767dfcb2a0197904287.jpg *echo $info->getFilename(); //输出 42a79759f284b767dfcb2a0197904287.jpg */ public function upload(){ $file = request()->file($this->fileName); $path = ROOT_PATH . 'public' . DS . $this->filePath; $info = $file->validate(['size'=>$this->fileSize,'ext' => $this->suffixArr])->move($path); if($info){ $this->path = $path.'/'.$info->getSaveName(); $this->suffix = $info->getExtension(); $res = self::import($file->getInfo()); if($this->switchLog){ self::importLog($this->path,count($res),$file->getInfo['name'],$file->getInfo['size'],$file->getInfo['type']); } if($this->dealData){ return $res; } $result = $this->insertAllData($res); if($result){ return $result; } throw new Exception('添加失败'); }else{ // 上传失败获取错误信息 throw new Exception($file->getError()); } } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/3 18:05 * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception * @Description：导入逻辑 */ private function import($fileInfo = []){ $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(ucfirst($this->suffix)); $reader->setReadDataOnly(TRUE); $spreadsheet = $reader->load($this->path); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 $highestColumnNum = self::getColumnArrByMaxCol($highestColumn); //列值 if(count($highestColumnNum) != count($this->tableField)) throw new Exception("表格的列数和表字段个数不相同，请确认后再试~"); $lines = $highestRow - 1;//减掉表的第一行（通常第一行是表头，不需要导入，导入时无需删除第一行） if ($lines <= 0) throw new Exception($fileInfo['name']."Excel表格中没有数据~"); $b = 0; for ($i = 2; $i <= $highestRow; $i++){ for($a = 0; $a <= count($highestColumnNum); $a++){ $data[$b][$this->tableField[$a]] = $worksheet->getCellByColumnAndRow($a+1,$i)->getValue(); } array_pop($data[$b]); $b++; } return $data; } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/4 14:35 * @param string $col * @throws Exception * @Description：根据列数的最大值返回最大范围内的列值 */ public function getColumnArrByMaxCol($col = ''){ if(empty($col)) throw new Exception("不能为空~"); if(strlen($col) > 2) throw new Exception("导入列数超出最大值~"); strtoupper($col); $column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; $colArr = []; if(in_array($col,$column_word)){ for ($i=0;$i<=25;$i++){ if($column_word[$i] == $col) { array_push($colArr,$column_word[$i]); return $colArr; } array_push($colArr,$column_word[$i]); } } $firstColumn = substr($col,0,1); $twoColumn = substr($col,1,1); if(!in_array($firstColumn,$column_word)) throw new Exception("错误的参数"); if(!in_array($twoColumn,$column_word)) throw new Exception("错误的参数"); $firstColumnNum = array_keys($column_word,$firstColumn); // $twoColumnNum = array_keys($column_word,$twoColumn); $colArr = $column_word; for($a = 0; $a <= $firstColumnNum[0]; $a++){ for($b = 0; $b <= 25; $b++){ if($twoColumn == $column_word[$b] && $firstColumnNum[0] == $a){ array_push($colArr,$column_word[$a].$column_word[$b]); return $colArr; } array_push($colArr,$column_word[$a].$column_word[$b]); } } } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/3 17:26 * @param $data * @return $this * @Description：添加全部数据 */ public function insertAllData($data = ''){ if(empty($data) || !is_array($data)) throw new Exception("导入的数据是空的"); $res = Db($this->tableName)->insertAll($data); if($res){ return $this; } throw new Exception("添加失败"); } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/3 16:53 * @param string $url * @param string $totalRow * @param string $totalInsert * @return string * @Description：日志记录 */ public function importLog($url = '', $totalRow = '', $name = '', $size = '', $type = ''){ if(!$this->switchLog){ return 'Method ERROR：The switchLog function must be enabled'; } if(!self::createTable()) throw new Exception("记录日志失败"); $data = [ 'create_time' => time(), 'url' => $url, 'name' => $name, 'size' => $size, 'type' => $type, 'num' => $totalRow, ]; $res = Db($this->logTableName)->insert($data); if($res){ return $this; } return "Error Message：Log recording failure"; } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/3 11:59 * @param $xlsxTableName * @return bool|string * @throws \think\db\exception\BindParamException * @throws \think\exception\PDOException * @Description：创建数据表 */ private function createTable(){ $xlsxTableName = config('database.prefix').$this->logTableName; $isTable=db()->query('SHOW TABLES LIKE '."'". $xlsxTableName ."'"); if($isTable){ return $this; } $database = config('database.database'); $sql = "CREATE TABLE `". $database ."`.`". $xlsxTableName ."` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增', `url` varchar(255) NULL COMMENT 'url地址', `name` varchar(255) NULL COMMENT '源文件名名称', `size` varchar(40) NULL COMMENT '源文件大小', `type` varchar(255) NULL COMMENT '源文件类型', `num` int(11) NULL COMMENT '总数量', `create_time` bigint(20) NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) COMMENT = '导入xlsx表格记录表'"; $res = db()->query($sql); if($res){ return $this; } return "Query Message：Error creating data table"; } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2021/8/3 17:18 * @param string $page * @param string $limit * @param string $sort * @return bool|false|\PDOStatement|string|\think\Collection * @throws \think\db\exception\DataNotFoundException * @throws \think\db\exception\ModelNotFoundException * @throws \think\exception\DbException * @Description：获取日志列表 */ public function getLogList($page = '1', $limit = '20', $sort = 'desc'){ $res = Db($this->logTableName)->page($page,$limit)->order('id',$sort)->select(); return $res; } }

【PHP】使用PHPoffice实现普通的导出功能 > 使用composer安装： composer require phpoffice/phpspreadsheet <?php namespace app\index\controller; use app\index\controller\Comm; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use think\Exception; use think\Request; class Importsheet extends comm{ /** * @var Excel文件名字 */ private $sheet_filename; /** * @var Excel中的sheet的名字 */ private $sheet_name; /** * @var array 输出Excel的表头 */ private $sheet_firstline = []; /** * @var array 表的内容 */ private $sheet_info = []; /** * Importsheet constructor. * @param $filename 文件名 * @param $name sheet名 * @param $firstline 表头 * @param $info 表内容 */ public function __construct($filename,$name,$firstline = [],$info = []) { parent::__construct(); $this->sheet_filename = $filename; $this->sheet_name = $name; $this->sheet_firstline = $firstline; $this->sheet_info = $info; } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2020/12/23 16:08 * @param int $column_num * @return mixed * @Description：获取表格列数的字母 */ public function getMaxColumn(int $column_num) { try{ if(empty($column_num)){ throw new Exception('column_num:列数为空~'); } if(!is_int($column_num)){ throw new Exception('column_num:参数类型错误~'); } if($column_num > 26*26 || $column_num < 0){ throw new Exception('最大列数：676列,最小列数：1列'); } $column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; //生成循环次数 $num = ceil($column_num/26); for($c = 0; $c < $num; $c++) { $first_word = $column_word[$c-1]; foreach($column_word as $key => $val){ if($c >= 1){ $word = $first_word.$column_word[$key]; }else{ $word = $column_word[$key]; } $column[] = $word; } } for($a = 0; $a < $column_num; $a++){ $new_column[] = $column[$a]; } return $new_column; }catch (Exception $e){ returnResponse(100,$e->getMessage()); } } /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2020/12/23 17:54 * @Description：输出表 */ public function outputSheet() { try{ $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); //设置sheet的名字 $sheet->setTitle($this->sheet_name); //默认表头第一行 $k = 1; //生成列的个数，根据表头个数来定 $column_num = count($this->sheet_firstline); $info_field_num = count($this->sheet_info[0]); if($column_num != $info_field_num){ throw new Exception('结果集列数和表头列数不一致~'); } //生成表头上方的字母（最大676，最小1） $column_word = $this->getMaxColumn($column_num); //设置表头 for($i=0;$i<$column_num;$i++){ $sheet->setCellValue($column_word[$i].$k, $this->sheet_firstline[$i]); } //第二行开始插入数据 $k = 2; //插入表格数据 foreach ($this->sheet_info as $key => $value) { $b = 0; for($a = 0; $a < $column_num; $a++){ $getvalbykey = array_values($value); $sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]); $b++; } $k++; } //文件名 $file_name = date('Y-m-d H:i:s', time()).'-'.rand(1000, 9999).'_'. $this->sheet_filename . ".xlsx"; //下载 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$file_name.'"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); }catch (Exception $e){ returnResponse(100,$e->getMessage()); } } } 使用方法TP框架为例： $res = Db('company')->field('id,companys,business_remark,img1,img2,img3')->select();//查询的数据 $first_line = ['ID','公司','备注','图片一','图片二','图片三']; $sheet = new Importsheet('下载的excel名','excel里面sheet名','表头','查询的结果集'); $sheet->outputSheet(); > 改变部分地方的语法，可以适用于任意框架； > phpoffice使用介绍：http://www.honeyrh.com/index.php/archives/100/ $writer->save('文件路径');//设置文件保存路径，自己拼接路由地址 如果有更好的解决方法，欢迎留言指导。 补充：导出需要导出图片的处理方法 for($a = 0; $a < $column_num; $a++){ $getvalbykey = array_values($value); /*写入图片*/ $files_arr = explode('.', $getvalbykey[$b]); if(!empty($files_arr)){ $file_suffix = array_pop($files_arr); strtolower($file_suffix); $suffix = ['jpg', 'jpeg', 'gif', 'bmp', 'png','pdf','doc','docx','xlsx','xls']; if(in_array($file_suffix,$suffix)){ $thumb_str = str_replace(request()->domain(),'',$getvalbykey[$b]); $thumb = '/home/wwwroot/crm.sddjieshui.com/public'.$thumb_str; if(file_exists($thumb)){ $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing ->setName('图片'); $drawing ->setDescription('图片'); $drawing ->setPath($thumb); $drawing ->setWidth(80); $drawing ->setHeight(80); $drawing ->setCoordinates($column_word[$a].$k); $drawing ->setOffsetX(0); $drawing ->setOffsetY(0); $drawing ->setWorksheet($spreadsheet->getActiveSheet()); } }else{ $sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]); } $b++; }// $sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]);// $b++;导出图片替换上面方法，也可以做成开关的形式或者自动选择的形式，根据业务场景来定，后期有时间再重新更新一版可以灵活控制应用的，开箱即用，无需修改，相当nice

【PHP】PHP导出Excel所需要的表头上方的字母超过26位的解决方法 /** * @Author: 小破孩嫩 * @Email: 3584685883@qq.com * @Time: 2020/12/23 16:08 * @param int $column_num * @return mixed * @Description：获取表格列数的字母 */ public function getMaxColumn(int $column_num) { try{ if(empty($column_num)){ throw new Exception('column_num:列数为空~'); } if(!is_int($column_num)){ throw new Exception('column_num:参数类型错误~'); } if($column_num > 26*26 || $column_num < 0){ throw new Exception('最大列数：676列,最小列数：1列'); } $column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; //生成循环次数 $num = ceil($column_num/26); for($c = 0; $c < $num; $c++) { $first_word = $column_word[$c-1]; foreach($column_word as $key => $val){ if($c >= 1){ $word = $first_word.$column_word[$key]; }else{ $word = $column_word[$key]; } $column[] = $word; } } for($a = 0; $a < $column_num; $a++){ $new_column[] = $column[$a]; } return $new_column; }catch (Exception $e){ returnResponse(100,$e->getMessage()); } }

【PHP】PHPoffice/PHPSpreadsheet读取和写入Excel **安装** 现在开始，创建项目目录/PHPExcel，进入项目目录。 使用composer安装： composer require phpoffice/phpspreadsheet **使用** 在项目目录下新建/public目录，在public目录下创建示例文件test.php，编辑test.php，用以下代码。 <?php require '../vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'Welcome to Helloweba.'); $writer = new Xlsx($spreadsheet); $writer->save('hello.xlsx'); 运行代码，你会发现在目录下生成一个hello.xlsx文件，打开Excel文件，你会看到Excel中的单元格A1中有“Welcome to Helloweba.”内容。当然你可以对单元格样式诸如颜色、背景、宽度、字体等等进行设置，这些会在接下来的几节中讲到。 **PhpSpreadsheet特性** - 支持读取.xls，.xlsx，.html，.csv等格式文件，支持写入导出.xls，.xlsx，.html，.csv，.pdf格式文件。 - 提供丰富的API，提供单元格样式设置、Excel表格属性设置、图表设置等等诸多功能。使用PhpSpreadsheet完全可以生成一个外观结构都满足你的Excel表格文件。 - 卓越的性能，尤其在PHP7上表现优异，比PHPExcel强大很多。 **2. 使用PhpSpreadsheet将Excel导入到MySQL数据库** 导入Excel 思路：使用PhpSpreadsheet读取Excel表格中的有用信息，然后组装成sql语句，最后批量插入到MySQL表中。 require 'vendor/autoload.php'; include('conn.php'); //连接数据库 $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setReadDataOnly(TRUE); $spreadsheet = $reader->load('students.xlsx'); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5 $lines = $highestRow - 2; if ($lines <= 0) { exit('Excel表格中没有数据'); } $sql = "INSERT INTO `t_student` (`name`, `chinese`, `maths`, `english`) VALUES "; for ($row = 3; $row <= $highestRow; ++$row) { $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名 $chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //语文 $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //数学 $english = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //外语 $sql .= "('$name','$chinese','$maths','$english'),"; } $sql = rtrim($sql, ","); //去掉最后一个,号 try { $db->query($sql); echo 'OK'; } catch (Exception $e) { echo $e->getMessage(); } $worksheet->getCellByColumnAndRow(col,row)->getValue();可以获取表格中任意单元格数据内容 col表示单元格所在的列，以数字表示，A列表示第一列，$row表示所在的行。 **3. 使用PhpSpreadsheet将数据导出为Excel文件** **一、设置表头** 首先我们引入自动加载PhpSpreadsheet库，然后实例化，设置工作表标题名称为：学生成绩表，接着设置表头内容。表头分为两行，第一行是表格的名称，第二行数表格列名称。最后我们将第一行单元格进行合并，并设置表头内容样式：字体、对齐方式等。 require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; include('conn.php'); //连接数据库 $spreadsheet = new Spreadsheet(); $worksheet = $spreadsheet->getActiveSheet(); //设置工作表标题名称 $worksheet->setTitle('学生成绩表'); //表头 //设置单元格内容 $worksheet->setCellValueByColumnAndRow(1, 1, '学生成绩表'); $worksheet->setCellValueByColumnAndRow(1, 2, '姓名'); $worksheet->setCellValueByColumnAndRow(2, 2, '语文'); $worksheet->setCellValueByColumnAndRow(3, 2, '数学'); $worksheet->setCellValueByColumnAndRow(4, 2, '外语'); $worksheet->setCellValueByColumnAndRow(5, 2, '总分'); //合并单元格 $worksheet->mergeCells('A1:E1'); $styleArray = [ 'font' => [ 'bold' => true ], 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; //设置单元格样式 $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28); $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14); **二、读取数据** 我们连接数据库后，直接读取学生成绩表t_student，然后for循环，设置每个单元格对应的内容，计算总成绩。注意的是表格中的数据是从第3行开始，因为第1，2行是表头占用了。 然后，我们设置整个表格样式，给表格加上边框，并且居中对齐。 $sql = "SELECT id,name,chinese,maths,english FROM `t_student`"; $stmt = $db->query($sql); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $len = count($rows); $j = 0; for ($i=0; $i < $len; $i++) { $j = $i + 3; //从表格第3行开始 $worksheet->setCellValueByColumnAndRow(1, $j, $rows[$i]['name']); $worksheet->setCellValueByColumnAndRow(2, $j, $rows[$i]['chinese']); $worksheet->setCellValueByColumnAndRow(3, $j, $rows[$i]['maths']); $worksheet->setCellValueByColumnAndRow(4, $j, $rows[$i]['english']); $worksheet->setCellValueByColumnAndRow(5, $j, $rows[$i]['chinese'] + $rows[$i]['maths'] + $rows[$i]['english']); } $styleArrayBody = [ 'borders' => [ 'allBorders' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['argb' => '666666'], ], ], 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; $total_rows = $len + 2; //添加所有边框/居中 $worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody); **三、下载保存** 强制浏览器下载数据并保存为Excel文件 $filename = '成绩表.xlsx'; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="'.$filename.'"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); 如想要保存为.xls文件格式的话，可以改下header代码： $filename = '成绩表.xlsx'; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$filename.'"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'xls'); $writer->save('php://output'); **4. 详解PhpSpreadsheet设置单元格** PhpSpreadsheet提供了丰富的API接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的Excel表格，PhpSpreadsheet都能做到。 **引入了正确的文件并实例化:** use PhpOffice\PhpSpreadsheet\Spreadsheet; $spreadsheet = new Spreadsheet(); $worksheet = $spreadsheet->getActiveSheet(); **字体** 第1行代码将A7至B7两单元格设置为粗体字，Arial字体，10号字；第2行代码将B1单元格设置为粗体字。 $spreadsheet->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial') ->setSize(10);; $spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); **颜色** 将文字颜色设置为红色 $spreadsheet->getActiveSheet()->getStyle('A4') ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); **图片** 可以将图片加载到Excel中 $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing->setName('Logo'); $drawing->setDescription('Logo'); $drawing->setPath('./images/officelogo.jpg'); $drawing->setHeight(36); **列宽** 将A列宽度设置为30（字符）: $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30); 如果需要自动计算列宽，可以这样： $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); 设置默认列宽为12: $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12); **行高** 设置第10行行高为100pt： $spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100); 设置默认行高: $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); **对齐** 将A1单元格设置为水平居中对齐: $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; $worksheet->getStyle('A1')->applyFromArray($styleArray); **合并** 将A18到E22合并为一个单元格: $spreadsheet->getActiveSheet()->mergeCells('A18:E22'); **拆分** 将合并后的单元格拆分: $spreadsheet->getActiveSheet()->unmergeCells('A18:E22'); **边框** 将B2至G8的区域添加红色边框: $styleArray = [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK, 'color' => ['argb' => 'FFFF0000'], ], ], ]; $worksheet->getStyle('B2:G8')->applyFromArray($styleArray); **工作表标题** 设置当前工作表标题: $spreadsheet->getActiveSheet()->setTitle('Hello'); **日期时间** 设置日期格式: $spreadsheet->getActiveSheet() ->setCellValue('D1', '2018-06-15'); $spreadsheet->getActiveSheet()->getStyle('D1') ->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); **换行** 使用\n进行单元格内换行，相当于(ALT+"Enter"): $spreadsheet->getActiveSheet()->getCell('A4')->setValue("hello\nworld"); $spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true); **超链接** 将单元格设置为超链接形式: $spreadsheet->getActiveSheet()->setCellValue('E6', 'www.helloweba.net'); $spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://www.helloweba.net'); **使用函数** 使用SUM计算B5到C5之间单元格的总和。其他函数同理：最大数(MAX)，最小数(MIN)，平均值(AVERAGE): $spreadsheet->getActiveSheet() ->setCellValue('B7', '=SUM(B5:C5)'); **设置文档属性** 可以设置Excel文档属性: $spreadsheet->getProperties() ->setCreator("Helloweba") //作者 ->setLastModifiedBy("Yuegg") //最后修改者 ->setTitle("Office 2007 XLSX Test Document") //标题 ->setSubject("Office 2007 XLSX Test Document") //副标题 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //描述 ->setKeywords("office 2007 openxml php") //关键字 ->setCategory("Test result file"); //分类 此外，除了提供丰富的Excel文件处理接口外，PhpSpreadshee还提供了CSV，PDF，HTML以及XML等文件处理接口。