XL Sheet Writer

The XL Sheet Writer library makes it easier for .NET developers to write raw data in Excel spreadsheets.

Existing libraries such as Open Xml API provide great features to manipulate Office documents (Word, Excel, and Powerpoint). Unfortunately, they suffer from performance issues and are unable to write large sets of data in a reasonable amount of time.

The XL Sheet Writer contains a couple of APIs which help you write raw data into a specified worksheet. It does that by using XmlWriter objects and the System.IO.Packaging namespace available in .NET framework (WindowsBase assembly). The Excel workbooks are organized internally as archives and if we are able to manipulate the data parts and their relationships properly, we can easily create or update workbooks without the need of specialized APIs.

The following example uses a SqlDataReader object to populate an Excel worksheet:

 


 

There are two ways you can use XL Sheet Writer component to write data into an Excel worksheet:

In order to populate a data sheet, you will create a 2007 / 2010 Excel workbook and nominate the worksheet to write data to. The library first writes data to a Xml file, then will use this file as source for the worksheet part. The part will be replaced in two steps:

  1. the existing part will be deleted;
  2. the new part will be created using the internal name of the deleted part and the source Xml file.

To use the main sheet approach, you will create a 2007 / 2010 Excel workbook and nominate the worksheet to add the data rows to. The library identifies the corresponding Xml file, will load it into memory, add the data rows, then update the workbook.


In either case, the worksheet is referred by its internal name (the name that the user cannot modify). This name is usually Sheet1, Sheet2, etc.

This approach is useful when using Excel files as templates that source their data from existing worksheets. The data can be analysed, drilled-down, or displayed in various forms (reports, pivot tables, charts, etc.)

The trick is to build your template accordingly. Excel is a great tool for reusing data. It has numerous features that help you manipulate data stored in a worksheet: formulas, named ranges, charts, pivot table, etc. Excel allows using dynamic named ranges which can be shrunk or expanded depending on the data set size.

Something worth mentioning is the Shared Strings Dictionary used by Excel to store string data. This is a separate part in the Excel workbook which stores all the strings used in the workbook. The strings have indexes which are stored in the value field of each text cell. The XL Sheet Writer component will use the shared strings dictionary of the template and will add the strings identified in the data.

Note: If required, you can use both ways to write data. However, due to the forward-only nature of data readers, the XLDataReaderSheetWriter class will populate only one worksheet.