Create excel file with PHP

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:

main report

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:

csv report

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”&gt;

<html>

<head></head>

<body></body>

</html>

php code have to write above the page. The bold code used for save data to excel file.

Advertisements

4 Responses

  1. thanks for the tips

  2. 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

  3. Its really great. Hats off to u.

  4. How to generate the excel report in php..??
    pls give me answer of this question……..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: