PHPSpreadsheet学习记录——recipes(1)

杜苏燕

2023-12-01

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前言

关于设置页面的样式，调整页面风格。

`提示：以下是本篇文章正文内容，下面案例可供参考`

一、设置电子表格的元数据和活动工作表

设置元数据

PhpSpreadsheet 允许一种简单的方法来设置电子表格的元数据，使用文档属性访问器。电子表格元数据可用于在文件存储库或文档中查找特定文档管理体系。例如，Microsoft Sharepoint 使用文档元数据以在其文档列表中搜索特定文档。

$spreadsheet->getProperties()
    ->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Mark Baker")
    ->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");

$spreadsheet->getProperties()
    ->setCustomProperty('Editor', 'Mark Baker')
    ->setCustomProperty('Version', 1.17)
    ->setCustomProperty('Tested', true)
    ->setCustomProperty('Test Date', '2021-03-17', Properties::PROPERTY_TYPE_DATE);

设置活动工作表

电子表格由（很少）任何工作表组成，一个或多个工作表。如果您有 1 个或多个工作表，则这些工作表中的一个（并且只有一个）一次可以处于“活动”状态（查看或更新），但始终存在“活动”工作表（除非您明确删除电子表格中的所有工作表）。

通过名字设置“活动”工作表

$activeWorksheet = $spreadsheet->setActiveSheetIndexByName('Sheet1')

通过索引号设置“活动”工作表

$activeWorksheet = $spreadsheet->setActiveSheetIndex(0);

创建新的sheet和获取“活动”sheet

// Create a Spreadsheet, with Worksheet Sheet1, which is the Active Worksheet
$spreadsheet = new Spreadsheet();
// Assign the Active Worksheet (Sheet1) to $worksheet1
$worksheet1 = $spreadsheet->getActiveSheet();
// Create a new Worksheet (Sheet2) and make that the Active Worksheet
$worksheet2 = $spreadsheet->createSheet();

$worksheet1->setCellValue('A1', 'I am a cell on Sheet1');
$worksheet2->setCellValue('A1', 'I am a cell on Sheet2');

二、将日期和时间写入单元格

以下有三种方式：

MySQL-like timestamp ‘2008-12-31’ or date string

\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

$spreadsheet->getActiveSheet()
    ->setCellValue('D1', '2008-12-31');

$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

PHP-time (Unix time)

$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$spreadsheet->getActiveSheet()
    ->setCellValue('D1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

Excel-date/time

$spreadsheet->getActiveSheet()->setCellValue('D1', 39813)
$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

三、公式写入和区域设置

小数点分隔符是（句点）.
函数参数分隔符为（逗号）,
矩阵行分隔符为（分号）;
必须使用英文函数名称

3.1公式的写入

以下代码行将公式写入单元格 B8。请注意，公式必须以=开头，以使 PhpSpreadsheet 将其识别为公式。=IF(C4>500,“profit”,“loss”)

$spreadsheet->getActiveSheet()->setCellValue('B8','=IF(C4>500,"profit","loss")');

如果想要使用字符串的形式，应该使用 setCellValueExplicit() 方法

$spreadsheet->getActiveSheet()
    ->setCellValueExplicit(
        'B8',
        '=IF(C4>500,"profit","loss")',
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
    );

读取单元格的公式内容：

$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();

获取单元格公式计算结果：

$value = $spreadsheet->getActiveSheet()->getCell('B8')->getCalculatedValue();

3.2公式区域设置

Phpspreadsheet当中已经包含了一些本地化元素，以设置为俄语为例：

$locale = 'ru';
$validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale);
if (!$validLocale) {
    echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";
}

如果俄语文件不可用，则该方法 setLocale() 将返回错误，并且将始终使用英语设置。

设置区域设置后，您可以从其翻译公式内部英语编码。

$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();
$translatedFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->translateFormulaToLocale($formula);

您还可以使用函数名称和参数创建公式适用于定义的区域设置的分隔符;然后将其翻译成设置单元格值之前的英语：

$formula = '=ДНЕЙ360(ДАТА(2010;2;5);ДАТА(2010;12;31);ИСТИНА)';
$internalFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->translateFormulaToEnglish($formula);
$spreadsheet->getActiveSheet()->setCellValue('B8',$internalFormula);

