Home Features Requirements Examples FAQ Contribute Documents Credits Contact

FAQ

Is there a roadmap for PHPExcel development?

There is, the roadmap for 2013 can be found here http://phpexcel.codeplex.com/discussions/428190

There seems to be a problem with character encoding...

It is necessary to use UTF-8 encoding for all texts in PHPExcel. If the script uses different encoding then it is possible to convert the texts with PHP's iconv() function.

PHP complains about ZipArchive not being found

Make sure you meet all Requirements, especially php_zip extension should be enabled.
The ZipArchive class is only required when reading or writing formats that use Zip compression (Excel2007 and OOCalc). From version 1.7.6 the PCLZip library has been bundled with PHPExcel as an alternative to the ZipArchive class.
This can be enabled by calling:
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
before calling the save method of the Excel2007 Writer.

You can revert to using ZipArchive by calling:
PHPExcel_Settings::setZipClass(PHPExcel_Settings::ZIPARCHIVE);

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

Excel 2007 can not open the file generated by PHPExcelWriter2007 on Windows

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

The php_zip extension on Windows coming with PHP 5.2.6 and PHP 5.2.8 contains an error when creating ZIP files. See http://bugs.php.net/48763 for details. The version that can be found on http://snaps.php.net/win32/php5.2-win32-latest.zip should work at all times.

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

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

Fatal error: Allowed memory size of xxx bytes exhausted (tried to allocate yyy bytes) in zzz on line aaa

PHPExcel holds an "in memory" representation of a spreadsheet, so it is susceptible to PHP's memory limitations. The memory made available to PHP can be increased by editing the value of the memorylimit directive in your php.ini file, or by using iniset('memory_limit', '128M') within your code (ISP permitting);

Some Readers and Writers are faster than others, and they also use differing amounts of memory. You can find some indication of the relative performance and memory usage for the different Readers and Writers, over the different versions of PHPExcel, here http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=234150

If you've already increased memory to a maximum, or can't change your memory limit, then this discussion on the board describes some of the methods that can be applied to reduce the memory usage of your scripts using PHPExcel http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=242712

Protection on my worksheet is not working?

When you make use of any of the worksheet protection features (e.g. cell range protection, prohibiting deleting rows, ...), make sure you enable worksheet security. This can for example be done like this:
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);


Feature X is not working with PHPExcelReaderY / PHPExcelWriterZ

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

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

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

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

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

Setting column width is not 100% accurate

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

The short answer is that PHPExcel uses a measure where padding is included. There is more information about why this is the case and how to deal with it in the "PHPExcel Developer Documentation".

How do I use PHPExcel with my framework

There's some instructions for using PHPExcel with Joomla on the Joomla message board http://forum.joomla.org/viewtopic.php?p=2174929
There's also a nice library package on github to simplify installing PHPExcel for joomla https://github.com/vdespa/PHPExcel-Joomla-Library
A page of advice on using PHPExcel in the Yii framework http://www.yiiframework.com/wiki/101/how-to-use-phpexcel-external-library-with-yii/
and on avoiding problems between the Yii autoloader and PHPExcel autoloader http://www.mrsoundless.com/post/2011/04/23/PHPExcel-in-Yii.aspx
The Bakery has some helper classes for reading and writing with PHPExcel within CakePHP http://bakery.cakephp.org/articles/melgior/2010/01/26/simple-excel-spreadsheet-helper and http://bakery.cakephp.org/articles/segy/2012/04/02/phpexcel_helper_for_generating_excel_files
PHPExcelをCakePHPで使ってみる http://www.okushin.co.jp/kodanuki_note/2010/06/phpexcelcakephp.html
Integrating PHPExcel into Kohana http://www.flynsarmy.com/2010/07/phpexcel-module-for-kohana-3/ and
Интеграция PHPExcel и Kohana Framework http://szpargalki.blogspot.com/2011/02/phpexcel-kohana-framework.html
Using PHPExcel with Typo3 http://typo3.org/documentation/document-library/extension-manuals/phpexcel_library/1.1.1/view/toc/0/
A PHPExcel service for Typo3 http://typo3.org/extensions/repository/view/phpexcel_service/current/
Danny Herran seems to have got PHPExcel working installed as a CodeIgniter library according to this recent post on his blog http://www.dannyherran.com/2011/03/exporting-your-mysql-table-data-with-phpexcel-codeigniter/
Using PHP with Symfony http://symfony.acobby.com/2011/06/using-phpexcel-with-symfony.html?spref=tw
Using PHPExcel with Symfony 2 https://github.com/liuggio/ExcelBundle
PHPExcel for the Contao CMS http://contao.org/en/extension-list/view/php_excel.en.html

