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?
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 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.