Wednesday, August 30, 2006

Export View as MS Excel

Follow up with the previous posting, I get the idea from Sean Burgess to export view data directly to excel. The idea is basically create a HTML table from a view and display it as application/msexcel.

Create a HTML view. I name the view with alias viewname.xls




In the header add in the required tag for a table <tr> and <td>


Then create a $$ViewTemplate for the view. In the Content Type, set it as application/msexcel


Embed the view into the viewtemplate then add in the opening and closing table tag for the view.


When you call the view (http://server/dbpath/viewname.xls) it should prompt you to save the view. The saved file can be open in Excel. The similar method can be used to export to other format like words, etc.

12 comments:

Anonymous said...

This looks like something that I could really use. I went all the way through, but when I brought up the TestView.xls in IE and Firefox, it just displayed the data and never asked about saving the file. Is there something likely that I did wrong to cause that? Thanks!

William Beh said...

@Jackie, Did you set the view template content type to application/msexcel? How was the data displayed?

Anonymous said...

I do the whole thing in a LotusScript agent which gets passed the output type in the URL (i.e. &ExportType=EXCEL) You can also pass CSV, HTML, and XML. The agent can be reused and you don't need to mess with a view template, all you have to do is create a flat view and it can even deal with multi-value columns (useful if you set the export type to XML).

Anyway, to have the browser prompt to save the spreadsheet rather than just display it is this (note the second line):
Print {Content-Type:application/vnd.ms-excel}
Print {Content-Disposition:Attachment;filename="TeamMembers.xls"}

Let me know if anyone wants the full code.

-chris

Anonymous said...

@William, the contect type is indeed application/msexcel. The data shows up with everything run together. The very beginning of the data looked something like this:
#StatePlanFacilitiesStatus100FLPlan Name HereFacility Name Here
Where #, State, Plan, Facilities, and Status are all the column headers and then each row that followed was just sort of strung together.

Anonymous said...

@Chris can you post the code :D? plz?

William Beh said...

@jackie. The view have to be in HTML format with table tags (<tr>, <td>). You have to put the opening and closing <table> tag in the $$ViewTemplate

Anonymous said...

The method seems really useful and simple. I follwed the instructions and when I requested for TestView.xls (http://server/dbpath/TestView.xls)in IE,it simply displayed wat is there in the view(only the data ;ignoring the html tags).Also never prompted me for saving the file. I have set the view template content to application/msexcel....
Can u please help me in solving this out.
Thanks in advance

William Beh said...

Did you check on "Treat view content as HTML"? It's in the view property

Alexis said...

Todat I was working with my MS Excel and accidentally light down.All my data was damaged.I couldn't know what to do.But fortunately on the net I saw-microsoft excel 2007 recovery tool.Tool decided my issues very easy and free of cost.My friends were lucky too.

Anonymous said...

I use this way now, im very grateful. but i did something different, when i use table - /table in $$viewtemplate, it doesn't work, so i append table to view's first column & i append /table as view's last column. before i did it, excel's A1 cell displays all of the data as merged

Anonymous said...

I m using this for one week, i have a problem now.only 1000 lines of view entry comes to the excel sheet, although it has 9500 lines? And I check emd.view property of displayed line number and try 9999lines, but i cant find a solution. Any idea?

Luis Canelo said...

What about large views (>9999 rows)? in a view template the maximum row quantity you can show is 9999 lines, if view is larger, it will cut it... advice please!