Page 1 of 1

How to format data in a file which will be open in excel ?

Posted: Tue Oct 11, 2005 11:48 am
by orangeapple
Hi there,

I am trying to generate a file with php from a database extract that i would like to be open in excel.

What i need is to format the content of the file in CSV i guess. The 'fgetcsv()' can do the job by formating the content of an existing file.

Is there an other option than to create a file with my database content, save it on the server and open/format it with 'fgetcsv()' ?

Thanks a lot for your help.

Posted: Tue Oct 11, 2005 3:09 pm
by mchaggis
You are correct that generating a csv file is the way forward... The follow is will out put the results from the query into a csv file that is downloaded:

Code: Select all

$res = mysql_query("SELECT * FROM table");

# Print the headers
header("Content-Type: application/vnd.ms-execl");
header("Content-Disposition: attachment; filename=\"MyFile.csv\"");
header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Connection: close");

# The next bit prints out the column headings
if ( $DataArr = mysql_fetch_array($res) ) {
        $HeadingFields = array();
        foreach( $DataArr as $FieldName => $FieldVal ) {
                if ( eregi('[a-z]', $FieldName) ) {
                       # We are only interested in the named elements of the array, 
                       # as mysql_fetch_array alsogives numeric entries
                       $HeadingFields[] = $FieldName;
               }
        }

        # Print the headings
        print '"'.join('","', $HeadingFields)."\"\n";
}

# We need to reset the pointer otherwise we don't get the first result
mysql_data_seek( $res, 0 );

# Next print the data
while( $DataArr = db_fetch_row($res) ) {
        print '"'.join('","', $DataArr)."\"\n";
}
As long as I have made no typo's in the above, it should do you. If you are dealing with Mac's you might what to check out phpMyAdmin as see how they do it.

Hope this helps