0

In using PHP to output an Excel file, it is working fine, however, each Excel file has 3 blank rows at the top. The headers are such:

<?php
header ("Expires: Mon, 28 Oct 2008 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/vnd.ms-excel");
header ("Content-Disposition: attachment; filename=\"GridExport.xls" );
header ("Content-Description: Generated Report" );
echo $content_for_layout;
?>

In looking at the 3 blank rows in a text editor such as notepad, I actually see one blank row, following by a row with a single space, following by another blank row, so that is basically equivalent to:

\n.\n\n

where the period = single space.

Finally, when I do open the file in Excel > 2003, I get this error message about the file not being in the right format, yet clicking yes opens it fine (minus the 3 blank row problem). Is it possible to send the right headers to not get this message? See a screenshot at:

https://twiki.auscope.org/wiki/pub/Grid/WfsReaderWs/OpenFileWarning.png

Yoseph
  • 608
  • 9
  • 22
  • What is in $content_for_layout? Is it actual an actual Excel file's binary data, or just some CSV data you're trying to pretend is an excel file? – Marc B Mar 13 '12 at 15:29
  • It is TAB-delimited data. Also, if I set it to nothing, or comment that line out, I still have the 3 blank row problem. – Yoseph Mar 13 '12 at 15:37
  • Check that this php script, and any other scripts you're including, don't contain the whitespace to begin with. You haven't complained about the headers not being honored, so most likely the putout is occuring AFTER your header() calls. – Marc B Mar 13 '12 at 15:41
  • Seems the blank lines were caused by "other" php files in the project having newlines or whitespace before/after the php tags. Used VIM "set invlist" to verify. This other post was the solution though: http://stackoverflow.com/a/1943780/1022147 – Yoseph Mar 13 '12 at 18:38

2 Answers2

0

If you output CVS with an XLS extension Excel 2007 and above will give you a warning that it isn't an excel file, but open it anyway. Earlier versions will not give that warning. Outputing it correctly as a CSV file will not cause that error, or prevent other CVS handling applications from handling it.

As to what the lines are - impossible to tell without knowing what is in $content_for_layout

Woody
  • 5,052
  • 2
  • 22
  • 28
  • The lines are not caused by $content_for_layout, cause if I comment out the that line, or set it to '', I still get them. – Yoseph Mar 13 '12 at 15:35
  • Btw, if I don't use an xls extension (use .csv instead), Excel won't pick up on the TAB delimiters. And if I use commas, some text may contain them, which screws everything up. – Yoseph Mar 13 '12 at 15:48
  • Excel won't pick up on tabs in a csv file (ie, comma separated values), if you want to use tab separated its extension is just text (which obviously doesn't help). There are a few php CSV libraries if you dont' want to handle the comma case yourself – Woody Mar 13 '12 at 16:17
0

Shouldn't:

header ("Content-Disposition: attachment; filename=\"GridExport.xls" );

be

header ("Content-Disposition: attachment; filename=\"GridExport.xls\"" );

I don't know if that's the answer but it looks wrong to me anyway.

user783322
  • 479
  • 1
  • 8
  • 19