Phpspreadsheet Development Notes

Keng
Installation
Simple example
Generate Files from templates
Freeing memory
Cell
Get English columns by index
Setting the value
Width setting
Direct Output Download
Automatically calculate column widths
function formula
Phpspreadsheet is a pure PHP class library that enables you to read and write in tabular format such as Excel, Libreoffic Calc, and so on.
https://phpspreadsheet.readthedocs.io/en/develop/

Keng

The column counts from 0, and the line starts at 1.
$sheet->setCellValueByColumnAndRow(0,1,‘特别说明‘);

Installation

composer require phpoffice/phpspreadsheet 版本号
By default, you will be prompted to find the library, composer, because there is no stable version, so you want to specify version 1.0.0beta

Depend on
The following software is required to develop using Phpspreadsheet:

• PHP version 5.6 or newer
• PHP extension Php_zip Enabled
• PHP extension Php_xml Enabled
• PHP extension PHP_GD2 enabled (if not compiled in)

Use Ziparchive to compress the save by default
Note Read and Write permissions

Simple example

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‘);

The default is saved to execute PHP root directory, take thinkphp as an example index.php D:\wwwroot\thinkphp\public , then the file is saved in this
Note: If you do not want to save to a file, you can pass in php://output or php://stdout direct output (such as HTML, Output Web page)

Generate Files from templates

Full-code write is too tired, you can use the template to modify, but for dynamic data, or to be generated by the code

//通过工厂模式创建内容$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(‘template.xlsx‘);$worksheet = $spreadsheet->getActiveSheet();$worksheet->getCell(‘A1‘)->setValue(‘John‘);$worksheet->getCell(‘A2‘)->setValue(‘Smith‘);//通过工厂模式来写内容$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, ‘Xls‘);$writer->save(‘write.xls‘);

Freeing memory

To prevent memory leaks, we recommend that you run out of manual cleanup

$spreadsheet->disconnectWorksheets();unset($spreadsheet);

Cell gets the English column according to the index

where a=0
Cell::stringFromColumnIndex($pColumn)

Setting the value

$worksheet->getCell(‘A1‘)->setValue(‘John‘);$sheet->setCellValue(‘A1‘, ‘Hello World !‘);$sheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, $value);

Width setting

$this->getColumnDimension($columnIndex)->setWidth($width);
can also make it adaptive (not reliable, recommended to set their own)
$sheet->calculateColumnWidths();

Direct Output Download

        header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘);//告诉浏览器输出07Excel文件//header(‘Content-Type:application/vnd.ms-excel‘);//告诉浏览器将要输出Excel03版本文件        header(‘Content-Disposition: attachment;filename="01simple.xlsx"‘);//告诉浏览器输出浏览器名称        header(‘Cache-Control: max-age=0‘);//禁止缓存        $writer = new Xlsx($spreadsheet);        $writer->save(‘php://output‘);

Automatically calculate column widths

  function autoFitColumnWidthToContent($sheet, $fromCol, $toCol) {        if (empty($toCol) ) {//not defined the last column, set it the max one            $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();        }        for($i = $fromCol; $i <= $toCol; $i++) {            $sheet->getColumnDimension($i)->setAutoSize(true);        }        $sheet->calculateColumnWidths();    }

function formula

https://phpspreadsheet.readthedocs.io/en/develop/references/function-list-by-name/
https://phpspreadsheet.readthedocs.io/en/develop/topics/calculation-engine/#function-reference

$worksheet->setCellValue(‘A12‘, ‘=DMIN(A4:E10,"Profit",A1:A2)‘);$retVal = $worksheet->getCell(‘A12‘)->getCalculatedValue();// $retVal = 225

Phpspreadsheet Development Notes