I just want to round-trip System.Data.DataTable to NamedRange - is this easy ?

Jul 18, 2011 at 10:00 PM

Hi,

  I've searched for *hours* on the general interwebs, and sadly it appears that without VSTO, there is no simple API for propogating a DataTable to a spreadsheet (with headers, and datatypes, and possibly a ListObject wrapping it), and of course going in the other direction.

  *Some* promise appears to be in the OLEDB Jet provider direction, however, all those examples I've seen require an external path to the file, as opposed to the workbook I'm currently in.

  To be clear, what I want is:

  System.Data.DataTable dt = GetTableFromService();

  Range["SavedStuff"].DataTable = dt ; //should expand the range as needed

  If there is something not this convenient in Excel-DNA - I'd be happy to contribute it - just give me some pointers on what exists. I really am shocked that this is not a solved problem after several years of having DataTables around, and their obvious similarity to the way Excel ranges are often used. Thanks in advance ! (I really love this project by the way:) )

Dean

Jul 19, 2011 at 4:38 AM

Hi Dean,

Look for existing projects on the web with the designation “Convert DS to RS”, or convert the ADO.NET DataSet (and it’s Datatables) to an ADO Recordset object. It is the only way to get bulk data into an excel spreadsheet with ease. Then bind the Recordset to a QueryTable object or List (in later versions of Excel) or to a PivotCache object (for display in a PivotTable). In the case of a QueryTable you can then populate a sheet with the raw results and create a named range off the results. I hope this helps. This approach is easy, depending on your perception J

Jordan

From: chicagogrooves [email removed]
Sent: Monday, July 18, 2011 5:00 PM
To: Jordan Blaydes
Subject: I just want to round-trip System.Data.DataTable to NamedRange - is this easy ? [exceldna:265632]

From: chicagogrooves

Hi,

I've searched for *hours* on the general interwebs, and sadly it appears that without VSTO, there is no simple API for propogating a DataTable to a spreadsheet (with headers, and datatypes, and possibly a ListObject wrapping it), and of course going in the other direction.

*Some* promise appears to be in the OLEDB Jet provider direction, however, all those examples I've seen require an external path to the file, as opposed to the workbook I'm currently in.

To be clear, what I want is:

System.Data.DataTable dt = GetTableFromService();

Range["SavedStuff"].DataTable = dt ; //should expand the range as needed

If there is something not this convenient in Excel-DNA - I'd be happy to contribute it - just give me some pointers on what exists. I really am shocked that this is not a solved problem after several years of having DataTables around, and their obvious similarity to the way Excel ranges are often used. Thanks in advance ! (I really love this project by the way:) )

Dean

Jul 19, 2011 at 6:32 PM

Thanks Jordan - Could you provide specific links / code ?

To be clear, I have a routine that iterates over a datatable and gets a string[,] multi-dimensional array, which i can bind. Thus I can't see what advantage converting to a recordset would be- it still has the 'copy from one structure to another' property which I find ugly, and indicative that I'm not using the correct API (though, it works).

I am interested in a) going both directions - sheet to DataTable as well as DataTable to sheet b) having less to no iteration code c) having named ranges that dont have to be maintained. Something just like my code sample.

Without seeing code, I can't tell whether the solutions you describe depend on API's from the Microsoft.Office.Tools namespace (or the COM Microsoft.Office.Interop.Excel one )  I can't use the Tools.* APIs because I can't get the VSTO system to initialize inside of ExcelDna (all the calls to .GetVstoObject() return null).. Unfortunately web searches like you suggest often return VSTO (Office.Tools.*) solutions with high frequency.

It seems likely that some API exists in Excel DNA or Interop.Excel, which has wrapped up the iteration and bidirectionality. But several days later, and I'm still searching !

PS Govert, I like the tip that I 'reimplement' what is done in the VSTO Office.Tools later - if I can get that approach to work, I'd gladly contribute it back. But naturally finding a pre-built solution is still my priority :)

Jul 19, 2011 at 7:29 PM
Edited Jul 19, 2011 at 9:19 PM

Hi Dean,
I had some thought on your comments:
“I am interested in a) going both directions - sheet to DataTable as well as DataTable to sheet”


Sheet to DataTable –

I have done this before using the Jet OLE DB Provider with Microsoft Excel as the data source

I got the code from the “ADO.NET Cookbook” published on O’Reilly by Bill Hamilton, First Edition pages 6-9

http://books.google.com/books/about/ADO_NET_cookbook.html?id=5Fu6ePnKgRQC


You should be able to see the sample code if you preview the keywords “Connecting to a Microsoft Excel Workbook”. There may also be a free PDF floating around on the internet but it is a later version (3.5) and I wasn’t confident this section remained in later editions.


It’s a very cool syntax because you can use Psuedo-SQL to reference the cells or ranges in Excel. I love this approach and ease of use.


DataTable to Sheet –

Since you have a multidimensional array, you could iterate through the array and populate the cells of a new sheet (Array Iteration approach). This is cumbersome and could be a tad slow, depending on the implementation. Since there is no native support for ADO.NET in Excel, as you mentioned, the easiest method is to convert the DataTable to a ListObject (VSTO approach).

