Using SpreadsheetML to generate rich reports of timesheet data
Happy New Year everyone. I hope everyone was able to enjoy the holiday season. I took a couple weeks off in case you were wondering why there weren't any new blog posts.
While I took some time off, I did still get a chance to play around with the file formats (I think I have a serious problem with proper use of vacation). We have a small architectural business in the family and I decided to go in and help out with a few of their IT projects. One problem, was that the employees had just been printing out time sheets and submitting them as paper copies and it was a mess to sort out when it came time to bill clients, or check on the budgets for various projects. So I took the asp.net time tracker starter kit and used it as a template for building a pretty basic time tracking system.
It was fun to just play around with for a few days, and see how we could come up with a better way of tracking the work for the various projects. Rather than play around with the reporting side of it too much though I figured I would take advantage of Excel's data analysis tool. So, I started with this example up on OpenXMLDeveloper.org (https://openxmldeveloper.org/articles/Table_to_Spreadsheet_by_Vijayeta.aspx) to allow me to generate rich Excel spreadsheets based on the data in the time tracking system. I could generate pivot tables, charts, etc. that help give a great overview of how much time has been spent per project/category/employee/time range.
It was super easy. I first started in Excel, and played around with a boilerplate table of data and built up a few charts and pivot tables. Then I took that workbook and put it up on the server. Whenever someone requests a report they specify the filters (project, date range, etc.) and my solution quickly grabs that data from the database and transform it into spreadsheetML. I then replace the boilerplate table with the new spreadsheetML, and when they open the workbook the charts and pivot tables display that new data. I had to do a few additional things like specify the range that the charts and pivot tables applied to since the number of rows can change based on the query, but that was pretty basic (and I think if I used the new table functionality in Excel 2007, that wouldn't have been necessary as I could just specify the table name rather than the cell range). Everyone in the office was using Excel 2003, but since the compatibility pack is already freely available, in no time everyone was able to open and view these spreadsheets that were being automatically generated on the server. It was really easy, and really cool.
-Brian