How can I export MySQL data to Excel using PHP and ensure proper cell distribution?

Exporting MySQL Data to Excel in PHP

When exporting MySQL data to Excel, it's crucial to ensure that the data is properly distributed across individual cells. Here's a detailed explanation to address this issue and improve the export functionality:

PHP Code:

// PHP code to connect to MySQL, retrieve data, and format it for Excel export
// Connect to MySQL
$conn = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
// Execute query and store results
$result = mysql_query($sql);
$header = '';
// Iterate over result fields to get column names
for ($i = 0; $i < mysql_num_fields($result); $i++) {
    $header .= mysql_field_name($result, $i) . "\t";
}
$data = '';
// Iterate over result rows to get data
while ($row = mysql_fetch_row($result)) {
    $line = '';
    // Iterate over row data to get individual cell values
    foreach ($row as $value) {
        if (!isset($value) || $value == "") {
            $value = "\t";
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    // Trim and append line to data
    $data .= trim($line) . "\n";
}
$data = str_replace("\r", "", $data);
// Export header and data
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo $header . "\n" . $data;
// Close MySQL connection
mysql_close($conn);

Explanation:

• The query is executed and the results are stored in the $result variable.
• The header is generated by iterating over the result fields and storing the column names separated by tabs.
• The data is generated by iterating over the result rows and cells, ensuring that each cell value is wrapped in double quotes and separated by tabs.
• Leading and trailing whitespace is removed from the data to ensure proper formatting.
• The header and data are echoed out for download as an Excel file.
• The MySQL connection is closed after the export is complete.

By following these modifications to your PHP code, each row value will now be placed in a separate Excel cell, resulting in a properly formatted export from your MySQL database.

The above is the detailed content of How can I export MySQL data to Excel using PHP and ensure proper cell distribution?. For more information, please follow other related articles on the PHP Chinese website!