Does anyone know how I can fill data from Oracle (with PL/SQL) into an Excel template (with pre-built formulas)? I mean the user provides the Excel template so that I just transfer the data from Oracle into particular cells in the worksheet. The template may be large and can become 50 pages long.
Does anyone have experience filling data into Excel template from Oracle database? My workbook template is big (50 pages and multiple worksheets and pre-built formulas inside the workbook). I need to dynamically change some numbers so that the other numbers will automatically change.
I have used PL/PDF and know that the company has another product called PL/XLS. However, PL/XLS does not so far support Excel template. Does anyone know the solution to this?
It's possible, though not very easy
What do you think about that:
You need Excel 2007+ (2003 should work also)
Edited by: le on Dec 7, 2010 3:38 PM
Edited by: le on Dec 7, 2010 3:42 PM
I do not have solution. And it might I do not understand fully requirement.
I just comment if it bring some ideas
You like fill data from database to some template ?
Why template is not in database ?
Edited by: jarola on Dec 8, 2010 12:39 AM
Your approach is possible.
But it is very limited in functionality
It's not easy to for instance add or remove rows etc..
But what I've done is a little bit more ambitious.
1) take template (it's an XML document) and shred it in elements.
2) Save template in shredded form
3) Assemble new document and substitute data in placeholders.
This approach allows to add or remove rows from the template,
change colors and formulas on the fly etc...
Because template is stored in shredded form performance is also rather good.
I am not good at XML. Have you used a product called PL/XLS (http://plpdf.com/downloads)? Basically, it generates Excel documents by simply calling the packaged PL/SQL API's. I don't understand why it cannot use Excel template. I oftern create acroform by using OpenOffice Writer and then use PL/PDF to generate the output file, which can be called directly from the Web or be saved into BLOB. I just need the similar solution for Excel.
I didn't use this tool, though I used PLPDF ( it looks like it's the same company)
and it's not bad at all .
Blob/placeholders approach is possible but very limited. I'm sure users soon will ask
more something like: add new row(s), hide something, change color (font) etc.
It took me 3-4 weeks to do this project and I also didn't know much about XML.
I used only basic XMLDB functionality + several questions on XMLDB forum
(they are very good there).
I recommend you to go in this direction - it's not very difficult.
The most difficult thing is ever changing Microsoft excel format.
You need to know in advance which version of excel you need to support.
What do you think - will it be of any value
if I restart this project again?
Template based Excel report
I think it will support Excel 2003,2007 2010
Edited by: le on Dec 8, 2010 10:32 AM
I think your idea is good. However, as much as possible, I would prefer to leave the work of designing Excel template to users because they may want to change it very often and the template itself is complicated. It is too much work for me to create a template with a lot of worksheet and cell references. I know some programmers, on Microsoft side, actually create VB programs that run in the background to merge data in a text file into an existing, pre-defined Excel template. In other words, what I want to do is to fill particular cells in the existing template and generate the output file.
So you want an Oracle process or an APEX process to take an existing Excel file and just plug data into it?? What I would suggest instead is, you export your data out and in your Excel file write some VBA code to take you input file and process it accordingly..
You can get more with a kind word and a two-by-four than you can with just a kind word
you example (http://apex.oracle.com/pls/otn/f?p=20622:16) looks very promising.
Could you please explain in detail (ideally step-by-step) how you achieved this!?
I am not that familiar with xml and this sort of export but I have a comparable target.
I try to export a report (pl/sql function body returning sql query) in some kind of template (preformatted form),
change/update the content within excel and then re-import the data via external-table back to the original table
(see question: Re-Import exported excel sheet (report) with updated/changed data
So, I'd like to preformat the excel-sheet just like you did it and additionally restrict the potential additional content that the user might enter
in order to re-import and update the original data with the original table.
thank you very much for your help.