Phpspreadsheet 中文文档（一）访问单元格

最新推荐文章于 2024-04-23 10:06:36 发布

withoutfear

最新推荐文章于 2024-04-23 10:06:36 发布

阅读量1k

点赞数

分类专栏： PhpSpreadsheet 文章标签： php PhpSpreadsheet

原文链接：https://www.cnblogs.com/zx-admin/p/11653754.html

版权

PhpSpreadsheet 专栏收录该内容

12 篇文章 6 订阅

订阅专栏

访问单元格

访问电子表格中的单元格应该非常简单。本主题列出了一些访问单元的选项。

通过坐标设置单元格值

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

// 用字符串值设置单元格A1
$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');

// 用数值设置单元格A2
$spreadsheet->getActiveSheet()->setCellValue('A2', 12345.6789);

// 设置单元A3具有布尔值
$spreadsheet->getActiveSheet()->setCellValue('A3', TRUE);

// 用公式设定单元格A4
$spreadsheet->getActiveSheet()->setCellValue(
    'A4',
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);

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

$spreadsheet->getActiveSheet()
    ->getCell('B8')
    ->setValue('Some value');

创建一个新的单元格

如果您呼叫getCell()，而该储存格尚不存在，则PhpSpreadsheet将（预设）为您建立储存格。如果您不想创建新的单元格，则可以传递第二个参数false，getCell()如果该单元格不存在，则将返回null。

注意：单元分配给变量作为分离引用

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

这是什么意思？考虑以下代码：

$spreadSheet = new Spreadsheet();
$workSheet = $spreadSheet->getActiveSheet();

// 设定我们想要评估的公式的细节，以及它所依赖的任何数据
$workSheet->fromArray(
    [1, 2, 3],
    null,
    'A1'
);

$cellC1 = $workSheet->getCell('C1');
echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;

$cellA1 = $workSheet->getCell('A1');
echo 'Value: ', $cellA1->getValue(), '; Address: ', $cellA1->getCoordinate(), PHP_EOL;

echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;

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

因此，当我们尝试再次显示值和地址时，可以显示其值，但是尝试显示其地址/坐标将引发异常，因为该链接已设置为null。

注意：有一些内部方法会从集合中获取其他单元格，这也将把您可能已分配给变量的任何单元格的链接分离。

Excel数据类型

MS Excel支持7种基本数据类型：

串
数
布尔值
空值
式
错误
内联（或富文本）字符串

默认情况下，当您调用工作表的setCellValue()方法或单元格的setValue()方法时，PhpSpreadsheet将为PHP空值，布尔值，浮点数或整数使用适当的数据类型；或将传递给该方法的任何字符串数据值转换为最合适的数据类型，这样数字字符串将=转换为数字，而以开头的字符串值将转换为公式。非数字字符串或不=以前导开头的字符串将被视为真正的字符串值。

此“转换”由单元格“值绑定器”处理，您可以编写自定义“值绑定器”以更改这些“转换”的行为。标准的PhpSpreadsheet程序包还提供了一个“高级绑定值”，用于处理许多更复杂的转换，例如将分数格式（例如“ 3/4”的字符串）转换为数字值（在这种情况下为0.75），并设置适当的“分数”数字格式掩码。同样，将类似“ 5％”的字符串转换为0.05的值，并应用百分比格式的掩码，将包含类似于日期的值的字符串转换为Excel序列化的datetimestamp值，并应用相应的掩码。从csv文件加载数据时，此功能特别有用，

高级价值绑定程序处理的格式包括：

TRUE或FALSE（取决于语言环境设置）将转换为布尔值。
标识为科学（指数）格式的数字字符串将转换为数字。
将分数和粗俗的分数转换为数字，并应用适当的数字格式掩码。
将百分比转换为数字，然后除以100，然后应用适当的数字格式掩码。
日期和时间将转换为Excel时间戳值（数字），并应用适当的数字格式掩码。
当字符串包含换行符（\n）时，单元格样式将设置为自动换行。

您可以在本文档的此部分后面阅读有关价值约束的更多信息。

在单元格中设置公式

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

但是，有时您可能希望存储以= 字符串开头的值，并且您不希望PHPSpreadsheet像公式一样对它进行求值。

