I have a web application that can group data on my table view. Also I can do Aggregate Functions on those groups. Now when I Export the data to Excel I call XlsFile.Subtotal() method for every group and every aggregate function for that group. If I have one group and one aggregate function it is OK, but if I call XlsFile.Subtotal() more than once, then it takes the rows that are SUBTOTALS and groups them too.
So if I have a group over column TYPE and do SUM and MIN functions the result is
A SUM
A MIN
A MIN SUM
Is there a way that I can avoid this?
Going row by row is not an option because of a huge data set.
Neither is manual work, it must be exported like it would be in Excel.
If I call the function SUBTOTAL in Excel, in Data tab, then it works perfectly.
Hi,
I am not sure I fully understood the use case, can you send me a small excel file with some data and tell me what rows you are subtotlling so I can get a better idea? (you can upload it here or send it to adrian@tmssoftware.com )
By the way, a note about this remark:
Going row by row is not an option because of a huge data set.
When you call SubTotal it goes row by row anyway, and it might even be slower than doing it manually because subtotal has to account for many possibilities you might not be. Specially if you are calling nested subtotals, I am sure going manually through all the rows will be faster.
Neither is manual work, it must be exported like it would be in Excel.
I understand the requirement, but I wanted to say that "subtotal" in Excel (or in FlexCel) isn't a real command. There is nothing saved as "Subtotal" in the file. What subtotal does is to go through all the rows in the range, search for similar values, add an outline, insert a row, add some bold "subtotal" labels, then insert -SubTotal() formulas. You can do it all manually and it will be impossible to know which one was done through Subtotal and which one manually. The saved file will be exactly the same.
We offer a Subtotal function because users like it, but internally it just groups, insert rows, adds outilnes and adds formulas. All of which you can do manually. It is just a convenience method, but if you are doing multiple subtotals, it might be faster to do it all in one pass by manually inserting the rows and formulas.