Add a data worksheet to precreated excel template

[jgebal]

Hi Anton First of all, thanks for creating this tool. I think that it's ease of use is really making it perfect. I would like to ask you about the use case I have.

We use Excel to present data in a nice visual form. The data lives in worksheets: Sheet1, Sheet2, Sheet3 and is a ump of data from specific SQL queries. The sheets with raw data are hidden and another sheet is used to visualize the data.

What I was aiming at is to:

• Prepare the Excel file without data
• Save the file into DB as BLOB
• use excel generator to add/populate Sheet1,2,3.. with data
• save the generated file into filesystem on sever or client (via sqlcl)

The problem that I am facing at the moment is that I can't really find a PL/SQL Excel generator that would allow me to do this. Our tool that we are using is reaching end of life and I need to find a decent & simple replacement for it.

Would it be difficult to extend the package you have now to work with an existing Excel Workbook instead of populating a blank one each time?

Thanks

[antonscheffer]

Adding a new worksheet or filling an empty sheet would probably be not that difficult.

[jgebal]

Great! Would you be able to do it or provide me with some guidance on where should I start if I would like to do it myself?

[antonscheffer]

Sorry, I don't have time to do it. The easiest way to do it your self will probably be:

• start with a Excel with the sheets you want to populate existing.
• use the existing functionality to read your template, meaning read all files in the xlsx into a array of blobs
• initalize workbook.str_ind with the sharedStrings.xml blob
• use the existing functionality to create the data for a sheet.
• create a xml blob for the sheet using the existing functionality
• replace the blob for the sheetX.xml with the just created xml blob
• zip all the blobs again in a new xlsx file.

U

[jgebal]

Thank you. I will give it a try in spare time and if it works, I'll share the outcomes.