asked on

Fastest way to interface between Excel data and C# objects

Hi, I want to know what the fastest way is of reading and writing data to and from an open Excel workbook to c# objects. The background is that I want to develop a c# application that is used from Excel and uses data held in excel.

The business logic will reside in the c# application but the data will reside in an Excel workbook. The user will be using Excel and will click a button (or do something similar) on the excel workbook to initiate the c# application. The c# application will then read data off the excel workbook, process the data, and then write data back to the excel workbook.
There may be numerous blocks of data that are required to be read off and written back to the excel workbook but they will normally be of a relatively small size, say 10 rows and 20 columns. Occasionally a large list of data may need to be processed, of the order of 50,000 rows and 40 columns.
I know that this is relatively easy to do say using VSTO but I want to know what the fastest (but still robust and elegant) solution is and get an idea of the speed. I don't mind if the solution recommends using third party products or uses C++.
Thanks.

Dirk Haest

I would suggest to use oledb to connect to your excel. That's the way you can use like a database...
Once you read the "database", you can easily use this to convert them into objects like you need, create them ...

C# example: How to read an Excel file with OleDb and a simple SQL query?
http://zamov.online.fr/EXHTML/CSharp/CSharp4.html

Reading and Writing Excel Spreadsheets Using ADO.NET
http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

If you gonna use the method mentioned in this
http://www.sharpprogrammer.com/dotnet/how-to-read-excel-file-in-c-net/
there are a lot of possible problems (I experience themself and seen in other trheads, like memory leaks, ....)

Converting Custom Collections To and From DataTable
http://lozanotek.com/blog/archive/2007/05/09/Converting_Custom_Collections_To_and_From_DataTable.aspx

jw_pr

ASKER

Hi Dhaest, thanks very much for the answer. Would you say that this is conclusively the fastest way of doing this for my use case i.e. interfacing while using excel?

By the way, I'm having some issues with the EE site at the moment, I can't see any graphics for some reason, for example I can't see buttons just placeholders, so need to get that resolved.

Thanks,

Dirk Haest

>> answer. Would you say that this is conclusively the fastest way of doing this for my use case i.e. interfacing while using excel?

I think so, because you don't need any overhead of excel-objects

jw_pr

ASKER

Thanks, I just did a search and here is a claim that SpreadsheetGear is faster:

http://forums.asp.net/p/1524164/3682891.aspx

I don't know if these solutions apply to data in an open workbook i.e. in memory, as opposed to saved files.

JonMny

You might want to look at flexcel. It's less expensive and very fast.http://www.tmssoftware.com/site/flexcelnet.asp

jw_pr

ASKER

Thanks, but can you use it to read from an open workbook that hasn't been saved? It seems that spreadsheet gears can't and it looks like this can't either?
Thanks

JonMny

if its open then you would need to use automation to get live data.

jw_pr

ASKER

okay, but what is the fastest way of doing that? Do you know? I have heard of using VSTO, XLL, DDE and even the clipboard (although I wouldn't use that solution). I was hoping there was a fast hardcore solution.

jw_pr

ASKER

I see there was a relevant post about VSTO here a while ago and I don't know if there is any update. I am also currently loading data using VBA.

https://www.experts-exchange.com/questions/23635459/Reading-in-from-Excel-via-VSTO-is-about-10-100-times-slower-than-from-VBA.html

JonMny

I think it would be hard to beat VBA or an xll for speed but the communication between apps would be more difficult.
Check this one out.
http://windale.com/edtx.php

jw_pr

ASKER

Thanks, will check it out.

jw_pr

ASKER

I'm waiting for a reply from windale. But if VBA is the fastest way, is there any way of calling a DLL from VBA that hooks into a c# server, i.e. have VBA load the data and then pass it to the C# server somehow?

ASKER CERTIFIED SOLUTION

JonMny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jw_pr

ASKER

Thanks. Yes, the user needs to interact with the c# application (through excel).
By the way, do you know if the VSTO performance has improved relative to VBA in the last couple of years?
Thanks.

JonMny

With VSTO it really depends on what your doing, Based on your description of the application your not doing a lot of manipulation of the sheets I think vsto would perform ok. VSTO is some cases can be faster, if your current vba code is doing alot of number crunching vsto would most likely outperform.
I have no complaints about the speed of vsto in our apps. (we dont have many with 5ok rows however)

jw_pr

ASKER

I use it for reading and writing blocks of data to and from the Excel sheet, most of the blocks are small, say 10 X 40 and reading and writing muliple comments. Occasionally there is a large block of data such as 50k rows but it's fairly rare. The c# app would need to constantly go back to the excel sheet to get data during execution.

JonMny

If you can avoid looping through cells I think you would be ok. Excel is very slow when looping cells and VSTO doesnt make it any better.

jw_pr

ASKER

I don't think that will be a problem as it should be feasible to cut and paste as ranges using the transpose method.

jw_pr

ASKER

Excel DNA can read live data. Search for the same question on stack overflow