How to create an Excel file in .NET using OpenXML – Part 3: Add Stylesheet to the Spreadsheet
Overview
This is the 3rd part of OpenXML Spreadsheet Tutorial Series:
-
Part 3: Add Stylesheet to the Spreadsheet
In this tutorial we are going work with our sample in the Part 2, and we are going to add style and do some customization on that.
Step 1: Add Stylesheet
Stylesheet class is used to add custom style to a spreadsheet. Stylesheet can accept different elements such as, Borders, Colors, Fills, and etc. as its child elements which specifies the look of a spreadsheet. The CellFormats stores the combination of different Styles which later can be applied on a cell.
In order to add a Stylesheet to our workbook, we need to add a WorkbookStylePart to the Workbook part and initialize its StyleSheet property.
We are going to make our header bold and white with dark background as well as adding border to all other cells.
Create a new method GenerateStylesheet() which returns a StyleSheet object to the Report class.
private Stylesheet GenerateStylesheet() { Stylesheet styleSheet = null; return styleSheet; }
Fonts
Fonts can have one or more Font children which each have different properties like FontSize, Bold, Color, and etc.
Add the following block of code in GenerateStyleSheet method:
Fonts fonts = new Fonts( new Font( // Index 0 - default new FontSize() { Val = 10 } ), new Font ( // Index 1 - header new FontSize() { Val = 10 }, new Bold(), new Color() { Rgb = "FFFFFF" } ));
Take note that we add two Font children to the Fonts object. The first one is the default font use by all cells, and the second one is specific to header.
Fills
Fills can have one or more Fill children which you can set its ForegroundColor.
Fills fills = new Fills( new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } }) { PatternType = PatternValues.Solid }) // Index 2 - header );
Excel needs to have the first two as the default. The 3rd one is the style we want to have for our header cells; a gray sold background.
Borders
Borders can have one or more Border children which each defines how the border should look like:
Borders borders = new Borders( new Border(), // index 0 default new Border( // index 1 black border new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder()) );
The 1st is the default border and the 2nd one is our customized border.
CellFormats
Now that we have defied our custom formats elements we can create CellFormats which has one or many CellFormat children. Each CellFormat gets the index of Font, Border, Fill, or etc. which it will associate with:
CellFormats cellFormats = new CellFormats( new CellFormat(), // default new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header );
Finally we initialize the Stylesheet object:
styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);
Complete Stylesheet code:
private Stylesheet GenerateStylesheet() { Stylesheet styleSheet = null; Fonts fonts = new Fonts( new Font( // Index 0 - default new FontSize() { Val = 10 } ), new Font( // Index 1 - header new FontSize() { Val = 10 }, new Bold(), new Color() { Rgb = "FFFFFF" } )); Fills fills = new Fills( new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } }) { PatternType = PatternValues.Solid }) // Index 2 - header ); Borders borders = new Borders( new Border(), // index 0 default new Border( // index 1 black border new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder()) ); CellFormats cellFormats = new CellFormats( new CellFormat(), // default new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header ); styleSheet = new Stylesheet(fonts, fills, borders, cellFormats); return styleSheet; }
Step 2: Add the style to the workbook
Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet:
// Adding style WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>(); stylePart.Stylesheet = GenerateStylesheet(); stylePart.Stylesheet.Save();
Step 3: Add style to Cells
Now that we have our stylesheet link to workbook we can specify what style each cell need to follow.
Modify the content of ConstructCell method in Report.cs to get the style index:
private Cell ConstructCell(string value, CellValues dataType, uint styleIndex = 0) { return new Cell() { CellValue = new CellValue(value), DataType = new EnumValue<CellValues>(dataType), StyleIndex = styleIndex }; }
As you can see, Cell have a property called StyleIndex which gets the index of the style we want to apply to this cell. The index here refers to index of CellFormats.
Modifies the construction of the header and cell in CreateExcelDoc and pass the desirable style index:
// Constructing header Row row = new Row(); row.Append( ConstructCell("Id", CellValues.String, 2), ConstructCell("Name", CellValues.String, 2), ConstructCell("Birth Date", CellValues.String, 2), ConstructCell("Salary", CellValues.String, 2)); // Insert the header row to the Sheet Data sheetData.AppendChild(row); // Inserting each employee foreach (var employee in employees) { row = new Row(); row.Append( ConstructCell(employee.Id.ToString(), CellValues.Number, 1), ConstructCell(employee.Name, CellValues.String, 1), ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 1), ConstructCell(employee.Salary.ToString(), CellValues.Number, 1)); sheetData.AppendChild(row); }
Step 4: Add custom width for columns
Adding custom width to specific columns is very easy. First we need to create a Columns object and then add one or more Column as its children which each will define the custom width of a range of columns in the spreadsheet. You can explore the properties of the column to specify more customization on columns. Here we are only interested in specifying the width of the columns.
// Setting up columns Columns columns = new Columns( new Column // Id column { Min = 1, Max = 1, Width = 4, CustomWidth = true }, new Column // Name and Birthday columns { Min = 2, Max = 3, Width = 15, CustomWidth = true }, new Column // Salary column { Min = 4, Max = 4, Width = 8, CustomWidth = true });
Lastly we need to append the columns to work worksheet.
worksheetPart.Worksheet.AppendChild(columns);
Result
Run the application and check the generated Excel
Complete Code
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace OpenXMLSample { public class Report { public void CreateExcelDoc(string fileName) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); // Adding style WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>(); stylePart.Stylesheet = GenerateStylesheet(); stylePart.Stylesheet.Save(); // Setting up columns Columns columns = new Columns( new Column // Id column { Min = 1, Max = 1, Width = 4, CustomWidth = true }, new Column // Name and Birthday columns { Min = 2, Max = 3, Width = 15, CustomWidth = true }, new Column // Salary column { Min = 4, Max = 4, Width = 8, CustomWidth = true }); worksheetPart.Worksheet.AppendChild(columns); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" }; sheets.Append(sheet); workbookPart.Workbook.Save(); List<Employee> employees = Employees.EmployeesList; SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Constructing header Row row = new Row(); row.Append( ConstructCell("Id", CellValues.String, 2), ConstructCell("Name", CellValues.String, 2), ConstructCell("Birth Date", CellValues.String, 2), ConstructCell("Salary", CellValues.String, 2)); // Insert the header row to the Sheet Data sheetData.AppendChild(row); // Inserting each employee foreach (var employee in employees) { row = new Row(); row.Append( ConstructCell(employee.Id.ToString(), CellValues.Number, 1), ConstructCell(employee.Name, CellValues.String, 1), ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 1), ConstructCell(employee.Salary.ToString(), CellValues.Number, 1)); sheetData.AppendChild(row); } worksheetPart.Worksheet.Save(); } } private Cell ConstructCell(string value, CellValues dataType, uint styleIndex = 0) { return new Cell() { CellValue = new CellValue(value), DataType = new EnumValue<CellValues>(dataType), StyleIndex = styleIndex }; } private Stylesheet GenerateStylesheet() { Stylesheet styleSheet = null; Fonts fonts = new Fonts( new Font( // Index 0 - default new FontSize() { Val = 10 } ), new Font( // Index 1 - header new FontSize() { Val = 10 }, new Bold(), new Color() { Rgb = "FFFFFF" } )); Fills fills = new Fills( new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } }) { PatternType = PatternValues.Solid }) // Index 2 - header ); Borders borders = new Borders( new Border(), // index 0 default new Border( // index 1 black border new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder()) ); CellFormats cellFormats = new CellFormats( new CellFormat(), // default new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header ); styleSheet = new Stylesheet(fonts, fills, borders, cellFormats); return styleSheet; } } }
This is very helpful, thank you very much!
You are welcome, I am glad it helps.
Is it possible to autowidth columns to fit the max length of a cell ?
I have never tried that. But I guess you need to calculate it automatically. See if this thread can help you:
http://stackoverflow.com/questions/31197038/how-to-autofit-excel-column-using-openxml-package
The great tutorial.
It would be nice if you write an example code about Condition Formatting >> Color Scale.
Thank you!
Hey Behnam, Really helpful post. Wanna ask you one thing: if I want to add text in cells having 50-60 different fonts and colours , Then i have to mention each and every font and colour in my workbook stylesheet ??
Thanks, I am happy it is useful. About your question, I haven’t done that and I am not sure. This was the way I have found. I am not sure if there is any possibilities on define classes, as in CSS, and apply to multiple cells. I will search and will let you know if I came across any solution.
Thanks! Extremely Helpful!
Great article
How could you use the NumberingFormats to format the salary? We would have to do another style?
Thank you for article
You don’t need to create another StyleSheet. You just need to append your NumberingFormat to that StyleSheet. This thread in StackOverflow might help:
https://stackoverflow.com/questions/7872116/applying-number-format-to-a-cell-value-using-openxml
Many Thanks. I like this tutorial very much.
Helped me a lot, thank you!
You are welcome. I am happy it helped.
Helped me a lot to understand! Thanks! nifty piece of work
You are welcome! Happy it helped you!
hey this is a great tutorial, can u suggest a way to set thousand separator in salary column in stylesheet?
Thank you!!
Can you show how to format a column as currency? I just can’t get it to work without a stylesheet error.
Well done!!!
Thanks!
Hi Behnam,
Thanks for your post. It really helped me to understand the OpenXML better.
I have a requirement. I need to find a particular row of an excel sheet using one column value and edit the other columns of that row.[Similar to editing an SQL Table item using primary key]. Can you please suggest the best approach? My excel can have 500 + rows.
Thank you
Regards
Nimisha
This was very useful, thank you very much. By following you guide, i finally got my code to work – so many other examples out there, that just doesn’t work (missing something) – This is a complete and working example, thank you 🙂
Welcome, happy it helps 🙂
Hi Benham
Thank you very much for this post and it was really helpful. I have an excel template with 4 tabs with all necessary headings. When I try to add data into it, it start from first cell, not after the heading rows. Can you please guide me how to achieve this?
For some reason only the borders are working when I use style index 2. That’s frustrating, because I really need the headings to be bold. Any suggestions?
My sincere thanks to the detailed tutorials.
I was stuck struggling with OpenXML doing column width, different font & fill for header and rest of the content, since 5 days.
And in few hours (using this tutorial) I have completed the work.
Though only alignment part remaining. 🙂
KUDOS to the tutorial & the contributor.
Thanks 🙂 happy it is helpful
I spent hours trying to determine why my custom Fills were not working. I eventually realized, through some reverse engineering, that the Fills array, unlike the other array elements, requires the _two_ default fills in positions [0] and [1]. This is not made clear in any of the examples I looked at, _except_ yours (which I only found after figuring it, sadly). Kudos to you for attention to detail that is lacking in all other examples I looked at. (“The devil is in the details”, but I see you know that by your choice of colours in your Fills example… 😉
Welcome. Happy it is helpful 🙂
I created the “columns ” as follows and could get the column definitions in the output.
var columns = new Columns();
var column1 = new Column { Min = 1, Max = 1, Width = 4, CustomWidth = true };
var column2 = new Column { Min = 1, Max = 1, Width = 20, CustomWidth = true };
var column3 = new Column { Min = 1, Max = 1, Width = 30, CustomWidth = true };
var column4 = new Column { Min = 1, Max = 1, Width = 10, CustomWidth = true };
columns.Append(column1);
columns.Append(column2);
columns.Append(column3);
columns.Append(column4);
worksheetPart.Worksheet.AppendChild(columns);
Everthing else ist just the same as yours.
Any suggestions?
very nice article to start with. thanks 🙂
Thanks
Thank you, may be the best tutorial for openXML in the web!
You are welcome. Happy it’s helpful.
Really helped me. Simple and precise explanation. Thanks a lot!
Hi benham,
Your post was quite helpful to start working with OpenXML but i am having issue while using Inlinstring type.
Issues:
Added inlinestring data (i.e. Bold, Italic etc…) its popping up an error message when we open Excel sheet. Its bit annoying for the user although purpose is achieved to provide formatted text in a cell in Excel file.
Error message:
We found a problem with some content in test.xlsx file. Do you want us to try to recover as much as we can? If you trust the source of this workbook. Click yes
And we click on Yes again information message like recovered file:
Excel was able to open the file by repairing or removing the unreadable content
Repaired Records: String properties from /xl/worksheets/sheet1.xml part
Need your expertise to solve this issue.
Thanks in advance
Thanks much for this.
Verry Nice TUTORIAL .
Thanks Behnam, you save my day 🙂
Hi, extremely helpful article.
Is it possible to wrap text in the entire sheet?
Thanks