How to create Excel sheets using PHP

It is a well known fact that the Excel spreadsheet editor supports CSV files. These files can easily be created using PHP. Since the OpenXML format emerged you can even use PHP to create Excel sheets (files in the XLSX format) directly.

Along with the arrival of the new Microsoft Office 2007 package new formats for saving documents were introduced with a collective name OpenXML. New file extensions DOCX, XLSX or PPTX indicate that the data included is not saved in a „bullet-proof“ binary format any more and that it can be easily accessed using the XML structure.

I believe that you all already know that, but just to make sure I will remind you that after renaming Word documents or Excel tables to a file with a ZIP extension you can easily see what the individual files consist of. Yes, strictly speaking the OpenXMLs are ZIP packages containing different images and a series of XML files.

And this relatively simple construction of the new document types provided for emergence of a PHPExcel PHP Library that you may find at the CodePlex portal, which is Microsoft’s analogy of SourceForge. PHPExcel enables you to easily create an Excel spreadsheet directly from PHP. You can forget all about exports into CSV that cause so much trouble to many beginners.

Exporting data from PHP directly to Excel

We will illustrate exporting data from a table in a database into an Excel spreadsheet and saving this spreadsheet into a XLSX file on a simple example. First copy the PHPExcel Library into a folder with the new project – unpack the Classes folder. Then prepare a index.php file that must also be copied into the project’s root folder.

All functionality in our example will be performed by the index.php file, into which the PHPExcel Library must be included. After that we create an $excel object that will allow us to set the attributes for our future file – the creator name, the name of the user who performed the last modification and the sheet name. Then we remove the automatically created Excel sheet that we will later replace with our own.

include "PHPExcel.php";
include "PHPExcel/Writer/Excel2007.php";

$excel = new PHPExcel;
$excel->getProperties()->setCreator('Jan Polzer');
$excel->getProperties()->setLastModifiedBy('Jan Polzer');
$excel->getProperties()->setTitle('Registered users list');
$excel->removeSheetByIndex(0);

To continue we will create a list of columns in Excel and a new sheet. We will set its name and the width of its columns. I will use only two columns in our example – one for a user name, the other one for his or her e-mail address. The content insertion itself will be performed by the setCellValue() function. The function has two parameters - the first one chooses a cell and the second one enters its value.

$cols = array('user' => 'A', 'e-mail' => 'B');
$list = $excel->createSheet();
$list->setTitle('Users');
$list->getColumnDimension('A')->setWidth(30);
$list->getColumnDimension('B')->setWidth(20);
$list->setCellValue('A1', 'User');
$list->setCellValue('B1', 'E-mail');

Next we must connect to the database server using the mysql_connect() function and choose the database using mysql_select_db().

if (!$conn = mysql_connect('localhost', 'username', 'password')):
  print 'Database connection has failed';
  exit;
endif;

if (!mysql_select_db('database_name', $conn)):
  print 'Database selection has failed';
  exit;
endif;

Now we will prepare a SQL command that will retrieve data from the list of users in the database. The data will be read using the mysql_query() function. As you can see, the function must be given parameters in a form of a SQL command and a link to the database connection. Handling and reviewing possible errors should not be forgotten.

$sql = 'SELECT * FROM users';
$data = mysql_query($sql, $conn);

if (!$data):
  print 'SQL syntax error.\n' . mysql_error();
  exit;
endif;

 

What is left to do now is to insert the data retrieved from the database into the Excel sheet in process. Prepare a row counter. The first row was used to write the column names, we will start entering the data from the second row. We will go through all rows from the database query result using a simple loop and use the above mentioned setCellValue() function to enter data from individual rows into the Excel sheet. The function will have the same parameters, only the row number in columns A and B will be changing according to the counter. The second parameter is not really a string, but a respective entry obtained from the row given by the database query.

$rowcounter = 2;

while ($row = mysql_fetch_assoc($data)){
  $list->setCellValue('A'.$rowcounter, $row['name']);
  $list->setCellValue('B'.$rowcounter, $row['mail']);
  $rowcounter++;
}

 

Finally you must create a file, into which the data for Excel will be stored. The following two lines will help us do that. In the first one we use the above prepared Excel object as a parameter, in the second one we introduce a path to the file, into which the data will be stored. If we use a dot, the file will be saved into the current folder, hence into the folder from which the index.php is running.

$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save('./users.xlsx');

 

The final index.php file will look as follows:

<?php

include "PHPExcel.php";
include "PHPExcel/Writer/Excel2007.php";

$excel = new PHPExcel;
$excel->getProperties()->setCreator('Jan Polzer');
$excel->getProperties()->setLastModifiedBy('Jan Polzer');
$excel->getProperties()->setTitle('Registered users list');
$excel->removeSheetByIndex(0);

 

$cols = array('user' => 'A', 'e-mail' => 'B');
$list = $excel->createSheet();
$list->setTitle('Users');
$list->getColumnDimension('A')->setWidth(30);
$list->getColumnDimension('B')->setWidth(20);
$list->setCellValue('A1', 'User');
$list->setCellValue('B1', 'E-mail');

if (!$conn = mysql_connect('localhost', 'username', 'password')):
  print 'Database connection has failed';
  exit;
endif;

if (!mysql_select_db('database_name', $conn)):
  print 'Database selection has failed';
  exit;
endif;

 

$sql = 'SELECT * FROM users';
$data = mysql_query($sql, $conn);

if (!$data):
  print 'SQL syntax error.\n' . mysql_error();
  exit;
endif;

 

$rowcounter = 2;

while ($row = mysql_fetch_assoc($data)){
  $list->setCellValue('A'.$rowcounter, $row['name']);
  $list->setCellValue('B'.$rowcounter, $row['mail']);
  $rowcounter++;
}

 

$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save('./users.xlsx');

print 'Users list has been exported.';

?>