So, some of us @ the eFORCE .NET Team have been spending some of our free time in playing around with ways in which you can program excel on the web server. Not because that's what we primarily do for a living, but because we like to play around with wacky problems. Making excel work in a Web environment has been a tricky problem in the past, mainly because of thread safety issues and other Issues Microsoft points out on MSDN.
In the past we've taken more than 3 tools to a test-ride. Most of them are pretty interesting. But all of them have their Pros and Cons. For Example, some of them won't support all functions supported by Excel 2007, while the others just won't recognize Excel 2007 file formats.
A few days ago I came across Excel services and started playing around with it. After a few days of playing around with it, I loved it and thought I would throw out a knowledge sharing session to folks at eFORCE to see how many others are interested in solving similar problems. I've been a little busy these days, so the session was conducted on short notice (we announced it Friday late night) and I mentioned in my email, that it would be a training without the bells and whistles of a formal training. Which means no hand-outs and no Powerpoint slides formatted with eFORCE branding (and no free food that we had in NEO and all that partying :)).
This evening when the presentation started we had throng of eFORCE'iasns. I think if you minus the eFORCE HR, Accounts, Admin and IT, pretty much the rest of eFORCE was there! One of the guys from accounts had planned on attending it but looks like he couldn't make it. Another person from accounts tells me he's interested in Excel Services and will take a look at my recorded training. :)
See? We ALL Love Excel Services!!! :) We had people from creative teams, testing teams, .NET teams, J2EE teams and pretty much every other team that you can think of. I was kind-of worried initially, because I thought people had confused this with a Introduction to Microsoft Excel Training or something, so I asked them if they thought they had read the training email correctly and threatened them that I'm going to write some C# code in here - but they just didn't budge! :)
Anyways, it was fun conducting the training; except of course just too many betas and software running on my box. So, the box was kind-of dead in the middle for a couple of minutes and I had to "trust" that it would come back to life again after a couple of minutes of hard disk thrashing. I had Sharepoint 2007 Beta, Excel Services, SQL 2005, Office 2007 Beta running on a VPC which was running on my box.
And then my box, which, besides running than 500 meg RAM, 7 Gig VPC, was also running Studio 2005, SQL 2005, SQL Express, My own active directory, My own DHCP Server, My own DNS Server, 10 skinning applications, 20... anyways I think I'll stop.
So KC (one of our very highly capable BA's who was also there), thinks that I shouldn't go on and on about the things that are loaded on my box. He thinks it's usually easier to just say what's not loaded on my box and move on with the training. So, I decided to use that same overworked box which was also recording the training for conducting the training which might have eaten up like 10 minutes of thrashing and hanging time from that one and half hour of training. And then there was the HR, who was concerned that people didn't have enough seats in the training room and all; But the attendants were pretty awesome and didn't mind standing. See? We ALL love Excel Services! :)
Now, I'm in the process of trying to edit that recording of the training and I just can't seem to get it right! Because there were some crappy jokes I cracked and then there were crappy jokes others cracked and every 15 minutes or so we seem to have some mild ha-ha-ha going on in the background. This background noise reduction in video editing is becoming kind of tricky for me! I told them I recording this stuff! And I warned them, that they should just shut-up and that anything they say, will be recorded and can be used against them. But most of them just didn't give a dam! :)
And then there were a lot of "Can I do this?" kind of questions and there were "How's this better than that 3rd party tool we evaluated last week / month / year?" kind of questions and then there were "How can I make it talk to J2EE?" kind of questions and then there were "How can I make J2EE talk to it?" kind of question and then there were some "Can I see the WSDL of Excel Services on the projector please?" kind of questions and then there were - "Are we covering some Sharepoint in this session too?" kind of questions :) - So basically there were tons of, well... questions! Which is good. Honestly! I'm not kidding!
It looks like I've been able to answer most of them to the best of my knowledge (or at-least I've tried :)). Give me a shout / drop me an email / comment, if there are more. One of the questions that came up more than once was - Can I call the SetCell Method and instead of passing a Value just pass a formula? In other words, can I change a formula or create a new formula using Excel Services? Interesting question. I think that one was the only one that was left un-answered - where I said - "Don't know. Try it. Good luck!". So, Let's try it, right now!
In the demo one of the simple Excel Spreadsheet example we wrote was one which let's you enter purchase (in cell B1), sales (in cell B2) and then there was profit (in cell B3), which got calculated based on a formula. And we covered the Sharepoint webpart based UI and a WinForm UI that hits the Excel Services Web Service. One of the examples we wrote had the code to set values. It looked pretty much like:
49 protected void SetValuesToSheet()
50 {
51 // Always Error Handle After checking the status. Moving on with the Demo For Now.
52 myStati = myService.SetCell(SessionId, "External Reports", 0, 1, txtPurchase.Text);
53 myStati = myService.SetCell(SessionId, "External Reports", 1, 1, txtSales.Text);
54 }
Let's tweak this a little bit and try to set the value of one of these cells equal to other:
49 protected void SetValuesToSheet()
50 {
51 // Always Error Handle After checking the status. Moving on with the Demo For Now.
52 myStati = myService.SetCell(SessionId, "External Reports", 0, 1, txtPurchase.Text);
53 myStati = myService.SetCell(SessionId, "External Reports", 1, 1, "=B1"); // tryiing to insert a formula!
54 }
And let's run it! And, when we run this we get this exception - "Editing Formulas is not enabled in this release of Excel Services."
So the short answer is, No. You can't. There's an interesting link out here which sums up all those "Can I do this?" type of questions. The thumb-rule, as this link points out, is that you can't "Author Workbooks" with excel service. Changing formulas is considered part of authoring and so are a host of other things (take a look at the link). The link is pretty elaborate and to-the-point on the things that you cannot do with Excel Services. Hope this helps.
I think this takes care of the pending questions. And for everyone who attended. Thanks for attending, asking questions (and if you were standing because of the lack of chairs) - sorry, we just didn't expect so many of you to show up :) and btw, thanks for not beating me up or something, for making some of you guys stand for an hour and a half! :) No, seriously, I'm not kidding - thanks for attending! With all those interesting questions it was fun taking the session.