Being able to code C# with Excel 2003 is great fun but now its time for something a bit more useful.
This next code sample shows how to connect to the internet inside of Excel. The idea is when the workbook opens the code behind the workbook searches for quotes on NASDAQ and then populates the spreadsheet with the data. You can imagine you could have a spreadsheet full of data which is retrieved from data sources on company servers and internet sources etc. As you can see coding for excel in C# allows you to leverage your C# skills and re-use them in the world of Excel spreadsheets.
Here is a screenshot of the workbook after its loaded and the code has run. As you can see I populate column B from the Internet then the graph updates.
Before you can do any of this you need to get the Office 2003 beta kit from
http://www.microsoft.com/office/preview/default.asp
Then you need the office add-on for Visual Studio.Net 2003
Here is the code showing how Excel is updated and also how I retrieve quotes from Nasdaq
-==-
About GetQuote
This piece of code simply returns the quote for a stock directly from NASDAQ. Because NASDAQ do not publish any web services or XML interfaces that I know of, I had to get the data the long way. That’s right I just open up a URL and then search through the raw html for the data I need. Now of course your thinking what happens when the web page changes, surely this code will not work? Normally you would be right but for many major websites the overall layout does not change and therefore the underlying html source code is pretty constant so you can write code to search through it and be pretty confident it will work. Actually I wrote that NASDAQ code 9 months ago and it worked up until a few weeks ago and then just needed a minor update.
So in this code I use a WebRequest class with a WebResponse class to request a page from a URL. I then create a new stream by using a GetResponseStream method of the WebResponse Class.
Once I have my stream encoded as ASCII I can then load the stream into a string variable using the ReadToEnd method of the StreamReader class. I can now search through my string variable using the IndexOf method. The rest of the code is simply chopping strings around to close in on the data I want to see.
That’s it! Now you know you can read data easily from the outside world directly into your spreadsheets opening up many possibilities. It would have been possible to write similar code in Excel VBA but it would have been significantly more difficult as it would have meant using WIN32 API calls.
John O’Donnell is a former Microsoft UK employee and MCSE who now lives and
works in the Chicago region. John published his first article on C#
programming on www.c-sharpcorner.com in May of 2001 and has enjoyed learning
this technology ever since. Today John works for MAS Consulting
(www.mcas.com), a Microsoft partner company where he is employed as a Senior
Technology Consultant specializing in Microsoft CRM. John is available for
consulting work across the USA.
