Applying Borders to a cell above the merged cell

Aspose.Cells Product Family
1

Requirements:


Applying borders to a set of cells.


Problem:
We have been using the feature of applying borders. We have noticed a problem while applying borders for the cells which have a merged cell in the bottom.

We are applying borders cell by cell, starting with top, bottom, left and right. However the bottom border for this cell is applied for the entire top border of that merged cell.

Attached is a sample copy of the same.

Can you please help us fix this issue, we expect it be behave exactly like Excel…

Thanks & Regards,
Anil



2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.3.2.3
and see if it resolves your issue.

I have tested your issue with the following sample code using the sample.xlsx file which I have created manually using your given source xlsx file and applied bottom borders to cells F4 till K4 and it has not affected the merged cell’s top border below.

I have also attached the source file and output file as well as screenshot for your reference.

If your issue is still unresolved, please provide us your sample code/project replicating this issue, we will soon look into it and resolve this issue.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Sample.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


//Apply bottom borders from cell F4 till K4

for (int col = 5; col <= 10; col++)

{

Cell cell = worksheet.Cells[3, col];

Style style = cell.GetStyle();

style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

style.Borders[BorderType.BottomBorder].Color = Color.Red;

cell.SetStyle(style);

}


workbook.Save(“output.xlsx”, SaveFormat.Xlsx);


Screenshot:
3
Thanks for your kind assistance. But somehow, its still not working for me...

Attached all the results and code herewith.

Thanks & Regards,
Anil
4

Hi,

Thanks for your posting and using Aspose.Cells.

Your code looks ok to me, however, I am not able to run it at my end and could check it.

There might be some logical errors in your code and because of that your relevant code where you are setting the borders might not be executing.

Could you please provide us your runnable sample project replicating your issue alongwith source xls/xlsx files being used in your code?

It will help us look into your issue precisely and we will be able to help/advise you.

5

Code Required:

You should now be good to go with the code provided below.

The source xls is the one which I have already provided minus the borders applied…

[Flags]
public enum CellBorderType
{
None = 0,
Left = 2,
Right = 4,
Top = 8,
Bottom = 16,
}
}

///
///
///
///
///
///
///
///
///
///
///
///
private void SetBorders(Aspose.Cells.Workbook workBook, int startRow, int startColumn, int endRow, int endColumn, string sheetName, string color, int value, bool applyBorders)
{

startRow–;
endRow–;
startColumn–;
endColumn–;

if (endRow < startRow || endColumn < startColumn)
return;

if (string.IsNullOrEmpty(sheetName))
sheetName = workBook.Worksheets[workBook.Worksheets.ActiveSheetIndex].Name;

CellBorderType type = CellBorderType.None;

if (value == 1)
{
type = CellBorderType.Left;
}
else if (value == 2)
{
type = CellBorderType.Right;
}
else if (value == 3)
{
type = CellBorderType.Top;
}
else if (value == 4)
{
type = CellBorderType.Bottom;
}
else if (value == 5)
{
type = CellBorderType.Top | CellBorderType.Bottom;
}
else if (value == 7)
{
type = CellBorderType.Left | CellBorderType.Right;
}
else if (value == 8)
{
type = CellBorderType.Top | CellBorderType.Left
| CellBorderType.Right | CellBorderType.Bottom;
}


//Color borderColor = GetColorFromString(color);
//if (borderColor == Color.Empty)
// borderColor = Color.Black;

Color borderColor = Color.Black;

Aspose.Cells.Cell cell = null;
for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++)
{
for (int colIndex = startColumn; colIndex <= endColumn; colIndex++)
{
cell = workBook.Worksheets[sheetName].Cells[rowIndex, colIndex];

Aspose.Cells.Style s = cell.GetStyle();

if ((type & CellBorderType.Top) == CellBorderType.Top)
{
if (applyBorders)
{
s.Borders[Aspose.Cells.BorderType.TopBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
}
else
{
s.Borders[Aspose.Cells.BorderType.TopBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.None;
}
}
if ((type & CellBorderType.Bottom) == CellBorderType.Bottom)
{
if (applyBorders)
{
s.Borders[Aspose.Cells.BorderType.BottomBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
}
else
{
s.Borders[Aspose.Cells.BorderType.BottomBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.None;
}
}

if ((type & CellBorderType.Left) == CellBorderType.Left)
{
if (applyBorders)
{
s.Borders[Aspose.Cells.BorderType.LeftBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
}
else
{
s.Borders[Aspose.Cells.BorderType.LeftBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.None;
}
}
if ((type & CellBorderType.Right) == CellBorderType.Right)
{
if (applyBorders)
{
s.Borders[Aspose.Cells.BorderType.RightBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
}
else
{
s.Borders[Aspose.Cells.BorderType.RightBorder].Color = borderColor;
s.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.None;
}
}
cell.SetStyle(s);
}
}

}


Please do let me know if you need any more information.

Thanks & Regards,
Anil