Applying % number format to a cell value using OpenXML

Question

I want to apply the % (percentage) number format using open XML C#

I have numeric value 3.6 that I want to display that number in excel as `3.6%.

How do I achieve that?

Answer 1

  WorkbookStylesPart sp = workbookPart.AddNewPart<WorkbookStylesPart>(); 

Create a stylesheet,

 sp.Stylesheet = new Stylesheet(); 

Create a numberingformat,

sp.Stylesheet.NumberingFormats = new NumberingFormats(); // #.##% is also Excel style index 1 

NumberingFormat nf2decimal = new NumberingFormat(); nf2decimal.NumberFormatId = UInt32Value.FromUInt32(3453); nf2decimal.FormatCode = StringValue.FromString("0.0%"); sp.Stylesheet.NumberingFormat.Append(nf2decimal); 

Create a cell format and apply the numbering format id

cellFormat = new CellFormat(); cellFormat.FontId = 0; cellFormat.FillId = 0; cellFormat.BorderId = 0; cellFormat.FormatId = 0; cellFormat.NumberFormatId = nf2decimal.NumberFormatId; cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true); cellFormat.ApplyFont = true;  //append cell format for cells of header row sp.Stylesheet.CellFormats.AppendChild<CellFormat>(cellFormat);   //update font count  sp.Stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)sp.Stylesheet.CellFormats.ChildElements.Count);   //save the changes to the style sheet part    sp.Stylesheet.Save(); 

and when you append the value to the cell have the following center code hereonversion and apply the style index in my case i had three style index hence the 3 one was my percentage style index i.e 2 since the indexes start from 0

string val = Convert.ToString(Convert.ToDecimal(value)/100); Cell cell = new Cell(); cell.DataType = new EnumValue<CellValues>(CellValues.Number); cell.CellValue = new CellValue(val); cell.StyleIndex = 2; row.Append(cell); 

Answer 2

Unfortunately there isn't a straight-forward answer. If you download the OpenXML Productivity Tool for Microsoft Office, you can dissect a simple spreadsheet and see how it formats the number. To do just what you want you would need to:

• Create a StyleSheet
• Add a new NumberFormat with your custom definition
• Create a CellStyleFormat, complete with Border, Fill, Font all defined, in addition to the NumberFormat above
• Create a CellFormats, which refers to the above
• Finally set your Cell's StyleIndex to the ID of your CellFormat which uses the NumberFormat.

WHEW!

A generally better option is to look at ClosedXML at http://closedxml.codeplex.com/ (horrid name). It's an open source (NOT GPL! - check the license) library that puts useful extensions on OpenXML. To format a cell of a worksheet, you'd instead do:

worksheet.Cell(row, col).Value = "0.036"; worksheet.Cell(row, col).Style.NumberFormat.Format = "0.0%"; 

(from http://closedxml.codeplex.com/wikipage?title=Styles%20-%20NumberFormat&referringTitle=Documentation )

---

UPDATE ClosedXML has moved to GitHub at https://github.com/ClosedXML/ClosedXML