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

Question

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();
}
}

?>

Xeridea · Accepted Answer · 2015-08-05 18:21:23Z

1

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.

edited Aug 5, 2015 at 18:21

answered Aug 5, 2015 at 18:10

Xeridea

1,1261 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Verhoeven Over a year ago

Thank you, i'll give this a try.

Xebax · Accepted Answer · 2015-08-05 19:26:14Z

1

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.

answered Aug 5, 2015 at 19:26

Xebax

4684 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related