为此，您需要通过将其值设置为“引用文本”来“转义”该值。

// 用公式设定单元格A4
$spreadsheet->getActiveSheet()->setCellValue(
    'A4',
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);
$spreadsheet->getActiveSheet()->getCell('A4')
    ->getStyle()->setQuotePrefix(true);

然后，即使您要求PHPSpreadsheet返回cell的计算值 A4，它也将以=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1)) 字符串形式返回，而不是尝试计算公式。

在单元格中设置日期和/或时间值

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

// 获取当前日期/时间并转换为Excel日期/时间
$dateTimeNow = time();
$excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $dateTimeNow );
// 用Excel日期/时间值设置单元格A6
$spreadsheet->getActiveSheet()->setCellValue(
    'A6',
    $excelDateValue
);
// 设置数字格式掩码，使excel时间戳显示为人类可读的日期/时间
$spreadsheet->getActiveSheet()->getStyle('A6')
    ->getNumberFormat()
    ->setFormatCode(
        \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
    );

用前导零设置数字

默认情况下，PhpSpreadsheet将自动检测值类型并将其设置为适当的Excel数字数据类型。这种类型转换由值绑定器处理，如本文档标题为“使用值绑定器以方便数据输入”的部分所述。

数字没有前导零，因此，如果您尝试设置确实有前导零的数字值（例如电话号码），则这些数字通常会在将值强制转换为数字时丢失，因此“ 01513789642”将显示为1513789642。

您可以通过两种方式强制PhpSpreadsheet覆盖此行为。

首先，您可以将数据类型显式设置为字符串，以便不将其转换为数字。

// 将单元格A8设置为数值，但是告诉PhpSpreadsheet它应该被当作字符串处理
$spreadsheet->getActiveSheet()->setCellValueExplicit(
    'A8',
    "01513789642",
    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);

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

// 用数值设置单元格A9
$spreadsheet->getActiveSheet()->setCellValue('A9', 1513789642);
// 设置一个数字格式掩码，将数值显示为带有前导零的11位数字
$spreadsheet->getActiveSheet()->getStyle('A9')
    ->getNumberFormat()
    ->setFormatCode(
        '00000000000'
    );

使用数字格式屏蔽，您甚至可以将数字分成几组，以使值更易于阅读。

// 用数值设置单元格A10
$spreadsheet->getActiveSheet()->setCellValue('A10', 1513789642);
// 设置一个数字格式掩码，将数值显示为带有前导零的11位数字
$spreadsheet->getActiveSheet()->getStyle('A10')
    ->getNumberFormat()
    ->setFormatCode(
        '0000-000-0000'
    );

注意：在检索格式化的值以在“屏幕上”显示时，或者对于某些编写器（例如HTML或PDF），并不是所有这样的复杂格式掩码都可以使用，但可以与真正的电子表格编写器（Xlsx和Xls）一起使用。

设置数组中的单元格范围

通过将值数组传递给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,  // 要设置的数据
        NULL,        // 具有该值的数组值不会被设置
        'C3'         // 工作表的左上角坐标范围我们要设置这些值(默认为A1)
    );

如果传递二维数组，则将其视为一系列行和列。一维数组将被视为单行，如果您要从数据库中获取数据数组，这将特别有用。

$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$spreadsheet->getActiveSheet()
    ->fromArray(
        $rowArray,   // 要设置的数据
        NULL,        // 具有该值的数组值不会被设置
        'C3'         // 工作表的左上角坐标范围我们要设置这些值(默认为A1)
    );

如果您有一个简单的1-d数组，并希望将其写为一列，则以下内容会将其转换为结构正确的2-d数组，可以将其馈送到该fromArray()方法中：

$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$columnArray = array_chunk($rowArray, 1);
$spreadsheet->getActiveSheet()
    ->fromArray(
        $columnArray,   // 要设置的数据
        NULL,           // 具有该值的数组值不会被设置
        'C3'            // 工作表的左上角坐标范围我们要设置这些值(默认为A1)
    );

通过坐标检索单元格值

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

// 从单元格A1中获取值
$cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue();

这将检索单元格中包含的原始，未格式化的值。

