Home » SQL & PL/SQL » SQL & PL/SQL » Generate New Excel format (XLSX) files using Oracle PLSQL (Oracle 11.2.0.4)
Generate New Excel format (XLSX) files using Oracle PLSQL [message #670578] Fri, 13 July 2018 15:09 Go to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
I would like your help to design a Oracle PLSQL code to export multiple tables from an Oracle DB schema to a pre-mapped Oracle directory EXPORT_FILES as one XLSX file.

- Program should generate one Excel workbook (Excel 2007+) in new format (.xlsx), as it can hold more number of rows, and is of significantly less size than older Excel format (XLS)
- Each table data will be in a separate worksheet
- Worksheet name should be the table name
- Do some regular formatting for each worksheet. Example - freeze header row, bold font header row, gray color header row, font Calibri size 9 etc.
- Have Header and Footer in all sheets for business purposes
- Good-to-Have feature: generate XLSX files, and ZIP it as well, all using PLSQL code. So that the final output is a ZIP file in the Oracle directory, thus saving a lot of space.

I saw a script here to generate Excel files and it's working, but the XLS files are much much larger since my table data is huge.
https://community.oracle.com/thread/2544010?start=0&tstart=0
I exported the same data using Oracle SQL Developer tool as XLSX files, and the size is much smaller. XLSX would be a much better option.

Thanks in advance Smile
Re: Generate New Excel format (XLSX) files using Oracle PLSQL [message #670579 is a reply to message #670578] Fri, 13 July 2018 15:51 Go to previous messageGo to next message
BlackSwan
Messages: 26061
Registered: January 2009
Location: SoCal
Senior Member
When your only tool is a hammer, then every problem is treated like a nail!

Alternatively write it it VB, that is executed from the Windows domain; since Excel can issue SQL directly against Oracle DB.
Or use a totally neutral tool like PERL which can interoperate against both Oracle DB & Excel.
Or just have Oracle produce XML file which can be ingested by Excel.

Best Wishes!
Re: Generate New Excel format (XLSX) files using Oracle PLSQL [message #670584 is a reply to message #670578] Sat, 14 July 2018 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65562
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

https://community.oracle.com/message/9362007#9362007

Re: Generate New Excel format (XLSX) files using Oracle PLSQL [message #670602 is a reply to message #670584] Mon, 16 July 2018 06:52 Go to previous message
Bill B
Messages: 1752
Registered: December 2004
Senior Member
The following is a commercial package to do exactly what you want and at only 19 pounds for a single database license is very inexpensive

https://www.oraexcel.com/
Previous Topic: ORA-04091: table Table_Name is mutating, trigger/function may not see it
Next Topic: Possibility of Name
Goto Forum:
  


Current Time: Wed Jul 18 00:18:50 CDT 2018