I’m using Drupal 7.x with the Views Data Export (7.x-3.0-beta7) module to provide Excel file downloads of view data I’ve created. With it I can output a simple link, that when clicked on, provides a direct download of the Excel file consisting of the view data. It’s pretty cool. The module basically does what it’s supposed to do except that the following message is displayed when trying to open the XLS files:
The file you are trying to open, ‘filename.xls.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
This happens on both XP machines and Windows 7 machines…
In the above message, “filename” appears as if it’s hashed (i.e. - it looks like encrypted text and is about 8 or 9 characters in length–this time, it was “i7P8vU9O”). I’m not sure why “xls” is showing twice but it’s not from anything I’ve done. This message-window has 3 buttons: “Yes”, “No”, and “Help” and if I click on “Yes”, the Excel file opens perfectly fine in Excel…
I’d like to fix the issue thought if possible… Any ideas what’s causing it? I might be wrong here but after scouring the module’s code, I found what I believe are the headers that are being sent:
meta http-equiv="Content-Type" content="application/vnd.ms-excel"
meta http-equiv="Content-Type" content="text/html; charset=utf-8"
I haven’t used Views Data Export but I do have a project that could use this sort of functionality so I’ll dig into it in the next bit to see if I can make heads or tails of it. I used to export tables from ASP sending just one header:
' ASP code
Response.ContentType = "application/vnd.ms-excel"
It would open up in Excel without an error but that was from a few years back and it could be that Excel has tightened things up a bit. My spreadsheets were also tables generated with ASP and contained no macros or calculated fields so as far as Excel was concerned, they were simple static tables. I wonder if you changed the charset to something else it would change things; maybe remove it for the moment and see if that changes it for the better or worse.
Hi, Andrew. Thanks for the heads-up… I think you’re right about Excel changing things because I found some code that makes the corruption nag go away–it boils down to using PHP’s pack() function to pack data into binary output–something of which I’m lost with. I guess it has something to do with making the data Excel-friendly…
Here’s what I found through Googling:
//Beginning of file...
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
//End of file...
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
//Creates a heading...
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
//Test Data
$result='this is a test';
//Send Headers
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=test.xls ");
header("Content-Transfer-Encoding: binary ");
//XLS Data Cell
$title = 'test';
xlsBOF();
xlsWriteLabel(1,1,$result);
xlsEOF();
I’m not sure if half of those header calls are even necessary but I do know that the packing is required to fix that error that kept popping up. Now I just need to find a way to add all this into Drupal Views Data Export…