facebooktwittermenuarrow-up

GemBox Support Forum

Allow different borders of bordering cells

GemBox.Spreadsheet
#1

Hello,

if you run following code:

var file = new ExcelFile();
var sheet = file.Worksheets.Add("Sheet1");
var black = SpreadsheetColor.FromName(ColorName.Black); 
sheet.Cells[0, 0].Style.Borders.SetBorders(MultipleBorders.Outside, black, LineStyle.Medium);
sheet.Cells[2, 0].Style.Borders.SetBorders(MultipleBorders.Outside, black, LineStyle.Medium);
sheet.Cells[1, 2].Style.Borders.SetBorders(MultipleBorders.Outside, black, LineStyle.Thin);

var sourceRange = sheet.Cells.GetSubrangeAbsolute(1, 2, 1, 2);
sourceRange.CopyTo(sheet, 1, 0);

as result there is a thin line between cells A1 and A2. But if you remove last two lines of code and copy cell C2 to A2 manually in Excel then the border between A1 and A2 is a thick line.

In other words in Excel each of cells A1,A2,A3 still keeps its formatting. (Just copy them to D1,E2 and F3 and you will see, what I mean).

Would it be possible to have the same behaviour in Gembox too?

Thank you very much for help

Otakar Milink

#2

Hi Otakar,

No, I’m afraid that for now, this is not possible.
You see, GemBox.Spreadsheet has borders resolution built within so when specifying borders on some cell(s), the neighboring borders will be automatically resolved and the same behavior is used when copying cells as well.

However, MS Excel has a different behavior when setting borders on cells and when copying cells with borders. You can notice this by setting the borders of cell “A2”, instead of copying cell “C2”. You will get the same result that you now get with GemBox.Spreadsheet.

Note, GemBox.Spreadsheet is not a GUI component, so having a different behavior for setting borders on cells and copying cells with borders could very likely be misleading and unexpected.

Anyway, when MS Excel copies a cell it will internally keep the defined borders on all cells (copied cell and its neighboring cells).
But it will display the borders based on their resolution, the larger borders will be shown, while the smaller borders will be hidden.

You can notice this by switching the borders. If you set “A1” and “A3” to LineStyle.Thin and “C2” to LineStyle.Medium. Then when copying “C2” to “A2” with MS Excel, you’ll notice that larger borders on “A2” are now shown as overriding the borders on “A1” and “A3”.

What’s even worse is that different Excel applications have different behavior. For instance, LibreOffice Calc and OpenOffice Calc will always keep internally the borders, so both copying and direct setting of cell borders will behave the same.

I hope this information is of any help to you.

Regards,
Mario

#3

Hi Mario,

thanks a lot for the super-fast response.

I agree with your observations of Excel behaviour.

I am not sure if Gembox should follow Excel or LibreOffice way, but there should definitely be a way how to set different borders for such bordering cells.
Maybe there could be an optional parameter “clear opposite border” ?

And also at the moment Gembox “destroys” this information, when loading such existing xlsx file. In such case bottom border from upper cell is “moved” to top border of lower cell.
I think this is not right.

What do you think?

Otakar

#4

Hi,

No, that is basically the same reason, GemBox.Spreadsheet will always resolve borders no matter what, even when loading.

In short, to handle your requirement GemBox.Spreadsheet needs to stop resolving borders.
Currently, I don’t have a clear vision as to what other users might expect or want so for now, it’s best to postpone making any breaking changes until more information is gathered.

Regards,
Mario

#5

IMHO if the file format supports different borders, then a library for processing this file format should support it too.

I understand that this could be a breaking change, so maybe it could be optional.

ExcelFile.ResolveBorders = true/false ?

with default to true…

Ota

#6

I’m not sure what you mean by that… you can specify whatever borders you want on the cells.
We’re discussing here a specific use case, copying, which has different behavior in different Excel applications and thus results in different outcomes (depending on what application, tool, or utility library is being used).

Anyway, there are numerous things in the file formats that are better left hidden or abstracted, for the sake of clarity and simplicity.

Last, as mentioned before, this is currently not supported and I’m afraid it won’t be available in the near future.

Regards,
Mario

#7

I thought, that if I make something like this in Excel VBA:

ActiveSheet.Cells(1, 1).Borders(xlEdgeBottom).Weight = xlThin
ActiveSheet.Cells(2, 1).Borders(xlEdgeTop).Weight = xlThick

each cell will keep its borders, but they do not. Setting top border of lower cell resets the bottom border of upper one. In this case it make sense, that Gembox behaves in the same way and I have to find another way for me.

Thanks a lot for all the discussion.

Ota

#8

Hi,

If it’s not too much trouble, can you tell us why you need this?
I would like to understand what your use case is, what requirement you have that needs this?

Regards,
Mario

#9

Of course.

We are composing Excel documents, from smaller parts, which can be defined by the user.
Example of such part looks like this:

obrazek

to show how the borders are actually defined:

obrazek

The #EB:FOREACH is our internal language. As result it takes this template:

obrazek

and insert it multiple times at this position.

Expected result looks like this (and this is how it looks when I make it manually in Excel):
obrazek

When I use CopyRange from GemBox library I get this result.
Notice the thin border between header and first “data” row and also between the last data and the footer.
obrazek

Of course, this is just a simple example. We have much more complex situations in our documents…

Ota

1 Like