**`PhpSpreadsheet`** is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.
[[git仓库]](https://github.com/PHPOffice/PhpSpreadsheet)
[[文档]](https://phpspreadsheet.readthedocs.io/en/latest/)
----
目录：
[TOC]
----
## Use composer to install PhpSpreadsheet into your project:
composer require phpoffice/phpspreadsheet
~~~php
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$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");
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("05featuredemo.xlsx");
$reader->setReadDataOnly(true);
$reader->load("05featuredemo.xlsx");
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
$spreadsheet = $reader->load("05featuredemo.xlsx")
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");
$writer->setPreCalculateFormulas(false);
$writer->save("05featuredemo.xlsx");
// Create a new worksheet called "My Data"
$myWorkSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'My Data');
// Attach the "My Data" worksheet as the first worksheet in the Spreadsheet object
$spreadsheet->addSheet($myWorkSheet, 0);
//in a workbook, add an new worksheet
$clonedWorksheet = clone $spreadsheet->getSheetByName('Worksheet 1');
$clonedWorksheet->setTitle('Copy of Worksheet 1');
$spreadsheet->addSheet($clonedWorksheet);
//add an new worksheet from another workbook
$clonedWorksheet = clone $spreadsheet1->getSheetByName('Worksheet 1');
$spreadsheet->addExternalSheet($clonedWorksheet);
//delete worksheet
$sheetIndex = $spreadsheet->getIndex(
$spreadsheet->getSheetByName('Worksheet 1')
);
$spreadsheet->removeSheetByIndex($sheetIndex);
By default, PhpSpreadsheet holds all cell objects in memory, but you can specify alternatives to reduce memory consumption at the cost of speed. Read more about memory saving.
//Setting a spreadsheet's active sheet
$spreadsheet->setActiveSheetIndex(0);
//You can also set the active sheet by its name/title
$spreadsheet->setActiveSheetIndexByName('DataSheet');
//Setting the default style of a workbook
$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
$spreadsheet->getDefaultStyle()->getFont()->setSize(8);
~~~
## Redirect output to a client's web browser
1. Create your PhpSpreadsheet spreadsheet.
2. Output HTTP headers for the type of document you wish to output.
3. Use the \\PhpOffice\\PhpSpreadsheet\\Writer\\\* of your choice, and save to 'php://output'.
'\\PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx' uses temporary storage when writing to 'php://output'. By default, temporary files are stored in the script's working directory. When there is no access, it falls back to the operating system's temporary files location.
When confidentiality of your document is needed, it is recommended not to use 'php://output'.
~~~php
// redirect output to client browser, \*.xlsx
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx"');
header('Cache-Control: max-age=0');
$writer = \\PhpOffice\\PhpSpreadsheet\\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
// redirect output to client browser, \*.xls
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myfile.xls"');
header('Cache-Control: max-age=0');
$writer = \\PhpOffice\\PhpSpreadsheet\\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
~~~
## Creating worksheets in a workbook
~~~php
$worksheet1 = $spreadsheet->createSheet();
$worksheet1->setTitle('Another sheet');
$worksheet1->getTabColor()->setRGB('FF0000');
~~~
The standard PhpSpreadsheet package also provides an "advanced value binder" that handles a number of more complex conversions, such as
converting strings with a fractional format like "3/4" to a number value (0.75 in this case) and
setting an appropriate "fraction" number format mask.
Similarly, strings like "5%" will be converted to a value of 0.05, and
a percentage number format mask applied, and
strings containing values that look like dates will be converted to Excel serialized datetimestamp values, and a corresponding mask applied.
This is particularly useful when loading data from csv files, or setting cell values from a database.
## Setting a range of cells from an array(a 2-d array from the database)
~~~php
$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, // The data to set
NULL, // Array values with this value will not be set
'C3' // Top left coordinate of the worksheet range where
// we want to set these values (default is A1)
);
~~~
//1-d array changed to a collumn
$rowArray = \['Value1', 'Value2', 'Value3', 'Value4'\];
$columnArray = array\_chunk($rowArray, 1);
$spreadsheet->getActiveSheet()
->fromArray(
$columnArray, // The data to set
NULL, // Array values with this value will not be set
'C3' // Top left coordinate of the worksheet range where
// we want to set these values (default is A1)
//retrieve the raw, unformatted value contained in the cell.
$cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue();
## Looping through cells,
### using iterators
using iterators to read all the values in a worksheet and display them in a table.
~~~php
$reader = \\PhpOffice\\PhpSpreadsheet\\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("test.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
echo '' . PHP\_EOL;
foreach ($worksheet->getRowIterator() as $row) {
echo '' . PHP\_EOL;
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
// even if a cell value is not set.
// By default, only cells that have a value
// set will be iterated.
foreach ($cellIterator as $cell) {
echo '' .
$cell->getValue() .
'' . PHP\_EOL;
}
echo ''
~~~
Note that we have set the cell iterator's `setIterateOnlyExistingCells()` to FALSE. This makes the iterator loop all cells within the worksheet range, even if they have not been set, for the cell iterator will return a `null` as the cell value if it is not set in the worksheet.
### using indexes
In PhpSpreadsheet column index and row index are 1-based. That means 'A1' ~ \[1, 1\].
~~~php
$reader = \\PhpOffice\\PhpSpreadsheet\\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("test.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = \\PhpOffice\\PhpSpreadsheet\\Cell\\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
echo '' . "\\n";
for ($row = 1; $row <= $highestRow; ++$row) {
echo '' . PHP\_EOL;
for ($col = 1; $col <= $highestColumnIndex; ++$col) {
$value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
echo '' . $value . '' . PHP\_EOL;
}
echo '' . PHP\_EOL;
}
echo '' . PHP\_EOL;
~~~
111
- WebAPP
- Linux Command
- 入门
- 处理文件
- 查找文件单词
- 环境
- 联网
- Linux
- Linux目录配置标准：FHS
- Linux文件与目录管理
- Linux账号管理与ACL权限设置
- Linux系统资源查看
- 软件包管理
- Bash
- Daemon/Systemd
- ftp
- Apache
- MySQL
- Command
- Replication
- mysqld
- remote access
- remark
- 限制
- PHP
- String
- Array
- Function
- Class
- File
- JAVA
- Protocals
- http
- mqtt
- IDE
- phpDesigner
- eclipse
- vscode
- Notepad++
- WebAPI
- Javasript
- DOM
- BOM
- Event
- Class
- Module
- Ajax
- Fetch
- Promise
- async/await
- Statements and declarations
- Function
- Framwork
- jQurey
- Types
- Promise
- BootStrap
- v4
- ThinkPHP5
- install
- 定时任务
- CodeIgniter
- React.js
- node.js
- npm
- npm-commands
- npm-folder
- package.json
- Docker and private modules
- module
- webpack.js
- install
- configuration
- package.json
- entry
- modules
- plugins
- Code Splitting
- loaders
- libs
- API
- webpack-cli
- Vue.js
- install
- Compile
- VueAPI
- vuex
- vue-router
- vue-devtools
- vue-cli
- vue-loader
- VDOM
- vue-instance
- components
- template
- Single-File Components
- props
- data
- methods
- computed
- watch
- Event-handling
- Render Func
- remark
- 案例学习
- bootstrap-vue
- modal
- fontAwesome
- Hosting Font Awesome Yourself
- using with jquery
- using with Vue.js
- HTML
- CSS
- plugins
- Chart.js
- D3.js
- phpSpreadSheet
- Guzzle
- Cmder
- Git
- git命令
- git流程
- Postman
- Markdown
- Regular Expressions
- PowerDesigner
- 附录1-学习资源