How to apply font and color on text in cells of Excel file using openxml

Question

I am new in Openxml. I am trying to create a xlsx file using openxml. I want to apply different font and color to the text of different cells in excel file. I am using this for creating a xlsx file but not able to do the font and color part.

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("E:\\Word9.xlsx", SpreadsheetDocumentType.Workbook);

        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();


        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());


        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        int i = 1;
        while (i <= 5)
        {
            Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = (UInt32)i, Name = "mySheet" + i.ToString() };

            for (int ii = 1; ii <= 5; ii++)
            {

                Row row1 = new Row();
                sheets.AppendChild(row1);

                Cell cll = new Cell(new InlineString(new DocumentFormat.OpenXml.Spreadsheet.Text("qqqqqq"))) { DataType = CellValues.InlineString };
                row1.AppendChild(cll);

            }
            sheets.Append(sheet);
            i++;
        }


        workbookpart.Workbook.Save();

Is there any one who know how to apply the font and color to different cells?

Answer 1

Like Edward already said you need to create a Stylesheet object.

var stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() {Ignorable = "x14ac"}};
stylesheet.AddNamespaceDeclaration("mc", "http: //schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http: //schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

The Stylesheet object have to contain all Fonts, Fills, CellFormats, etc. that you want use.

// create collections for fonts, fills, cellFormats, ...
var fonts = new Fonts() { Count = 1U, KnownFonts = true };
var fills = new Fills() {Count = 5U};
var cellFormats = new CellFormats() { Count = 4U };

// create a font: bold, red, calibr
Font font = new Font();
font.Append(new FontSize() {Val = 11D});
font.Append(new Color() { Rgb = "ff0000"});
font.Append(new FontName() {Val = "Calibri"});
font.Append(new FontFamilyNumbering() {Val = 2});
font.Append(new FontScheme() {Val = FontSchemeValues.Minor});
font.Append(new Bold());
// add the created font to the fonts collection
// since this is the first added font it will gain the id 1U
fonts.Append(font);

// create a background: green
Fill fill = new Fill();
var patternFill = new PatternFill() {PatternType = PatternValues.Solid};
patternFill.Append(new ForegroundColor() {Rgb = "00ff00"});
patternFill.Append(new BackgroundColor() {Indexed = 64U});
fill.Append(patternFill);
fills.Append(fill);

// create a cell format (combining font and background)
// the first added font/fill/... has the id 0. The second 1,...
cellFormats.AppendChild(new CellFormat(){ FontId = 0U, FillId = 0U });

// add the new collections to the stylesheet
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(cellFormats);

Assign the Stylesheet to your workbookpart object

var stylePart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = stylesheet;
stylePart.Stylesheet.Save();

After that you are able to assign a CellStyleId to a Cell.

var cell = new Cell() {
  CellValue = new CellValue("your cooler string"),
  DataType = new EnumValue<CellValues>(CellValues.String),
  StyleIndex = 0U // index in the cellFormats array
};

---

assigning fonts, fills, borders, etc. to objects like CellFormats

Each Font, Fill, Border, ... can be assigned to other objects by their index in the corresponding collection. For example the first added font to the fonts object will have the index 1U.

These indices are represented as unsigned integers (no negative values more positive values). The 'U' suffix denotes either a uint or a ulong.

---

links

OpenXml docs, unsigned integers

Answer 2

You need to create and use a style sheet. Here is a good reference, but there are more if you search on creating a stylesheet. Stylizing your Excel worksheets