访问 Spreadsheet 中的单元格很简单。本小节列出了访问单元格的一些选项。

通过坐标设置单元格的值

通过坐标来设置单元格值可以通过工作表的 setCellValue() 方法来完成。

  1. // Set cell A1 with a string value
  2. $spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');
  4. // Set cell A2 with a numeric value
  5. $spreadsheet->getActiveSheet()->setCellValue('A2', 12345.6789);
  7. // Set cell A3 with a boolean value
  8. $spreadsheet->getActiveSheet()->setCellValue('A3', TRUE);
  10. // Set cell A4 with a formula
  11. $spreadsheet->getActiveSheet()->setCellValue(
  12.     'A4',
  13.     '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
  14. );

或者，你可以检索单元格对象，然后调用单元格的 setValue() 方法：

  1. $spreadsheet->getActiveSheet()
  2.     ->getCell('B8')
  3.     ->setValue('Some value');

创建新的单元格

如果你调用 getCell()，而该单元格还不存在，那么 PhpSpreadsheet 将为你创建该单元格。

作为一个 “in-memory” 模型，PHPSpreadsheet 对内存的要求很高，尤其是在处理大型电子表格时。用于减少这种内存开销的一种技术是单元格缓存，因此单元格实际上被保存在一个集合中，当你在处理电子表格时，这个集合可能会也可能不会被保存在内存中。正因为如此，对 getCell()（或任何类似方法）的调用会返回单元格数据，并在集合中为该单元格设置一个指针。虽然这通常不是一个问题，但如果你将调用 getCell() 的结果分配给一个变量，它就会变得很重要。任何后续的对其他单元格的调用都会改变该指针，尽管该单元格对象仍将保留其数据值。

这是什么意思？考虑一下下面的代码：

  1. $spreadSheet = new Spreadsheet();
  2. $workSheet = $spreadSheet->getActiveSheet();
  4. // Set details for the formula that we want to evaluate, together with any data on which it depends
  5. $workSheet->fromArray(
  6.     [1, 2, 3],
  7.     null,
  8.     'A1'
  9. );
  11. $cellC1 = $workSheet->getCell('C1');
  12. echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;
  14. $cellA1 = $workSheet->getCell('A1');
  15. echo 'Value: ', $cellA1->getValue(), '; Address: ', $cellA1->getCoordinate(), PHP_EOL;
  17. echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;

对 getCell(‘C1’) 的调用返回 C1 的单元格，包含它的值（3），以及它与集合的链接（用于识别它的地址/坐标C1）。随后对访问单元格A1的调用修改了 $cellC1 的值，将它与集合的链接断开。

因此，当我们试图第二次显示该值和地址时，我们可以显示它的值，但试图显示它的地址/坐标时，会抛出一个异常，因为该链接已被设置为空。

Excel 数据类型

微软的 Excel 支持以下 7 种数据类型

  • string
  • number
  • boolean
  • null
  • formula
  • error
  • Inline (or rich text) string

默认情况下，当你调用工作表的 setCellValue() 方法或单元格的 setValue() 方法时，PhpSpreadsheet 将使用适当的数据类型来处理 PHP 空值、布尔值、浮点数或整数；或者将你传递给该方法的任何字符串数据值转换为最合适的数据类型，因此数字字符串将被转换为数字，而以 =开头的字符串值将被转换为一个公式。不是数字的字符串，或者没有以 =开头的字符串，将被视为真正的字符串值。

