phpspreadsheet 中文文档（二） 结构+自动筛选

原理图

01-schematic.png

自动加载器

PhpSpreadsheet依赖于Composer自动加载器。因此，在独立使用PhpSpreadsheet之前，请确保先运行composer install。或使用将其添加到预先存在的项目中composer require phpoffice/phpspreadsheet。

电子表格在内存中

PhpSpreadsheet的体系结构以可以用作内存电子表格的方式构建。这意味着，如果要创建与PhpSpreadsheet的对象模型进行通信的电子表格的基于Web的视图，则他只需要编写前端代码即可。

就像桌面电子表格软件一样，PhpSpreadsheet表示包含一个或多个工作表的电子表格，该工作表包含具有数据，公式，图像等的单元格。

读者和作家

就其本身而言，该Spreadsheet班没有提供的功能读取或写入到一个持久的电子表格（在磁盘或数据库）。为了提供该功能，可以使用读取器和写入器。

默认情况下，PhpSpreadsheet软件包提供了一些读取器和写入器，其中包括一种用于Open XML电子表格格式（也称为Excel 2007文件格式）的读写器。您不限于默认的读取器和编写器，因为您可以在自定义类中自由实现 \PhpOffice\PhpSpreadsheet\Reader\IReaderand \PhpOffice\PhpSpreadsheet\Writer\IWriter接口。

02-readers-writers.png

流利的界面

PhpSpreadsheet在大多数位置都支持流畅的界面。这意味着您可以轻松地“链接”对特定方法的调用，而无需新的PHP语句。例如，使用以下代码：

$spreadsheet->getProperties()->setCreator("Maarten Balliauw");
$spreadsheet->getProperties()->setLastModifiedBy("Maarten Balliauw");
$spreadsheet->getProperties()->setTitle("Office 2007 XLSX Test Document");
$spreadsheet->getProperties()->setSubject("Office 2007 XLSX Test Document");
$spreadsheet->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$spreadsheet->getProperties()->setKeywords("office 2007 openxml php");
$spreadsheet->getProperties()->setCategory("Test result file");

可以改写为：

$spreadsheet->getProperties()
    ->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Maarten Balliauw")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");

不需要使用流利的接口已经实现了流利的接口以提供方便的编程API。不需要使用它们，但是可以使您的代码更易于阅读和维护。当您减少对PhpSpreadsheet方法的调用总数时，它也可以提高性能：在上面的示例中，该 getProperties()方法仅被调用一次，而不是非流利版本中被调用7次

 

 

自动筛选参考

介绍

Excel工作簿中的每个工作表都可以包含一个自动筛选范围。筛选的数据仅显示符合您指定条件的行，并隐藏您不想显示的行。您可以按不止一列进行过滤：过滤器是加性的，这意味着每个其他过滤器都基于当前过滤器，并进一步减少了数据子集。

01-01-autofilter.png

将自动过滤器应用于一定范围的单元格时，自动过滤器范围中的第一行将是标题行，其中显示自动过滤器下拉图标。它不是实际的自动筛选数据的一部分。随后的所有行都是自动过滤的数据。因此，自动筛选器范围应始终包含标题行和一个或多个数据行（一个数据行几乎没有意义），但是PhpSpreadsheet并不会阻止您指定无意义的范围：由开发人员自行决定如何避免此类错误。

要确定是否应用了过滤器，请注意列标题​​中的图标。下拉箭头（01-03-filter-icon-1.png）表示已启用过滤但未应用过滤。在MS Excel中，当您将鼠标悬停在启用了过滤但未应用过滤的列的标题上时，屏幕提示将显示该列第一行的单元格文本，并显示消息“（显示全部）”。

01-02-autofilter.png

“过滤器”按钮（01-03-filter-icon-2.png）表示已应用过滤器。将鼠标悬停在已过滤列的标题上时，屏幕提示会显示已应用于该列的过滤器，例如“等于红色单元格颜色”或“大于150”。

01-04-autofilter.png

在工作表上设置一个自动筛选区域

