phpspreadsheet 中文文档（三）计算引擎-morecoder

phpspreadsheet 中文文档（三）计算引擎

2019-10-11 16:04:25 发布 679 浏览

页面报错/反馈

已收藏点赞

2019年10月11日13:59:52

使用PhpSpreadsheet计算引擎

执行公式计算

由于PhpSpreadsheet表示内存中的电子表格，因此它还提供公式计算功能。单元格可以是值类型（包含数字或文本），也可以是公式类型（包含可以求值的公式）。例如，该公式=SUM(A1:A10) 计算得出A1，A2，...，A10中的值之和。

要计算公式，可以调用包含公式方法的单元格getCalculatedValue()，例如：

$spreadsheet->getActiveSheet()->getCell(‘E11‘)->getCalculatedValue();

如果您在PhpSpreadsheet随附的发票演示中编写了以下代码行，则其评估值为“ 64”：

分享图片

PhpSpreadsheet公式解析器的另一个不错的功能是，它可以在插入/删除行/列时自动调整公式。这是一个例子：

分享图片

您会看到单元格E11中包含的公式是“ SUM（E4：E9）”。现在，当我编写以下代码行时，添加了两个新的产品线：

$spreadsheet->getActiveSheet()->insertNewRowBefore(7, 2);

分享图片

你注意到了吗？以前的单元格E11中的公式（当我插入2个新行时为E13）更改为“ SUM（E4：E11）”。同样，插入的单元格将复制前一个单元格的样式信息，就像Excel的行为一样。请注意，您可以插入行和列。

计算缓存

一旦计算引擎评估了单元格中的公式后，结果将被缓存，因此，如果您getCalculatedValue()再次调用同一单元格，则结果将从缓存中返回，而不是第二次评估公式。这有助于提高性能，因为就性能和速度而言，评估公式是一项昂贵的操作。

但是，有时您可能不希望这样，也许您已经更改了基础数据，并且需要使用该新数据重新评估相同的公式。

Calculation::getInstance($spreadsheet)->disableCalculationCache();

将禁用计算缓存，并刷新当前计算缓存。

如果您只想刷新缓存，则可以调用

Calculation::getInstance($spreadsheet)->clearCalculationCache();

已知限制

PhpSpreadsheet计算引擎有一些已知的限制。它们中的大多数是由于在执行之前将Excel公式转换为PHP代码的事实。这意味着Excel公式的计算取决于PHP的语言特性。

Xls不支持的功能

并非所有功能都受支持，要获取完整列表，请按名称阅读功能列表。

运算符优先级

在Excel中+胜过&，就像在普通代数中*胜过一样+。前一条规则不是使用PhpSpreadsheet附带的计算引擎所能找到的。

涉及数字和文字的公式

包含数字和文本的公式可能会产生意外的结果，甚至导致文件内容无法读取。例如，=3+"Hello "期望该公式在Excel中产生错误（#VALUE！）。由于PHP转换"Hello "为数值（零）的事实，该公式的结果被评估为3，而不是错误。这还会导致Excel文档被生成为包含不可读的内容。

PHP中此行为的参考

公式似乎不是在Excel2003中使用兼容包计算的？

这是兼容包的正常行为，Xlsx正确显示了这一点。使用PhpOfficePhpSpreadsheetWriterXls如果你真的需要计算的值，或强制重新计算Excel2003中中。

处理日期和时间值

返回日期和时间值的Excel函数

在Excel中返回日期值的任何Date and Time函数都可以返回Excel时间戳或PHP时间戳或DateTime对象。

脚本可以通过调用PhpOfficePhpSpreadsheetCalculationFunctions::setReturnDateType() 方法来更改用于返回日期值的数据类型：

PhpOfficePhpSpreadsheetCalculationFunctions::setReturnDateType($returnDateType);

以下常量可用于$returnDateType：

PhpOfficePhpSpreadsheetCalculationFunctions::RETURNDATE_PHP_NUMERIC
PhpOfficePhpSpreadsheetCalculationFunctions::RETURNDATE_PHP_OBJECT
PhpOfficePhpSpreadsheetCalculationFunctions::RETURNDATE_EXCEL

