Spreadsheet Document API - An Old Excel Format and the Covid-19 Pandemic

We recently had a private support ticket submitted to us – it included the following in BOLD.

NOTE: We are a medical device manufacturer and helping companies delivering medical products to hospitals affected by COVID-19. Your urgent help is really appreciated.

Use Case

A couple of weeks ago, a customer wrote to us with a problem. They were using our .NET Spreadsheet Document API to transfer data from an XLS file to a database. The XLS file contained data that was exported from a legacy FoxPro application. Needless to say, the app itself could not be upgraded to support newer versions of XLS.

Problem

The FoxPro app in question generated Excel 2 Worksheets binary files (Microsoft Excel 2.0 released in 1987). As you can probably imagine, our Spreadsheet Document API does not support this format as it’s frankly not used by any of our customers (or so we thought).

Our Solution

Once we were made aware of the need – and given the pressures facing medical device manufacturers today – we decided to add support for Excel 2.

An Excel 2 Worksheets (or BIFF2) stores data for a single worksheet as separate binary records. Each record has a header and an optional block of data (which includes numeric values, text, formulas, etc.).

To support this format, we had to:

  1. Detect the BIFF2 format when loading a document. To do this, our Spreadsheet Document API analyzes the first record header of the incoming binary file stream.
  2. Determine the types of records to support. We ultimately chose to import nine types of data records: BOF, EOF, BLANK, INTEGER, NUMBER, LABEL, BOOLERR, FORMULA, and STRING.
  3. Test our solution. We ran tests against the user’s file and actual documents generated by Microsoft Excel to ensure files were imported correctly.

With these three steps complete, we released a hotfix for the customer. The good news was this - our solution addressed their problem.

Current Progress and Limitations

We’ve since enhanced our implementation and the Spreadsheet Document API can now import the following Excel 2 features/settings:

  • Cell values (numeric, string, Boolean, and errors)
  • Formulas
  • Row heights and column widths
  • Cell, row, and column formatting
  • Selection
  • Comments
  • Frozen panes
  • Headers and footers, page margins, and page breaks
  • Protection options
  • Calculation options and the 1904 date system support
  • Defined names (including Print_Area)
  • Files with XOR obfuscation

The following document elements are not supported:

  • External references
  • Some built-in defined names (for instance, Print_Titles)
  • Chart sheets and macro sheets

Please remember, if you and your company are on the frontlines of this horrible pandemic, do let us know. We’ll do what we can to support your efforts.

Thank you to everyone fighting to save lives across the globe.

What’s New in v20.1

To explore the scope, breadth and capabilities of our new products/features, please visit: https://www.devexpress.com/Subscriptions/New-2020-1.xml.
7 comment(s)
Edhy Rijo
Edhy Rijo

Wow, I have been a DevExpress customer for many years now and I do love your products and the quality and knowledge of all your support team and all this time they always go beyond my expectations to make sure my issues has been addressed.

I am very, very proud to be part of the DevExpress family and looking forward to keep working together to deliver the best solutions for our customers.

28 April 2020
Hans Nieuwenhuis
Hans Nieuwenhuis
Well done! Keep up the good work!
28 April 2020
Jose Isga
Jose Isga

Hi, You are really amazing!!!

Great products!!!

28 April 2020
Customer69806
Mikael 69806

Good job!

28 April 2020
Isa_Tahiri
Isa_Tahiri
Good job ! 
28 April 2020
Anders Wang
Anders Wang
NPOI is enough. why spend time on this oldest version?
28 April 2020
Tom Mcd
Tom Mcd
Great Job!
4 May 2020

Please login or register to post comments.