Hi Friends,
Recently for one of my project work, I need to generate excel report for customer data using php and mysql. Here I am posting tutorial of generate excel report in easy steps.
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.
require_once("excelwriter.class.php");
$excel=new ExcelWriter("report.xls");
if($excel==false)
echo $excel->error;
Step 2. Now time to fetch data from mysql database. Before fetch data, call excel header having columns as values of array.
// this will create heading of each column in excel file
$myArr=array("S.No.","Company Name","Email","City","Username","Reg. Date");
$excel->writeLine($myArr);
// now fetch data from database table, there is a new line create each time loop runs
$qry=mysql_query("select * from customer");
if($qry!=false)
{
$i=1;
while($res=mysql_fetch_array($qry))
{
$myArr=array($i,$res['company_name'],$res['email'],...);
$excel->writeLine($myArr);
$i++;
}
}
Step 3. Create link to generate excel file.
<a href="javascript:void(0);" onClick="download();">Download Excel Report</a>
and here download function pointing to excel file
<script language="javascript">
function download()
{
window.location='report.xls';
}
</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.
BE SMART BE HAPPY…
January 31st, 2011 at 6:43 am
hi…
i’ve try your code but i get an error
The requested URL /report.xls was not found on this server.
Can u help me please
February 7th, 2011 at 10:35 am
Good Job ! Neatly done.. Keep doing stuffs like this .
February 22nd, 2011 at 5:42 pm
@han This happens when you have removed generated xl sheet from your folder(under www / script folder).
Just refresh (Ctrl+F5) your browser and it will work fine then.
February 22nd, 2011 at 5:45 pm
@Surendar Thanks
June 17th, 2011 at 6:36 pm
………works well ! nice work ! . But i still need help ,I want codes to automatically sum up values entered into spreadsheet cells i.e if i type in 5 into cell A1 and 5 into cell B1 then 10 automatially displays in cell C1 , i know this can be achieved using the formular bar , but can i call up a predetermined template like a report which just fills in values
How do i go about it ?
Thankxxx…………….
June 23rd, 2011 at 9:04 am
hi,
i need help. do you know how to show the excel file on the browser instead of downloading it? that way they have the option to check the excel file?
any suggestion is great.
thanks in advance!
June 28th, 2011 at 10:38 pm
Thanks,its perfect
September 7th, 2011 at 7:32 am
how to make the content of excel heading in bold
November 26th, 2011 at 5:03 am
Hi,
I new to this coding. I’m designing a web application in PHP which connects to a database in MySQL, this application will generate reports according to the fields that a user choose from diferent tables, there is a way to do this,prefering only by code. (of) Open Source softwares if any..
I will be happy if you brief in step by step.
December 2nd, 2011 at 9:39 am
Sir,
I used your script and it works fine.Please tell me how to send this excel file through php mail function.Without clicking the download link how to send the file.
December 11th, 2011 at 1:49 pm
how can i get the data from mysql get a border?
December 27th, 2011 at 2:28 pm
Just great! works fine.