Skip to main content

How to generate excel report with php and mysql



Step 1. Include phpexcel class file along with your config file and make its object with excel file name passed parameter in constructor. Its will display if any error found in next echo statement.
1require_once("excelwriter.class.php");
2
3$excel=new ExcelWriter("report.xls");
4
5if($excel==false)
6echo $excel->error;
Step 2. Now time to fetch data from mysql database. Before fetch data, call excel header having columns as values of array.
01// this will create heading of each column in excel file
02
03$myArr=array("S.No.","Company Name","Email","City","Username","Reg. Date");
04$excel->writeLine($myArr);
05
06// now fetch data from database table, there is a new line create each time loop runs
07
08$qry=mysql_query("select * from customer");
09if($qry!=false)
10{
11 $i=1;
12 while($res=mysql_fetch_array($qry))
13 {
14 $myArr=array($i,$res['company_name'],$res['email'],...);
15 $excel->writeLine($myArr);
16 $i++;
17 }
18}
Step 3.  Create link to generate excel file.
1<a href="javascript:void(0);" onClick="download();">Download Excel Report</a>
and here download function pointing to excel file
1<script language="javascript">
2function download()
3{
4 window.location='report.xls';
5}
6</script>
Thats it. This three steps demonstrate how to generate excel file successfully.
Hope this tuto surely help you to quick develop your export excel report module.

You can download complete source code.

Download

Comments

  1. very helpful.
    thank you.

    ReplyDelete
  2. Wow nice tutorial i help me a lot. post more tutorials

    ReplyDelete
  3. Very helpful. Am still facing a slight problem though, the last row in my DB doesn't get written to excel.

    ReplyDelete
  4. this method doesn't work in latest chrome and firefox.?

    ReplyDelete

Post a Comment

Popular posts from this blog

Shell Script to check tomcat status and restart

The below script checks the status of a particular tomcat status and restarts it if the tomcat does not respond....TOMCAT_HOME=/usr/local/tomcat-folder/

is_Running ()
{

        wget -O - http://yourserver.com/ >& /dev/null
 if( test $? -eq 0 ) then
  return 0
 else
  return 1
 fi
}


kill_Hanged_Processes ()
{
 echo "killing hanged processes......"
 javaProcs=`ps -efl| grep -v grep | grep java`
 if(test ! -z "$javaProcs") then
  echo "nonzero"
  processId=`echo $javaProcs | awk '{ print $2} '`
  echo "$processId"
  kill -9 $processId
 fi
}
stop_Tomcat ()
{
 echo "shutting down......"
 $TOMCAT_HOME/bin/shutdown.sh
}

start_Tomcat ()
{
 echo "starting......"
 $TOMCAT_HOME/bin/startup.sh
}

restart ()
{
 stop_Tomcat
 sleep 10
 kill_Hanged_Processes
 start_Tomcat
 sleep 60
}

send_Mail ()
{
#!/bin/bash
# script to send simple email
# email subject
SUBJECT="Telegraphindia.com went down"
# Email To ?
EMAIL="mailid1@gmail.com,mailid2@gmail.com"
# Em…

Getting access to menu from portlet in liferay

The below code describes the way of accessing menu items from the portlet. Here i have called it from jsp. The best thing about this is you can use the beautiful permission management of menu items of liferay as well without bothering about that. Writing services for fetching menu names from the layout tables will need more codes for permission managements......

Hope this helps.........


<%--
/**
* Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
*
* 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.
*/
--%>
<%@ page import="com.li…

Virus f Opyum Team

Symptoms :: 
Several processes running named f or i. 
Network choked
Apache Dead
Unknown entry  * * * * * root f Opyum Team in crontab

Investigate ::
Try to kill the f process and even root will be unable to kill it. Check file named f and i in /etc or /bin or /usr/sbin directory. Try to delete, the file f  will not delete.  Some unknown .jpg files will be there in the same folder. 

Solve :: The root is unable to delete the file f because of the immutable bit in f
To remove it use ses or chattr
Follow the below steps to remove it completely.....



remove the unknown .jpg files in system folders

#top
(kill process f) option k

#ses -i /bin/f
#rm /bin/f
#ses -i /etc/crontab
#vi /etc/crontab (and delete last line)
#reboot


Precautions ::  In my case the server was hacked bu Brute-force attack
So set a good password with a combination of upper,lower case letters and special characters.

Post your feedback or any issues you may face removing the virus.  Very less number of good threads are there explaining …