Datawatch builds reports using SpreadsheetML


Datawatch just announced the availability of Monarch V.9.0 which has the ability to output rich spreadsheet reports on data leveraging SpreadsheetML: http://www.datawatch.com/datawatch/news.asp?display=detail&id=128


This is pretty cool because they take advantage of the 300 pages of spreadsheet function documentation in the OpenXML standard to help create super rich reports. They don’t just output the raw data, but also provide rollups leveraging spreadsheet functions.


Here’s a list of some of the benefits they get with SpreadsheetML as identified on their website:



  • Enhanced Exporting to Excel – The Monarch V9 new Excel exporting engine runs up to 5X faster than previous versions.

  • Summary Export to Excel – Formulas, outlining and conditional formatting have been implemented for summary export to Excel.

  • Open XML Spreadsheet Support – Monarch V9 can export Excel 2007 OpenXML spreadsheets (xlsx), allowing users to take full advantage of the new functionality and dramatically increased spreadsheet capacity of Excel 2007.

  • Summary Sub-totals and Grand Totals Export to Excel as Formulas – Monarch V9 now exports the values and formulas, allowing users to make revisions to Excel files without worrying about sub-total/grand total errors.

  • Excel Autofilter – Users have the option to automatically invoke the Excel AutoFilter when they open an exported file in Excel.

I’m expecting we’ll see more and more data providers leveraging SpreadsheetML to provide much richer reporting than was possible before (and with much less overhead). Rather than doing the calculations themselves, they can just specify the function and the consuming spreadsheet application will perform the calculation. This is also true for things like charts and pivot tables. We’ve definitely come a long way from the basic CSV file J


-Brian

Comments (5)

  1. Peter Wilkins says:

    "dramatically increased spreadsheet capacity"?  How does one increase spreadsheet capacity?

    Come on, if you’re blogging, you could at least not copy out press releases verbatim but translate them from marketing-speak to English.

  2. Excel 2007 significantly increased the limits of what you can do in a spreadsheet.

    Here’s the list (taken from Dave Gainer’s blog http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx):

    • The total number of available columns in Excel
    • Old Limit: 256  (2^8)
    • New Limit: 16k  (2^14)
  3. The total number of available rows in Excel
    • Old Limit: 64k  (2^16)
    • New Limit: 1M  (2^20)
  4. Total amount of PC memory that Excel can use
    • Old Limit: 1GB
    • New Limit: Maximum allowed by Windows
  5. Number of unique colours allowed a single workbook
    • Old Limit: 56 (indexed colour)
    • New Limit: 4.3 billion (32-bit colour)
  6. Number of conditional format conditions on a cell
    • Old Limit: 3 conditions
    • New Limit: Limited by available memory
  7. Number of levels of sorting on a range or table
    • Old Limit: 3
    • New Limit: 64
  8. Number of items shown in the Auto-Filter dropdown
    • Old Limit: 1,000
    • New Limit: 10,000
  9. The total number of characters that can display in a cell
    • Old Limit: 1k (when the text is formatted)
    • New Limit: 32k or as many as will fit in the cell (regardless of formatting)
  10. The number of characters per cell that Excel can print
    • Old Limit: 1k
    • New Limit: 32k
  11. The total number of unique cell styles in a workbook (combinations of all cell formatting)
    • Old Limit: 4000
    • New Limit: 64k
  12. The maximum length of formulas (in characters)
    • Old Limit: 1k characters
    • New Limit: 8k characters
  13. The number of levels of nesting that Excel allows in formulas
    • Old Limit: 7
    • New Limit: 64
  14. Maximum number of arguments to a function
    • Old Limit: 30
    • New Limit: 255
  15. Maximum number of items found by “Find All”
    • Old Limit: ~64k (65472)
    • New Limit: ~2 Billion
  16. Number of rows allowed in a Pivot Table
    • Old Limit: 64k
    • New Limit: 1M
  17. Number of columns allowed in a Pivot Table
    • Old Limit: 255
    • New Limit: 16k
  18. Maximum number of unique items within a single Pivot Field
    • Old Limit: 32k
    • New Limit: 1M
  19. Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table
    • Old Limit: 255 characters
    • New Limit: 32k
  20. The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations
    • Old Limit: 255
    • New Limit: 32k
  21. The number of fields (as seen in the field list) that a single PivotTable can have
    • Old Limit: 255
    • New Limit: 16k
  22. The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
    • Old Limit: 8k
    • New Limit: Limited by available memory
  23. The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
    • Old Limit: 64k
    • New Limit: Limited by available memory
  24. The number of array formulas in a worksheet that can refer to another (given) worksheet
    • Old Limit: 65k
    • New Limit: Limited by available memory
  25. The number of categories that custom functions can be bucketed into
    • Old Limit: 32
    • New Limit: 255
  26. The number of characters that may be updated in a non-resident external workbook reference
    • Old Limit: 255
    • New Limit: 32k
  27. Number of rows of a column or columns that can be referred to in an array formula
    • Old Limit: 65,335
    • New Limit: Limitation removed (full-column references allowed)
  28. The number of characters that can be stored and displayed in a cell formatted as Text
    • Old Limit: 255
    • New Limit: 32k
  • Doug Mahugh says:

    One Open XML implementation that I find interesting for a variety of reasons is the new release of Monarch

  • Stephane Rodriguez says:

    There is a reason why MS Reporting Services does not export Excel functions in their "Excel export" feature. The reason is, the report engine does all sorts of calculations (filters, sorts and other aggregations), and there is a disconnect between what it does and what would be expressed in terms of Excel functions within Excel’s context (i.e. Excel grid and an associated snapshot of data). Should the user do some more calculations, drill down, etc., the results would be totally out of whack.

    Therefore, you’d be hard pressed to explain the benefits of creating that disconnect in the first place using DataWatch’s product.

    In addition to this, I think Microsoft is perfectly happy when whatever files are sucked up into Excel so that Excel is the only application that actually calculates and renders said data.

    There is no contradiction here with the anticompetitive behavior.

    I can see that Microsoft either speaks like there is no third party out there (for instance, when it comes to generating Excel files, all they say is that Excel’s COM automation on the server is not the best solution, yet forget to remind their audience that a ton of third parties have solved this problem for more than a decade). Or, when for some amazing reason they mention one, it’s for their own advantage.

    Amazing.

  • hAl says:

    It seems Ecma has responded to the objections made by the national bodies in ISO.

    http://ooxmlhoaxes.blogspot.com/2007/03/ecma-responded-to-ooxml-contradictions.html