This project has moved. For the latest updates, please go here.

Subtotals

Mar 23, 2011 at 2:59 PM

Is there any way to do subtotals?  This would be very useful for me, as we have a number of spreadsheets that need to be subtotaled and right now I use macros.  Is there a way to do it with formulas?  Thanks!

Coordinator
Mar 23, 2011 at 4:21 PM

Sure, you can use:

  1. Tables. The documentation page "Using Tables" has examples of subtotals (http://closedxml.codeplex.com/wikipage?title=Using%20Tables)
  2. Straight formulas. The documentation page "Using Formulas" (http://closedxml.codeplex.com/wikipage?title=Using%20Formulas) doesn't have an explicit example of subtotals but you can just add the formula manually, like "SUM(A1:A10)". Even better, if you have defined a range with the data you can use something like:
    • ws.Cell("A1").FormulaA1 = "SUM(" + dataRange.RangeAddress.ToString() + ")";
    • ws.Cell("A1").FormulaA1 = "SUM(" + dataRange.Column(1).RangeAddress.ToString() + ")";
    • You can also set a formula of an entire range of cells in one shot, like:
    • dataGrid.LastColumn().FormulaR1C1 = String.Format("SUM(RC[-{0}]:RC[-1])", dataGrid.ColumnCount() - 1);
Mar 23, 2011 at 4:55 PM

Thanks for the quick reply.  Doing the formula with ranges may work, but it would be pretty cumbersome.  What I'm looking for is something like a printed report - it would break and print totals on changes in the data.  For example, if the data is sorted by date, then you would have a subtotal of the amount for each change in date and a grand total at the end.  You can do this in Excel using Data/Subtotal, but I've never seen it done programatically - except through a macro.

I can always continue to use macros, but I was hoping there would be a way to automate it during the creation of the spreadsheet without too much effort.  I'll have to play with the ranges and see how difficult it would be and what kind of performance impact it would have.

Coordinator
Mar 23, 2011 at 5:02 PM

Can you upload a sample file so I know exactly what end result you're looking for?

Mar 23, 2011 at 5:35 PM

I'm not sure how to upload a file, but here's an example.  Let me know if that helps.   I think, using the examples of formulas and ranges you gave above, I could make it work for the specific spreadsheet I'm working on.  The problem is, I'd have to figure out how to do it for each spreadsheet that uses subtotals.  I'd like an easy way to define what field to break on and which columns to add or count or whatever for the subtotal.

Name Date Amount
Joe Blow 2-Mar-2011 50.00
Slim Jim 2-Mar-2011 45.00
Bobby Boy 2-Mar-2011 50.00

2-Mar-2011 Total 145.00
Joe Blow 5-Mar-2011 30.00
Bobby Boy 5-Mar-2011 25.00

5-Mar-2011 Total 55.00
Someone Else 7-Mar-2011 50.00
Still Another 7-Mar-2011 25.00

7-Mar-2011 Total 75.00

Grand Total 275.00
Coordinator
Mar 23, 2011 at 5:57 PM

Now I get it.

That kind of grouping is made by the Excel application. It would be a very nice addition to this library but right now I have a backlog of features to implement and bugs to fix. I'll implement it eventually but for now you'll have to code it. Sorry.

-Manuel

Mar 23, 2011 at 6:07 PM

Thanks.  I was afraid it wasn't available.  As long as it's on your list I'll be patient and just code by hand for now.

Mar 30, 2012 at 2:01 PM

Hi Manuel - any idea if you'll ever get around to working on subtotals.  I've got a couple of projects coming up and it would be really nice to have.  I thought I would ask again, since it's been about a year.  I certainly understand if it's not on the list yet - I know there are always lots of things to do.

-Joe

Coordinator
Mar 30, 2012 at 3:35 PM

I'm going to copy this to the issues so it doesn't get lost but I can't work on it for a while. I wish I had more time to work on this but real life keeps getting in the way.

Sorry.

Coordinator
Mar 30, 2012 at 3:36 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Mar 30, 2012 at 5:11 PM

I understand completely.  Real life is so inconvenient.  Thanks for putting it on the list.