Hi guys,

I am trying to export an excel sheet. I have the following headers:

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: filename=".$filename.".xls");

This works fine but if you open in excel 2007 + you get a popup/warning ("the file you are trying to open 'filename.xls' , is in a different format than specified by the file extension..") which lets you open it but I would like to remove this message if possible

I came across this content-type for 2007+
header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

but this won't even open at all...

I get the following message: "Excel cannot open the file 'filename.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted..."

i came across a few searches that said this had to be done on the client end and then some others which recommend maybe using a class like phpExcel. I don't know if i have all the access i need to implement this class though so was wondering if anyone had any suggestions or is this something i am gonna have to investigate?

thanks

    I appreciate the reference.

    REgarding the syntax:
    I added 'attachment' and updated the filename extension:

    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=".$filename.".xlsx");

    unfortunately, it's still not opening up unless i leave the extension as xls (with the popup)...?

    also, just curious is it possible to have 2 content types? I was hoping to maybe have both formats (xls and xlsx) to cover my basis..

      As he explained, xls and xlsx are two COMPLETELY DIFFERENT documents types. This means that the binary data representing an XLS file cannot be read if the extension is xlsx and vice versa. To confirm - open excel, create a file with a single cell with the contents Hello World, then save as xls and save as xlsx then compare file size. You might think they are the same but the computer (more specifically office suites) don't recognize xls binary data as being a valid xlsx file (and vice versa).

        Derokorian;11004837 wrote:

        As he explained, xls and xlsx are two COMPLETELY DIFFERENT documents types. This means that the binary data representing an XLS file cannot be read if the extension is xlsx and vice versa.

        I understand that they are different formats, but as I mentioned I changed the headers and it's still not working...
        if i am showing a content type as an xlsx and i have excel 2007 shouldn't it recognize this?

          Hey Bradgrafelman,

          i missed this post...

          anyway, thanks for your reponse again...

          So...

          bradgrafelman;11004839 wrote:

          What do you mean you "updated" the file extension? What is the format of the actual data you're outputting? Again, that is what dictates what the "correct" file extension is...

          by "update" i meant i changed $filename . ".xls" to $filename . ".xlsx"

          so how would i find the format out then?

          can i email you a test link to show what i am trying to export? it's just selected items from an html table which i change back into a string before the form is sent..so just text i guess...

            bradgrafelman;11004850 wrote:

            Where is the data coming from that you're actually outputting to the browser? The code you've shown us so far only involves the HTTP headers - we haven't actually seen where/how you get the data those headers are to be describing.

            it's coming from a form:

            <form name="ex" action="export.php" method="post">
            <input type="submit" value="Export">
            <input type="hidden" value="" name="my_output" id="my_output">
            </form>

            there is also an html table on this page. If an item is checked in the table, it gets added to the my_output (with js). This is converted to a string before it's sent to export.php..

              bradgrafelman;11004856 wrote:

              But what does export.php do with that form submission? How does it generate .xls and/or .xlsx data?

              oh...ah...

              well, this is my export.php which just does a print:

              <?php

$out = '';



if (isset($_POST['my_output'])) {
$out .= $_POST['my_output'];
}

$filename = $file."_".date("Y-m-d_H-i",time());

$xls_header = "Post Title \t Pub Date \t Categories \n"; 

header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=".$filename.".xls");

echo $xls_header;

//Print the contents 
print $out;

//Exit the script
exit;
?>
                bradgrafelman;11004871 wrote:

                Well that explains the problem... .xls files are not plain text - they're binary data in a proprietary Microsoft format.

                Looks like what you're going after is just a plain CSV file, not an Excel Worksheet file.

                well i originally i had this working as a csv file but now i am trying to save with xlsx extension. I am having trouble finding how to do this...

                So if still wanted to actually export as an xlsx file, is it possible to convert that to binary? or because it's proprietary is that going to be very difficult to do?

                  bradgrafelman;11004877 wrote:

                  The .xlsx is the non-proprietary version (it's XML-based). Either way, you're probably going to want to find some 3rd party library/class/etc. that can generate what you want.

                  yeah, been looking into this...

                  thank you for your help.

                    Write a Reply...