在一系列单元格上设置自动过滤器。

$spreadsheet->getActiveSheet()->setAutoFilter('A1:E20');

自动过滤器范围的第一行将是标题行，其中显示自动过滤器下拉图标。它不是实际的自动筛选数据的一部分。随后的所有行都是自动过滤的数据。因此，自动筛选器范围应始终包含标题行和一个或多个数据行（一个数据行是毫无意义的，但是PhpSpreadsheet并不会阻止您指定一个无意义的范围：这取决于开发人员如何避免此类错误。

如果要将整个工作表设置为自动筛选区域

$spreadsheet->getActiveSheet()->setAutoFilter(
    $spreadsheet->getActiveSheet()
        ->calculateWorksheetDimension()
);

这样可以进行过滤，但实际上不应用任何过滤器。

自动过滤器表达式

PHPEXcel 1.7.8引入了实际创建，读取和写入过滤器表达式的功能；最初仅适用于Xlsx文件，但更高版本会将其扩展为其他格式。

要将过滤器表达式应用于autoFilter范围，首先需要确定要将此过滤器应用于哪一列。

$autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter();
$columnFilter = $autoFilter->getColumn('C');

这将返回一个autoFilter列对象，然后您可以将过滤器表达式应用于该列。

有许多不同类型的自动过滤器表达式。最常用的是：

  • 简单过滤器
  • 日期组过滤器
  • 自定义过滤器
  • 动态滤镜
  • 十大筛选器

这些不同类型在任何单个列中都是互斥的。您不应在同一列中混合使用不同类型的过滤器。PhpSpreadsheet不会主动阻止您执行此操作，但是结果是不可预测的。

尚不支持其他过滤器表达式类型（例如单元格颜色过滤器）。

简单的过滤器

在MS Excel中，“简单过滤器”是该列中使用的所有值的下拉列表，用户可以通过选中和取消选中每个选项旁边的复选框来选择要显示的值和要隐藏的值。应用过滤器时，将显示包含已选中条目的行，不包含那些值的行将被隐藏。

04-01-simple-autofilter.png

要创建过滤器表达式，我们需要先确定过滤器类型。在这种情况下，我们将仅指定此过滤器为标准过滤器。

$columnFilter->setFilterType(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER
);

现在我们已经确定了过滤器类型，我们可以创建过滤器规则并设置过滤器值：

在PhpSpreadsheet中创建简单过滤器时，只需指定“已检查”列的值即可：您可以通过为每个值创建过滤器规则来做到这一点。

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        'France'
    );

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        'Germany'
    );

这将创建两个过滤规则：该列将使用与“ France”或“ Germany”匹配的值进行过滤。对于简单过滤器，您可以根据需要创建任意多个规则

简单过滤器始终是EQUALS的比较匹配，而多个标准过滤器始终被视为由OR条件连接。

配套空白

如果要创建过滤器以选择空白单元格，则可以使用：

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        ''
    );

日期组过滤器

在MS Excel中，DateGroup筛选器为日期值提供了一系列下拉筛选器选择器，因此您可以指定整个年份或一年中的几个月，或每个月中的几天。

04-02-dategroup-autofilter.png

DateGroup筛选器仍作为标准筛选器类型应用。

$columnFilter->setFilterType(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER
);

在PhpSpreadsheet中创建一个dateGroup过滤器，您可以将“选中”列的值指定为年份的关联数组。月，日，小时，分钟和秒。要选择年份和月份，您需要创建一个DateGroup规则来标识所选的年份和月份：

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        [
            'year' => 2012,
            'month' => 1
        ]
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP
    );

关联数组的键值为：

  • 年
  • 月
  • 天
  • 小时
  • 分钟
  • 第二

与标准过滤器一样，DateGroup过滤器始终是EQUALS的匹配项，并且多个标准过滤器始终被视为由OR条件联接。

请注意，我们还指定了ruleType：为了将其与标准过滤器区分开来，我们将Rule's Type显式设置为AUTOFILTER_RULETYPE_DATEGROUP。与标准过滤器一样，我们可以创建任意数量的DateGroup过滤器。

