Author:
Maarten Balliauw, Mark Baker
Version:
1.8.0
Date:
14 January 2016
PHPExcel Developer Documentation
PHPExcel Developer Documentation
2
1. Contents
PHPExcel Developer Documentation..............................................................................1
1. Contents
Contents
PHPExcel Developer Documentation..............................................................................1
1. Contents .........................................................................................................2
2. Prerequisites ....................................................................................................5
2.1. Software requirements..................................................................................5
2.2. Installation instructions.................................................................................5
2.3. Getting started...........................................................................................5
2.4. Useful links and tools ...................................................................................5
2.4.1. OpenXML / SpreadsheetML .......................................................................5
2.4.2. Frequently asked questions ......................................................................6
2.4.3. Tutorials.............................................................................................7
3. Architecture.....................................................................................................8
3.1. Schematical...............................................................................................8
3.2. Lazy Loader...............................................................................................8
3.3. Spreadsheet in memory.................................................................................8
3.4. Readers and writers .....................................................................................8
3.5. Fluent interfaces.........................................................................................9
4. Creating a spreadsheet ......................................................................................11
4.1. The PHPExcel class.....................................................................................11
4.1.1. Loading a Workbook from a file................................................................11
4.1.2. Creating a new workbook .......................................................................11
4.2. Configuration Settings .................................................................................11
4.2.1. Cell Caching .......................................................................................11
4.2.2. Language/Locale .................................................................................13
4.3. Clearing a Workbook from memory..................................................................14
4.4. Worksheets ..............................................................................................14
4.4.1. Adding a new Worksheet ........................................................................15
4.4.2. Copying Worksheets..............................................................................15
4.4.3. Removing a Worksheet ..........................................................................15
4.5. Accessing cells ..........................................................................................16
4.5.1. Setting a cell value by coordinate .............................................................16
4.5.2. Setting a range of cells from an array ........................................................18
4.5.3. Retrieving a cell by coordinate.................................................................20
4.5.4. Setting a cell value by column and row.......................................................20
4.5.5. Retrieving a cell by column and row ..........................................................20
4.5.6. Looping cells ......................................................................................21
4.5.7. Using value binders to facilitate data entry .................................................22
4.6. PHPExcel recipes .......................................................................................22
4.6.1. Setting a spreadsheet’s metadata .............................................................23
4.6.2. Setting a spreadsheet’s active sheet..........................................................23
4.6.3. Write a date or time into a cell................................................................23
4.6.4. Write a formula into a cell......................................................................24
4.6.5. Locale Settings for Formulae ...................................................................25
4.6.6. Write a newline character "\n" in a cell (ALT+"Enter") .....................................25
4.6.7. Explicitly set a cell’s datatype .................................................................26
4.6.8. Change a cell into a clickable URL.............................................................26
4.6.9. Setting a worksheet’s page orientation and size ............................................26
4.6.10. Page Setup: Scaling options.....................................................................26
4.6.11. Page margins ......................................................................................27
4.6.12. Center a page horizontally/vertically.........................................................28
4.6.13. Setting the print header and footer of a worksheet ........................................28
4.6.14. Setting printing breaks on a row or column ..................................................30
4.6.15. Show/hide gridlines when printing ............................................................30
PHPExcel Developer Documentation
3
4.6.16. Setting rows/columns to repeat at top/left .................................................30
4.6.17. Specify printing area.............................................................................30
4.6.18. Formatting cells ..................................................................................30
4.6.19. Number formats ..................................................................................32
4.6.20. Alignment and wrap text........................................................................32
4.6.21. Setting the default style of a workbook ......................................................33
4.6.22. Styling cell borders...............................................................................33
4.6.23. Conditional formatting a cell...................................................................34
4.6.24. Add a comment to a cell ........................................................................35
4.6.25. Apply autofilter to a range of cells............................................................35
4.6.26. Setting security on a spreadsheet .............................................................35
4.6.27. Setting data validation on a cell ...............................................................36
4.6.28. Setting a column’s width........................................................................37
4.6.29. Show/hide a column .............................................................................37
4.6.30. Group/outline a column.........................................................................37
4.6.31. Setting a row’s height ...........................................................................38
4.6.32. Show/hide a row .................................................................................38
4.6.33. Group/outline a row .............................................................................38
4.6.34. Merge/unmerge cells ............................................................................38
4.6.35. Inserting rows/columns..........................................................................39
4.6.36. Add a drawing to a worksheet..................................................................39
4.6.37. Reading Images from a worksheet .............................................................39
4.6.38. Add rich text to a cell ...........................................................................40
4.6.39. Define a named range ...........................................................................40
4.6.40. Redirect output to a client’s web browser ...................................................41
4.6.41. Setting the default column width..............................................................42
4.6.42. Setting the default row height .................................................................42
4.6.43. Add a GD drawing to a worksheet .............................................................42
4.6.44. Setting worksheet zoom level ..................................................................42
4.6.45. Sheet tab color ...................................................................................42
4.6.46. Creating worksheets in a workbook ...........................................................42
4.6.47. Hidden worksheets (Sheet states) .............................................................43
4.6.48. Right-to-left worksheet .........................................................................43
5. Performing formula calculations ...........................................................................44
5.1. Using the PHPExcel calculation engine .............................................................44
5.2. Known limitations ......................................................................................45
5.2.1. Operator precedence ............................................................................45
5.2.2. Formulas involving numbers and text .........................................................45
6. Reading and writing to file ..................................................................................46
6.1. PHPExcel_IOFactory....................................................................................46
6.1.1. Creating PHPExcel_Reader_IReader using PHPExcel_IOFactory...........................46
6.1.2. Creating PHPExcel_Writer_IWriter using PHPExcel_IOFactory ............................46
6.2. Excel 2007 (SpreadsheetML) file format ............................................................47
6.2.1. PHPExcel_Reader_Excel2007 ...................................................................47
6.2.2. PHPExcel_Writer_Excel2007 ....................................................................47
6.3. Excel 5 (BIFF) file format .............................................................................48
6.3.1. PHPExcel_Reader_Excel5 .......................................................................48
6.3.2. PHPExcel_Writer_Excel5 ........................................................................49
6.4. Excel 2003 XML file format ...........................................................................49
6.4.1. PHPExcel_Reader_Excel2003XML ..............................................................49
6.5. Symbolic LinK (SYLK)...................................................................................50
6.5.1. PHPExcel_Reader_SYLK..........................................................................50
6.6. Open/Libre Office (.ods) ..............................................................................51
6.6.1. PHPExcel_Reader_OOCalc ......................................................................51
6.7. CSV (Comma Separated Values)......................................................................51
6.7.1. PHPExcel_Reader_CSV...........................................................................51
6.7.2. PHPExcel_Writer_CSV............................................................................52
6.8. HTML .....................................................................................................53
6.8.1. PHPExcel_Reader_HTML.........................................................................53
6.8.2. PHPExcel_Writer_HTML .........................................................................53
PHPExcel Developer Documentation
4
6.9. PDF .......................................................................................................55
6.9.1. PHPExcel_Writer_PDF............................................................................55
6.10. Generating Excel files from templates (read, modify, write)................................56
7. Credits ..........................................................................................................58
Appendix A: Valid array keys for style applyFromArray()..................................................59
PHPExcel Developer Documentation
5
2. Prerequisites
2.1. Software requirements
The following software is required to develop using PHPExcel:
» PHP version 5.2.0 or newer
» PHP extension php_zip enabled *)
» PHP extension php_xml enabled
» PHP extension php_gd2 enabled (if not compiled in)
*) php_zip is only needed by PHPExcel_Reader_Excel2007, PHPExcel_Writer_Excel2007 and
PHPExcel_Reader_OOCalc. In other words, if you need PHPExcel to handle .xlsx or .ods files you will need
the zip extension, but otherwise not.
You can remove this dependency for writing Excel2007 files (though not yet for reading) by using the PCLZip
library that is bundled with PHPExcel. See the FAQ section of this document (2.4.2) for details about this.
PCLZip does have a dependency on PHP’s zlib extension being enabled.
2.2. Installation instructions
Installation is quite easy: copy the contents of the Classes folder to any location within your
application source directories.
Example:
If your web root folder is /var/www/ you may want to create a subfolder called /var/www/Classes/ and copy the files into
that folder so you end up with files:
/var/www/Classes/PHPExcel.php
/var/www/Classes/PHPExcel/Calculation.php
/var/www/Classes/PHPExcel/Cell.php
...
2.3. Getting started
A good way to get started is to run some of the tests included in the download.
Copy the "Examples" folder next to your "Classes" folder from above so you end up with:
/var/www/ Examples/01simple.php
/var/www/ Examples/02types.php
...
Start running the tests by pointing your browser to the test scripts:
http://example.com/ Examples/01simple.php
http://example.com/ Examples/02types.php
...
Note: It may be necessary to modify the include/require statements at the beginning of each of the test scripts if your
"Classes" folder from above is named differently.
2.4. Useful links and tools
There are some links and tools which are very useful when developing using PHPExcel. Please refer
to the PHPExcel CodePlex pages for an update version of the list below.
2.4.1. OpenXML / SpreadsheetML
» File format documentation
http://www.ecma-international.org/news/TC45_current_work/TC45_available_docs.htm
» OpenXML Explained e-book
http://openxmldeveloper.org/articles/1970.aspx
» Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats
http://www.microsoft.com/downloads/details.aspx?familyid=941b3470-3ae9-4aee-8f43-
