Creating an Excel document using OpenXml sdk 2.0

Question

I created an Excel document using the OpenXML SDK 2.0, now I need to style it, but I can not.

I do not know how to draw the background color or change the font size in different cells.

My code to create a cell:

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

Answer 1

Note. The OpenXML 2.0 SDK is currently located in CTP and does not have a production license until Office2010.

My general methodology for working with the OpenXML SDK is to create an empty document and a document using only those functions that you would like to learn how to implement (for example, the background color) and use the OpenXmlDiff SDK to find out what changes should be in order to implement this function.

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

Starting from the default value:

 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 added a new font of size 12 and a new Fill with a red background (indexed value of 64) and added new CellFormats that refer to the index of the new font and fill. (Be sure to update the counters)

 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 the code I apply the CellStyle index to the cells that I want to format: (There was already data in cells A2 and A3. Cell A2 gets a larger size, A3 gets a 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; 

Answer 2

Thanks so much for this article.

After many problems (and Googling), I was able to create a very easy-to-use C # class that accepts a DataSet and file name and creates an Office.xlsx file containing DataSet data.

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

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

I have posted the full source code as well as an example of its use on the following website.

This is a Visual Studio 2008 C # WinForms application, but you can let Visual Studio upgrade this project if you are using VS2010.

Enjoy.

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

Answer 3

How to specify 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.

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