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 文件分多個批量導出，這樣可以防止同步調用接口導致的超時。