Joomla Autoloader interferes with PHPExcel Autoloader

Thanks to peterrlynch for the following advice on resolving issues between the PHPExcel autoloader and Joomla Autoloader
Joomla & PHPExcel > 1.7.2 Issue

Resolving Yii Autoloader problems with the PHPExcel Autoloader

A useful blog post on avoiding autoloader problems between the Yii autoloader and PHPExcel http://www.mrsoundless.com/post/2011/04/23/PHPExcel-in-Yii.aspx

Last edited Apr 17, 2013 at 6:16 AM by maartenba, version 48

Comments

arronlee Jun 23 at 8:20 AM 
Thanks for your sharing.It is appreciated very much. And I wonder whether there are some differences between the excel code 128 barcode generation add-in I am testing these days and the one you mentioned above? Do you have any ideas about it? Or any good suggestion? Thanks in advance.

mehdi4467 Jan 2 at 4:48 PM 
Hello
I have a problem with font in generated excel file.
in template file I set a font on cells for example Bnazanin.
when excel file generated, the font is not Bnazanin, although when we point curcer to cells, see the font is Bnazanin, but we view the default fonts and when we double click on cell, the font go true.

anjilpanchal Oct 17, 2013 at 11:40 AM 
Can i made column width to adjust dynamically according to the text in cell

JorgeGonzales Jul 17, 2013 at 3:16 AM 
Una ayuda estoy probando la libreria PHPEXCEL en smarty pero me sale el sgte error
Warning: require_once(PHPExcel\Shared\String.php): failed to open stream: No such file or directory in C:\library\Framework\class.php on line 12 Fatal error: require_once(): Failed opening required 'PHPExcel\Shared\String.php' (include_path='.;c:\library')
ALGUIEN PUEDE AYUDARME

ArmandoMorales Nov 7, 2012 at 5:46 PM 
Una GRAN PREGUNTA...estoy usando esta libreria genero el excel y me imprime los datos de manera vertical en excel, y lo quiero obtener en forma horizontal como realmente se encuentra en mysql.

rezopac Jan 6, 2012 at 5:53 PM 
hello,
I was looking for reading a xlsx (open xlm) document, so this classes is meant to write one, but I figured out how to do to read whith php and your librairy a xls or xlsx document, this is a complete example :

