Добавление изображения в excel в phpexcel в переполнении стека

Properties

protected bool $readDataOnly Read data only?
Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
or whether it should read both data and formatting.
from 
protected bool $readEmptyCells Read empty cells?
Identifies whether the Reader should read data values for cells all cells, or should ignore cells containing
null value or empty string.
from 
protected bool $includeCharts Read charts that are defined in the workbook?
Identifies whether the Reader should read the definitions for any charts that exist in the workbook;.
from 
protected array $loadSheetsOnly Restrict which sheets should be loaded?
This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
from 
protected IReadFilter $readFilter IReadFilter instance. from 
protected $fileHandle from 
protected XmlScanner $securityScanner from 
protected string $inputEncoding Input encoding.
protected int $sheetIndex Sheet index to read.
protected array $formats Formats.
protected $rowspan
protected $dataArray
protected $tableLevel
protected $nestedColumn

Импорт данных из PHP в Excel

Рассмотрим пример по формированию таблицы умножения.

// Подключаем класс для работы с excel
require_once('PHPExcel.php');
// Подключаем класс для вывода данных в формате excel
require_once('PHPExcel/Writer/Excel5.php');

// Создаем объект класса PHPExcel
$xls = new PHPExcel();
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Таблица умножения');

// Вставляем текст в ячейку A1
$sheet->setCellValue("A1", 'Таблица умножения');
$sheet->getStyle('A1')->getFill()->setFillType(
    PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE');

// Объединяем ячейки
$sheet->mergeCells('A1:H1');

// Выравнивание текста
$sheet->getStyle('A1')->getAlignment()->setHorizontal(
    PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

for ($i = 2; $i setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                          $i . "x" .$j . "=" . ($i*$j));
	    // Применяем выравнивание
	    $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	}
}

Далее нам необходимо получить наш *.xls файл. Здесь можно пойти двумя путями. Если предположим у вас интернет магазин, и клиент хочет скачать прайс лист, то будет лучше прибегнуть к такому выводу:

// Выводим HTTP-заголовки
 header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
 header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
 header ( "Cache-Control: no-cache, must-revalidate" );
 header ( "Pragma: no-cache" );
 header ( "Content-type: application/vnd.ms-excel" );
 header ( "Content-Disposition: attachment; filename=matrix.xls" );

// Выводим содержимое файла
 $objWriter = new PHPExcel_Writer_Excel5($xls);
 $objWriter->save('php://output');

Здесь сформированные данные сразу “выплюнутся” в браузер. Однако, если вам нужно файл сохранить, а не “выбросить” его сразу, то не нужно выводить HTTP-заголовки и вместо “php://output” следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.

Рассмотрим еще на примере три полезные инструкции:

  • $sheet->getColumnDimension(‘A’)->setWidth(40) – устанавливает столбцу “A” ширину в 40 единиц;
  • $sheet->getColumnDimension(‘B’)->setAutoSize(true) – здесь у столбца “B” будет установлена автоматическая ширина;
  • $sheet->getRowDimension(4)->setRowHeight(20) – устанавливает четвертой строке высоту равную 20 единицам.

Также обратите внимание на следующие необходимые для работы с отчетом методы:

  • Методы для вставки данных в ячейку:
    • setCellValue(]]) — принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр эта одна из констант типа boolean: true или false (если передать значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
    • setCellValueByColumnAndRow(]]]) — принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue().
  • Методы для получения ячейки:
    • getCell() — принимает в качестве параметра координату ячейки;
    • getCellByColumnAndRow(]) — принимает два параметра в виде номеров столбца и строки ячейки.

Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.

1. Prerequisites

1.1 Software requirements

The following software is required to develop using PHPExcel:

  • PHP version 5.2.0 or newer
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled (if not compiled in)

: php_zip is only needed by PHPExcel_Reader_Excel2007, PHPExcel_Writer_Excel2007 and PHPExcel_Reader_OOCalc. In other words, if you need PHPExcel to handle .xlsx or .ods files you will need the zip extension, but otherwise not. You can remove this dependency for writing Excel2007 files (though not yet for reading) by using the PCLZip library that is bundled with PHPExcel. See the FAQ section of this document for details about this. PCLZip does have a dependency on PHP’s zlib extension being enabled.