But again, as you said, this approach is not very attractive with ExcelDNA in the mix.

The best non-VSTO solution I have found (and it does iterate through the data too, but it is pretty fast) is to keep the DataTable intact and convert it directly to an ADO Recordset.

Convert the ADO DataTable to an ADO Recordset http://www.codeproject.com/KB/database/DataTableToRecordset.aspx

2. Then take the resulting ADO Recordset and bind it to a QueryTable Object using the QueryTables.Add method
http://msdn.microsoft.com/en-us/library/ff837764.aspx

You could also at that point take the data as a whole and give it a range name for easy reference. I believe the method described above uses only Microsoft.Office.Interop.Excel and it’s dependencies, and does not require VSTO.

Drawbacks to the QueryTable/Recordset approach:

1. The querytable refresh is now entirely disconnected from Excel. Meaning, Excel cannot natively refresh this object, you must do it yourself through a method you design. This can be tricky because Excel will attempt to refresh when the user refreshes the spreadsheet (usually only during CalculateFullRebuild only, but sometimes it just happens) and it will break unless you tell Excel that the Refresh is manual.

2. The same drawback exists for the Array Iteration approach, but it might be a little cleaner, since you don’t have to mess with the properties of the QueryTable to get Excel to keep its hands away from the refresh methods.

This ADO recordset has other uses outside of the QueryTable too, you could bind it to a PivotCache to be used in a PivotTable. Although, I have noticed this is a little slower when binding the Recordset to the PivotCache for some reason.

I hope this gets you home!

Jordan

Coordinator
Jul 19, 2011 at 7:50 PM

Hi Dean,

I'm pretty sure there is no such magic binding API in the Excel COM interface. (And certainly not in the C API or Excel-DNA.) This means the VSTO developers do exactly the 'ugly' thing which is to update that Range through the COM interface from the DataTable.

I'd guess it's a fair amount of work to implement, but I don't think there is any magic. Jordan's advice suggests one to do it.

I'd probably suggest a simpler approach - build up and fill in a Range or ListObject from your DataTable, then listen to SheetChange, check for intersection with your range and update back as needed. The main performance trick will be to write and read the whole range in one go, not cell-by-cell.

So the answer to your initial question is probably 'No, it's not easy.' Sorry.

If you do get it right, I'd certainly be interested :-)

-Govert

Jul 19, 2011 at 8:27 PM
Edited Jul 19, 2011 at 10:30 PM

Hi Dean,

I just re-read you original post, where you mentioned the OleDB Provider for connecting to a workbook. I didn’t catch that you were interested in completing the action with the current open workbook, not a connection to a file. So, that advice on Sheet to DataTable probably doesn’t help and is something you already considered, sorry.

One thing to consider in that approach is that you could temporarily save the sheet from the current workbook you intend to export to a DataSet/DataTable into a TEMP folder as a part of the routine, then use the .tmp workbook file as your connection.

Jordan

Jul 20, 2011 at 4:00 AM
Edited Jul 20, 2011 at 4:03 AM

Hi guys,

My last post on this thread, I found some links that may be useful. I like some of the approaches here

The most interesting one is this, although I'm not sure that the "transpose" option is desirable, but it does appear to "paste" the array in one command:

http://webcache.googleusercontent.com/search?q=cache:0ws2VuDyegkJ:vbadud.blogspot.com/2007/03/transferring-array-to-excel-range.html+array+to+excel+range&cd=1&hl=en&ct=clnk&gl=us&source=www.google.com

I also really like the class that this guy developed, it's very deep and solves issue with the OleDb Adapter that most examples leave as an exercise for the reader, very cool:

http://dhruval-dotnet.blogspot.com/2009/04/import-excel-sheet-to-data-table.html

Another interesting approach using a DataGrid control and HTML tables in Excel:

http://www.codeproject.com/KB/office/ExcelDataTable.aspx

This is a bit of the iterative array approach, but one of the comments by rngGuy on this blog (unfortunately with a dead link) sounds promising:

http://msmvps.com/blogs/deborahk/archive/2009/07/23/writing-data-from-a-datatable-to-excel.aspx

And of course, the VSTO approaches, but the last one uses a little known feature (only in Excel 2003) that I thought was cool, XMLMaps:

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.listobject.datasource(v=VS.80).aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.xmlmaps(v=VS.80).aspx

Of course, it would seem that the ListObject takes care of the same process in Excel 2007 and Excel 2010, but if you find yourself in a bind on Excel 2003, it might be worth a shot (in VSTO)

Keep up the great work on Excel DNA, I love it!

Jordan

Coordinator
Jul 20, 2011 at 6:35 AM

I might add one link to the list:

If you really want to read and write data between Excel and some other structure as fast as possible (from Excel-DNA) you can look at this questions (and my answer about half-way down):

http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-objects.

I think with the 'binding' requirement the main issue is watching for and dealing with the changes that happen in Excel. The Excel object model and eventing is quite limited when it comes to watching an 'area' of the sheet. A fairly general Range or ListObject wrapper like tose in VSTO would be very useful. The 'binding' in th other direction is not really an issue - dumping data into Excel, then formatting headers etc., is pretty easy, and the DataTable has a rich event interface.

-Govert