Forum Stats

  • 3,837,767 Users
  • 2,262,296 Discussions
  • 7,900,386 Comments

Discussions

What is the best way to generate a .xlsm file?

LSDev
LSDev Member Posts: 80
edited Jun 22, 2015 7:48AM in SQL & PL/SQL

I have a requirement to generate a macro enabled .xlsm file from Oracle and was wondering the best way to achieve this.

The file is multi tabbed and includes various buttons and macros however all that I need to generate is the table data from the database.

I would imaging that the best way would be to store a blank version of the file with all the macros etc in place and then open it up and add in the table data using PL/SQL.  Am I on the right lines here?

I have had a quick look and there seem to be a number of ways to do this including creating a jsp, using the COM integration or just writing the whole thing in PL/SQL.  I am leaning towards the PL/SQL generation using UTL_FILE as I'm more proficient in that area but wanted to see if anyone had any comments/experience in this area.  If opening up the file and inserting table data is the way forward then it might not be so complicated but I'm sure there will be lots of issues as there usually is with something like this!

Thanks.

Tagged:
dd7470fb-8a19-472a-aaf7-6c596c8c969b

Answers

  • Marwim
    Marwim Member Posts: 3,650 Gold Trophy
    edited Jun 22, 2015 6:30AM

    Hello,

    wouldn't it be easier to create the template manually and then fetch the data by an Excel macro?

    Regards

    Marcus

  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Jun 22, 2015 6:39AM

    As you already have figured out there are many different ways for that issue to solve.

    In your particular case, without knowing any details of your overall software architecture it might be an idea to create the xml data separatly.

    As you might know you can view the office 2007 format as a zip file.

    If you take a closer look at it you will get aware of a worksheet-folder that contains the worksheet-xmls.

    Aside from the fact i never have done this by myself (due to other requirements i mostly used COM), i would spent some few thoughts on creating the xml file myself and add it to the zip file finally.

    But to stress it again, to find the accurate solution depends heavily on your software architecture.

    LSDev
  • LSDev
    LSDev Member Posts: 80
    edited Jun 22, 2015 6:43AM

    The file is going to be generated by the user clicking a button in an application, so it needs to be generated by Oracle and served up to the browser as a file download.  The downloading of the file is fine it's just modifying the data that will be a challenge.

  • LSDev
    LSDev Member Posts: 80
    edited Jun 22, 2015 6:57AM

    Thanks for the reply.

    Manipulating the XML could be a possible solution - ideally I would want to keep everything within Oracle/PL/SQL so that could maybe be achieved this way.

    I don't suppose you know whether modifying one of the worksheet XML files and repacking as an xlsm file (and having it still work) is easy enough to do?  I'm going to do some investigation regarding this now but thought I would ask!  Reading and writing the XML file is no problem it's just the rebuilding of the excel file I have no experience with.

  • Marwim
    Marwim Member Posts: 3,650 Gold Trophy
    edited Jun 22, 2015 7:14AM

    Anton Scheffer has an example how an Excel 2007 file can be generated from PL/SQL

    http://technology.amis.nl/blog/10995/create-an-excel-file-with-plsql

    LSDev
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Jun 22, 2015 7:16AM
    LSDev wrote:
    it's just the rebuilding of the excel file I have no experience with.
    

    Me neither. I just wanted to hint you to that idea. I know some smart people here did some stuff like this already, including marwim, so you might get some more helpful answers yet.

    In the meantime you may search on the web for some clues, e.g.:

    sql outputs to excel sheets

    LSDev
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Jun 22, 2015 7:24AM

    I designed a PL/SQL API that generates xlsx/xlsm documents from the db.

    Basically it works like this :

    - fixed parts (styles, vba modules etc.) are stored as they come in the db

    - moving parts (workbook, worksheets, tables, sharedstrings etc.) are generated using XSLT

    - everything is then packed into a zip archive, generating all the relationships (.rels parts) in the process

    I don't suppose you know whether modifying one of the worksheet XML files and repacking as an xlsm file (and having it still work) is easy enough to do?
    

    If the data is the only moving part then yes, it's as easy as replacing one sheet file by another in the archive.

    dd7470fb-8a19-472a-aaf7-6c596c8c969bLSDev
  • LSDev
    LSDev Member Posts: 80
    edited Jun 22, 2015 7:29AM

    Good to hear you have had success with it, I think this is the direction I will take.

    I literally only need to change the data so based on what you said it should be a case of having the same static files for every file I generate and just manually build up the worksheet I need to change before repacking into a zip.

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Jun 22, 2015 7:48AM

    From a more general point of view, one drawback of this approach is that a modification of the template may actually impact multiple parts in the archive, which makes maintenance a little harder.

    For example, if you decide to add some new styling to the workbook, say bold table headers, the modification will end up in the style info part (usually stored in "xl/styles.xml").

    Due to the peculiar way the style info is organized and referenced in a worksheet (through 0-based indices), the whole XSLT stylesheet (in my case) may need an upgrade to target the new style references.

    LSDev
This discussion has been closed.