1.2 Installation instructions

Installation is quite easy: copy the contents of the Classes folder to any location within your application source directories.

Example:

If your web root folder is /var/www/ you may want to create a subfolder called /var/www/Classes/ and copy the files into that folder so you end up with files:

/var/www/Classes/PHPExcel.php
/var/www/Classes/PHPExcel/Calculation.php
/var/www/Classes/PHPExcel/Cell.php

1.3 Getting started

A good way to get started is to run some of the tests included in the download.
Copy the «Examples» folder next to your «Classes» folder from above so you end up with:

/var/www/Examples/01simple.php
/var/www/Examples/02types.php

Start running the tests by pointing your browser to the test scripts:

Note: It may be necessary to modify the include/require statements at the beginning of each of the test scripts if your «Classes» folder from above is named differently.

1.4.2 Frequently asked questions

There seems to be a problem with character encoding…

It is necessary to use UTF-8 encoding for all texts in PHPExcel. If the script uses different encoding then you can convert those texts with PHP’s iconv() or mb_convert_encoding() functions.

PHP complains about ZipArchive not being found

Make sure you meet all requirements, especially php_zip extension should be enabled.

The ZipArchive class is only required when reading or writing formats that use Zip compression (Excel2007 and OOCalc). Since version 1.7.6 the PCLZip library has been bundled with PHPExcel as an alternative to the ZipArchive class.

This can be enabled by calling:

PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);

before calling the save method of the Excel2007 Writer.

You can revert to using ZipArchive by calling:

PHPExcel_Settings::setZipClass(PHPExcel_Settings::ZIPARCHIVE);

At present, this only allows you to write Excel2007 files without the need for ZipArchive (not to read Excel2007 or OOCalc)

Excel 2007 cannot open the file generated by PHPExcel_Writer_2007 on Windows

«Excel found unreadable content in ‘.xlsx’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.»*

Alternatively, upgrading to at least PHP 5.2.9 should solve the problem.

If you can’t locate a clean copy of ZipArchive, then you can use the PCLZip library as an alternative when writing Excel2007 files, as described above.

Protection on my worksheet is not working?

When you make use of any of the worksheet protection features (e.g. cell range protection, prohibiting deleting rows, …), make sure you enable worksheet security. This can for example be done like this:

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
Feature X is not working with PHPExcel_Reader_Y / PHPExcel_Writer_Z

Not all features of PHPExcel are implemented in all of the Reader / Writer classes. This is mostly due to underlying libraries not supporting a specific feature or not having implemented a specific feature.

For example autofilter is not implemented in PEAR Spreadsheet_Excel_writer, which is the base of our Excel5 writer.

We are slowly building up a list of features, together with the different readers and writers that support them, in the «Functionality Cross-Reference.xls» file in the /Documentation folder.

Formulas don’t seem to be calculated in Excel2003 using compatibility pack?

This is normal behaviour of the compatibility pack, Excel2007 displays this correctly. Use PHPExcel_Writer_Excel5 if you really need calculated values, or force recalculation in Excel2003.

Setting column width is not 100% accurate

Trying to set column width, I experience one problem. When I open the file in Excel, the actual width is 0.71 less than it should be.

The short answer is that PHPExcel uses a measure where padding is included. See section: «Setting a column’s width» for more details.

Methods

bool

()

Write charts in workbook?
If this is true, then the Writer will write definitions for any charts that exist in the PhpSpreadsheet object.

from 

IWriter

(bool $pValue)

Set write charts in workbook
Set to true, to advise the Writer to include any charts that exist in the PhpSpreadsheet object.

from 

bool

()

Get Pre-Calculate Formulas flag
If this is true (the default), then the writer will recalculate all formulae in a workbook when saving,
so that the pre-calculated values are immediately available to MS Excel or other office spreadsheet
viewer when opening the file
If false, then formulae are not calculated on save. This is faster for saving in PhpSpreadsheet, but slower
when opening the resulting file in MS Excel, because Excel has to recalculate the formulae itself.

