CODEWITHSUNDEEP
c#excelopenxml
jimboweb
jimboweb

Reputation: 4542

c# openxml excel stylesheet border error

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
}

Upvotes: 2

Views: 2842

Answers (1)

petelids
petelids

Reputation: 12815

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 },

Upvotes: 3

Related Questions