使用OpenXml sdk 2.0创建Excel文档 [英] Creating Excel document with OpenXml sdk 2.0
问题描述
我不知道如何画背景颜色或更改不同单元格中的字体大小。
我创建单元格的代码是：
private static Cell CreateTextCell（string header，string text，UInt32Value index）
{
单元格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;
}
注意：OpenXML 2.0 SDK目前在CTP中，直到Office2010才被许可使用。
我的一般方法来处理OpenXML SDK是创建一个空白的文档和一个文档，只需要你想学习的功能（如背景颜色）并使用SDK的OpenXmlDiff来查看实现该功能需要做哪些更改。
如果您从头开始创建文档，可以使用DocumentReflector生成默认Stylesheet对象的代码，然后添加所需的样式。
从默认值开始：
新样式表（
new Fonts（
new Font（
new FontSize（）{Val = 10D}，
new Color（）{Theme =（UInt32Value）1U}，
new FontName（）{Val =Arial $，
new Fill（
new Fill（
new Fill（
new Fill 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的新字体和一个带有红色背景的新填充（Indexed值64），并添加了引用新Font和Fill的索引的新CellFormats。 （确保更新计数）
新样式表（
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}，Backgro undColor = new BackgroundColor（）{Indexed = 64}}）
）{Count =（UInt32Value）3U}，
new Borders（
new Border（
new LeftBorder新的RightBorder（），新的TopBorder（），新的BottomBorder（），新的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 b $ b 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 =（U Int $）$ 2
new CellStyles（
new CellStyle（）{Name =（UInt32Value）0U）
）{Count =（UInt32Value）3U} Normal，FormatId =（UInt32Value）0U，BuiltinId =（UInt32Value）0U}
）{Count =（UInt32Value）1U}，
new DifferentialFormats（）{Count =（UInt32Value）0U}，
new TableStyles（）{Count =（UInt32Value）0U，DefaultTableStyle =TableStyleMedium9，DefaultPivotStyle =PivotStyleLight16}）;
然后，在代码中，我将CellStyle索引应用于要格式化的单元格：
（单元格A2和A3中已经存在数据，单元格A2得到较大的尺寸，A3获得红色背景）
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild< SheetData>（）;
sheetData.Descendants< Row>（）。其中（r => r.RowIndex == 2U）.First（）。Descendants< Cell>（）。First（）。StyleIndex = 1U;
sheetData.Descendants< Row>（）。其中（r => r.RowIndex == 3U）.First（）。Descendants< Cell>（）。First（）。StyleIndex = 2U;
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.
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;
}
Note: OpenXML 2.0 SDK is currently in CTP and is not licensed for production use until 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.
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.
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)
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)
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屋！
