PHPExcel Documentation for Developers

Author: Maarten Balliauw

Version: 1.7.4
Date: 27 April 2011
1. Contents
1. CONTENTS........................................................................................................................................................2
2. REQUIREMENTS PREVIOUS..................................................................................................................5

PCAN I REMOVE THIS DEPENDENCY TO WRITE EXCEL?2007FILES(NOT FOR READING) THROUGH THE USE OF THE
LIBRARY PCL ZIP THAT IS INCLUDED WITH PHP EXCEL. SEE THE FAQ SECTION OF THIS DOCUMENT (2.4.2) FOR DETAILS

ABOUT THIS. VTHIS IS THE FAQ SECTION OF THIS DOCUMENT(2.4.2)FOR MORE DETAILS ABOUT THIS.
PCLZIP DOES HAVE A DEPENDENCY ON PHP’S ZLIB EXTENSION BEING ENABLED. PCLZIP HAS A DEPENDENCY ON ZLIB.

EXTENSIONPHPIS ENABLING5
2.1. LINSTALLATION INSTRUCTIONS5
2.2. PFIRST STEPS5
2.3. EUSEFUL PLACES AND TOOLS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.3.1. OpenXML / SpreadsheetML
2.3.2. Frequently Asked Questions...............................................................................................6
2.3.3. Tutorials................................................................................................................................................9
3. ARCHITECTURE...........................................................................................................................................10
3.1. ESCHEMATIC..................................................................................................................................................10
3.2. LAZYLOADER10
3.3. SPREADSHEET(HSHEET OFCCALCULATION) IN MEMORY10
3.4. EWRITING ANDLLECTURE......................................................................................................................................11
3.5. FLIQUID INTERFACES...........................................................................................................................................12
4. CREATION FROM A SPREADSHEET..............................................................14
4.1. LTo classPHPEXCEL14
4.2. TheCONFIGURATION OPTIONS.............................................................................................................................14
4.2.1. Caching of a cell...........................................................................................14
4.2.2. Language / Regional settings..........................................................................................................16
4.3. EREMOVAL OF A BOOK FROM THE MEMORY..........................................................................................................17
4.4. WWORKSHEETS17
4.5. AACCESSING CELLS17
4.5.1. Setting a cell value by coordinate.........................................................17
4.5.2. Recovery of a coordinate cell........................................................................................17
4.5.3. The establishment of a cell value in the column and row..........................................................17
4.5.4. Retrieval of a cell by column and row....................................................................................18
4.5.5. Looping cells......................................................................................................................................18
4.5.6. Use of value folders to facilitate data entry.................................19
4.6. PHPEXCEL RECIPES.........................................................................................................................................20
4.6.1. Spreadsheet framework of the metadata of a................................................................................20
4.6.2. Sheet frame of a spreadsheet
4.6.3. Write a date in a cell............................................................................................................21
4.6.4. Write a formula in a cell.......................................................................................................22
4.6.5. Regional settings for the preparations in..................................................................................22
4.6.6. Write a newline character "\n" in a cell (Alt + Enter)..................................................23
4.6.7. Explicitly set the data type of a cell..........................................................................24
4.6.8. Change a cell in a URL click on......................................................................24
4.6.9. Page orientation of a spreadsheet and size.......................................................24
4.6.10. Configure page: Options scale.............................................................................................24
4.6.11. Margin page............................................................................................................................25
4.6.12. Center of a horizontal / vertical page.........................................................................................26
4.6.13. Adjustment of the header and footer for printing a spreadsheet.....................................26
4.6.14. Marco breaks the print in a row or column..............................................................................28
4.6.15. Show or hide print division lines when................................................................28
4.6.16. Set rows/columns to repeat at the top/left.
4.6.17. Specify the print area........................................................................................................29
4.6.18. Cell format...............................................................................................................................29
4.6.19. Number of formats ............................................................................................................................30
4.6.20. Align and adjust the text...............................................................................................................31

PHPExcel Developer Documentation 2

 4.6.21. Default style configuration of a book.................................................................32
4.6.22. Cell border style.....................................................................................................................32
4.6.23. Conditional formatting in a cell.............................................................................................33
4.6.24. Add a comment to a cell......................................................................................................34
4.6.25. Apply autofilter to a range of cells..............................................................................................34
4.6.26. Definition of security in a spreadsheet............................................................................34
4.6.27. Data validation framework in a cell.......................................................................................35
4.6.28. Marco column of the width of a.......................................................................................................36
4.6.29. Show or hide a column..........................................................................................................37
4.6.30. Group / scheme of a column......................................................................................................37
4.6.31. Marco measures the height of a...
4.6.32. Show or hide a row...................................................................................................................37
4.6.33. Group / outline a row.....................................................................................................................37
4.6.34. Combine / unmerge cells ................................................................................................................38
4.6.35. Insertion of rows / columns
4.6.36. Add a drawing to a spreadsheet...........................................................................................38
4.6.37. Add rich text to a cell
4.6.38. Define a named range
4.6.39. Redirect the output to the client's web browser..............................................................40
4.6.40. Adjustment of the default column width....................................................................................41
4.6.41. Default row height adjustment.............................................................................................41
4.6.42. Add a drawing to a GD sheet.................................................................................................41
4.6.43. Marco spreadsheet zoom level.............................................................................................41
4.6.44. Color sample sheet.........................................................................................................................42
4.6.45. Create spreadsheets in a workbook.....................................................................................................42
4.6.46. Hidden spreadsheets (status sheet)........................................................................................42
4.6.47. Left sheet of Law..............................................................................................................42
5. EXECUTION OF FORMULA CALCULATIONS...............................................................................43
5.1. USAND OF THE CALCULATION ENGINEPHPEXCEL........................................................................................................43
5.2. LKNOWN IMITATIONS..................................................................................................................................44
5.2.1. Operator Precedence..................................................................................................................44
5.2.2. The formulas that use numbers and text............................................................................................44
6. READING AND FILE WRITING...............................................................................................45
6.1. PHPEXCEL_IOFfactory..................................................................................................................................45
6.1.1. Create PHPExcel_Reader_IReader using PHPExcel_IOFactory.................................................45
6.1.2. Create PHPExcel_Writer_IWriter using PHPExcel_IOFactory....................................................46
6.2. EXCEL2007 (SPREADSHEETML)FILE FORMAT.......................................................................................46
6.2.1. PHPExcel_Reader_Excel2007
6.2.2. PHPExcel_Writer_Excel2007...............................................................................................................47
6.3. EXCEL5 (BIFF)FILE FORMAT................................................................................................................47
6.3.1. PHPExcel_Reader_Excel5
6.3.2. PHPExcel_Writer_Excel5.....................................................................................................................49
6.4. EXCEL2003 FFILE FORMATXML.........................................................................................................49
6.4.1. PHPExcel_Reader_Excel2003XML.....................................................................................................49
6.5. ESYMBOLIC LINK(SYLK).............................................................................................................................50
6.5.1. PHPExcel_Reader_SYLK.....................................................................................................................50
6.6. CSV (VALUES SEPARATED BY COMMAS).............................................................................................................50
6.6.1. PHPExcel_Reader_CSV
6.6.2. PHPExcel_Writer_CSV
6.7. HTML
6.7.1. PHPExcel_Writer_HTML ....................................................................................................................53
6.8. PDF
6.8.1. PHPExcel_Writer_PDF ..........................55
6.9. GFILE GENERATION OFEEXCEL FROM TEMPLATES(READ, MODIFY AND WRITE)....................................56
7. CREDITS
APPENDIX A: VALID KEYS OF APPLYFROMARRAY MATRIX FOR STYLE () ..........58

PHPExcel Developer Documentation 3

PHPExcel Developer Documentation 4
2. Prerequisites
Software requirements

The following software is required to develop with PHPExcel:

• PHP version 5.2.0 or newer

• PHP extension php_zip enabled *) php_zip extension enabled with PHP *)
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled (if not compiled in)
are not compiled in

Php_zip is only required 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
you will need the zip extension, but otherwise not. In other words, if you need PHPExcel from
Handling .xlsx or .ods files will have the zip extension, but not the other way around.

You can remove this dependency to write Excel 2007 files (not for reading) using
the PCLZip library included with PHPExcel. See the FAQ section of this document (2.4.2)
for details about this. See the frequently asked questions section of this document.2.4.2) for
more details about this. PCLZip does have a dependency on PHP’s zlib extension being enabled.
PCLZip has a dependency on zlib PHP extension being enabled.

2.1. The installation instructions

The installation is very easy: copy the contents of the classes folder to any location

in your application required.

Example:

If your web root directory is / var / www / you may want to create a subfolder called / var / www / classes / and copy the
files in that folder because they end with the files:

/var/www/Classes/[Link]
/var/www/Classes/PHPExcel/[Link]
/var/www/Classes/PHPExcel/[Link]
...

2.2. First steps

A good way to start is to run some of the tests included in the download.

Copy the 'Tests' folder next to your 'Classes' folder from above so that they end with:

/var/www/Tests/[Link]
/var/www/Tests/[Link]
...

PHPExcel Developer Documentation 5

Start running the tests, pointing your browser for the test scripts:

[Link]
The provided text appears to be a URL and does not contain translatable content.
...

Note: It may be necessary to modify the inclusion or requirement of declarations at the beginning of each of the test scripts if your 'Classes' ...
the folder above is called something else.

2.3. Useful links and tools

There are some links and tools that are very useful when developing using PHPExcel.
Please refer to thePHPExcel CodePlex pagesfor an updated version of the list below. Please,
consult theCodePlex PHPExcel pagefor an update version of the list to
continuation.

2.3.1. OpenXML / SpreadsheetML

Documentation file in format

[Link]
[Link]
OpenXML Explained e-book
[Link] [Link]
Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats
Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 Formats
file
[Link]
c6bb74cd1466&displaylang=en [Link]
941b3470-3ae9-4aee-8f43-c6bb74cd1466
OpenXML Package Explorer
[Link] [Link]

2.3.2. Frequently Asked Questions

The FAQ more pages for PHPExcel can be found at

Unable to access the content of the provided URL for translation..

It seems that there is a problem with character encoding ...

The use of UTF-8 encoding is necessary for all texts in PHPExcel.

If the encoding is different, it is possible to convert the texts with PHP's iconv() function.
Script sequence uses different encoding, so it's possible to convert the texts.
with the iconv() of PHP. PHP complains about ZipArchive not being found.
ZipArchive is found

PHP complains that ZipArchive cannot be found

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

The ZipArchive class is only necessary when reading or writing that use formats of
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. Since version 1.7.6 of the PCLZip library has
included with PHPExcel as an alternative to the ZipArchive class.

This can be activated by calling:

PHPExcel Developer Documentation 6

PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
before calling the save method of the excel2007 Writer.

You can reuse ZipArchive by calling:

PHPExcel_Settings::setZipClass(PHPExcel_Settings::ZIPARCHIVE);

Currently, this only allows you to write excel2007 files without the need for ZipArchive.
read excel2007 or OOCalc

Excel 2007 cannot open the file generated by PHPExcel_Writer_2007 in

Windows

Excel found unreadable content in '*.xlsx'. Do you want to recover the contents of this?
workbook? Do you want to recover the content of this book? If you trust the source of this workbook,
Click Yes. If you trust the source of this book, click Yes.

Some versions of the php_zip extension on Windows contain a bug when creating ZIP files.
version that can be found on[Link] work at
all times. The version that can be found in[Link]
[Link] must work at all times.

On the other hand, the update to at least PHP 5.2.9 should resolve the issue.

If you cannot find a clean copy of ZipArchive, then you can use the library
PCLZip as an alternative to writing excel2007 files, as described above.

Fatal error: domestic memory size of xxx bytes exhausted (tried to

assign bytes yyy) in zzz aaa online

PHPExcel has a 'memory' representation of a spreadsheet, so it is susceptible to

memory limitations of PHP. The memory made available to PHP can be increased by editing
the value of the memory limit directive in your [Link] file, or by using ini_set('memory_limit', '128M')
within your code (ISP permitting). The memory available for PHP can be increased by
the modification of the value of the memoryLimit directive in your [Link] file, or using iniset
'memory_limit', '128M ' inside your code (ISP allows it).

Some readers and writers are faster than others, and they also use different
memory amounts. You can find some indication of the relative performance and memory
usage for the different Readers and Writers, over the different versions of PHPExcel, on the
discussion boardYou can find some indications regarding relative performance and usage
from memory for the different readers and writers, about the different versions of PHPExcel, in
thedebate forum.

If you have already increased the memory to a maximum, or cannot change its memory limit,
sothis discussionthe forum describes some of the methods that can be applied to
reduce the memory usage of scripts using PHPExcel.

Protection in my spreadsheet is not working?

When using any of the spreadsheet protection features (for example,

broad cell protection, which prohibits deleting rows, ...), make sure to enable security
spreadsheet. This can for example be done like this: This can be done, for example, like this:

PHPExcel Developer Documentation 7

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);

Feature X does not work with PHPExcel_Reader_Y / PHPExcel_Writer_Z

Not all of the features of PHPExcel apply to all Reader class / writer.
mostly due to underlying libraries not supporting a specific feature or not having implemented a
specific feature. This is mainly due to the underlying libraries not supporting a
specific characteristic or not having applied a specific characteristic.

For example, Autofilter is not implemented in PERA Spreadsheet_Excel_writer, which is the basis of
our writer Excel5.

Little by little we are creating a list of features, along with the different readers and
writers who support them, in the "[Link]" functionality file in the folder of
documentation /.

The formulas do not seem to be calculated using the compatibility package.

Excel 2003?

This is normal behavior for the compatibility package, excel2007 shows this
Use PHPExcel_Writer_Excel5 if you really need calculated values, or force
Recalculation in Excel2003. PHPExcel_Writer_Excel5 use if you really need the values.
calculated, or of current calculation in Excel 2003.

Setting the column width is not 100% reliable.

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

The short answer is that PHPExcel uses a measurement that includes padding.
See the section: 'Column width frame' for more details.
obtain more details.

How can I use my PHPExcel framework?

There are some instructions for using PHPExcel with Joomla in theJoomla! Forum
A page of advice on usingPHPExcel in the Yii frameworkA tips page on the use of
PHPExcel in the Yii framework
The Bakeryhas some helper classes for reading and writing with PHPExcel within CakePHPThe
bakeryIt has some helper classes for reading and writing with PHPExcel in CakePHP.
IntegratingPHPExcel into Kohana[Link]
kohana-3/ andIntegration of PHPExcel and Kohana FrameworkThe integration ofPHPExcel in Kohana
[Link] andIntegration of PHPExcel and
Kohana Framework
UsingPHPExcel with Typo3UsingPHPExcel with Typo3

Joomla autoloader interferes with PHPExcel autoloader

Thanks to peterrlynch for the following advice on resolving issues between thePHPExcel autoloader
and Joomla AutoloaderThanks to peterrlynch for the following tips on resolving issues.
between thecharger and the automatic charger PHPExcel Joomla

PHPExcel Developer Documentation 8

2.3.3. Tutorials

• English PHPExcel tutorial

[Link] [Link]

• French PHPExcel tutorial

The provided text does not contain translatable content.
[Link]/tutorials/excel2007/
• Russian PHPExcel Blog Postings

Invalid input. Please provide text for translation.

The provided text is a URL and cannot be translated.

• A Japanese-language introduction to PHPExcel

The provided text is a URL and does not contain translatable [Link] introduction
from Japanese language for PHPExcel
Unable to access the content of the link provided.

PHPExcel Developer Documentation 9

3. Architecture
3.1. Schematic

3.2. Lazy Loader

PHPExcel implements an autoloader or 'lazy manager', which means it is not necessary
Include all files within PHPExcel. It is only necessary to include the initial PHPExcel.
class file, then the autoloader will include other class files as and when required, so only those files
that are actually required by your script will be loaded into PHP memory. It is only necessary to include
the initial file of the PHPExcel class, below, the autoloader includes others
class files when necessary, so that only those files that are required by
the script is loaded into PHP memory. The main benefit of this is that it reduces
the memory footprint of PHPExcel itself, so that it uses less PHP memory. The main benefit of
this reduces the memory consumption of PHPExcel itself, so it uses less memory
of PHP.

If your own scripts already define an autoload function, then this can be
overwritten by the PHPExcel autoload function. For example, if you have: For example, if
you have:

function __autoload($class) {
...
}

Do this instead:

function myAutoload($class) {
...
}
spl_autoload_register('myAutoload');

Your autoloader below will coexist with the PHPExcel autoloader.

3.3. Spreadsheet in memory

The PHPExcel architecture is built in such a way that it can serve as a spreadsheet in
memory. This means that, if one would want to create a web-based view of a spreadsheet which
communicates with PHPExcel's object model, he would only have to write the front-end code. This
means that, if someone wants to create a web-based viewpoint from a spreadsheet that is

PHPExcel Developer Documentation 10

communicate with the PHPExcel object model, you would just have to write the end of the code for
front.

Like a desktop spreadsheet, PHPExcel represents a spreadsheet that

contains one or more spreadsheets, which contain cells with data, formulas, images, ...

3.4. Writing and Reading

For its part, PHPExcel does not provide the functionality to read or write to a spreadsheet.
persisted (on the disk or in a database). To provide that functionality, readers and writers
can be used. To provide that functionality, readers and writers can be used.

By default, the PHPExcel package provides some readers and writers, among
they one for the spreadsheet in Open XML format (also known as file format
from Excel 2007).

You are not limited to the default readers and writers, as they are free to apply the
PHPExcel_Writer_IReader and the PHPExcel_Writer_IWriter interface in a custom class.

PHPExcel Developer Documentation 11

3.5. Fluid interfaces
PHPExcel supports fluent interfaces in most places.

This means that you can easily 'chain' calls to specific methods without
need for a new PHP declaration.

This means that you can easily 'chain' calls to specific methods without
need for a new PHP declaration For example, let's take the following code:

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
Test document for Office 2007 XLSX,
generated using PHP classes.
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");

PHPExcel Developer Documentation 12

$objPHPExcel->getProperties()->setCategory("Test result file");

This can be rewritten as:

$objPHPExcel->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");

Fluent use of interfaces is not necessary

Fluent interfaces have been implemented to provide convenient API programming.

The use of them
It is not mandatory, but it can make your code easier to read and maintain.

PHPExcel Developer Documentation 13

4. Creation of a spreadsheet
4.1. The PHPExcel class

The PHPExcel class is the core of PHPExcel.. It contains references to the spreadsheets listed.
in the document the security settings and metadata of the document.

To simplify the PHPExcel concept: the PHPExcel class represents the workbook.

4.2. Configuration options

Once the PHPExcel files have been included in the script, but before creating instances of a
PHPExcel object or load a workbook file, there are a series of configuration options that you
It can be established that they will affect the subsequent behavior of the script.

4.2.1. The caching of a cell

PHPExcel uses an average of around 1k/cell in its worksheets, large books such
fast can use the available memory. the cell caching is a
mechanism that allows PHPExcel to keep cell objects smaller in size
from memory, disk, or in APC, memcache or WinCache, instead of in PHP memory. This
It allows you to reduce memory usage for large books, although at a cost of speed for
access cell data.

By default, PHPExcel still holds all cell objects in memory, but

you can specify alternatives. To enable cell caching, it must be
call the PHPExcel_Settings::setCacheStorageMethod() method, passing through the method of
cache storage that you want to use.

PHPExcel_CachedObjectStorageFactory::cache_in_memory
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

