Read and write Excel file using PHP (example)

Posted by admin on Jan 24, 2012 in php | 23 comments

This post will describe a basic usage of PHPExcel to read and write Excel file. Although in this demo I use .xls extension, PHPExcel really can write and read Excel 2007 (.xlsx) files too.

Download PHPExcel here (free of charge, it’s open source ).

Unzip the PHPExcel .zip file into your project destination. You only need to extract “Classes” folder.

Our scenario: user will fill some HTML form then from this input, we will populate a new Excel file (.xls) and send it to user as downloadable file.

Excel template screen shot

We will fill C1 cell with the current date when the form submitted. C3 cell will be the user’s name and C4 cell will contain user’s message. Our template file –> http://demo.ahowto.net/phpexcel/template.xls

Full source code of our simple form can be seen by viewing source code (Ctrl+U on Firefox) our demo URL: http://demo.ahowto.net/phpexcel/

Form inputs will be processed by process.php below:

//processing form submitted
 
if (!isset($_POST['btnSubmit'])) exit;
 
//include PHPExcel library
require_once "Classes/PHPExcel/IOFactory.php";
 
//load Excel template file
$objTpl = PHPExcel_IOFactory::load("template.xls");
$objTpl->setActiveSheetIndex(0);  //set first sheet as active
 
$objTpl->getActiveSheet()->setCellValue('C2', date('Y-m-d'));  //set C1 to current date
$objTpl->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //C1 is right-justified
 
$objTpl->getActiveSheet()->setCellValue('C3', stripslashes($_POST['txtName']));
$objTpl->getActiveSheet()->setCellValue('C4', stripslashes($_POST['txtMessage']));
 
$objTpl->getActiveSheet()->getStyle('C4')->getAlignment()->setWrapText(true);  //set wrapped for some long text message
 
