Can't find what you are looking for? Try these pages!

Blog

Managing Excel Files with the DataFlex Library for LibXL

By Randall Rutherford

A common feature request that application developers are often asked to incorporate in their business software is the ability to import or export data to or from Microsoft Excel files. Fortunately, DataFlex makes this easy to do by using the powerful DataFlex Library for LibXL!

With this library developers can easily expand their Windows, web and mobile apps to read and write Excel files without requiring the use of Microsoft Excel and the .NET framework to:

  • Generate a new spreadsheet from scratch
  • Extract data from an existing spreadsheet
  • Edit an existing spreadsheet

The DataFlex Library for LibXL makes it fast & easy to use LibXL with your DataFlex applications for much less cost than licensing Microsoft Excel. To see LibXL working in a simple DataFlex app, check out the LibXL Demo.

More reasons to use the DataFlex Library for LibXL …

  • High performance: LibXL is a very high performance engine, capable of creating up to 2 million cells per second.
  • LibXL offers a very broad array of functions that the DataFlex library translates to a list of easily programmable options.
  • Support for both the old Excel file format (.xls) and new formats (.xlsx and .xlxm), and numerous formatting options for fonts, borders, fill patterns, merging cells, etc.
  • High efficiency: LibXL is far more efficient than the COM+Excel alternative which can use more than 50% of CPU resources to process a spreadsheet. LibXL’s lower CPU overhead is particularly important for web-based applications.
  • And even more great news: For solutions using LibXL, no royalty-fees are charged for LibXL when distributing your application!

The DataFlex Library for LibXL consists of:

  • A DataFlex Library workspace that can be attached to any workspace
  • A demo workspace that contains examples of using the library

The software is for use with LibXL v3.8.1.0 and is not supported for any older or newer version. By default, LibXL uses a demo license, which is fine for experimentation. It will write a banner in the first row of each spreadsheet and it will be able to read only 300 cells (first row is unavailable).

The DataFlex Library for LibXL is sold by Data Access Europe, and includes a LibXL license. A commercial license is required to remove the banner and reading limitation.

Comparing the DataFlex Library for LibXL vs. COM proxy classes for Excel 

In the past, DataFlex developers would usually use COM to provide this same functionality, but this has some drawbacks, especially for web applications.

DataFlex developer and business analyst Frank van den Beemt for the Dutch firm VolkerWessels Infra ICT gives an overview of his experience using both DataFlex COM proxy classes generated from Excel, and the DataFlex Library for LibXL with his firm’s applications ...


For many years I’ve used an Excel COM class (generated with DataFlex) for creating and reading Excel documents. Once you have generated the class, you must build your own Procedures and Functions in several Excel objects within your program (or build your own .pkg) in order to use it.

Advantages with COM:

  • In debug mode you can see what is happening
  • Anything is possible

Disadvantages with COM:

  • You need to add your own procedures and functions
  • During processing it uses more than 50% CPU capacity
  • The processing speed is very slow. It may take several minutes for a large document
  • You need an Excel version installation on your server/webserver. With the latest version of Excel, the process of opening Excel must be done by a known registered user and not the system.

Using the DataFlex Library for LibXL

For a web application, I could not use the COM class because of Excel licensing issues, so I purchased the DataFlex Library for LibXL. This library is immediately ready for use and comes with example source code to help get you started.

Advantages with LibXL:

  • You don’t need an Excel license
  • You don’t need to add your own procedures/functions
  • It is extremely fast (generates large sheets within seconds)

Disadvantages with LibXL:

  • Limited debugging possibilities (you cannot see what is happening)
  • In many cases you have to split up cell formatting and cell value writing

With the DataFlex Library for LibXL I easily created an Excel spreadsheet in which I used a limited variety of cell formatting as shown below. While formatting was a bit more difficult than I was used to doing within the COM class, overall using the Library is faster and easier, and the results are excellent!
Excel LibXL screenshot
  Figure 1: Excel spreadsheet created with the DataFlex Library for LibXL


See the working LibXL + DataFlex demo, or better yet, get started today by ordering your license for EUR 495 via orders@dataaccess.eu!

When viewing the LibXL demo, please note:

  • The demo is a compiled example, so you can see how it works (what it does) – you can see the DataFlex code and how it is programmed;
  • You cannot compile the code yourself, however, because we do not provide the DataFlex Library source code for evaluation purposes.