Applying % Number Format to a Cell Value Using Openxml

Applying % number format to a cell value using OpenXML

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

How to get cell value with applied formatting (formatted cell value) with OpenXML SDK

Men, this is a hard one... I will be adding here things that i found that could be worth..

First is to get the numbering format of the cell (once you have the CellFormat:

string format = excel.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements<NumberingFormat>()
            .Where(i => i.NumberFormatId.ToString() == cellFormat.NumberFormatId.ToString())
            .First().FormatCode;

For more information about this you can go to: NumberingFormats

Im trying to find out how to apply this format to the cell.CellValue property... I think thats the way you have to go!

Ok, reading the ClosedXml code (its open source), seems to be easy to get the format.

Simply convert the value text to its type (int, double, etc) and call the ToString method passing the format. I was trying do that with the String.Format and didnt work. Ive tested the ToString and it works, but something still missing.

I recommend to you to look at this class and get the code from the method GetFormattedString() as @El G tell in his comment.

Bassicaly you will have to add something like this:

double d = double.Parse(cell.CellValue.InnerText);
string val = d.ToString(format);

Hope it helps you...

Applying number formatting in OpenXML

Answering my own question, but I came across this post:

http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/

Which suggested that the minimum stylesheet requires quite a few more things than just the numFmts and the cellXfs. So I adapted their code to produce a minimal stylesheet ready for me to insert my cell formats and number formats (which I was doing in a loop):

    private Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();

        Fonts fts = new Fonts();
        DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font()
        {
            FontName = new FontName()
            {
                Val = "Calibri"
            },
            FontSize = new FontSize()
            {
                Val = 11
            }
        };
        fts.Append(ft);
        fts.Count = (uint)fts.ChildElements.Count;

        Fills fills = new Fills();
        fills.Append(new Fill()
        {
            PatternFill = new PatternFill()
            {
                PatternType = PatternValues.None
            }
        });
        fills.Append(new Fill()
        {
            PatternFill = new PatternFill()
            {
                PatternType = PatternValues.Gray125
            }
        });
        fills.Count = (uint)fills.ChildElements.Count;

        Borders borders = new Borders();
        Border border = new Border()
        {
            LeftBorder = new LeftBorder(),
            RightBorder = new RightBorder(),
            TopBorder = new TopBorder(),
            BottomBorder = new BottomBorder(),
            DiagonalBorder = new DiagonalBorder()
        };
        borders.Append(border);
        borders.Count = (uint)borders.ChildElements.Count;

        CellStyleFormats csfs = new CellStyleFormats();
        CellFormat cf = new CellFormat() { 
            NumberFormatId = 0,
            FontId = 0,
            FillId = 0,
            BorderId = 0
        };

        csfs.Append(cf);
        csfs.Count = (uint)csfs.ChildElements.Count;

        NumberingFormats nfs = new NumberingFormats();
        CellFormats cfs = new CellFormats();
        cf = new CellFormat() 
        { 
            NumberFormatId = 0,
            FontId = 0,
            FillId = 0,
            BorderId = 0,
            FormatId = 0
        };
        cfs.Append(cf);

        ss.Append(nfs);
        ss.Append(fts);
        ss.Append(fills);
        ss.Append(borders);
        ss.Append(csfs);
        ss.Append(cfs);

        CellStyles css = new CellStyles();
        CellStyle cs = new CellStyle() 
        {
            Name = "Normal",
            FormatId = 0,
            BuiltinId = 0
        };
        css.Append(cs);
        css.Count = (uint)css.ChildElements.Count;
        ss.Append(css);

        DifferentialFormats dfs = new DifferentialFormats();
        dfs.Count = 0;
        ss.Append(dfs);

        TableStyles tss = new TableStyles() 
        {
            Count = 0,
            DefaultTableStyle = "TableStyleMedium9",
            DefaultPivotStyle = "PivotStyleLight16"
        };
        ss.Append(tss);

        return ss;
    }

Not positive that there aren't things there that could be dropped, but I don't have the patience to go through it by trial-and-error to see if it can be made any slimmer.

I guess a cell format must have a FontId, FillId, BorderId and FormatId in addition to the NumberFormatId and in order to have those ids, you need to create at least one entry for each of them. This is despite the XML Schema labeling them as "optional".

c# open xml reformatting existing cell to a number

Decompile your xlsx to C# using Microsoft Open XML SDK Tools.

https://www.microsoft.com/en-us/download/details.aspx?id=30425

You'll see that your cells have SharedString data type. It's a kind of id, cell stores id, while values are stored in sharedstring table. These weird numbers you see after changing datetype are these ids in sharedstring table.
Changing DataType does not really change the way values are stored, it changes how they are interpreted. They still will be "Number stored as string" if you change its type to numeric.
For example let's take a look at cell "E17":

Cell cell261 = new Cell(){ CellReference = "E17", StyleIndex = (UInt32Value)11U, DataType = CellValues.SharedString };
CellValue cellValue90 = new CellValue();
cellValue90.Text = "26";

...
Should be 50000 isn't it?

        SharedStringItem sharedStringItem27 = new SharedStringItem();
        Text text45 = new Text();
        text45.Text = "50000";
        sharedStringItem27.Append(text45);

In order to read correct value from spreadsheet you need to use GetCellValue code from ms docs sample.

https://learn.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet

Then you can write the following code to read real cell values (I changed it a bit and pass document instead of file path) parse it and save to decimal type:

var value = GetCellValue(d, "Sheet1", cellRef.CellReference);

if (!string.IsNullOrEmpty(value))
{
  if (decimal.TryParse(value, out decimal val))
   {
      cellRef.CellValue = new 
      CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(val));
      cellRef.StyleIndex = 0U;                            
   }
 }

StyleIndex 0U appears to be general format, but since you have correct cell type it does not matter. Even though I still did't get why =sum formulas are not calculated when you open fixed xlsx.

OpenXML SDK - Excel Change Cell Format - C#

I used the following line of code to change the format from percent to currency. THis works since the basic number formats are already added to the file by default.

cell.StyleIndex = 103U

How to create a CellValue for a Decimal With Format (#,###.##) in OpenXML

You should read that article.

Main concept is using custom CellFormat with NumberingFormat:

 var nformat4Decimal = new NumberingFormat
                     {
                         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                         FormatCode = StringValue.FromString("#,##0.0000")
                     };

The Excel cell type is 'Numeric' but still it appears as 'General'

You've misunderstood what the Type function does. It returns the type of the value, not the format of the cell.

If you leave the formatting alone, and change the contents of the cell to "test," the type will change to 2. If you change it to "true" the type will come back as 4. It has nothing to do with the formatting of the cell, which is independent of the content.

---

Related Topics