from 

IWriter

(bool $pValue)

Set Pre-Calculate Formulas
Set to true (the default) to advise the Writer to calculate all formulae on save
Set to false to prevent precalculation of formulae on save.

from 

bool

()

Get use disk caching where possible?

from 

IWriter

(bool $pValue, string $pDirectory = null)

Set use disk caching where possible?

from 

string

()

Get disk caching directory.

from 

(Spreadsheet $spreadsheet)

Create a new PDF Writer instance.

(string $pFilename)

Save Spreadsheet to file.

from 

int

()

Get sheet index.

from 

$this

(int $pValue)

Set sheet index.

from 

bool

()

Get sheet index.

from 

$this

(bool $pValue)

Set sheet index.

from 

$this

()

Write all sheets (resets sheetIndex to NULL).

from 

string

(bool $pIncludeStyles = false)

Generate HTML header.

from 

string

()

Generate sheet data.

from 

string

()

Generate sheet tabs.

from 

string

(bool $generateSurroundingHTML = true)

Generate CSS styles.

from 

array

(bool $generateSurroundingHTML = true)

Build CSS styles.

from 

()

Generate HTML footer.

from 

string

()

Get images root.

from 

$this

(string $pValue)

Set images root.

from 

bool

()

Get embed images.

from 

$this

(bool $pValue)

Set embed images.

from 

bool

()

Get use inline CSS?

from 

$this

(bool $pValue)

Set use inline CSS?

from 

bool

()

Get use embedded CSS?

from 

$this

(bool $pValue)

Set use embedded CSS?

from 

string

(string $pValue, string $pFormat)

Add color to formatted string as inline style.

from 

string

()

Get Font.

$this

(string $fontName)

Set font. Examples:
‘arialunicid0-chinese-simplified’
‘arialunicid0-chinese-traditional’
‘arialunicid0-korean’
‘arialunicid0-japanese’.

int

()

Get Paper Size.

Pdf

(string $pValue)

Set Paper Size.

string

()

Get Orientation.

Pdf

(string $pValue)

Set Orientation.

string

()

Get temporary storage directory.

Pdf

(string $pValue)

Set temporary storage directory.

resource

(string $pFilename)

Save Spreadsheet to PDF file, pre-save.

(resource $fileHandle)

Save PhpSpreadsheet to PDF file, post-save.

Properties

protected bool $readDataOnly Read data only?
Identifies whether the Reader should only read data values for cells, and ignore any formatting information;
or whether it should read both data and formatting.
from 
protected bool $readEmptyCells Read empty cells?
Identifies whether the Reader should read data values for cells all cells, or should ignore cells containing
null value or empty string.
from 
protected bool $includeCharts Read charts that are defined in the workbook?
Identifies whether the Reader should read the definitions for any charts that exist in the workbook;.
from 
protected array $loadSheetsOnly Restrict which sheets should be loaded?
This property holds an array of worksheet names to be loaded. If null, then all worksheets will be loaded.
from 
protected IReadFilter $readFilter IReadFilter instance. from 
protected $fileHandle from 
protected XmlScanner $securityScanner from 

Methods

$this

()

Update the cell into the cell collection.

()

No description

(Cells $parent)

No description

(mixed $pValue, string $pDataType, Worksheet $pSheet)

Create a new Cell.

string

()

Get cell coordinate column.

int

()

Get cell coordinate row.

string

()

Get cell coordinate.

mixed

()

Get cell value.

string

()

Get cell value with formatting.

$this

(mixed $pValue)

Set cell value.

Cell

(mixed $pValue, string $pDataType)

Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder).

mixed

(bool $resetLog = true)

Get calculated cell value.

Cell

(mixed $pValue)

Set old calculated value (cached).

mixed

()

Get old calculated value (cached)
This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
create the original spreadsheet file.

string

()

Get cell data type.

Cell

(string $pDataType)

Set cell data type.

bool

()

Identify if the cell contains a formula.

