Best way to generate .xls file from PHP

excel

[mbabuskov]

I need enable the website users to export some data into Microsoft Excel format. I googled around, but most solutions require Windows platform and that MS Office is installed to create the file. The website runs PHP on a Linux server, so I'm looking for PHP-only solution.

I'm currently using CSV format (via fputcsv function) and on some Windows machines the browser correctly opens the file with Excel, but it looks like different versions of Excel expect different field separators. I'm trying to find some solution that would work with all versions of MS Office.

Thanks.

[Kings on steeds]

There is PHP class PHPExcel that I use, can't link but if you Google it you'll find it.

:-)

[rocknbil]

Can you make the delimiter an option when you export the data? Drop down list or text field (less friendly.) Alternatively you can train your users to use import instead of "open," that should force selection of a delimiter.

[mbabuskov]

Can you make the delimiter an option when you export the data?

I tried this, but there are some versions of Excel that just spit some error message (invalid CTSV file, or something like that) and refuse to open it.

I found some examples how to create regular .xls file using BIFF format and used that. It seems to work fine, at least for single-byte character sets. I have still to figure out how to create .xls file with UTF8 strings.

[jspeed]

I used this script a while ago on a site where users could export search results to excel. It may not be the most efficient, but it worked for me. You can change the delimiters based on a conditional statement e.g. what version of excel do you have? (within a drop down or something)

$search = @$_GET['search'];
$column = @$_GET['c'];
$order_by = @$_GET['ob'];
$order = @$_GET['o'];

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=results.xls");
header("Pragma: no-cache");
header("Expires: 0");

// connection string to db here

@mysql_select_db($dbname) or die("Unable to select database");
$select = "SELECT columnl,column2,column3,column4,column5 FROM $dbtable WHERE ($column LIKE '%$search%') ORDER BY $order_by $order";
$export = mysql_query($select);
$count = mysql_num_fields($export);
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($export, $i)."\t";
}
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
print "$header\n$data";

Hope that helps

[mbabuskov]

As I wrote before, version of Excel I have is irrelevant. I initially managed to get it working with code very similar to yours, except is uses fputcsv instead of printing values one by one. The trick was to use php output stream. This was working fine on my machine. But I need to give this functionality to website users and they have different versions of Excel, some even non-English with specific regional settings, etc.

I currently settled at two approaches:

- to export ASCII, ISO8859_1 and ISO8859_2 data I'm creating .xls file in BIFF format directly.

- to export other character sets, I write a regular HTML table and output it as .xls file in HTTP headers. This tricks Windows to start Excel to load the file and Excel is smart enough to detect it is HTML and load it properly. Downside to this approach is that a) user gets a warning message and b) spreadsheet shows up without grid lines, so it might look weird to some users

I gave both options to users and they can pick whichever suits them better.