自定义过滤器

在MS Excel中，自定义过滤器允许我们使用运算符和值来选择更复杂的条件。典型示例可能是落入某个范围内的值（例如-20至+20之间），或者是带有通配符的文本值（例如以字母U开头）。为了解决这个问题，他们

04-03-custom-autofilter-1.png

04-03-custom-autofilter-2.png

自定义过滤器限制为2个规则，可以使用AND或OR将它们结合在一起。

我们从指定过滤器类型开始，这次是CUSTOMFILTER。

$columnFilter->setFilterType(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER
);

然后定义我们的规则。

下面显示了一个简单的通配符过滤器，以显示所有以字母开头的列条目U。

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        'U*'
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER
    );

MS Excel使用*作为通配符来匹配任意数量的字符，并且？作为匹配单个字符的通配符。“ U *”等于“以'U'开头”；'* U'等于“以'U'结尾”；“ * U *”等于“包含'U'”

如果要显式匹配*或？字符，您可以使用波浪号（\〜）对其进行转义，因此？\〜**将显式匹配*字符作为单元格值中的第二个字符，后跟任意数量的其他字符。唯一需要转义的其他字符是\〜本身。

要创建“介于”条件，我们需要定义两个规则：

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL,
        -20
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER
    );
$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL,
        20
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER
    );

我们还将规则类型设置为CUSTOMFILTER。

这定义了两个规则，即过滤>= -20OR的数字<= 20，因此我们还需要修改联接条件以反映AND而不是OR。

$columnFilter->setAndOr(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_COLUMN_ANDOR_AND
);

在\PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule类中定义了自定义过滤器的有效运算符集，这些运算符 包括：

运算符常量值
AUTOFILTER_COLUMN_RULE_EQUAL '等于'
AUTOFILTER_COLUMN_RULE_NOTEQUAL '不等于'
AUTOFILTER_COLUMN_RULE_GREATERTHAN '比...更棒'
AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL 'greaterThanOrEqual'
AUTOFILTER_COLUMN_RULE_LESSTHAN '少于'
AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL “ lessThanOrEqual”

动态滤镜

动态过滤器基于动态比较条件，其中我们要与单元格值进行比较的值是可变的，例如'today'；或在我们针对单元格数据的汇总进行测试时（例如“ aboveAverage”）。一次只能将一个动态过滤器应用于一列。

04-04-dynamic-autofilter.png

同样，我们从指定过滤器类型开始，这次是DYNAMICFILTER。

$columnFilter->setFilterType(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER
);

在为动态过滤器定义规则时，我们没有定义值（我们可以简单地将其设置为NULL），但可以指定动态过滤器类别。

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
        NULL,
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER
    );

我们还将规则类型设置为DYNAMICFILTER。

\PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule该类中定义了有效的动态过滤器类别集 ，包括：

运算符常量值
AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY '昨天'
AUTOFILTER_RULETYPE_DYNAMIC_TODAY '今天​​'
AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW '明天'
AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE '今年迄今为止'
AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR '今年'
AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER 'thisQuarter'
AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH '这个月'
AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK '本星期'
AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR '去年'
AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER “ lastQuarter”
AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH '上个月'
AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK '上个星期'
AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR '明年'
AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER 'nextQuarter'
AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH '下个月'
AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK '下周'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_1 'M1'
AUTOFILTER_RULETYPE_DYNAMIC_JANUARY 'M1'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_2 'M2'
AUTOFILTER_RULETYPE_DYNAMIC_FEBRUARY 'M2'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_3 'M3'
AUTOFILTER_RULETYPE_DYNAMIC_MARCH 'M3'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_4 'M4'
AUTOFILTER_RULETYPE_DYNAMIC_APRIL 'M4'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_5 'M5'
AUTOFILTER_RULETYPE_DYNAMIC_MAY 'M5'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_6 'M6'
AUTOFILTER_RULETYPE_DYNAMIC_JUNE 'M6'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_7 'M7'
AUTOFILTER_RULETYPE_DYNAMIC_JULY 'M7'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_8 'M8'
AUTOFILTER_RULETYPE_DYNAMIC_AUGUST 'M8'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_9 'M9'
AUTOFILTER_RULETYPE_DYNAMIC_SEPTEMBER 'M9'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_10 'M10'
AUTOFILTER_RULETYPE_DYNAMIC_OCTOBER 'M10'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_11 'M11'
AUTOFILTER_RULETYPE_DYNAMIC_NOVEMBER 'M11'
AUTOFILTER_RULETYPE_DYNAMIC_MONTH_12 'M12'
AUTOFILTER_RULETYPE_DYNAMIC_DECEMBER 'M12'
AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_1 'Q1'
AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_2 'Q2'
AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_3 'Q3'
AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_4 'Q4'
AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE '高于平均水平'
AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE '低于平均值'

