Displaying a dataset or datatable in Excel

D

David

There's something I think ought to be easy, but I can't find a way to do it.
Either there's something wrong with the way I'm searching, or there's a
reason it can't be done easily.

What I want is to create an excel addin that, when the user opens a
spreadsheet , it goes out and fetches some data from a database, and displays
it in a table. Pretty straightforward. Here's some code:


void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook wb)
{//I've also attached this to other events. The "open" event isn't
the key part. It doesn't do anything in any event.

string connstring="Integrated Security=SSPI;
database=MyInventoryDB; Data Source=MyServer\\MyInventory";

System.Data.SqlClient.SqlDataAdapter da=new
System.Data.SqlClient.SqlDataAdapter("Select * from
PlantInventory",connstring);
System.Data.DataSet ds=new System.Data.DataSet();
da.Fill(ds);
Excel.Worksheet
activeWorksheet=((Excel.Worksheet)Application.ActiveSheet);
Excel.Range
inventoryrange=activeWorksheet.get_Range("C6",missing);
//Here's the line I know doesn't work, but I think something
sort of like it ought to.
inventoryrange.Value2=ds.Tables[0];
//or maybe create a range and set a datasource.
}

So, the key is that I want to use a range of cells that I define sort of
like I would use a datagridview. I want to fetch a table, as above, and then
tell the sheet to display the data in a given range.

When googling, I seem to find people who have iterated through each row in
the returned datatable, and each column within the row, and filled in one
cell at a time. I know how to do that, but it seems like this would be so
commonly requested that there ought to be a simple, one or two step command
to make this happen. Any suggestions?
 
J

Jie Wang [MSFT]

Hi,

If your add-in is targeting Excel 2003 or higher version, I highly
recommend you use Visual Studio Tools for Office as the development tool.
It provides built-in databinding facilities so that you can bind your
DataTable to an Excel ListObject.

Please let me know which version of Visual Studio you're using so I can
provide further information on how to use ListObject.

Regards,
Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
D

David

Yes, we are targetting Excel 2003. (Using Visual Studio 2008 for the
development platform).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top