php导出excel php excel导入数据

文章插图

文章插图
phpExcel早已停止维护，现在都用phpSpreadSheet 。
网上那些phpSpreadSheet教程都啰里啰唆，叨叨这个那个，没个能打的~
还得看我这个，代码拷走，改改就能用，就是这么粗鲁！
安装

composer require phpoffice/phpspreadsheet

代码

<?php
declare (strict_types = 1);
namespace app\controller;
use think\Request;
use think\facade\View;
【php导出excel php excel导入数据】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 = http://www.mnbkw.com/jxjc/176333/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’);
}
}

上面代码直接保存成一个.php文件，就能用。