Excel Add-ins
Each week, I normally submit a report to my management that contains details of people's utilization etc. I had an Excel sheet that connected to a SQL Store and populated a table in the sheet from which I had a pivot table. From this pivot table, I used to cut and paste data into my actual report.
It was tedious work and as the team size grew (more than 100), it was difficult to do this manually. Wanting to automate it, I looked at options like VSTO, Macros in Excel. Sometime back, I ran into this great tutorial put together by the Swiss MSDN team on Office Business Applications. You can download the tutorial here. Suffice to say that it was one of the best tutorials that I've ever read.
I quickly created a C# Excel Add-in that allowed me to get the data that I want from the database and then populate my template Excel. There was also a tutorial on creating Office Ribbons in the same document and in 2 minutes I had one up and running. Too cool!
Some excerpts of the code that I used:
|
// this code allows you to get a range and manipulate it as a whole
Excel.Range rng = Globals.ThisAddIn.Application.get_Range("LoggedHours", Type.Missing);
rng.Value2 = ""; |
|
// this snippet gets a value in a cell. Note how everything is treated as
// a range
string cellValue = (rng.get_Item(i, 1) as Excel.Range).Value2.ToString();
int key = Int16.Parse(cellValue.Length > 0 ? cellValue : "0"); |
I also managed to experiment with one other new feature in C#, which is extension methods. I had to mimic the Right function to get at the rightmost characters of a string and it was tedious to use SubString all the time. By implementing an extension method on the String datatype, it became a breeze. You can see how I'm using it here:
|
toDate = dtToDate.Value.Year.ToString() +
("0" + dtToDate.Value.Month.ToString()).Right(2) +
("0" + dtToDate.Value.Day.ToString()).Right(2); |
At the end, I'm happy now that what used to take me 30 or so minutes to complete now takes me 30 seconds!