require 'IOFactory.php';
function read($file){
//get file info
$file_info = pathinfo($file);
//get last file ext (in case of file.iNc.Lab.xLSx => xslx)
$file_info['extension'] = strtolower($file_info['extension']);
if(strpos($file_info['extension'], '.') !== false){
$file_info['extension'] = substr($file_info['extension'], -(strlen($file_info['extension'])-strrpos($file_info['extension'], '.')-1));
}
if($file_info['extension'] != 'xls' && $file_info['extension'] != 'xlsx'){
return false;
}
$ext = $file_info['extension'];
//extensions to PHPExcel fileformat
$xlsx = 'Excel2007';
$xls = 'Excel5';

//create reader
$objReader = PHPExcel_IOFactory::createReader($$ext);
//load file
$objPHPExcel = $objReader->load($file);
//initiate excel datas
$excel = array();

$cellValue = true;

$dim = $objPHPExcel->getActiveSheet()->calculateWorksheetDimension();
list($start, $end) = explode(':', $dim);
if(!preg_match('#([A-Z]+)([0-9]+)#', $start, $rslt)){
return false;
}
list($start, $start_h, $start_v) = $rslt;
if(!preg_match('#([A-Z]+)([0-9]+)#', $end, $rslt)){
return false;
}
list($end, $end_h, $end_v) = $rslt;
set_time_limit(5);
//starting reading verticaly
for($v=$start_v; $v<=$end_v; $v++){
//starting reading horizontaly
for($h=$start_h; ord($h)<=ord($end_h); pp($h)){
$cellValue = get_cell($h.$v, $objPHPExcel);
if($cellValue !== null){
if(!is_array($excel[$v])){
$excel[$v] = array();
}
//inserting in array "preg_match_all" like
$excel[$v][todec($h)] = $cellValue;
}
}
}

return $excel;
}

function get_cell($cell, $objPHPExcel){
//select one cell
$objCell = ($objPHPExcel->getActiveSheet()->getCell($cell));
//get cell value
return $objCell->getvalue();
}

function pp(&$var){
$var = chr(ord($var)+1);
return true;
}

function mm(&$var){
$var = chr(ord($var)-1);
return true;
}

$content = read('file.xls');
$content_openxml = read('file.xlsx');

good work!

lj_lj Dec 21, 2011 at 8:18 AM 
Guys
for SYMFONY2 i found a very usefull bundle
https://github.com/liuggio/ExcelBundle
you'd add to this doc

pamoxi Nov 4, 2011 at 2:07 PM 
Hello.
First I want to congratulate you for this great code

The question is:
How to insert HTML code in a cell and display it formatted?.
For example: <font color=#ff0000> <strong> Hello World! </ Strong> </ font>

daredev Jun 24, 2011 at 7:24 AM 
Suggest me how to read, dump data from excel (.xls/.xlsx) file to Access/mySQL using phpExcel...

daredev Jun 24, 2011 at 7:24 AM 
Hi All!!!
I'm new to phpExcel. (IT'S URGENT!!!)
I used phpExcel to convert my .xlsx file to .xls (say 1) file. Then, i'm using phpExcelReader to dump it into Access/mySQL as it doesn't support .xlsx.
But, the converted .xls (say 2) file opens perfectly in Microsoft Excel 2007, but it can't be dumped in Access/mySQL.
Also, when i save the .xlsx file in .xls format using Excel, then it is dumped into Access/mySQL successfully, without any errors.

