How to create excel files in PHP?

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.

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("First Name",
				"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("Sah");
	// 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(
				"First Name",
				"Last Name",
				"Address",
				"Age"
				);

	$excel->writeLine($myArr, array('text-align'=>'center', 'color'=> 'red'));

	$myArr=array("Vijay","Joshi","Vikaspuri, Delhi",27);
	$excel->writeLine($myArr);

	$excel->writeRow();
	$excel->writeCol("Alok");
	$excel->writeCol("Sah");
	$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.";

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

Related Posts

• Book Review: Mastering phpMyAdmin 3.3.x for Effective MySQL Management
• Favorite tech links of the week : 17 Jan – 23 Jan, 2011
• PHP class for expanding and shortening URLs using Google URL shortener API
• Wordpress Internal Server Error 500 in wp-admin
• PHP: Shorten URLs using Google URL shortener API