1 2 Previous Next 21 Replies Latest reply: Aug 21, 2013 8:08 AM by BluShadow RSS

    sql outputs to excel sheets

    Ora_83
      Hi;

      From sqlplus, I can write the output of the sql query to csv file.

      What I want to do is,
      Suppose my excel file which consist of two seperate sheets.
      When I execute query1 it should write the output to sheet1.
      When I execute query2 it should write the output to sheet2.

      How can I do that? Any suggestion?
        • 1. Re: sql outputs to excel sheets
          damorgan
          Either purchase a third-party product if one that does this exists or do your Excel work in Excel.

          Oracle is not a Microsoft product.
          • 2. Re: sql outputs to excel sheets
            247823
            Hi,

            There are two options.

            Option1: using SPOOL ON, it create a text file but not the csv / excel file
            Option2: using PL/SQL Built-in Package UTL_FILE, possible in required format


            Regards,
            Sailaja
            • 3. Re: sql outputs to excel sheets
              damorgan
              Neither of these options does what the OP asked.
              • 4. Re: sql outputs to excel sheets
                585426
                Hi,

                As suggested above, you will need to buy a third-party tool (like Toad) to make that happen.

                Thanks,
                Ankur
                • 5. Re: sql outputs to excel sheets
                  Billy~Verreynne
                  Ora_83 wrote:

                  What I want to do is,
                  Suppose my excel file which consist of two seperate sheets.
                  When I execute query1 it should write the output to sheet1.
                  When I execute query2 it should write the output to sheet2.
                  If you are prepared to get stuck into the new XML format used by MS Office - see [XML in Excel and the Spreadsheet Component|http://msdn.microsoft.com/en-us/library/aa140062%28office.10%29.aspx] for details.

                  Oracle enables you to create XML data sets in various flexible ways (from using a SQL query to using Oracle's integrated XML database called XDB).

                  Thus if you understand the XML format/structure expected by an Excel spreadsheet, you should be able to generate that in Oracle based on the results of SQL queries. The resulting XML can then be spooled to a physical file (using UTL_FILE for example) for an Excel executable to open.
                  • 6. Re: sql outputs to excel sheets
                    Marwim
                    Thus if you understand the XML format/structure expected by an Excel spreadsheet, you should be able to generate that in Oracle based on the results of SQL queries. The resulting XML can then be spooled to a physical file (using UTL_FILE for example) for an Excel executable to open.
                    Or you can use ready-made packages
                    https://xml-spreadsheet.samplecode.oracle.com/ or
                    https://exceldocumenttype.samplecode.oracle.com/
                    You can find others at Re: How to save a query result and export it to, say excell?

                    Regards
                    Marcus
                    • 7. Re: sql outputs to excel sheets
                      djboracle
                      You might want to look at the ExcelDocumentType and ExcelDocTypeUtils here: http://www.jasonsdevelopercorner.com/?page_id=8

                      The ExcelDocumentType is an Oracle User Defined Type (UDT) that allows a developer to generate fully functional Microsoft Excel Spreadsheets using PL/SQL directly from the database. The spreadsheets will be encoded as Microsoft Excel 2003 XML Spreadsheets. Microsoft Excel will recognize the files with both a .xml or .xls extension (Excel 2007 will ask user for permission to open the file if the .xls extension is used …). The ExcelDocumentType supports the following Excel features:

                      * Creation of multiple worksheets
                      * Custom Header and Footer
                      * Creation of custom Excel styles (color, font, number formats, text rotation, etc.)
                      * Creation and application of formulas
                      * Worksheet titles (expanded cell)
                      * Worksheet naming (sheet tabs)
                      * Creation and application of worksheet level conditional formatting.
                      * Creation of hyper-linked cells (Sheet to sheet, external links, or email links).
                      * Custom cell attributes (in case you want to apply one that isn’t already provided).

                      The ExcelDocumentType provides three direct methods of document retrieval/delivery:

                      * The document can retrieved as a CLOB.
                      * The document can be streamed directly to a web browser through a PL/SQL DAD (if delivering content to the browser using PL/SQL).
                      * The document can be retrieved in a PL/SQL table via a user defined type called ExcelDocumentLine

                      The code packaged with the ExcelDocumentType includes a PL/SQL Package (API) called ExcelDocTypeUtils that makes the creation of a spreadsheet very simple.

                      People have been using this for several years with very good results.

                      Jason Bennett
                      • 8. Re: sql outputs to excel sheets
                        Marwim
                        Hello Jason,

                        isn't this the same as in the linked https://exceldocumenttype.samplecode.oracle.com/? ;-)

                        Regards
                        Marcus
                        • 9. Re: sql outputs to excel sheets
                          djboracle
                          Yes. I just made it more obvious. A self plug :).

                          Edited by: djboracle on Feb 4, 2010 11:06 AM
                          • 10. Re: sql outputs to excel sheets
                            Marwim
                            I didn't advertise mine, but I put the link before the link to your project :-)
                            • 11. Re: sql outputs to excel sheets
                              djboracle
                              I've seen yours. Very cool stuff!
                              • 12. Re: sql outputs to excel sheets
                                MichaelS
                                What I want to do is,
                                Suppose my excel file which consist of two seperate sheets.
                                put this in a sql file and run it:
                                spool tab.xls
                                
                                prompt <HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
                                prompt  <HEAD>
                                prompt <meta name="Excel Workbook Frameset">
                                prompt   <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
                                prompt   <meta content="Excel.Sheet" name="ProgId">
                                prompt   <meta content="Microsoft Excel 11" name="Generator">
                                prompt   <LINK href="#ab">
                                prompt   <LINK href="#xy">
                                prompt <!--[if gte mso 9]><xml>
                                prompt  <x:ExcelWorkbook>
                                prompt   <x:ExcelWorksheets>
                                prompt    <x:ExcelWorksheet>
                                prompt     <x:Name>Sheet1</x:Name>
                                prompt     <x:WorksheetSource HRef="emp.xls"/>
                                prompt    </x:ExcelWorksheet>
                                prompt    <x:ExcelWorksheet>
                                prompt     <x:Name>Sheet2</x:Name>
                                prompt     <x:WorksheetSource HRef="dept.xls"/>
                                prompt    </x:ExcelWorksheet>
                                prompt   </x:ExcelWorksheets>
                                prompt  </x:ExcelWorkbook>
                                prompt </xml><![endif]-->
                                prompt  </HEAD>
                                prompt  <body>
                                prompt  </body>
                                prompt </HTML>
                                spool off
                                
                                set markup html on spool on
                                spool emp.xls
                                 
                                select * from emp;
                                 
                                spool off
                                
                                spool dept.xls
                                 
                                select * from dept;
                                 
                                spool off
                                set markup html off spool off
                                Now open tab.xsl with excel .... ;)
                                • 13. Re: sql outputs to excel sheets
                                  djboracle
                                  That's a really cool and fast way to do it. The WebFocus product from Information Builders uses that method to generate Excel. The only downside is that it limits you to a single worksheet.

                                  Edited by: djboracle on Feb 4, 2010 8:16 PM
                                  • 14. Re: sql outputs to excel sheets
                                    djboracle
                                    Never mind ... lol. I didn't look at that carefully.
                                    1 2 Previous Next