PhpSpreadsheet 电子表格(excel) PHP处理笔记

1 安装

composer require phpoffice/phpspreadsheet

Github地址：PHPOffice/PhpSpreadsheet

文档地址：PhpSpreadsheet’s documentation

2 基本用法

// 这里是以thinkphp5环境为例
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet(); // 创建新表格
$spreadsheet->createSheet(); // 创建sheet
$sheet = $spreadsheet->getActiveSheet(); // 获取当前sheet
$sheet->setCellValue('A1', 'Hello World222 !'); // 设置单元格A1的值

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//浏览器输出07Excel文件
//header('Content-Type:application/vnd.ms-excel');//浏览器将要输出Excel03版本文件
header('Content-Disposition: attachment;filename="'.time().'.xlsx"');//浏览器输出浏览器名称
header('Cache-Control: max-age=0'); //禁止缓存
$writer = new Xlsx($spreadsheet);
$writer->save('php://output'); // 直接下载excel
// $writer->save('1.xlsx');  默认保存到根目录，thinkphp5里默认是public目录

// 断开连接，销毁对象
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);

3 添加sheet

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

// 添加sheet
$myWorkSheet = new Worksheet($spreadsheet, 'My Data'); // sheet名称
$spreadsheet->addSheet($myWorkSheet, 0); // 添加sheet位置index

4 获取sheet下标/名称，切换sheet

$sheet = $spreadsheet->getSheet(1); // 根据下标获取sheet
$sheet = $spreadsheet->getSheetByName('Worksheet 1'); //根据name获取sheet
// 切换当前sheet
$spreadsheet->setActiveSheetIndex(1); // 按sheet下标切换
$spreadsheet->setActiveSheetIndexByName('Sheet 1'); // 按sheet name切换

5 批量设置单元格数据

// 设置单元格数据
$arrayData = [
   [NULL, 2010, 2011, 2012],
   ['Q1',   12,   15,   21],
   ['Q2',   56,   73,   86],
   ['Q3',   52,   61,   69],
   ['Q4',   30,   32,    0],
];
$sheet->fromArray(
   $arrayData,  // The data to set 
   NULL,        // Array values with this value will not be set
   'C3'         // Top left coordinate of the worksheet range where
                // we want to set these values (default is A1)
);

如果是设置一行，可以设置一维数组；

$arrayData = [1,2,3,4];

如果想设置一列数据，可以这样：

$arrayData = [1,2,3,4];
$columnArray = array_chunk($rowArray, 1);

6 加载表格

方式1：

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'C:\Users\Administrator\Document\1.xlsx';
/**  获得文档类型(xls/xlsx)  **/
$inputFileType = IOFactory::identify($inputFileName);
/**  Create a new Reader of the type that has been identified  **/
$reader = IOFactory::createReader($inputFileType);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

方式2：

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'C:\Users\Administrator\Document\1.xlsx';
$spreadsheet = IOFactory::load($inputFileName);

因第一种方式得到了具体的文件类型，所以处理起来会比较快。

7 遍历单元格

use PhpOffice\PhpSpreadsheet\IOFactory;

//1. 使用迭代器遍历单元格
$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('1.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
echo '<table>' . PHP_EOL;
foreach ($worksheet->getRowIterator() as $row) {
    echo '<tr>' . PHP_EOL;
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE);
    foreach ($cellIterator as $cell) {
        echo '<td>' .
            $cell->getValue() .
            '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

// 2.使用索引循环遍历单元格
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
    echo '<tr>' . PHP_EOL;
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
        echo '<td>' . $value . '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

8 删除sheet

$sheetIndex = $spreadsheet->getIndex(
	$spreadsheet->getSheetByName('Worksheet 1')
);
$spreadsheet->removeSheetByIndex($sheetIndex);

9 设置文档元数据（摘要信息）

$spreadsheet->getProperties()
            ->setCreator("Zhang Ying")
            ->setLastModifiedBy("Zhang Ying")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

10 小技巧

(1) 单元格内换行

$sheet->getCell('B1')->setValue("hello\nworld");
$sheet->getStyle('B1')->getAlignment()->setWrapText(true);

(2) 强制设置单元格格式为string格式，设置正常数字

$sheet->setCellValueExplicit('A8',"0123456",DataType::TYPE_STRING);
$sheet->getStyle('A9')->getNumberFormat()->setFormatCode('00000000000');

(3) 设置列宽度自适应

$sheet->getColumnDimension('A')->setAutoSize(true);

(4) 按行、列下标设置和查询单元格数据

// 按列和行设置单元格值
$sheet->setCellValueByColumnAndRow(1, 3, 'PhpSpreadsheet Demo');
// 按列和行查询单元格值
$sheet->getCellByColumnAndRow(2, 5)->getValue();

(5) 开启筛选功能

$spreadsheet->getActiveSheet()->setAutoFilter('A1:E20');

(6) 设置打印格式

use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);