?
Solved

Export to Excel File

Posted on 2008-01-29
31
Medium Priority
?
492 Views
Last Modified: 2013-12-12
This piece of simple code doesn't seem to work any longer since installing a new server.

if( $_GET['action'] == 'excel') {
      header("Content-type: application/octet-stream");
      header("Content-Disposition: attachment; filename=spreadsheet.xls");
}

I'm trying to simply export data to an excel spreadsheet. This should give the option to download the file.

Thanks!
0
Comment
Question by:deharvy
  • 16
  • 11
  • 4
31 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 20772517
Try:
header("Content-type: application/vnd.ms-excel");

0
 

Author Comment

by:deharvy
ID: 20772598
I tried the suggested. it did not work. Any other suggestions?
0
 
LVL 82

Accepted Solution

by:
hielo earned 1600 total points
ID: 20772663
Are you positive you are passing the correct value via the correct method? I would expect either of these two to work as long as there are no spaces preceeding or trailing the value "excel":
if( strtolower($_REQUEST['action']) == 'excel') {
      header("Content-type: application/octet-stream");
      header("Content-Disposition: attachment; filename=spreadsheet.xls");
}

if( strtolower($_REQUEST['action']) == 'excel') {
      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=spreadsheet.xls");
}
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 10

Expert Comment

by:ray-solomon
ID: 20772734
Here is part of my code that I use on a site for clients to download in csv or xls format.
The csv or xls data is formatted in the $contents variable and the $filename variable is just 'whatever.csv' or 'whatever.xls'.
These headers work well with both csv and xls.

// Create some http headers
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Type: application/octet-stream"); // may also try 'application/force-download'
header("Content-Disposition: attachment; filename=\"".$filename."\";" );
header("Content-Length: ".strlen($contents));
echo stripslashes($contents);
0
 

Author Comment

by:deharvy
ID: 20772755
Thanks for all your help. Instead of using if( strtolower($_REQUEST['action']) == 'excel') to indicate equals excel, how do I say either startsWith or includes excel? Maybe that will resolve the issue for me.
0
 
LVL 82

Expert Comment

by:hielo
ID: 20772847
if( FALSE !== stristr( strtolower($_REQUEST['action']),'excel') )
{
header("Content-type: application/octet-stream");
      header("Content-Disposition: attachment; filename=spreadsheet.xls");

}
0
 

Author Comment

by:deharvy
ID: 20772937
Ok. Thanks! It's still not working and I'm not sure why. My link is: test.php?action=excel&name=sample

I'm sure the suggested codes above are accurate; I'm probably just doing something strange.
0
 

Author Comment

by:deharvy
ID: 20773923
I was able to test to validate that the action 'excel' is being met. I did so by doing:

if( $_GET['action'] == 'excel') {
 echo "test";
}

So, what else could be wrong? I tried on a different server and it works. Is there something with my PHP version?

Here is the version: PHP Version 4.4.7
0
 
LVL 10

Assisted Solution

by:ray-solomon
ray-solomon earned 400 total points
ID: 20774041
Add this code to the top of your script to see if there are any errors in your code.

error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);
ini_set('display_startup_errors','1');
ini_set('display_errors','1');
0
 
LVL 82

Expert Comment

by:hielo
ID: 20774080
Try:
Content-Type

instead of:
Content-type
0
 

Author Comment

by:deharvy
ID: 20777719
Heilo: I'm using "Content-Type". It doesn't resolve the issue.

Ray: I used your debugging code and I think it helps. I get the following:

Notice: Undefined index: action in /home/www/test.php on line 172

------------------------

Starting at line 172.......

if( $_GET['action'] == 'excel') {
      header("Pragma: public"); // required
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Cache-Control: private",false); // required for certain browsers
      header("Content-Type: application/force-download");
      header("Content-Disposition: attachment; filename=\"".$filename."\";" );
      header("Content-Length: ".strlen($contents));
      echo stripslashes($contents);
      }
0
 
LVL 82

Expert Comment

by:hielo
ID: 20779264
>>Undefined index: action
that means that the key 'action' in $_GET['action'] does not exist. Make sure you are supplying the correct key on the url. Example if you are sendig the user to:
site.com/download.php? action=excel
then the problem is that the "key" is not "action" but rather " action". Notice the leading space. Also makesure you are not supplying ACTION or Action. Provide the correct case as well.
0
 
LVL 10

Expert Comment

by:ray-solomon
ID: 20779455
hielo is correct.

You just simply need to check if the $_GET['action'] is set.

if ((isset($_GET['action'])) && ($_GET['action'] == 'excel'))
{
      // put your header code here
}
0
 

Author Comment

by:deharvy
ID: 20779988
Heilo: Thanks for the explanation. My link is: http://website/test.php?action=excel&name=sample

Ray: Thanks for the code. I used it and it removed the startup index error. However, it doesn't export to excel. I'm going to create a simple page on my website and see if that works. I'll get back to you.
0
 
LVL 10

Expert Comment

by:ray-solomon
ID: 20780230
You should post your entire code so we can see where the problem is.
For us, it would be very simple to figure out if we could see it.
0
 
LVL 82

Expert Comment

by:hielo
ID: 20780383
See if this helps you determine the values seen by the script:
if( isset($_REQUEST['action']) && ($_REQUEST['action'] == 'excel') ) {
      //header("Content-Type: application/octet-stream");
      header("Content-Type: application/vnd.ms-excel");

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

      }
       else
       {
             echo "isset: " . isset($_REQUEST['action']);
            echo "action: " . $_REQUEST['action'];
}

