上代码

<?php

namespace app\admin\controller\student;

use app\common\controller\Backend;

use app\common\library\Curl;
use think\Lang;
use app\common\model\Goods;
//
use Exception;
use think\Db;
use think\exception\PDOException;
use think\exception\ValidateException;
use PhpOffice\PhpWord\PhpWord;
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
/**
 * 物品登记
 */
class Derive extends Backend
{

    //导出数据
    public function download(){
       
        // if ($this->request->isPost()){
        set_time_limit(0);//设置时间限制
        $ids = $this->request->post('ids'); //获取数据的所有id
        $newExcel = new Spreadsheet(); 
        ///
        $sheet = $newExcel->getActiveSheet();
        $sheetPHPExcel = $newExcel->setActiveSheetIndex(0);
        $char_index    = range('A', 'Z');
        ///

        $newExcel->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);     //水平居中
        $newExcel->getDefaultStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);   //垂直居中
        //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet(); 
        //获取当前操作sheet的对象
        $objSheet->setTitle('学员导出'); 
        //设置当前sheet的标题
        //$newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true)->setWidth(50);
        $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
        $newExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);


        
        $objSheet->setCellValue('A1', '姓名')
        ->setCellValue('B1', '证件照')
        ->setCellValue('C1', '性别')
        ->setCellValue('D1', '年龄')
        ->setCellValue('E1', '比赛类型')
        ->setCellValue('F1', '等级')
        ->setCellValue('G1', '届次')
        ->setCellValue('H1', '证书编号')
        ->setCellValue('I1', '身份证号')
        ->setCellValue('J1', '证书');
         $sql=model('student')->where('c_student.deletetime', 'exp', 'is null')->order('id asc')->select();
         $sql = collection($sql)->toArray();
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        $i=2; 
        //定义一个i变量，目的是在循环输出数据是控制行数
         $count = count($sql); 
        //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $row = $i-2;
            $objSheet->setCellValue('A' . $i, $sql[$row]['name'])
            ->setCellValue('B' . $i, '')
            ->setCellValue('C' . $i, $sql[$row]['sex'] == 1?'女':'男')
            ->setCellValue('D' . $i, $sql[$row]['age'])
            ->setCellValue('E' . $i, $sql[$row]['type'])
            ->setCellValue('F' . $i, $sql[$row]['grade'])
            ->setCellValue('G' . $i, $sql[$row]['student_group_name'])
            ->setCellValue('H' . $i, $sql[$row]['id_certificate'])
            ->setCellValue('I' . $i, $sql[$row]['id_number']."\t")//注意这里的"\t"是为了防止身份证号被转义为科学计数
            ->setCellValue('J' . $i, '');
           //第二种防止数字被转义科学计数
            //$newExcel->getActiveSheet()->setCellValueExplicit(
                //'I'. $i,
                //$sql[$row]['id_number'],
                //\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
            //);
      //$newExcel->getActiveSheet()->setCellValueExplicit(
                //'H'. $i,
                //$sql[$row]['id_certificate'],
                //\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
            //);
//////////
            $isImg1 = ROOT_PATH.'public'.$sql[$row]['photo'];
            $orImg1 = pathinfo($isImg1);
            if (isset($orImg1['extension']) && (($orImg1['extension'] == 'jpg') || ($orImg1['extension'] == 'png') || ($orImg1['extension'] == 'gif'))){
                $drawing[$row] = new Drawing();
                $drawing[$row]->setName( 'Logo');
                $drawing[$row]->setDescription( 'Logo');
                $drawing[$row]->setPath($isImg1);
                $drawing[$row]->setPath($isImg1);
                $drawing[$row]->setWidth(80);
                $drawing[$row]->setHeight(80);
                $drawing[$row]->setCoordinates('B' . $i);
                $drawing[$row]->setOffsetX(12);
                $drawing[$row]->setOffsetY(12);
                $drawing[$row]->setWorksheet($newExcel->getActiveSheet());
            }
            ////////////
            $isImg = ROOT_PATH.'public'.'/assets/img/certificate.jpg';
            $orImg = pathinfo($isImg);
            if (isset($orImg['extension']) && (($orImg['extension'] == 'jpg') || ($orImg['extension'] == 'png') || ($orImg['extension'] == 'gif'))){
                $drawing[$row] = new Drawing();
                $drawing[$row]->setName( 'Logo');
                $drawing[$row]->setDescription( 'Logo');
                $drawing[$row]->setPath($isImg);
                $drawing[$row]->setPath($isImg);
                $drawing[$row]->setWidth(80);
                $drawing[$row]->setHeight(80);
                $drawing[$row]->setCoordinates('J' . $i);
                $drawing[$row]->setOffsetX(12);
                $drawing[$row]->setOffsetY(12);
                $drawing[$row]->setWorksheet($newExcel->getActiveSheet());
            }
            $sheet->getRowDimension($i)->setRowHeight(80);
        }
            /*--------------下面是设置其他信息------------------*/
        $title = date("Ymd-学员导出");
        $objWriter = IOFactory::createWriter($newExcel, 'Xlsx');
        //输出但不保存
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // $objWriter->save('1.Xlsx');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($newExcel, 'Xlsx');
        // php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。 
        $writer->save('php://output');
        exit();
        //不输出,保存本地,API使用
        $path = '/xlsx/'.$title.'.xlsx';
        // $path = iconv('gbk', 'utf-8', $path);
        $objWriter->save(ROOT_PATH.'public'.$path);
        $this->success('获取成功!',['path'=>request()->domain().$path]);
        //以下为需要用到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;
  }
}

效果图