We can generate reports in php page by collecting required data from mysql database we know. Sometimes we also need to save those reports into excel file format as per our clients needs. I describe bellow how can we do this:
1. User select start date and end date with UserID select from combobox, then the report will be generated below:
2. User press save as Excel button to save the report as excel file and press ok from popup dialog. The excel file will save and display the report page content as it is into excel file like below:
The code for display all report data to excel file is :
<?PHP
//start save as excell file
//display data with combo from tblDailyLogin
if(isset($_POST['btnPress'])){
$SubmitVal=$_POST['btnPress'];
if($SubmitVal==”SaveAsExcel”){
$username=”root”;
$password=”";
$database=”EntryChack”;
mysql_connect(@localhost,$username,$password);
@mysql_select_db($database) or die(“Unable to select database”);
//default sql value
$SQL=”SELECT * FROM tbldailylogin where empid=’VMC000′ Order by
login_id asc”;
if(isset($_POST['selempid']))
{
$setempid=$_POST['selempid'];
$SQL=”SELECT * FROM tbldailylogin WHERE
empid=’$setempid’ AND log_date BETWEEN ‘$SearchSDate’ AND ‘$SearchEDate’ Order by login_id
asc”;
}
$result=mysql_query($SQL) or die(‘Error, query failed’);
mysql_close();
$num=mysql_numrows($result); //echo $SQL;
//echo $num;
//for save as excel
mysql_connect(@localhost,$username,$password);
@mysql_select_db($database) or die(“Unable to select database”);
$export = mysql_query ( $SQL ) or die ( “Sql error : ” . mysql_error( ) );
$fields = mysql_num_fields ( $export );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . “\t”;
}
while( $row = mysql_fetch_row( $export ) )
{
$line = ”;
foreach( $row as $value )
{
if ( ( !isset( $value ) ) || ( $value == “” ) )
{
$value = “\t”;
}
else
{
$value = str_replace( ‘”‘ , ‘”"‘ , $value );
$value = ‘”‘ . $value . ‘”‘ . “\t”;
}
$line .= $value;
}
$data .= trim( $line ) . “\n”;
}
$data = str_replace( “\r” , “” , $data );
if ( $data == “” )
{
$data = “\n(0) Records Found!\n”;
}
header(“Content-type: application/octet-stream”);
header(“Content-Disposition: attachment;
filename=User_Wise_Attendence_Report.xls”);
header(“Pragma: no-cache”);
header(“Expires: 0″);
print “$header\n$data”;
}
}
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
“http://www.w3.org/TR/html4/loose.dtd”>
<html>
<head></head>
<body></body>
</html>
php code have to write above the page. The bold code used for save data to excel file.
Filed under: PHP
thanks for the tips
hi,
I m happy to use ur code but i have some problem i want to export the company logo in excel sheet and without using any html page i need only the original .csv format with logo it is possible?
plz send me reply on my email
Thanks