Php spreadsheet add sheet

Creating a spreadsheet

The Spreadsheet class is the core of PhpSpreadsheet. It contains references to the contained worksheets, document security settings and document meta data.

To simplify the PhpSpreadsheet concept: the Spreadsheet class represents your workbook.

Typically, you will create a workbook in one of two ways, either by loading it from a spreadsheet file, or creating it manually. A third option, though less commonly used, is cloning an existing workbook that has been created using one of the previous two methods.

Loading a Workbook from a file

Details of the different spreadsheet formats supported, and the options available to read them into a Spreadsheet object are described fully in the Reading Files document.

$inputFileName = './sampleData/example1.xls'; /** Load $inputFileName to a Spreadsheet object **/ $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName); 

Creating a new workbook

If you want to create a new workbook, rather than load one from file, then you simply need to instantiate it as a new Spreadsheet object.

/** Create a new Spreadsheet Object **/ $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); 

A new workbook will always be created with a single worksheet.

Clearing a Workbook from memory

The PhpSpreadsheet object contains cyclic references (e.g. the workbook is linked to the worksheets, and the worksheets are linked to their parent workbook) which cause problems when PHP tries to clear the objects from memory when they are unset() , or at the end of a function when they are in local scope. The result of this is «memory leaks», which can easily use a large amount of PHP’s limited memory.

This can only be resolved manually: if you need to unset a workbook, then you also need to «break» these cyclic references before doing so. PhpSpreadsheet provides the disconnectWorksheets() method for this purpose.

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

Источник

Worksheets

A worksheet is a collection of cells, formulae, images, graphs, etc. It holds all data necessary to represent a spreadsheet worksheet.

When you load a workbook from a spreadsheet file, it will be loaded with all its existing worksheets (unless you specified that only certain sheets should be loaded). When you load from non-spreadsheet files (such as a CSV or HTML file) or from spreadsheet formats that don’t identify worksheets by name (such as SYLK), then a single worksheet called «WorkSheet1» will be created containing the data from that file.

When you instantiate a new workbook, PhpSpreadsheet will create it with a single worksheet called «WorkSheet1».

The getSheetCount() method will tell you the number of worksheets in the workbook; while the getSheetNames() method will return a list of all worksheets in the workbook, indexed by the order in which their «tabs» would appear when opened in MS Excel (or other appropriate Spreadsheet program).

Individual worksheets can be accessed by name, or by their index position in the workbook. The index position represents the order that each worksheet «tab» is shown when the workbook is opened in MS Excel (or other appropriate Spreadsheet program). To access a sheet by its index, use the getSheet() method.

// Get the second sheet in the workbook // Note that sheets are indexed from 0 $spreadsheet->getSheet(1); 

Methods also exist allowing you to reorder the worksheets in the workbook.

To access a sheet by name, use the getSheetByName() method, specifying the name of the worksheet that you want to access.

// Retrieve the worksheet called 'Worksheet 1' $spreadsheet->getSheetByName('Worksheet 1'); 

Alternatively, one worksheet is always the currently active worksheet, and you can access that directly. The currently active worksheet is the one that will be active when the workbook is opened in MS Excel (or other appropriate Spreadsheet program).

// Retrieve the current active worksheet $spreadsheet->getActiveSheet(); 

You can change the currently active sheet by index or by name using the setActiveSheetIndex() and setActiveSheetIndexByName() methods.

Adding a new Worksheet

You can add a new worksheet to the workbook using the createSheet() method of the Spreadsheet object. By default, this will be created as a new «last» sheet; but you can also specify an index position as an argument, and the worksheet will be inserted at that position, shuffling all subsequent worksheets in the collection down a place.

A new worksheet created using this method will be called Worksheet where is the lowest number possible to guarantee that the title is unique.

Alternatively, you can instantiate a new worksheet (setting the title to whatever you choose) and then insert it into your workbook using the addSheet() method.

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

