使用composer安装: composer require phpoffice/phpspreadsheet

<?php
namespace appindexcontroller;
use appindexcontrollerComm;
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
use thinkException;
use thinkRequest;
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

最后修改:2021 年 08 月 12 日 04 : 24 PM
对您有帮助的话,请赏包辣条吧 ^~^