setCacheStorageMethod() will return a boolean true if successful, false in case of failure (for
example, if trying to cache to APC while APC is not enabled.

The cache is maintained separately for each individual spreadsheet, and it is created
automatically when a spreadsheet instance is created based on the method of
cached storage and the configuration that you have set up. You cannot change the
configuration settings once they have started reading a book, or have created their first sheet
of calculation.

Currently, the following caching methods are available.

PHPExcel_CachedObjectStorageFactory::cache_in_memory;
The default value. If you do not initialize any caching method, then this
It is the method that PHPExcel will use. Cell objects are maintained in PHP memory as at present.
the cell is kept in PHP memory as it is today.
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
The use of this caching method keeps the cells in PHP memory.
like a set of objects in series, which reduces the memory footprint with overhead of

PHPExcel Developer Documentation 14

minimum performance.
PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
Like cache_in_memory_serialized, this method keeps the cells in PHP memory.
like a set of objects in series, but gzip to further reduce memory usage, although
accessing to read or write in a cell is a bit slower.
PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
When using cache_to_discISAM, all cells are kept in a temporary file in
disco, with only an index for its location in that file kept in PHP memory.
This is slower than any of the cache_in_memory methods, but it significantly reduces
the memory consumption.
The temporary disk file is automatically deleted when the script finishes.
PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
Just like cache_to_discISAM, using cache_to_phpTemp all the cells are carried out.
in PHP: // temp I/O current, with only one index for its location keeps in memory
PHP. In PHP, php://memory stores a container of data in memory: php://temp
behaves similarly, but uses a temporary file to store the data when a
memory limit is reached certain. The default is 1 MB, but you can change this when initializing
cache_to_phpTemp. The default value is 1 MB, but you can change this when the
cache_to_phpTemp initialization.

PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp
$cacheSettings = array( ' memoryCacheSize ' => '8MB'
);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

The php: // temporary file is automatically deleted when the script ends.
PHPExcel_CachedObjectStorageFactory::cache_to_apc;
When cache_to_apc is used, cell objects are kept inaAPC with only one index
keeps in PHP memory to determine that the cell exists. By default, a time of
a cache wait of 600 seconds is used, which should be sufficient for most of the
applications: although it is possible to change this when initializing cache_to_APC.

PHPExcel_CachedObjectStorageFactory::cache_to_APC
$cacheSettings = array( 'cacheTime' => 600
);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

When the script sequence ends, all entries will be removed from APC, with
independence of the cacheTime value, so it cannot be used for storage
persistent using this mechanism.

PHPExcel_CachedObjectStorageFactory::cache_to_memcache

PHPExcel Developer Documentation 15

When cache_to_memcache is used, cell objects are kept in memcache.2with just a
index keeps in PHP memory to determine that the cell exists. In this way
By default, PHPExcel looks for a memcache server on localhost on port 11211. It also
set a memcache timeout limit of 600 seconds. If you are running memcache
on a different server or a port, then you can change these default values
when initializing cache_to_memcache:

PHPExcel_CachedObjectStorageFactory::cache_to_memcache
$cacheSettings = array( 'memcacheServer' => 'localhost',
'memcachePort' => 11211,
'cacheTime' => 600
);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

When the script sequence ends, all entries will be removed from memcache.
regardless of the cacheTime value, so it cannot be used for storage
persistent using this mechanism.

PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
When cache_to_wincache is used, cell objects are kept in WinCache.3with just a
index is maintained in PHP memory to determine that the cell exists. by default, a time
A WinCache cache wait of 600 seconds is used, which should be sufficient for most.
from the applications: although it is possible to change this initialization cache_to_wincache
when.

PHPExcel_CachedObjectStorageFactory::cache_to_wincache
$cacheSettings = array( 'cacheTime' => 600
);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

When the script sequence ends, all entries will be removed from WinCache.
regardless of the cacheTime value, it cannot be used for storage
persistent using this mechanism.

4.2.2. Language / Regional settings

Some localization elements have been included in PHPExcel. You can set a
regional configuration when changing the settings. To set the regional configuration of
Brazilian Portuguese should be used:

pt_br
PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
Unable to set locale to
}

If the Brazilian Portuguese language files are not available, then the Portuguese ones are.
it will be enabled in its place: if the Portuguese language files are not available, then setlocale
() method will return an error, and the American English (es_es) setting is used throughout. More
details of the available functions once the regional settings have been established, including
a list of the languages and regional settings currently supported can be found at
the section4.5.5Locale Settings for Formulae. Regional settings for preparations.

PHPExcel Developer Documentation 16

4.3. Removal of a book from memory
The PHPExcel object contains circular references (for example, the workbook is related to the
spreadsheets and the worksheets are linked to your parents' book), which causes
problems when PHP tries to delete objects from memory when unset (), or at the end of a
function when they are in the local scope. The result of this is 'memory losses', which
It is easy to use a limited amount of PHP memory.

This can only be solved manually: if it is necessary to disassemble a book, then also
You have to "break" these circular references before doing it. PHPExcel provides the
disconnectWorksheets() method for this purpose. PHPExcel provides the disconnectWorksheets()
for this purpose.

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

4.4. Worksheets
A spreadsheet is a collection of cells, formulas, images, graphs, ... It has all the
data you want to represent as a spreadsheet.

4.5. Accessing cells

Accessing cells in a PHPExcel spreadsheet should be quite simple. This topic is
they list some of the options to access a cell.

4.5.1. Setting a cell value by coordinate

Setting a cell value by coordinates can be done using the
setCellValue method of the spreadsheet.

$objPHPExcel->getActiveSheet()->setCellValue('B8', 'Some value');

4.5.2. Recovery of a coordinate cell

To retrieve the value of a cell, the first cell must be retrieved from the spreadsheet.
using the getCell method. The value of a cell can be read again with the following line
of code:

$objPHPExcel->getActiveSheet()->getCell('B8')->getValue();

If you need the calculated value of a cell, use the following code. This is explained in
4.4.35.

$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue();

4.5.3. The establishment of a cell value in the column and the row
Setting a cell value by coordinates can be done using a spreadsheet.
desetCellValueByColumnAndRow method.

Set cell B8
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'Some value');

PHPExcel Developer Documentation 17

4.5.4. Recovery of a cell by column and row
To recover the value of a cell, the first cell must be retrieved from the spreadsheet.
using the getCellByColumnAndRow method. The value of a cell can be read again with
the following line of code:

// Get cell B8
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getValue();

If you need the calculated value of a cell, use the following code. This is explained in
4.4.35

Get cell B8
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getCalculatedValue();

4.5.5. Looping cells

Looping cells using iterators

The easiest way to loop cells is by using iterators. By using iterators, one can use
foreach for spreadsheet loop, rows and cells.

The following is an example where all the values of a spreadsheet are read and
shows them in a table.

<?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("[Link]");
$objWorksheet = $objPHPExcel->getActiveSheet();

echo '<table>' . "\n";

foreach ($objWorksheet->getRowIterator() as $row) {
echo '<tr>' . "\n";

$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // This loops through all the
cells
Although it is not established.

By default,
only the cells
That they are one
Iterate.
foreach ($cellIterator as $cell) {
echo '<td>' . $cell->getValue() . '</td>' . "\n";
}

echo '</tr>' . "\n";

}
</table>
?>

Please note that we have set the cell iterator setIterateOnlyExistingCells to () in

false. This causes the loop to iterate through all the cells, even though they were not set beforehand.

PHPExcel Developer Documentation 18

 The cell repeater will return null like the cell if it is not set in the sheet.
calculation.

Marco cell iteratorsetIterateOnlyExistingCells la() for loop false all the

cells in the spreadsheet that may be available at that moment. This will create new
Cells if the increased use of memory is necessary! It should only be used if it is intended for all
the loop cells that will eventually be available.

Cell loop by means of indices

You can use the ability to access cell values and the column index row.
like (0,1) instead of 'A1' to read and write values of the cells in loops.

Note: The index of the PHPExcel column is based on 0, while the row index
It's based on 1. That means 'A1' ~ (0,1) It means 'A1' ~ (0,1)

Below is an example where all the values of a spreadsheet are read and the
show in a table.

<?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("[Link]");
$objWorksheet = $objPHPExcel->getActiveSheet();

$objWorksheet->getHighestRow(); // example 10
$highestColumn = $objWorksheet->getHighestColumn(); // example 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); example

5

echo '<table>' . "\n";

for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {

<td>
</td>
}

echo '</tr>' . "\n";

}
</table>
?>

4.5.6. Use of value folders to facilitate data entry

Internally, PHPExcelPHPExcel_Cell_IValueBinder uses a default implementation
(PHPExcel_Cell_DefaultValueBinder) to determine the data types of the data
introduced through a cells setValue() method.

Optionally, the default behavior of PHPExcel can be modified, which

facilitates data entry. For example, a classPHPExcel_Cell_AdvancedValueBinder
is present. Automatically converts percentages and entry dates as strings
with the correct format, also set the style of the cell information. In the example
The following shows how to set the value folder in PHPExcel:

PHPExcel
require_once '[Link]';

PHPExcel Developer Documentation 19

PHPExcel_Cell_AdvancedValueBinder
require_once 'PHPExcel/Cell/[Link]';

PHPExcel_IOFactory
require_once 'PHPExcel/[Link]';

// Set the value binder folderPHPExcel_Cell::setValueBinder( new

PHPExcel_Cell_AdvancedValueBinder() );

Create a new PHPExcel object

$objPHPExcel = new PHPExcel();

// ...

Add some data, resembling some different types of data

Percentage value:
$objPHPExcel->getActiveSheet()->setCellValue('B4', '10%');
Convert to 0.1 and percentage cell style sets
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Date/time value:');
December 21, 1983
Convert to the date and set the cell style date format

Creating your own binding value is easy.

To link advanced value is necessary, you can implement the interface

PHPExcel_Cell_IValueBinder or expand the classes or
PHPExcel_Cell_AdvancedValueBinderPHPExcel_Cell_DefaultValueBinder.

4.6. PHPExcel recipes

The following pages offer you some recipes that extensively use PHPExcel. Please be
It states that these do not provide complete documentation on certain PHPExcel functions.
from the API, but just a hit to get started. If you need the specific functions of the API, consult
the API documentation.

For example,4.4.7 Setting a worksheet's page orientation and sizecovers setting a page orientation
to [Link] orientation framework and the size of a spreadsheetit covers establishing a
A4 page orientation. Other paper formats, such as US Letter, are not covered in this.
document, but in the PHPExcel API documentation.

4.6.1. Spreadsheet framework of the metadata of a

PHPExcel provides an easy way to set up a metadata spreadsheet, using the

document access property. spreadsheet metadata can be useful for finding a
specific document in a file repository or a document management system. For
For example, Microsoft SharePoint uses the document's metadata to search for a document.
specific in the document its lists.

The metadata spreadsheet Marco is made as follows:

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
Office 2007 XLSX Test Document
Test document for Office 2007 XLSX,
generated using PHP classes.
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");

PHPExcel Developer Documentation 20

4.6.2. Marco activa de hoja de una hoja de cálculo
The following line of code sets the active sheet index to the first sheet:

$objPHPExcel->setActiveSheetIndex(0);

4.6.3. Write a date in a cell

In Excel, dates are stored as numerical values counting the number of days that have passed.
since 1/1/1900. For example, the date '2008-12-31' is represented as 39813. You can
verify this in Microsoft Office Excel by entering that date in a cell and
after changing the number format to "General" so that the numeric value is revealed.

Writing a date value in a cell consists of two lines of code. Select the method
what suits him best. Here are some examples:

/* PHPExcel_Cell_AdvanceValueBinder needed for this example */

require_once 'PHPExcel/Cell/[Link]';

As the MySQL date and time '2008-12-31'

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$objPHPExcel->getActiveSheet()->setCellValue('D1', '2008-12-31')
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()-
setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)

// PHP-time (Unix time)

int(1230681600)
$objPHPExcel->getActiveSheet()->setCellValue('D1',
PHPExcel_Shared_Date::PHPToExcel($time)
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()-
>setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)

Excel in time
$objPHPExcel->getActiveSheet()->setCellValue('D1', 39813)
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()-
setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)

The previous methods for entering a date all yield the same result.
PHPExcel_Style_NumberFormat provides a large number of predefined date formats.

Notes:
See the section "Using value folders to facilitate data entry" for more.
Information about the AdvancedValueBinder used in the first example.

2. In previous versions of PHPExcel up to and including 1.6.6, when a cell had a

number code format like the date, it was possible to enter a date directly
through an integer of PHP in time without having to convert them to format of
Excel date. Starting with PHPExcel 1.6.7 this is no longer supported. Starting from
PHPExcel 1.6.7 is no longer supported.

Excel can also operate on a 1904 date system (default for workbooks
saved on Mac). Normally, you don't have to worry about this when you
use PHPExcel.

PHPExcel Developer Documentation 21

4.6.4. Write a formula in a cell
Within the Excel file, formulas are always stored as they would appear in a version in
Microsoft Office Excel's English, and PHPExcel handles all formulas internally.
format. This means that the following rules are expected:

decimal separator is '.' (period)

Function argument separator is ',' (comma)
(coma)
Matrix row separator is ';' (semicolon) row separator of Matrix is ',' (comma)
English function names must be used

This is regardless of the language version of Microsoft Office Excel that may have been.
used to create the Excel file.

At the end of the book, it opens by the user, Microsoft Office Excel will take care of displaying the formula
according to the language applications. Translation is taken care of by the application!
application load!

The following line of code writes the formula "= IF (C4> 500, " profit ", " loss ")" in cell B8.
Note that the formula must start with "=" for PHPExcel to recognize this as
a formula.

$objPHPExcel->getActiveSheet()->setCellValue('B8','=IF(C4>500,"profit","loss")');

Cells from formula A can be read again with the following line of code:

$formula = $objPHPExcel->getActiveSheet()->getCell('B8')->getValue();

If you need the calculated value of a cell, use the following code. This is explained in
4.4.35.

$value = $objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue();

4.6.5. Regional configuration for the preparations in

Some localization elements have been included in PHPExcel. You can set a
regional settings when changing the settings. To set the regional configuration of
Russia must use:

ru
PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
Unable to set locale to
}

If the language of the Russian files is not available, the setlocale() method will return an error, and
The English setting will be used everywhere.

Once you have set a regional setting, you can translate a formula from within it.
English coding.

$formula = $objPHPExcel->getActiveSheet()->getCell('B8')->getValue();
translatedFormula
PHPExcel_Calculation::getInstance()->_translateFormulaToLocale($formula);

PHPExcel Developer Documentation 22

You can also create a formula that uses function names and argument separators
appropriate for the defined regional configuration, and then translate it into English before setting
the value of the cell:

=DAYS360(DATE(2010,2,5),DATE(2010,12,31),TRUE)
$internalFormula
PHPExcel_Calculation::getInstance()->translateFormulaToEnglish($formula);
$objPHPExcel->getActiveSheet()->setCellValue('B8',$internalFormula);

Currently, the translation only translates the formula names of the functions, the constants
TRUE and FALSE, and the argument separators of the function.

Currently, the following regional settings are supported:

Language Locale Code
Czech Czech Cs
Danish Danish Yes
German German Of
Spanish Spanish It is
Finnish Finland Fi
French French Fr
Hungarian Hungarian Hu
Italian Italian It
Dutch Dutch Nl
Norwegian Norwegian No
Polish Polish language Pl
Portuguese Portuguese
Brazilian Portuguese Brazilian Portuguese
Russian русский язык ru
Swedish Swedish Sv

4.6.6. Write a newline character "\n" in a cell (Alt + Enter)

In Microsoft Office Excel, you can achieve a line break in a cell by pressing ALT + 'Enter'.
When this is done, it automatically becomes 'wrap text' for the cell.

Here is how to achieve this in PHPExcel:

hello\nworld
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

Tip
Learn more about cell formatting using getStyle() elsewhere.

Tip
[Link] automatically enables "wrap text" of the cell when viewed a
newline character in a string that will be inserted into a cell. Just like Microsoft Office Excel.
just like Microsoft Office Excel. Try this:

require_once 'PHPExcel/Cell/[Link]';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

hello world

Read more about [Link] elsewhere.

PHPExcel Developer Documentation 23

4.6.7. Explicitly set the data type of a cell

You can explicitly set a cell's data type using the setValueExplicit method of
the cell, or the setCellValueExplicit method of a spreadsheet. Here is an example: of the
United Nations:

$objPHPExcel->getActiveSheet()->getCell('A1')->setValueExplicit('25',
PHPExcel_Cell_DataType::TYPE_NUMERIC;

4.6.8. Change a cell in a URL to click on

You can make a cell click on a URL by using the

establishment of your property hyperlink:

$objPHPExcel->getActiveSheet()->setCellValue('E26', '[Link]');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()-
>setUrl('[Link]

If you want to create a hyperlink to another spreadsheet or cell, use the following
code:

$objPHPExcel->getActiveSheet()->setCellValue('E26', '[Link]');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()-
>setUrl("sheet://'Sheetname'!A1");

4.6.9. Marco orientation of the page, a spreadsheet, and the size

Page orientation of a spreadsheet and the size can be adjusted using the
following lines of code:

$objPHPExcel->getActiveSheet()->getPageSetup()-
setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()-
setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

Please note that there are additional page options available. Please consult the
API documentation for all possible options.

4.6.10. Configure page: Options scale

The page scale configuration options in PHPExcel are directly related to the
scale options in the 'Page Setup' section of the dialog as shown in the illustration.

The default values in PHPExcel correspond to the default values of MS Office Excel, such as
it is shown in the illustration

PHPExcel Developer Documentation 24

method initial value calling method will Note
trigger
setFitToPage(...) false -
setScale(...) 100 setFitToPage(false)
setFitToWidth(...) 1 setFitToPage(true) value 0 means
do-not-fit-to-
width
setFitToHeight(...) 1 setFitToPage(true) value 0 means
do not fit to
height

Example

This is how to fit to a page of width by infinite height pages:

$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

As you can see, it is not necessary to call asetFitToPage (true) from setFitToWidth.
(...) ysetFitToHeight (...) causes this.

If you use setFitToWidth(), you should generally also specify setFitToHeight.

() explicitly as in the example. Be careful to rely on the initial values.
This is especially true if you are upgrading from PHPExcel 1.7.0 to 1.7.1, where the
default values for height adjustment and width adjustment have changed from 0 to 1.

4.6.11. Margin page

To set the page margins of a spreadsheet, use the code:

PHPExcel Developer Documentation 25

$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

Please note that the margin values are specified in inches.

4.6.12. Center of a horizontal / vertical page

To center a page horizontally / vertically, you can use the following code:

$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

4.6.13. Adjustment of the header and footer for printing a sheet

of calculation

Marco printing sheet of a header and footer can be done using the following
lines of code:

&C&HPlease treat
this document as confidential!
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B'
$objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N';

Substitution and format codes (starting from y) can be used within headers and footers
of the page. There is no necessary order in which these codes must appear.

The first appearance of the following codes converts the ON format, the second appearance
turn OFF again:

• Strikethrough
• Superscript
• Subscript

PHPExcel Developer Documentation 26

Superscript and subscript cannot be both ON at the same time. Whatever happens first wins and the
another is overlooked, while the first is ON.

The following codes are compatible with excel2007:

&L Code of the 'left section' (there are three headers and
footer locations, the "left", "center", and "right".
When two or more occurrences of this section marker exist, the
the content of all the markers is concatenated, in the order of
appearance, and is placed on the left side.
&P Current page code #
&N Total pages code
&font size Code for "the font size of text", where the size of the
source is a font size in points.
&K Text font color code

RGB color is specified as RRGGBB

The color theme is specified as TTSNN where TT is the Id.
theme color, S is '+' or '-' of / shadow value tint, NN is the
tone / shadow value.

&S Code for "strikethrough text" on / off

&X Code for 'text superscript' on / off
&Y Code for 'text subscript' on / off
on / off
&C Code of the 'central section'. When two or more occurrences of this
section markers exist, the content of all markers is
they are concatenated, in the order of appearance, and placed in the section
central.
&D Code for the 'date'
&T Code for the 'time'
&G Code for the 'background image'

Please make sure to add the image to the header / footer

page:

$objDrawing = new
PHPExcel_Worksheet_HeaderFooterDrawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setPath('./images/phpexcel_logo.gif');
$objDrawing->setHeight(36);
$objPHPExcel->getActiveSheet()->getHeaderFooter()-
>addImage($objDrawing,
PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT
&U Code for 'underlined text only'
&E Code of the 'double underline'
&R Code of the "right section". When two or more appearances of
this section marker exists, the content of all
markers are concatenated, in the order of appearance, and are placed in the
right section.
&Z Code for "the file path of this book"
&F Code for "filename of this book"
&A Code of the 'sheet name tag'
&+ Code to add to the page #
&- Code to subtract from the page #

PHPExcel Developer Documentation 27

font name,font type text source name
In the name of the font and the font type are strings that specify
the name and type of the font, separated by a comma. When
a hyphen appears in the name of the source, which means "no
specified". Both the name of the font and the type of font can
localized values.
Bold Code for 'bold'
&B Code for 'bold'
&"-,Regular Regular font style code
-Italic Italic font style code
&I Italic font style code
Bold Italic Code for 'bold italic'
&O Style scheme code
&H Shadow style code

Tip

The table above the codes may seem overwhelming the first time you are trying to find the
Manner of writing some header or footer. Fortunately, there is an easier way.
Microsoft Office Excel does the work for you.

For example, create an xlsx file in Microsoft Office Excel where the header and footer are inserted.
From the page as you wish using the programs in the interface itself. Save file as [Link].
Save file as [Link]. Now, take that file and read off the values using PHPExcel as
Now, take the file and read the values using PHPExcel as follows:

$objPHPexcel = PHPExcel_IOFactory::load('[Link]');
$objWorksheet = $objPHPexcel->getActiveSheet();
var_dump($objWorksheet->getHeaderFooter()->getOddFooter());
var_dump($objWorksheet->getHeaderFooter()->getEvenFooter());
var_dump($objWorksheet->getHeaderFooter()->getOddHeader());
var_dump($objWorksheet->getHeaderFooter()->getEvenHeader());

It reveals the codes for even/odd header and footer. Experienced users can
find it easier to rename [Link] to [Link], decompress it, and inspect
directly the content of the relevant sheets / [Link] to find the codes of
header and footer.

4.6.14. Marco breaks the print in a row or column

To set a print break, use the following code, which sets a break of
line in row 10.

$objPHPExcel->getActiveSheet()->setBreak('A10', PHPExcel_Worksheet::BREAK_ROW);

The following line of code sets a print break in column D:

$objPHPExcel->getActiveSheet()->setBreak('D10',
PHPExcel_Worksheet::BREAK_COLUMN );

4.6.15. Show or hide print division lines when

To show/hide gridlines when printing, use the following code:

$objPHPExcel->getActiveSheet()->setShowGridlines(true);

PHPExcel Developer Documentation 28

4.6.16. Set rows/columns to repeat at the top/left
PHPExcel can repeat specific rows / cells at the top and left of a page.
The following code is an example of how to repeat rows 1 to 5 on every printed page of a sheet.
of specific calculation:

$objPHPExcel->getActiveSheet()->getPageSetup()-
setRowsToRepeatAtTopByStartAndEnd(1, 5);

4.6.17. Specify the print area

To specify the print area spreadsheet, use the following code:

$objPHPExcel->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5');

You can also print multiple areas on a single spreadsheet:

$objPHPExcel->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5,G4:M20');

4.6.18. Cell format

A cell can have the font format, border, fill, ... the style information. By
for example, you can adjust the background color of a cell to red, aligned to the right, and the
thick border style and black. Let's make it in cell B2:

The provided text appears to be a fragment of code and does not require translation.
setARGB(PHPExcel_Style_Color::COLOR_RED);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()-
setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getTop()-
setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getBottom()-
setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getLeft()-
>setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getRight()-
setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()-
setFillType(PHPExcel_Style_Fill::FILL_SOLID);
The provided text appears to be a code snippet rather than a translatable text.
setARGB('FFFF0000');

Starting from PHPExcel 1.7.0, getStyle() also accepts a range of cells as a parameter.
for example, you can set a red background in a range of cells:

$objPHPExcel->getActiveSheet()->getStyle('B3:B7')->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setARGB('FFFF0000');

Tip
It is recommended to set the style of the cells at once, using getStyle for example ('A1: M500), instead
to cultivate the cells individually in a loop. This is much faster in comparison
with a loop through the cells and the style individually.

PHPExcel Developer Documentation 29

There is also an alternative way to set styles. The following code defines the style of
a bold cell, right alignment, thin top border and a gradient fill:

array(
array
'bold' => true,
),
'alignment' => array(
PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
),
array
array
PHPExcel_Style_Border::BORDER_THIN
),
),
array
PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR
'rotation' => 90,
'startcolor' => array(
FFA0A0A0
),
'endcolor' => array(
FFFFFFFF
),
),
);

$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);

Or with a range of cells:

$objPHPExcel->getActiveSheet()->getStyle('B3:B7')->applyFromArray($styleArray);

This alternative method using matrices should be faster in terms of execution every time.
it's going to configure more than one style property. But the difference can hardly be measured.
less than there are different styles in the book.

Before PHPExcel 1.7.0, duplicateStyleArray() is the recommended method for crafting a

range of cells, but this method has fallen into disuse since getStyle() began to
accept a range of cells.

4.6.19. Number of formats

Often, formatting numbers in Excel is desired. For example, you may want a separator.
of miles, plus a fixed number of decimals after the decimal separator. Or maybe you want a
a little the numbers to be filled with zeros.

In Microsoft Office Excel, those who are familiar with selecting a number format of
"Cell Format" dialog. Here are some available predefined number formats.
including some of the dates. The dialogue is designed in such a way that you do not have
to interact with the underlying code number in raw format unless needed a
custom number format.

In PHPExcel, you can also apply various predefined number formats. Example:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

PHPExcel Developer Documentation 30

This format will be a number for example, 1.587,2 so it is displayed as 1,587.20 when opened.
book in Microsoft Office Excel. (Depending on the configuration of the decimal separators and
thousands of Microsoft Office Excel that can appear as 1,587.20

You can get exactly the same thing as the above by using this:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('#,##0.00');

In Microsoft Office Excel, as well as in PHPExcel, you will need to interact with the RAW format
numerical codes whenever you need some special number format. Example:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('[Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0');

Another example is when it is desired that the numbers zero are filled with zeros on the left with a
fixed length:

$objPHPExcel->getActiveSheet()->getCell('A1')->setValue(19);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->setFormatCode('0000'); // will show as 0019 in Excel

Tip
The rules for composing a number format code in Excel can be quite
complicated. Sometimes, you know how to create a number format in Microsoft Office Excel, but
They don't know what the code number of the underlying format looks like. How did you find it?

Readers send with PHPExcel come to the rescue. Upload your workbook template using, for example
Excel 2007 reader to reveal the number format code. Example of how to read a code of
Number format for cell A1:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('[Link]');
var_dump($objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
->getFormatCode());

Advanced users may find it easier to inspect the formatting code.

number directly by renaming [Link] to [Link], unzip, and in
search for the relevant piece of exploitation XML code, the number formatting code inxl
[Link].

4.6.20. Align and adjust the text

We are going to set the vertical alignment of the top of cells A1:D4

$objPHPExcel->getActiveSheet()->getStyle('A1:D4')
->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

Here is how to adjust the text:

$objPHPExcel->getActiveSheet()->getStyle('A1:D4')
->getAlignment()->setWrapText(true);

PHPExcel Developer Documentation 31

4.6.21. Default Style Configuration of a Book
It is possible to set the default style of a book. Let's set the font.
default Arial size 8:

$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(8);

4.6.22. Cell border style

In PHPExcel, it is easy to apply different borders to a rectangular selection. This is how
apply a thick red border color scheme around the cells B2: G-8.

array(
'borders' => array(
'outline' => array(
PHPExcel_Style_Border::BORDER_THICK
'color' => array('argb' => 'FFFF0000'),
),
),
);
$objWorksheet->getStyle('B2:G8')->applyFromArray($styleArray);

In Microsoft Office Excel, the previous operation would correspond to selecting the cells B2:
from the G-8, the launch of the style dialog, the choice of a thick red border, and
click on the diagram 'border component'.

Please note that the border lines apply to the rectangular selection B2: of the G-8 in
as a whole, not in each individual cell.

You can achieve any border effect by using only the five basic borders and
operating in a single cell at one moment:

Array key Maps to property

left getLeft()
right getRight()
top getTop()
bottom getBottom()
diagonal getDiagonal()

shortcut to the additional borders very useful, as in the example above the.
These are the available shortcut borders:

Array key Maps to property

allborders getAllBorders()
outline getOutline()
inside getInside()
vertical getVertical()
horizontal getHorizontal()

An overview of all the border shortcuts can be seen in the following

PHPExcel Developer Documentation 32

 image:

If at the same time we set all borders, for example, and vertical, then we have
superposition of the borders, and one of the components has to prevail over the other, where not
borders overlap. In PHPExcel, from weakest to strongest of the borders, the list is
the following: all borders, contour / interior, vertical and horizontal, left / right / top /
down / diagonal.

This hierarchy of the border can be used to achieve different effects in a way
easy.

4.6.23. Conditional formatting in a cell

A cell can apply conditional formatting based on a specific rule. For example, it can
you can adjust the background color of a cell to red if its value is below zero, and green if its
value is zero or more.

A set of conditional styling rules can be established for a cell using the following
code:

$objConditional1 = new PHPExcel_Style_Conditional();

$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
$objConditional1->addCondition('0');
$objConditional1->getStyle()->getFont()->getColor()-
setARGB(PHPExcel_Style_Color::COLOR_RED);
$objConditional1->getStyle()->getFont()->setBold(true);

$objConditional2 = new PHPExcel_Style_Conditional();

$objConditional2->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional2-
setOperatorType(PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL);
$objConditional2->addCondition('0');
$objConditional2->getStyle()->getFont()->getColor()-
>setARGB(PHPExcel_Style_Color::COLOR_GREEN);
$objConditional2->getStyle()->getFont()->setBold(true);

$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('B2')
getConditionalStyles();
array_push($conditionalStyles, $objConditional1);
array_push($conditionalStyles, $objConditional2);

PHPExcel Developer Documentation 33

The provided text is incomplete and does not contain translatable content.
setConditionalStyles($conditionalStyles);

If you want to copy the set of rules to other cells, you can duplicate the style object:

$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()-
>getStyle('B2'), 'B3:B7' );

4.6.24. Add a comment to a cell

To add a comment to a cell, use the following code. The following example adds a
comment on cell E11:

$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()-
PHPExcel:

$objCommentRichText->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getComment('E11')->getText()-
translatedText

Total
amount on the current invoice, excluding VAT.

4.6.25. Apply autofilter to a range of cells

To apply an AutoFilter to a range of cells, use the following code:

$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');

Make sure to always include the full range of filter!

Excel is compatible with adjusting only the title row, but it is not a good practice ...

4.6.26. Definition of security in a spreadsheet

Excel offers 3 levels of 'protection': document security, sheet security and the
cell security.

• The document protection allows you to set a password on a spreadsheet.

completes, allowing changes to be made only when the password is entered.
• The safety sheet offers other security options: it may not allow the insertion of
rows in a specific sheet, the classification does not allow,

PHPExcel Developer Documentation 34

 • the cell security offers the option to lock/unlock a cell phone, as well as
show / hide the internal formula

An example of the security of adjustment documents:

$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");

An example of a security configuration spreadsheet:

$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

An example of cell security configuration:

$objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(
PHPExcel_Style_Protection::PROTECTION_UNPROTECTED
);

Make sure to activate sheet protection if you need any of the

spreadsheet protection features;

This can be done using the following code $ objPHPExcel-> getActiveSheet () ->
getProtection () -> setSheet (true)

4.6.27. Data validation framework in a cell

Data validation is a powerful feature of excel2007. It allows specifying a filter.
for the input of data that can be inserted into a specific cell. This filter can be a
distribution area (that is, the value must be between 0 and 10), a list (in value must be
selected from a list), ...

The following code snippet only allows a number between 10 and 20 that is entered in cell B3:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')
->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
Input error
Number is not allowed!
Allowed input
Only numbers between 10 and 20 are allowed.
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);

The following code snippet only allows one selected element from a data list that
should appear in cell B3:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')
->getDataValidation();

PHPExcel Developer Documentation 35

$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
Input error
Value is not in list.
Pick from list
Please pick a value from the drop-down list.
$objValidation->setFormula1('"Item A,Item B,Item C"');

When using a data validation list like the above, make sure to put the
list between "and" that separates the elements with a comma (,) .
It is important to remember that any string that participates in an Excel formula is
allows a maximum of 255 characters (not bytes). This sets a limit on the number of
elements that can be in the string "Point A, Point B, Point C". Therefore, normally
it is a better idea to write the article directly in the values in some range of cells, for
example A1: A3, and instead of using, for example, $ objValidation-> setFormula1 ('Sheet 3! $ A
$ 1: $ A $'. Another benefit is that the topic can contain the own values of the comma ','
character.

If you need data validation in multiple cells, you can clone the set of rules.

$objPHPExcel->getActiveSheet()->getCell('B8')->setDataValidation(clone
$objValidation);

4.6.28. Marco column of the width of a

A column width can be set using the following code:

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

If you want PHPExcel to perform an automatic width calculation, use the following code.
PHPExcel will approach the column with the width of the widest value column.

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

The measurement for the column width in

PHPExcel does not correspond exactly to the
measure with which it can be used in Microsoft
Office Excel. column widths are difficult to
dealing in Excel, and there are various measures for it
column width.

Interior width in units of

characters (for example 8.43 this is
probably what you are
familiar with in Excel

2) total width in pixels (for example, 64 pixels)

3) total width in character units (for example, 9.140625, the value -1 indicates width of
disconnection)

PHPExcel Developer Documentation 36

 PHPExcel always operates with 3) "total width in character units" which is actually the
the only value that is stored in an Excel file, therefore, the most reliable measure.
Unfortunately, Microsoft Office Excel does not present this measure.
On the other hand, measures 1) and 2) are calculated by the application when the file is opened and these
values are presented in various dialogues and advice from the tool.
The character width unit is the width of a '0' (zero) glyph in the default font.
books. Therefore, the width of the columns is measured in character units in two different books.
it can only be compared if they have the same default source book.
If you have an Excel file and the need to know the width of the columns in measurement.
3), you can read the Excel file with PHPExcel and echo the retrieved values.

4.6.29. Show or hide a column

To set up the visibility spreadsheet of a column, you can use the code
next. The first line explicitly shows column C, the second line of the column
hide D.

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);

4.6.30. Group / scheme of a column

To group / scheme a column, you can use the following code:

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);

You can also contract the column. Please note that you should also set the column.
invisible, otherwise the drop will not be visible in Excel 2007.

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);

Please refer to the part 'group / outline a row' of a complete example in the collapse.

You can indicate PHPExcel to add a summary to the right (by default), or to the
leftThe following code adds the summary to the left:

$objPHPExcel->getActiveSheet()->setShowSummaryRight(false);

4.6.31. Marco measures the height of a

A row height can be adjusted using the following code:

$objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(100);

4.6.32. Show or hide a row

To set up the row visibility spreadsheet, you can use the following code.
the following example hides row number 10.

$objPHPExcel->getActiveSheet()->getRowDimension('10')->setVisible(false);

4.6.33. Group / sketch a row

PHPExcel Developer Documentation 37

To group / outline a row, you can use the following code:

$objPHPExcel->getActiveSheet()->getRowDimension('5')->setOutlineLevel(1);

You can also collapse the row. Note that you must also set the invisible line.
the opposite the drop will not be visible in Excel 2007.

$objPHPExcel->getActiveSheet()->getRowDimension('5')->setCollapsed(true);
$objPHPExcel->getActiveSheet()->getRowDimension('5')->setVisible(false);

Here is an example that collapses rows 50 to 80:

for ($i = 51; $i <= 80; $i++) {

$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "FName $i");
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'LName $i');
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, "PhoneNo $i");
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, "FaxNo $i");
$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, true);

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getRowDimension($i)->setVisible(false);
}
$objPHPExcel->getActiveSheet()->getRowDimension(81)->setCollapsed(true);

You can indicate PHPExcel to add a summary of the collapsible rows (by default) next.
the superior. The following code adds the previous summary:

$objPHPExcel->getActiveSheet()->setShowSummaryBelow(false);

4.6.34. Combine / unmerge cells

If you have a large piece of data that you want to display in a spreadsheet, you can
combine two or more cells together to become one cell. This can be done by the
next code:

$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

Extraction of a merge can be done using the method unmergeCells:

$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');

4.6.35. Insertion of rows / columns

You can insert or delete rows/columns at a specific position. The following code inserts two
new rows, just before row 7:

$objPHPExcel->getActiveSheet()->insertNewRowBefore(7, 2);

4.6.36. Add a drawing to a spreadsheet

A drawing is always represented as an independent object that can be added to a sheet.
of calculation. Therefore, you must first create instances of aPHPExcel_Worksheet_Drawing
new, and relinquish their properties of significant value:

$objDrawing = new PHPExcel_Worksheet_Drawing();

$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/[Link]');

PHPExcel Developer Documentation 38

$objDrawing->setHeight(36);

To add the logo to the spreadsheet, use the following code snippet. PHPExcel
create the link between the drawing and the spreadsheet:

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

You can set the properties of numerous in a drawing, here are some examples:

$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/[Link]');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);

4.6.37. Add rich text to a cell

Addition of rich text to a cell can be done using instances of PHPExcel_RichText.
Here is an example, which creates the rich following text string:

This invoice is payable within thirty days following the end of the month unless otherwise stated.
specify the opposite on the invoice.

$objRichText = new PHPExcel_RichText();

This invoice is

payable within thirty days after the end

of the month
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new
PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN );

, unless otherwise specified in

the invoice.

$objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);

4.6.38. Define a named range

HPExcel supports the definition of named ranges. These can be defined using the following
code:

Add some data

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Firstname:');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Lastname:');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Maarten');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Balliauw');

Define named ranges

$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('PersonFN', $objPHPExcel-
>getActiveSheet(), 'B1') ;
Invalid input for translation.
>getActiveSheet(), 'B2') );

PHPExcel Developer Documentation 39

Optionally, a fourth parameter can be passed for the definition of the local named range (it is
to say, it can only be used in the current spreadsheet). named ranges are global by default.

4.6.39. Redirect the output to the client's web browser

Sometimes, one really wants the output of a file in the client's browser, especially
when creating spreadsheets on the go. There are some simple steps you can take
continue to do this:

Create the PHPExcel spreadsheet

HTTP header output for the type of document you want in the output
Use the PHPExcel_Writer_ of your choice, and save to "php://output"

PHPExcel_Writer_Excel2007 uses temporary storage when writing to php://output.

By default, temporary files are stored in the working directory of
script. When there is no access, it reverts to the temporary file system location of the
exploitation.

This may not be safe for unauthorized persons!

Depending on your operating system configuration, temporary storage may be
read by anyone who uses the same temporary storage folder.
When the confidentiality of your document is necessary, it is recommended not to use php://
output.

HTTP headers
Example of a script sequence to redirect an Excel 2007 file to the browser
client

<?php
/*Here there will be some code where objPHPExcel is created */

Redirect the output to the client's browser

header('Content-Type: application/[Link]-
[Link]
header('Content-Disposition: attachment;filename="[Link]"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save('php://output');
?>

Example of a script sequence to redirect an Excel5 file to the browser client:

<?php
/*Here there will be some code where objPHPExcel is created */

Redirect the output to the client's browser

header('Content-Type: application/[Link]-excel');
header('Content-Disposition: attachment;filename="[Link]"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');
?>

PHPExcel Developer Documentation 40

Caution:

• Make sure not to include any echo of statements or the output of anyone else
content that the Excel file. There should be no blank spaces before the opening <?
PHP tags and more on a new line after the closing ?> Tags (which also
it can be omitted to avoid problems.
• Make sure the script is saved without a bill of materials (mark of
byte order). (Since this counts as echo of the output)
• the same applies to all included files

Not following these rules can result in corrupted Excel files reaching the browser of
client, and/or that the headers cannot be set by PHP (which results in messages of
warning).

4.6.40. Adjustment of default column width

The default column width can be set using the following code:

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);

4.6.41. Default row height adjustment

The default row height can be configured using the following code:

$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

4.6.42. Add a drawing to a GD sheet

There could be a situation where you want to generate a memory image using GD and
add it to a PHPExcel spreadsheet without having to save this file to a location
temporal.

Here is an example that generates an image in memory and adds it to the active spreadsheet:

Generate an image

$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image

stream');
imagecolorallocate($gdImage, 255, 255, 255);
imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel', $textColor);

Add a drawing of the spreadsheet

$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();

$objDrawing->setName('Sample image');
$objDrawing->setDescription('Sample image');
$objDrawing->setImageResource($gdImage);
$objDrawing-
setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

4.6.43. Marco spreadsheet the zoom level

To set the zoom level of a spreadsheet, the following code can be used:

$objPHPExcel->getActiveSheet()->getSheetView()->setZoomScale(75);

PHPExcel Developer Documentation 41

Please note that the zoom level must be in the range of 10 - 400.

4.6.44. Color chip sheet

Sometimes you want to set a tab color for a sheet. For example, you might have a tab of
red sheet

$objWorksheet->getTabColor()->setRGB('FF0000');

4.6.45. Create spreadsheets in a workbook

If you need to create more sheets in the workbook, here is how:

$objWorksheet1 = $objPHPExcel->createSheet();
$objWorksheet1->setTitle('Another sheet');

Think of createSheet() as the 'Insert sheet button in Excel. When you press the button of a
A new sheet is added to the existing collection of the book's sheets.

4.6.46. Hidden spreadsheets (status sheet)

Establish a spreadsheet that is hidden using this code:

$objPHPExcel->getActiveSheet()
->setSheetState(PHPExcel_Worksheet::SHEETSTATE_HIDDEN);

It is possible that you want the spreadsheet to be 'very hidden'. The sheet of states
available are:

PHPExcel_Worksheet::SHEETSTATE_VISIBLE
PHPExcel_Worksheet::SHEETSTATE_HIDDEN
PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN

In the Excel sheet state 'very hidden', it can only be set programmatically, due to
example, with Visual Basic Macro. It is not possible to make a sheet visible through the interface of
user.

4.6.47. Left sheet of Law

Spreadsheets can be adjusted individually if column 'A' should start on the side
right or left. The default value is to the left. Here is how to set the
columns from right to left.

On the left spreadsheet on the right

$objPHPExcel->getActiveSheet()
->setRightToLeft(true);

PHPExcel Developer Documentation 42

5. Performing calculations of the formula
5.1. Using the PHPExcel calculation engine
How PHPExcel represents a spreadsheet in memory, but also offers capabilities
of the formula calculation. A cell can be of one type of value (that contains a number or
text), or a type of formula (which contains a formula that can be evaluated). For example, the
"= SUM (A1: A10)" is evaluated as the sum of the values in A1, A2, ..., A10.

For the calculation of a formula, you can refer to the cell that contains the formula of the method of the
getCalculatedValue(), for example:

$objPHPExcel->getActiveSheet()->getCell('E11')->getCalculatedValue();

If you write the following line of code in the demo invoice included with PHPExcel, it is evaluated
like the value '64':

Another interesting feature of the PHPExcel parser formula is that it can be adjusted.
automatically a formula to insert / delete rows / columns. Here is an example:

You see that the formula contained in cell E11 is "SUM(E4:E9)". Now, when I write the
the following line of code, two new product lines are added:

$objPHPExcel->getActiveSheet()->insertNewRowBefore(7, 2);

PHPExcel Developer Documentation 43

Did you notice? The formula in cell E11 ex (now E13, since it adds two new rows), has
changed to 'SUM (E4: E11)'. In addition, the inserted cells duplicate the style information of the
previous cell, just like Excel's behavior. Note that both can
insert rows and columns.

5.2. Known limitations

There are some known limitations for the PHPExcel calculation engine. Most of them are due to
to the fact that an Excel formula is converted into PHP code before being executed. This
It means that the Excel calculation formula is subject to the characteristics of the PHP language.

5.2.1. Operator Precedence

In Excel, '+' beats '&', just as '*' slightly beats '+' in ordinary algebra. The rule
previous is not what is found using the calculation engine included with PHPExcel.

Operator precedence request in Excel:

[Link] [Link]

Operator precedence request in PHP:

[Link]

5.2.2. The formulas that use numbers and text

The formulas that use numbers and text can produce unexpected results and even the
Content of the file unreadable. For example, the formula "= 3 +" Hello, " is expected to produce a
error in Excel (#VALUE!). Due to the fact that PHP converts "Hello" to a numeric value
(zero), the result of this formula is evaluated as 3 instead of evaluating as an error. This
It also makes the Excel document generated with unreadable content.

For reference on this behavior in PHP::

[Link]

PHPExcel Developer Documentation 44

6. Reading and writing files
As you already know from the part3.3 Readers and writers, The readers and writers, reading and
Writing to a persistent storage is not possible using the PHPExcel class library.
this end, PHPExcel offers readers and writers, which are implementations of
PHPExcel_Writer_IReader and PHPExcel_Writer_IWriter.

6.1. PHPExcel_IOFactory

The PHPExcel API offers several methods to create an instance of PHPExcel_Writer_IWriter.

PHPExcel_Writer_IReader:

• Direct creation of
• Through PHPExcel_IOFactory

All the examples below demonstrate the method of direct creation. Note that
You can also use the PHPExcel_IOFactory class to do this.

6.1.1. Create PHPExcel_Reader_IReader using

PHPExcel_IOFactory

There are two methods for reading a file in PHPExcel: using automatic file type detection.
solve it explicitly.

automatic file type resolution of the different PHPExcel_Reader_IReader controls

distributed with PHPExcel. If one of them can load the specified filename, the file
it loads with PHPExcel_Reader_IReader. explicit mode requires that it be specified that
PHPExcel_Reader_IReader must be used.

You can create an instance of PHPExcel_Reader_IReader using PHPExcel_IOFactory in the type of

automatic file for resolving the way to use the code example:

$objPHPExcel = PHPExcel_IOFactory::load("[Link]");

A typical use of this function is when it is needed to read the files uploaded by users, and
I don't know if it's loading xls or xlsx.

If it is necessary to set some properties on the reader (for example, to read only the data,
see more about this later), then instead of wanting to use this variant:

$objReader = PHPExcel_IOFactory::createReaderForFile("[Link]");
$objReader->setReadDataOnly(true);
$objReader->load("[Link]");

You can create an instance of PHPExcel_Reader_IReader using PHPExcel_IOFactory in mode

explicit with the following code example:

$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objPHPExcel = $objReader->load("[Link]");

PHPExcel Developer Documentation 45

 Please note that the automatic resolution type mode is slightly slower.
that the explicit mode.

6.1.2. Create PHPExcel_Writer_IWriter using PHPExcel_IOFactory

You can create an instance of PHPExcel_Writer_Iwriter using PHPExcel_IOFactory:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");

$objWriter->save("[Link]");

6.2. Excel 2007 (SpreadsheetML) file format

Excel2007 file format is the main file format of PHPExcel. It allows for output of
the spreadsheet in memory to an xlsx file.

6.2.1. PHPExcel_Reader_Excel2007
Reading a spreadsheet

You can read an xlsx code. File with the following:

$objReader = new PHPExcel_Reader_Excel2007();

$objPHPExcel = $objReader->load("[Link]");

read-only data

You can set the setReadDataOnly option on the reader to instruct the reader to bypass the
style, data validation, ... and just read data from the cell:

$objReader = new PHPExcel_Reader_Excel2007();

$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("[Link]");

specifics of the read-only sheets

You can configure the setLoadSheetsOnly option in the reader to instruct the reader to load only
the leaves with a name that is given:

$objReader = new PHPExcel_Reader_Excel2007();

$objReader->setLoadSheetsOnly( array("Sheet 1", "My special sheet") );
$objPHPExcel = $objReader->load("[Link]");

specifics of read-only cells

You can set the setReadFilter option on the reader, to instruct the reader to load only the
cells that match a given rule. A reading of the filter can be any class that
implement PHPExcel_Reader_IReadFilter. By default, all cells are read using the
PHPExcel_Reader_DefaultReadFilter.

The following code will only read row 1 and rows 20 to 30 from any sheet in the Excel file:

PHPExcel Developer Documentation 46

class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
public function readCell($column, $row, $worksheetName = '') {
Read title row and rows 20 - 30
if ($row == 1 || ($row >= 20 && $row <= 30)) {
return true;
}

return false;
}
}

$objReader = new PHPExcel_Reader_Excel2007();

$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("[Link]");

6.2.2. PHPExcel Writer Excel2007

Write a spreadsheet

You can write an xlsx code. File with the following:

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

$objWriter->save("[Link]");

Formula pre-calculation

By default, this writer pre-calculates all the formulas in the spreadsheet. This
it can be slow on large spreadsheets, and perhaps even unwanted. You can disable the
formula however before calculation:

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

$objWriter->setPreCalculateFormulas(false);
$objWriter->save("[Link]");

Office 2003 Compatibility Pack

Due to a bug in the Office2003 compatibility pack, there may be some small
problems when opening Excel 2007 spreadsheets (mostly related to calculation of the
formula). It can enable Office 2003 compatibility with the following code:

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

$objWriter->setOffice2003Compatibility(true);
$objWriter->save("[Link]");

Office 2003 compatibility should only be used when necessary.

The Office 2003 compatibility option should only be used when necessary. This option
disables several Office 2007 file format options, resulting in a loss of functions
Office 2007 spreadsheet when this option is used.

6.3. Excel 5 (BIFF) file format

Excel5 file format is the old file format in Excel, implemented in PHPExcel for
provide evenly to create both .xlsx and .xls. It is basically a version
modified fromPERA Spreadsheet_Excel_Writer, and has the same limitations and characteristics
like the PEAR library.

PHPExcel Developer Documentation 47

Excel5 file format will not be developed any further, it just provides an additional file format for
PHPExcel.Excel5 file format will not be developed any further, it only provides a
additional file formats for PHPExcel.

Excel5 (BIFF) limitations

Please note that files in BIFF format have some limitations regarding cells.
style and handling of large spreadsheets through PHP.

6.3.1. PHPExcel Reader Excel5

Reading a spreadsheet

You can read the code. Xls with the following:

$objReader = new PHPExcel_Reader_Excel5();

$objPHPExcel = $objReader->load("[Link]");

read-only data

You can set the setReadDataOnly option in the reader to instruct the reader to skip the
style, data validation, ... and has just read data from the cell:

$objReader = new PHPExcel_Reader_Excel5();

$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("[Link]");

specific to read-only sheets

You can set the setLoadSheetsOnly option in the reader, to instruct the reader to load only
the leaves with a name that is given:

$objReader = new PHPExcel_Reader_Excel5();

$objReader->setLoadSheetsOnly( array("Sheet 1", "My special sheet") );
$objPHPExcel = $objReader->load("[Link]");

specific to read-only cells

You can set the setReadFilter option in the reader to instruct the reader to load only the
cells that match a given rule. A filter read can be any class that
implement PHPExcel_Reader_IReadFilter. By default, all cells are read using the
PHPExcel_Reader_DefaultReadFilter.

The following code will only read row 1 and rows 20 to 30 from any sheet in the Excel file:

class MyReadFilter implements PHPExcel_Reader_IReadFilter

{
public function readCell($column, $row, $worksheetName = '') {
Read title row and rows 20 - 30
if ($row == 1 || ($row >= 20 && $row <= 30)) {
return true;
}

return false;
}

PHPExcel Developer Documentation 48

}

$objReader = new PHPExcel_Reader_Excel5();

$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("[Link]");

6.3.2. PHPExcel_Writer_Excel5
Write a spreadsheet

You can write a code. Xls with the following:

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

$objWriter->save("[Link]");

6.4. Excel 2003 XML file format

Excel 2003 XML file format is a file format that can be used in the
previous versions of Microsoft Excel.

Excel 2003 XML limitations

Please note that Excel 2003 in XML format has some limitations regarding the
style cells and manage large spreadsheets through PHP.

6.4.1. PHPExcel_Reader_Excel2003XML
Reading a spreadsheet

You can read the XML code. File with the following:

$objReader = new PHPExcel_Reader_Excel2003XML();

$objPHPExcel = $objReader->load("[Link]");

specific to read-only cells

You can set the setReadFilter option in the reader, to instruct the reader to load only the
cells that match a given rule. A filter reading can be any class that
implement PHPExcel_Reader_IReadFilter. By default, all cells are read using the
PHPExcel_Reader_DefaultReadFilter.

The following code will only read row 1 and rows 20 to 30 of any sheet in the Excel file:

class MyReadFilter implements PHPExcel_Reader_IReadFilter

{
public function readCell($column, $row, $worksheetName = '') {
Read title row and rows 20 - 30
if ($row == 1 || ($row >= 20 && $row <= 30)) {
return true;
}

return false;
}

PHPExcel Developer Documentation 49

}

$objReader = new PHPExcel_Reader_Excel2003XML();

$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("[Link]");

6.5. Symbolic link (SYLK)

Symbolic Link (SYLK) is a file format from Microsoft usually used for data exchange.
data between applications, especially spreadsheets. Composed of ANSI characters that
they can only be visualized, which can be easily created and processed by others
applications, like databases.

SYLK limitations
Please note that SYLK format files have some limitations regarding the cells of
style and management of large spreadsheets through PHP.

6.5.1. PHPExcel Reader SYLK

Reading a spreadsheet

You can read an SLK code. File with the following:

$objReader = new PHPExcel_Reader_SYLK();

$objPHPExcel = $objReader->load("[Link]");

specific to read-only cells

You can set the setReadFilter option in the reader to instruct the reader to load only the
cells that match a given rule. A filter reading can be any class that
implement PHPExcel_Reader_IReadFilter. By default, all cells are read using the
PHPExcel_Reader_DefaultReadFilter.

The following code will only read row 1 and rows 20 to 30 of any sheet in the SYLK file:

class MyReadFilter implements PHPExcel_Reader_IReadFilter

{
public function readCell($column, $row, $worksheetName = '') {
// Read title row and rows 20 - 30
if ($row == 1 || ($row >= 20 && $row <= 30)) {
return true;
}

return false;
}
}

$objReader = new PHPExcel_Reader_SYLK();

$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load("[Link]");

6.6. CSV (comma-separated values)

CSV (comma separated values) are often used as an import/file format
export with other systems. PHPExcel allows reading and writing to CSV files. PHPExcel
allows reading and writing in CSV files.

PHPExcel Developer Documentation 50

 CSV limitations
Please note that a CSV file has some limits regarding the cells of
style, number format, ...

6.6.1. PHPExcel_Reader_CSV
Reading a CSV file

You can read a CSV file with the following:

$objReader = new PHPExcel_Reader_CSV();

$objPHPExcel = $objReader->load("[Link]");

Marco CSV options

Often, CSV files are not actually 'comma-separated', or semicolon used (;)
separator. PHPExcel_Reader_CSV can indicate some options before reading a CSV file.

Please note that PHPExcel_Reader_CSV by default assumes that the loading of the CSV file is
UTF-8 encoding. If you are reading CSV files that were created in Microsoft Office Excel
the correct input encoding and cannot be Windows-1252 (CP1252). Always ensure to
that the encoding of an appropriate value.

$objReader = new PHPExcel_Reader_CSV();

$objReader->setInputEncoding('CP1252');
$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcel = $objReader->load("[Link]");

Read a specific spreadsheet

CSV files can only contain one spreadsheet. Therefore, you can specify that the sheet
to read from CSV:

$objReader->setSheetIndex(0);

Read in the existing spreadsheet

When working with CSV files, it may happen that you want to import CSV data into an object.
existing PHPExcel. The following code loads a CSV file in current dollars objPHPExcel
it contains some sheets, and the imports in sheet 6º:

$objReader = new PHPExcel_Reader_CSV();

$objReader->setDelimiter(';');
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(5);
$objReader->loadIntoExisting("[Link]", $objPHPExcel);

PHPExcel Developer Documentation 51

6.6.2. PHPExcel_Writer_CSV
Creation of a CSV file

You can write a csv code. File with the following:

$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);

$objWriter->save("[Link]");

Marco CSV options

Often, CSV files are not really "comma-separated" or use a semicolon (;) as
separator. PHPExcel_Writer_CSV can indicate some options before writing a CSV file:

$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);

