Excel Document Generation for .NET Developers - Your Options

Thinking Out Loud
15 June 2015

In blog posts leading up to our launch couple of weeks ago, I described our new XL Export Library – the ultra fast Excel data export engine that generates spreadsheet files by writing them directly to a stream.

If you have used our Document Generation libraries in the past, you may be familiar with the DevExpress Spreadsheet Document Server....A non-visual component library for .NET with complete spreadsheet generation functionality. The Spreadsheet Document Server API allows you to load, create, modify, print, save and export spreadsheet documents and of course perform data analysis and calculate worksheet formulas.

Since the release of v15.1, a number of you have asked for guidance as to which of these 2 products to use within your projects. in this post, I'll try to explain the differences between the two product libraries and hopefully help you choose the right product for your next .NET project. 

I'll start with the key differences between the Spreadsheet Document Server and the new XL Export Library -- the basic algorithms each use for document generation.

Spreadsheet Document Server

The Spreadsheet Document Server's architecture is based on its internal document model which stores all spreadsheet data in memory. This means that when you load a document, the Spreadsheet Document Server reads the file (using the appropriate file format, for example XLSX) and imports data (cell values, formulas, format settings, etc.) to the document model. At this point, you can use the spreadsheet API to modify the model or perform calculations over cell values. Once complete, you can call the SaveDocument or ExportTo* method and export the document model with modified data to the desired file format.

Generating a new document works in much the same way. When you create a new workbook using API methods, the spreadsheet control creates a new document model, generates its content and then exports it to the specified file format. This "universal" approach allows you to use a single library to both read/edit and create new documents....but it can cause significant problems with performance. Generation of very large Excel files will often lead to the high memory consumption and associated time delays.

The graphic below helps illustrate the processes used by the Spreadsheet Document Server and XL Export Library to generate files...

.NET Spreadsheet Excel Document Generation

XL Export Library

As you can see in the graphic above, the DevExpress XL Export Library does not use an internal document model and writes data directly to the output stream in consecutive order: row by row, cell by cell. This approach allows it to generate Excel files at very high speeds, with very low memory consumption (because there is no need to put data into an in-memory document model and then export the model's content to the output stream).  

The charts below breakdown the performance advantages when exporting data using the DevExpress XL Export Library. When generating a large XLSX file with 1048576 rows and 20 columns, the XL Export Library is 2.5X faster and uses up to 10 times less memory than the Spreadsheet Document Server.


Your Best Option and Our Recommendation

If performance is your number 1 requirement when generating/exporting data to Excel, and you don't require the capabilities built into the Spreadsheet Document Server, then it's best to use the XL Export Library. It's optimized for performance and allows you to create extremely large spreadsheet files in the most efficient manner possible (important to those of you generating numerous Excel documents on the server side).

I'll wrap this post up with a summary of both products so you can decide what works best for you and your business.


Supported Document Formats

Document Format

Spreadsheet Document Server

XL Export Library

XLSX

Yes

Yes

XLS

Yes

Yes

CSV

Yes

Yes

XLSM

Yes

 

XLTX/XLTM/XLT

Yes

 

TXT (Tab Delimited Text)

Yes

 


Supported Document Features

Feature

Spreadsheet Document Server

XL Export Library

Workbook/Worksheet

Yes

Yes

Rows/Columns

Yes

Yes

Cells

Yes

Yes

Merged cells

Yes

Yes

Formulas

Yes

Yes

Calculation engine

Yes

 

Custom functions

Yes

 

Defined names

Yes

 

External links

Yes

 

Cell styles

Yes

 

Cell formatting

Yes

Yes

Conditional formatting

Yes

Yes

.NET number formats

 

Yes

Theme colors

 

Yes

Pictures

Yes

Yes

Charts

Yes

 

Comments

 Yes

 

Sparklines

Yes

 

Hyperlinks

Yes

Yes

Tables

Yes

 

Filtering

Yes

Yes*

Grouping/Outline

Yes

Yes

Data validation

Yes

Yes

Frozen panes

Yes

Yes

Page breaks/ Page setup

Yes

Yes

Print area/options/titles

Yes

Yes

Headers/footers

Yes

Yes

Error checking options

Yes

Yes**

Protection

Yes

 

Document properties

Yes

Yes

*Limited support: enable/disable filtering for the data range without specifying filter criteria.
 ** Available for the entire worksheet data range, not a specific cell range.


To learn more about our new XL Export Library, review our Demo Center examples (see the XL Export API module in the Document Server category) and Code Examples database:

As always, we welcome your feedback - tell us know what you think of our new XL Export Library and how you might use it going forward.


no comments
No Comments

Please login or register to post comments.