[Zope] Sending results of query to Excel

Richard Moon Richard Moon <richard.moon@nec.ac.uk>
13 Apr 2000 15:38:21 +0100


More results on this research - its not really Zope related but I'm posting these because I was asked by some people to do so.

It seems that if you either set the MIME type to something which the browser recgnises as Excel  for example 

<dtml-call "RESPONSE.setHeader('ContentType','application/vnd.ms-excel')"> 

 _OR_ call the DTML method wich produces the output something .xls then -
   1) IE 4 and 5 (on PC) will open the data in an Excel spreadsheet within the viewer (presumably using OLE). However the ability to use this is severly limited, there's no Excel toolbars or menus available.(Not ideal) I haven't found any way to turn this behaviour off.
   2) Netscape, provided you have the MIME-type entered in the Help Apps list will give you the option to save it as a .XLS file or will open Excel with the downloaded data. (This is perfect). On a Mac you can preconfigure Netscape's behaviour to either save the file (and it will save it as an Excel file), or open Excel with the data.
   3) The data will be formatted correctly within the spreadsheet provided you use tab as the separator. You can't enter tabs via the managament interface so you have to edit the DTML method in an external editor and FTP or copy/paste it in.
   
The other way to do it, which works better with IE4 and IE5 is to name the dtml method something.csv and set the MIME type to something the browser doesn;t recognise such as 'application/x-csv''. In this case, on PC the file will download to disk as a .csv file which Excel will happily open. A Mac won't make any sense of the .csv extension so you'll have to open the file within Excel. If you do it this way you must replace the tabs field separators with commas and it's best to single-quote all data to 'escape' embedded commas.

In every case I'm still getting blank lines at the top of the spreadsheet.

So there you have it - 

On 4/11/00, Richard Moon <richard.moon@nec.ac.uk> wrote:
>Results of the experiments so far (thanks for all the help)
>
>If you set the RESPONSE variable as follows (which I suspect sets 
>a mime type whcih my browser doesn't recognise) then IE 5 will download 
>the file for you. If the DTML method that contains this code is called 
>something.csv then Excel will happily open up the file as a spreadsheet 
>(I'm getting some blank lines at the top though).
>
>Interestingly this also works with Netscape 4 on a Mac, though Netscape 
>gives you the extra option of opening the file directly with an application. 
>However if you do this then Excel doesn't recognise the commas and 
>each line of output goes into one column. hmmm. If you save the file 
>to disc then you have to open the file from within Excel as the Mac 
>doesn't recognise the Windows .csv file extension. What I don't know 
>is how you tell the Mac its an Excel file (anyone ???)
>
>Here's a sample of code that works
>
><dtml-call "RESPONSE.setHeader('Content-Type', 'application/x-csv')">
>
><dtml-in Org_search>
><dtml-if sequence-start>
>Name,Town,Post code,Telephone,Org.code     
></dtml-if sequence-start>
><dtml-var name null="">,<dtml-var town null="">,<dtml-var post_code 
>null="">,<dtml-var telephone null="">,<dtml-var code null="">
></dtml-in>
>
>Note that any spaces or newlines are interpreted by Excel so you 
>have to run things together on one line as I have done.
>
>Interestingly if you leave out the setting of RESPONSE then IE5 opens 
>up Excel inside the browser (if the DTML method is called something.csv, 
>though as with the Mac it doesn;t format it properly, everythig is 
>in one column). 
>
>
>
>I think I need to research the mime types properly now (unless anyone 
>else has any ideas ?)
>
>
>
>On 4/11/00, Jerome Alet <alet@unice.fr> wrote:
>
>Note that any spaces 
>
>>On 11 Apr 2000, Richard Moon wrote:
>>
>>> Thanks for the help - I don't see why this shouldn't work with 
>
>>Zope - I
>>> just need to find out how to get that Content-type header in the 
>
>>object
>>> Zope is serving up. 
>>
>>yes, and then it should work fine.
>>
>>IMHO this should be something like:
>>
>><dtml-call "RESPONSE.setcontenttype('text/yourname')">
>>
>>(not tested, and I'm even not sure about the function name, however 
>
>>I'm
>>almost sure you have to use RESPONSE.
>> 
>>then a <dtml-in ...> to loop in your selected records, however you'll
>>have to display all results at the same time, not on a 50 results 
>
>>by 50
>>results basis just like the "normal" zope behavior. 
>>
>>good luck.
>>
>>please could you tell me the final solution, because I might be 
>interested
>>in a not so long time ?
>>
>>thanks.
>>Jerome
>>
>>
>>
>>
>>