How to Separate MySQL Data into Individual Cells in Excel Exports with PHP?

Exporting MySQL Data to Excel in PHP

Problem:

When exporting MySQL data to an Excel file, the text is combined into a single cell instead of being separated into individual cells.

Solution:

To export MySQL data to Excel with each row value in a separate cell, modify your PHP code as follows:

<code class="php">// ...
while (($row = mysql_fetch_row($result)) != FALSE) {
    foreach ($row as $value) {
        if (is_null($value)) {
            $line .= 'NULL' . "\t";
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
            $line .= $value;
        }
    }
    $line = trim($line) . "\n";
    $line = preg_replace('/\r\n|\r|\n/', "\n", $line);  // Normalize newlines

    $data .= $line;
}
// ...</code>

Explanation:

• Each row value is now iterated over and encapsulated within double quotes to comply with Excel's format.
• Newlines are normalized to ensure consistency in the Excel file.

Updated Code:

<code class="php">$queryexport = ("
SELECT username,password,fullname FROM ecustomer_users
WHERE fk_customer='" . $fk_customer . "'
");

$result = mysql_query($queryexport);
$header = '';

for ($i = 0; $i < $count; $i++) {
    $header .= mysql_field_name($result, $i) . "\t";
}

while (($row = mysql_fetch_row($result)) != FALSE) {
    $line = '';
    foreach ($row as $value) {
        if (is_null($value)) {
            $line .= 'NULL' . "\t";
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
            $line .= $value;
        }
    }
    $line = trim($line) . "\n";
    $line = preg_replace('/\r\n|\r|\n/', "\n", $line);  // Normalize newlines

    $data .= $line;
}

header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

// output data
echo $header . "\n" . $data;

mysql_close($conn);</code>

The above is the detailed content of How to Separate MySQL Data into Individual Cells in Excel Exports with PHP?. For more information, please follow other related articles on the PHP Chinese website!