该方法将在成功时返回布尔值True，在失败时返回False（例如，如果为返回日期类型传递了无效值）。

该PhpOfficePhpSpreadsheetCalculationFunctions::getReturnDateType() 方法可用于确定此设置的当前值：

$returnDateType = PhpOfficePhpSpreadsheetCalculationFunctions::getReturnDateType();

默认值为RETURNDATE_PHP_NUMERIC。

PHP时间戳

如果RETURNDATE_PHP_NUMERIC为Return Date Type设置了返回值，则通过对Excel中的Date和Time函数的任何访问返回到调用脚本的任何日期值将是一个整数值，表示距PHP / Unix基础日期的秒数。PHP / Unix的基准日期（0）在1970年1月1日为美国标准时间00:00。该值可以是正数或负数：因此-3600的值将是1969年12月31日的23:00。而1970年1月1日的+3600值为01:00。这使PHP的日期范围为1901年12月14日至2038年1月19日。

PHP `DateTime`对象

如果将Return Date Type设置为RETURNDATE_PHP_OBJECT，则通过对Excel中的Date和Time函数的任何访问返回到调用脚本的任何日期值将是一个PHP DateTime对象。

Excel时间戳

如果RETURNDATE_EXCEL为Return Date Type设置了返回值，则通过对Excel中的Date和Time函数的任何访问返回的日期值将是一个浮点值，代表距Excel基本日期的天数。Excel的基本日期由Excel使用的日历决定：Windows 1900或Mac 1904日历。1900年1月1日是Windows 1900日历的基准日期，而1904年1月1日是Mac 1904日历的基准日期。

脚本可以通过调用以下PhpOfficePhpSpreadsheetSharedDate::setExcelCalendar()方法来更改用于计算Excel日期值的日历：

PhpOfficePhpSpreadsheetSharedDate::setExcelCalendar($baseDate);

以下常量可用于$baseDate：

PhpOfficePhpSpreadsheetSharedDate::CALENDAR_WINDOWS_1900
PhpOfficePhpSpreadsheetSharedDate::CALENDAR_MAC_1904

该方法将在成功时返回布尔值True，在失败时返回False（例如，如果传入了无效值）。

该PhpOfficePhpSpreadsheetSharedDate::getExcelCalendar()方法可用于确定此设置的当前值：

$baseDate = PhpOfficePhpSpreadsheetSharedDate::getExcelCalendar();

默认值为CALENDAR_WINDOWS_1900。

返回日期/时间值的函数

日期
DATEVALUE
EDATE
EOMONTH
现在
时间
时间值
今天

接受日期和时间值作为参数的Excel函数

作为参数传递给函数的日期值可以是Excel时间戳或PHP时间戳；或DateTime对象；或包含日期值的字符串（例如“ 2009年1月1日”）。PhpSpreadsheet将尝试根据PHP数据类型识别其类型：

整数将被视为PHP / Unix时间戳。实数值（浮点数）将被视为Excel日期/时间戳。任何PHP DateTime对象都将被视为DateTime 对象。任何字符串值（甚至包含直接数字数据的字符串值）都将转换为DateTime用于验证的对象作为基于服务器区域设置的日期值，因此，如果服务器设置为英国，则通过模棱两可的值‘07 / 08/2008‘将被视为2008年8月7日，而如果服务器为UK，则将其视为2008年7月8日设置为美国。但是，如果您传递的值（例如“ 31/12/2008”）被位于美国的服务器视为错误，但并不明确，则PhpSpreadsheet会尝试将其更正为2008年12月31日。字符串的内容与php DateTime对象实现所识别的任何格式都不匹配strtotime()（可以处理比常规strtotime()函数更广泛的格式），然后该函数将返回#VALUE错误。但是，Excel建议您始终对日期函数使用日期/时间戳，对PhpSpreadsheet的建议也相同：避免字符串，因为结果不可预测。

将数据写入Excel时，将应用相同的原理。包含日期实际值（而不是返回日期值的Excel函数）的单元格始终被写入Excel日期，并在必要时进行转换。如果格式化为日期的单元格包含整数或 DateTime对象值，则将其转换为Excel值以进行写入：如果格式化为日期的单元格包含实数值，则无需进行转换。请注意，字符串值被写为字符串，而不是转换为Excel日期时间戳值。