我们一次只能将一个动态过滤器规则应用于一列。

十大筛选器

前十个过滤器与动态过滤器相似，因为它们基于单元格中实际数据值的汇总。但是，与只能选择一个选项的“动态过滤器”不同，“十大过滤器”使您可以根据多个条件进行选择：

04-05-custom-topten-1.png

04-05-custom-topten-2.png

您可以确定要使用最高（最高）值还是最低（最低）值。可以确定要在过滤器中选择多少个值。可以确定是百分比还是多个项目。

与动态过滤器一样，一次只能将一个前十名过滤器应用于一列。

我们从指定过滤器类型开始，这次是DYNAMICFILTER。

$columnFilter->setFilterType(
    \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER
);

然后我们创建规则：

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT,
        5,
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_TOPTENFILTER
    );

这将过滤列中前5％的值。

要指定最低值（底部2个值），我们将指定以下规则：

$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE,
        5,
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM
    )
    ->setRuleType(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_TOPTENFILTER
    );

TopTen过滤器的顶部/底部值/百分比的选项值均在\PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule该类中定义 ，包括：

运算符常量值
AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE 'byValue'
AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT 'byPercent'

和

运算符常量值
AUTOFILTER_COLUMN_RULE_TOPTEN_TOP '最佳'
AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM '底部'

执行自动筛选

在MS Excel中应用自动过滤器时，它将根据所选条件为自动过滤器区域的每一行设置行隐藏/可见标志，以便仅显示与过滤器条件匹配的那些行。

当您设置或更改过滤器表达式时，PhpSpreadsheet不会自动执行等效功能，而仅在文件保存时。

应用过滤器

如果希望从脚本中执行过滤器，则需要手动执行。您可以使用autofilters showHideRows() 方法执行此操作。

$autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter();
$autoFilter->showHideRows();

这会将与过滤条件匹配的所有行设置为可见，同时将其他所有行隐藏在自动过滤器区域内。

显示过滤的行

只需循环遍历自动过滤器区域中的行，无论是否符合过滤条件，仍将访问任何行。要有选择地仅访问过滤的行，您需要测试每行的可见性设置。

foreach ($spreadsheet->getActiveSheet()->getRowIterator() as $row) {
    if ($spreadsheet->getActiveSheet()
        ->getRowDimension($row->getRowIndex())->getVisible()) {
        echo '    Row number - ' , $row->getRowIndex() , ' ';
        echo $spreadsheet->getActiveSheet()
            ->getCell(
                'C'.$row->getRowIndex()
            )
            ->getValue(), ' ';
        echo $spreadsheet->getActiveSheet()
            ->getCell(
                'D'.$row->getRowIndex()
            )->getFormattedValue(), ' ';
        echo PHP_EOL;
    }
}

自动筛选排序

在MS Excel中，自动筛选还允许对行进行排序。PhpSpreadsheet 不支持此功能。

posted @ 2022-06-29 11:57  独孤败天大神  阅读(245)  评论(0)  编辑  收藏  举报