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?
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 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.
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.
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.
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.
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?
We are composing Excel documents, from smaller parts, which can be defined by the user.
Example of such part looks like this:
to show how the borders are actually defined:
The #EB:FOREACH is our internal language. As result it takes this template:
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):
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.
Of course, this is just a simple example. We have much more complex situations in our documents…