需要日期/时间值的函数

达蒂夫
天
DAYS360
EDATE
EOMONTH
小时
分钟
月
网络日
第二
平日
WEEKNUM
工作日
年
年分会

辅助方法

除了setExcelCalendar()和getExcelCalendar()方法，PhpOfficePhpSpreadsheetSharedDate该类中还有许多其他方法可以帮助处理日期：

PhpOffice PhpSpreadsheet Shared Date :: excelToTimestamp（$ excelDate）

从Excel日期时间戳转换日期/时间以返回PHP序列化的日期/时间戳。

请注意，此方法不会捕获超出PHP日期时间戳有效范围的Excel日期。

PhpOffice PhpSpreadsheet Shared Date :: excelToDateTimeObject（$ excelDate）

将日期转换为Excel日期/时间戳以返回PHP DateTime 对象。

PhpOffice PhpSpreadsheet Shared Date :: PHPToExcel（$ PHPDate）

转换PHP序列化的日期/时间戳或PHP DateTime对象以返回Excel日期时间戳。

PhpOffice PhpSpreadsheet Shared Date :: formattedPHPToExcel（$ year，$ month，$ day，$ hours = 0，$ minutes = 0，$ seconds = 0）

接受年，月和日值（以及可选的时，分和秒值），并返回Excel日期时间戳值。

时区支持Excel日期时间戳转换

PhpSpreadsheet中日期函数的默认时区为UST（通用标准时间）。如果需要使用其他时区，则可以使用以下方法：

PhpOffice PhpSpreadsheet Shared Date :: getDefaultTimezone（）

返回PhpSpeadsheet用于处理日期和时间的当前时区值。

PhpOffice PhpSpreadsheet Shared Date :: setDefaultTimezone（$ timeZone）

将Excel日期时间戳转换的时区设置为$ timeZone，该值必须是有效的PHP DateTimeZone值。返回值是一个布尔值，其中true是成功，false是失败（例如，传递了无效的DateTimeZone值。）

PhpOffice PhpSpreadsheet Shared Date :: excelToDateTimeObject（$ excelDate，$ timeZone）

PhpOffice PhpSpreadsheet Shared Date :: excelToTimeStamp（$ excelDate，$ timeZone）

这些函数支持将时区作为可选的第二个参数。这会将特定时区应用于该函数调用，而不会影响默认的PhpSpreadsheet时区。

功能参考

数据库功能

降级

DAVERAGE函数返回列表或数据库中符合您指定条件的列中单元格的平均值。

句法

DAVERAGE (database, field, criteria)

参量

数据库组成列表或数据库的单元格范围。

数据库是相关数据的列表，其中相关信息的行是记录，数据的列是字段。列表的第一行包含每一列的标签。

字段指示函数中使用数据库的哪一列。

输入列标签为字符串（用双引号引起来），例如“ Age”或“ Yield”，或者输入代表列在列表中位置的数字（不带引号）：1为第一列，2用于第二列，依此类推。

条件包含您指定条件的单元格范围。

您可以对criteria参数使用任何范围，只要它包含至少一个列标签和在列标签下方的至少一个单元格（您在其中为该列指定条件）即可。

返回值

float匹配单元格的平均值。

这是统计平均值。

例子

$database = [
    [ ‘Tree‘,  ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘ ],
    [ ‘Apple‘,  18,       20,    14,      105.00  ],
    [ ‘Pear‘,   12,       12,    10,       96.00  ],
    [ ‘Cherry‘, 13,       14,     9,      105.00  ],
    [ ‘Apple‘,  14,       15,    10,       75.00  ],
    [ ‘Pear‘,    9,        8,     8,       76.80  ],
    [ ‘Apple‘,   8,        9,     6,       45.00  ],
];

