Web Application firing up Excel

[ConvertFromOldNGs]

by Rich >> Tue, 15 Jul 2008 10:50:33 GMT

Hello again,

We are currently developing a section of one of our web applications where the user can essentially query the database and get an extract of the data. We have this functionality numerous times in the main system, but have never attempted it online before. The development is now at a stage where a string is created containing the data separated by commas and Cr's, and from this i would like it to create a .csv file that opens in Excel.

Is it possible to get this string and get it to open up in Excel without the need of saving it to a physical file? I am happy to put it into a transient File object if need be but i am not sure as to how i can fire up Excel.

Can anybody provide an explanation, and ideally coded examples of how i can fire up Excel from the web app in order to display the string to the user.

Thanks in advance,

Rich

[ConvertFromOldNGs]

by John Munro >> Tue, 15 Jul 2008 13:24:49 GMT

We do something like this using an asp file. Asp allows us to set the content type, so the browser interprets it as a file to open/save rather than a web page to display.

The basic file looks like this:

<%@ language="javascript"%>
<%

Response.Clear();
Response.ContentType ="application/csv"; Response.AddHeader("content-disposition", "inline; filename=export.csv"); Response.Buffer = true;
Response.Write("field1,field2\r");
Response.Write("field3,field4\r");
Response.End();
%>

The asp file will need to have access to the data it's going to export - how you do that depends on how your web app works. In our case the asp file makes a web services connection and uses the parameters it was called with to get the desired information (I've removed that code to keep the example simple).

John

[ConvertFromOldNGs]

by Rich >> Thu, 17 Jul 2008 14:01:33 GMT

Hi John,

Thanks for your response in this matter. I am afraid that not only is my asp knowledge very limited, but my knowledge of integrating other languages into JADE is also weak. Can you please advise on how i could use this with the JADE code, i.e. where, and how, would i put the asp code to allow me to open Excel.

Rich

[ConvertFromOldNGs]

by John Munro >> Fri, 18 Jul 2008 4:11:22 GMT

When the user pushes a button or clicks a link to extract the data as a csv, you would direct the browser to the url of the asp file - for example http://www.example.com/extract.asp?data ... ta2=value2

The asp file would then need to be able to use the parameters (in this example data1 and data2) to get the data from the Jade database and return it to the user as a csv file.

The way we did this was to create a Web Services application in Jade and have the asp file connect to it using the xmlhttp object, but I'm sure there are other ways of doing this

You will need to know some javascript in order to write the asp file

Come to think of it, you might be able to avoid the whole asp solution if you're using a Jade HTML Documents Application and your subclass of JadeHTMLClass could modify its headers. I don't have much experience with Jade HTML Documents Applications, but that's the bit you really need - to set the content-type and content-disposition headers. If you can do that then you just reply with the contents of the csv file and you're home free

John

[ConvertFromOldNGs]

by Dave Patrick >> Sun, 20 Jul 2008 20:38:29 GMT

We do something similar to this to allow the download of a csv file from our JADE HTML application. You need to create the file as a Binary type file, and use writeBinary to create it rather than writeLine. This displays the 'Open or Save' file dialog on the end-user's machine - if they choose open it will automatically start whatever program on their machine is associated with the file type being downloaded.

// download a file via the web browser (binary http stream)
vars
downloadFile : File;
message : Binary;
header : String;begin

// create and set up your file with whatever contents you
// require.....
create downloadFile transient;
downloadFile.kind := downloadFile.Kind_Binary
// set filename, contents, etc
// use writeBinary
/ set contentType - indicates the type of file in this case csv contentType := 'text/csv';
// copy the file contents to the http message
downloadFile.mode := File.Mode_Input;
downloadFile.open;
message := downloadFile.readBinary(downloadFile.fileLength);
header := 'HTTP/1.1 200 OK' & CrLf &
'Content-Type: ' & contentType & CrLf & downloadFile.fileName.stripToFileName & CrLf &
'Content-Disposition: attachment;filename=' & downloadFile.fileName.stripToFileName & CrLf &
'Content-Length: ' & message.length.String & CrLf & CrLf;

// send the http reply
replyAsBinary (header, message);
epilog
delete downloadFile;
end;

This displays the 'Open or Save' file dialog on the end-user's machine - if they choose open it will automatically start whatever program on their machine is associate with the file type being downloaded.

[ConvertFromOldNGs]

by Rich >> Tue, 22 Jul 2008 11:43:06 GMT

Firstly, Thank you both for your replys.

I have tried to use your code (Dave) to use the save / open dialog box - that is exactly the functionality that i am after. However, i am having a few issues with the file. I have used the .compress method of the String class to get the contents into a Binary format and i have set all of the relevant fields on the file. My issue arises when the execution reaches the writeBinary line to copy it into the file, it throws an error that the file does not exist. this file obviously won't exist at this stage, and i only want a persistent file created if the user was to select that option on the dialog. How do i get around this problem? I have tried setting the ..allowCreate and .allowReplace methods of the File class but not had any luck.

Also, as the data within the file is stored in Binary representation, would this cause any issues upon opening the files?

Thanks for all your help so far.

Rich

[ConvertFromOldNGs]

by Dean Cooper >> Tue, 22 Jul 2008 12:20:10 GMT

Dave's code is always creating a file on the server. It creates a binary file on the server and then reads it back (into the "message" variable) to send in the HTTP reply. When the user receives the HTTP reply in their browser they'll get the Save/Open prompt. Don't use the compress method to convert your strings to binaries because this will alter the data before it's written to the file (instead of writing csv data you'll end up with compressed csv data that the user's browser will then need to know how to uncompress). Here's a JadeScript that will write a string to a binary file (creating or replacing) and then read it back in:

vars
f : File;
s : String;
b : Binary;
begin

// create the file and write the binary output
create f transient;
f.allowCreate := true;
f.allowReplace := true;
f.shareMode := File.Share_Exclusive;
f.kind := File.Kind_Binary;
f.openOutput("c:\temp\testfile.txt");
s := "hello world";
b := s.Binary;
f.writeBinary(b);
write "written file, length = " & f.fileLength.String;
f.close;

// read the entire binary file we've just written
f.mode := File.Mode_Input;
f.open;
b := f.readBinary(f.fileLength);
f.close;

// output the binary data as the original string
s := b.String;
write "read from file: " & s;

epilog
delete f;
end;

[ConvertFromOldNGs]

by Rich >> Thu, 14 Aug 2008 14:31:05 GMT

Hi Dean,

Thank you for your advice on converting to binary, it was very useful. I have used your code and Dave's code to successfully fire up excel as needed.

Also, in the HTTP header, changing the contentType from 'text/csv' to 'application/csv' allows Excel to fire up automatically and for the data to be delimitted correctly, just using 'text/csv' only saves it as a .file and doesn't know how to read it.

Thanks for all your help!

Rich