Generating Excel Charts with MarkLogic

GIVE MY CREATION LIFE!and some fun with Formulas too…

This is another interesting one I see regularly: “How can I generate Excel Charts from MarkLogic Server?”

Charts are actually rendered from DrawingML found in the .xlsx package.  The DrawingML is embedded in SpreadsheetML, which is the Open XML format for Excel 2007/2010.

You don’t want to mess with DrawingML, as it’s a nasty frickin riddle, wrapped in an engima, inside a russian doll style matrix of insanity and pain.

Word, Excel and PowerPoint are producers and consumers of XML.  To some extent and to varying degrees, each of their respective XML formats can be understood and worked with in a relatively straightforward and reasonable way.  Sometimes though, the XML generated by these applications is really just a serialization of their object model and you’ll just waste a ton of time and find yourself in an extremely uncomfortable place (ed-like the back of a Volkswagen?) trying to figure the XML out when you don’t have to.  So let’s leave the DrawingML be. Capisci?

Think about it this way:  A chart in a workbook is tied to certain cell values in a worksheet.  When the cell values update, the chart dynamically updates.  At the end of the day, the DrawingML is just a snapshot of what the chart looked like based on the cell values when the Workbook was saved in Excel. (ed-Pivot tables work similarly in this way, but that’s a post for another day.)

Now let’s say we have a workbook containing a chart.  We know we can save our .xlsx to MarkLogic Server and have it automatically unzipped for us, its component XML parts made immediately available for search and re-use.   We can then update our extracted worksheets in the Server using XQuery.  Finally, we can re-zip the extracted workbook components back up and open the updated .xlsx into Excel.  Excel will automatically refresh its chart for us when it consumes the XML so we see the latest visualization of our chart based on the information we added to the worksheets.

5 Steps to Chart Freedom

Step 1

Create your chart in a workbook and drive it off of some cell values.  Note the cells and the name of the worksheet you’re driving your chart from. (example: Sheet1, cells: B2, B3, B4, etc.) I’ve provided a sample .xlsx here.

On Sheet1 we see download counts for a fictional company’s widgets for the month of September. The chart shows downloads for the widgets Foo, Bar, and Wumpus.  The chart columns correspond to cells B2, B3, and B4.

On Sheet2 we see sales counts for a fictional company’s widgets for each salesperson. The chart shows total sales for each salesperson.  The chart sections correspond to cells E2, E3, and E4.  Look closer and you’ll see that the cell values in column E driving the chart are actually the result of formulas; they are SUMs of all widgets for each salesperson row.  Note that the cells B6, C6, D6, and E6 all contain SUM formulas for their respective columns as well.

Step 2

Enable the Office OpenXML Extract and Status Change Handling CPF pipelines for your MarkLogic database so the .xlsx will automatically be unzipped when ingested into MarkLogic and its component parts made available for update.  Also insure you have the URI Lexicon enabled for your database. An example how to set this up can be found here.

Step 3

Save your .xlsx to MarkLogic. Once saved, the .xlsx is unzipped, and we can now manipulate it’s extracted XML component parts directly.  The idea is to save workbooks containing your charts as templates within MarkLogic and then update the extracted worksheet parts based on new information being saved to your database.

Step 4

Use the XQuery API that comes with the MarkLogic Toolkit for Excel to set the cell values for your chart in the extracted worksheet.  In particular, look at the function excel:set-cells() for updating worksheets.  Evaluate the following in CQ.

Note: you may need to update the code samples below to reflect your workbook and where you saved it in MarkLogic.

xquery version “1.0-ml”;

import module namespace excel=”http://marklogic.com/openxml/excel” at “/MarkLogic/openxml/spreadsheet-ml-support.xqy”;

let $doc1 := “/MySpreadsheet_xlsx_parts/xl/worksheets/sheet1.xml”
let $doc2 := “/MySpreadsheet_xlsx_parts/xl/worksheets/sheet2.xml”
let $sheet1:= fn:doc($doc1)/node()
let $sheet2 := fn:doc($doc2)/node()

let $cell1 := excel:cell(“B2”,120)
let $cell2 := excel:cell(“B3”,99)
let $cell3 := excel:cell(“B4”,456)