bool

()

Does this cell contain Data validation rules?

DataValidation

()

Get Data validation rules.

Cell

(DataValidation $pDataValidation = null)

Set Data validation rules.

bool

()

Does this cell contain valid value?

bool

()

Does this cell contain a Hyperlink?

Hyperlink

()

Get Hyperlink.

Cell

(Hyperlink $pHyperlink = null)

Set Hyperlink.

Cells

()

Get cell collection.

Worksheet

()

Get parent worksheet.

bool

()

Is this cell in a merge range.

bool

()

Is this cell the master (top left cell) in a merge range (that holds the actual data value).

false|string

()

If this cell is in a merge range, then return the range.

Style

()

Get cell style.

Cell

(Worksheet $parent)

Re-bind parent.

bool

(string $pRange)

Is cell in a specific range?

static int

(Cell $a, Cell $b)

Compare 2 cells.

static IValueBinder

()

Get value binder to use.

static 

(IValueBinder $binder)

Set value binder to use.

()

Implement PHP __clone to create a deep clone, not just a shallow copy.

int

()

Get index to cellXf.

Cell

(int $pValue)

Set index to cellXf.

$this

(mixed $pAttributes)

Set the formula attributes.

()

Get the formula attributes.

string

()

Convert to string.

phpexcel: писать данные в Excel с помощью PHP и читать из Excel

Примеры по phpexcel. Создание excel-документа(.xlsx) на php

← разместить еще код

Пример создания excel-документа(.xlsx) на php с помощью класс PHPEXCEL. На странице представлен пример подключения класса и вызов методов для создания документа. Также приведены примеры следующих возможностей: — заполнение ячеек — объединение ячеек — установка ширины колонки — выравнивание контента ячейки — заливка ячеек — рамки ячеек — размер шрифта в ячейках

Расшаренный код:

//———————————-Пример————————————— date_default_timezone_set(‘Europe/London’); if (PHP_SAPI == ‘cli’) die(‘This example should only be run from a Web Browser’); require_once $_SERVER.»/phpexcel/Classes/PHPExcel.php»;/* подключаем класс */ $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator(«афтор») ->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»); $objWorkSheet = $objPHPExcel->createSheet(0);/* номер листа */ /* …. здесь заполняем лист …. */ header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’); header(‘Content-Disposition: attachment;filename=»01simple.xlsx»’); header(‘Cache-Control: max-age=0’); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’); $objWriter->save(‘php://output’); //———————————————————————— //———————————-Примеры оформления листа————————————— /* заполняем ячейки */ $objWorkSheet->setCellValue(‘A1’, ‘1 этаж’); /* объединение ячеек */ $objWorkSheet->mergeCells(‘A1:A2’); /* устанавливаем ширину колонки */ $objWorkSheet->getColumnDimension(‘A’)->setWidth(9); /* выравниваем по центру (вертильно-горизонтально) */ $objWorkSheet->getStyle(‘A2’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); /* заливаем ячейки нужным цветом — hex (b2b2b2) */ $objWorkSheet ->getStyle(‘A1:A2’) ->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor() ->setRGB(‘hex’); /* устанавливаем бордер ячейкам */ $styleArray = array( ‘borders’ => array( ‘allborders’ => array( ‘style’ => PHPExcel_Style_Border::BORDER_THIN ) ) ); $objWorkSheet->getStyle(‘A1:A2’)->applyFromArray($styleArray); /* размер шрифта в ячейке */ $objWorkSheet->getStyle(‘A1’)->getFont()->setSize(13); /* Имя листа */ $objWorkSheet->setTitle(‘Прайс-лист ‘.$block_section);

Methods

static bool

(string $compatibilityMode)

Set the Compatibility Mode.

static string

()

Return the current Compatibility Mode.

static bool

(string $returnDateType)

Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object).

static string

()

Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object).

static string

()

DUMMY.

static string

()

DIV0.

static string

()

NA.

static string

()

NaN.

static string

()

NAME.

static string

()

REF.

static string

()

NULL.

static string

()

VALUE.

static 

