在MS Excel和其他电子表格格式中，有两种命名范围：命名范围(Named Ranges)和命名公式(Named Formulae)。它们可以为您的电子表格添加很多强大的功能，但需要正确使用。

所有在此文档中显示的代码示例都可以在/samples/DefinedNames文件夹中找到。

命名范围

命名范围为单元格或单元格范围提供一个名称引用。然后，您可以在公式中通过该名称引用该单元格或单元格范围。

作为示例，我将创建一个简单的计算器，将税加到价格上。

  1. // 设置一些基本数据
  2. $worksheet->setCellValue('A1', 'Tax Rate:')->setCellValue('B1', '=19%')->setCellValue('A3', 'Net Price:')->setCellValue('B3', 12.99)->setCellValue('A4', 'Tax:')->setCellValue('A5', 'Price including Tax:');
  4. // 定义命名范围
  5. $spreadsheet->addNamedRange(new \PhpOffice\PhpSpreadsheet\NamedRange('TAX_RATE', $worksheet, '=$B$1'));
  6. $spreadsheet->addNamedRange(new \PhpOffice\PhpSpreadsheet\NamedRange('PRICE', $worksheet, '=$B$3'));
  8. // 在公式中引用定义的名称
  9. $worksheet->setCellValue('B4', '=PRICE*TAX_RATE')->setCellValue('B5', '=PRICE*(1+TAX_RATE)');
  11. echo sprintf(
  12.     'With a Tax Rate of %.2f and a net price of %.2f, Tax is %.2f and the gross price is %.2f',
  13.     $worksheet->getCell('B1')->getCalculatedValue(),
  14.     $worksheet->getCell('B3')->getValue(),
  15.     $worksheet->getCell('B4')->getCalculatedValue(),
  16.     $worksheet->getCell('B5')->getCalculatedValue()
  17. ), PHP_EOL;

/samples/DefinedNames/SimpleNamedRange.php

这使得生成的电子表格中的公式在查看时更易于理解。使用这些命名范围(为单元格提供有意义的人类可读名称)可以明确公式的目的。我们无需查找单元格B2,就可以知道它是什么。如果税率更改为16%,则我们只需更改单元格B1的值为新的税率(=16%),或者如果我们想要计算不同净价下的税收费用，那么这将立即反映在引用那些命名范围的所有计算中。无论我在工作表中使用哪个命名范围，它始终引用单元格B1。实际上，由于我们在定义名称时指定了一个工作表，因此该名称在电子表格中的任何工作表内都是可用的，并且始终表示此工作表内的单元格B2(但请参阅命名范围范围以下的注释)。

绝对命名范围

在上面的示例中，当我定义命名范围的值(例如='=$B$1'),我在行和列两侧使用了美元符号。这使命名范围成为绝对引用。

