Interacting winform app with excel

views:

answers:

Interacting winform app with excel

I have a c# winform app that shows trading info to users. What we want to do is be able to open an excel sheet and have it request the data from the application. In return application gives data back to excel. After that if the data is updated in application, it also gets reflected in excel. In other words, we make excel display what app is showing (only when excel requests it).

Does anyone know what we need to achieve this.

Thanks a million.

Original:

Could use a database connection in Excel to query the database directly?
Where does the winform app store it's data?

Edit:
I can think of a few approaches:

Use excel automation to generate the spreadsheets as the user selects the columns.
This is probably slower then option 2.
On selecting the columns, save the information to a configuration table on the database.
Have the spreadsheet read this, and using VBA adapt the sheet to match the application.
This is more complex than option 1, but much quicker.

Bravax 2010-08-27 12:44:37

it stores in local database (sqlite). But the idea is to allow user to manipulate the data he sees on the screen. User on the front end selects the columns that he is interested in and then the data is displayed on the grid. User can create many different sets of columns and change them. As user changes, we need to update the excel grid accordingly. Therefore, going directly to database is not an option. Excel has to be in sync with current application state.Thanks

Sheraz Khan 2010-08-27 13:07:32

This solution(#2) might work but doesn't give the flexibility. First, Excel needs to ask for info. Considering that we end up saving in local database, we have many screens and we are going to have to save a lot of data and extra complexity to application. Are you familiar with RTD Server (real time data server)? I was trying to use it but could not figure out how I can have my application feed data to it.

Sheraz Khan 2010-08-27 13:49:13

I'm not familiar with real time data server. In your question you state (only when excel requests it). Doesn't that mean, you can refresh the layout and data from a config table, when the users click a button in excel?

Bravax 2010-08-27 14:08:33

Possibly another question to stackoverflow, could help resolve your issues with real time data server?

Bravax 2010-08-27 14:09:01

yeah I think real time data server is the answer to it. I did post the question yesterday but didn't get any response. There isn't much info available as well. I has more to do with COM server and I don't have any background with COM world. Implementation is simple though. If you have have COM background,may be you can get it working?

Sheraz Khan 2010-08-27 14:25:24

Com interop may be of some use to you. http://dotnetperls.com/excel-interop it allows you to create/edit/read excel (and other com apps) from within your C# app when you reference their namespaces.

JohnnyH 2010-08-27 13:48:21

johnnyh, interop allows your app to interact with excel. What we want is to allow excel interact with app. In other words, excel asks for the data and application provides it.

Sheraz Khan 2010-08-27 13:51:19

in that case, you could write a custom macro and have a button on the excel toolbar with a call to the application, which then via interop repopulates the data on the sheet?

JohnnyH 2010-08-27 13:55:36

Can you please provide a link that shows how to do that? What's very interesting for me is this line of yours"have a button on the excel toolbar with a call TO YOUR APPLICATION". How can excel ping my app???

Sheraz Khan 2010-08-27 14:02:37

You can use Visual Studio Tools for Office to create a custom add in for Excel. There are tutorials here http://msdn.microsoft.com/en-gb/vsto/dd162436.aspx This particular section might also be of use http://msdn.microsoft.com/en-us/library/htx7t4k0.aspx#data

JohnnyH 2010-08-27 14:20:54

Johnnyh, I forgot to mention that we are using (.net 2.0) with vs.net 2005. I know we need to move on :-( and Visual Studio tools for Office requires vs.net 2008 or 2010.

Sheraz Khan 2010-08-27 15:24:29

JohnnyH 2010-08-27 15:40:22

ah. I don't think you are gonna like me now :-(. The kit is applicable to Office 2003 or later ones. We use 2002. None the less, you said you have done similar work for your client. This gives me hope to move forward. BTW have you looked into RTD Server (Real time data server)?

Sheraz Khan 2010-08-27 16:10:48

Would an in memory database work for you? I have used this one http://www.quilogic.cc/features.htm in the past to make Access VBA work with a .net application. You could have your application write the current data into the in memory store and Excel can read from the store through VBA.

J. Clay 2010-08-30 04:19:56

ansaurus

tags:

views:

answers:

Interacting winform app with excel

related questions