2

I got an Excel sheet with some formulas in them. What I'm trying to do is fill in a formula and let Excel calcute it, then return to me the outcome.

Let's say my formula ( which is in cell A3) look like this:

 =SUM(A1+A2)

What I would like to do is fill in A1 and A2 from my c# code. Than read the outcome that is in A3. However, since this is for a website, I want multiple users to be able to calcute at once. So I suspect really opening the file will take too many resources.

I've searched high and low and found this: SpreadsheetGear. I'm not sure, but I think that library should do the trick. Too bad it is way to expensive for the simple things I need to accomplish.

Is there a (preferable free) way to return the value that Excel calculates based of my formula?

Chumbawamba
  • 67
  • 2
  • 5
  • 1
    That's an [Excel-lent Design](http://thedailywtf.com/Articles/Excellent-Design.aspx). – CodeCaster Nov 23 '12 at 10:51
  • After reading the article I see what you mean. It's indeed a similar problem. However I'm not willing to install Excel on the webserver :P The must be other ways to do this, right? – Chumbawamba Nov 23 '12 at 11:13
  • 2
    Yes, by doing the calculations from code. Perhaps you don't even need Excel, but you can probably hardcode them if they don't change in logic but only in parameters. Otherwise you're going to need a library like the one you mentioned, I don't know of any free ones by head. Alternatively you can dump Excel and use a [rule engine](http://stackoverflow.com/questions/1596073/what-can-rules-engines-accomplish), if the formulas do change. – CodeCaster Nov 24 '12 at 16:01
  • Depending on your application if you're simply trying to give multiple users access to the spreadsheet you could use Google Docs to create a spreadsheet and then share it with the various users. Or if you want to calculate it in the backend but you want users to enter the values for A1 and A2, you could create a web form using Wufoo (www.wufoo.com) or something similar and collect the data directly from Wufoo and pull it into your spreadsheet. Or as @CodeCaster said, using #C to calculate the result. – getting-there Dec 22 '12 at 08:57
  • It would help a lot if you could give us some more info on what formulas you are trying to calculate. For the example of A1+A2, it is trivial to do it in code, so what makes you require Excel? – RipperDoc Feb 20 '13 at 03:21

1 Answers1

0

Does that work?

        Range r = sheet.get_Range("A1");
        r.Value2 = 1;
        r = sheet.get_Range("A2");
        r.Value2 = 2;
        r = sheet.get_Range("A3");
        r.FormulaR1C1 = "=R[-2]C+R[-1]C";//=(A1+A2)
        int A3_Value = (int)(r.Value2);
0699
  • 250
  • 2
  • 11