Unable to export to excel file

Unable to export to an excel file using php

[ronnie1985]

hi,

I'm trying to export data to an excel file using PHP and I'm unable to do so. The data just shows up on the screen.

Here is the code:
---

<?php

include_once("db.php");
$result = mysql_query('SELECT * FROM VEHICLE_INFORMATION');
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) ¦¦ $value == ""){
$value = "\t";
}else{

# escape quotes
$value = str_replace('"', '""', $value);

# encapsulate data
$value = '"' . $value . '"' . "\t";
}

$line .= $value;
}
$data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);

# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";
}

$xlsdata=$header."\n".$data;

# This line will stream the file to the user rather than spray it across the screen
header("Content-Type: application/vnd.ms-excel; name='excel'");

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=fleet_report.xls");
header("Cache-Control: public");
header("Content-length: ".strlen($xlsdata));

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

echo $header."\n".$data;
?>
------

I got the code off the net and tried using it, but it is not working. Please help.

[dreamcatcher]

The reason its being echoed to the screen is this line:

echo $header."\n".$data;

Try readfile [us2.php.net]:

readfile($header."\n".$data);
exit;

dc

[janharders]

have you tried commenting out

header("Content-Type: application/vnd.ms-excel; name='excel'"); 

?
I'm not sure what browsers will do with multiple content-type headers....
also you might want to check with Live HTTP Headers or another tool wether there aren't any additional headers sent by you server that cause your browser to react as it does.

readfile($header."\n".$data);
exit;

Is that some magic php-syntax where php treats strings as files? iirc, readfile takes a filename and prints its content.

[ronnie1985]

Hey, thanks for the reply. Will try them both.

[rocknbil]

Try readfile

At first I thought I was missing something, but the link posted clearly says

Reads a file and writes it to the output buffer.

The searched data is not yet a file, it is dynamically output. Right? That is, unless you add the context which was only added in PHP 5.0.0, per the docs.

In addition to the previous, a couple things catch my eye.

# encapsulate data
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}

I'm not "up" on MS Excel format, but I do believe it's proprietary and may or may not contain extra header information; also I am guessing it is binary data, not plain text.

What you are doing here is generating plain text tab-delimited output, so what you should output is a .csv file, not .xls. While this will be recognized and can be imported into Excel, I doubt it's going to be directly opened in Excel. I may be wrong, but presuming that's the case, the second point is,

# This line will stream the file to the user rather than spray it across the screen
header("Content-Type: application/vnd.ms-excel; name='excel'");

So now you have a plain text file and are shooting an Excel header. If this header is recognized by the browser (which IE surely will) it will not "output a stream" (by which I am guessing you mean generate a download dialogue). It will attempt to open a "helper" application, Excel, and being it's a tab-delimited plain text file, may or may not actually read the data.

When you want to force a download of data, you want to "munge" the header or use octet-stream to insure it's an unrecognized format and the browser has no other option than to open a download dialogue. (You have multiple headers there, probably out of experimentation; only the last will be used by the browser.) This can be as simple as

header("content-type:bad/type");

but in PHP, I've had success with something like this:

## Compose $filename, I like to do a date and
## some related data, so the file name comes out
## something like "2009-07-23-data.csv"

$content = $header . "\n" . $data;
$size = strlen($content);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.$filename);
# May or may not need the following line
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $size);
echo $content;

[henry0]

Test this header

This works (most of the times :) ) although I did not need any DB/XL since a few years, but it should work.

# Stream the file rather than screen output

header("Content-type: application/octet-stream");

# replace XL file.xls with whatever you want the filename default to be
example as below is Data.xls

header("Content-Disposition: attachment; filename=Data.xls");
header("Pragma: no-cache");
header("Expires: 0");

[ronnie1985]

I'm using the following code now which works perfectly
---

// Get data records from table.
$result = "SELECT a.*, b.*
FROM VEHICLE_INFORMATION a, DEVICE_REGISTER b
WHERE a.IMEI=b.IMEI
AND b.Account_ID='$Account_ID'";

$result = mysql_query($result);

// Functions for export to excel.
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=fleet_report.xls ");
header("Content-Transfer-Encoding: binary ");

xlsBOF();

/*
Make a top line on your excel sheet at line 1 (starting at 0).
The first number is the row number and the second number is the column, both are start at '0'
*/

xlsWriteLabel(0,0,"FLEET INFORMATION REPORT");

// Make column labels. (at line 3)
xlsWriteLabel(2,0,$table_114_01);
xlsWriteLabel(2,1,$table_114_02);
xlsWriteLabel(2,2,$table_114_03);
xlsWriteLabel(2,3,$table_114_04);
xlsWriteLabel(2,4,$table_114_05);
xlsWriteLabel(2,5,$table_114_06);
xlsWriteLabel(2,6,$table_114_07);
xlsWriteLabel(2,7,$table_114_08);
xlsWriteLabel(2,8,$table_114_09);
xlsWriteLabel(2,9,$table_114_10);

$xlsRow = 3;

// Put data records from mysql by while loop.
while($row=mysql_fetch_array($result)){

xlsWriteLabel($xlsRow,0,$row['Make']);
xlsWriteLabel($xlsRow,1,$row['Model']);
xlsWriteLabel($xlsRow,2,$row['IMEI']);
xlsWriteLabel($xlsRow,3,$row['Serial_No']);
xlsWriteLabel($xlsRow,4,$row['Vehicle_Condition']);
xlsWriteLabel($xlsRow,5,$row['Plate']);
xlsWriteLabel($xlsRow,6,$row['Base_Hub']);
xlsWriteLabel($xlsRow,7,$row['Comments']);
xlsWriteLabel($xlsRow,8,$row['Description']);
xlsWriteNumber($xlsRow,9,$row['Hours']);

$xlsRow++;
}
xlsEOF();
exit();

----

The only problem is the excel file hides the data the data is larger than the cell. Is there a way to fix that?

Also If I want to make the headers BOLD then what can I do?