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?
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?
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.NumberingFormats.Append(nf2decimal);
Create a cell format and apply the numbering format id
CellFormat 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);
NumberFormatId
3453 a special meaining or can I freely choose the ID? –
Hustings 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:
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
Excel contains predefined formats to format strings in various ways. The s
attribute on a cell element will refer to a style which will refer to a number format that will correspond to the percent format you want. See this question/answer for more information.
Here is the CellFormat object you will need to create in order to have the 0.00% mask applied to your number. In this case you want the predefined format number 10 or 0.00%:
CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)10U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
Here is a quick way to insert the CellFormat into the workbook:
CellFormats cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.Elements<CellFormats>().First();
cellFormats.Append(cellFormat);
uint styleIndex = (uint)cellFormats.Count++;
You will then need to get the cell that has the 3.6 in it and set it's s
attribute (StyleIndex) to the newly inserted cell format:
Cell cell = workSheetPart.Worksheet.Descendants<Cell>().SingleOrDefault(c => cellAddress.Equals("A1"));
cell.StyleIndex = styleIndex;
You can do it in a simple way. If you want to apply it on single cell then do this,
worksheet.Cell(9, 10).Style.NumberFormat.Format = "#,##0.00\\%";
And If you want to apply it on a Range of Cells then do this,
worksheet.Range(9, 10, 15, 10).Style.NumberFormat.Format = "#,##0.00\\%";
you can also find more formats Here, and also you can find the same from Excel as well.
The answer by Selwin didn't work for me (Excel showed an error message), but I figured out that you have to defoine default Font, Fill and Border and then you want to have a default CellFormat and your custom CellFormat(s).
The CellFormat style by default references FontId = 0
, FillId = 0
and BorderId = 0
. If those are not found, Excel shows an error message and no styles are applied. I also figured out, that setting counts for each collection is not mandatory (at least my Excel worked without it).
This is a bare minimum that worked for me:
static void SetupStylesheet(WorkbookPart workbookPart)
{
WorkbookStylesPart sp = workbookPart.AddNewPart<WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
const uint numberFormatId = 3453;
sp.Stylesheet.NumberingFormats = new NumberingFormats(new NumberingFormat
{
NumberFormatId = numberFormatId,
FormatCode = StringValue.FromString("0.00%")
});
sp.Stylesheet.Fonts = new Fonts(new Font());
sp.Stylesheet.Fills = new Fills(new Fill());
sp.Stylesheet.Borders = new Borders(new Border());
sp.Stylesheet.CellFormats = new CellFormats(
new CellFormat { FormatId = 0 },
new CellFormat { FormatId = 1, NumberFormatId = numberFormatId }
);
sp.Stylesheet.Save();
}
The first (index 0) CellFormat is applied to all cells by default. For the cells, where you want to use the custom format (in this case Percentage), you just assign:
Cell cell = new Cell()
{
DataType = CellValues.Number,
CellValue = new CellValue(1.23),
cell.StyleIndex = 1
};
© 2022 - 2025 — McMap. All rights reserved.