2019年10月11日09:32:33
官方使用文档 https://phpspreadsheet.readthedocs.io/en/stable/topics/accessing-cells/
api文档 https://phpoffice.github.io/PhpSpreadsheet/master/PhpOffice.html
翻译的是使用文档,机翻+校验,因为现在单个文档内容放的东西有限分成几篇文章
只翻译标题的主要部分
phpspreadsheet 中文文档(一) 访问单元格 未校验
phpspreadsheet 中文文档(二) 结构+自动筛选 未校验
phpspreadsheet 中文文档(三) 计算引擎 未校验
phpspreadsheet 中文文档(四) 创建电子表格+档案格式 未校验
phpspreadsheet 中文文档(五)节约内存+PHPExcel迁移 未校验
phpspreadsheet 中文文档(六)读写文件+读取文件 未校验
phpspreadsheet 中文文档(七)技巧和诀窍 未校验
phpspreadsheet 中文文档(八)读写文件+读取文件 未校验
文档部分主要的菜单↓↓
Accessing cells 访问单元格
Setting a cell value by coordinate 通过坐标设置单元格值
Excel DataTypes Excel 数据类型
Setting a range of cells from an array 设置数组中的单元格范围
Retrieving a cell value by coordinate 通过坐标检索单元格值
Setting a cell value by column and row 按列和行设置单元格值
Retrieving a cell value by column and row 按列和行检索单元格值
Retrieving a range of cell values to an array 检索单元格值范围到数组
Looping through cells 遍历单元格
Using value binders to facilitate data entry 使用值绑定器简化数据输入
Architecture 结构
Architecture 结构
Schematical 示意图
AutoLoader 自动加载器
Spreadsheet in memory 表格在内存中使用
Readers and writers 读取器和写入器
Fluent interfaces 优美的接口
AutoFilter Reference 自动筛选引用
Introduction 简介
Setting an AutoFilter area on a worksheet 在工作表上设置自动筛选区域
Autofilter Expressions 自动筛选表达式
Executing an AutoFilter 执行自动筛选
AutoFilter Sorting 自动筛选排序
Calculation Engine 计算引擎
Using the PhpSpreadsheet calculation engine 使用phpspreadsheet计算引擎
Calculation Cache 计算缓存
Known limitations 已知限制
Handling Date and Time Values 处理日期和时间值
Function Reference 函数引用
Creating a spreadsheet 创建电子表格
The Spreadsheet class Spreadsheet类
Clearing a Workbook from memory 从内存中清除工作簿
File Formats 文件格式
Xls
Xml
Xlsx
Ods
Slk
Gnumeric
Csv
Html
Memory saving 节约内存
Beware of TTL 注意TTL使用
Common use cases 常见用例
Migration from PHPExcel 从PHPExcel迁移
Automated tool 自动化工具
Manual changes 需要手动更改
Reading and writing to file 读写文件
\PhpOffice\PhpSpreadsheet\IOFactory IOFactory类
Excel 2007 (SpreadsheetML) file format Excel 2007 文件格式
Excel 5 (BIFF) file format Excel 5 文件格式
Excel 2003 XML file format Excel 2003 文件格式
Symbolic LinK (SYLK) 符号链接(Sylk)
Open/Libre Office (.ods)
CSV (Comma Separated Values)
HTML
PDF
Generating Excel files from templates (read, modify, write) 从模板生成Excel文件(读取,修改,写入)
Generating Excel files from HTML content 从HTML内容生成Excel文件
Reading Files 读取文件
Security 安全问题
Loading a Spreadsheet File 加载电子表格文件
Creating a Reader and Loading a Spreadsheet File 创建阅读器并加载电子表格文件
Spreadsheet Reader Options 电子表格阅读器选项
Error Handling 错误处理
Helper Methods 辅助方法
Recipes 技巧和诀窍demo
Setting a spreadsheet's metadata 设置电子表格的元数据
Setting a spreadsheet's active sheet 设置电子表格的活动工作表
Write a date or time into a cell 将日期或时间写入单元格
Write a formula into a cell 将公式写入单元格
Locale Settings for Formulae 公式的区域设置
Write a newline character "\n" in a cell (ALT+"Enter") 在单元格中写入换行符“ \ n”
Explicitly set a cell's datatype 明确设置单元格的数据类型
Change a cell into a clickable URL 将单元格更改为可点击的网址
Setting Printer Options for Excel files 设置Excel文件的打印机选项
Styles 样式设置
Conditional formatting a cell 有条件地格式化单元格
Add a comment to a cell 在单元格中添加评论
Apply autofilter to a range of cells 将自动过滤器应用于指定范围的单元格
Setting security on a spreadsheet 在电子表格上设置安全性
Setting data validation on a cell 在单元上设置数据验证
Setting a column's width 设置列的宽度
Show/hide a column 显示/隐藏列
Group/outline a column 分组/概述一列
Setting a row's height 设置行的高度
Show/hide a row 显示/隐藏行
Group/outline a row 分组/概述行
Merge/unmerge cells 合并/取消合并单元格
Inserting rows/columns 插入行/列
Add a drawing to a worksheet 将工程图添加到工作表
Reading Images from a worksheet 从工作表中读取图像
Add rich text to a cell 向单元格添加富文本
Define a named range 定义一个命名范围
Redirect output to a client's web browser 将输出重定向到客户端的Web浏览器
Setting the default column width 设置默认列宽
Setting the default row height 设置默认行高
Add a GD drawing to a worksheet 将GD工程图添加到工作表
Setting worksheet zoom level 设置工作表缩放级别
Sheet tab color 工作表标签颜色
Creating worksheets in a workbook 在工作簿中创建工作表
Hidden worksheets (Sheet states) 隐藏的工作表(Sheet状态)
Right-to-left worksheet 从右到左的工作表
Configuration Settings 配置设定
Cell collection caching 单元格集合缓存
Language/Locale 语言/地区
Worksheets 工作表
Adding a new Worksheet 添加一个新的工作表
Copying Worksheets 复制工作表
Removing a Worksheet 删除工作表
一点个人经验:
//第一行冻结
$sheet->freezePaneByColumnAndRow(1, 1);
$sheet->freezePaneByColumnAndRow(1, 2);
//吧列转换成A B C AA等列的形式
Coordinate::stringFromColumnIndex('9')
专用的类来操纵坐标
曾经存在于PHPExcel_Cell
其中的坐标处理方法被提取到一个专用的新类中\PhpOffice\PhpSpreadsheet\Cell\Coordinate
。方法是:
absoluteCoordinate()
absoluteReference()
buildRange()
columnIndexFromString()
coordinateFromString()
extractAllCellReferencesInRange()
getRangeBoundaries()
mergeRangesInCollection()
rangeBoundaries()
rangeDimension()
splitRange()
stringFromColumnIndex()
//给某一个单元格添加注释
$sheet->getComment(Coordinate::stringFromColumnIndex('9') . $Row)->getText()->createTextRun('注释');
在一系列单元格上设置自动过滤器。
$spreadsheet->getActiveSheet()->setAutoFilter('A1:E20');
如果要将整个工作表设置为自动筛选区域
$spreadsheet->getActiveSheet()->setAutoFilter(
$spreadsheet->getActiveSheet()
->calculateWorksheetDimension()
);
要将过滤器表达式应用于autoFilter范围,首先需要确定要将此过滤器应用于哪一列。
$autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter();
$columnFilter = $autoFilter->getColumn('C');
\vendor\phpoffice\phpspreadsheet\samples\index.php 这里面有很多的demo
//给表格设置样式
$styleArray = self::getStyleArray($Row);
$sheet->getStyle('B2:G8')->applyFromArray($styleArray);
// 设置富文本,但是支持的html的标签不多
$html5 = '<strong>bold</strong>, <em>italic</em>, <strong><em>bold+italic</em></strong>';
$wizard = new HtmlHelper();
$richText = $wizard->toRichTextObject($html1);
$spreadsheet->getActiveSheet()->setCellValue('A1', $richText);
注意这里对闭合的标签支持会好一些,尽量使用闭合标签,解析效果会好一点
是吧html标签解析成xml转义进excel的
//设置富文本备注的宽度
$wizard = new Html();
$richText = $wizard->toRichTextObject($html1);
$sheet->getComment(Coordinate::stringFromColumnIndex('10') . $Row)->setWidth('800pt')->getText()->createTextRun($richText);
// Create new Spreadsheet object
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
$helper->log('Create new Spreadsheet object');
$spreadsheet = new Spreadsheet();
// 设置文档相关属性
$helper->log('Set document properties');
$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');
// 创建一张表,表示销售数据
$helper->log('Add some data');
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setCellValue('B1', 'Invoice');
$spreadsheet->getActiveSheet()->setCellValue('D1', Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
$spreadsheet->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_XLSX15);
$spreadsheet->getActiveSheet()->setCellValue('E1', '#12566');
$spreadsheet->getActiveSheet()->setCellValue('A3', 'Product Id');
$spreadsheet->getActiveSheet()->setCellValue('B3', 'Description');
$spreadsheet->getActiveSheet()->setCellValue('C3', 'Price');
$spreadsheet->getActiveSheet()->setCellValue('D3', 'Amount');
$spreadsheet->getActiveSheet()->setCellValue('E3', 'Total');
$spreadsheet->getActiveSheet()->setCellValue('A4', '1001');
$spreadsheet->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
$spreadsheet->getActiveSheet()->setCellValue('C4', '20');
$spreadsheet->getActiveSheet()->setCellValue('D4', '1');
$spreadsheet->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');
$spreadsheet->getActiveSheet()->setCellValue('A5', '1012');
$spreadsheet->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
$spreadsheet->getActiveSheet()->setCellValue('C5', '22');
$spreadsheet->getActiveSheet()->setCellValue('D5', '2');
$spreadsheet->getActiveSheet()->setCellValue('E5', '=IF(D5<>"",C5*D5,"")');
$spreadsheet->getActiveSheet()->setCellValue('E6', '=IF(D6<>"",C6*D6,"")');
$spreadsheet->getActiveSheet()->setCellValue('E7', '=IF(D7<>"",C7*D7,"")');
$spreadsheet->getActiveSheet()->setCellValue('E8', '=IF(D8<>"",C8*D8,"")');
$spreadsheet->getActiveSheet()->setCellValue('E9', '=IF(D9<>"",C9*D9,"")');
$spreadsheet->getActiveSheet()->setCellValue('D11', 'Total excl.:');
$spreadsheet->getActiveSheet()->setCellValue('E11', '=SUM(E4:E9)');
$spreadsheet->getActiveSheet()->setCellValue('D12', 'VAT:');
$spreadsheet->getActiveSheet()->setCellValue('E12', '=E11*0.21');
$spreadsheet->getActiveSheet()->setCellValue('D13', 'Total incl.:');
$spreadsheet->getActiveSheet()->setCellValue('E13', '=E11+E12');
// 添加备注
$helper->log('Add comments');
$spreadsheet->getActiveSheet()->getComment('E11')->setAuthor('PhpSpreadsheet');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');
$spreadsheet->getActiveSheet()->getComment('E12')->setAuthor('PhpSpreadsheet');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');
$spreadsheet->getActiveSheet()->getComment('E13')->setAuthor('PhpSpreadsheet');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
$spreadsheet->getActiveSheet()->getComment('E13')->setWidth('100pt');
$spreadsheet->getActiveSheet()->getComment('E13')->setHeight('100pt');
$spreadsheet->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
$spreadsheet->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');
// 添加富文本字符串
$helper->log('Add rich-text string');
$richText = new RichText();
$richText->createText('This invoice is ');
$payable = $richText->createTextRun('payable within thirty days after the end of the month');
$payable->getFont()->setBold(true);
$payable->getFont()->setItalic(true);
$payable->getFont()->setColor(new Color(Color::COLOR_DARKGREEN));
$richText->createText(', unless specified otherwise on the invoice.');
$spreadsheet->getActiveSheet()->getCell('A18')->setValue($richText);
// 合并单元格
$helper->log('Merge cells');
$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
$spreadsheet->getActiveSheet()->mergeCells('A28:B28'); // Just to test...
$spreadsheet->getActiveSheet()->unmergeCells('A28:B28'); // Just to test...
// 保护单元格不被修改
$helper->log('Protect cells');
$spreadsheet->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$spreadsheet->getActiveSheet()->protectCells('A3:E13', 'PhpSpreadsheet');
// 设置单元格编号格式
$helper->log('Set cell number formats');
$spreadsheet->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
// 设置列宽
$helper->log('Set column widths');
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
// 设置字体
$helper->log('Set fonts');
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
// 设置对其方式
$helper->log('Set alignments');
$spreadsheet->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_JUSTIFY);
$spreadsheet->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('B5')->getAlignment()->setShrinkToFit(true);
// 在列周围设置细的黑色边框轮廓
$helper->log('Set thin black border outline around column');
$styleThinBlackBorderOutline = [
'borders' => [
'outline' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
];
$spreadsheet->getActiveSheet()->getStyle('A4:E10')->applyFromArray($styleThinBlackBorderOutline);
// 在“总计”周围设置粗的棕色边框轮廓
$helper->log('Set thick brown border outline around Total');
$styleThickBrownBorderOutline = [
'borders' => [
'outline' => [
'borderStyle' => Border::BORDER_THICK,
'color' => ['argb' => 'FF993300'],
],
],
];
$spreadsheet->getActiveSheet()->getStyle('D13:E13')->applyFromArray($styleThickBrownBorderOutline);
// 设置填充
$helper->log('Set fills');
$spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID);
$spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFill()->getStartColor()->setARGB('FF808080');
// 使用替代方法设置标题行的样式
$helper->log('Set style for header row using alternative method');
$spreadsheet->getActiveSheet()->getStyle('A3:E3')->applyFromArray(
[
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_RIGHT,
],
'borders' => [
'top' => [
'borderStyle' => Border::BORDER_THIN,
],
],
'fill' => [
'fillType' => Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startColor' => [
'argb' => 'FFA0A0A0',
],
'endColor' => [
'argb' => 'FFFFFFFF',
],
],
]
);
$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray(
[
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_LEFT,
],
'borders' => [
'left' => [
'borderStyle' => Border::BORDER_THIN,
],
],
]
);
$spreadsheet->getActiveSheet()->getStyle('B3')->applyFromArray(
[
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_LEFT,
],
]
);
$spreadsheet->getActiveSheet()->getStyle('E3')->applyFromArray(
[
'borders' => [
'right' => [
'borderStyle' => Border::BORDER_THIN,
],
],
]
);
// 取消单元格保护
$helper->log('Unprotect a cell');
$spreadsheet->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
// 在工作表中添加超链接
$helper->log('Add a hyperlink to an external website');
$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$spreadsheet->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$helper->log('Add a hyperlink to another cell on a different worksheet within the workbook');
$spreadsheet->getActiveSheet()->setCellValue('E27', 'Terms and conditions');
$spreadsheet->getActiveSheet()->getCell('E27')->getHyperlink()->setUrl("sheet://'Terms and conditions'!A1");
$spreadsheet->getActiveSheet()->getCell('E27')->getHyperlink()->setTooltip('Review terms and conditions');
$spreadsheet->getActiveSheet()->getStyle('E27')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
// 将工程图添加到工作表
$helper->log('Add a drawing to the worksheet');
$drawing = new Drawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath(__DIR__ . '/../images/officelogo.jpg');
$drawing->setHeight(36);
$drawing->setWorksheet($spreadsheet->getActiveSheet());
// 将工程图添加到工作表
$helper->log('Add a drawing to the worksheet');
$drawing = new Drawing();
$drawing->setName('Paid');
$drawing->setDescription('Paid');
$drawing->setPath(__DIR__ . '/../images/paid.png');
$drawing->setCoordinates('B15');
$drawing->setOffsetX(110);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);
$drawing->setWorksheet($spreadsheet->getActiveSheet());
// 将工程图添加到工作表
$helper->log('Add a drawing to the worksheet');
$drawing = new Drawing();
$drawing->setName('PhpSpreadsheet logo');
$drawing->setDescription('PhpSpreadsheet logo');
$drawing->setPath(__DIR__ . '/../images/PhpSpreadsheet_logo.png');
$drawing->setHeight(36);
$drawing->setCoordinates('D24');
$drawing->setOffsetX(10);
$drawing->setWorksheet($spreadsheet->getActiveSheet());
// 尝试插入和删除行和列
$helper->log('Play around with inserting and removing rows and columns');
$spreadsheet->getActiveSheet()->insertNewRowBefore(6, 10);
$spreadsheet->getActiveSheet()->removeRow(6, 10);
$spreadsheet->getActiveSheet()->insertNewColumnBefore('E', 5);
$spreadsheet->getActiveSheet()->removeColumn('E', 5);
// 设置页眉和页脚。 当不使用奇数/偶数的不同标头时,将采用奇数标头。
$helper->log('Set header/footer');
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');
// 设置页面方向和大小
$helper->log('Set page orientation and size');
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_PORTRAIT);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
// 重命名第一个工作表
$helper->log('Rename first worksheet');
$spreadsheet->getActiveSheet()->setTitle('Invoice');
// 在默认工作表之后创建一个新的工作表
$helper->log('Create a second Worksheet object');
$spreadsheet->createSheet();
// 看不懂什么语言
$sLloremIpsum = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Vivamus eget ante. Sed cursus nunc semper tortor. Aliquam luctus purus non elit. Fusce vel elit commodo sapien dignissim dignissim. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Curabitur accumsan magna sed massa. Nullam bibendum quam ac ipsum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Proin augue. Praesent malesuada justo sed orci. Pellentesque lacus ligula, sodales quis, ultricies a, ultricies vitae, elit. Sed luctus consectetuer dolor. Vivamus vel sem ut nisi sodales accumsan. Nunc et felis. Suspendisse semper viverra odio. Morbi at odio. Integer a orci a purus venenatis molestie. Nam mattis. Praesent rhoncus, nisi vel mattis auctor, neque nisi faucibus sem, non dapibus elit pede ac nisl. Cras turpis.';
// 将一些数据添加到第二张表中,类似于一些不同的数据类型
$helper->log('Add some data');
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setCellValue('A1', 'Terms and conditions');
$spreadsheet->getActiveSheet()->setCellValue('A3', $sLloremIpsum);
$spreadsheet->getActiveSheet()->setCellValue('A4', $sLloremIpsum);
$spreadsheet->getActiveSheet()->setCellValue('A5', $sLloremIpsum);
$spreadsheet->getActiveSheet()->setCellValue('A6', $sLloremIpsum);
// 设置工作表标签的颜色
$helper->log('Set the worksheet tab color');
$spreadsheet->getActiveSheet()->getTabColor()->setARGB('FF0094FF');
// 设置对其方式
$helper->log('Set alignments');
$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);
// 设置列宽
$helper->log('Set column widths');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(80);
// 设置字体
$helper->log('Set fonts');
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);
$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getFont()->setSize(8);
// 将图形添加到工作表
$helper->log('Add a drawing to the worksheet');
$drawing = new Drawing();
$drawing->setName('Terms and conditions');
$drawing->setDescription('Terms and conditions');
$drawing->setPath(__DIR__ . '/../images/termsconditions.jpg');
$drawing->setCoordinates('B14');
$drawing->setWorksheet($spreadsheet->getActiveSheet());
// 设置页面方向和大小
$helper->log('Set page orientation and size');
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
// 重命名第二个工作表
$helper->log('Rename second worksheet');
$spreadsheet->getActiveSheet()->setTitle('Terms and conditions');
// 将活动工作表索引设置为第一张工作表,因此Excel将其打开为第一张工作表
$spreadsheet->setActiveSheetIndex(0);
return $spreadsheet;