Talking to the Outside World in Excel 2003

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 datasources 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 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. Its Here 

Here is the code showing how excel is updated and also how I retrieve quotes from Nasdaq.

protected void ThisWorkbook_Open()
{
Excel.Worksheet ws=new Excel.WorksheetClass();
ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet;
string stock;
double stockval;
for(int i=2;i<8;i++)
{
Excel.Range rng=(Excel.Range)ws.Cells[i,1];
stock=rng.Value2.ToString();
stockval=GetQuote(stock);
ws.Cells[i,2]=stockval;
}
}
protected double GetQuote(string symbol)
{
string a;
string b;
string c="";
string d="";
string</span>query=http://quotes.nasdaq.com/Quote.dll?mode=stock&symbol=;
query=query+symbol+"&&quick.x=0&quick.y=0";
WebRequest wrq = WebRequest.Create(query);
// Return the response.
WebResponse wr= wrq.GetResponse();
StreamReader sr = new StreamReader(wr.GetResponseStream(),Encoding.ASCII);
a=sr.ReadToEnd().ToString();
if(a.IndexOf(symbol)>0)
{
int pos1=a.IndexOf("$&nbsp;");
//int pos2=a.IndexOf("]");
b=a.Substring(pos1,50);
c=b.Substring(7,b.Length-8);
int pos2=c.IndexOf("</td>");
d=c.Substring(0,pos2);
}
wr.Close();
sr.Close();
return(Convert.ToDouble(d));
}
}

Thats 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.