I want to return a excel file from 1

cjunkie · Oct 29, 2003

I want to return a excel file from a cgi script. The cgi script generates some table data and can return it in HTML format. I also want the user to have the option to send it to excel. I have made this work by giving the cgi script a *.csv extension and setting the "Content-type:" to something other that "text/html" for example:

The CGI called data.csv outputs:

printf("Content-type: application/ms-excel\n\n&quot

;
followed by some data.

This is called by the following in an html page:

<p><a href="../cgi-bin/data.csv?param1=value1">Get the data from data.csv</a></p>

or

<form name="input" action="../cgi-bin/data.csv" method="Post">
<input type="submit" name="test" value="data.csv via form">
</form>

My problem(s):

1) It seems very crude to have to set the extension of the cgi script to .csv. I would like to know how to set the name and extension of the file from within the cgi (some more header info?).

2) IE 6 does not seem to be very interested in the Content-type I set in the respose header. If the cgi extension is *.csv, the IE asks if I want to save or open in Excel and it does not seem to matter what mime type I use. If the extension is *.cgi, then IE does not know what to do with the file regardless of the mime type I set. Shouldn't IE be using the mime type to decide what application to send the file to?

3) If I call my cgi data.csv, then IE offers to save the file as data.csv or open it in Excel. But, if I use a href to call the script and use a very long param=value list, then IE gives the file a random name with a .csv extension. What is going on here? Is the a restriction on the length of param=value list that I can use?

siberian · Oct 31, 2003

1) use the content disposition header :
Content-Disposition: attachment; filename=YOURFILENAME.csv

2) IE is lame that way, use the above to get around this problem.

3) Item 1 should fix this, its the same problem as (2). IE uses windows file extensions to redefine mime type and ignores the header data. So when it gets 'file.csv?bla=1&bla=2' it only checks the last 4 characters for the file extension and loses the mime type. LAME!

Hope this helps. You may want to check the Perl Excel modules, they do great stuff for direct Excel output. I use them all the time, the marketroids love it.

cjunkie · Nov 3, 2003

Siberian,

Your suggestions have been VERY helpful. Thanks!!!

Here is what worked best for me (for anyone who is interested):

The html…

Code:

<!DOCTYPE HTML PUBLIC &quot;-//W3C//DTD HTML 4.01 Transitional//EN&quot;>
<html>
<head>
<title>Test Page</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>

<body>
  <form name=&quot;input1&quot; action=&quot;../../cgi-bin/test/download2.cgi&quot; method=&quot;Post&quot;>
  <input type=&quot;submit&quot; name=&quot;test&quot; value=&quot;download&quot;>
  </form>
</body>
</html>

The cgi…

Code:

// download2.cpp : Test downloading output of cgi script as a file.
//

#include &quot;StdAfx.h&quot;  // Header required by windows, disabled on Linux.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

int main() 
{   
    /** Print the CGI response header, required for all HTML output. **/
    /** Note the extra \n, to send the blank line.                  **/
    printf(&quot;Content-type: application/vnd.ms-excel\n&quot;);
    printf(&quot;Content-Disposition: attachment; filename=table.csv;\n&quot;);
    printf(&quot;Content-Description: Data in table form - readable by Excel\n&quot;);
    printf(&quot;\n&quot;);

    /** Finally, print out the complete HTML response page.         **/
    int row, col;
    for (row = 1; row <= 10; row++)
    {
      for (col = 1; col <= 5; col++)
      {
        printf(&quot;R%dC%d&quot;, row, col);
        if (col < 5) printf(&quot;,&quot;);
      }
      if (row < 10) printf(&quot;\n&quot;);
    }

    exit(0);
    return(0);
}

