Applying % number format to a cell value using OpenXML
Asked Answered
I

5

26

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?

Ileum answered 24/10, 2011 at 6:47 Comment(0)
I
35
  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);
Ileum answered 26/10, 2011 at 8:37 Comment(6)
This flow worked for we can have have any cell formatting using this process. Thanks ssyladinIleum
No worries. Actually spend ~30 minutes trying to do the formatting "manually" with OpenXML only and eventually gave up. Kept on getting a corrupt worksheet. So this is a good answer to me, since it helps avoid external library dependencies.Construe
Does your comment about the Excel style index mean that, if you want to use the default Excel percent styling, that you don't need to add a new format, just set the cell to style index 1?Broadus
Has the NumberFormatId 3453 a special meaining or can I freely choose the ID?Hustings
@Ileum Not working for me , excel getting corrupted .Erasmo
Same is happening for me. I get a corrupted error and no formatting with this method. I also had to add a new CellFormats() to the sp.Stylesheet just to avoid a null reference exception.Harebrained
C
19

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

Construe answered 25/10, 2011 at 6:25 Comment(1)
this may work, however requires a third party addition, not 'plain' OpenXml.Younger
W
6

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;
Wideeyed answered 25/10, 2011 at 13:20 Comment(0)
L
5

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.

Lashing answered 19/10, 2015 at 15:39 Comment(2)
This is not OpenXml as indicated on the question.Younger
Your backslashes saved my life :) Thanks.Inorganic
Q
0

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
};
Queenie answered 18/7, 2024 at 12:36 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.