Language		Locale Code
Czech	Ceština	cs
Danish	Dansk	da
German	Deutsch	de
Spanish	Español	es
Finnish	Suomi	fi
French	Français	fr
Hungarian	Magyar	hu
Italian	Italiano	it
Dutch	Nederlands	nl
Norwegian	Norsk Bokmål	nb
Polish	Jezyk polski	pl
Portuguese	Português	pt
Brazilian Portuguese	Português Brasileiro	pt_br
Russian	русский язык	ru
Swedish	Svenska	sv
Turkish	Türkçe	tr

四、设置单元格

在单元格中写入换行符

在Microsoft Office Excel中，您可以通过点击在单元格中获得换行符 ALT+“输入”。当您这样做时，它会自动打开“自动换行” 单元格。

以下是在PhpSpreadsheet中实现此目的的方法：

$spreadsheet->getActiveSheet()->getCell('A1')->setValue("hello\nworld");
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

设置单元格数据类型

您可以使用单元格的 setValueExplicit 方法，或 setCellValueExplicit 方法。下面是一个示例：

$spreadsheet->getActiveSheet()->getCell('A1')
    ->setValueExplicit(
        '25',
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC
    );

将单元格更改为可点击的网址

您可以通过设置单元格的超链接属性使单元格成为可单击的 URL：

$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');

如果要创建指向另一个工作表/单元格的超链接，请使用以下代码：

$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!A1");

五、风格

5.1设置单元格格式

单元格可以使用字体，边框，填充，…样式信息。例如，可以将单元格的前景色设置为红色，对齐，右侧，边框为黑色和粗边框样式。让我们在单元格 B2 上设置：

$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getLeft()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getBorders()->getRight()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$spreadsheet->getActiveSheet()->getStyle('B2')
    ->getFill()->getStartColor()->setARGB('FFFF0000');

getStyle() 还接受单元格区域作为参数。例如，您可以在一系列单元格上设置红色背景颜色

$spreadsheet->getActiveSheet()->getStyle('B3:B7')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFFF0000');

建议一次设置多个单元格的样式，例如使用 getStyle（'A1：M500'），而不是再单独圈。与循环遍历单元格和样式相比，这要快得多。

还有一种设置样式的替代方法。以下代码将单元格的样式设置为字体粗体、右对齐、上边框细和渐变填充：

$styleArray = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
    ],
    'borders' => [
        'top' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startColor' => [
            'argb' => 'FFA0A0A0',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];

$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);

将样式应用在多个单元格：

$spreadsheet->getActiveSheet()->getStyle('B3:B7')->applyFromArray($styleArray);

您可以执行相反的功能，将样式导出为数组，如下：

$styleArray = $spreadsheet->getActiveSheet()->getStyle('A3')->exportArray();

5.2数字格式

您经常希望在Excel中设置数字格式。例如，您可能需要一个千位分隔符加上小数点后固定的小数位数分隔符。或者，也许您希望某些数字以零填充。

在Microsoft Office Excel中，您可能熟悉选择数字格式从“设置单元格格式”对话框。这里有一些预定义的可用的数字格式，包括一些日期格式。对话框是设计方式使你不必与底层 RAW 交互数字格式代码，除非您需要自定义数字格式。

在 PhpSpreadsheet 中，您还可以应用各种预定义的数字格式。例：

$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

5.3对齐和自动换行

让我们将单元格 A1：D4 的垂直对齐方式设置为顶部

$spreadsheet->getActiveSheet()->getStyle('A1:D4')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

以下是实现自动换行的方法：

$spreadsheet->getActiveSheet()->getStyle('A1:D4')
    ->getAlignment()->setWrapText(true);

5.4设置工作簿样式

可以设置工作簿的默认样式。让我们设置默认字体为 Arial 大小 8

$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
$spreadsheet->getDefaultStyle()->getFont()->setSize(8);

Excel 还提供“主题字体”，主要（标题）和次要（正文）文本具有单独的字体名称。PhpSpreadsheet 将使用 Excel 2007 默认值（Cambria）表示主要（默认为 Excel 2013+ 中的 Calibri Light）;次要的 PhpSpreadsheet 默认为 Calibri，由 Excel 2007+ 使用。要将默认字体名称与次要字体名称对齐：

$spreadsheet->getTheme()
    ->setThemeFontName('custom')
    ->setMinorFontValues('Arial', 'Arial', 'Arial', []);
$spreadsheet->getDefaultStyle()->getFont()->setScheme('minor');

5.5设置单元格边框样式

以下是在单元格B2：G8.周围应用粗红色边框轮廓的方法

$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];

$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。