It is very common to have the list exportable to save the data from the database to local machine. Doing this with online web list data is not that hard. We would like to recommend using phpExcel to get this work done. However, this tutorial not only introduce that library but also introduce how to using that library dynamically meaning that create your own library to work with the existing library. What does using that library dynamically really mean? To answer this question, let’s suppose there are 4 table lists in our website with different data like customerinfo, customerlog, songlist, and newslist. Therefore, using that library dynamically means having a standard php file containing our own library and be able to be used for all data list type by just identifying the list type.

INSTRUCTION

HTML Script (list.php)

<img src="images/excel_icon_v.png" onclick="exportData('customerInfo',0);" title="Export only this page" />
<img src="images/excel_icon.png" onclick="exportData('customerInfo',1);" title="Export all pages" />

<input type="hidden" id="visibleStringSQL" value="SELECT * FROM tblcustomer WHERE accountType=2 ORDER BY id desc LIMIT 0,20" />
<input type="hidden" id="allStringSQL" value="SELECT * FROM tblcustomer WHERE accountType=2 ORDER BY id desc" />

There are some important things to work with html. In the html script, we need to have two export buttons that are normally placed at the top of the table list. These two buttons are for visible page export and all pages export that are identified by setting the second parameter value to 0 and 1 where 0 for visible page export and 1 for all pages export. We also need to set type of list data to be exported by setting value to the first parameter of the exportData function.

The two input elements are needed to store the SQL string of those two export list type as shown in above. In the list.php, we need to display the list of data to be exported. We recommend using the list navigator that can be found at Ajax and PHP List Navigator. Therefore, working with that tutorial, we have to prepare the SQL string of each list type to the value of each input element. As shown above, the example is already given:

• SELECT * FROM tblcustomer WHERE accountType=2 ORDER BY id desc LIMIT 0,20

The SQL for export the active or visible list page that is limited to 20 rows

• SELECT * FROM tblcustomer WHERE accountType=2 ORDER BY id desc

The SQL for exporting all list pages from the table of the database.

JavaScript Export Function (functions.js)

function exportData(listName,type){//0: export only visible page, 1: export all records found
if(type==0){
var stringSQL = $("#visibleStringSQL").val();
}else if(type==1){
var stringSQL = $("#allStringSQL").val();
}
$.post("phpExcel/exportExcel.php",
{listName:listName, stringSQL:stringSQL},
function(data){window.location.href=data;});
}

This is the JavaScript function used to request to file exportExcel.php by using jQuery Ajax to send the required data like stringSQL and listName. The stringSQL gets from the value of the input element in the main list page.

PHP Excel Export Script (exportExcel.php)

/** 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 />');	
/** Include PHPExcel */
require_once 'phpexcel/PHPExcel.php'; //phpExcel library
include 'connect_db.php';//we need function exec_query_utf8 to query the utf8 data

At the beginning of script, we need to include the phpExcel library that can be found at www.phpexcel.net and the database connection function exec_query_utf8 is also needed to.

$listName=$_POST["listName"];
$stringSQL=stripslashes($_POST["stringSQL"]);
$validRequest=false;	
if($listName=='customerInfo'){		
$selectedFields='username,accountType,fullname,country,contactNumber,registerDate,actviated';
$qryString=str_replace('*',$selectedFields,$stringSQL);		
$columnName=explode(',',$selectedFields);		
$listTitle='Customer Information';
$sheetName='CustomerInfo';
$fileName='CustomerInfo';	
$validRequest=true;
}elseif($listName=='customerLog'){		
$selectedFields='userid,logType,activity,dateTime,publicip,ispName';
$qryString=str_replace('*',$selectedFields,$stringSQL);
$columnName=explode(',',$selectedFields);		
$listTitle='Customer Activity Log';
$sheetName='CustomerLog';
$fileName='CustomerLog';
$validRequest=true;
}	
//check if request is valid.
if(!$validRequest){echo 'Invalid Request!';exit;}

This block contains the needed variables used to create excel file where there two most important variables, listName and stringSQL gotten from the Ajax request. We also need to do the IF condition to assign the specific data to the excel file by identifying the data list type. We also have a variable named validRequest used to check whether the request is valid or not.

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();	
$startRowIndex =3;
$exportedDate = date("d-M-Y H:i:s A");
$letters = range('A', 'Z');
$getSQLCondition = getStringBetween($stringSQL,'where','order');
$borderStyle = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$cellHeadColor=array('type'=>PHPExcel_Style_Fill::FILL_SOLID,'startcolor'=>array('rgb'=>'d4d4d4'));
$bgRedColor=array('type'=>PHPExcel_Style_Fill::FILL_SOLID,'startcolor'=>array('rgb'=>'fe96a6'));

