Bagaimana untuk Memisahkan Data MySQL ke dalam Sel Individu dalam Eksport Excel dengan 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>

Atas ialah kandungan terperinci Bagaimana untuk Memisahkan Data MySQL ke dalam Sel Individu dalam Eksport Excel dengan PHP?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!