Hi
I have a excel template and am using
worksheet.Cells[RowCnt , ColCnt].PutValue(12)
to insert values into each cells.I have one "Subtotal" formula in my Template like
=SUBTOTAL(9,A3:A1048576) in my A1 cell.Its working fine.But my problem is in my template i given till last row number in the subtotal formula,because i don't know the number of rows at first time.Because of this Subtotal formula my template size is more.So my requrement is instead of giving last row number,at the time of looping my dataset how to auto increment my subtotal formula,that is
while creating the template i will create the subtotal like "=SUBTOTAL(9,A3:A3)" and when am looping my dataset i need to increment subtotal like "=SUBTOTAL(9,A3:A4)" ,"=SUBTOTAL(9,A3:A5)".One important thing is if subtotal formula is there in A1 cell,then only i should do all these things.I know how to find ,there is a formula in cell or not,worksheet.Cells[RowCnt , ColCnt].IsFormula,but how to find that formula is a Subtotal formula
So my Requirement is if there is a subtotal in particular "column",just auto increment that subtotal formula in each loop of my dataset
Am attaching my Sample template and please check where and all i kept Subtotal formula
I added Comments on subtotal
Regards
Ajeesh M J
Hi,
AjeeshJohn:=SUBTOTAL(9,A3:A1048576) in my A1 cell.Its working fine.But my problem is in my template i given till last row number in the subtotal formula,because i don't know the number of rows at first time.Because of this Subtotal formula my template size is more.So my requrement is instead of giving last row number,at the time of looping my dataset how to auto increment my subtotal formula,that is
while creating the template i will create the subtotal like "=SUBTOTAL(9,A3:A3)" and when am looping my dataset i need to increment subtotal like "=SUBTOTAL(9,A3:A4)" ,"=SUBTOTAL(9,A3:A5)".
Well, for your custom needs, you have to do it by yourself by using your own code, there is no automatic way either in MS Excel or in Aspose.Cells APIs to cope with it. If you know any option to do the job in Ms Excel, let us know and we will guide you how to do it by Aspose.Cells. Moreover, Aspose.Cells does have SharedFormula feature but it won't help for your cause precisely, see the topic:
http://www.aspose.com/docs/display/cellsnet/Setting+Shared+Formula
If you follow shared formula approach using Aspose.Cells, then your SubTotal formula in the cells would increase like following which I don't think it will suit your needs:
A1: =SUBTOTAL(9,A3:A3
A2: =SUBTOTAL(9,A4:A4
A3: =SUBTOTAL(9,A5:A5
In short, you have to use your own code to do the custom increment for formulas for your needs.
AjeeshJohn: One important thing is if subtotal formula is there in A1 cell,then only i should do all these things.I know how to find ,there is a formula in cell or not,worksheet.Cells[RowCnt , ColCnt].IsFormula,but how to find that formula is a Subtotal formula
So my Requirement is if there is a subtotal in particular "column",just auto increment that subtotal formula in each loop of my dataset
Yes, you may find out Subtotal formula in the formula cells using the Find or Search options provided by Aspose.Cells APIs, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data
Thank you.