Set Bold to a table cell

Home Forums SpreadsheetML Set Bold to a table cell

This topic contains 2 replies, has 3 voices, and was last updated by  Jim Snyder 11 months, 3 weeks ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #3387

    grivas
    Participant

    I’m not able to figure out how to set a bold text into a Spreadsheet when creating it.
    I need some guidance please

    #3395

    Eric White
    Keymaster

    Dealing with styles in SpreadsheetML is an area that definitely needs more content – how it works, and how to generate it. Styling is definitely optimized for quick reading of spreadsheets, not for ease of modification of styling.

    Have you taken a look at the ClosedXml library (https://closedxml.codeplex.com/). I haven’t used it, but have heard good things about it.

    #3602

    Jim Snyder
    Participant

    This is getting long in the tooth, but an answer still needs to be there for others. There are some things that are background to cover first. Since you didn’t give much detail as to where you are starting from, you start with building the top level container to add parts to. At an absolute minimum, you need this top level container and a worksheet (can be empty).
    You need to add other parts to make this happen. One is the WorkbookPart which is the container to add a workbook to. Another is the WorksheetPart to contain the sheet description. This also requires a SheetData part to build your spreadsheet rows and cells in. The part that will be of help to you is the WorkbookStylesPart that will be the container for your stylesheet. An example stylesheet would look like this below. It has a commented bold style for you to emulate:

            // Style part
            private Stylesheet GenerateWorkbookStylesPartContent(WorkbookStylesPart stylesPart)
            {
                return new Stylesheet
                (
                    new Fonts
                    (
                        new Font(new FontSize(){Val = 11}, new Color(){Rgb = new HexBinaryValue(){Value = "000000"}}, new FontName(){Val = "Calibri"}),
                        new Font(new Bold(), new FontSize(){Val = 11}, new Color(){Rgb = new HexBinaryValue(){Value = "000000"}}, new FontName(){Val = "Calibri"}),
                        new Font(new Italic(), new FontSize(){Val = 11}, new Color(){Rgb = new HexBinaryValue(){Value = "000000"}}, new FontName(){Val = "Calibri"})
                    ),
                    new Fills
                    (
                        new Fill(new PatternFill(){PatternType = PatternValues.None}),       // Required. FillID = 0
                        new Fill(new PatternFill(){PatternType = PatternValues.Gray125}),    // Required. FillID = 1
                        new Fill(new PatternFill(new ForegroundColor(){Rgb = new HexBinaryValue(){Value = tabColor1}}){PatternType = PatternValues.Solid}), // FillID = 3
                        new Fill(new PatternFill(new ForegroundColor(){Rgb = new HexBinaryValue(){Value = tabColor2}}){PatternType = PatternValues.Solid}), // FillID = 4
                        new Fill(new PatternFill(new ForegroundColor(){Rgb = new HexBinaryValue(){Value = "FFE2E2E2"}}){PatternType = PatternValues.Solid}) // FillID = 5
                    ),
                    new Borders
                    (
                        new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder()),     // Default - required
                        new Border
                        (
                            new LeftBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                            new RightBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                            new TopBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                            new BottomBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                            new DiagonalBorder(new Color(){Auto = true}){Style = BorderStyleValues.None}
                        )
                    ),
                    new CellFormats
                    (
                        new CellFormat(){FontId = 0, FillId = 0, BorderId = 0, ApplyFont = true},   // Index 0 - default cell style.
                        new CellFormat(){FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true},   // Index 1 - Bold.
                        new CellFormat(){FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true},   // Index 2 - Italic.
                        new CellFormat(){FontId = 0, FillId = 3, BorderId = 0, ApplyFill = true},   // Index 3 - Lt yellow fill.
                        new CellFormat(){FontId = 0, FillId = 4, BorderId = 0, ApplyFill = true},   // Index 4 - Lt green fill.
                        new CellFormat(){FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true},  // Index 5 - Border.
                        new CellFormat(){FontId = 1, FillId = 5, BorderId = 1, ApplyFont = true, ApplyFill = true, ApplyBorder = true}   // Index 6 - Bold w/lt gray fill and borders.
                    )
                );              // Return StyleSheet
            }
    

    There are some important things to note:

  • It is important to reference the style when you create the cell in the WorksheetData.
  • There are default styles that must be there to have your style show. For fonts, this is just a zero indexed blank font style. That would mean your font style, if next, would have an index of one.
  • A workbook will only have a single WorkbookPart, a single WorkbookStylesPart, a single SharedStringTablePart, but can have as many WorksheetPart as you need. Each place you use the style or sharedstring will point back to the index within that part to be accessed.
  • The OpenXml SDK has a reflection part that will show you in code what a sheet you build manually has.
Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.