CODEWITHSUNDEEP
c#.netexcelexport-to-excelnpoi
Jitender Dhaliwal
Jitender Dhaliwal

Reputation: 56

NPOI C# Cell Format Warning(Number Stored As Text)

I'm trying to generate a excel file(.xlsx) using C# in visual studio 2019, using the NPOI library. I ran into the issue where the data inserted is format incorrectly.

I have a field which can have both numbers and text, when it is displayed in the excel the cell gives warning "Number Stored As Text" only on number fields like in the image attached.

I tried following methods to make cell a string cell.

  1. By giving Style to the cell.
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("text");
  1. By settng cell Type
ICell cell = row.CreateCell(cellIndex);
cell.CellStyle = cellStyle;
cell.SetCellType(CellType.String);
cell.SetCellValue(itemData.PropertyName.ToString());

Still I get same warning in generated excel file

enter image description here

Any help is appreciated.

Upvotes: 1

Views: 2620

Answers (2)

Luis Linares
Luis Linares

Reputation: 1

After investigate some answers on the web, i realized that funcion cell.SetCellValue has multiple overloadings to set different value types (int, double, string, DateOnly, DateTime, Bool). For example. If you send a numeric value as a string (number.ToString()), the function writes the num as a string on the cell, causing the described behaviour on the question, but if you send an integer or a double without converting it to string, then the function writes the value as number.

Upvotes: 0

AlanK
AlanK

Reputation: 1949

This is correct and valid behaviour. The Excel message is informational, not an error. It is telling you that you HAVE managed to store a text string consisting of only numeric characters. Your options in Excel are to convert it to a number or ignore the "error".

enter image description here

If you wanted the value stored as a number then you have to NOT make the cell type string, doing something like this:

  if (itemData.PropertyName.ToString().Any(c => !char.IsNumber(c)))
    cell.SetCellType(CellType.String);
  else
    cell.SetCellType(CellType.Numeric);

This might be of use: NPOI Cell Formatting Link

Upvotes: 2

Related Questions