Aren't you sick and tired of having to use different programming languages for different projects? A few years back I spent nearly 3 years working on Microsofts Excel VBA support desk. Each day I would take calls from customers asking how to solve different problems using Microsoft Excel and VBA or Visual Basic for Applications.
If youre an Excel user you will know its a great tool for manipulating numeric data and lists. For example
One customer has a spreadsheet containing details of telephone pbxs and needed to alter some of the data on each line. Well normally you would update the data manually but in his case he had thousands of lines to update. Note to worry, a short Excel VBA macro later and he was happy. The other extreme was the customer who called from some government agency wondering if Excel could help him calculate ballistic trajectories.
The message here is that Excel is a very powerful tool and VBA just makes it even better. If you are a BASIC Programmer you may be wondering what VBA is? Well with Microsoft Office its an object orientated way of programming using objects.
These work like this
Object.Method.Property=whatever
Or rather you are supplied with a number of objects or classes, each of which has their own methods and properties. Really coding with VBA for me was very logical. You had an Application object with let you control the errr..Application!! Then you would have worksheet objects which had cells which could be populated!
All this was fine and cool but what if you wanted to do something like get some data from an outside source? It could be done but typically you would end up adding in declare statements for WIN32 APIs. In short as soon as you tried to do anything none Excel related you were back to using APIs which was a pain.
I guess Microsoft could have produced VBA.NET and given you all sorts of funky language add-ons but instead they have taken the reverse route and now given you the ability to code excel applications in C#.NET and VB.NET!
Why??
We are all? Excited about .NET and are busy coding award in C# or VB.NET when suddenly you get a command from the company president to build some business logic in Excel so he can manipulate some company financial data. Oh no you think its been months since you did any VBA. Well worry no more, you can now launch Visual Studio.NET and write your Excel code in C#!!!
Over time companies are going to end up having more and more .NET skilled developers and it makes sense to be able to use these skills wherever possible. Let me just say that VBA is an excellent tool but now I have transitioned to C# I do not want to have to use VBA if at all possible.
So here you go, a first Excel C# program! In this example I will simply populate the data in the Active Worksheet to give you a taste of whats possible.
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.
http://www.microsoft.com/downloads/details.aspx?FamilyID=9e0b1b7c-4ab5-40d2-b4d9-5817ab0bc1e5&DisplayLang=en
Once its all loaded up as per the instructions its time to code some Excel!!
Launch Visual Studio.Net 2003 and go new project and choose an Excel Workbook project like this
Next you will be prompted to create a new Excel Workbook for this project or use an existing one. I chose to create a new Excel workbook project like this.
Ok now that we have created the project you can just go ahead and build the project as normal. However when you run it you will just get Excel opening and nothing else happening. We need to add some code to make something useful happen.
For this project I have added code to the ThisWorkbook_Open method of my OfficeCodeBehind class. This code of course is run when I open the workbook.
Here are the guts of my code.
protected
void ThisWorkbook_Open()
{
Excel.Worksheet ws=new Excel.WorksheetClass();
ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet;
for(int i=1;i<11;i++)
{
for(int j=1;j<11;j++)
{
ws.Cells[i,j]=i;
}
}
}
You will notice that coding for Excel is not much different from coding other applications in C#. You have classes and methods and properties as always. You just have to learn a new set of classes specific to Microsoft Excel.
In the above code I have used the WorksheetClass and then retrieved the ActiveSheet or the one Excel is showing. Once that is done I can use Cells to specify the cell value by row and column.
In fact the only thing complicated in the code above is this line,
ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet;
Here I am using (Excel.Worksheet) to cast the object to a worksheet. Yes, for some reason .ActiveSheet Is not returning a worksheet object but is instead returning just an object so I have to cast it to another type or in this case a Worksheet object.
Okay so this code is built and ready to run so lets see the output!
Wow all that effort for so little reward! So every time you open this Workbook the code I have written will be run. In this case the output is pretty simplistic but you can imagine the same code could instead be connecting to some server on the other side of the world and pulling in last nights sales figures and populating the spreadsheet with them. The power of this tool is going to be the ability to create libraries of C# or VB.NET code and allow you to reuse them in Microsoft Office documents as well as more normal Window Applications.
In later articles on this subject well show a lot more cooler stuff you can do inside of Excel so keep checking back to http://www.c-sharpcorner.com