0

Are there any options implementing any kind of service that can be consumed by Excel, and user can modify/add data via Excel, and such changes will be communicated back to service? I read some articles about OData but it seems to be a one way export in excel, no data modification are supported.

DarkDeny
  • 1,750
  • 2
  • 21
  • 31
  • 1
    Using VBA you can do about anything. http://stackoverflow.com/questions/3250186/how-can-excel-2007-2010-consume-a-rest-web-service, http://stackoverflow.com/questions/19553476/how-to-make-rest-call-with-vba-in-excel – CodeCaster Mar 05 '16 at 12:31
  • Did you try Microsoft.Office.Interop.Excel? http://www.dotnetperls.com/excel – masinger Mar 05 '16 at 12:37
  • @CodeCaster I am not sure about modifying data. Does Excel need any kind of addin, or is there some builtin features? – DarkDeny Mar 05 '16 at 12:37
  • 1
    I wouldn't put this on hold. Many developers ask the same questions, and need a top-level answer on what approaches exist... – code4life Mar 06 '16 at 16:10

3 Answers3

2

There are several ways to expose data from C# to Excel so that you can edit the information in Excel and pass it into the C# world (... and beyond, e.g. to a database server, to an entirely different system, etc etc).

One way -- which happens to be an inherent Excel feature -- is to build an RTD server that wraps a C# dll. You could use a tool like Excel-DNA which will take care of the complexities of writing the RTD wrapper. One nice feature of RTD is that it's quite asynchronous, which can be a performance booster at times. One pain point of RTD is that the asynchronous-ness is managed on a cell-by-cell basis, which means that if the cells relate to each other in any way (e.g. one cell contains the part number, another contains quantity, and yet another the price, and you need all three to get anything done) then you have to write some clever logic to accomodate this. Not impossible, but definitely a pain point. Usually what we would do is create a very hidden sheet that contains the RTD formulas to watch over a different sheet for changes, and handle the changes accordingly.

Another approach would be to write what's called UDF's. Similar challenges to RTD, but you get to have the reference to the entire worksheet rather than one cell at a time, so the pain point is lessened a bit. UDF's are not asynchronous out of the box, but you can definitely make them behave asynchronously. This is pretty important - if you have a long running operation inside the UDF function, and you don't background thread it, Excel will freeze until you complete the function. This could lead to users thinking you broke Excel (LOL). Note this is not an issue with RTD's, since they are already asynchronous. There's several ways you could create a UDF, one of them is again Excel-DNA, but you should explore your options before going headlong into that route. The actual implmentation of handling change is similar to RTD - create a very hidden page, scan the target worksheet.

There is yet another approach, which would be to create a VSTO add-in. This basically integrates your C# dll into Excel. You can create toolbar elements, intercept Excel events -- essentially you can interact with Excel on a very low level. Pretty much anything you can do in Excel VBA, you can do with VSTO, and more. Handling change here is done at the add-in side (i.e. your code-behind .cs files) and the world is your oyster at that point. I usually write a cell changed event handler, but you can capture changes at pretty much any point that you want. You can even defer handling the data change until the user presses the save button - I used to do this, because it was a pretty straightforward paradigm for the end-user.

Those would be the 3 options that come to the top of my mind.

code4life
  • 15,655
  • 7
  • 50
  • 82
0

You might take a look at the Microsoft.Office.Interop.Excel namespace.

Here is a good explanation what one could do with it.

masinger
  • 775
  • 6
  • 13
  • interop excel allows me to create/read excel files, make some managing operations. But how about scenario where end user modifies data in excel sheet, and these changes are reported to my services? – DarkDeny Mar 05 '16 at 12:40
  • So the edit is done externally? You could start an Excel process that opens the file and shows it to the user. Then wait for this process to terminate and read the file again. – masinger Mar 05 '16 at 12:48
  • That would not be a live update, right? – DarkDeny Mar 05 '16 at 12:51
0

Try the Excel-DNA library http://exceldna.codeplex.com/

Better avoid Microsoft.Office.Interop libraries. They are very difficult to handle disposing of references and they require office (in this case Excel) to be installed in your machine (and even worse app servers etc.)

gpanagopoulos
  • 2,842
  • 2
  • 24
  • 19
  • Probably I am missing a part how to use excel DNA in my scenario. Can you please give me more details? – DarkDeny Mar 05 '16 at 12:51
  • Using Excel-DNA, you can create an excel add-in using C#. In the add-in you can pretty much anything you can do in a plan .NET client application including integrating with services or writing data in the excel. – gpanagopoulos Mar 05 '16 at 12:56
  • You can check the tutorial found here: http://exceldna.codeplex.com/documentation. – gpanagopoulos Mar 05 '16 at 13:06