PhpSpreadsheet的简单使用

PhpSpreadsheet的简单使⽤
由于PHPExcel已经不再维护，PhpSpreadsheet是PHPExcel的下⼀个版本。

PhpSpreadsheet是⼀个⽤纯PHP编写的库，并引⼊了命名空间，PSR规范等。

这⾥简单介绍下PhpSpreadsheet的导⼊导出功能。

1、安装
使⽤composer安装：
composer require phpoffice/phpspreadsheet
GitHub下载：
2、excel⽂件导出
/**
* excel⽂件导出
*/
function export()
{
require_once __DIR__ . '/vendor/autoload.php';
$data = [
['title1' => '111', 'title2' => '222'],
['title1' => '111', 'title2' => '222'],
['title1' => '111', 'title2' => '222']
];
$title = ['第⼀⾏标题', '第⼆⾏标题'];
// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// ⽅法⼀，使⽤ setCellValueByColumnAndRow
//表头
//设置单元格内容
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++;
}
// ⽅法⼆，使⽤ setCellValue
//表头
//设置单元格内容
$titCol = 'A';
foreach ($title as$key => $value) {
// 单元格内容写⼊
$sheet->setCellValue($titCol . '1', $value);
$titCol++;
}
$row = 2; // 从第⼆⾏开始
foreach ($data as$item) {
$dataCol = 'A';
foreach ($item as$value) {
// 单元格内容写⼊
$sheet->setCellValue($dataCol . $row, $value);
$dataCol++;
}
$row++;
}
// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output');
exit;
}
结果：
3、excel⽂件保存到本地
/**
* excel⽂件保存到本地
*/
function save()
{
require_once __DIR__ . '/vendor/autoload.php';
$data = [
['title1' => '111', 'title2' => '222'],
['title1' => '111', 'title2' => '222'],
['title1' => '111', 'title2' => '222']
];
$title = ['第⼀⾏标题', '第⼆⾏标题'];
// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//表头
//设置单元格内容
$titCol = 'A';
foreach ($title as$key => $value) {
// 单元格内容写⼊
$sheet->setCellValue($titCol . '1', $value);
$titCol++;
}
$row = 2; // 从第⼆⾏开始
foreach ($data as$item) {
$dataCol = 'A';
foreach ($item as$value) {
// 单元格内容写⼊
$sheet->setCellValue($dataCol . $row, $value);
$dataCol++;
}
$row++;
}
// Save
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('01simple.xlsx');
}
4、读取excel⽂件内容
/**
* 读取excel⽂件内容
*/
function read()
{
require_once __DIR__ . '/vendor/autoload.php';
$inputFileName = dirname(__FILE__) . '/01simple.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
// ⽅法⼆
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true); return$sheetData;
}
结果：
可能出现的问题：
1、Fatal error: Uncaught Error: Class 'PhpOffice\PhpSpreadsheet\Spreadsheet' not found 这是因为没有⾃动加载。

可以⼿动引⼊加载⽂件。

require_once __DIR__ . '/vendor/autoload.php';
或者：
require_once __DIR__ . '/vendor/phpoffice/phpspreadsheet/src/Bootstrap.php';
2、Fatal error: Interface 'Psr\SimpleCache\CacheInterface' not found
这是因为没有psr⽂件，缺少simple-cache模块。

如果使⽤composer安装的话会⾃动⽣成。

没有的话可以⼿动下载。