Complete Solution for Exporting MySQL Data to Excel Using PHP

Problem Analysis and Background

In web development, exporting database data to Excel files is a common requirement. Many developers encounter a typical issue when using PHP and MySQL for data export: all text content is incorrectly merged into a single Excel cell instead of being distributed across the expected row and column structure. This situation usually arises from improper data formatting and delimiter handling.

Core Solution

The key to resolving this problem lies in correctly setting data delimiters and formatting the output. Below is an optimized complete code implementation:

<?php
// Database connection configuration
$DB_Server = "localhost";
$DB_Username = "username";
$DB_Password = "password";
$DB_DBName = "databasename";
$DB_TBLName = "tablename";
$filename = "excelfilename";

// Establish database connection
$Connect = mysql_connect($DB_Server, $DB_Username, $DB_Password);
if (!$Connect) {
    die("Database connection failed: " . mysql_error());
}

// Select database
$Db = mysql_select_db($DB_DBName, $Connect);
if (!$Db) {
    die("Database selection failed: " . mysql_error());
}

// Execute query
$sql = "SELECT * FROM $DB_TBLName";
$result = mysql_query($sql, $Connect);
if (!$result) {
    die("Query execution failed: " . mysql_error());
}

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

// Define column separator
$sep = "\t"; // Use tab as column separator

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

// Output data rows
while ($row = mysql_fetch_row($result)) {
    $line = "";
    foreach ($row as $value) {
        if (!isset($value) || $value === "") {
            $line .= "NULL" . $sep;
        } else {
            // Handle special characters
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . $sep;
            $line .= $value;
        }
    }
    // Remove trailing separator
    $line = rtrim($line, $sep);
    echo $line . "\n";
}

// Close database connection
mysql_close($Connect);
?>

Code Analysis and Optimization

The core of the above code lies in proper data separation and formatting:

First, using tab characters (\t) as column separators is crucial for Excel to correctly recognize and split cells. Tab characters are interpreted as column separators in Excel, ensuring each field value is allocated to an independent cell.

Second, appropriate quotation wrapping is applied to data values. When field values contain special characters (such as commas, quotes, etc.), values are enclosed in double quotes with internal double quotes escaped, preventing Excel from misinterpreting the data format.

When outputting column headers, the mysql_field_name() function dynamically retrieves database field names, ensuring the exported Excel file contains correct column name information and improving data readability.

Advanced Feature Discussion

Based on reference article content, while basic export functionality is achieved, users may require more advanced features such as auto-filter functionality. In Excel, auto-filter allows users to sort and filter data, which is particularly useful when handling large datasets.

To implement auto-filter functionality, consider using more advanced Excel generation libraries such as PHPExcel or PhpSpreadsheet. These libraries offer richer features including:

• Setting cell formats and styles
• Adding formulas and calculations
• Creating multiple worksheets
• Applying auto-filter and sorting functions

It's important to note that auto-filter functionality has better support in .xlsx format (Office 2007 and later versions), while compatibility issues may exist in traditional .xls format.

Performance Optimization Recommendations

For large data volume exports, consider the following optimization strategies:

• Use paginated queries to avoid loading all data into memory at once
• Consider streaming output to reduce memory usage
• For extremely large datasets, generate CSV format files which Excel also supports well
• Use more modern database extensions (such as MySQLi or PDO) to replace the deprecated mysql extension

Error Handling and Debugging

In practical applications, comprehensive error handling mechanisms are essential:

• Check database connection and query execution results
• Verify output data integrity
• Handle character encoding issues to ensure proper display of non-ASCII characters
• Test compatibility across different Excel versions

Through this complete solution and optimization recommendations, developers can reliably export MySQL data to Excel files and ensure each field value correctly displays in independent cells.