An example is given below to export data into Excel from Oracle table using PL SQL. You can do this task with the help of xlsx_builder_pkg package, which you can download from GITHUB using the following link click here. After downloading the package extract the zip file and locate the package at this path \alexandria-plsql-utils-master\ora\. You will find the two files xlsx_builder_pkg.pks and xlsx_builder_pkg.pkb.
There are many other utility scripts also in the downloaded zip file, which you can use for your development. I will give more examples of those scripts in my other posts.
Install these two scripts into your database schema and create a directory object for Excel xlsx files (if you don't have already) as shown in below example:
Create Oracle Directory Object
Create OR Replace Directory excel_files as 'c:\excel_files';
Your database directory is now created and now you can create Excel file from Oracle table using PL SQL. Below is the example:
Export Data into Excel from Oracle Table using PL SQL
BEGIN
xlsx_builder_Pkg.clear_workbook;
xlsx_builder_pkg.new_sheet ('emp');
xlsx_builder_pkg.query2sheet (p_sql => 'select * from emp', p_sheet => 1);
xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
END;Above example will export all the data from emp table to Excel file with one sheet named emp. Note, procedure query2sheet having two parameters one p_sql for select statement and the second p_sheet is the sheet number. As in the example, we are creating just one sheet with name emp, so we will pass 1 for p_sheet parameter. You can create multiple sheets with data. See the below example:
BEGIN
xlsx_builder_Pkg.clear_workbook;
xlsx_builder_pkg.new_sheet ('emp');
xlsx_builder_pkg.query2sheet (p_sql => 'select * from emp', p_sheet => 1);
xlsx_builder_pkg.new_sheet ('dept');
xlsx_builder_pkg.query2sheet (p_sql => 'select deptno, dname from dept where deptno = 20',
p_sheet => 2);
xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
END;
This will export the data from emp table into emp sheet and from dept table into dept sheet in one Excel file named emp.xlsx. You can study this package more and can perform more tasks. I will also give more examples from it.
Hi,
xlsx_builder_capi.cell (1, 6, 'Name', p_fontId => xlsx_builder_capi.get_font ('calibri', 2, 10, p_bold => true, p_rgb => 'Green'));
It is maybe because “Green” is not the RGB format.
Try to specify color in RGB format, for example, ‘123, 111, 123’, or in any valid format. You can Google it to find right code for color green.
in the Package we have:
p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
Yes I saw some examples on GitHub and found that they are specifying using Hex8 format. For example,
Hi Vinish,
it work, thank you, i like your job,
After creating the excel file in a Oracle Directory on the server, how can I download that file through Oracle APEX?
You can check the following posts to do this job:
Thank you, I'll give that a try.
Is there a way to accomplish this without an Oracle Directory?
To write a file on server, you need Oracle directory.
And if you are storing the BLOB data into a table then you can get in Oracle Apex, there is no need of Oracle Directory for it.
Great, that's exactly what I need, how would I use xlsx_builder_pkg to create the excel file as BLOB data into a table?
Thank you again for your help.
Declare a variable for blob, for example:
After the following line:
xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');Add the below line:
Now insert this b_blob variable into a table having blob column, for example:
That easy? You're awesome!
Thank you again!
I'm getting this error: ora_sqlerrm: ORA-29280: invalid directory path ORA-06512.
I guess is this line that causing the error, since I didn't create an Oracle Directory.
xlsx_builder_pkg.save ('EXCEL_FILES', 'emp.xlsx');
Is there a way to get this working without an Oracle Directory?
You can remove this line, then it will not save the file on the server.
Use only xlsx_builder_pkg.finish to get the file in blob.
That works, thank you!
Thanks for sharing pkg. Any idea how to start generate excel from row 15. and add some text before the table data display.
Hi,
Processing consumes a lot of memory (UGA), I want
Write the sheet content to a file each time you have completed the processing of 200 rows (bulk select). Write in append mode. therefore synchronize with reading data.
Is it possible?
I didn't try this yet. If you have some ideas, you can try it and let me know if it works.
Hi Vinish
I tried the above package and procedure . The stored procedure is looping
Can you please help on this
Hello Vinish, I will use this package and attach .xls file through email, but the file is corrupted in attachment. how this issue is resolve, guide me?
After generating the Xls file, if you can open it but get corrupt in the attachment, you should check your email code.
I will use this process apex_mail.Add_Attachment. Tell me, how i will send in email this generated file. Any idea?
Hello,
Hello Vinish, i am using this proccedure, the xlsx file is formed, but can't be opened, is there any solution
Hi Vinish,
How to insert image in xlsx file.
Error report -
ORA-06550: line 3, column 2:
PLS-00221: 'NEW_SHEET' is not a procedure or is undefined
ORA-06550: line 3, column 2:
PL/SQL: Statement ignored
ORA-06550: line 4, column 1:
PLS-00221: 'QUERY2SHEET' is not a procedure or is undefined
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
ORA-06550: line 5, column 18:
PLS-00302: component 'SAVE' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
GETTING THIS ERROR PLEASE PROVIDE EXECUTION