Excel 导出学生信息

1.前言

前面小节介绍了如何使用 phpspreadsheet 导入学生数据，本小节介绍如何使用 phpspreadsheet 导出学生数据。

2.从数据库读取数据

在 Student 控制器中定义如下方法，表示从数据库读取数据：

        $students = StudentModel::select();

如下图所示：

3.将数据写入 Excel 处理对象

        //设置 excel 信息
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->getDefaultRowDimension()->setRowHeight(20);//设置默认行高
        $sheet->getDefaultColumnDimension()->setWidth(10);//设置默认宽度
        $sheet->getStyle("A1:Z1")->getFont()->setSize(10)->setBold(true);//设置第一行字体
        $sheet->getStyle("A1:Z1")->getFont()->getColor()->setRGB("FFFFFF");//设置第一行字体颜色
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '学生姓名');
        $sheet->setCellValue('C1', '年龄');
        $sheet->setCellValue('D1', '身份证号');
        $n = 2;
        foreach ($students as $student) {
            $sheet->setCellValue('A' . $n, $student->id);//客户名称
            $sheet->setCellValue('B' . $n, $student->name);//客户编号
            $sheet->setCellValue('C' . $n, $student->age);//
            $sheet->setCellValue('D' . $n, $student->id_number);
            $n++;
        }

4.浏览器下载导出

设置好文件名后，就可以下载导出 Excel 了：

        $file = "学生信息".date('YmdHis').".xlsx";
        $writer = new Xlsx($spreadsheet);
        header('Content-Disposition: attachment;filename='.$file);//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存

        $writer->save("php://output");;

5.完整代码

<?php

namespace app\study\controller;

use app\study\model\StudentModel;
use cmf\controller\AdminBaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Request;

class StudentController extends AdminBaseController
{
    public function add()
    {
        return $this->fetch();
    }

    public function addPost()
    {
        try {
            $studentModel             = new StudentModel();
            $studentModel->name       = $this->request->param('name', "");
            $studentModel->age        = $this->request->param('age', 0, 'intval');
            $studentModel->id_number  = $this->request->param('id_number', "");
            $studentModel->created_at = time();
            $studentModel->save();
        } catch (\Exception $exception) {
            return $this->error($exception->getMessage());
        }

        return $this->success('请求成功');
    }

    public function upload()
    {
        return $this->fetch();
    }

    public function uploadExcel()
    {
        $data        = $this->request->param();
        $file_url    = "./upload/" . $data['file_url'];
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_url);
        $n           = 2;
        while (true) {
            $name      = $spreadsheet->getActiveSheet()->getCell('A' . $n)->getValue();
            $age       = $spreadsheet->getActiveSheet()->getCell('B' . $n)->getValue();
            $id_number = $spreadsheet->getActiveSheet()->getCell('C' . $n)->getValue();
            try {
                $studentModel             = new StudentModel();
                $studentModel->name       = $name;
                $studentModel->age        = $age;
                $studentModel->id_number  = $id_number;
                $studentModel->created_at = time();
                $studentModel->save();
            } catch (\Exception $exception) {

            }
            if (empty($name) && empty($age) && empty($id_number)) {
                break;
            }
            $n++;
        }

        return $this->success('导入成功');
    }

    public function down()
    {
        //读取数据
        $students = StudentModel::select();

        //设置 excel 信息
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->getDefaultRowDimension()->setRowHeight(20);//设置默认行高
        $sheet->getDefaultColumnDimension()->setWidth(10);//设置默认宽度
        $sheet->getStyle("A1:Z1")->getFont()->setSize(10)->setBold(true);//设置第一行字体
        $sheet->getStyle("A1:Z1")->getFont()->getColor()->setRGB("FFFFFF");//设置第一行字体颜色
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '学生姓名');
        $sheet->setCellValue('C1', '年龄');
        $sheet->setCellValue('D1', '身份证号');
        $n = 2;
        foreach ($students as $student) {
            $sheet->setCellValue('A' . $n, $student->id);//客户名称
            $sheet->setCellValue('B' . $n, $student->name);//客户编号
            $sheet->setCellValue('C' . $n, $student->age);//
            $sheet->setCellValue('D' . $n, $student->id_number);
            $n++;
        }
        $file   = "学生信息" . date('YmdHis') . ".xlsx";
        $writer = new Xlsx($spreadsheet);
        header('Content-Disposition: attachment;filename=' . $file);//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存

        $writer->save("php://output");;
    }
}

6.视频演示

7.小结

本小节主要介绍如何使用 phpspreadsheet 导出学生数据，需要注意的是若实际业务中，导出的 Excel 中的数据库过大，则可以使用异步的方式后台处理，将 Excel 文件分多个批量导出，这样可以防止同步调用接口导致的超时。