Set bold and Top and Bottom Bordered to the Subtotal value

Aspose.Cells Product Family
1
Dear All,

Evaluating Aspose Cells 8.0.2.0
I am trying to set BOLD and Top and Bottom Bordered to the Subtotal value using codes below but no luck :(,.. what I missed?



Style stotal = new Style();
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;

cells.ApplyColumnStyle(2, stotal, flag1);
cells.ApplyColumnStyle(3, stotal, flag1);
cells.ApplyColumnStyle(4, stotal, flag1);
cells.ApplyColumnStyle(5, stotal, flag1);
cells.ApplyColumnStyle(6, stotal, flag1);
cells.ApplyColumnStyle(7, stotal, flag1);
cells.ApplyColumnStyle(8, stotal, flag1);
cells.ApplyColumnStyle(9, stotal, flag1);



2

Hi,


Well, it is working absolutely fine here as I tested. Please make sure to set the relevant StyleFlag options on so your code could work fine accordingly. I used the following sample code, it works fine. I have also attached the output file for your reference. Please update your code in accordance with it.
e.g
Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
cells[“C2”].PutValue(1234);

Style stotal = new Style();
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;

cells.ApplyColumnStyle(2, stotal, flag1);

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

Hope, this helps you a bit.

Thank you.
3
Hi
but what I need is only to set top and bottom bordered to the Subtotal and Grand Total Row...

C18, D18, ...
C19, D19...

please help

thnaks
4

Hi,


I have written a sample code to accomplish your task for your requirements for your reference. Please refer to the code segment and adjust your codes accordingly for your needs.
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 Total 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”);


Thank you.
5

Hi


Thanks… its good idea…
So if I know the row and column then how to set the style on that specified location?

Thanks & Regards
6

Hi,


Sure, you should find out the Subtotal row indexes first and then apply your desired borders and other formattings to those rows only as per my sample code (please refer to it).

Let us know if I can be of any further help.

Thank you.
7

so, could you please give me an example for doing this?..


thanks a lot in advance

regards
Winanjaya
8

Hi Winanjaya,

Thanks for your posting and using Aspose.Cells.

If you know the row or column and you want to apply style, then you can use the Row.ApplyStyle() or Column.ApplyStyle() method.

Amjad has given you the example on this post: 547559.

Please check these lines.

//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);

Here, he is using Cells.ApplyRowStyle() but you can also use the Row.ApplyStyle() to get the same effect.