How can I add borders around a spreadsheet range?

Aspose.Cells Product Family
1
I want to add borders to a range. Based on some VB code here, I tried this:

Range range = locationWorksheet.Cells.CreateRange(7, 0, 93, 6);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue);
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Red);

...but it pushes most of the data down the sheet, as can be seen on the first two screen shots.

To see what the sheet looked like prior to adding these borders, see the third and final screen shot.

Actually, I want internal borders, too, not just around the edge, but first things first, I guess.

BTW, it also seemed like a very "expensive" operation - the report took much longer to generate with that borderizing code added.

NOTE: I updated the Stack Overflow version of this question, which is here.

I added a couple of more scream shots there, too.
2

Hi Clay,


Thank you for contacting Aspose support.

Please check the following piece of code to apply range’s outline borders as well as borders to individual cells in the range. Please note, the calls to apply the borders depend on the number of cells in the range. If there are too many cells, the operation may take more time.

By the way, I have tested the code segment against a sample of my own while using the latest version of Aspose.Cells for .NET 17.1.0, and I am not able to notice the problem related to the lost of existing formatting. In case you face any such difficulty, please share an executable simple console application along with input spreadsheet to demonstrate the said issue on our side.

C#

var book = new Workbook(dataDir + “book1.xlsx”);
var sheet = book.Worksheets[0];
var range = sheet.Cells.MaxDisplayRange;

//Setting border for each cell in the range
var style = book.CreateStyle();
style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Green);
style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Blue);
style.SetBorder(BorderType.TopBorder, CellBorderType.MediumDashed, Color.Black);

for (int r = range.FirstRow; r < range.RowCount; r++)
{
for (int c = range.FirstColumn; c < range.ColumnCount; c++)
{
Cell cell = sheet.Cells[r, c];
cell.SetStyle(style, new StyleFlag() { TopBorder = true, BottomBorder = true, LeftBorder = true, RightBorder = true });
}
}

//Setting outline border to range
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Blue);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Blue);
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Blue);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Red);

book.Save(dataDir + “output.xlsx”);