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”.
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.
|
1
2
|
//We are using PHPExcel Library for creating the Microsoft Excel file
require_once './PHPExcel/Classes/PHPExcel.php';
|
Step 2 : In this step, we will instantiate the PHPExcel class and activate the first Excel Sheet.
|
1
2
3
|
$objPHPExcel = new PHPExcel();
//Activate the First Excel Sheet
$ActiveSheet = $objPHPExcel->setActiveSheetIndex(0);
|
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.
|
1
2
3
4
5
6
7
8
9
10
|
//Write the Header
$Header = array('Name', 'Age');
$i=0;
foreach($Header as $ind_el)
{
//Convert index to Excel compatible Location
$Location = PHPExcel_Cell::stringFromColumnIndex($i) . '1';
$ActiveSheet->setCellValue($Location, $ind_el);
$i++;
}
|
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”.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
//Insert that data from Row 2, Column A (index 0)
$rowIndex=2;
$columnIndex=0; //Column A
foreach($data as $row)
{
foreach($row as $ind_el)
{
$Location = PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex;
//var_dump($Location);
$ActiveSheet->setCellValue($Location, $ind_el); //Insert the Data at the specific cell specified by $Location
$columnIndex++;
}
$rowIndex++;
$columnIndex = 0;
}
|
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).
|
1
2
3
4
5
6
7
8
9
10
11
|
$rowIndex = count($data) + 3;
$Location = PHPExcel_Cell::stringFromColumnIndex(0) . $rowIndex;
$ActiveSheet->setCellValue($Location, 'AVERAGE');
//Insert Average Formula -> AVERAGE(B2:B9)
$start_row = 2;
$end_row = count($data)+1;
$formula = "=AVERAGE(B" . $start_row . ":B" . $end_row . ")";
$Location = PHPExcel_Cell::stringFromColumnIndex(1) . $rowIndex;
$ActiveSheet->setCellValue($Location, $formula);
|
The last step is to save the Excel file.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
//Result File name
$randno = rand(100000, 999999);
$fileName = "ResultExcel-" . $randno . ".xlsx";
$folder = "Result";
//Create the Result Directory if Directory is not created already
if (!file_exists($folder))
mkdir($folder);
$fullpath = $folder . '/' . $fileName;
$objWriter->save($fullpath);
|
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.
SapnaEdu is a portal to learn some really cool Mathematical Shortcuts. I hope, it will help students preparing for various competitive examinations. Some of these shortcuts are explained in Vedic Mathematics and rest of them are from my own experience of teaching students. Get to know about SapnaEdu 