How to apply outline table border to a cell range using OpenXml?

Question

I set border style for each cell. But if you look closely, you'll see the blue lines are not consecutive. There are some white hash marks across the vertical lines. Because inside the table, I set the top and bottom cell border as white. Anyone knows how to avoid this?

WorksheetPart v_worksheetPart = a_workbookPart.AddNewPart<WorksheetPart>();
v_worksheetPart.Worksheet = new Worksheet();
SheetData v_sheetData = new SheetData();
Row v_Row = new Row();
Cell v_Cell = new Cell();
...
v_Row.Append(v_Cell);
v_sheetData.Append(v_Row);
v_worksheetPart.Worksheet.AppendChild(v_sheetData);
...

score 4 · Answer 1 · answered Sep 12 '17 at 18:26

4

Just call

ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);

answered Sep 12 '17 at 18:26

napi15

2,354
2
31
55

Hi, can you let me know what object 'ws' is. Looks like OpenXml.Spreadsheet.WorkSheet does not contain an attribute 'Cells'. – Joyin Sep 12 '17 at 18:30
@Joyin ws is your spreadcheat , yes it actually contains ....what version of openXML are you using? do you mind posting a bit of your code? I could much help you if I can see the structure of what you are doing – napi15 Sep 12 '17 at 18:32
I'm using V2.5. Let me try to post some of my code. – Joyin Sep 12 '17 at 18:41
@Joyin Thanks for the edit , I will get back at you soon so If I understand you want to remove the weird formatting? Yes your code helps – napi15 Sep 12 '17 at 18:49
I posted some of my code. Not sure if it helps. Thank you very much for your help in advance! – Joyin Sep 12 '17 at 18:49

score 2 · Answer 2 · answered Sep 13 '17 at 11:37

2

Instead of drawing white borders to cover up the default grey grid lines, you should instead just hide the grid lines of your worksheet. To do this via code you would set the ShowGridLines property to False in the SheetView, like the code in this SO answer.

Then remove your code that added white borders to your solution and leave in the code for the blue borders. This will remove the white breaks in your blue border.

answered Sep 13 '17 at 11:37

Taterhead

5,763
4
31
40

Hi Taterhead, thank you very much for your answer! This is a nice solution too. The only drawback is that grid lines are always applied to the whole worksheet or workbook, and can't be applied to specific cells or ranges. – Joyin Sep 13 '17 at 14:44

score 1 · Answer 3 · answered Sep 13 '17 at 21:26

Another solution would be to start with a worksheet where all the gridlines are colored white. The first bullet here shows you how to do this with Excel. To do this on a blank workbook, use the code below:

  sheetView1.DefaultGridColor = false;
  sheetView1.ColorId = 9U;

According to the docs, these properties tell excel not to paint the gridlines the default color and use color index 9 instead. The 9 corresponds to the White color index in a blank excel workbook. It will make all cells have a white gridline color by default.

Then you would remove any of your existing code that painted any cell borders white and leave the code that painted you cell borders blue. This is another way to remove the white hash marks in your original problem.

score 0 · Accepted Answer · answered Sep 13 '17 at 14:52

0

Taterhead's solution is a nice one. I have another one here. Instead of drawing white borders, just set the background color of the cell range to white.

Thanks everyone's help!

answered Sep 13 '17 at 14:52

Joyin

295
1
3
11

Don't forget to upvote the answers and mark the one that suits you the most as resolved – napi15 Sep 14 '17 at 21:18
any idea how to set background color so it doesn't remove all the borders? – bobwah Feb 12 '20 at 12:12

How to apply outline table border to a cell range using OpenXml?

4 Answers4