Despite the strong focus on XML we still need to handle traditional file formats like CSV et al. Here in this post we will take a closer look on how to use CSV files as data sources to ListObject controls in Excel. In the example I use VSTO but the technically approach can be applied in .NET automation of Excel 2003 and 2007.
The ListObject control acts like a DataGrid and in addition to retrieving data it can manipulate data (add, delete and update data) in the data source it’s connected to. To manipulate the data in the data source we need to use a DataSet. It’s my understanding that the option to manipulate data is the main reason for why we can only use DataSets and not DataReaders together with ListObjects.
In this post I will only view one way to retrieve data from a CSV file as it’s beyond the scope of this post to discuss any further ADO.NET. As for the case it can be argued that after populating the ListObject control in the worksheet the VSTO solution can be detached. Host controls like ListObject that are added programmatically are not persisted when closing workbooks. Instead it’s converted to a regular range and the data binding is lost.
In general I find the ListObject control easy to work with and as the above case shows it’s also easy to connect and disconnect ListObjects from various external data sources. Classic ADO supports CopyFromRecordset which makes it convenient to place a large amount of data in a worksheet. Using ADO.NET with the ListObject control provides the same functionality but comes with more powerful options.
The source code is available here for download.
Have You shipped a solution that leverage the ListObject control together with a database?
For an introduction to the ListObject Control please see:
- ListObject Control
- Fill ListObject Control with Data
- ListObject.DataBoundFormat Property
- Working with Tables in Excel 2007 (VBA)