Merged Cell Border Problems

Aspose.Cells Product Family
1

I"m creating a spreadsheet with a lot of merged cells that need to have borders. When I set the border, it only appears on the first (upper left) cell in the merged area. Here’s code that demonstrates this:

Dim oCell As Cell = xl.Worksheets(0).Cells(5, 5)

oCell.Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium

oCell.Style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Medium

oCell.Style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Medium

oCell.Style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Medium

xl.Worksheets(0).Cells.Merge(5, 5, 3, 3)

I’m using version 2.9.6.1

Thanks,
Ethan

2

Hi Ethan,

You should set all merged cells’ borders. The following is the sample code:

Dim rng As Range = xl.Worksheets(0).Cells.CreateRange(5,5,3,3)
rng.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Black)
rng.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black)
rng.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.Black)
rng.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black)

xl.Worksheets(0).Cells.Merge(5, 5, 3, 3)

3

@eyoung,
Aspose.Excel is discarded and a new product Aspose.Cells has replaced it. This new product contains all the advanced features to format cells including setting borders on a range and merging cells as well. The following example demonstrates this feature where border is set for a range of cells and later those cells are merged. It can be seen that merged cells contain proper border in the saved Excel file.

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the first (default) worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];

// Adding some value to the "A1" cell
cell.PutValue("Hello World From Aspose");

// Creating a range of cells starting from "A1" cell to 3rd column in a row
Range range = worksheet.Cells.CreateRange(0, 0, 1, 3);

// Adding a thick top border with blue line
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue);

// Adding a thick bottom border with blue line
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue);

// Adding a thick left border with blue line
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue);

// Adding a thick right border with blue line
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Blue);

//Merge first three columns in the first row
worksheet.Cells.Merge(0, 0, 1, 3);

// Saving the Excel file
workbook.Save("MergedCellsBorder.xlsx");

MergedCellsBorder
MergedCellsBorder1098×550 80.4 KB

For more information on Cells Formatting refer to the following document:
Cells Formatting

You may download the latest free trial version of this product here:
Aspose.Cells for .NET (Latest Version)

A ready to run solution is available here that can be used to test the product features with minimum effort.