int main()
{
/** Print the CGI response header, required for all HTML output. **/
/** Note the extra \n, to send the blank line. **/
printf("Content-type: application/vnd.ms-excel\n&quot

;
printf("Content-Disposition: attachment; filename=table.csv;\n&quot

;
printf("Content-Description: Data in table form - readable by Excel\n&quot

;
printf("\n&quot

;

/** Finally, print out the complete HTML response page. **/
int row, col;
for (row = 1; row <= 10; row++)
{
for (col = 1; col <= 5; col++)
{
printf("R%dC%d", row, col);
if (col < 5) printf(",&quot

;
}
if (row < 10) printf("\n&quot

;
}

exit(0);
return(0);
}
[/code]

In this example I’m using the *.csv extension so that Excel will parse the sample data. In my real application, I am outputting html formatted tables to Excel and using the *.xls extension. Excel then parses the html and makes a nice looking spread sheet based on the html table.

SirCharles · Feb 1, 2004

Where is documentation on using the Content-Disposition paramter?

I've got an ms word file that I'd like to have automagically download to client's browser. In other cases, its a wpd file that I'd like to download to either IE or Netscape. Sometimes following code works to download file wihout security/open prompt window - with shorter filenames - and sometimes it does not - with longer filenames; Rather a security/open window prompt is displayed. I noticed that if I clear the filename value, sometimes on some clients the filename is still obtained by the word processing appication, and no security/open prompt is displayed for IE?! In addition, when clicking on link from Netscape, nothing appears to happen - I have to right click on the link in order for the Netscape client to bring up security/open window. Would prefer not to have this prompt occur. But if necessary, would like to have it work same with IE and Netscape clients, and have the filename sent to the client be the same - just the filename and nothing else - no prefix URL and no *.suffix stuff.

$file_content = subtoreadfilefromdisk();
$query->header(content_type:application/ms word);
$query->header(Content_Disposition:inline;filename=$filename);
print $file_content;

Is there a correct method to use for this type of thing, where the content of the file is printed after the header information. Or would be better to use the Content_Disposition value of 'attachment...'? And specify the content differently somehow? If differently, what would you recommend?

Also, syntax references I'd seen and used uses underscore rather than hyphen to disignate Content_Disposition. Do both syntaxes work? Is one more correct than another? Again, I've found very little information on searching through the internet on this one.

cjunkie · Feb 1, 2004

I feel your pain.

Try:

http://www.w3.org/1999/07/NOTE-device-upload-19990706

If you find specific info on how IE and Netscape handle this, let me know. I never got a satisfactory handle on this and just stopped working on it when I got my application to work.

P.S. You might look at "extra path information". At one point I used the following JavaScript function to add the name of the file I wanted to download to the end of my form action...

function doExportToExcel(fileName)
{
// This can be used to pass the file name as "extra path information"
// using the format:
// CGI path[/extra path information ][?query string]
input.action += "/"+fileName;
}

...

<input name="ExcelAssumptions" type="image"
onClick='doExportToExcel("table.xls&quot

'
src="Calculators/Common/images/ExportToExcelButton.gif"
alt="Export To Excel" border="0"
class="calcOffsetDown">

SirCharles · Feb 5, 2004

I'm not invoking the CGI query from a FORM, just want to send file from server to client, and have it open automatically. You probably figured that out already.

I found that by trapping the mime type for application/msword and nulling out the filename, could get file to open up in msword without security window.

Also, lately, clients have been opening powerpoint files in viewer, automatically without security/open prompt, rather than in powerpoint editor. Probably a patch or update on the client pushed down from corporate install scripts. Was able to have security/open prompt come up for powerpoint files by changing content-disposition to "attachment" rather than "inline". In this instance, the client starts up the powerpoint editor rather than the viewer - I know it's weird behavior, but it works - for now - at least until next time.

Thanks for all your help.

fabien · Mar 5, 2004

Hi guys!

I am trying to do something similar to what cjunkie described. I have a cgi script that creates an html page
with a only a table. What I would like to do add is the option to click on a link and this will open the exact same table in a new Excel window so that the user can then save it whereever he wants to.

I am new to cgi so I am unsure how to proceed.
Referring to cjunkie's post:
1) Do I need to compile the .cpp and rename the excecutable .cgi?
2) How does the .cpp know what data to read, how are nb row and col set?
3)when you say "In my real application, I am outputting html formatted tables to Excel and using the *.xls extension." can you please give me an example?

Many thanks

cjunkie · Mar 5, 2004

Excel knows how to parse html, so what I do is send the whole table to excel. As a test, try cutting and pasting a table from the source of your html page. In IE, do view -> source. Then copy a table (<table …> to </table> tags). Save this to a file called test.xls. Then open this new file. This way you can see how Excel will parse your html table.

In your cgi script you can output:

printf("Content-type: application/vnd.ms-excel\n");
printf("Content-Disposition: attachment; filename=test.xls;\n");
printf("Content-Description: Data in table form - readable by Excel\n");
printf("\n");

Followed by your existing code to create the table part of your html page. This should give your user the option to save the file as test.xls or open it directly into Excel.

P.S. I’m using C++ (I work in MS Visual C++ 6.0 and then compile on RedHat 8) for my cgi scripts, but Perl would be more common. Also, I am using Linux and Apache as my web server. If you need more concrete examples let me know. I’ll try to help.

fabien · Mar 5, 2004

OK I see I'll try that soon. I just did not understand why you had to write a C++ program for doing this.

THanks!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I want to return a excel file from 1

cjunkie

Programmer

siberian

Programmer

cjunkie

Programmer

SirCharles

Programmer

cjunkie

Programmer

SirCharles

Programmer

fabien

Technical User

cjunkie

Programmer

fabien

Technical User

Similar threads

Part and Inventory Search

Sponsor