Borders can be set by programming, by calling APIs worksheet directly, also can be set by performing actions.
Border Styles
The following border styles are supported by current version of ReoGrid.
Border styles are defined as BorderLineStyle
enumeration.
Border Positions
Borders must be set around a range, even the range contains only one cell. To specify border positions, use BorderPositions
enumeration and its binary combined value.
Set borders
To set borders, use SetRangeBorders
method:
var sheet = reoGridControl.CurrentWorksheet; sheet.SetRangeBorders("B3:D7", BorderPositions.Outside, new RangeBorderStyle { Color = Graphics.SolidColor.Magenta, Style = BorderLineStyle.BoldSolid, });
Set borders by performing action
Setting borders by actions can be undone. For example, to set all dashed borders inside a range:
reoGridControl.DoAction(reoGridControl.CurrentWorksheet, new SetRangeBorderAction(new RangePosition(2, 1, 5, 3), BorderPositions.All, new RangeBorderStyle { Color = Graphics.SolidColor.Red, Style = BorderLineStyle.Dashed }) );
Get borders
To get borders from a range, use GetRangeBorders
method of worksheet.
RangeBorderInfoSet borderInfoSet = sheet.GetRangeBorders("A1:D5");
The method GetRangeBorders
filters all same borders and returns only one style instance. Check the property NonUniformPos
to see what positions of border in the range are not same.
Iterate Borders
To iterate over all borders in a specified range. use IterateBorders method of worksheet.
Define:
IterateBorders(RowOrColumn direction, RangePosition range, Func<int, int, int, RangeBorderStyle, bool> iterator);
where
direction
determines the iterating mode:RowOrColumn.Row
– iterates over all horizontal bordersRowOrColumn.Column
– iterates over all vertical bordersRowOrColumn.Both
– iterates over all borders (both Row and Column)
range
is the target range to be iteratediterator
is a callback function that is provided by user code to scan all borders returned from worksheet, which has following arguments:int
Row – the number of row where border existsint
Column – the number of column where border existsint
Span – how many borders exist with same styles from this positionRangeBorderStyle
Border information – contains the color and style information of borderbool
return value – return true to continue iterating; return false to abort
For example, iterate over all horizontal borders and markup cells as red background if the position of the cell has borders.
// set borders worksheet.Ranges["C3:F10"].BorderInsideHorizontal = RangeBorderStyle.BlackSolid; // iterate borders worksheet.IterateBorders(RowOrColumn.Row, RangePosition.EntireRange, (r, c, span, border) => { worksheet.Cells[r, c].Style.BackColor = Color.LightCoral; return true; });
Result:
Iterate over all vertical borders:
// set borders worksheet.Ranges["C3:F10"].BorderInsideVertical = RangeBorderStyle.BlackSolid; // iterate borders worksheet.IterateBorders(RowOrColumn.Column, RangePosition.EntireRange, (r, c, span, border) => { worksheet.Cells[r, c].Style.BackColor = Color.LightCoral; return true; });
Result:
Iterate over all borders, both Row and Column borders:
// set borders worksheet.Ranges["C3:F10"].BorderOutside = RangeBorderStyle.BlackBoldSolid; worksheet.Ranges["C3:F10"].BorderInsideHorizontal = RangeBorderStyle.BlackSolid; worksheet.Ranges["C3:F10"].BorderInsideVertical = RangeBorderStyle.BlackDotted; // iterate borders worksheet.IterateBorders(RowOrColumn.Both, RangePosition.EntireRange, (r, c, span, border) => { worksheet.Cells[r, c].Style.BackColor = Color.LightCoral; return true; });
Result:
See also: Iterate Cells
Remove borders
To remove borders from worksheet, use RemoveRangeBorders
method.
sheet.RemoveRangeBorders(new RangePosition(2, 1, 5, 1), ReoGridBorderPos.All);
Setting border’s style to RangeBorderStyle.Empty
is also possible to remove borders:
sheet.SetRangeBorders(2, 1, 5, 3, ReoGridBorderPos.All, RangeBorderStyle.Empty);
Remove borders by doing action
Removing borders by action can be undone, for example:
reoGridControl.DoAction(reoGridControl.CurrentWorksheet, new RemoveRangeBorderAction(new RangePosition(2, 1, 5, 1), ReoGridBorderPos.All));
To undo this removing operation:
reoGridControl.Undo();
Excel support
ReoGrid support reading and saving all borders with styles from or to Excel file.
Demo project
Example code about using borders is available in demo project.
How do I iterate over all the RangeBorders in a given Worksheet?
Thanks
JK
Currently there is no method that can iterate over all borders from a range. Iterating over all borders in a worksheet will be very slow, instead please consider to use
GetRangeBorders
method of worksheet.The
GetRangeBorders
method returns an object that has been filtered out from same borders in a range, for example, if all rows have same bottom-border styles, the return value ofGetRangeBorders
contains only one bottom-border style, otherwise the propertyNonUniformPos
with valueBorderPositions.Bottom
indicates that bottom-border styles are not uniform.So there might be a complex logic to get all borders from worksheet but it is more efficient. Please refer to source code in
Editor\PropertyPages\BorderPage.cs
for more example about getting borders. Do you want to check all borders one by one in a worksheet?Edit 1: We consider to add a method like
IterateBorders
from next version. The method skips borders with same styles by returning aspan
value. We see this method will bring performance problem but it should not be too bad.Edit 2:
IterateBorders
is supported from 0.8.9.3 version.Thanks. Let me work through your description and the logic of it in my head.
Basically, what I am trying to do is store the worksheet configuration in tables in a database. I’m currently iterating through the cells which gives me contents, style and merged ranges. If I can store the border settings then that’s most of it.
You could also consider to save the worksheet in RGF format, which contains all worksheet content, including cells data, styles, formulas, borders, data formats and cell body types. It could be saved and loaded from any stream as well as be stored in database (with a ntext:string or varbinary:byte-array column type). See https://reogrid.net/document/rgf-format
For my app, its better I store it in the database so all the data is contained in one place.
But is the code for the RGF writer available? I can probably get everything I need from it….
I should add that the reason I cant just BLOB the RGF is because there are special fields in my app that get stored differently. Basically all fields that are user input get stored elsewhere.
OK. Please try the
IterateBorders
method from next version, it will available within 3 days.Wow okay 🙂 Thats fast!
Thanks for your donation! Very helpful!
QUick question: What do the fields in the callback function from IterateBorders mean?
Thanks
This document has been updated, please check the section ‘Iterate Borders’.