CODEWITHSUNDEEP
c#openxmlopenxml-sdk
Selwyn
Selwyn

Reputation: 1621

Applying % number format to a cell value using OpenXML

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?

Upvotes: 26

Views: 70992

Answers (5)

Elephantik
Elephantik

Reputation: 2008

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

Upvotes: 0

Selwyn
Selwyn

Reputation: 1621

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

Upvotes: 35

jklemmack
jklemmack

Reputation: 3636

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

Upvotes: 19

Smit Patel
Smit Patel

Reputation: 3247

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.

Upvotes: 5

amurra
amurra

Reputation: 15401

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;

Upvotes: 6

Related Questions