I am making a stylesheet for an excel sheet with borders for cells, and it works as long as I have no more than two borders. When I add a third border, however, excel tells me there is an error with the xml when I open it, but only when the third border has a right and left border. When it's just a top and bottom border there's no problem.
Why am I getting an error only for border index 2 and only when it has a right and left border?
I'll put the full code for my style sheet method below, but the specific part where I'm having trouble is here:
//this is the border index 2 (third border)
new DocumentFormat.OpenXml.Spreadsheet.Border(
//error is caused by this section
//if I leave the right and left border out there is no error
//also no error if this whole border is border index 0 or 1
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
// end section where error is caused
//top and bottom borders work just fine
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DiagonalBorder()
)//,
Below is my full code for generating stylesheet
private Stylesheet GenerateStyleSheet()
{
return new Stylesheet(
new DocumentFormat.OpenXml.Spreadsheet.Fonts(
new DocumentFormat.OpenXml.Spreadsheet.Font( // Index 0 - The default font.
new FontSize() { Val = 11 },
new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
new FontName() { Val = "Calibri" }),
new DocumentFormat.OpenXml.Spreadsheet.Font( // Index 1 - The bold font.
new Bold(),
new FontSize() { Val = 11 },
new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
new FontName() { Val = "Calibri" }),
new DocumentFormat.OpenXml.Spreadsheet.Font( // Index 2 - The Italic font.
new Italic(),
new FontSize() { Val = 11 },
new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
new FontName() { Val = "Calibri" }),
new DocumentFormat.OpenXml.Spreadsheet.Font( // Index 3 - The Times Roman font. with 16 size
new FontSize() { Val = 16 },
new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
new FontName() { Val = "Times New Roman" })
),
new Fills(
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 0 - The default fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.None }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 0 - The default fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.None }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 2 - The gray fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "0000FF" } }
)
{ PatternType = PatternValues.Solid })
),
new DocumentFormat.OpenXml.Spreadsheet.Borders(
new DocumentFormat.OpenXml.Spreadsheet.Border( // Index 0 - The default border.
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
new DiagonalBorder()),
new DocumentFormat.OpenXml.Spreadsheet.Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DiagonalBorder()
),
new DocumentFormat.OpenXml.Spreadsheet.Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DiagonalBorder()
)//,
//new DocumentFormat.OpenXml.Spreadsheet.Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
// new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
// new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
// )
// { Style = BorderStyleValues.Thick },
// new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
// new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
// )
// { Style = BorderStyleValues.Thick },
// new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
// new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
// )
// { Style = BorderStyleValues.Thick },
// new DiagonalBorder()
//)
),
new CellFormats(
//new CellFormat() { FontId = 1, FillId = 1, BorderId = 0 }//debug
new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead
new CellFormat(
new Alignment()
{ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
)
{ FontId = 1, FillId = 2, BorderId = 0, ApplyFont = true }
),
new CellFormat(
new Alignment()
{
Horizontal = HorizontalAlignmentValues.Right,
Vertical = VerticalAlignmentValues.Bottom
}
)
{
FontId = 0, FillId = 1, BorderId = 0
},
new CellFormat(
new Alignment()
{
Horizontal = HorizontalAlignmentValues.Right,
Vertical = VerticalAlignmentValues.Bottom
}
)
{
FontId = 1, FillId = 0, BorderId = 2
}
); // return
}
The border elements are defined as a sequence and therefore have an explicit order. LeftBorder has to come before RightBorder so you just need to reverse the order in your code:
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }
)
{ Style = BorderStyleValues.Thick },