create excel file of database query result in php

Question

I create excel file using php.It has contain Query Result data from database.Excel file generate and download very well but when i opened i found it gives some format error and some coding error also. enter image description here

function generate_excel($conn) {
 $filename = "website_data_" . date('Ymd') . ".xls";

 function cleanData(&$str) {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if (strstr($str, '"'))
        $str = '"' . str_replace('"', '""', $str) . '"';
}

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-type: application/octet-stream;charset=utf-8");

 $flag = false;
 $qry = "SELECT ContactId,UniqueContactId FROM Contacts ORDER BY ContactId  ";
 $stmt = sqlsrv_query($conn, $qry);
 $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);

while (false !== ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))) {
    if (!$flag) {
        // display field/column names as first row
        echo implode("\t", array_keys($row)) . "\r\n";
        $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
}
exit;
}

AVK · Accepted Answer · 2015-04-17 05:05:01Z

1

sqlsrv_fetch_array() can return either NULL or FALSE. You can make your while statement as follows:

while (NULL !== ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))) {
    if(!$row)
        break ;

    //...
}

Here's PHP function reference: http://php.net/manual/en/function.sqlsrv-fetch-array.php

answered Apr 17, 2015 at 5:05

AVK

6459 silver badges22 bronze badges

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

2 Comments

Vishal Patel Over a year ago

It's working but now some data is in local language 'Gujarati' i add UTF-8 but its not display proper. have you any idea about it? header("Content-type: application/octet-stream;charset=utf-8");

AVK Over a year ago

Setting a header will not help. The problem is probably in data itself and how it is retrieved from the database. Try explicitly setting charset to UTF-8 in the server connection. Here's an example

Collectives™ on Stack Overflow

create excel file of database query result in php

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related