Exporting MySQL Data to Excel in PHP with Separate Cells

You are encountering an issue while exporting MySQL data to Excel, where all the text is being displayed in a single cell instead of separate cells for each row value. This article aims to provide a solution to this problem by addressing each line of your code and recommending appropriate changes.

Line 12: $count = mysql_num_fields($result);

This line counts the number of columns in the result set and assigns it to the variable $count.

Line 14: $header .= mysql_field_name($result, $i)."\t";

This line appends the column names to the $header variable, separated by tabs.

Line 24: if(!isset($value) || $value == ""){ $value = "\t"; }

In this line, if the cell value is empty or undefined, it is replaced with a tab. This ensures that every cell has at least a space.

Line 28: $data .= trim($line)."\n";

Here, the trimmed line of data is appended to the $data variable, separated by newlines.

Line 38: header("Content-Disposition: attachment; filename=exportfile.xls");

In this line, the header is set to force the browser to download the file as an Excel document named exportfile.xls.

Line 52: $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:
" . mysql_error(). "
" . mysql_errno());

This line executes the SQL query and handles any potential errors.

Line 63: header("Pragma: no-cache"); and header("Expires: 0");

These headers disable caching to ensure that the latest data is downloaded upon each request.

Line 92: print trim($schema_insert);

In this line, the trimmed data is printed out.

Enhanced Code:

Here is an enhanced version of your code that should resolve the issue of exporting data to separate cells in Excel:

$DB_Server = "localhost";
$DB_Username = "username";
$DB_Password = "password";
$DB_DBName = "databasename";
$DB_TBLName = "tablename";
$filename = "excelfilename";

$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");

$count = mysql_num_fields($result);

for ($i = 0; $i