1 Reply Latest reply: Apr 4, 2013 9:05 AM by jflack RSS

    PLSQL Web Toolkit - Display html table in Excel

      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:

      owa_util.mime_header('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', FALSE);
      htp.print('Content-Disposition: filename="excel-filename.xls";' );

      FOR i IN ( sELECT homeorg tablespace_name,
      COUNT(*) tbsCount
      FROM employee
      GROUP BY homeorg )
      END LOOP;

      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);



      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
          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