搜索
简帛阁>技术文章>PhpSpreadsheet详解

PhpSpreadsheet详解

php excel 导出 PhpSpreadsheet详解

引入正确的文件并实例化

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

赋值

//给A2赋值 hello
$worksheet->setCellValueByColumnAndRow(1, 2, 'hello');

设置标题

$worksheet->setTitle('Excel导出');

字体

//将A1至D1单元格设置成粗体
$worksheet->getStyle('A1:D1')->getFont()->setBold(true);

//将A1单元格设置成粗体,黑体,10号字
$worksheet->getStyle('A1')->getFont()->setBold(true)->setName('黑体')->setSize(10);

颜色

//将文字颜色设置成红色
$worksheet->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

列宽

//将A列宽度设置成20
$worksheet->getColumnDimension('A')->setWidth(20);

//自动计算列宽
$worksheet->getColumnDimension('A')->setAutoSize(true);

//设置默认列宽20
$worksheet->getDefaultColumnDimension()->setWidth(20);

行高

//设置第一行行高为20pt
$worksheet->getRowDimension('1')->setRowHeight(20);

//设置默认行高
$worksheet->getDefaultRowDimension()->setRowHeight(20);

对齐

use PhpOffice\PhpSpreadsheet\Style\Alignment;
$styleArray = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
        'vertical' => Alignment::VERTICAL_CENTER, //垂直居中
    ],
];
$worksheet->getStyle('A1')->applyFromArray($styleArray);

边框

//红色边框
use PhpOffice\PhpSpreadsheet\Style\Border;
$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];
$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

合并单元格

$worksheet->mergeCells('A1:A4');

拆分单元格

$worksheet->unmergeCells('A1:A4');

换行

//使用 \n 进行单元格内换行,相当于 Alt+Enter
$worksheet->getCell('A1')->setValue("hello\nworld");
$worksheet->getStyle('A1')->getAlignment()->setWrapText(true);

超链接

$spreadsheet->getActiveSheet()->setCellValue('E6', '胖坨坨的博客');
$spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://blog.csdn.net/u011167662');

使用函数

$worksheet->setCellValue('A3', '=SUM(A1:A2)');
$worksheet->setCellValue('A3', '=MAX(A1:A2)');

导出

use PhpOffice\PhpSpreadsheet\IOFactory;
$write = IOFactory::createWriter($spreadsheet, 'Xlsx');
$write->save('php://output');
exit(); //需要退出程序,否则导出的excel文件会在打开时遇到错误

部分代码

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

$title = 'Excel导出';
$worksheet->setTitle($title);
$worksheet->mergeCells('B2:D2');
$worksheet->getRowDimension(1)->setRowHeight(50);
$worksheet->getStyle('B2:D2')->applyFromArray([
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'borders' => [
        'outline' => [
           'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000']
        ],
    ],
    'font' => [
        'name' => '黑体',
        'bold' => true,
        'size' => 22
    ]
]);
$worksheet->setCellValueByColumnAndRow(2, 2, $title);
$worksheet->setCellValueByColumnAndRow(2, 3, '姓名');
$worksheet->setCellValueByColumnAndRow(3, 3, '性别');
$worksheet->setCellValueByColumnAndRow(4, 3, '年龄');
$worksheet->setCellValueByColumnAndRow(2, 4, '张三');
$worksheet->setCellValueByColumnAndRow(3, 4, '男');
$worksheet->setCellValueByColumnAndRow(4, 4, '20');


header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=Excel导出.xlsx');
header('Cache-Control: max-age=0');

$write = IOFactory::createWriter($spreadsheet, 'Xlsx');
$write->save('php://output');
exit();

效果如下

官网文档:https://phpspreadsheet.readthedocs.io/en/stable/

phpexcel导出PhpSpreadsheet详解引入正确的文件并实例化usePhpOffice\PhpSpreadsheet\Spreadsheet;$spreadsheetnewSpreadsh
php用PhpSpreadsheet对Excel进行读取、写入、修改十分便捷,下面将我在工作中用到过的操作进行总结。文档参考百度搜索和原始文档:https://phpspreadsheetreadth
由于PHPExcel已经不再维护,PhpSpreadsheet是PHPExcel的下一个版本。PhpSpreadsheet是一个用纯PHP编写的库,并引入了命名空间,PSR规范等。这里简单介绍下Php
简便方法:使用PHP原生导出Excel和CSV文件|LaravelChina社区usePhpOffice\PhpSpreadsheet\Spreadsheet;usePhpOffice\PhpSpre
1、PhpSpreadsheet是什么PhpSpreadsheet由来:phpexcel由于版本陈旧性能低下官方放弃维护转而开发PhpSpreadsheet用了最新得psr标准因而对php版本不向下兼
PhpSpreadsheet提供了丰富的API接口,可以设置诸多单元格以及文档属性,包括样式、图片、日期、函数等等诸多应用,总之你想要什么样的Excel表格,PhpSpreadsheet都能做到。在
一、安装1、修改phpini配置文件,开启fileinfo扩展:2、使用composer安装:composerrequirephpoffice/phpspreadsheet二、引入加载文件<?p
单个sheet页Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是1048576行。Excel2003的最大列数是256列,2007以上版本是16384列。xlswri
theme:github读取Excel文件,并将数据读取成数组php$spreadsheet\PhpOffice\PhpSpreadsheet\IOFactory::load($file[tmp_fi
·1、实例化Spreadsheet对象<?phpnamespaceapp//给类文件的命名空间起个别名usePhpOffice\PhpSpreadsheet\Spreadsheet;//Xlsx