请注意，以零开头的数字字符串（后面没有紧跟小数点分隔符）将不会被转换为数字，所以像电话号码这样的数值（如 `01615991375`` 将保持为字符串）。

这种 “转换 “是由一个单元格 “value binder” 处理的，你可以编写自定义 “value binder” 来改变这些 “转换 “的行为。标准的 PhpSpreadsheet 软件包还提供了一个 “高级 value binder”，它可以处理一些更复杂的转换，比如将像 “3/4” 这样的小数格式的字符串转换为一个数字值（本例中为0.75），并设置一个适当的 “分数 “数字格式屏蔽。同样，像 “5%” 这样的字符串将被转换为 0.05 的值，并应用百分比数字格式掩码，而包含看起来像日期的值的字符串将被转换为 Excel 序列化的日期时间戳值，并应用相应的掩码。这在从 csv 文件加载数据，或从数据库设置单元格值时特别有用。

高级 value binder 处理的格式包括

  • TRUE 或 FALSE（取决于区域设置）被转换为布尔运算。
  • 识别为科学（指数）格式的数字字符串被转换为数字。
  • 分数和庸俗分数被转换为数字，并应用适当的数字格式掩码。
  • 百分比被转换为数字，除以 100，并应用一个适当的数字格式掩码。
  • 日期和时间被转换为 Excel 时间戳值（数字），并应用适当的数字格式掩码。
  • 当字符串包含一个换行符(/n) 时，单元格的样式被设置为换行。

给单元格设置公式

如上所述，如果你在一个单元格中存储一个第一个字符为 = 的字符串值。PHPSpreadsheet 会将该值视为一个公式，然后你可以通过对单元格调用 getCalculatedValue() 来执行该公式。

但有时你可能希望将一个以 = 开头的值存储为一个字符串，而不希望 PHPSpreadsheet 将其作为一个公式来评估。

要做到这一点，你需要通过将其设置为 “引号文本” 来 “转义” 该值。

  1. // Set cell A4 with a formula
  2. $spreadsheet->getActiveSheet()->setCellValue(
  3.     'A4',
  4.     '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
  5. );
  6. $spreadsheet->getActiveSheet()->getCell('A4')
  7.     ->getStyle()->setQuotePrefix(true);

那么，即使你要求 PHPSpreadsheet 返回 A4 单元格的计算值，它也会将 =IF(A3, CONCATENATE(A1, “”, A2), CONCATENATE(A2, “”, A1)) 作为一个字符串返回，而不会尝试执行公式。

设置单元格值为日期值（date/time）

日期或时间值在 Excel 中以时间戳的形式保存（一个简单的浮点值），数字格式掩码用于显示该值应如何格式化；因此，如果我们想在单元格中存储一个日期，我们需要计算正确的 Excel 时间戳，并设置一个数字格式掩码。

  1. // Get the current date/time and convert to an Excel date/time
  2. $dateTimeNow = time();
  3. $excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $dateTimeNow );
  4. // Set cell A6 with the Excel date/time value
  5. $spreadsheet->getActiveSheet()->setCellValue(
  6.     'A6',
  7.     $excelDateValue
  8. );
  9. // Set the number format mask so that the excel timestamp will be displayed as a human-readable date/time
  10. $spreadsheet->getActiveSheet()->getStyle('A6')
  11.     ->getNumberFormat()
  12.     ->setFormatCode(
  13.         \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
  14.     );

设置一个带有前导零的数字

默认情况下，PhpSpreadsheet 会自动检测数值类型并将其设置为适当的 Excel 数值数据类型。这种类型的转换是由一个值绑定器 (value binder) 来处理的，正如本文档中题为 “使用值绑定器来方便数据输入” 的部分所描述的。

数字没有前导零，所以如果你试图设置一个有前导零的数字值（如电话号码），那么这些前导零通常会在该值被转换为数字时丢失掉，所以 “01513789642” 将被显示为1513789642。

有两种方法可以强制改写 PhpSpreadsheet 这种行为。

首先，你可以将数据类型明确地设置为字符串，这样就不会被转换为数字。

  1. // Set cell A8 with a numeric value, but tell PhpSpreadsheet it should be treated as a string
  2. $spreadsheet->getActiveSheet()->setCellValueExplicit(
  3.     'A8',
  4.     "01513789642",
  5.     \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
  6. );

或者，你可以使用数字格式掩码来显示带有前导零的值。

  1. // Set cell A9 with a numeric value
  2. $spreadsheet->getActiveSheet()->setCellValue('A9', 1513789642);
  3. // Set a number format mask to display the value as 11 digits with leading zeroes
  4. $spreadsheet->getActiveSheet()->getStyle('A9')
  5.     ->getNumberFormat()
  6.     ->setFormatCode(
  7.         '00000000000'
  8.     );

通过数字格式掩码，你甚至可以把数字分成几组，使数值更容易读懂。

  1. // Set cell A10 with a numeric value
  2. $spreadsheet->getActiveSheet()->setCellValue('A10', 1513789642);
  3. // Set a number format mask to display the value as 11 digits with leading zeroes
  4. $spreadsheet->getActiveSheet()->getStyle('A10')
  5.     ->getNumberFormat()
  6.     ->setFormatCode(
  7.         '0000-000-0000'
  8.     );

访问单元格 - 图1

注意: 上诉方式在除了 Xlsx Writer 和 Xls Writer 之外的其他的 Writer （比如 HTML writer 或者 PDF writer） 上可能不会生效。

通过数组来批量设置一组单元格的值

也可以通过向 fromArray() 方法传递数值数组，在一次调用中批量设置单元格数值。

  1. $arrayData = [
  2.     [NULL, 2010, 2011, 2012],
  3.     ['Q1',   12,   15,   21],
  4.     ['Q2',   56,   73,   86],
  5.     ['Q3',   52,   61,   69],
  6.     ['Q4',   30,   32,    0],
  7. ];
  8. $spreadsheet->getActiveSheet()
  9.     ->fromArray(
  10.         $arrayData,  // The data to set
  11.         NULL,        // Array values with this value will not be set
  12.         'C3'         // Top left coordinate of the worksheet range where
  13.                      //    we want to set these values (default is A1)
  14.     );

访问单元格 - 图2

如果你传递一个二维数组，那么这将被视为一系列的行和列。一个1维数组将被视为一个单行，如果你从数据库中获取一个数组的数据，这一点特别有用。

  1. $rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
  2. $spreadsheet->getActiveSheet()
  3.     ->fromArray(
  4.         $rowArray,   // The data to set
  5.         NULL,        // Array values with this value will not be set
  6.         'C3'         // Top left coordinate of the worksheet range where
  7.                      //    we want to set these values (default is A1)
  8.     );

访问单元格 - 图3

如果你有一个简单的一维数组，并想把它写成一列，那么下面将把它转换为一个适当结构的二维数组，可以输入到 fromArray() 方法：

  1. $rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
  2. $columnArray = array_chunk($rowArray, 1);
  3. $spreadsheet->getActiveSheet()
  4.     ->fromArray(
  5.         $columnArray,   // The data to set
  6.         NULL,           // Array values with this value will not be set
  7.         'C3'            // Top left coordinate of the worksheet range where
  8.                         //    we want to set these values (default is A1)
  9.     );

访问单元格 - 图4

根据坐标获取单元格的值

要检索一个单元格的值，首先应使用 getCell() 方法从工作表中检索出该单元格。单元格的值可以用 getValue() 方法来读取。

  1. // 获取单元格 A1 的值
  2. $cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue();

这能获取单元格中原始的、没有格式化的值。

如果一个单元格包含一个公式，而你需要检索计算值而不是公式本身，那么就使用单元格的 getCalculatedValue() 方法。这将在计算引擎中会有进一步解释。

  1. // 获取 A4 单元格中的值
  2. $cellValue = $spreadsheet->getActiveSheet()->getCell('A4')->getCalculatedValue();

另外，如果你想获取到单元格格式化之后的值（例如，对于人类可读的日期或时间值），那么你可以使用单元格的 getFormattedValue() 方法。

  1. // 获取 A6 单元格的值
  2. $cellValue = $spreadsheet->getActiveSheet()->getCell('A6')->getFormattedValue();

根据行和列来设置单元格的值

使用工作表的 setCellValueByColumnAndRow() 方法，可以按坐标来设置单元格的值。

  1. // 设置单元格 A5 的值为字符串
  2. $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet');

注意：A 列的引用以 1 开始。(不是从 0 开始)

根据行和列来获取单元格的值

要检索一个单元格的值，首先应使用 getCellByColumnAndRow() 方法从工作表中检索出该单元格。可以用下面的这行代码再次读取一个单元格的值：

  1. // 获取单元格 B5 的值
  2. $cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();

如果你需要一个单元格的计算值，请使用以下代码。这在计算引擎中会有进一步解释。

  1. // 获取单元格 A4 的值
  2. $cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(1, 4)->getCalculatedValue();

获取指定范围内的单元格的值到数组中

也可以使用 toArray()、rangeToArray() 或 namedRangeToArray() 方法在一次调用中检索单元格值的范围到一个数组。

  1. $dataArray = $spreadsheet->getActiveSheet()
  2.     ->rangeToArray(
  3.         'C3:E5',     // 想要获取的范围
  4.         NULL,        // 如果单元格为空的话返回的值，比如范围内有1一个单元格为空，那么就会返回这里设置的 null 值
  5.         TRUE,        // 如果单元格包含公式的话，是否要获取公式执行之后的值
  6.         TRUE,        // 是否要获取格式化之后的值（针对单元格有格式的情况下）
  7.         TRUE         // 对应的数组是否能够通过行或者列来索引到
  8.     );

这些方法都将返回一个行和列的二维数组。toArray() 方法将返回整个工作表；rangeToArray() 将返回一个指定的范围或单元格；而 namedRangeToArray() 将返回一个定义的命名范围内的单元格。

循环单元格

通过迭代器来循环单元格

循环单元格的最简单方法是使用迭代器。使用迭代器，人们可以使用 foreach 来循环工作表、工作表中的行以及行中的单元格。

下面是一个例子，我们读取一个工作表中的所有数值，并将其显示在一个表中。

  1. $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
  2. $reader->setReadDataOnly(TRUE);
  3. $spreadsheet = $reader->load("test.xlsx");
  5. $worksheet = $spreadsheet->getActiveSheet();
  7. echo '<table>' . PHP_EOL;
  8. foreach ($worksheet->getRowIterator() as $row) {
  9.     echo '<tr>' . PHP_EOL;
  10.     $cellIterator = $row->getCellIterator();
  11.     $cellIterator->setIterateOnlyExistingCells(FALSE); // 设置为 false 会循环所有的单元格，即使单元格的值没有设置。设置为 true 的话就只会遍历有设置过值的单元格，默认值为 false。
  12.     foreach ($cellIterator as $cell) {
  13.         echo '<td>' .
  14.              $cell->getValue() .
  15.              '</td>' . PHP_EOL;
  16.     }
  17.     echo '</tr>' . PHP_EOL;
  18. }
  19. echo '</table>' . PHP_EOL;