($idx)

No description

static 

($idx)

No description

static 

($idx)

No description

static 

($condition)

No description

static bool

(mixed $value = »)

ERROR_TYPE.

static bool

(mixed $value = null)

IS_BLANK.

static bool

(mixed $value = »)

IS_ERR.

static bool

(mixed $value = »)

IS_ERROR.

static bool

(mixed $value = »)

IS_NA.

static bool|string

(mixed $value = null)

IS_EVEN.

static bool|string

(mixed $value = null)

IS_ODD.

static bool

(mixed $value = null)

IS_NUMBER.

static bool

(mixed $value = null)

IS_LOGICAL.

static bool

(mixed $value = null)

IS_TEXT.

static bool

(mixed $value = null)

IS_NONTEXT.

static number

(null|mixed $value = null)

N.

static number

(null|mixed $value = null)

TYPE.

static array

(array $array)

Convert a multi-dimensional array to a simple 1-dimensional array.

static array

(array $array)

Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing.

static mixed

(mixed $value = »)

Convert an array to a single scalar value by extracting the first element.

static bool|string

(mixed $cellReference = », Cell $pCell = null)

ISFORMULA.

Details

Update the cell into the cell collection.

Create a new Cell.

Get cell coordinate column.

Get cell coordinate row.

Get cell coordinate.

Get cell value.

Get cell value with formatting.

Set cell value.

Sets the value for a cell, automatically determining the datatype using the value binder

Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder).

Get calculated cell value.

Set old calculated value (cached).

Get old calculated value (cached)
This returns the value last calculated by MS Excel or whichever spreadsheet program was used to
create the original spreadsheet file.

Note that this value is not guaranteed to reflect the actual calculated value because it is
possible that auto-calculation was disabled in the original spreadsheet, and underlying data
values used by the formula have changed since it was last calculated.

Get cell data type.

Set cell data type.

Identify if the cell contains a formula.

Does this cell contain Data validation rules?

Get Data validation rules.

Set Data validation rules.

Does this cell contain valid value?

Does this cell contain a Hyperlink?

Get Hyperlink.

Set Hyperlink.

Get cell collection.

Get parent worksheet.

Is this cell in a merge range.

Is this cell the master (top left cell) in a merge range (that holds the actual data value).

If this cell is in a merge range, then return the range.

Get cell style.

Re-bind parent.

Is cell in a specific range?

Compare 2 cells.

Get value binder to use.

Set value binder to use.

Implement PHP __clone to create a deep clone, not just a shallow copy.

Get index to cellXf.

Set index to cellXf.

Set the formula attributes.

Get the formula attributes.

Methods

()

Create a new Xlsx Reader instance.

bool

()

Read data only?
If this is true, then the Reader will only read data values for cells, it will not read any formatting information.

from 

IReader

(bool $pValue)

Set read data only
Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.

from 

bool

()

Read empty cells?
If this is true (the default), then the Reader will read data values for all cells, irrespective of value.

from 

IReader

(bool $pValue)

Set read empty cells
Set to true (the default) to advise the Reader read data values for all cells, irrespective of value.

from 

bool

()

Read charts in workbook?
If this is true, then the Reader will include any charts that exist in the workbook.

from 

IReader

(bool $pValue)

Set read charts in workbook
Set to true, to advise the Reader to include any charts that exist in the workbook.

from 

mixed

()

Get which sheets to load
Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
indicating that all worksheets in the workbook should be loaded.

from 

IReader

(mixed $value)

Set which sheets to load.

from 

IReader

()

Set all sheets to load
Tells the Reader to load all worksheets from the workbook.

from 

IReadFilter

()

Read filter.

from 

IReader

(IReadFilter $pValue)

Set read filter.

from 

()

No description

from 

(string $pFilename)

Open file for reading.

from 

bool

(string $pFilename)

Can the current IReader read the file?

array

(string $pFilename)

Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.

array

(string $pFilename)

Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).

Spreadsheet

(string $pFilename)

Loads Spreadsheet from file.

Details

Create a new HTML Reader instance.

