Sunday, February 20, 2011

Working with Office 2007 (OOXML) files using PL/SQL

Starting with Office 2007, Microsoft switched to an XML-based format called Office Open XML (OOXML).

There has been some debate as to how "open" this format really is, given that the specs run to around 7,000 pages (!).

Be that as it may, it's a fact of life that a lot of people use Microsoft's Office suite, and that means we have to deal with this new format in a lot of situations.

The OOXML format is, as it turns out, not so difficult to deal with. The main concept is that an Office document, whether it is a Word document (.docx), Excel spreadsheet (.xlsx) or Powerpoint presentation (.pptx), is actually a compressed (.zip) file that contains a number of XML documents (as well as any image files the user has included in the document).

So to work with OOXML files, we need to be able to zip and unzip files, and to parse and generate XML. Oracle (and PL/SQL) has had good support for XML for a number of years, but (even though there is a UTL_COMPRESS package in the database) there is no built-in zip/unzip support. Of course you could load some Java classes into the database to do it, but dealing with the Java stuff is always a bit of a hassle. But some time ago the good gentleman Anton Scheffer published a PL/SQL implementation based on UTL_COMPRESS that supports zipping and unzipping.

Based on this I have written a package for working with OOXML documents. It's called OOXML_UTIL_PKG and you can download it as part of (you guessed it) the Alexandria utility library for PL/SQL.

Let's see what this package allows us to do.

Get document properties from a Word (docx) file

First we fire up Word and create a test document:

By the way, you can read and write the new OOXML formats using an older version of Office (as I do in the screenshot above), by downloading the Microsoft Office Compatibility Pack from Microsoft.

After saving the document, we can then extract the document properties using the GET_DOCX_PROPERTIES function, which returns a custom record type called T_DOCX_PROPERTIES.

Extract plain text from a Word (docx) file

Using our test document again, we can extract the plain text of the document using the GET_DOCX_PLAINTEXT function, which returns a CLOB.

This is of course very useful if you want to search and/or index (just) the text of a document, or otherwise work with the content.

(This has to be one of the lamest spreadsheets of all time, but it will do fine as an example. It has some text, some numbers, and a formula.)

Similar to the Word document, we can now use the GET_XLSX_PROPERTIES function, which returns a custom record type called T_XLSX_PROPERTIES.

You'll notice that Word documents and Excel spreadsheets have slightly different properties.

Extract a cell value from an Excel (xlsx) file

The GET_XLSX_CELL_VALUE function allows us to retrieve a single value from a named cell in a specific worksheet, like this:

Technical Detail: In the XLSX format, strings (as opposed to numbers) are not stored in the actual cell where they are entered, but rather in a "shared strings" section. The cell just contains a reference back to this "shared strings" section. The GET_XLSX_CELL_VALUE function handles this for you, so you don't have to worry about that.

Extract multiple cell values from an Excel (xlsx) file

Since the function that extracts a single value from a spreadsheet must open the file, unzip it, and parse the XML content every time you call that function, there is another function (GET_XLSX_CELL_VALUES, notice the plural) that allows you to retrieve multiple values in one call. In other words, the file is unzipped and the contents parsed as XML just once, which is obviously more efficient.

Simply specify the names of multiple cells using an array of strings:

Write contents into OOXML file using PL/SQL

Since the contents of OOXML files are XML files, you can manipulate the existing content, or generate new content, and then save it back to the zip file that contains your document.

The following demonstrates one approach; it uses a Powerpoint file, but this technique will also work with Word and Excel files.

We create a Powerpoint 2007 file (.pptx) and put in some tags that we want to replace via code. In other words, this becomes a template that we can fill with dynamic values from the database.

The GET_FILE_FROM_TEMPLATE function takes a template file as input, and two string arrays: The tag names and actual values to replace the tags with. It unzips the file, performs the substitutions, writes back the file to the zip archive, and returns the file, which you can then save back to disk (or, more likely, store in the database or send to a web browser).

The code is trivial:

Here is the result when opening the output file:

So the next time you do a presentation, you could actually update your Powerpoint slides with the latest sales figures (or whatever) from within SQL*Plus...

Conclusion

Working with Office 2007 (OOXML) files from PL/SQL is easy and opens up many possibilities, both for extracting information from documents and storing them in the database, as well as generating or modifying OOXML files in the database server.

You have a great idea.Before that i did not found functionality to access Microsoft Documents from PLSQL. So it's a magic for me also.I have not OOXML_UTIL_PKG package in my database.Can I have a script of package to run in my database.

his is excellent. I have managed to take a word doc as a template, substitute text values and save the result.But I'm struggling with embedding line feeds into the substitution strings, for a letter address for example where the whole address is one item (to avoid blank lines)

Tried the chr(10)||chr(13) thing but just puts everything on one line.Hope someone can help.

I have almost same problem like one of the guys above that has a problem if he tries to replace text in a docx with something containing the GBP £ character. I have the same problem when I use cyrillic characters.The resulting file will not open in Word saying that the £ is an illegal XML character.

I had exactly the same issue and tryed to print special characters by using dbms_xmlgen.convert. It again failed and word is giving illegal XML character error.

So I fixed it by creating following function:-- function converts string to hex what can be used-- in ooxml document-- created by: David Michel-- created on: 19.02.2013create or replacefunction func_string_to_xmlhex( p_string IN NVARCHAR2) RETURN CLOBIS v_hex_string CLOB; v_return CLOB;BEGIN FOR i in 1..length(p_string) LOOP SELECT case length(RAWTONHEX(substr(p_string, i, 1))) when 2 then '&#x00'||RAWTONHEX(substr(p_string, i, 1))||';' when 3 then '&#x0'||RAWTONHEX(substr(p_string, i, 1))||';' else '&#x'||RAWTONHEX(substr(p_string, i, 1))||';' end INTO v_return FROM dual;

About Me

I have been working as a consultant and software developer for 18 years (since 1997), with a special passion for relational databases. I have done a fair share of work using Microsoft tools (including SQL Server, VB, ASP, .NET and C#) and other tools (most notably Delphi), but my favorite tool is the Oracle database with PL/SQL and Apex.