Read, Write and Create Excel documents in PHP - Spreadsheet engine

Microsoft office documents is very useful to many workers, now here is PHPExcel,to make your work easier and so that you can save time, PHPExcel has many types of format you can select.Below I will show you a sample of code so that you will have knowledge on applying your codes with PHPExcel, I've used 10autofilter here because I find it fits our work and hopefully also to you.

->Download PHPExcel here

1. To start I will show you the data that we will generate on excel.

*Administration area codes credits to sourcecodester.com

and the excecuted file 

2. Now below will be the codes from 10autofilter, please make sure that you have a connection to your database.

<?php

error_reporting(0);

session_start();

 include("dbconnect.php");

/**

 * PHPExcel

 *

 * Copyright (C) 2006 - 2014 PHPExcel

 *

 * This library is free software; you can redistribute it and/or

 * modify it under the terms of the GNU Lesser General Public

 * License as published by the Free Software Foundation; either

 * version 2.1 of the License, or (at your option) any later version.

 *

 * This library is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU

 * Lesser General Public License for more details.

 *

 * You should have received a copy of the GNU Lesser General Public

 * License along with this library; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

 *

 * @category   PHPExcel

 * @package    PHPExcel

 * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)

 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL

 * @version    1.8.0, 2014-03-02

 */

/** Error reporting */

error_reporting(E_ALL);

ini_set('display_errors', TRUE);

ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Europe/London');

/** Include PHPExcel */

require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';

// Create new PHPExcel object

echo date('H:i:s').' Create new PHPExcel object'.EOL;

$objPHPExcel = new PHPExcel();

// Set document properties

echo date('H:i:s').' Set document properties'.EOL;

$objPHPExcel->getProperties()->setCreator('Maarten Balliauw')

->setLastModifiedBy('Maarten Balliauw')

->setTitle('PHPExcel Test Document')

->setSubject('PHPExcel Test Document')

->setDescription('Test document for PHPExcel, generated using PHP classes.')

->setKeywords('office PHPExcel php')

->setCategory('Test result file');

// Create the worksheet

echo date('H:i:s').' Add data'.EOL;

$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Year Assisted')

                              ->setCellValue('B1', 'Project Code')

 ->setCellValue('C1', 'Project Proponent')

 ->setCellValue('D1', 'Project Title')

 ->setCellValue('E1', 'Sector')

 ->setCellValue('F1', 'Contact Person')

 ->setCellValue('G1', 'Project Location')

 ->setCellValue('H1', 'Setup Investment')

 ->setCellValue('I1', 'Beneficiary Investment')

 ->setCellValue('J1', 'Date Approved RPMO')

  ->setCellValue('K1', 'Date Received RPMO')

 ->setCellValue('L1', 'Date of RTEC review')

 ->setCellValue('M1', 'Date endorsed to NPMO')

 ->setCellValue('N1', 'Date approved NPMO')

 ->setCellValue('O1', 'Remarks');

                            

/** edit your query here */  

$query = "select * from appointment_detail,services where (services.serviceid = appointment_detail.serviceid) and `status`='on progress' ORDER by yearassistancereceived";

$result = mysql_query($query) or die(mysql_error());

$row = mysql_num_rows($result);

for($x=0; $x<$row; $x++)

{

$servicename = mysql_result($result,$x,"servicename");

$projectcode= mysql_result($result,$x,"projectcode");

$projecttitle = mysql_result($result,$x,"projecttitle");

$update = mysql_result($result,$x,"update");

$remarks = mysql_result($result,$x,"projectstatus");

$projectlocation = mysql_result($result,$x,"limitt");

$yearassistancereceived = mysql_result($result,$x,"yearassistancereceived");

$setupinvestment = mysql_result($result,$x,"setupinvestment");

$beneficiaryinvestment = mysql_result($result,$x,"beneficiaryinvestment");

$confname = mysql_result($result,$x,"charge");

$conmiddlename = mysql_result($result,$x,"contactmiddlename");

$conlname = mysql_result($result,$x,"contactlastname");

$contactperson = $confname." ".$conmiddlename." ".$conlname;

$update = mysql_result($result,$x,"update");

$sector = mysql_result($result,$x,"sector");

$projectstatus = mysql_result($result,$x,"projectstatus");

$expectedoutput = mysql_result($result,$x,"expectedoutput");

$datereceivedbyrpmo  = mysql_result($result,$x,"datereceivedbyrpmo");

$dateapprovednpmo = mysql_result($result,$x,"dateapprovednpmo");

$dateendorsedtonpmo = mysql_result($result,$x,"dateendorsedtonpmo");

$dateapprovedatrpmolevel = mysql_result($result,$x,"dateapprovedatrpmolevel");

$dateofrtecreview = mysql_result($result,$x,"dateofrtecreview");

    $dataArray = array(

        array(

$yearassistancereceived,

            $projectcode,

            $servicename,

$projecttitle,

$sector,

$contactperson,

$projectlocation,

$setupinvestment,

$beneficiaryinvestment,

$dateapprovedatrpmolevel,

$datereceivedbyrpmo,

$dateofrtecreview,

$dateendorsedtonpmo,

$dateapprovednpmo,

$remarks

        )

    );

    $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A'.($x+2));

}

// Set title row bold

echo date('H:i:s').' Set title row bold'.EOL;

$objPHPExcel->getActiveSheet()->getStyle('A1:O1')->getFont()->setBold(true);

// Set autofilter

echo date('H:i:s').' Set autofilter'.EOL;

// Always include the complete filter range!

// Excel does support setting only the caption

// row, but that's not a best practise...

$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file

echo date('H:i:s') , " Write to Excel2007 format" , EOL;

$callStartTime = microtime(true);

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

$objWriter->save(str_replace(__FILE__, 'C:/DOSTX On Progress Projects.xlsx', __FILE__));

$callEndTime = microtime(true);

$callTime = $callEndTime - $callStartTime;

echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;

echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;

// Echo memory usage

echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

// Save Excel 95 file

echo date('H:i:s') , " Write to Excel5 format" , EOL;

$callStartTime = microtime(true);

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

$objWriter->save(str_replace('.php', '.xls', __FILE__));

$callEndTime = microtime(true);

$callTime = $callEndTime - $callStartTime;

ECHO $filename= str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;

echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;

echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;

// Echo memory usage

echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo memory peak usage

echo date('H:i:s').' Peak memory usage: '.(memory_get_peak_usage(true) / 1024 / 1024).' MB'.EOL;

// Echo done

echo date('H:i:s').' Done writing files'.EOL;

  echo "<script>alert('File saved at drive C')</script>";

?>

  /** Happy coding! :) */