Read data only?
If this is true, then the Reader will only read data values for cells, it will not read any formatting information.

If false (the default) it will read data and formatting.

Set read data only
Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.

Set to false (the default) to advise the Reader to read both data and formatting for cells.

Read empty cells?
If this is true (the default), then the Reader will read data values for all cells, irrespective of value.

If false it will not read data for cells containing a null value or an empty string.

Set read empty cells
Set to true (the default) to advise the Reader read data values for all cells, irrespective of value.

Set to false to advise the Reader to ignore cells containing a null value or an empty string.

Read charts in workbook?
If this is true, then the Reader will include any charts that exist in the workbook.

Note that a ReadDataOnly value of false overrides, and charts won’t be read regardless of the IncludeCharts value.
If false (the default) it will ignore any charts defined in the workbook file.

Set read charts in workbook
Set to true, to advise the Reader to include any charts that exist in the workbook.

Note that a ReadDataOnly value of false overrides, and charts won’t be read regardless of the IncludeCharts value.
Set to false (the default) to discard charts.

Get which sheets to load
Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
indicating that all worksheets in the workbook should be loaded.

Set which sheets to load.

Set all sheets to load
Tells the Reader to load all worksheets from the workbook.

Read filter.

Set read filter.

Validate that the current file is an HTML file.

Loads Spreadsheet from file.

Set input encoding.

Get input encoding.

Loads PhpSpreadsheet from file into PhpSpreadsheet instance.

Spreadsheet from content.

Get sheet index.

Set sheet index.

Check if has #, so we can get clean hex.

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

// Подключаем библиотеку
require_once 'Classes/PHPExcel.php';
$pExcel = PHPExcel_IOFactoryload('simple.xlsx');

// Цикл по листам Excel-файла
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
    // выгружаем данные из объекта в массив
    $tables = $worksheet->toArray();
}

Теперь можно вывести массив:

// Цикл по листам Excel-файла
foreach( $tables as $table ) {
    echo '

;
// Цикл по строкам
foreach($table as $row) {
echo »;
// Цикл по колонкам
foreach( $row as $col ) {
echo ».$col.’
‘;
}
echo ‘
‘;
}
echo ‘
‘;
}

Для получения значения отдельной ячейки:

// выбираем лист, с которым будем работать
$pExcel->setActiveSheetIndex();
$aSheet = $pExcel->getActiveSheet();
// получаем доступ к ячейке по номеру строки 
// (нумерация с единицы) и столбца (нумерация с нуля) 
$cell = $aSheet->getCellByColumnAndRow($col, $row);
// читаем значение ячейки
$value = $cell->getValue()

или так:

$value = $pExcel->getActiveSheet()->getCellValue('B2')

Еще два примера:

// Цикл по листам Excel-файла
foreach( $pExcel->getWorksheetIterator() as $worksheet ) {
    echo '

Лист «’

.$worksheet->getTitle().’»’;
echo ‘

;
// Цикл по строкам
foreach( $worksheet->getRowIterator() as $row ) {
echo »;
// Цикл по колонкам
foreach( $row->getCellIterator() as $cell ) {
$value = $cell->getValue();
// $calcValue = $cell->getCalculatedValue()
// $dataType = PHPExcel_Cell_DataType::dataTypeForValue($value);
echo ».$value.’
‘;
}
echo ‘
‘;
}
echo ‘
‘;
}

// Цикл по листам Excel-файла
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // например, 10
    $highestColumn      = $worksheet->getHighestColumn(); // например, 'E'
    $highestColumnIndex = PHPExcel_CellcolumnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    echo '

Лист «’

.$worksheetTitle.’» ‘;
echo $nrColumns . ‘ колонок (A-‘ . $highestColumn . ‘) ‘;
echo ‘ и ‘ . $highestRow . ‘ строк.’;
echo ‘

;
// Цикл по строкам
for ($row = 1; $row = $highestRow; $row++) {
echo »;
// Цикл по колонкам
for ($col = ; $col $highestColumnIndex; $col++) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
echo ».$cell->getValue().’
‘;
}
echo ‘
‘;
}
echo ‘
‘;
}

