Excel 導(dǎo)出學(xué)生信息

1.前言

前面小節(jié)介紹了如何使用 phpspreadsheet 導(dǎo)入學(xué)生數(shù)據(jù)，本小節(jié)介紹如何使用 phpspreadsheet 導(dǎo)出學(xué)生數(shù)據(jù)。

2.從數(shù)據(jù)庫讀取數(shù)據(jù)

在 Student 控制器中定義如下方法，表示從數(shù)據(jù)庫讀取數(shù)據(jù)：

        $students = StudentModel::select();

如下圖所示：

3.將數(shù)據(jù)寫入 Excel 處理對(duì)象

        //設(shè)置 excel 信息
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->getDefaultRowDimension()->setRowHeight(20);//設(shè)置默認(rèn)行高
        $sheet->getDefaultColumnDimension()->setWidth(10);//設(shè)置默認(rèn)寬度
        $sheet->getStyle("A1:Z1")->getFont()->setSize(10)->setBold(true);//設(shè)置第一行字體
        $sheet->getStyle("A1:Z1")->getFont()->getColor()->setRGB("FFFFFF");//設(shè)置第一行字體顏色
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '學(xué)生姓名');
        $sheet->setCellValue('C1', '年齡');
        $sheet->setCellValue('D1', '身份證號(hào)');
        $n = 2;
        foreach ($students as $student) {
            $sheet->setCellValue('A' . $n, $student->id);//客戶名稱
            $sheet->setCellValue('B' . $n, $student->name);//客戶編號(hào)
            $sheet->setCellValue('C' . $n, $student->age);//
            $sheet->setCellValue('D' . $n, $student->id_number);
            $n++;
        }

4.瀏覽器下載導(dǎo)出

設(shè)置好文件名后，就可以下載導(dǎo)出 Excel 了：

        $file = "學(xué)生信息".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('請(qǐng)求成功');
    }

    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('導(dǎo)入成功');
    }

    public function down()
    {
        //讀取數(shù)據(jù)
        $students = StudentModel::select();

        //設(shè)置 excel 信息
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->getDefaultRowDimension()->setRowHeight(20);//設(shè)置默認(rèn)行高
        $sheet->getDefaultColumnDimension()->setWidth(10);//設(shè)置默認(rèn)寬度
        $sheet->getStyle("A1:Z1")->getFont()->setSize(10)->setBold(true);//設(shè)置第一行字體
        $sheet->getStyle("A1:Z1")->getFont()->getColor()->setRGB("FFFFFF");//設(shè)置第一行字體顏色
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '學(xué)生姓名');
        $sheet->setCellValue('C1', '年齡');
        $sheet->setCellValue('D1', '身份證號(hào)');
        $n = 2;
        foreach ($students as $student) {
            $sheet->setCellValue('A' . $n, $student->id);//客戶名稱
            $sheet->setCellValue('B' . $n, $student->name);//客戶編號(hào)
            $sheet->setCellValue('C' . $n, $student->age);//
            $sheet->setCellValue('D' . $n, $student->id_number);
            $n++;
        }
        $file   = "學(xué)生信息" . 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.小結(jié)

本小節(jié)主要介紹如何使用 phpspreadsheet 導(dǎo)出學(xué)生數(shù)據(jù)，需要注意的是若實(shí)際業(yè)務(wù)中，導(dǎo)出的 Excel 中的數(shù)據(jù)庫過大，則可以使用異步的方式后臺(tái)處理，將 Excel 文件分多個(gè)批量導(dǎo)出，這樣可以防止同步調(diào)用接口導(dǎo)致的超時(shí)。