CODEWITHSUNDEEP
phpexcelcsv
Kevin Verhoeven
Kevin Verhoeven

Reputation: 189

Export Mysql table data to Excel through PHP: Data not showing properly in Excel

I need to export the data from the Mysql table “tblinfoaanvraag” (see here for picture of the table: http://s29.postimg.org/qeqp43z4n/mysql.png)

I have a working code with the correct query and it triggers an Excel download and the data is in the file. However, The data inside the Excel file is still formatted as an sql query result and the headers (field names) are only shown as “Array”.

So there are two things I’m struggling with at this point: format the sql-results as cells in the .csv file and the headers/table field names should be shown correctly instead of just “Array”.

Here is a screenshot of the downloaded Excel-file: http://s13.postimg.org/p0rcehehj/excel1.png

The code:

<?php

if(isset($_POST['InfoaanvragenDownloaden']))
{
    try 
{
// Variables for later use      
    $header="";
    $data="";

//connection
    $sHost = "localhost";
    $sUser = "root";
    $sPassword = "";
    $sDatabase = "corbati";
    $link = @mysqli_connect($sHost, $sUser, $sPassword, $sDatabase) or die("Oop, dbase is gone!");

//create query to select as data from your table
$select = "select * from tblinfoaanvraag ORDER BY PK_Infoaanvraag DESC";

//run mysql query and then count number of fields
$export = mysqli_query ( $link, $select ) 
   or die ( "Sql error : " . mysql_error( ) );
$fields = mysqli_num_fields ( $export );

//create csv header row, to contain table headers 
//with database field names
for ( $i = 0; $i < $fields; $i++ ) {
$header .= mysqli_fetch_fields( $export ) . ",";
}

//this is where most of the work is done. 
//Loop through the query results, and create 
//a row for each
while( $row = mysqli_fetch_row( $export ) ) {
$line = '';
//for each field in the row
foreach( $row as $value ) {
    //if null, create blank field
    if ( ( !isset( $value ) ) || ( $value == "" ) ){
        $value = ",";
    }
    //else, assign field value to our data
    else {
        $value = str_replace( '"' , '""' , $value );
        $value = '"' . $value . '"' . ",";
    }
    //add this field value to our row
    $line .= $value;
}
//trim whitespace from each row
$data .= trim( $line ) . "\n";
}
//remove all carriage returns from the data
$data = str_replace( "\r" , "" , $data );


//create a file and send to browser for user to download
header("Content-type: application/vnd.ms-excel");
header( "Content-disposition: filename=Infoaanvragen.csv");
print "$header\n$data";
exit;

} 
catch (Exception $e) 
{
    $feedback = $e->getMessage();
}
}

?>

Upvotes: 0

Views: 1219

Answers (2)

Xebax
Xebax

Reputation: 470

Use ';' as delimiter (fputcsv has a parameter for that and it's ',' by default) and set the end of lines in DOS format. At least it's what phpMyAdmin does when doing an export in the "CSV for MS Excel" format.

Upvotes: 1

Xeridea
Xeridea

Reputation: 1136

http://php.net/manual/en/function.fputcsv.php

You can pass it a file handle and your data for each row, and it formats it as a CSV line, with correct escapings. It uses a file, though you can use an in memory file stream to avoid hitting the disk

$filename = "php://memory";

$fp = fopen($filename, "w+b");

$rowNum = 1;
while( $row = mysqli_fetch_row( $export ) ) {
    if ($rowNum == 1){
        fputcsv($fp, array_keys($row)); // Headers
    }
    fputcsv($fp, $row) // Data
    $rowNum++;
}
rewind($fp);

var_dump(stream_get_contents($fp));

You would use the array keys of a row for first CSV row, and array values of each row for data.

Upvotes: 1

Related Questions