If you don’t specify an index position as the second argument, then the new worksheet will be added after the last existing worksheet.

Copying Worksheets

Sheets within the same workbook can be copied by creating a clone of the worksheet you wish to copy, and then using the addSheet() method to insert the clone into the workbook.

$clonedWorksheet = clone $spreadsheet->getSheetByName('Worksheet 1'); $clonedWorksheet->setTitle('Copy of Worksheet 1'); $spreadsheet->addSheet($clonedWorksheet); 

You can also copy worksheets from one workbook to another, though this is more complex as PhpSpreadsheet also has to replicate the styling between the two workbooks. The addExternalSheet() method is provided for this purpose.

$clonedWorksheet = clone $spreadsheet1->getSheetByName('Worksheet 1'); $spreadsheet->addExternalSheet($clonedWorksheet); 

In both cases, it is the developer’s responsibility to ensure that worksheet names are not duplicated. PhpSpreadsheet will throw an exception if you attempt to copy worksheets that will result in a duplicate name.

Removing a Worksheet

You can delete a worksheet from a workbook, identified by its index position, using the removeSheetByIndex() method

$sheetIndex = $spreadsheet->getIndex( $spreadsheet->getSheetByName('Worksheet 1') ); $spreadsheet->removeSheetByIndex($sheetIndex); 

If the currently active worksheet is deleted, then the sheet at the previous index position will become the currently active sheet.

Источник

PhpSpreadsheet: Работа с excel файлами (запись и чтение данных)

На смену PHPExcel пришла мощная библиотека PhpSpreadsheet от тех же разработчиков.

Создание exсel файла

use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setTitle('Worksheet 1'); $sheet->setCellValue('A1', 'Hello World !'); // Writer можно создать так: $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); //$writer = new Xlsx($spreadsheet); $writer->save($path . '/export/worksheet__' . date('Ymd_h:i:s') . '.xlsx'); $spreadsheet->disconnectWorksheets();

Запись данных в exсel с помощью итератора

Более информативный пример со стилями документа, строк, ячеек по условию.

use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Style; try < $path = $path = realpath(__DIR__); // Способ #1. Стили для шапки $headerStyles = [ 'font'=>[ 'color'=>[ 'rgb' => '000' ], 'bold' => true, 'size' => 13 ], 'fill'=>[ 'fillType' => Fill::FILL_SOLID, 'startColor' => [ 'rgb' => Color::COLOR_CYAN ] ], ]; // Способ #2. Стили для данных $redStyle = new Style(false, true); $redStyle->getFill() ->setFillType(Fill::FILL_SOLID) ->getStartColor()->setARGB(Color::COLOR_RED); // Создание документа, листа и "писателя" $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // Название листа $sheet->setTitle('Worksheet 1'); $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // Стили документа по умолчанию $spreadsheet->getDefaultStyle() ->getFont() ->setName('Arial') ->setSize(12); // Дополнительные стили // . // Выравнивание по центру в строке //$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // Установка стилей для шапки $spreadsheet->getActiveSheet()->getStyle('A1:F1')->applyFromArray($headerStyles); // Шапка $headerItems = [ 'First Name', 'Last Name', 'Email', 'Gender', 'Age', 'Class' ]; // Добавление шапки $sheet->fromArray($headerItems); // A1 start // Данные из файла для примера (чаще всего из базы данных) $file = file_get_contents('student-data.json'); $studentData = json_decode($file,true); // Расчёт крайней правой точки листа $extremeCellValue = $sheet ->getCellByColumnAndRow( count($headerItems), count($studentData) + 1 ) ->getCoordinate(); // Форматирование ячеки по условию (если студенту меньше 18 лет) $conditionalStyles = []; $wizardFactory = new Wizard("A1:$extremeCellValue"); $textWizard = $wizardFactory->newRule(Wizard::CELL_VALUE); $textWizard->lessThan(18) ->setStyle($redStyle); $conditionalStyles[] = $textWizard->getConditional(); $spreadsheet->getActiveSheet() ->getStyle($textWizard->getCellRange()) ->setConditionalStyles($conditionalStyles); // Данные вставлять со 2-й строки $row = 2; foreach ($studentData as $student): unset($student['id']); // Исключаем id из данных (если необходимо) $sheet->fromArray($student, null, 'A'.$row); // A2 start $row++; endforeach; // Сохранение файла $writer->save($path . '/export/students_list__' . date('Ymd_h:i:s') . '.xlsx'); > catch (Exception $e) < $error = date('Y/m/d H:i:s') . ': ' . $e->getMessage() . PHP_EOL; error_log($error, 3, $path . '/runtime/app.log'); die($e->getMessage() . PHP_EOL); >

