VSTO & .NET & Excel

May 8, 2007

CSV files, ListObject Controls and VSTO

Filed under: VSTO & Excel — Dennis M Wallentin @ 4:04 pm

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.

 databinding1.png

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:

Kind regards,
Dennis

Advertisements

14 Comments »

  1. Hi Dennis: Could you provide the source code in text format? Thanks.

    Comment by NCGordon — May 8, 2007 @ 5:05 pm

  2. Hi Noel,

    The source code is now available for download from the post.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — May 8, 2007 @ 5:10 pm

  3. That’s a really nice article and code example Dennis. 🙂

    It’s interesting that the ‘ListObject’ refers to what Excel 2007 now calls a ‘Table’. This change in naming convention is shows the difference between user interface flexibility and programming interface inflexibility. I suppose — if they wanted — they could create a new ‘TableObject’ that inherits from ‘ListObject’, however, if they wanted…

    Another question this brings up for me is how do we distinguish what Excel 2003 and below called a “Table” (which is a formula-calculation construct and is the reason for the “Semiautomatic Calculation” mode) versus the Excel 2007 “Table”, which as you mention Dennis, is what Excel 2003 called a ‘List’ although Excel 2007’s “Table” takes the concept much further.

    Dennis, do you know how we are supposed to refer to an Excel 2007 “Table” versus what previous versions call a “Table”?

    Comment by Mike Rosenblum — May 12, 2007 @ 6:25 pm

  4. Mike,
    Thanks for Your encouragement 🙂
    The ListObject represent a specific table and it’s a part of the ListObjects collection. I find the naming convention to be odd and cause some confusing. Confusing as we can for instance either work with a QueryTable directly or via a ListObject.
    In order to simplify it I see the Listobject as a ‘container’ that allow us to handle individual tables in code. In my opinion it comes to the best use when working with data from databases as this example shows. So far I have only worked with it through VB.NET (automation and VSTO) + classic VB. Working with it in Excel is, in my experience, more difficult.

    >>Dennis, do you know how we are >>supposed to refer to an Excel >>2007 “Table” versus what >>previous versions call a “Table”?
    Since the ListObject is available in both versions I cannot see it as an issue in code.

    For some days ago I did some “googling”. The outcome of it was a negative surprise – very little was found. The only (logical) conclusion is that the ListObject is not so common in use.
    Kind regards,
    Dennis

    Comment by Dennis Wallentin — May 16, 2007 @ 11:54 pm

  5. Thanks Dennis, for the detail. By whatever name, the ListObject does seem very good and I really like what Excel 2007 has done with Tables. But I do still wonder what legacy Excel “Tables” are now called, hmmm….

    Comment by Mike Rosenblum — May 18, 2007 @ 11:00 pm

  6. I really like the ListObject and therefore I will come back to it in future posts.

    In general I strongly believe we need to make a different between Excel 2003/2007 and previously versions when developing solutions.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — May 20, 2007 @ 2:33 pm

  7. Hi Dennis, great blog you have here, very useful!!
    I am an excel fan and wanted to start learning VSTO. Do you know if it is possible to hide one column of the listobject? (I searched this but it seems it is not possible).
    To go trough this I tried to hide a column of the sheet…but I cannot do it!! Do you know how to hide a column of the sheet via code? (C#.NET or VB.NET)
    Thanks for all this info you are sharing with us!
    Miguel.

    Comment by Qete_arg — June 22, 2008 @ 9:15 pm

  8. Hi Miguel,

    You may try something like the following:

    Globals.Sheet3.Range(“A1:B1”).EntireColumn.Hidden = True

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — June 24, 2008 @ 10:44 am

  9. Thanks a lot Dennis!!
    I was trying to hide a listobject column (the one that has the id field of the DB that I use to populate it) but it seems I cannot do that, thats why I need to hide the entire column.
    Thank you and keep on with this blog!

    Comment by Qete_arg — June 26, 2008 @ 1:31 am

  10. Miguel, You’re welcome.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — June 26, 2008 @ 10:11 am

  11. A bit late…
    The old “Table” “Object” has no object model representative as far as I know. All you can do is search your workbook for cells which contain an array formula which contains the string “=TABLE(“.

    In Excel 2007, on the UI side of the plate MSFT renamed the List thingy (introduced with 2003) to “Tables”, but for backwards compatibility kept the “old” ListObject object name in VBA.

    Clear as mud?

    Comment by Jan Karel Pieterse — August 7, 2008 @ 4:04 pm

  12. Hi J K,

    Thanks and it’s very clear 😉

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — August 7, 2008 @ 4:32 pm

  13. Hi

    I am working on C# VSTO Excel application.I created 2 listobjects created in the same sheet side by side. Listobject1 contains 4 columns and listobjectt2 contains 4 columns.
    I fetch the data for these 2 columns from 2 different tables of database.
    I am doing this in a background threads i.e. fetching the data from table and binding it to listobject but its not stable. Sometimes it wrks fine. but sometimes it throws COMExceptions.
    One of the exception which i got is

    SetDataBindingFailedException: ListObject cannot be bound because it cannot be resized to fit the data. The ListObject failed to add new columns. This can be caused because of inability to move objects to the right of the list object

    Let me know how to resolve it.

    Comment by Ranjith — August 28, 2008 @ 11:53 am

  14. What does the main thread do while fetching the data?

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — August 31, 2008 @ 1:43 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: