使用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.

我已经创建了一个使用OpenXml SDK 2.0的Excel文档，现在我必须设计它，但是我不能。

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

我不知道如何绘制背景色或改变不同单元格的字体大小。

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

3 个解决方案

#1

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

注意:OpenXML 2.0 SDK目前在CTP中，直到Office2010才被许可用于生产使用。

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.

我处理OpenXML SDK的一般方法是创建一个空白文档和一个文档，其中只包含您想要学习如何实现的特性(比如背景色)，并使用SDK的OpenXmlDiff查看实现该特性需要做哪些更改。

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.

如果要从头创建文档，可以使用DocumentReflector为默认样式表对象生成代码，然后添加所需的样式。

Starting with the default:

从默认值:

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

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)

我添加了一个新的12号字体和一个红色背景(索引值64)的新填充，并添加了引用新字体和填充索引的新的cellformat。(确保更新计数)

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

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)

然后，在代码中，我将CellStyle索引应用到想要格式化的单元格:(单元格A2和A3中已经有数据了。单元格A2变大，A3变红)

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;

#2

Many thanks for this article.

非常感谢这篇文章。

After a lot of struggling (and Googling), I finally managed to create a very easy-to-use C# class, which takes a DataSet and a Filename, and creates an Office 2007 .xlsx containing the DataSet's data.

经过大量的努力(和google)之后，我终于创建了一个非常容易使用的c#类，它获取一个数据集和一个文件名，并创建一个Office 2007 .xlsx，其中包含数据集的数据。

Suddenly, the process of adding a function to your application to "Export to Excel" becomes as easy as...

突然之间，将函数添加到应用程序以“导出到Excel”的过程变得非常简单……

DataSet ds = CreateSampleData();                  //  Your code here !
string excelFilename = "C:\\Sample.xlsx";

CreateExcelFile.CreateExcelDocument(ds, excelFilename);

I've posted the full source code, plus an example of using it, on the following website.

我已经在下面的网站上发布了完整的源代码，以及使用它的一个例子。

It's a Visual Studio 2008 C# WinForms application, but you can let Visual Studio upgrade this project, if you're running VS2010.

这是一个Visual Studio 2008 c# WinForms应用程序，但是如果你正在运行VS2010，你可以让Visual Studio升级这个项目。

Enjoy.

享受。

http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

#3

How to specify a cell style?

如何指定单元格样式?

new Cell() { CellReference = "B6", StyleIndex = 11U }

Here "11U" is a zero-based index of StylesPart.Stylesheet.CellFormats, in which each CellFormat defines a combination of NumberFormat, Font, Fill and Border styles.

这里“11U”是StylesPart.Stylesheet的从零开始的索引。CellFormat，其中每个CellFormat定义数字格式、字体、填充和边框样式的组合。

You do not have to add all the styles by program, instead you can create a template xlsx file with all the formats you need in it, and then specify the style index in your program.

您不必按程序添加所有样式，而是可以创建一个模板xlsx文件，其中包含所需的所有格式，然后在程序中指定样式索引。

#1