如果单元格包含一个公式，并且您需要检索计算的值而不是公式本身，则使用该单元格的 getCalculatedValue()方法。这将在计算引擎中进一步说明。

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

或者，如果要查看应用了任何单元格格式的值（例如，对于人类可读的日期或时间值），则可以使用单元格的getFormattedValue()方法。

// 从单元格A6中获取值
$cellValue = $spreadsheet->getActiveSheet()->getCell('A6')->getFormattedValue();

按列和行设置单元格值

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

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

注意：列引用以1for column 开头A。

按列和行检索单元格值

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

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

如果需要计算出的像元值，请使用以下代码。这将在计算引擎中进一步说明。

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

检索单元格值范围到数组

另外，也可以在使用一个单一的呼叫以检索范围的细胞的值到一个数组toArray()，rangeToArray()或 namedRangeToArray()方法。

$dataArray = $spreadsheet->getActiveSheet()
    ->rangeToArray(
        'C3:E5',     // 我们要检索的工作表范围
        NULL,        // 空单元格应返回的值
        TRUE,        // 是否应该为每个单元格计算( get计算值的等价性() )
        TRUE,        // 每个单元格的值应该格式化为( getFormattedValue的等价形式() )
        TRUE         // 该数组应该由单元格行和单元格列索引
    );

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

遍历单元格

使用迭代器遍历单元格

循环单元的最简单方法是使用迭代器。使用迭代器，可以使用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); // 这个循环遍历所有单元格，即使没有设置单元格值。
                                                       // 默认情况下，只有具有值集的单元格才会被迭代。
    foreach ($cellIterator as $cell) {
        echo '<td>' .
             $cell->getValue() .
             '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

请注意，我们已将单元格迭代器设置 setIterateOnlyExistingCells()为FALSE。这使得迭代器循环工作表范围内的所有单元格，即使它们尚未设置。

null如果未在工作表中设置，单元格迭代器将返回a 作为单元格值。将单元格迭代器设置 setIterateOnlyExistingCells()为false会循环工作表中当时可用的所有单元格。如果需要，这将创建新的单元并增加内存使用！仅在打算循环所有可用单元时才使用它。

使用索引循环遍历单元格

可以使用按列和行索引访问单元格值的可能性，[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();
// 获取工作表中引用的最高行列号
$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();
// 获取工作表中引用的最高行号和列字母
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// 增加最高列字母
$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;

请注意，我们不能<=在此处进行比较，因为'AA'它将与匹配<= 'B'，因此我们递增最高的列字母，然后$col !=在递增的最高列中循环。

使用价值粘结剂方便数据输入

在内部，PhpSpreadsheet使用默认 \PhpOffice\PhpSpreadsheet\Cell\IValueBinder实现（\ PhpOffice \ PhpSpreadsheet \ Cell \ DefaultValueBinder）使用单元格的setValue()方法确定输入数据的数据类型（该 setValueExplicit()方法会绕过此检查）。

（可选）可以修改PhpSpreadsheet的默认行为，以便更轻松地输入数据。例如，一个 \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder类可用。它会自动将百分比，科学格式的数字和以字符串形式输入的日期转换为正确的格式，同时还设置单元格的样式信息。下面的示例演示如何在PhpSpreadsheet中设置值活页夹：

/** PhpSpreadsheet */
require_once 'src/Boostrap.php';

// 设定连接器的值
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

// 创建新的电子表格对象
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// ...
// 增加一些数据，类似于一些不同的数据类型
$spreadsheet->getActiveSheet()->setCellValue('A4', 'Percentage value:');
// 将字符串值转换为0.1并设置百分比单元格样式
$spreadsheet->getActiveSheet()->setCellValue('B4', '10%');

$spreadsheet->getActiveSheet()->setCellValue('A5', 'Date/time value:');
// 将字符串值转换为Excel日期戳并设置日期格式单元格样式
$spreadsheet->getActiveSheet()->setCellValue('B5', '21 December 1983');

创建自己的价值粘合剂很容易。需要高级值绑定时，可以实现 \PhpOffice\PhpSpreadsheet\Cell\IValueBinder接口或扩展 \PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinderor \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder类