Native XLS File from Query

Most web programs which output a so-called XLS format don't really output XLS, they output CSV, which has it's limitation. This script creates REAL XLS spreadsheets from any database query.

Start Script:
// put your authentication stuff here. don't leave this one open to public yo!

// Put your db connect stuff here.

// i pass a query to this from a form.
$query = stripslashes($_POST['Sql']);
// what i want the filename to appear as when the user is prompted to save
// the file. Agian, posted from another page
$filename = stripslashes($_POST['Filename']);
$result = mysql_query($query) or die(mysql_error());;
$totalRowsResult = mysql_num_rows($result);

// Excel begin of file header
function xlsBOF() {
print(pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0));
return;
}

// Excel end of file footer
function xlsEOF() {
print(pack("ss", 0x0A, 0x00));
return;
}

// Function to write a Number (double) into Row, Col
function xlsWriteNumber($Row, $Col, $Value) {
print(pack("sssss", 0x203, 14, $Row, $Col, 0x0));
print(pack("d", $Value));
return;
}

// Function to write a label (text) into Row, Col
function xlsWriteLabel($Row, $Col, $Value) {
$L = strlen($Value);
print(pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L));
print($Value);
return;
}

// tell browser that a xls file is on it's way.
Header("Content-Type: application/vnd.ms-excel");
Header("Content-Disposition: attachment; filename=$filename");

// start the file
xlsBOF();

// generate first row with field names
$row = 0;
$col = 0;
$headersResult = mysql_fetch_assoc($result);
while(list($key,$val) = each($headersResult)){
xlsWriteLabel($row,$col,$key);
$col++;
}
$row ++;

// now spit out the data.
mysql_data_seek($result,0);
while ($rowsResult = mysql_fetch_assoc($result)){
// reset($rowsResult);
$col = 0;
while(list($key,$val) = each($rowsResult)){
if(is_numeric($val)){ // data in integer
xlsWriteNumber($row,$col,$val);
} else
if(is_string($val)){ // data is char
xlsWriteLabel($row,$col,$val);
}
$col++;
}
$row ++;
}

// end the file
xlsEOF();

End Script: