How to create excel files in PHP?

Posted By Vijay Joshi December 15, 2009
1 comment

Some time ago I found a class on phpclasses.org by Harish Chauhan which makes it incredibly easy to generate simple excel spreadsheets from PHP. I found it useful so I want to share its usgae with readers too. I also made some changes to the class for styling the rows.


Download Source

Here is the process:

1- Include the ExcelWriter class file in your code

	include("excelwriter.inc.php");

2- Initialize the ExcelWriter class

	$fileName = "myNewExcel.xls";
	$excel = new ExcelWriter($fileName);

Constructor of ExcelWriter class accepts a file name as parameter. File will be saved by this name.

3- Write data to the file

There are 2 ways to write data to the file. One row at a time or column by column.


-Writing one complete row: 

 	$myArr=array("<strong>First Name</strong>",
				"Last Name",
				"Address",
				"Age"
				);
 
	$excel->writeLine($myArr, array('text-align'=>'center', 'color'=> 'red'));

First create an array of all values and use the writeLine method to write it to file. WriteLine accepts 2 parameters. First is an array of values that you wish to write in a row. Second parameter, which is optional, is the array of style properties that you may wish to apply to whole row. All css properties will work as long as you pass them in array format.

-Writing column by column data:
You can write data on column basis too. To do this create a row first and then write columns as required.

	// this will create an empty row
	$excel->writeRow();
	// Now write columns
	$excel->writeCol("Alok");
	// inline styling can be done too
	$excel->writeCol("<span style="color:red;">Sah</span>");
	// or you can pass array of css properties
	$excel->writeCol("Pandav Nagar", array('text-align'=>'center', 'color'=> 'green','font-size'=> '22px'));
	$excel->writeCol(30,array('text-align'=>'right', 'color'=> 'brown'));

WriteCol writes data to a single cell. Just like WriteLine, WriteCol also accepts 2 parameters. First is column value and second(optional) is css properties.

4- Close the file and you are done

	$excel->close();

After you are done with writing all data, don’t forget to close the file. Your excel file is ready now.

One more thing. You can style the column data in 2 ways. Either pass an array of css properties to WriteLine() method or wrap the column data in a html element.

Also make sure that you have write permission set for your excel file.
Complete source code for example:

	include("excelwriter.inc.php");
	$fileName = "mynewxls.xls";
	$excel = new ExcelWriter($fileName);
 
	if($excel==false)
	{
		echo $excel->error;
		die;
	}
	$myArr=array(
				"<strong>First Name</strong>",
				"Last Name",
				"Address",
				"Age"
				);
 
	$excel->writeLine($myArr, array('text-align'=>'center', 'color'=> 'red'));
 
	$myArr=array("Vijay","Joshi","<span style="color:blue;">Vikaspuri</span>, Delhi",27);
	$excel->writeLine($myArr);
 
	$excel->writeRow();
	$excel->writeCol("Alok");
	$excel->writeCol("<span style="color:red;">Sah</span>");
	$excel->writeCol("Pandav Nagar", array('text-align'=>'center', 'color'=> 'green','font-size'=> '22px'));
	$excel->writeCol(30,array('text-align'=>'right', 'color'=> 'brown'));
 
	$excel->close();
	echo "Data written to file $fileName Successfully.";


Download Source

Give this article a Thumbs-Up on StumbleUpon if you like this.

Related Posts

FAQ, php

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

One Response to “How to create excel files in PHP?”

Leave Comment

(required)

(required)


9,548 spam comments
blocked by
Akismet