This discussion is archived
1 Reply Latest reply: Apr 4, 2013 7:05 AM by jflack RSS

PLSQL Web Toolkit - Display html table in Excel

user13399186 Newbie
Currently Being Moderated
First off, I am sorry this question probably does not belong in this forum but I couldn't find a good fit for it in any other forum either. Sorry.

I am running 10g database, IE9, MS Excell 2007.

I have the following simple PLSQL stored procedure:

CREATE OR REPLACE PROCEDURE EARCH1.TESTEXCELL2 IS
BEGIN
owa_util.mime_header('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', FALSE);
htp.print('Content-Disposition: filename="excel-filename.xls";' );
owa_util.http_header_close;

htp.p('<html><body><table>');
FOR i IN ( sELECT homeorg tablespace_name,
COUNT(*) tbsCount
FROM employee
GROUP BY homeorg )
LOOP
htp.p('<tr><td>'||i.tablespace_name||'</td><td>'||i.tbsCount||'</td></tr>');
END LOOP;
htp.p('</table></body></html>');
END;


I want to display the HTML table in MS excel. So from my browser I enter the url: http:\\serverName\pls\dad\testexcell2.

I am then asked "Do you want to open or save excel-filename.xls from serverName?" The buttons "Open", "Save" & "Cancel" are displayed.

I click on the "Open" button. MS Excel 2007 comes up and the following message is displayed:

"Excel cannot open the file 'excel-filename.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." an "OK" button is displayed under the message. I click on the "OK" button and the message goes away and I am left with MS Excel 2007 sitting there with out a spread sheet.


Now if I change the following line in the procedure above from:

owa_util.mime_header('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', FALSE);

to

owa_util.mime_header('application/vnd.ms-excel',FALSE);

I am asked if I want to "Open", "Save" or "Cancel". I click on the "Open" link and the following message is displayed:

"The file you are trying to open, 'testExcell2.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" "Yes" or "No"

I click on the "Yes" button and MS Excel 2007 is displayed with the correct data in the spread sheet.

How do I get this to work with out the error messages?
  • 1. Re: PLSQL Web Toolkit - Display html table in Excel
    jflack Oracle ACE
    Currently Being Moderated
    Unfortunately, you can't get rid of Excel's error message. Excel is telling the truth - you said the file is Excel, but it isn't really Excel, it is HTML. Yet, if you want Excel to open the page, you have to use the application/vnd.ms-excel mime type. We just tell our users to expect the error message, and press "OK".

    You might be able to output a file in xslx format, and it wouldn't do that. Web PL/SQL doesn't have to put HTML in the buffer. There may even be a PL/SQL package in open source that can do this. Try a web search for one.

    Edited by: jflack on Apr 4, 2013 10:03 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points