Saturday, April 11, 2009

Sum for the range of a column in Excel

Working on my current project, one requirement came out which made me all the more appreciative of django's nice array of built-in filters. I generated an Excel report for my clients which is generated from html. Good and nice. Then one of them said, hey can the Excel report have a row which outputs the sum of the whole column of say the column E? Something usual and easy in Excel but stumped me for a while of how to do it from html to excel. For the most part it was trying to figure out how to include that SUM formula at my html code.

A little visit to M$ support center got me the help I needed and then it was back to nice plethora of filters django has .... Turns out that you can include formula into a cell (or a table column in this case) by simply doing something like this '=SUM(E2:E70)' just after the opening tags for your column. This part actually took the longest for me as I did not really know how to do it correctly. My last row in this case was dynamic because it depended on the number of rows generated by my query. So all I did next was some little magicks like this ...

'=SUM(E2:E{{query.count|add:1}})', which takes the count of the results and adds one to it because Excel rows start from a 1 while the count function returns counts starting from row 0. Just something very simple. Don't really want to tax the brain during the weekends y'know ... anyway just a small thank you to django for really great tool box of useful built-in filters. You really made this post shorter than it ought to have been :).