Search code examples
excelborderasposeaspose-cells

How can I add borders around a spreadsheet range (Aspose Cells)?

I want to add borders to a range on a spreadsheet. 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 here:

enter image description here

...and here:

enter image description here

This is what the sheet looked like prior to adding those borders.

enter image description here

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

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

UPDATE

I was able to get it to work better, but it's still messing up my formatting. With this code:

private void BorderizeDataPortionOfLocationSheet()
{
    int FUDGE_FACTOR = 5;
    int rowsUsed = locationWorksheet.Cells.Rows.Count + FUDGE_FACTOR;
    int colsUsed = locationWorksheet.Cells.Columns.Count; //QTY_COL; // last column
    string rangeBegin = RoboReporterConstsAndUtils.GetRangeLettersNumbersAsStr(1, 8);
    string rangeEnd = RoboReporterConstsAndUtils.GetRangeLettersNumbersAsStr(6, rowsUsed);
    Range entireSheetRange = locationWorksheet.Cells.CreateRange(rangeBegin, rangeEnd);

    CellsFactory cf = new CellsFactory();
    Style style = cf.CreateStyle();
    entireSheetRange.SetStyle(style);
    entireSheetRange.SetOutlineBorders(CellBorderType.Thin, Color.Black);
}

...I am getting a border that doesn't shove the data down the sheet:

enter image description here

But it 86ed my beautiful formatting, which you can see here, when the range was borderless:

enter image description here

How can I get my border and retain my formatting, too?

Solution

  • Your code to apply the outline borders to a range is correct as I have tested it against the latest version of Aspose.Cells for .NET 17.1.0 (available via NuGet and Aspose download section). Please note, setting the outline borders should not disturb the existing formatting of the cells because the Range.SetOutlineBorder operates only on the borders, however, if you wish to apply the border to each individual cell in the range, the existing formatting can be overwritten.

    I am going to post the sample code along with the input & output spreadsheets on your thread created in Aspose.Cells support forum, and I humbly request you to share your input spreadsheet along with executable piece of code in Aspose.Cells support forum for further investigation in case the problem persists. 

    var book = new Workbook(dataDir + "book1.xlsx");
var sheet = book.Worksheets[0];
var range = sheet.Cells.MaxDisplayRange;
//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");

    Note: I am working as Developer Evangelist at Aspose.