let $cell4 := excel:cell(“D3”,127)
let $cell5:= excel:cell(“E3″,(),”SUM(B3:D3)”)

return (xdmp:document-insert($doc1, excel:set-cells($sheet1,($cell1, $cell2, $cell3))),
                xdmp:document-insert($doc2, excel:set-cells($sheet2,($cell4, $cell5))))

In the code above, for Sheet1, we see that we use the excel:cell() constructor to create cells for B2, B3, and B4.  We set the values for these cells to new numbers. These numbers could be coming from the results of another query.  We update the worksheet, using excel:set-cells(), passing the function the sheet we want to update, as well as a sequence of cells we’d like added and/or updated on the referenced sheet.  Finally, we xdmp:document-insert() our updated document, overwriting the existing one with our updated worksheet.  Remember, Sheet1 just held the simple chart driven directly from the cell values.

With Sheet2, we again use excel:cell() to create cells for D3 and E3. Sheet2 is more interesting as the chart here is driven from cells that contain formulas. For E3, we create a cell using excel:cell(), setting the value of the cell to the empty sequence, () , and passing in the formula for the cell.  Again we excel:set-cells() to update our worksheet and xdmp:document-insert() to save our updated worksheet back to the Server.

Note on excel:cell(): This function creates a new cell, so if you wish to retain an existing formula for a cell before you update it in a worksheet, you can’t use the 2 argument excel:cell() function.  If you did that, you’d lose the formula for the cell in the worksheet when you overwrite the XML.  You must create the cell with the formula, as we did above for E3.  If this doesn’t work for you, you can always roll your own XQuery to update the cell values for worksheets containing formulas in a different way.

Note on Excel formulas: Unlike charts, cells containing formulas will not calculate and refresh automatically when you open the updated worksheet in Excel if those cells already contain values. The value of the cell within the XML for the worksheet is considered the cached value by Excel and will be displayed when the workbook is opened.  This is done for performance reasons, so formula heavy worksheets don’t take forever to open as they calculate the value for every cell containing a formula when a workbook is opened.  Formula calculation is postponed to avoid wait time when opening a workbook.  As a result of this though, you can create XML for a worksheet that when consumed by Excel, will result in a cell displaying the wrong results given its formula.

To get a formula to calculate the value for a cell when you open a workbook in Excel and insure the correct cell value is displayed, you need to set the value of the cell to nothing.  You can do this using excel:cell(), setting the value of the cell to the empty sequence: ().

For more information on the excel:* functions,  check out the XQuery API docs that come with the Toolkit for Excel.  There are a lot of functions available, all documented and with examples of usage.

Step 5

Zip up the updated .xlsx from it’s extracted component parts and open into Excel.  When you do this, it doesn’t matter what the DrawingML is.  Excel reads the cell values when it consumes the XML and will update the chart automatically.  The next time you save the workbook, the DrawingML is updated to reflect what the chart looks like based on the latest cell values. Evaluate the following in CQ.

xquery version “1.0-ml”;

let $directory := “/MySpreadsheet_xlsx_parts/”
let $uris := cts:uris(“”,”document”,cts:directory-query($directory,”infinity”))
let $parts := for $i in $uris let $x := fn:doc($i) return  $x

let $manifest := <parts xmlns=”xdmp:zip”>
                         {
                              for $i in $uris
                              let $dir := fn:substring-after($i,$directory)
                              let $part :=  <part>{$dir}</part>
                              return $part
                          }
                         </parts>

let $xlsx := xdmp:zip-create($manifest, $parts)
return xdmp:save(“C:\MyUpdatedSheet.xlsx”,$xlsx)

Open MyUpdatedSheet.xlsx into Excel.

BooYaa!  We update a few cells on Sheet1, and our chart automatically updates for us when we open the .xlsx into Excel.

Now take a look at Sheet2.  We updated D3 and set the value of E3 to (). Subsequently, the formula in E3 calculated its SUM formula when the workbook was opened.  Since the chart is driven from E2, E3, and E4, it updated properly as well.  WooHaa!