Just so we are clear, this line:
header("Content-Disposition: attachment; filename=spreadsheet.xls");

DOES NOT send the spreadsheet.xsl file to the browser. It merely tells the browser, it is about to receive a file named spreadsheet.xls

To actually send the contents of the file, you would need to open the file (look up fopen), read its  contents, add a Content-Lenght header, and lastly, echo/print the contents of the file. If you do not echo/print the contents of the file, the browser will not receive anything.
0
 

Author Comment

by:deharvy
ID: 20780506
At the top of the page, I see this:

isset: action:

I guess it's not finding the first condition.
0
 
LVL 82

Expert Comment

by:hielo
ID: 20780607
>>I guess it's not finding the first condition.
Correct.

If you actually provided a querystring, like this:
http://website/test.php?action=hi&name=sample

Then 'action' should report something in the else. It does not have to have excel as its value.
0
 

Author Comment

by:deharvy
ID: 20784358
Oddly enough, I used this code:
if( isset($_GET['action']) && ($_GET['action'] !== 'excel') ) {

When I did this, I got this output:
isset: 1action: excel

This indicates to me that the proper values are being met. That's frustrating.

(I know I've taken up alot of your time. Let me know when it's time to move on)
0
 
LVL 82

Expert Comment

by:hielo
ID: 20784875
Based on your previous post, we are getting somewhere. Let's cast the $_GET to a string and see if it helps:
if( isset($_REQUEST['action']) && ( strval($_REQUEST['action']) === 'excel') ) {
        echo "ready to send file!"
      }
       else
       {
             echo "isset: " . isset($_REQUEST['action']);
            echo "action: " . $_REQUEST['action'];
}

Open in new window

0
 

Author Comment

by:deharvy
ID: 20787551
It definitely outputs "ready to send file!"

However, I added:

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=spreadsheet.xls");

Unfortunately, it still doesn't export to a file. ;(
0
 
LVL 82

Expert Comment

by:hielo
ID: 20787969
>>It definitely outputs "ready to send file!"
OK. This is great news.

Simply adding:
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=spreadsheet.xls");

will not cause the server to send the file to the client. You need to actually "send" something. I explained this issue above (http:#20780383). On the code below I am "sending" the contnent by simply echoing the file contents. Try the code below.
if( isset($_REQUEST['action']) && ( strval($_REQUEST['action']) === 'excel') )
{
	header("Content-Type: application/vnd.ms-excel");
	header("Content-Disposition: attachment; filename=spreadsheet.xls");
	echo('<table><tr><th>State</th><th>Name</th></tr><tr><td>CA</td><td>California</td></tr><tr><td>NY</td><td>New York</td></tr></table>');
}
else
{
	echo "isset: " . isset($_REQUEST['action']);
	echo "action: " . $_REQUEST['action'];
}

Open in new window

0
 

Author Comment

by:deharvy
ID: 20788022
The page echos:

State      Name
CA      California
NY      New York

But not in a file, just a web page. :(
0
 
LVL 82

Expert Comment

by:hielo
ID: 20788252
Did you copy and paste from my post above. I just tested it and it works perfectly for me.
0
 

Author Comment

by:deharvy
ID: 20788458
Heilo: I really don't think the issue has to do with the code. I think there is something with the PHP version.

On a different server that uses a different version of PHP, the initial code works (as does your code above).

I'm trying to migrate to a new host that uses PHP 4.4.7. There has to be something there. What version of PHP are you testing with?
0
 
LVL 82

Expert Comment

by:hielo
ID: 20790183
I have PHP 5.2.1
BTW, look at the example by "lasitha dot alawatta at gmail dot com" on "29-Jul-2007 01:15" at http://us.php.net/header. Also, read the one right above it. Not sure if you are using IE7.
0
 

Author Comment

by:deharvy
ID: 20791785
Heilo, first off, you have been great.

Just creating a simple page, it works:

<?php
      header("Content-Type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=spreadsheet.xls");
      echo('<table><tr><th>State</th><th>Name</th></tr><tr><td>CA</td><td>California</td></tr><tr><td>NY</td><td>New York</td></tr></table>');
?>

There is definitely something in my full code that's messing things up.
0
 

Author Comment

by:deharvy
ID: 20792209
I think I made some progress:

Warning: Cannot modify header information - headers already sent by....

That's when I put the code in debugging mode. Any ideas?
0
 

Author Comment

by:deharvy
ID: 20792543
Warning: Cannot modify header information - headers already sent by (output started at /homepages/website/test.php:1) in /homepages/website/test.php on line 173
Warning: Cannot modify header information - headers already sent by (output started at /homepages/website/test.php:1) in /homepages/website/test.php on line 174

The web says it's an infamous "whitespace" issue.

My first few lines are at the bottom

-------------------------------

<?php
///*
error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);
ini_set('display_startup_errors','1');
ini_set('display_errors','1');
//*/
0
 

Author Closing Comment

by:deharvy
ID: 31426180
Thanks for all your help!
0
 

Author Comment

by:deharvy
ID: 20792803
All along, it appears the problem was the whitespace on the very first line of my code. I simply placed the file on a Unix server, used 'vi' to edit it and removed the junk.

I learned many troubleshooting routines in this post that will be valuable well into the future. Error checking, Manipulating header output, echoing to validate values... all important stuff.

Thanks to all (especially heilo) for your help!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question