Is there an issue with encoding??? Also, the content of both .xls files are exactly same, but size differs greatly (.xls (say 1) is of 10 KB, whereas .xls (say 2) is of 26 KB!!!

Alternatively, suggest me how to read, dump data from excel file to Access/mySQL using phpExcel...

AiNguyen May 29, 2011 at 4:07 PM 
I'm using Zend Framework to code my project. On my localhost, it can helps me to export my excel reports, but when I hosting it to my website, I got this error when I export report:

Warning: require_once() [function.require-once]: Unable to access /Classes/PHPExcel.php in /home/fit/public_html/QLPC/application/controllers/ReportController.php on line 58

Warning: require_once(/Classes/PHPExcel.php) [function.require-once]: failed to open stream: Permission denied in /home/fit/public_html/QLPC/application/controllers/ReportController.php on line 58

Fatal error: require_once() [function.require]: Failed opening required '/Classes/PHPExcel.php' (include_path='APPLICATION_PATH/../library:/home/fit/public_html/QLPC/library:.:/usr/lib/php:/usr/local/lib/php') in /home/fit/public_html/QLPC/application/controllers/ReportController.php on line 58



My files permissions is 755.

What can I do now?

Please help!

MarkBaker May 15, 2011 at 11:52 AM 
@fouzia_riaz_khan
I'm assuming that you're talking about Excel's row filtering. When PHPExcel reads a workbook, it reads the entire worksheet (as does MS Excel itself) into memory. MS Excel then filters the values in the worksheet to display in the UI; but PHPExcel doesn't have a UI, so it doesn't filter the records for display. Nor does PHPExcel actually read any currently applied filters when loading the file, or allow you to set filter values when saving a file. It only reads, and allows you to specify, the filterable range. This will be addressed in a future release; but applying filters (because it is an Excel UI function, will probably always require user-code to implement.

fouzia_riaz_khan May 11, 2011 at 11:39 AM 
When i read xls / xlsx file (having drop down menus in it) using PHPEXCEL reader, all rows return.My objective is to fetch only that rows which contain data.

antonio_carosi Sep 28, 2010 at 1:48 PM 
Hi,

my problem has a precise name: PHP_XML.DLL .
Manual says it has to be declared in the extensions section of php.ini, but i can't locate it in the ext directory of my php installation.
Where can I find it?
I can't write Excel2007 file without that dll....

Thanks!
Bye!

SidneyP Sep 27, 2010 at 7:40 PM 
First: thank you for a great library. However, I've got problems when reading large Excel files (from over 5.000 lines): it gives me a "memory exhausted" error.
Therefore I split up the file in a couple of files of 5.000 lines which work fine when I try to read them seperately, but when I try to read them sequentially, it gives the same memory error. Is there a way to clear the memory of the class between reading the different files or some other solution for large files?

MarkBaker Aug 20, 2010 at 9:57 AM 
@vineet4008 - Would you believe $sheetCount = $objPHPExcel->getSheetCount()

vineet4008 Jul 29, 2010 at 7:41 AM 
hello.
I have loaded an excel file...I want to know how many sheets are there present in that file....so that I can use it for my purpose...Is there any function for that?????
Anybody can help...i would be very thankful....

new_bies Mar 17, 2010 at 6:37 AM 
Help needed!!!
hi, i am using SQL query to pass into the excel.
when I use open office to open it, all the data is fined.
However, when I open it with Microsoft Excel, one of the particular column will come out weird weird numbers!
Beside that, Anyone know how to format number in 1,000 which is also so called English format to format numbers?
thx in adv.

MarkBaker Feb 23, 2010 at 8:00 AM 
@roshanocs
Section 4.4.33 of the developer documentation
$objPHPExcel->getActiveSheet()->insertNewColumnBefore('H', 1);
Will insert 1 new column immediately before the current column H in the current worksheet

roshanocs Feb 23, 2010 at 4:30 AM 
pls help imd...

i want to add new Column near specific column like 'H'. how i do it ?

tzi0 Feb 8, 2010 at 1:48 PM 
One more thing (i.e. two):
1. How can i merge cells using column and row numbers only?
2. How to bring a cell address (1,1) to format "B1" - like (and why first column has number 0 whereas first row has number 1)

tzi0 Feb 8, 2010 at 1:33 PM 
Hi all
i have a problem :
$worksheet2->setCellValue('A1','=Worksheet1.D2');
code doesnt work, i have a error message: Fatal error: Uncaught exception 'Exception' with message 'Formula Error: An unexpected error occured' in C:\Inetpub\wwwroot\repsys\Classes\PHPExcel\Calculation.php:3067 Stack trace: #0
=(

MarkBaker Feb 4, 2010 at 8:07 AM 
>> (1) Excel file generated are in office 2007 format (.xlsx extension). How can I create office 2003 file (with .xls) extension.
>>
It's as easy as using the Excel5 writer (rather than the Excel2007 writer), and specifying a filename with an extension of .xls. Take a look at 14excel5.php in the /Tests directory
Note that certain features of Excel aren't yet available in the Excel5 writer (e.g. use of functions from the analysis toolkpack in your formulae)

>> (2) Every time i try to open generated excel file I get message that "windows cannot find c:\wamp\wwww\cs_admin\read_english_xml.xlsx. Make sure you typed the name correctly,
>> and then try again. To search for a file, click the start button, and then click search.", but interestingly windows open the same file from Temp directory in "Read-only" format. How
>> can fix this issue?
That sounds like something contained within your worksheet. Do you have any links to external files? or similar? that might be using relative path names?

kalpan_vyas Feb 4, 2010 at 5:28 AM 
HI
I have Just started using it and realize how easy it is to read and write Excel file from PHP. I got 2 queries.

(1) Excel file generated are in office 2007 format (.xlsx extension). How can I create office 2003 file (with .xls) extension.

(2) Every time i try to open generated excel file I get message that "windows cannot find c:\wamp\wwww\cs_admin\read_english_xml.xlsx. Make sure you typed the name correctly, and then try again. To search for a file, click the start button, and then click search.", but interestingly windows open the same file from Temp directory in "Read-only" format. How can fix this issue?

To provide more idea I am using windows XP and wamp 2.0 server. Php version is 5.2.9-2 with php_zip extension enabled.

This is kind of urgent so please reply asap.

Any help is much appreciated.

plugin_developer Dec 22, 2009 at 5:05 PM 
thanks for this plugin, I was able to work on my Excel file importing into PHP scripts...

adamquadmon Nov 6, 2009 at 10:53 AM 
Thnks, the reason is my script output other code, not only the PHPExcell stuffs

koyama Nov 6, 2009 at 1:48 AM 
@adamquadmon
There can be many reasons why one gets that error. I suggest that you write a post in "Discussions" describing the details.

adamquadmon Nov 6, 2009 at 12:27 AM 
Excel found unreadable content,

My php version is 5.3.0, my zip version is 1.9.1

MarkBaker Oct 3, 2009 at 2:48 PM 
@justin_wang @bachsmann
when you read xls file with PHPExcel_Reader_Excel5 lib, the data in file is 39984, but Excel formats it using a date formatting mask as '2009-06-20'? Excel holds dates as a count of the number of days since 1st January 1900 (on Windows 1900 calendar). PHPExcel stores its dates in the same way, but doesn't automatically format them for you. You can format them yourself using PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY) or any of the other format masks in PHPExcel_Style_NumberFormat, or convert it to a PHP date using PHPExcel_Shared_Date::ExcelToPHP(39984) and then use PHP's date() function to format it as you wish

MarkBaker Oct 3, 2009 at 2:41 PM 
@dankevin
It might seem strange, but floating point math is rarely 100% accurate in any computer language, and PHP is no exception, because of the way computers store floating point numbers. Typically there is some slight error in precision.
The PHP precision setting doesn't affect the accuracy of calculations in any way, the value held in memory is identical: merely the number of significant digits displayed in floating point numbers. PHPExcel only modifies this setting for test purposes: so that I can compare the PHPExcel result with Excel's own result when displayed to an arbitrary (16) number of significant digits.

dankevin Oct 1, 2009 at 6:54 AM 
//MY php
PHP Version 5.2.6

==========================================
PHPExcel/Calculation/Functions.php

Line 54~56
if ($savedPrecision < 16) {
ini_set('precision',16);
}
=================

// Something Wrong
echo round( (56/62)*100,2);
// result: 90.31999999999999


// Php default precision = 12 , it's ok
ini_set('precision',12);
echo round( (56/62)*100,2);
// result: 90.32

bachsmann Aug 23, 2009 at 7:15 AM 
i have the same proble with the load data (Date type)
who can tell me why and how to solution it?

justin_wang Jul 23, 2009 at 9:44 AM 
when i read xls file with PHPExcel_Reader_Excel5 lib, the returned result is changed,for example, the data in file is '2009-06-20', but the returned result is 39984. who can tell me why and how to solution it?

guarus Apr 4, 2009 at 9:24 PM 
good afternoon, I have a question: how can I do to, based on an already created excel format, open it to write on and keep cells specifies those changes without losing their formats?