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.
License - The XL Sheet Writer library is released under Microsoft Public License (Ms-PL).
With XL Sheet Writer you can simply populate a worksheet with data. The component will replace the worksheet, so make sure you specify an empty sheet to be populated. The XL Sheet Writer component inserts the sheet into the file, based on the PlainData.xlsx template. The Run (Large) option will return 100,000 rows from the database and create a worksheet.
Using XL Sheet Writer allow you to add data rows to an existing worksheet (referred to as main sheet). The component will identify the XML part corresponding to the main sheet and will add row and cell elements. Optionally, you can specify the number of rows and columns to skip, using the properties SkipRows and SkipColumns of the XLPackageSettings object.
Alows the data worksheet populated by XL Sheet Writer to be referenced in another worksheet. The disadvantage is that this approach works only when the number of rows is known, or the template can have a large enough number of references to cover the possible number of rows returned.
Allows the data in the data sheet created using XL Sheet Writer to be used as a source of charts in Excel. Because the number of columns and rows can varies, Excel offers a nice option - Dynamic Ranges. These are ranges that expand based on the number of rows and columns. Dynamic Ranges can be used in charts, pivot tables, and pivot charts.
This is not exactly a feature of XL Sheet Writer, but rather Excel. However, it shows how easily you can integrate powerful features like Conditional Formatting with your data.
One of the Excel's powerful features is Pivot Table. You can use XL Sheet Writer to source data for Pivot Tables. Additionally, if you manipulate data using custom types (and you probably should), you can write your own XL Sheet Writer implementation.