Spooling of data into different sheets in a Excel

Can anyone help me spooling of data into different sheets for different queries in a excel from Oracle.

Query开发者_如何学Python1-->Sheet1

Query2-->Sheet2

Query3-->Sheet3

Thanks

---

A fellow named Jason Bennett has a package called ExcelDocumentType. Haven't done much with it besides download it, but it looks promising.

---

I have developed ORA_EXCEL www.oraexcel.com, an Oracle PL/SQL package that can create Excel documents with sheets.

Code that will create Excel document with data based on your question:

BEGIN  
   ORA_EXCEL.new_document; 

   -- Sheet 1
   ORA_EXCEL.add_sheet('Sheet1');
   ORA_EXCEL.add_row;
   ORA_EXCEL.set_cell_value('A', 'Id');
   ORA_EXCEL.set_cell_value('B', 'First name');
   ORA_EXCEL.set_cell_value('C', 'Last name');      
   FOR rec IN(SELECT * FROM table1) LOOP
       ORA_EXCEL.add_row;
       ORA_EXCEL.set_cell_value('A', red.id);
       ORA_EXCEL.set_cell_value('B', red.first_name);
       ORA_EXCEL.set_cell_value('C', red.last_name);
   END LOOP;

   --Sheet 2      
   ORA_EXCEL.add_sheet('Sheet2');
   ORA_EXCEL.add_row;
   ORA_EXCEL.set_cell_value('A', 'Id');
   ORA_EXCEL.set_cell_value('B', 'First name');
   ORA_EXCEL.set_cell_value('C', 'Last name');      
   FOR rec IN(SELECT * FROM table2) LOOP
       ORA_EXCEL.add_row;
       ORA_EXCEL.set_cell_value('A', red.id);
       ORA_EXCEL.set_cell_value('B', red.first_name);
       ORA_EXCEL.set_cell_value('C', red.last_name);
   END LOOP;

   -- Sheet 3
   ORA_EXCEL.add_sheet('Sheet3');
   ORA_EXCEL.add_row;
   ORA_EXCEL.set_cell_value('A', 'Id');
   ORA_EXCEL.set_cell_value('B', 'First name');
   ORA_EXCEL.set_cell_value('C', 'Last name');      
   FOR rec IN(SELECT * FROM table3) LOOP
       ORA_EXCEL.add_row;
       ORA_EXCEL.set_cell_value('A', red.id);
       ORA_EXCEL.set_cell_value('B', red.first_name);
       ORA_EXCEL.set_cell_value('C', red.last_name);
   END LOOP;      

   -- EXPORT_DIR is Oracle directory
   ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');
END;

---

I have upgraded package, so now you can export query to sheet on following way:

BEGIN
    ORA_EXCEL.new_document;

    ORA_EXCEL.add_sheet('Employees');
    ORA_EXCEL.query_to_sheet('select * from employees');

    ORA_EXCEL.add_sheet('Departments');
    ORA_EXCEL.query_to_sheet('select * from departments');

    ORA_EXCEL.add_sheet('Locations');
    ORA_EXCEL.query_to_sheet('select * from locations');

    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');
END;