CODEWITHSUNDEEP
oracle-databaseexceloracle11g
Vivek
Vivek

Reputation: 4652

Extract data from oracle to multiple sheets in excel

Is there any way we can extract data from oracle tables to multiple sheets in an excel?

For example: I have two tables checkpoints and hold. I want data from checkpoints to go to sheet1 of MS excel and data from hold to go to sheet2.

Is this possible to implement in oracle?

Oracle version: Oracle 11G

Upvotes: 0

Views: 14152

Answers (3)

ora_excel
ora_excel

Reputation: 112

There is PL/SQL package that is able to create Excel document with multiple sheets and put data from SQL query to separate sheets.

Please see example below:

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', FALSE);  

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

    -- EXPORT_DIR is an Oracle directory with at least  
    -- write permission  
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');  
END;

More details you can find here: http://www.oraexcel.com/examples/pl-sql-excel-query-to-sheet-export

Upvotes: -2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

If the spreadsheet you want to generate is very simple (i.e. just cells with data, and multiple sheets) you could try the Excel generation API in the Alexandria PL/SQL Library - just grab XLSX_BUILDER_PKG and its dependencies (I don't think there are many) and it should do the job.

I've used it myself to generate simple spreadsheets in XLSX format, and it's all in PL/SQL. It returns the results in a BLOB, and there's another routine in the library for spitting that out with UTL_FILE, if you need the result on the database server.

Upvotes: 4

Alen Oblak
Alen Oblak

Reputation: 3325

Of course it is possible, but it depends how do you want that to be done.

1) If you have a reporting tool (Jasper Reports, Oracle BI) you can use that tool to create a report and export the data.

2) If you have a developer tool (Toad, SQL Developer) you can export the two tables "by hand".

3) If you know how to write code, than you can use a API to create the Excel file and populate it with the data. You can use PL/SQL, PHP, C, C++ or almost any other language, it just needs a Oracle API and an Excel API.

Upvotes: 0

Related Questions