But take a closer look at cells D6 and E6.  They each contain SUM formulas for their columns, and they’re displaying the wrong values!  (ed-#fail) This is because we didn’t set their values to nothing.  Since the cells contained values in the XML for the worksheet, the cell formulas were not calculated when the workbook was opened and the cached value was displayed.  If you click on each of those cells, you’ll see the formula, click off of them, and the cells will recalculate and update with the correct values.

Bring Excel Workbooks to Life!

There's always another way.So the title was a bit misleading, as we don’t actually generate charts, so much as create the appropriate XML for Excel worksheets so that the Excel application will update and render charts for us when it consumes the XML.   But understanding a little bit of the SpreadsheetML format and how Excel behaves when consuming XML for charts and formulas, the doors open up to some very interesting possibilities.

The above examples are intentionally simple, but think about this…

Instead of a dead .xlsx, that sits lifeless on the filesystem and only alive when opened and active and being manipulated directly in Excel,  workbooks can now stay alive in the Server, constantly updated from complex queries being evaluated as new information is saved to the database.  These workbooks can then be dynamically zipped up when called upon to open in Excel and provide snapshot visualizations and results for a point in time.  Excel will consume the XML and can update charts and calculate formulas when opening this snapshot workbook, while the underlying, extracted workbook lives on and continues to be updated.

But this is just one way to use MarkLogic and Excel together.  There’s always another way…

Advertisements

MarkLogic Toolkits for Word, Excel, and PowerPoint

I thought I’d use this post to provide a brief introduction to the MarkLogic Toolkits for Office.  So here’s an overview:

What is a Toolkit?

A Toolkit is a set of tools for jumpstarting your development with MarkLogic Server and Microsoft Office 2007 / Office 2010 / Open XML.

There are currently 3 Office Toolkits:

  1. MarkLogic Toolkit for Word
  2. MarkLogic Toolkit for Excel
  3. MarkLogic Toolkit for PowerPoint

We care about Word, Excel, and PowerPoint, because with Office 2007, their respective document formats are now XML.  Take a .docx, .xlsx, or .pptx and change it’s file extension to .zip.  Extract the file and inside you’ll find a bunch of interrelated XML parts.

This update to the document formats provides an interesting opportunity as people can now work with XML without learning new, specialized tools, or even really being aware of the fact that they’re working with XML.  Authors continue to use the tools they know and are familiar with in Office, and we can provide additional functionality to them by taking advantage of the XML.  The Toolkits provide ways for us to enhance the authoring experience within Office as well as on the Server where we can prepare content for Office as well as additional consumers.

Each Toolkit is composed of 3 major components:

  1. Add-in for Word | Excel | PowerPoint
  2. XQuery API
  3. Sample Applications

Add-in with supporting JavaScript API

The Add-in is just a standard Windows application you install using a .msi.  Double-click the .msi to start installation, click next, next, next, through the dialog screens as you would with any Windows app, and the next time you start Office you’ll find a Task Pane on the right hand side of the application (see image below).

NOTE: The Task Pane is just a browser!  It’s using whatever version of IE is installed on the client, and exposing that within Office.

The Addin may just be a browser, but it also installs a supporting library for interacting with the active document (the document being authored).   Access to this libary from the browser is available from the JavaScript API that comes with the Addin.  Developers can quickly create a webapp within Word, Excel, or PowerPoint that communicates with and/or is even served from MarkLogic Server and they can use the JavaScript APIs to get XML in and out of the document being authored.

We wanted to avoid creating a situation where users had to constantly re-install Add-ins on the client.  By making it a browser, we can update functionality by simply changing the application code on the Server.

JSDocs are provided with each Toolkit for the respective JavaScript API.

XQuery API

The XQuery APIs for Word, Excel, and PowerPoint provide functions for developers to manipulate and generate Office documents on the Server.

The goal is to simplify the use of Open XML.  Along with each XQuery API,   a CPF pipeline for MarkLogic is also provided that will automatically update Office documents on the Server as they are ingested to make all the XML more friendly for search and reuse.  These updates are done without using any custom XML and without losing any document fidelity.

NOTE: The Add-in and XQuery API can work in concert or separately.  If your authors use Office, it might make sense to use both. If you’re querying Office documents (or some other XML format) on the Server, and delivering results through a regular browser or some other consumer, you might not need Office on the client at all.  But if you’re delivering an Office document as a result, yes,  you still may require the Office application on the client, but you don’t necessarily require Add-in.  It all depends on your use-cases and particular goals.

XQuery API docs are provided with each TK as well.

Sample Applications

Rather than just give developers an empty browser with a .js file and JavaScript API documentation to start development with, each TK comes with Sample Applications.  A developer can just drop the Sample right into MarkLogic, configure their Addin to reference the URL of the Server, and quickly be up and running with applications within the Task Pane.

These Samples are VERY simple.  They provide just a sliver of the available API functionality.  Again, they’re intended to jumpstart development.  We provide these samples so a developer can quickly see some useful functionality, open the source to see how the code looks, and get in there and start hacking to create the app they actually want.  Developers can reference the API docs and add/change functionality as they require.  When you look at the docs you’ll see that there is a LOT that can be done on the client and in the Server that isn’t demonstrated in the Samples at all.

NOTE: The Sample Application is not the Toolkit.  It’s just one example of the type of application you can build using a TK.

NOTE: The Sample Application is not Office.  We skinned the samples to be the colors of Office.  But it’s just HTML, JavaScript, and CSS.  Remember, the Pane is just a browser serving up pages from MarkLogic.  The goal is to keep authors comfortable in their authoring environment, letting MarkLogic do what it does best (search, reuse, enrich, analyze, etc.) and let Office do what it does best (author,analyze,present).  If you want to use crazy colors and  the blink tag for your app, go for it!

A Toolkit Guide rounds out the documentation with details on creating, configuring, and delivering solutions that use the Toolkits.

Overview

Office is ubiquitous.  The goal of the Toolkits is to keep authors authoring and analysts analyzing  in the tools they are already using and comfortable with.  Office is a publisher and consumer of XML, MarkLogic is an XML Server.  The products compliment each other very nicely and we can create a much richer Office experience for authors without requiring them to learn new, custom tools, or even be aware of the fact that behind the scenes, it’s all XML.

Conclusion

The Toolkits are all free and now available on codeplex.  They are all open source, released under the Apache 2 license.

The response to the TKs has been very positive.  I’ve seen an increase in interest lately, and it’s been great to hear people are using these and finding them very useful.  I was surprised to hear at #MLUC10 how one person has deployed all 3 TKs across his organization and is very excited about the possibilities.  He also told me that multiple authors are enjoying the Sample apps in PowerPoint as-is.  Very cool!

I just have to say, I get excited too!  Each Office application has a different degree of XML friendliness and Word is by far the friendliest.  With the Toolkit for Word we can use Word as a browser into MarkLogic.  At work I send content back and forth between Word and MarkLogic and never have to save a local .docx on the client.  It’s just XML going back and forth.  Office consumes and publishes Open XML.  Using the XQuery API, I can dynamically create the XML Word requires for consumption from alternative XML formats. Also, Word publishes WordprocessingML, but my destination XML format isn’t necessarily always Office docs.  It’s pretty awesome, and that’s just Word!  Similar opportunities exist for Excel and PowerPoint as well.

Spoiler Alert: there’s more awesome coming!

So that’s it. You’re now Toolkit experts.  Go download the Toolkits and have fun creating your own MarkLogic applications for Office.  If you have any questions , comments, or suggestions for the TKs please feel free to drop me a line in the comments. Thanks!

Saving macro code from an Active Excel Workbook as text in a Custom XML Part

Eric B. and RakimSo we can audit and search it later!

Interesting question came my way last week: Is it possible to save macro code as text in an Excel 2007 .xlsm package?

The answer is: Yes!

Why would someone want to do this?  Well, this particular organization wants to save Excel workbooks to MarkLogic Server.  In the Server, the workbooks are automatically unzipped and they can search and run reports on the SpreadsheetML, running analytics on their analytics.

But macros are saved as .bin files within the .xlsm (in vbaProject.bin) and they want the ability to audit the macros being used, as well as search and possibly re-use them.

So wouldn’t it be dreamy if we could create an Add-in for Excel that could capture when an Analyst saves a workbook and serialize the code for any macros in the workbook as text, storing them in Custom XML Parts within the .xlsm package?

Recipe for Awesome

We can prototype this quickly using the MarkLogic Toolkit for Excel.

I’m just going to focus on the macro to text part for this post.  I choose to handwave at events right now as that’s a topic for another time.  There are many you could possibly capture, but let’s just say we (handwave, handwave) capture the WorkbookBeforeSave event.  When that event fires, we’d call some function that uses the code below to get the text for each macro, XML-ize it, and add it to the .xlsm package.  Score-Double-Bonus-Action: the Add-in that comes with the Toolkit already has a function addCustomXMLPart(string myxml), that provides a way for us to add the Custom XML Parts to the .xlsm.

I used Excel to record a simple macro that merges a couple of cells and does some formatting. To tell you the truth, what I know about macros in Office is more meta than practical, so this was an interesting learning exercise for me.  I created my macro using this tutorial, which I found useful.

All that’s left is to create two functions: one to get the count of the macros in the workbook, and another to get each macro and serialize it as text:

//you probably need to add the reference
using VBA = Microsoft.Vbe.Interop;

public int getMacroCount()
{

  VBA.VBProject proj = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject;
  return proj.VBComponents.Count;

}

public string getMacroText(int idx)
{

  VBA.VBProject proj = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject;
  string projName = proj.Name;
  string componentFile = "";
  object o_idx = idx;

  try
  {
    VBA.VBComponent vbComponent = proj.VBComponents.Item(o_idx);

    if (vbComponent != null)
    {
      VBA.CodeModule componentCode = vbComponent.CodeModule;
      componentFile = "";
      if (componentCode.CountOfLines > 0)
      {
        for (int i = 0; i < componentCode.CountOfLines; i++)
        {
          componentFile += componentCode.get_Lines(i + 1, 1) + Environment.NewLine;
        }
      }
    }
  }

  catch (Exception e)
  {
    componentFile = "error: "+e.Message;
  }

  return componentFile;

}
 

So we call the count, and for each one, we get the macro text, if there is any.  If a macro exists, we can just wrap it in some well formed XML and add it to the .xlsm package using addCustomXMLPart(ourmacroinxml).

Now once we’ve saved the .xlsm toMarkLogic, we can query from CQ:

     xquery version "1.0-ml";
     declare namespace xl="http://marklogic.com/xl";
     cts:search(//xl:metadata, cts:word-query("format"))

And view our results:

Notes

MSDN documentation isn’t the greatest to begin with, but I couldn’t find anything useful in the library on Microsoft.Vbe.Interop, VBProject, or VBComponents.

I did find some older blog posts and some suggestions on the forums.  Consensus seems to be that you have to rely on intellisense and just play around with these objects to figure them out.  Of course, after monkeying around with this stuff for a couple of hours I then knew what to look for and found some useful posts on stackoverflow.  But if anyone knows where I can find decent API documentation or any useful information on using  Microsoft.Vbe.Interop, that would be much appreciated.

Finally, the example here is pretty simple and you can easily add more metadata about the .xlsm and/or any embedded macros to the Custom XML Part.   Take a closer look at componentCode (vbComponent.CodeModule) above.   You’ll find properties that return the procedure name, signature, line counts, and more, which could be useful for reporting and diffing.  Enjoy!

public int getMacroCount()

{

int count=0;

try

{

VBA.VBProject proj = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject;

count = proj.VBComponents.Count;

}

catch

{

count = 0;

}

return count;

}

public string getMacroText(int idx)

{

VBA.VBProject proj = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject;

string projName = proj.Name;

string componentFile = “”;

object o_idx = idx;

try

{

VBA.VBComponent vbComponent = proj.VBComponents.Item(o_idx);

if (vbComponent != null)

{

VBA.CodeModule componentCode = vbComponent.CodeModule;

componentFile = “”;

if (componentCode.CountOfLines > 0)

{

for (int i = 0; i < componentCode.CountOfLines; i++)

{

componentFile += componentCode.get_Lines(i + 1, 1) + Environment.NewLine;

}

}

}

}

catch (Exception e)

{

componentFile = “error: “+e.Message;

}

return componentFile;

}