How to write an Excel file in PHP?


Introduction

In this tutorial, we will learn how to write an excel file in PHP.  At the end of this tutorial, you will learn how to create an Microsoft Excel file, write the data into particular column in a particular sheet of the Excel file.


Example

Let us assume that you would like to create an Excel file which consists of 2 columns Name and Age. We will develop the PHP code to write the data into an Excel file. And finally we will compute the average age of all the students by inserting the Excel Formula “AVERAGE”.

Data1
Steps

Step 1 : The first step is to include the PHPExcel library. We use PHPExcel library to create the Microsoft Excel File. The library is included as is as part of the complete source here. If you want to download the latest version, you can download the PHPExcel library from here.


Step 2 :
In this step, we will instantiate the PHPExcel class and activate the first Excel Sheet.

 

Step 3 : Add the Header : As seen from the Excel sheet, we need to first add two headers, “Name” and “Age” in cell A1 and  B1, respectively. Notice the function stringFromColumnIndex() converts number to Excel column name.

 

Similarly, we need to add the name and age of the students. Let us assume we have an array of array consisting of Name and age in Python variable “data”.

 

Step 4 : Computing the Average

We need to add the row AVERAGE on column A below all the names of all the students. We need to use the Excel Average Formula AVERAGE(B2:B9).

 

The last step is to save the Excel file.

 

Download

The complete PHP source for writing an Microsoft Excel file can be downloaded from here.

I hope, you find this article very useful. If you have any doubts or have any comments concerning this tutorial, Please leave a comment or contact me. I would be glad to help.


Kiran Chandrashekhar

Hey, Thanks for dropping by. My name is Kiran Chandrashekhar. I am a full-time software freelancer. I love Maths and Mathematical Shortcuts. Numbers fascinate me. I will be posting articles on Mathematical Shortcuts, Software Tips, Programming Tips in this website. I love teaching students preparing for various competitive examinations. Read my complete story.

Leave a Reply

Your email address will not be published. Required fields are marked *

Protected by WP Anti Spam