CODEWITHSUNDEEP
c#excelopenxml
Mauro Bilotti
Mauro Bilotti

Reputation: 6232

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

I'm currently working in the requirement of export an Excel file by using Open XML framework. The problem that I have, is that one of the columns of this spreadsheet has to be decimal in format (#,###.##) which has to allow sums. I could export the Excel perfectly in this format by using the following method:

private static Cell CreateTextCell(string header, UInt32 index, object text, CellStyleIndex cellStyle)
{
    var cell = new Cell
    {
        DataType = CellValues.InlineString,
        CellReference = header + index,
        StyleIndex = (UInt32)cellStyle
    };

    var istring = new InlineString();
    var t = new Text { Text = text.ToString() };
    istring.AppendChild(t);
    cell.AppendChild(istring);
    return cell;
}

As you can see, I'm specifying the StyleIndex which applies the format that I mentioned. But the problem with this is that Excel recognizes this value as a text:

enter image description here

That's why I tried to create a new method which is invoked as soon I want to create a decimal in the file:

private static Cell CreateValueCell(string header, UInt32 index, decimal value, CellStyleIndex cellStyle)
{
     var cell = new Cell
     {
         DataType = CellValues.Number,
         CellReference = header + index,
         StyleIndex = (UInt32)cellStyle,
         CellValue = new CellValue(value.ToString())
     };

     return cell;
}

By doing this, I reach how to convert as a number, but I lose the decimal places as you can see in the following image:

enter image description here

I saw a class named DecimalValue but I couldn't figure out how to append it to the cell. Any thoughts about how to solve it?

Upvotes: 3

Views: 14812

Answers (2)

Érica Betto Sene
Érica Betto Sene

Reputation: 116

In addition to Layonez's answer, you have to create a NumberingFormat to any decimal places format that you wish to use. For example:

NumberingFormats numberingFormats = new NumberingFormats(
    new NumberingFormat { NumberFormatId = 165, FormatCode = "0.0" }, // one decimal place
    new NumberingFormat { NumberFormatId = 166, FormatCode = "0.00" }, // two decimal places
    new NumberingFormat { NumberFormatId = 167, FormatCode = "0.000" }, // three decimal places
);

After that, you have to create a CellFormat to every NumberingFormats:

CellFormats cellFormats = new CellFormats(
    new CellFormat() { NumberFormatId = UInt32Value.FromUInt32(165), ApplyNumberFormat = BooleanValue.FromBoolean(true) }, // index 0: one decimal place
    new CellFormat() { NumberFormatId = UInt32Value.FromUInt32(166), ApplyNumberFormat = BooleanValue.FromBoolean(true) }, // index 1: two decimal places
    new CellFormat() { NumberFormatId = UInt32Value.FromUInt32(167), ApplyNumberFormat = BooleanValue.FromBoolean(true) }, // index 2: three decimal places
);

With this, you can create cells with decimal values and specify decimal places:

// one decimal place
new Cell() { CellValue = new CellValue(1.2m), DataType = CellValues.Number, StyleIndex = 0 };

// two decimal place
new Cell() { CellValue = new CellValue(1.25m), DataType = CellValues.Number, StyleIndex = 1 };

// three decimal place
new Cell() { CellValue = new CellValue(1.253m), DataType = CellValues.Number, StyleIndex = 2 };

Remembering that you must inform value in "new CellValue()" keeping it as decimal. If you change to another format (ex: string) you'll lose decimal places format.

Upvotes: 1

layonez
layonez

Reputation: 1785

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

Upvotes: 7

Related Questions