$objTpl->getActiveSheet()->getColumnDimension('C')->setWidth(40);  //set column C width
$objTpl->getActiveSheet()->getRowDimension('4')->setRowHeight(120);  //set row 4 height
$objTpl->getActiveSheet()->getStyle('A4:C4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); //A4 until C4 is vertically top-aligned
 
//prepare download
$filename=mt_rand(1,100000).'.xls'; //just some random filename
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
 
$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!
 
exit; //done.. exiting!

I already put a lot of comment on the codes. It should be clear. On the code above, I show you how to read Excel .xls file, change cell content, formatting a cell (alignment), change column width, set row height and finally “force” send result file to user.

The Excel result file should be like this:

You can do more than I showed here (such as setting cell color, rotation, inserting images, formula, printing, etc.) by reading PHPExcel Documentation and see the codes in Tests folder (it is included in the PHPExcel download zip).

23 Responses to “Read and write Excel file using PHP (example)”

badtrip says:

2012/06/24 at 21:34

Reply
olivia says:

2012/07/03 at 01:40

Beautiful, thanks.

Reply
pankaj says:

2012/08/30 at 17:28

I says at first a lot of thanks .
I want save the excel file at my physical location .
c:\wamp\www\……
Can you help me .

Reply
- admin says:
  
  2012/08/31 at 05:35
  
  remove all header() PHP code and
  just change from this:
  $objWriter->save('php://output');
  into this:
  $objWriter->save('your_target_filename.xls');
  Your file will be saved in the same directory/folder as the PHP file
  
  Reply
vde says:

2012/09/28 at 03:55

Great for basic XLS, but …

I have make a bot which write data into a template excel file.
All are ok but not at all optimized for insert many lines from a n array. In this case performance are bad and crash the api.

For write many lines dynamicaly, use writeexcel library.

Reply

rizky says:

2012/10/17 at 15:46

where must i set a location of my excel template..??

admin says:

2012/10/17 at 16:18

place it where-ever you want — as long as the script has access to it — . The safest place would in the same folder/directory as the script

rizky says:

2012/10/17 at 17:39

I am sorry, I’m making an application using ExtJS and PHP,
I put the same location with a php script,
and the error :

<b>Fatal error</b>:  Uncaught exception 'Exception' with message 'Could not open template.xls for reading! File does not exist.' in C:\wamp\www\nrcfv2_prev\system\cms\modules\accounts\controllers\phpexcel\Classes\PHPExcel\Reader\Excel5.php:519
Stack trace:
#0 C:\wamp\www\nrcfv2_prev\system\cms\modules\accounts\controllers\phpexcel\Classes\PHPExcel\IOFactory.php(268): PHPExcel_Reader_Excel5-&gt;canRead('template.xls')
#1 C:\wamp\www\nrcfv2_prev\system\cms\modules\accounts\controllers\phpexcel\Classes\PHPExcel\IOFactory.php(191): PHPExcel_IOFactory::createReaderForFile('template.xls')
#2 C:\wamp\www\nrcfv2_prev\system\cms\modules\accounts\controllers\admin.php(721): PHPExcel_IOFactory::load('template.xls')
#3 [internal function]: Admin-&gt;save()
#4 C:\wamp\www\nrcfv2_prev\system\codeigniter\core\CodeIgniter.php(339): call_user_func_array(Array, Array)
#5 C:\wamp\www\nrcfv2_prev\index.php(289): require_once('C:\wamp\www\nrc...')
#6 {main}
  thrown in <b>C:\wamp\www\nrcfv2_prev\system\cms\modules\accounts\controllers\phpexcel\Classes\PHPExcel\Reader\Excel5.php</b> on line <b>519</b>

please help me to solve this.

admin says:

2012/10/17 at 17:44

I’m not familiar on how extJS work but you can always use “desperate measure” by using absolute path.. something like this:
$objTpl = PHPExcel_IOFactory::load("c:\\wamp\\www\\nrcfv2_prev\\template.xls");
and put your excel template in c:\wamp\www\nrcfv2_prev\

Reply
- rizky says:
  
  2012/10/17 at 17:55
  
  thanks for the reply,
  here I just want to edit a field with a script:
  $objTpl->getActiveSheet()->setCellValue(‘C2′, date(‘Y-m-d’))
  and the error:
  Severity: Notice
  Message: Constant IDENTIFIER_OLE already defined
  Filename: Shared/OLERead.php
  Line Number: 28
  and the error:
  Severity: Notice
  Message: Constant IDENTIFIER_OLE already defined
  Filename: Shared/OLERead.php
  Line Number: 28
  Thanks

admin says:

2012/10/17 at 18:16

@rizky:
that’s just NOTICE error, you can disable it by editing your php.ini and edit your error_reporting value to:
error_reporting = E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED
restart your Apache after that.

you can also set this value via php script like this:
error_reporting(E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED);
but the first solution is more guaranteed to work.

Reply
rizky says:

2012/10/18 at 10:24

Thank you,
I’ve tried, but no effect,
I really need this, please, can you send a small sample project to edit excel file with php.
if you are willing, please send it to: rizkylazu@yahoo.com
Thank you very much

Reply
rizky says:

2012/10/18 at 10:41

oh, My application is already running

thank you very much

Reply
dimitrikx says:

2013/01/02 at 11:24

thanks.
Dimitri Vargas Figueiredo Guimarães

Reply
Harshish Patel says:

2013/01/11 at 07:07

Excellent Job…
Works very nice!!
Thank you

Reply
Pibe says:

2013/02/05 at 01:26

Fuck this shit men!

Reply
captnskippy says:

2013/03/04 at 02:23

Wanted to know if multiple people can write to or read from the same excel file over a network ??

Reply
Dartfrogdk says:

2013/03/31 at 19:17

Hello, i hope you can answer my question.

can this code let multiple user log into a website and enter a user number and a value, then not save a new file but all users use one xls file.

ex.

215 +50 kg
524 -200 kg

Reply
Brian says:

2013/04/14 at 14:49

Hello!

I am a hobby coder, so please bear with my inexperience, if you would be so kind.

I love the demo version, works wonderfully. However, I am wondering if you know someway to add functionality in the html forms where delimiters from Excel sheet will appropriately skip ‘s

So in essence: copying data from three cells in the same row with put one cells data in three different ‘s aligned horizontally, and the same but transposed for three cells in the same column:

Maybe some sample code is in order:

Column One Column Two Column Three

And let’s say for arguments sake I had 6 cells in Excel, aligned exactly how the ‘s are in the above sample form:

Blue Single 10
Orange Double 20

Is there any solution (javaScript or jQuery) where the “\t” and “\n” hidden delimiters would be recognized and skip ‘s appropriately rather than dumping all data of the 6 selected cells into only one

Tall order I am guessing

Reply
- Brian says:
  
  2013/04/14 at 14:50
  
  Ok, the code didn’t show how I thought it ought to…. hence the inexperience
  
  the code:
  
  Column One
  Column Two
  Column Three
  
  Reply
Brian says:

2013/04/14 at 14:56

crud, it still isn’t working to show the sample form code, and now I am barfing all over you blog…. great! Don’t I feel like the f’ing a’hole.

Reply
Zeeshan says:

2013/04/17 at 15:24

Nothing so great in writing out a excel file. Real point is how would you “READ” an Excel file?

Reply
auujay says:

2013/04/27 at 15:39

i want to upload the excel file and show it on html tabl…

Reply