ThinkPHP6 excel 导出功能完整实现

在 ThinkPHP 6.0 中实现 excel 导入导出功能，需要使用第三方库或扩展，例如 phpspreadsheet。

目录

安装

浏览器下载

加载类库

添加路由

创建控制器方法

实例化类

设置sheet内容

实例化xlsx类

真实数据导出

保存到本地

加载依赖

保存设置

完整实现

总结

---

安装

项目目录下打开命令行，执行命令：

composer require phpoffice/phpspreadsheet

安装过程

$ composer require phpoffice/phpspreadsheet
Using version ^1.25 for phpoffice/phpspreadsheet
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 8 installs, 0 updates, 0 removals
  - Installing psr/http-factory (1.0.1): Loading from cache
  - Installing psr/http-client (1.0.1): Loading from cache
  - Installing markbaker/matrix (3.0.1): Downloading (100%)
  - Installing markbaker/complex (3.0.2): Downloading (100%)
  - Installing myclabs/php-enum (1.8.4): Downloading (100%)
  - Installing maennchen/zipstream-php (2.1.0): Downloading (100%)
  - Installing ezyang/htmlpurifier (v4.16.0): Downloading (100%)
  - Installing phpoffice/phpspreadsheet (1.25.2): Downloading (100%)
ezyang/htmlpurifier suggests installing cerdic/css-tidy (If you want to use the filter 'Filter.ExtractStyleBlocks'.)
ezyang/htmlpurifier suggests installing ext-tidy (Used for pretty-printing HTML)
phpoffice/phpspreadsheet suggests installing ext-intl (PHP Internationalization Functions)
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing mitoteam/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
Writing lock file
Generating autoload files
> @php think service:discover
Succeed!
> @php think vendor:publish
File D:\tp_solve\config\trace.php exist!
Succeed!

浏览器下载

加载类库

导出xlsx格式文件，需要使用Spreadsheet和Xlsx类

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

添加路由

Route::get('productExport', 'ProductOrder/exportExcel');

创建控制器方法

/**
 * excel导出
 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
 */
public function exportExcel()
{
    
}

实例化类

$spreadsheet = new Spreadsheet();

设置sheet内容

$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Sheet1');
$sheet->setCellValue('A1', '姓名');
$sheet->setCellValue('B1', '年龄');
$sheet->setCellValue('C1', '性别');
$sheet->setCellValue('A2', '张三');
$sheet->setCellValue('B2', '30');
$sheet->setCellValue('C2', '男');

实例化xlsx类

实例化xlsx类，激活下载窗口，保存设置php://output。

$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="导出数据.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');

效果如下：

真实数据导出

代码如下：

**
 * excel导出
 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
 * @throws \think\db\exception\DataNotFoundException
 * @throws \think\db\exception\DbException
 * @throws \think\db\exception\ModelNotFoundException
 */
public function exportExcel()
{
    // 查询数据
    $key = ['id', 'product_name', 'product_desc', 'optionsNo', 'created_at'];
    $data = Db::name('product')
        ->order('id', 'asc')
        ->field($key)->select()->toArray();

    // 处理数据
    foreach ($data as &$v) {
        $v['created_at'] = date('Y/m/d H:i:s', $v['created_at']);
    }
    unset($v);

    // 加载sheet
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Sheet1');

    // 处理标题
    $title = ['ID', '产品名称', '产品描述', '产品编号', '入库时间'];
    foreach ($title as $key => $value) {
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
    }

    // 处理标题下内容
    // 从第二行开始
    $row = 2;
    foreach ($data as $item) {
        $column = 1;
        foreach ($item as $value) {
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }

    // 文件下载
    $filename = '产品数据导出';
    $writer = new Xlsx($spreadsheet);
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
    header('Cache-Control: max-age=0');
    $writer->save('php://output');
}

效果如下：

保存到本地

加载依赖

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

保存设置

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('test.xlsx');

完整实现

/**
 * excel导出
 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
 * @throws \think\db\exception\DataNotFoundException
 * @throws \think\db\exception\DbException
 * @throws \think\db\exception\ModelNotFoundException
 */
public function exportExcel()
{
    // 查询数据
    $key = ['id', 'product_name', 'product_desc', 'optionsNo', 'created_at'];
    $data = Db::name('product')
        ->order('id', 'asc')
        ->field($key)->select()->toArray();

    // 处理数据
    foreach ($data as &$v) {
        $v['created_at'] = date('Y/m/d H:i:s', $v['created_at']);
    }
    unset($v);

    // 加载sheet
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Sheet1');

    // 处理标题
    $title = ['ID', '产品名称', '产品描述', '产品编号', '入库时间'];
    foreach ($title as $key => $value) {
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
    }

    // 处理标题下内容
    // 从第二行开始
    $row = 2;
    foreach ($data as $item) {
        $column = 1;
        foreach ($item as $value) {
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }

    // 文件下载
    $filename = './uploads/产品数据导出.xlsx';
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($filename);
}

效果如下

注意：uploads文件夹如果不存在，需要预先创建。

总结

导出到这里就结束了，经过测试Xls/Xlsx/Csv/Html等格式都可顺利导出。

导入功能如何做，可点击《ThinkPHP6 excel导入完整实现》。