另一个示例：

  1. // 为时间表设置一些基本数据
  2. $worksheet->setCellValue('A1', 'Charge Rate/hour:')->setCellValue('B1', '7.50')->setCellValue('A3', 'Date')->setCellValue('B3', 'Hours')->setCellValue('C3', 'Charge');
  4. // 定义命名范围使用绝对单元格引用
  5. $spreadsheet->addNamedRange(new NamedRange('CHARGE_RATE', $worksheet, '=$B$1'));
  7. $workHours = [
  8.     '2020-0-06' => 7.5,
  9.     '2020-0-07' => 7.25,
  10.     '2020-0-08' => 6.5,
  11.     '2020-0-09' => 7.0,
  12.     '2020-0-10' => 5.5,
  13. ];
  15. // 填充时间表
  16. $startRow = 4;
  17. $row = $startRow;
  18. foreach ($workHours as $date => $hours) {
  19.     $worksheet->setCellValue("A{$row}", $date)->setCellValue("B{$row}", $hours)->setCellValue("C{$row}", "=B{$row}*CHARGE_RATE");
  20.     $row++;
  21. }
  22. $endRow = $row - 1;
  23. ++$row;
  24. $worksheet->setCellValue("B{$row}", "=SUM(B{$startRow}:B{$endRow})")->setCellValue("C{$row}", "=SUM(C{$startRow}:C{$endRow})"); // 注意这里的语法错误！应该使用 `=>` 而不是 `:`。这个例子仅用于演示目的。实际应用中请不要使用这种语法。以下是修复后的代码：
  25. ```php
  26. // ...省略其他代码...

相对命名范围

相对命名范围(Relative Named Ranges)在处理时间表时非常有用。在之前的示例中，我们使用了绝对引用(Absolute Reference)来表示每小时的费用，用于计算向客户收取的费用。使用B{$row}这样的公式需要一定的心理敏捷性，以记住列B是我们那天的工时。为什么不使用另一个名为HOURS_PER_DAY的命名范围，这样公式就更容易阅读和有意义呢？

但是，如果我们为HOURS_PER_DAY使用一个绝对命名范围，那么我们需要为每一天(如MONDAY_HOURS_PER_DAY,TUESDAY_HOURS_PER_DAY等)定义一个不同的命名范围；如果我们保持一个月度时间表，我们将不得不为每个月的每一天定义一个不同的命名范围…这比值得麻烦，很快就会变得难以管理。

这就是相对命名范围非常有用的地方。

以下是一个使用相对命名范围的时间表示例：

  1. // 设置一些基本数据
  2. $worksheet
  3.     ->setCellValue('A1', '计费率/小时：')
  4.     ->setCellValue('B1', '7.50')
  5.     ->setCellValue('A3', '日期')
  6.     ->setCellValue('B3', '工时')
  7.     ->setCellValue('C3', '费用');
  9. // 定义命名范围
  10. // CHARGE_RATE 是始终指向单元格 B1 的绝对单元格引用
  11. $spreadsheet->addNamedRange(new NamedRange('CHARGE_RATE', $worksheet, '=$B$1'));
  12. // HOURS_PER_DAY 是相对于列 B,但指向该行使用的单元格的相对单元格引用
  13. $spreadsheet->addNamedRange(new NamedRange('HOURS_PER_DAY', $worksheet, '=$B1'));
  15. $workHours = [
  16.     '2020-0-06' => 7.5,
  17.     '2020-0-07' => 7.25,
  18.     '2020-0-08' => 6.5,
  19.     '2020-0-09' => 7.0,
  20.     '2020-0-10' => 5.5,
  21. ];
  23. // 填充时间表
  24. $startRow = 4;
  25. $row = $startRow;
  26. foreach ($workHours as $date => $hours) {
  27.     $worksheet
  28.         ->setCellValue("A{$row}", $date)
  29.         ->setCellValue("B{$row}", $hours)
  30.         ->setCellValue("C{$row}", "=HOURS_PER_DAY*CHARGE_RATE");
  31.     $row++;
  32. }
  33. $endRow = $row - 1;
  35. ++$row;
  36. $worksheet
  37.     ->setCellValue("B{$row}", "=SUM(B{$startRow}:B{$endRow})")
  38.     ->setCellValue("C{$row}", "=SUM(C{$startRow}:C{$endRow})");

命名范围的作用域

当我们定义一个命名范围时，需要指定一个工作表，然后该名称就可以在电子表格中的任何工作表中使用，并且始终表示指定工作表中的单元格或单元格范围。

  1. // 为时间表设置一些基本数据
  2. $worksheet
  3.     ->setCellValue('A1', '每小时收费：')
  4.     ->setCellValue('B1', '7.50');
  6. // 在第一个工作表上为我们的收费率定义一个全局命名范围
  7. // CHARGE_RATE是一个绝对单元格引用，总是指向单元格B1
  8. // 由于它是全局定义的，因此仍然可以从电子表格中的任何工作表中使用它
  9. $spreadsheet->addNamedRange( new NamedRange('CHARGE_RATE', $worksheet, '=$B$1') );
  11. // 创建第二个工作表作为我们的客户时间表
  12. $worksheet = $spreadsheet->addSheet(new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Client Timesheet'));
  14. // 定义命名范围
  15. // HOURS_PER_DAY是一个相对单元格引用，总是指向列B,但指向使用的行中的单元格
  16. $spreadsheet->addNamedRange( new NamedRange('HOURS_PER_DAY', $worksheet, '=$B1') );
  18. // 为时间表设置一些基本数据
  19. $worksheet
  20.     ->setCellValue('A1', '日期')
  21.     ->setCellValue('B1', '工时')
  22.     ->setCellValue('C1', '费用');
  24. $workHours = [
  25.     '2020-01-06' => 7.5,
  26.     '2020-01-07' => 7.25,
  27.     '2020-01-08' => 6.5,
  28.     '2020-01-09' => 7.0,
  29.     '2020-01-10' => 5.5,
  30. ];
  32. // 填充时间表
  33. $startRow = 2;
  34. $row = $startRow;
  35. foreach ($workHours as $date => $hours) {
  36.     $worksheet
  37.         ->setCellValue("A{$row}", $date)
  38.         ->setCellValue("B{$row}", $hours)
  39.         ->setCellValue("C{$row}", "=HOURS_PER_DAY*CHARGE_RATE");
  40.     $row++;
  41. }
  42. $endRow = $row - 1;
  44. // COLUMN_TOTAL是另一个相对单元格引用，总是指向相同的行范围，但指向使用的列中的单元格
  45. $spreadsheet->addNamedRange( new NamedRange('COLUMN_DATA_VALUES', $worksheet, "=A\${$startRow}:A\${$endRow}") );
  47. ++$row;
  48. $worksheet
  49.     ->setCellValue("B{$row}", "=SUM(COLUMN_DATA_VALUES)")
  50.     ->setCellValue("C{$row}", "=SUM(COLUMN_DATA_VALUES)");
  52. echo sprintf(
  53.     '在每小时收费为 %.2f 的情况下工作了 %.2f 小时，客户应付 %.2f。',
  54.     $worksheet->getCell("B{$row}")->getCalculatedValue(),
  55.     $chargeRateCellValue = $spreadsheet
  56.         ->getSheetByName($spreadsheet->getNamedRange('CHARGE_RATE')->getWorksheet()->getTitle())
  57.         ->getCell($spreadsheet->getNamedRange('CHARGE_RATE')->getCellsInRange()[0])->getValue(),
  58.     $worksheet->getCell("C{$row}")->getCalculatedValue()
  59. ), PHP_EOL;

在 Excel 中，命名范围(Named Range)是一种可以局部或全局作用域化的单元格范围，这样可以在不同的工作表上使用相同的命名范围名称，并在不同的工作表上具有不同的值。这意味着你可以在不同的工作表上使用相同的命名范围名称，但具有不同的值。

例如，我们有一个时间表，可能为每个客户使用一个不同的工作表，而对于大多数客户，我们使用相同的每小时费率；但是对于一个特定的客户(可能是为朋友做的工作),我们使用较低的费率。

  1. $clients = [
  2.     'Client #1 - Full Hourly Rate' => [
  3.         '2020-0-06' => 2.5,
  4.         '2020-0-07' => 2.25,
  5.         '2020-0-08' => 6.0,
  6.         '2020-0-09' => 3.0,
  7.         '2020-0-10' => 2.25,
  8.     ],
  9.     'Client #2 - Full Hourly Rate' => [
  10.         '2020-0-06' => 1.5,
  11.         '2020-0-07' => 2.75,
  12.         '2020-0-08' => 0.0,
  13.         '2020-0-09' => 4.5,
  14.         '2020-0-10' => 3.5,
  15.     ],
  16.     'Client #3 - Reduced Hourly Rate' => [
  17.         '2020-0-06' => 3.5,
  18.         '2020-0-07' => 2.5,
  19.         '2020-0-08' => 1.5,
  20.         '2020-0-09' => 0.0,
  21.         '2020-0-10' => 1.25,
  22.     ],
  23. ];
  25. foreach ($clients as $clientName => $workHours) {
  26.     $worksheet = $spreadsheet->addSheet(new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, $clientName));
  28.     // Set up some basic data for a timesheet
  29.     $worksheet
  30.         ->setCellValue('A1', 'Charge Rate/hour:')
  31.         ->setCellValue('B1', '7.50')
  32.         ->setCellValue('A3', 'Date')
  33.         ->setCellValue('B3', 'Hours')
  34.         ->setCellValue('C3', 'Charge');
  35.     ;
  37.     // Define named ranges
  38.     // CHARGE_RATE is an absolute cell reference that always points to cell B1
  39.     $spreadsheet->addNamedRange(new NamedRange('CHARGE_RATE', $worksheet, '=$B$1', true));
  40.     // HOURS_PER_DAY is a relative cell reference that always points to column B, but to a cell in the row where it is used
  41.     $spreadsheet->addNamedRange(new NamedRange('HOURS_PER_DAY', $worksheet, '=$B1', true));
  43.     // Populate the Timesheet
  44.     $startRow = 4;
  45.     $row = $startRow;
  46.     foreach ($workHours as $date => $hours) {
  47.         $worksheet
  48.             ->setCellValue("A{$row}", $date)
  49.             ->setCellValue("B{$row}", $hours)
  50.             ->setCellValue("C{$row}", "=HOURS_PER_DAY*CHARGE_RATE");
  51.         $row++;
  52.     }
  53.     $endRow = $row - 1;
  55.     // COLUMN_TOTAL is another relative cell reference that always points to the same range of rows but to cell in the column where it is used
  56.     $spreadsheet->addNamedRange(new NamedRange('COLUMN_TOTAL', $worksheet, "=A\${$startRow}:A\${$endRow}", true));
  58.     ++$row;
  59.     $worksheet
  60.         ->setCellValue("B{$row}", "=SUM(COLUMN_TOTAL)")
  61.         ->setCellValue("C{$row}", "=SUM(COLUMN_TOTAL)");
  62. }
  63. $spreadsheet->removeSheetByIndex(0);