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")
};
Comments
-
Mauro Bilotti almost 2 years
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:
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:
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?