Now, we are creating a new phpExcel object and assigning some styles to its header and cells.

// merge cell
$objPHPExcel->getActiveSheet()->setCellValue("A1",$listTitle)->mergeCells("A1:".$letters[count($columnName)]."1");	
//not use count()-1 bcoz we add No. column which is not in fieldlist			
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setSize(15)->getColor()->setRGB('6F6F6F');	
$objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'No.');	
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($borderStyle);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->applyFromArray($cellHeadColor);

After the excel object is created, we need to merge the top row cell for making the list title name.

//create excel list header name. the list name is taken from the table fieldname
for($i=0;$i<=count($columnName)-1;$i++){
$objPHPExcel->getActiveSheet()->setCellValue($letters[$i+1].'2', ucfirst($columnName[$i]));
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle($letters[$i+1].'2')->applyFromArray($borderStyle);
$objPHPExcel->getActiveSheet()->getStyle($letters[$i+1].'2')->getFill()->applyFromArray($cellHeadColor);	
$objPHPExcel->getActiveSheet()->getStyle($letters[$i+1].'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}

Then, in this stage, we are assigning the list header name by getting data from the columnName array through looping each element of that array and assign its value to the list header row.

$result = exec_query_utf8($qryString);
$autoNum=1;
while($row = mysqli_fetch_array($result)){
$currencySign='';
$objPHPExcel->getActiveSheet()->setCellValue('A'.$startRowIndex, $autoNum);		
for($i=0;$i<=count($columnName)-1;$i++){
$newData=$row[$i];
if($columnName[$i]=='active'){
if($row[$i]==1){$newData='Yes';}elseif($row[$i]==0){$newData='No';}
}
$objPHPExcel->getActiveSheet()->setCellValue($letters[$i+1].$startRowIndex, strip_tags($newData));		
}		
$objPHPExcel->getActiveSheet()->getStyle('A'.$startRowIndex.':'.$letters[count($columnName)].$startRowIndex)->applyFromArray($borderStyle);//not use count()-1 bcoz we add No. column which is not in fieldlist				
$startRowIndex++;$autoNum++;
}

We are now in the important stage of the script where the data query is placed. We need to do the data query using the SQL string gotten from the variable qryString and assign the data from each row of the database to the excel cells. In case we want to show the caption of data not the exact data value, for example, for the ‘active’ column value is number 1 or 0. To do this, we need to do the IF condition by matching the list header name to the table filed name of the database to rewrite the exact value to any caption we want.

//display query info to the bottom excel table
$objPHPExcel->getActiveSheet()->setCellValue('A'.($startRowIndex+1), 'Queried By: '.$getSQLCondition)->mergeCells('A'.($startRowIndex+1).':'.$letters[count($columnName)-1].($startRowIndex+1));
$objPHPExcel->getActiveSheet()->setCellValue('A'.($startRowIndex+2), 'Exported Date: '.$exportedDate.' (GTM 0)')->mergeCells('A'.($startRowIndex+2).':'.$letters[count($columnName)-1].($startRowIndex+2));
$objPHPExcel->getActiveSheet()->setCellValue('A'.($startRowIndex+3), 'Exported By: '.strtoupper(username))->mergeCells('A'.($startRowIndex+3).':'.$letters[count($columnName)-1].($startRowIndex+3));

Then, we will have some information to place at the bottom of the list just for reference.

//set excel filename to be created
$objPHPExcel->getActiveSheet()->setTitle($sheetName);
$excelName = $fileName.".xlsx";

After all styles and data are successfully set, we are now assigning the excel file name and sheet name.

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);	
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($excelName);

Having done creating excel style, format, and data, the excel file is now ready to be created and save.

//now the excel file is successfully created, so just return the file URL.
echo 'phpExcel/'.$excelName;

Alright, the excel file with data is now successfully created and saved to the server host. Then, we just return the just created excel file’s URL back to the Ajax request and right after that the Ajax will start to access the URL to start the file download.

Similar Tutorials

Comments