Details

Write charts in workbook?
If this is true, then the Writer will write definitions for any charts that exist in the PhpSpreadsheet object.

If false (the default) it will ignore any charts defined in the PhpSpreadsheet object.

Set write charts in workbook
Set to true, to advise the Writer to include any charts that exist in the PhpSpreadsheet object.

Set to false (the default) to ignore charts.

Get Pre-Calculate Formulas flag
If this is true (the default), then the writer will recalculate all formulae in a workbook when saving,
so that the pre-calculated values are immediately available to MS Excel or other office spreadsheet
viewer when opening the file
If false, then formulae are not calculated on save. This is faster for saving in PhpSpreadsheet, but slower
when opening the resulting file in MS Excel, because Excel has to recalculate the formulae itself.

Set Pre-Calculate Formulas
Set to true (the default) to advise the Writer to calculate all formulae on save
Set to false to prevent precalculation of formulae on save.

Get use disk caching where possible?

Set use disk caching where possible?

Get disk caching directory.

Create a new PDF Writer instance.

Save Spreadsheet to file.

Get sheet index.

Set sheet index.

Get sheet index.

Set sheet index.

Write all sheets (resets sheetIndex to NULL).

Generate HTML header.

Generate sheet data.

Generate sheet tabs.

Generate CSS styles.

Build CSS styles.

Generate HTML footer.

Get images root.

Set images root.

Get embed images.

Set embed images.

Get use inline CSS?

Set use inline CSS?

Get use embedded CSS?

Set use embedded CSS?

Get Font.

Set font. Examples:
‘arialunicid0-chinese-simplified’
‘arialunicid0-chinese-traditional’
‘arialunicid0-korean’
‘arialunicid0-japanese’.

Get Paper Size.

Set Paper Size.

Get Orientation.

Set Orientation.

Get temporary storage directory.

Set temporary storage directory.

Save Spreadsheet to PDF file, pre-save.

Methods

()

Create a new HTML Reader instance.

bool

()

Read data only?
If this is true, then the Reader will only read data values for cells, it will not read any formatting information.

from 

IReader

(bool $pValue)

Set read data only
Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.

from 

bool

()

Read empty cells?
If this is true (the default), then the Reader will read data values for all cells, irrespective of value.

from 

IReader

(bool $pValue)

Set read empty cells
Set to true (the default) to advise the Reader read data values for all cells, irrespective of value.

from 

bool

()

Read charts in workbook?
If this is true, then the Reader will include any charts that exist in the workbook.

from 

IReader

(bool $pValue)

Set read charts in workbook
Set to true, to advise the Reader to include any charts that exist in the workbook.

from 

mixed

()

Get which sheets to load
Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
indicating that all worksheets in the workbook should be loaded.

from 

IReader

(mixed $value)

Set which sheets to load.

from 

IReader

()

Set all sheets to load
Tells the Reader to load all worksheets from the workbook.

from 

IReadFilter

()

Read filter.

from 

IReader

(IReadFilter $pValue)

Set read filter.

from 

()

No description

from 

(string $pFilename)

Open file for reading.

from 

bool

(string $pFilename)

Validate that the current file is an HTML file.

Spreadsheet

(string $pFilename)

Loads Spreadsheet from file.

$this

(string $pValue)

Set input encoding.

string

()

Get input encoding.

($column)

No description

()

No description

()

No description

(Worksheet $sheet, $column, $row, $cellContent)

No description

(DOMNode $element, Worksheet $sheet, int $row, string $column, string $cellContent)

No description

Spreadsheet

(string $pFilename, Spreadsheet $spreadsheet)

Loads PhpSpreadsheet from file into PhpSpreadsheet instance.

Spreadsheet

(string $content, Spreadsheet|null $spreadsheet = null)

Spreadsheet from content.

int

()

Get sheet index.

$this

(int $pValue)

Set sheet index.

null|string

($value)

Check if has #, so we can get clean hex.

null|string

(string $style)

Map html border style to PhpSpreadsheet border style.

Ссылка на основную публикацию