How to php export to excel

<?php
$filename ="excelreport.xls";
$contents = "testdata1 \t testdata2 \t testdata3 \t \n";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $contents;
 ?>

This is the simplest way to generate an excel report.

hi, i am janardhan..
i want some more coding details about export to excell sheet.. suppose i am retriving data from database and storing the values in a table. i want to export in an excell sheet with proper header ..how can i develop this.?

Replace testdata in the above example with the database values.

Can I change the color of contents ?
e.g testdata1 ---> blue
testdata2 ---> red
etc ...

Thanks!

<?php
$filename ="excelreport.xls";
$contents = "testdata1 \t testdata2 \t testdata3 \t \n";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $contents;
 ?>

This is the simplest way to generate an excel report.

Hi,
Let me confess, I am ZERO in php.
I purchased one cd which has php files embedded with html page then scrolling pages in php formate withen windows of html .
Main data in in php file .
How to get this data in office document formate ( either MS Word or MS Excel)? Can some one help me in doing so....I am fed up.

Code is working well in localhost. When I Upload it, it is not exporting to excel,but printing it to a page.

$result=mysql_query("select * from tbl_name");
    function xlsBOF()
    {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
    return;
    }
    function xlsEOF()
    {
    echo pack("ss", 0x0A, 0x00);
    return;
    }
    function xlsWriteNumber($Row, $Col, $Value)
    {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
    }
    function xlsWriteLabel($Row, $Col, $Value )
    {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
    return;
    }
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=list.xls");
    header("Content-Transfer-Encoding: binary ");
    xlsBOF();
     
    xlsWriteLabel(0,0,"Heading1");
    xlsWriteLabel(0,1,"Heading2");
    xlsWriteLabel(0,2,"Heading3");
    $xlsRow = 1;
    while($row=mysql_fetch_array($result))
    {
    xlsWriteNumber($xlsRow,0,$row['field1']);
    xlsWriteLabel($xlsRow,1,$row['field2']);
    xlsWriteLabel($xlsRow,2,$row['field3']);
    $xlsRow++;
    }
    xlsEOF();
commented: Good +1

Hey Karthik.R,
Thanks for your Reply.I resolved my Error. Actually it was headers problem. I turned off all the errors and warnings. Now I have another problem. I am exporting time to ods file. After exporting in ods(openoffice) file, the Time (e.g 08:00) is showing as 08:00:00 AM. I want to show it as it is in my db. Do you know have any idea about that. Kindly Post here.

Sorry i'm not clear with your question. What you mean by ods file?

<?php if (!$HTTP_GET_VARS['submit']) { ?>

<?php
echo "Export and Save Customer Data onto your Local Machine";
echo '<form action="'. $phpself.'">';
echo '<input type="submit" value="Export" name="submit"></form>';
?>

<?php
}
else
{
$contents="Board Category,Questions,Answers\n";
$user_query = mysql_query('select
f.faqdesk_id
, c.categories_id
, f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
from faqdesk_description as f
join faqdesk_to_categories as f2c
on f.faqdesk_id = f2c.faqdesk_id
join faqdesk_categories_description as c
on f2c.categories_id = c.categories_id');
while($row = mysql_fetch_array($user_query))
{
$contents.=$row[categories_name].",";
$contents.=$row[faqdesk_question].",";
$answer = str_replace(',', '\,', $row[faqdesk_answer_short]); // escape internalt commas
$contents.=$answer."\n";
}
$contents = strip_tags($contents); // remove html and php tags etc.
Header("Content-Disposition: attachment; filename=export.csv");
print $contents;

?>

Thanks a lot for yor time to write this code down.

Have a nice day

@Karthik_pranas great! that works like a charm! thanks!!

can anyone tell how to print excel sheet values in php?

<?php
$filename ="excelreport.xls";
$contents = "testdata1 \t testdata2 \t testdata3 \t \n";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $contents;
?>

You can try Exls (XMlSpreadSheet) in exls.ru (has demo and examples of templates of xls files)

can i make some excel file without window prompt

can i export some data from php web page to excel

<?php
/*******EDIT LINES 3-8*******/

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "root"; //MySQL Username     
$DB_Password = "";             //MySQL Password     
$DB_DBName = "demo";         //MySQL Database Name  
$DB_TBLName = "employee"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from employee";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>  

thanks for the code

i have report page showing the result card of students.
I want to export it in excel but the only problem I am facing is that i can only fetch the data from db but the remaining stuff like conditions on averages total marks I am not able to export it please reply as soon as possible

<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Facemappsoft.xls");
header("Pragma: no-cache");
header("Expires: 0");
?>

When i execute this i got error like below when am opening the downloaded .xls file....

"The file You are trying to open Facemappsoft.xls is in a different format than file extension"

Can any one help me to resolve this right now???

Member Avatar

Any data in the file?