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.

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.

Free DevExpress Products - Get Your Copy Today