This content has been marked as final. Show 12 replies
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?
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.
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.