How to get the row index of sub total 's row?

Aspose.Cells Product Family
1
Dear All.

How to get the row index of sub total 's row?

Please help

Thanks


2

Hi,


See the post in your other thread here for your reference:
https://forum.aspose.com/t/78205

I will paste the code segment here again for your reference, I used your template file you attached in your other thread here.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\aspose+cells+pls+help.xls”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//Set the Top and Bottom border styles with alignment setting
Style stotal = workbook.Styles[workbook.Styles.Add()];
stotal.Number = 4;
stotal.HorizontalAlignment = TextAlignmentType.Right;
stotal.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
stotal.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;

StyleFlag flag1;
flag1 = new StyleFlag();
flag1.NumberFormat = true;
flag1.Borders = true;
flag1.HorizontalAlignment = true;


//Find the Subtotal cell to get the row indexes.
int row1=0, row2=0;
FindOptions options = new FindOptions();
options.CaseSensitive = true;
options.LookAtType = LookAtType.EntireContent;
options.LookInType = LookInType.Values;
Cell foundCell = cells.Find(“2014 Total”, null, options);
if (foundCell != null)
{
row1 = foundCell.Row;
}


foundCell = cells.Find(“Grand Total”, null, options);
if (foundCell != null)
{
row2 = foundCell.Row;
}





//Now apply the top and bottom borders with number formattings and alignment to both entire rows.
cells.ApplyRowStyle(row1, stotal, flag1);
cells.ApplyRowStyle(row2, stotal, flag1);

workbook.Save(“e:\test2\topbottomborders2.xlsx”);

Note: If you want to find the cell with Subtotal() formula in it, you may do it easily, all you need to do is set the appropriate options for FindOptions, see the following code segment for your reference:
e.g
Sample code:

…
//Find the cell having “Subtotal” formula in it to get the row indexes.
int row1=0, row2=0;
FindOptions options = new FindOptions();
options.CaseSensitive = false;
options.LookAtType = LookAtType.Contains;
options.LookInType = LookInType.OnlyFormulas;
//find the cell having Subtotal formula in it.
Cell foundCell = cells.Find(“=SUBTOTAL(”, null, options);
…

Hope, this helps a bit.

Thank you.

3

But there are many subtotal…


I am asking this question because I want to put a formula value beside each subtotal… thats why I need to get each subtotal position…

By using snippet codes I cam only get the subtotal of 2014.

Are you clear now?


Please help …

Thanks a lot in advance
4

Hi Winanjaya,

Thanks for your posting and using Aspose.Cells.

Please see the workaround at the following post.

( Subtotal with formula )