与OpenXML的SDK 2.0创建Excel文档 [英] Creating Excel document with OpenXml sdk 2.0

问题描述

我一直在使用的OpenXML SDK 2.0中创建一个Excel文档，现在我要的风格，但我不能老是。

I have created an Excel document using OpenXml SDK 2.0, now I have to style It, but I can`t.

我不知道怎么画的背景颜色或不同的细胞改变字体大小。

I don't know how to paint the background color or change the font size in different cells.

我的code创建一个单元格是：

My code to create a cell is:

private static Cell CreateTextCell(string header, string text, UInt32Value index)
{
    Cell c = new Cell();
    c.DataType = CellValues.InlineString;
    c.CellReference = header + index;
    InlineString inlineString = new InlineString();
    DocumentFormat.OpenXml.Spreadsheet.Text t = new DocumentFormat.OpenXml.Spreadsheet.Text();
    t.Text = text;
    inlineString.AppendChild(t);
    c.AppendChild(inlineString);
    return c;
}

感谢您的帮助和抱歉，我的英语不好。

Thanks for your help and sorry by my poor English.

推荐答案

注：OpenXML的2.0 SDK是目前在CTP和未授权用于生产，直到Office2010的。

Note: OpenXML 2.0 SDK is currently in CTP and is not licensed for production use until Office2010.

我一般methodoloy处理OpenXML的SDK是创建一个空白文档，并只用功能的文档，你想了解如何实现（如背景色），并使用SDK的OpenXmlDiff，看看有什么变化需为执行该功能。

My general methodoloy to deal with OpenXML SDK is to create a blank document and a document with just the features you'd like to learn how to implement (like background color) and use the SDK's OpenXmlDiff to see what changes need to be made to implement the feature.

如果您是从头开始创建文档，可以使用DocumentReflector生成code默认样式表的对象，然后添加你需要的风格。

If you are creating a document from scratch, you can use DocumentReflector to generate the code for the default Stylesheet object and then add the styles you need.

用默认的开始：

new Stylesheet(
new Fonts(
    new Font(
        new FontSize() { Val = 10D },
        new Color() { Theme = (UInt32Value)1U },
        new FontName() { Val = "Arial" },
        new FontFamilyNumbering() { Val = 2 })
) { Count = (UInt32Value)1U },
new Fills(
    new Fill(
        new PatternFill() { PatternType = PatternValues.None }),
    new Fill(
        new PatternFill() { PatternType = PatternValues.Gray125 })
) { Count = (UInt32Value)2U },
new Borders(...
...
...
new CellFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }) { Count = (UInt32Value)1U }, ...

我添加了一个新的字体大小12和红色背景（索引值64）的新填充，并补充说，引用新字体的索引和填充新CellFormats。 （请务必更新计数太）

I've added a new Font of size 12 and a new Fill with red background (Indexed value 64), and added new CellFormats that reference the index of the new Font and Fill. (Make sure to update the Counts too)

new Stylesheet(
    new Fonts(
        new Font(
            new FontSize() { Val = 10D },
            new Color() { Theme = (UInt32Value)1U },
            new FontName() { Val = "Arial" },
            new FontFamilyNumbering() { Val = 2 }),
        new Font(
            new FontSize() { Val = 12D },
            new Color() { Theme = (UInt32Value)1U },
            new FontName() { Val = "Arial" },
            new FontFamilyNumbering() { Val = 2 })
            ) { Count = (UInt32Value)2U },
    new Fills(
        new Fill(
            new PatternFill() { PatternType = PatternValues.None }),
        new Fill(
            new PatternFill() { PatternType = PatternValues.Gray125 }),
        new Fill(
            new PatternFill() { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor() { Rgb = "FFFF0000" }, BackgroundColor = new BackgroundColor() { Indexed = 64 } })
            ) { Count = (UInt32Value)3U },
    new Borders(
        new Border(
            new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder())
    ) { Count = (UInt32Value)1U },
    new CellStyleFormats(
        new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }
    ) { Count = (UInt32Value)1U },
    new CellFormats(
        new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
        new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
        new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }
    ) { Count = (UInt32Value)3U },
    new CellStyles(
        new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U }
    ) { Count = (UInt32Value)1U },
    new DifferentialFormats() { Count = (UInt32Value)0U },
    new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" });

然后，在code，I应用CellStyle指数我想格式的单元格：
（有已经在单元中的数据A2和A3。细胞A2获取较大的尺寸，A3得到红色背景）

Then, in code, I apply the CellStyle index to the cells I want to format: (There was already data in cells A2 and A3. Cell A2 gets the larger size, A3 gets red background)

SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
sheetData.Descendants<Row>().Where(r => r.RowIndex == 2U).First().Descendants<Cell>().First().StyleIndex = 1U;
sheetData.Descendants<Row>().Where(r => r.RowIndex == 3U).First().Descendants<Cell>().First().StyleIndex = 2U;

这篇关于与OpenXML的SDK 2.0创建Excel文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！