How to Export MySQL Data to Separate Excel Cells using PHP?

Retrieving MySQL Data in Separate Excel Cells using PHP

Your original PHP script combines all text values into a single Excel cell, conflicting with your desired output of separate rows. To rectify this issue, we'll modify the code to properly format your data into distinct cells.

The updated code below includes the following changes:

1. Separate Rows: mysql_fetch_row() is utilized to retrieve data row by row.
2. Formatted Headers: The header row is populated with MySQL field names.
3. Formatted Data Rows: The data rows are parsed, separated by tabs, and quoted for Excel compatibility.
4. Excel Output: The formatted data is echoed to an Excel-compatible (.xls) file.

<?php
// MySQL connection details
$DB_Server = "localhost";
$DB_Username = "username";
$DB_Password = "password";
$DB_DBName = "databasename";
$DB_TBLName = "tablename";

// Define Excel file name
$filename = "exportfile.xls";

// Connect to MySQL
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL: " . mysql_error());

// Select database
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database: " . mysql_error());

// Execute query
$sql = "SELECT * FROM $DB_TBLName";
$result = @mysql_query($sql, $Connect) or die("Couldn't execute query: " . mysql_error());

// Set CSV separator (tabs)
$sep = "\t";

// Print column names as headers
for ($i = 0; $i < mysql_num_fields($result); $i++) {
    echo mysql_field_name($result, $i) . $sep;
}
echo "\n";

// Print data rows
while ($row = mysql_fetch_row($result)) {
    $line = '';

    for ($j = 0; $j < mysql_num_fields($result); $j++) {
        if (empty($row[$j])) {
            $line .= "NULL" . $sep;
        } else {
            $line .= '"' . str_replace('"', '""', $row[$j]) . '"' . $sep;
        }
    }

    $line = trim($line);
    echo $line . "\n";
}

// Close MySQL connection
mysql_close($Connect);

// Set file headers for Excel
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");

// Echo data to Excel file
echo $line . "\n";

This updated code will generate an Excel file with data separated into individual cells, fulfilling your requirement.

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