Чтение данных из файла exсel

use PhpOffice\PhpSpreadsheet\IOFactory; $reader = IOFactory::createReader('Xlsx'); $spreadsheet = $reader->load('students_list.xlsx'); // Только чтение данных $reader->setReadDataOnly(true); // Количество листов $sheetsCount = $spreadsheet->getSheetCount(); // Данные в виде массива $data = $spreadsheet->getActiveSheet()->toArray(); foreach ($data as $item): var_dump($item); endforeach;

Источник

Welcome to PhpSpreadsheet’s documentation

PhpSpreadsheet is a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc.

File formats supported

Format	Reading	Writing
Open Document Format/OASIS (.ods)	✓	✓
Office Open XML (.xlsx) Excel 2007 and above	✓	✓
BIFF 8 (.xls) Excel 97 and above	✓	✓
BIFF 5 (.xls) Excel 95	✓
SpreadsheetML (.xml) Excel 2003	✓
Gnumeric	✓
HTML	✓	✓
SYLK	✓
CSV	✓	✓
PDF (using either the TCPDF, Dompdf or mPDF libraries, which need to be installed separately)	✓

Getting started

Software requirements

PHP version 7.4 or newer to develop using PhpSpreadsheet. Other requirements, such as PHP extensions, are enforced by composer. See the require section of the composer.json file for details.

PHP version support

LTS: Support for PHP versions will only be maintained for a period of six months beyond the end of life of that PHP version.

Currently the required PHP minimum version is PHP 7.4: the last release was 7.4.32 on 29th September 2022, and security support ends on 28th November 2022, so PhpSpreadsheet will support PHP 7.4 until 28th May 2023. PHP 8.0 is officially End of Life on 26th November 2023, and PhpSpreadsheet will continue to support PHP 8.0 for six months after that date.

See the composer.json for other requirements.

Installation

Use composer to install PhpSpreadsheet into your project:

composer require phpoffice/phpspreadsheet 

Or also download the documentation and samples if you plan to use them:

composer require phpoffice/phpspreadsheet --prefer-source 

If you are building your installation on a development machine that is on a different PHP version to the server where it will be deployed, or if your PHP CLI version is not the same as your run-time such as php-fpm or Apache’s mod_php , then you might want to add the following to your composer.json before installing:

to ensure that the correct dependencies are retrieved to match your deployment environment.

Hello World

This would be the simplest way to write a spreadsheet:

getActiveSheet(); $activeWorksheet->setCellValue('A1', 'Hello World !'); $writer = new Xlsx($spreadsheet); $writer->save('hello world.xlsx'); 

Learn by example

A good way to get started is to run some of the samples. Don’t forget to download them via —prefer-source composer flag. And then serve them via PHP built-in webserver:

php -S localhost:8000 -t vendor/phpoffice/phpspreadsheet/samples 

Then point your browser to:

The samples may also be run directly from the command line, for example:

php vendor/phpoffice/phpspreadsheet/samples/Basic/01_Simple.php 

Learn by documentation

Credits

Please refer to the contributor list for up-to-date credits.

Источник