Borders for merged cells

Aspose.Cells Product Family
1

Hi,
how can we apply borders to merged cells?
can we apply border for multiple cells at a time, which not merged.
something like, i wan to apply border from cell no D3 to G10 like this, how can we do this?

Thanks & Regards
Sankar V

2

Hi,

See the following sample code (how to apply borders to merged cell(s)) for your reference:
Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
//Merge cells B2:E8
sheet.Cells.Merge(1, 1, 7, 4);
//Apply borders to merged cell (B2)
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Blue);
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Blue);
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.Blue);
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Blue);
workbook.Save(“e:\test\output.xls”);


Thank you.

3

Hi,
with this code i’m able to get the border, but with border i need some style also.
i need to fill a color to this merged cells, and the text is aligned to center like this.

how can i do all these in a single shot?

Please find the attachment.
the upper image is the one, which i’m getting. i need it should be like the bottom one.


Thanks & Regards
Sankar V

4

Hi,

Yes, you may do it with ease, see the updated code:
Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
//Merge cells B2:E8
sheet.Cells.Merge(1, 1, 7, 4);

Cells cells = sheet.Cells;
cells[“B2”].PutValue(“This is test”);
Style style = workbook.Styles[workbook.Styles.Add()];
style.ForegroundColor = Color.Gray;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Center;
style.VerticalAlignment = TextAlignmentType.Center;
cells[“B2”].SetStyle(style);

//Apply borders to merged cell (B2)
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Blue);
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Blue);
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.Blue);
sheet.Cells[1, 1].GetMergedRange().SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Blue);

workbook.Save(“e:\test\output.xls”);


Thank you.

5

Hi,
this is ok.
is it possible to apply the borders for cells with out merging.

In the above example after merging we are applying the borders.
i know that by using style we can give the borders, but how can we put these styles to the large number of individual cells which are not merged. Like E7 to H10.

Thanks & Regards
Sankar V

6

Hi,

Yes quite possible, see the example below:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;


//apply style to a range of cells.
Range range = cells.CreateRange(“E7”, “H10”);
Style stl = workbook.Styles[workbook.Styles.Add()];
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
StyleFlag flg = new StyleFlag();
flg.Borders = true;

range.ApplyStyle(stl, flg);

workbook.Save(“e:\test\rangeborders.xls”);

Thank you.

7

Hi,
How can we apply the same (border for cells using range) in multiple sheets?

Thanks & Regards
Sankar V

8

Hi,

Well, this is easy, you may add worksheets to the workbook and access the worksheets in the workbook. You can repeat the operation (apply borders to range of cells) for multiple sheets in the workbook. See the following example code:
Workbook workbook = new Workbook();
workbook.Worksheets.Clear();
int index;
for (int i = 0; i < 5; i++)
{
index = workbook.Worksheets.Add();
Worksheet sheet = workbook.Worksheets[index];

Cells cells = sheet.Cells;

//apply style to a range of cells.
Range range = cells.CreateRange(“E7”, “H10”);
Style stl = workbook.Styles[workbook.Styles.Add()];
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
StyleFlag flg = new StyleFlag();
flg.Borders = true;

range.ApplyStyle(stl, flg);
}
workbook.Save(“e:\test\multi_rangeborders.xls”);


Thank you.