Importing and exporting data from/to an excel file is quite a common requirement for many web applications, and to import from or export data into an excel 2007 file using PHPExcel might be heavy on memory resources, PHPExcel performs the job beautifully most of the times. In one of my previous posts: Import Data Into MySQL From Excel File Using PHPExcel, I’ve discussed how to import data in your MySQL database by reading an excel file. In that post too I used the MySQLi class provided by Opencart and so I’m going to use the same one in this demonstration too.

In the import data post, we imported data into our ‘workforce’ database and ’employees’ table, so now we are going to read our data from that very same table and create an excel 2007 (.xlsx) file using it. You can download the database from the link at the bottom; it’s bundled along with the rest of the package (PHPExcel_export), in a workforce.sql file. If you want to just download the PHPExcel library, you can download it from:
https://github.com/PHPOffice/PHPExcel/tree/develop

In the package you’ll notice that we have our classes and export.php file in the root directory. Obviously, we’ll have our code to export in the export.php file and we’ll include the required libraries from the classes directory. So, in the export.php file our include code and db query will look like:

/** Include PHPExcel and MySQLi db */
require_once dirname(__FILE__) . '/Classes/DB.php';
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

//Create DB object
use DB\MySQLi;

$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'workforce';

$db = new MySQLi($hostname, $username, $password, $database);

$query = $db->query("SELECT * FROM employees");

$dataArr = $query->rows;

If you’ll print_r the $dataArr you’ll get an array like:

Array
(
    [0] => Array
        (
            [id] => 1
            [fname] => Sonya
            [lname] => Bergs
            [email] => dolor.Donec.fringilla@interdumNunc.net
            [phone] => 1-446-463-3154
            [company] => Tellus Consulting
        )

    [1] => Array
        (
            [id] => 2
            [fname] => Nathaniel
            [lname] => Mcdaniel
            [email] => Curabitur.dictum@magnaDuis.net
            [phone] => 909-0353
            [company] => Mus Company
        )
..........
..........
..........

Before we move forward, we need to add a header row, which is the top row in an excel file giving heading to each column and then format the array in a form which the PHPExcel object can better understand. To add the header row, we’ll just create an array and add it to the top of our data array using array_splice() function.

//This will become our top header row
$headerRowArr = array(array('id', 'fname', 'lname', 'email', 'phone', 'company'));

array_splice($dataArr,0,0,$headerRowArr);

Now, we have to format the array in a more compatible way with PHPExcel. We’ll call this array $excelDataArr. But you may ask what is a ‘compatible’ array? You see, to add data in a cell using the PHPExcel object, we can use the setCellValue() method as following:

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B1', 'world!');

As you can see, the setCellValue() method takes 2 parameters: 1st is the cell index like ‘A1’ or ‘B1’… and so on and the second one is the ‘value’ that we want the cell to have. Now this is quite difficult to iterate and feels impractical. Fortunately, there’s another method we can use, the setCellValueByColumnAndRow($column, $row, $value) method. This method takes 3 parameters: 1st is the column number starting from ‘0’ (zero), the 2nd one is the row number starting from ‘1’ and third one is the value we want our cell to have. So, for example let’s say we want our cell ‘A1’ to have the value ‘Hello’, the cell ‘B1’ to have the value ‘world!’ and the cell ‘C1’ to have the value ‘:)’, we’ll use the method as:

$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(0,1, 'Hello');
$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(1,1, 'world!');
$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(2,1, ':)');

As you can see, this can be iterated quite easily. The compatible array which we talked earlier about will be an array whose keys we could use to index/designate a cell. We can do that in a foreach loop as:

/*
 * The PHPExcel Library can designate a cell's location in a matrix as 
 * (column, row) i.e. (0,1), (1,1), (2,1)...
 * e.g. A1 becomes (0,1), B1 becomes (1,1) and C1 becomes (2,1).. and so on..
 * Notice that the columns start from 0 and rows start from 1
 * So to achieve an array which will have keys matching this columns and rows format, we'll
 * apply the following logic in the foreach loop 
 */
$i = 1;
foreach($dataArr as $key => $val){
	foreach($val as $value){
		$excelDataArr[$i][] = $value;
	}
	$i++;	
}

if you’ll print_r the $excelDataArr array, you’ll get something like:

Array
(
    [1] => Array
        (
            [0] => id
            [1] => fname
            [2] => lname
            [3] => email
            [4] => phone
            [5] => company
        )

    [2] => Array
        (
            [0] => 1
            [1] => Sonya
            [2] => Bergs
            [3] => dolor.Donec.fringilla@interdumNunc.net
            [4] => 1-446-463-3154
            [5] => Tellus Consulting
        )

    [3] => Array
        (
            [0] => 2
            [1] => Nathaniel
            [2] => Mcdaniel
            [3] => Curabitur.dictum@magnaDuis.net
            [4] => 909-0353
            [5] => Mus Company
        )
...........
...........
...........

Notice how the keys are different from the previously printed array. The keys of the first dimension array gives us the row number and the keys of the second dimension array gives us the column number. So now we’ll create our PHPExcel object, assign some properties to our excel sheet, iterate through the $excelDataArr array and assign value to the cells, and finally we’ll save the excel file in a given location.

Let’s first create the PHPExcel object, set the document properties and assign values to the cells:

$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("TheDevLogs")
			->setLastModifiedBy("Brajinder Singh")
			->setTitle("Office 2007 XLSX Export Document")
			->setSubject("Office 2007 XLSX Export Document")
			->setDescription("Exported doc for Office 2007 XLSX, generated by PHPExcel.")
			->setKeywords("office EXCEL 2007 PHPExcel XLSX php")
			->setCategory("Exported file");
$objPHPExcel->getActiveSheet()->setTitle('TheDevLogs Excel Export');

foreach($excelDataArr as $row => $val){
	foreach($val as $column => $value){
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($column, $row, $value);
	}
}

Since we are also using a header row, we need to stylize it to look a bit different than the rest of the document. We can do this by making the header cells value bold and having a border. With PHPExcel this is also very easy. We can do it as:

//Style the first header row to be bold and have borders
$styleArray = array(
    'font' => array(
        'bold' => true,
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
    ),
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
        )
    )
);


$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleArray);

The cells ‘A1’ to ‘F1’ are our header cells. You can read more about formatting cells on the documentation page of PHPExcel at: https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/08-Recipes.md

Now, we’ll set the output as ‘Excel2007’ using the createWriter() method and save the file in a given location as ‘MyExcelSheet.xlsx’. Every time you’ll run the script this file will get replaced. If you don’t want to do that then you can probably concatenate the name using PHP uniqid() function as ‘MyExcelSheet’.uniqid().’.xlsx’ .

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$location = __DIR__.'/files/excel/'; //Make sure this location exists

$objWriter->save($location.'MyExcelSheet.xlsx');

echo 'File created: '.$location.'MyExcelSheet.xlsx';

This is it. You file is created and now you can give the download link to anyone you want. You can download the whole demo package from here:
PHPExcel_export.zip

Leave a Reply

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