如何使用OpenXML在Excel文件的单元格中的文本上应用字体和颜色 [英] How to apply font and color on text in cells of Excel file using openxml

问题描述

我是Openxml的新手.我正在尝试使用openxml创建一个xlsx文件.我想将不同的字体和颜色应用于excel文件中不同单元格的文本.我正在使用它来创建xlsx文件，但无法执行字体和颜色部分.

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?

推荐答案

就像爱德华已经说过的那样，您需要创建一个样式表对象.

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");

Stylesheet对象必须包含要使用的所有字体，填充，单元格格式等.

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);

将样式表分配给您的workbookpart对象

Assign the Stylesheet to your workbookpart object

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

之后，您可以将CellStyleId分配给单元格.

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

---

将字体，填充，边框等分配给诸如CellFormats之类的对象

每个Font，Fill，Border，...可以通过它们在相应集合中的索引分配给其他对象.例如，第一个添加到fonts对象的字体将具有索引1U.

---

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.

这些索引表示为无符号整数(没有负值，只有正值). "U"后缀表示uint或ulong.

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

OpenXml文档，无符号整数

这篇关于如何使用OpenXML在Excel文件的单元格中的文本上应用字体和颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！