文章目录
一、实例化对象
<?php
namespace app
// 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//Xlsx类 保存文件功能类
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
创建工作簿
// 1获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
二、访问单元格
2.1获取单元格
// 2获取单元格
$cell = $sheet->getCell('A1'); //方法1
$cell = $sheet->getCellByColumnAndRow(1,1); //方法2
2.2单元格赋值
// 3给单元格赋值
$cellA->setValue('A1单元格内容');
// 3-1获取设置单元格,链式操作
$sheet->getCell('A3')->setValue('郭靖');
$sheet->getCellByColumnAndRow(1,4)->setValue('杨康');
获取单元格的值
// 3-2获取单元格
$cellA = $sheet->getCell('A1');
echo '值: ', $cellA->getValue(),PHP_EOL;
echo '坐标: ', $cellA->getCoordinate(); //获取单元格坐标
2.3excel数据类型
- string 字符串
- number 数
- boolean 布尔
- null 空
- formula 公式
- error 错误
- Inline (or rich text) string 内联(或富文本)字符串
2.3.1在单元格设置公式
// Set cell A4 with a formula
$spreadsheet->getActiveSheet()->setCellValue(
'A4',
'=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);
$spreadsheet->getActiveSheet()->getCell('A4')
->getStyle()->setQuotePrefix(true);
2.3.2在单元格设置日期和时间值
// Get the current date/time and convert to an Excel date/time
$dateTimeNow = time();
$excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $dateTimeNow );
// Set cell A6 with the Excel date/time value
$spreadsheet->getActiveSheet()->setCellValue(
'A6',
$excelDateValue
);
// Set the number format mask so that the excel timestamp will be displayed as a human-readable date/time
$spreadsheet->getActiveSheet()->getStyle('A6')
->getNumberFormat()
->setFormatCode(
\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
);
2.3.3 设置带前导零的数字
默认情况下,PhpSpreadsheet 将自动检测值类型和 将其设置为适当的 Excel 数字数据类型。此类型转换 由值绑定器处理,如本部分所述 题为“使用值活页夹促进数据输入”的文件。
数字没有前导零,因此如果您尝试设置数值 确实有前导零(例如电话号码),然后这些 通常会丢失,因为值被转换为数字,因此 “01513789642”将显示为1513789642。
有两种方法可以强制 PhpSpreadsheet 覆盖它 行为。
首先,您可以将数据类型显式设置为字符串,以便它是 未转换为数字。
// Set cell A8 with a numeric value, but tell PhpSpreadsheet it should be treated as a string
$spreadsheet->getActiveSheet()->setCellValueExplicit(
'A8',
"01513789642",
\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);
或者,您可以使用数字格式掩码来显示值 带前导零。
// Set cell A9 with a numeric value
$spreadsheet->getActiveSheet()->setCellValue('A9', 1513789642);
// Set a number format mask to display the value as 11 digits with leading zeroes
$spreadsheet->getActiveSheet()->getStyle('A9')
->getNumberFormat()
->setFormatCode(
'00000000000'
);
使用数字格式掩码,您甚至可以将数字分成几组 以使值更易于阅读。
// Set cell A10 with a numeric value
$spreadsheet->getActiveSheet()->setCellValue('A10', 1513789642);
// Set a number format mask to display the value as 11 digits with leading zeroes
$spreadsheet->getActiveSheet()->getStyle('A10')
->getNumberFormat()
->setFormatCode(
'0000-000-0000'
);
2.4设置数组中的单元格区域
也可以在一次调用中通过以下方式设置单元格值范围 将值数组传递给方法。 fromArray()
$arrayData = [
[NULL, 2010, 2011, 2012],
['Q1', 12, 15, 21],
['Q2', 56, 73, 86],
['Q3', 52, 61, 69],
['Q4', 30, 32, 0],
];
$spreadsheet->getActiveSheet()
->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)
);
一维数组将被视为单行
$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$spreadsheet->getActiveSheet()
->fromArray(
$rowArray, // 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)
);
如果您有一个简单的一维数组,并且想将其编写为列,则 以下内容将其转换为结构适当的二维数组 可以提供给方法:fromArray()
$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$columnArray = array_chunk($rowArray, 1);
$spreadsheet->getActiveSheet()
->fromArray(
$columnArray, // 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)
);
2.5将单元格值范围检索到数组
可以将单元格值范围检索到数组中的数组 使用 或 方法进行单个调用。toArray() rangeToArray() namedRangeToArray()
$dataArray = $spreadsheet->getActiveSheet()
->rangeToArray(
'C3:E5', // The worksheet range that we want to retrieve
NULL, // Value that should be returned for empty cells
TRUE, // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
TRUE, // Should values be formatted (the equivalent of getFormattedValue() for each cell)
TRUE // Should the array be indexed by cell row and cell column
);
2.6循环通过单元格
2.6.1使用迭代器遍历单元格
循环单元格的最简单方法是使用迭代器。使用迭代器, 可以使用foreach循环工作表,工作表中的行,以及 行中的单元格。
下面是一个示例,我们读取工作表中的所有值,并且 在表格中显示它们。
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("test.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
echo '<table>' . PHP_EOL;
foreach ($worksheet->getRowIterator() as $row) {
echo '<tr>' . PHP_EOL;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
// even if a cell value is not set.
// For 'TRUE', we loop through cells
// only when their value is set.
// If this method is not called,
// the default value is 'false'.
foreach ($cellIterator as $cell) {
echo '<td>' .
$cell->getValue() .
'</td>' . PHP_EOL;
}
echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;
2.6.2使用索引遍历单元格
可以使用按列和行访问单元格值的可能性 索引,而不是用于读取和写入单元格值 循环。[1, 1]‘A1’
注意:在 PhpSpreadsheet 中,列索引和行索引都是从 1 开始的。这意味着'A1' ~ [1, 1]
下面是一个示例,我们读取工作表中的所有值,并且 在表格中显示它们。
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("test.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\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;
或者,可以利用PHP的“Perl风格”字符。 增量器通过坐标遍历单元格:
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("test.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter
$highestColumn++;
echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>' . PHP_EOL;
for ($col = 'A'; $col != $highestColumn; ++$col) {
echo '<td>' .
$worksheet->getCell($col . $row)
->getValue() .
'</td>' . PHP_EOL;
}
echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;
请注意,我们不能在这里使用比较,因为会匹配 as ,所以我们递增最高的列字母,然后循环 而递增的最高列。<='AA'<= 'B'$col !=