$objWriter->setDelimiter(';');
$objWriter->setEnclosure('');
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
The object writer saves the file as '[Link]'.

Write a specific spreadsheet

CSV files can only contain one spreadsheet. Therefore, you can specify which sheet to
write to a CSV file:

$objWriter->setSheetIndex(0);

Pre-calculus formula

By default, this writer pre-calculates all the formulas in the spreadsheet. This
it can be slow on large spreadsheets, and perhaps even unwanted. You can disable the
formula however before calculation:

$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);

$objWriter->setPreCalculateFormulas(false);
$objWriter->save("[Link]");

Write UTF-8 CSV files

A CSV file can be marked as UTF-8 by writing a header in the list file.
materials. This can be activated using the following code:

$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);

$objWriter->setUseBOM(true);
$objWriter->save("[Link]");

Decimal and thousands separators

If the spreadsheet you are exporting contains numbers with thousands or decimal separators,
then you should think about the characters you want to use for those before proceeding to do the
export

By default, PHPExcel looks in the locale settings of the configuration of

server to decide which characters to use. But to avoid problems, it is recommended to set the
characters explicitly as shown below.

PHPExcel Developer Documentation 52

English users will have to use this before making the export:

require_once 'PHPExcel/Shared/[Link]'
PHPExcel_Shared_String::setDecimalSeparator('.');
PHPExcel_Shared_String::setThousandsSeparator(',');

German users will want to use the opposite values.

require_once 'PHPExcel/Shared/[Link]'
PHPExcel_Shared_String::setDecimalSeparator(',');
PHPExcel_Shared_String::setThousandsSeparator('.');

Please note that the above code sets and thousand decimal separators as options.
global. This also affects the way it is exported in HTML and PDF.

6.7. HTML
PHPExcel allows you to write a spreadsheet in HTML format for quick representation.
the data in it to anyone who does not have a spreadsheet on their PC.

HTML limitations
Please note that an HTML file has certain limitations regarding cells.
style, number format, ...

6.7.1. PHPExcel_HTML_Writer

Please note that only PHPExcel_Writer_HTML outputs the first spreadsheet.

predetermined.

Write a spreadsheet

You can write an HTML code file with the following:

$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);

$objWriter->save("[Link]");

Write all the sheets

HTML files can contain one or more spreadsheets. If you want to write all the sheets in
a single HTML file, use the following code:

$objWriter->writeAllSheets();

Write a specific spreadsheet

HTML files can contain one or more spreadsheets. Therefore, you can specify what
writing sheet in HTML:

$objWriter->setSheetIndex(0);

Framework of the root images of the HTML file

There may be situations where you want to explicitly include the root images.
For example, one might want to see the style <img = "position: relative; left:
0px; top: 0px; width: 140px; height: 78px;" src = "images
[Link] logo . jpg "border =" 0 "> instead of style

PHPExcel Developer Documentation 53

<img style="position: relative; left: 0px; top: 0px; width: 140px;
height:; "src =". 78px / images / [Link] "border ="0">.

You can use the following code to achieve this result:

$objWriter->setImagesRoot('[Link]

Pre-calculation formula

By default, this writer pre-calculates all the formulas in the spreadsheet.

it can be slow on large spreadsheets, and perhaps unwanted, even. You can disable the
formula however before calculation:

$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);

$objWriter->setPreCalculateFormulas(false);
$objWriter->save("[Link]");

Embedding generated HTML code on a web page

There could be a situation where you want to embed the generated HTML code on a website.
existing. PHPExcel_Writer_HTML provides support for generating only the specific parts of
HTML code that allows the use of these pieces on your website.

Supported methods:

• generateHTMLHeader()
• generateStyles()
• generateSheetData()
• generateHTMLFooter()

Here is an example that retrieves all the parts independently and combines them into one.
resulting HTML page:

<?php
$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
echo $objWriter->generateHTMLHeader();
?>

<style>
<!--
html {
font-family: Times New Roman;
font-size: 9pt;
background-color: white;
}

<?php
echo $objWriter->generateStyles(false); // do not write <style> and </style>
?>

-->

<?php
echo $objWriter->generateSheetData();
echo $objWriter->generateHTMLFooter();
?>

PHPExcel Developer Documentation 54

Write UTF-8 HTML files

An HTML file can be marked as UTF-8 by writing a header at the beginning of the file.
materials. This can be activated using the following code:

$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);

$objWriter->setUseBOM(true);
$objWriter->save("[Link]");

Decimal and thousand separators

See the PHPExcel_Writer_CSV section on how to control their appearance.

6.8. PDF
PHPExcel allows you to write a spreadsheet in PDF format, for quick distribution of the
represented data.

PDF limitations
Please note that PDF files have some limitations regarding cells.
of style, number format, ...

6.8.1. PHPExcel_Writer_PDF

Note that only PHPExcel_Writer_PDF exports the first worksheet.

predetermined.

Write a spreadsheet

You can write a PDF code file with the following:

$objWriter = new PHPExcel_Writer_PDF($objPHPExcel);

Write all the sheets

PDF files can contain one or more spreadsheets. If you want to write all the sheets in
a single PDF file, use the following code:

$objWriter->writeAllSheets();

Write a specific spreadsheet

PDF files can contain one or more spreadsheets. Therefore, you can specify what
sheet for writing in PDF:

$objWriter->setSheetIndex(0);

Formula pre-calculation

By default, this writer pre-calculates all the formulas in the spreadsheet. This
it can be slow in large spreadsheets, and perhaps undesirable, it can even disable the
formula however before calculation:

PHPExcel Developer Documentation 55

$objWriter = new PHPExcel_Writer_PDF($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save("[Link]");

Decimal and thousand separators

See the PHPExcel_Writer_CSV section on how to control their appearance.

6.9. Generation of Excel files from templates (read,

modify and write
Readers and writers are the tools that allow you to generate Excel files from
from templates. This requires less coding effort than generating the Excel file from scratch, especially
this requires less effort
of coding to generate the Excel file from scratch, especially if the template has many
styles, page layout properties, headers, etc

Here is an example of how to open a template file, fill in a couple of fields, and save it.
new

$objPHPexcel = PHPExcel_IOFactory::load('[Link]');

$objWorksheet = $objPHPexcel->getActiveSheet();
John
$objWorksheet->getCell('A2')->setValue('Smith');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');

$objWriter->save('[Link]');

Please note that an xlsx file can be uploaded and an xls file generated.

4. Credits
Please refer to the internet pageThe provided text is a URL and does not contain translatable content.
Creditsfor up-to-date credits. Please check the website
[Link]
update the credit date.

PHPExcel Developer Documentation 56

7. Credits
Please visit the websiteThe provided text is a URL and does not contain translatable content.
Creditsto update the credit date.

PHPExcel Developer Documentation 57

Appendix A: Valid matrix keys
applyFromArray for style ()
The following table shows the valid matrix keys for applyFromArray PHPExcel_Style ()
classes. If the "property map" column assigns a key to an incubator, the value
provided for that key can be applied directly. If the "property map" column
a key is assigned to a buyer, the value provided for that key will be applied as a
style set.

PHPExcel_Style
Array key: Maps to property:
fill getFill()
font getFont()
borders getBorders()
alignment getAlignment()
number format getNumberFormat()
protection getProtection()

PHPExcel_Style_Fill
Array key: Maps to property:
type setFillType()
rotation setRotation()
startcolor getStartColor()
endcolor getEndColor()
color getStartColor()

PHPExcel_Style_Font
Array key: Maps to property:
name setName()
bold setBold()
italic setItalic()
underline setUnderline()
strike setStrikethrough()
color getColor()
size setSize()
superScript setSuperScript()
subScript setSubScript()

PHPExcel Style Borders

Array key: Maps to property:
allborders getLeft()
left getLeft()
right getRight()
top getTop()
bottom getBottom()
diagonal getDiagonal()
vertical getVertical()
horizontal getHorizontal()
diagonal direction setDiagonalDirection()
outline setOutline()

PHPExcel_Style_Border
Array key: Maps to property:
style setBorderStyle()
color getColor()

PHPExcel Developer Documentation 58

PHPExcel Style Alignment
Array key: Maps to property:
horizontal setHorizontal()
vertical setVertical()
rotation setTextRotation()
wrap setWrapText()
shrinkToFit setShrinkToFit()
indent setIndent()

PHPExcel_Style_NumberFormat
Array key Maps to property:
code setFormatCode()

PHPExcel_Style_Protection
Array key: Maps to property:
locked setLocked()
hidden setHidden()

PHPExcel Developer Documentation 59