Create Excel Workbook by PL/SQL
I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.
Further to my previous post..
I have uploaded the actual package code here
Compile the code on 9i or above and it should compile w/o error.
Package Name : gen_xl_xml
Version : 0.62
You can download samples for demo of how to create a simple and multi-sheet workbook from here
Here I explain how to use this package...
1. First step is to open the file. You do this by using the procedure
If you not using the package in Oracle Applications then use
create_excel - You need to pass the directory object and the file name to create.
If you are under Oracle Applications then use
create_excel_apps - This does not have any parameter as it writes to the out file.
You can not call both procedures at a time.
2. Then you can create styles , worksheet by using the respective procedures.
3. To Write the cells you call write_cell_char and write_Cell_num procedures
4. If you just want to apply style but no contents then use write_cell_null
5. Use set_row_height and set_column_width to set the height and width of row and column respectively.
6. At then end you call close file method. At this point the package will actually write to the file.
Debugging :
There is a package level variable debug_flag which is false by default.
If your excel file is not opening or there are any errors you may set it on and try running from sqlplus to see all the steps that the package executes like creating worksheet , setting row height etc.
Known Limitations.
1. As of now Rows have to be inserted in ascending order.
2. Not all attributes in style are available now. For Example : Borders.
3. Currently formulas are not supported. You may try passing formula strings , I have not checked this but excel's XML structure does not write the formulas that way.
4. Date format is not supported as of now.
5. Column height changes has to be inserted in the ascending order. For example you can not call height change for column c after column e.
Note : This code is not using any version specific features but if you are using 8i I think directory object is not there rather you have to use path set by utl_dir.
I request you to use this code and pass on to whoever requires it. If you get any errors please write to me and I will try to sort them out. I plan to fix the above limitations as I go ahead.
Regards
Sanjeev
I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.
Further to my previous post..
I have uploaded the actual package code here
Compile the code on 9i or above and it should compile w/o error.
Package Name : gen_xl_xml
Version : 0.62
You can download samples for demo of how to create a simple and multi-sheet workbook from here
Here I explain how to use this package...
1. First step is to open the file. You do this by using the procedure
If you not using the package in Oracle Applications then use
create_excel - You need to pass the directory object and the file name to create.
If you are under Oracle Applications then use
create_excel_apps - This does not have any parameter as it writes to the out file.
You can not call both procedures at a time.
2. Then you can create styles , worksheet by using the respective procedures.
3. To Write the cells you call write_cell_char and write_Cell_num procedures
4. If you just want to apply style but no contents then use write_cell_null
5. Use set_row_height and set_column_width to set the height and width of row and column respectively.
6. At then end you call close file method. At this point the package will actually write to the file.
Debugging :
There is a package level variable debug_flag which is false by default.
If your excel file is not opening or there are any errors you may set it on and try running from sqlplus to see all the steps that the package executes like creating worksheet , setting row height etc.
Known Limitations.
1. As of now Rows have to be inserted in ascending order.
2. Not all attributes in style are available now. For Example : Borders.
3. Currently formulas are not supported. You may try passing formula strings , I have not checked this but excel's XML structure does not write the formulas that way.
4. Date format is not supported as of now.
5. Column height changes has to be inserted in the ascending order. For example you can not call height change for column c after column e.
Note : This code is not using any version specific features but if you are using 8i I think directory object is not there rather you have to use path set by utl_dir.
I request you to use this code and pass on to whoever requires it. If you get any errors please write to me and I will try to sort them out. I plan to fix the above limitations as I go ahead.
Regards
Sanjeev