$criteria = [
    [ ‘Tree‘,      ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘, ‘Height‘ ],
    [ ‘="=Apple"‘, ‘>10‘,    NULL,  NULL,    NULL,     ‘<16‘    ],
    [ ‘="=Pear"‘,  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, ‘A1‘ )
    ->fromArray( $database, NULL, ‘A4‘ );

$worksheet->setCellValue(‘A12‘, ‘=DAVERAGE(A4:E10,"Yield",A1:B2)‘);

$retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();
// $retVal = 12

笔记

此功能没有其他注释

DCOUNT

DCOUNT函数返回指定列表或数据库中符合条件的列中包含数字的单元格计数。

句法

DCOUNT(database, [field], criteria)

参量

数据库组成列表或数据库的单元格范围。

数据库是相关数据的列表，其中相关信息的行是记录，数据的列是字段。列表的第一行包含每一列的标签。

字段指示函数中使用数据库的哪一列。

条件包含您指定条件的单元格范围。

您可以对criteria参数使用任何范围，只要它包含至少一个列标签和在列标签下方的至少一个单元格（您在其中为该列指定条件）即可。

返回值

float匹配单元格的计数。

例子

$database = [
    [ ‘Tree‘,  ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘ ],
    [ ‘Apple‘,  18,       20,    14,      105.00  ],
    [ ‘Pear‘,   12,       12,    10,       96.00  ],
    [ ‘Cherry‘, 13,       14,     9,      105.00  ],
    [ ‘Apple‘,  14,       15,    10,       75.00  ],
    [ ‘Pear‘,    9,        8,     8,       76.80  ],
    [ ‘Apple‘,   8,        9,     6,       45.00  ],
];

$criteria = [
    [ ‘Tree‘,      ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘, ‘Height‘ ],
    [ ‘="=Apple"‘, ‘>10‘,    NULL,  NULL,    NULL,     ‘<16‘    ],
    [ ‘="=Pear"‘,  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, ‘A1‘ )
    ->fromArray( $database, NULL, ‘A4‘ );

$worksheet->setCellValue(‘A12‘, ‘=DCOUNT(A4:E10,"Height",A1:B3)‘);

$retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();

// $retVal = 3

笔记

在MS Excel中，field参数是可选的。如果省略该字段，则DCOUNT将对数据库中符合条件的所有记录进行计数。PhpSpreadsheet中尚未实现此逻辑。

DCOUNTA

DCOUNT函数返回列表或数据库的列中不为空且匹配您指定条件的单元格计数。

句法

DCOUNTA(database, [field], criteria)

参量

数据库组成列表或数据库的单元格范围。

数据库是相关数据的列表，其中相关信息的行是记录，数据的列是字段。列表的第一行包含每一列的标签。

字段指示函数中使用数据库的哪一列。

条件包含您指定条件的单元格范围。

您可以对criteria参数使用任何范围，只要它包含至少一个列标签和在列标签下方的至少一个单元格（您在其中为该列指定条件）即可。

返回值

float匹配单元格的计数。

例子

$database = [
    [ ‘Tree‘,  ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘ ],
    [ ‘Apple‘,  18,       20,    14,      105.00  ],
    [ ‘Pear‘,   12,       12,    10,       96.00  ],
    [ ‘Cherry‘, 13,       14,     9,      105.00  ],
    [ ‘Apple‘,  14,       15,    10,       75.00  ],
    [ ‘Pear‘,    9,        8,     8,       76.80  ],
    [ ‘Apple‘,   8,        9,     6,       45.00  ],
];

$criteria = [
    [ ‘Tree‘,      ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘, ‘Height‘ ],
    [ ‘="=Apple"‘, ‘>10‘,    NULL,  NULL,    NULL,     ‘<16‘    ],
    [ ‘="=Pear"‘,  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, ‘A1‘ )
    ->fromArray( $database, NULL, ‘A4‘ );

$worksheet->setCellValue(‘A12‘, ‘=DCOUNTA(A4:E10,"Yield",A1:A3)‘);

$retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();

// $retVal = 5

笔记

在MS Excel中，field参数是可选的。如果省略该字段，则DCOUNTA将对数据库中符合条件的所有记录进行计数。PhpSpreadsheet中尚未实现此逻辑。

DGET

DGET函数从与您指定的条件匹配的列表或数据库的列中提取单个值。

句法

DGET(database, field, criteria)

参量

数据库组成列表或数据库的单元格范围。

数据库是相关数据的列表，其中相关信息的行是记录，数据的列是字段。列表的第一行包含每一列的标签。

字段指示函数中使用数据库的哪一列。

条件包含您指定条件的单元格范围。

您可以对criteria参数使用任何范围，只要它包含至少一个列标签和在列标签下方的至少一个单元格（您在其中为该列指定条件）即可。

返回值

混合从匹配行的选定列中选择的值。

例子

$database = [
    [ ‘Tree‘,  ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘ ],
    [ ‘Apple‘,  18,       20,    14,      105.00  ],
    [ ‘Pear‘,   12,       12,    10,       96.00  ],
    [ ‘Cherry‘, 13,       14,     9,      105.00  ],
    [ ‘Apple‘,  14,       15,    10,       75.00  ],
    [ ‘Pear‘,    9,        8,     8,       76.80  ],
    [ ‘Apple‘,   8,        9,     6,       45.00  ],
];

$criteria = [
    [ ‘Tree‘,      ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘, ‘Height‘ ],
    [ ‘="=Apple"‘, ‘>10‘,    NULL,  NULL,    NULL,     ‘<16‘    ],
    [ ‘="=Pear"‘,  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, ‘A1‘ )
    ->fromArray( $database, NULL, ‘A4‘ );

$worksheet->setCellValue(‘A12‘, ‘=GET(A4:E10,"Age",A1:F2)‘);

$retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();
// $retVal = 14

笔记

此功能没有其他注释

DMAX

DMAX函数返回列表或数据库中符合您指定条件的列中的最大数字。

句法

DMAX(database, field, criteria)

参量

数据库组成列表或数据库的单元格范围。

数据库是相关数据的列表，其中相关信息的行是记录，数据的列是字段。列表的第一行包含每一列的标签。

字段指示函数中使用数据库的哪一列。

条件包含您指定条件的单元格范围。

您可以对criteria参数使用任何范围，只要它包含至少一个列标签和在列标签下方的至少一个单元格（您在其中为该列指定条件）即可。

返回值

float匹配单元格的最大值。

例子

$database = [
    [ ‘Tree‘,  ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘ ],
    [ ‘Apple‘,  18,       20,    14,      105.00  ],
    [ ‘Pear‘,   12,       12,    10,       96.00  ],
    [ ‘Cherry‘, 13,       14,     9,      105.00  ],
    [ ‘Apple‘,  14,       15,    10,       75.00  ],
    [ ‘Pear‘,    9,        8,     8,       76.80  ],
    [ ‘Apple‘,   8,        9,     6,       45.00  ],
];

$criteria = [
    [ ‘Tree‘,      ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘, ‘Height‘ ],
    [ ‘="=Apple"‘, ‘>10‘,    NULL,  NULL,    NULL,     ‘<16‘    ],
    [ ‘="=Pear"‘,  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, ‘A1‘ )
    ->fromArray( $database, NULL, ‘A4‘ );

$worksheet->setCellValue(‘A12‘, ‘=DMAX(A4:E10,"Profit",A1:B2)‘);

$retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();
// $retVal = 105

笔记

此功能没有其他注释

DMIN

DMIN函数返回与指定条件匹配的列表或数据库的列中的最小数字。

句法

DMIN(database, field, criteria)

参量

数据库组成列表或数据库的单元格范围。

数据库是相关数据的列表，其中相关信息的行是记录，数据的列是字段。列表的第一行包含每一列的标签。

字段指示函数中使用数据库的哪一列。

条件包含您指定条件的单元格范围。

您可以对criteria参数使用任何范围，只要它包含至少一个列标签和在列标签下方的至少一个单元格（您在其中为该列指定条件）即可。

返回值

float匹配单元格的最小值。

例子

$database = [
    [ ‘Tree‘,  ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘ ],
    [ ‘Apple‘,  18,       20,    14,      105.00  ],
    [ ‘Pear‘,   12,       12,    10,       96.00  ],
    [ ‘Cherry‘, 13,       14,     9,      105.00  ],
    [ ‘Apple‘,  14,       15,    10,       75.00  ],
    [ ‘Pear‘,    9,        8,     8,       76.80  ],
    [ ‘Apple‘,   8,        9,     6,       45.00  ],
];

$criteria = [
    [ ‘Tree‘,      ‘Height‘, ‘Age‘, ‘Yield‘, ‘Profit‘,

登录查看全部

参与评论