安装

composer require phpoffice/phpspreadsheet

代码

<?php

declare (strict_types = 1);

namespace app\controller;

use think\Request;

use think\facade\View;

use think\facade\Db;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Csv;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Xiangmu

{

public function exportExcel()

{

// 查询要导出的数据

$data = Db::query(“select project,price from xiangmu”);

// 实例化

$spreadsheet = new Spreadsheet();

// 获取活动单元格

$sheet = $spreadsheet->getActiveSheet();

// 获取单元格

$cellA = $sheet->getCell(‘A1’);

// 设置单元格的值

$cellA->setValue(‘项目名称’);

// 设置 A 列列宽

$sheet->getColumnDimension(‘A’)->setWidth(100);

// 设置第一行行高

$sheet->getRowDimension(1)->setRowHeight(20);

$cellB = $sheet->getCell(‘B1’);

$cellB->setValue(‘操作人’);

$sheet->getColumnDimension(‘B’)->setWidth(20);

$cellC = $sheet->getCell(‘C1’);

$cellC->setValue(‘检测量’);

$sheet->getColumnDimension(‘C’)->setWidth(10);

// 设置样式标题

$styleArray = [

‘alignment’ => [

‘horizontal’ => ‘center’, //水平居中

‘vertical’ => ‘center’, //垂直居中

],

‘font’ => [

‘name’ => ‘黑体’,

‘bold’ => false,

‘size’ => 10

]

];

// 设置样式正文

$styleArrayBody = [

‘alignment’ => [

‘horizontal’ => ‘center’, //水平居中

‘vertical’ => ‘center’, //垂直居中

],

‘font’ => [

‘name’ => ‘宋体’,

‘bold’ => false,

‘size’ => 10

]

];

// 应用样式

$sheet->getStyle(‘A1’)->applyFromArray($styleArray);

$sheet->getStyle(‘B1’)->applyFromArray($styleArray);

$sheet->getStyle(‘C1’)->applyFromArray($styleArray);

// 给sheet起个名字

$sheet->setTitle(‘项目’);

// 从 A2 开始填充数据

foreach ($data as $k => $v) {

$n = $k + 2;

// 获取单元格

$cellA = $sheet->getCell(‘A’ . $n);

// 设置单元格的值

$cellA->setValue($v[‘project’]);

$cellB = $sheet->getCell(‘B’ . $n);

$cellB->setValue(‘民族复兴不可阻挡’);

$cellC = $sheet->getCell(‘C’ . $n);

$cellC->setValue($v[‘price’]);

}

$file_name = ‘导出数据.xlsx’;

// 实例化导出类

header(‘Content-Type:application/vnd.ms-excel’);

header(‘Content-Disposition:attachment;filename=’ . $file_name);

header(‘Cache-Control:max-age=0’);

$writer = IOFactory::createWriter($spreadsheet, ‘Xlsx’);

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

}

}