1

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.

Jitender Dhaliwal
  • 56
  • 9

1 Answers1

1

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

AlanK
  • 1,827
  • 13
  • 16
  • Thank you for the answer, If I prefix value with apostrophe it shows it in the excel file so it comes as '12345 in excel. – Jitender Dhaliwal Sep 30 '21 at 06:06
  • 1
    @JitenderDhaliwal Please see my updated answer. – AlanK Sep 30 '21 at 06:35
  • Thank you for the explanation, I will keep looking if I can find something to avoid this. – Jitender Dhaliwal Sep 30 '21 at 06:44
  • @JitenderDhaliwal You are getting correct behaviour. I have updated the answer again to show you how you can force Excel to treat numeric text as a number (the opposite of what you asked for); be sure that this is what you want. I am not certain of the enumerable value for `CellType` on the last line (it will be something like `.Number` `.Numeric` or even `.General`) – AlanK Sep 30 '21 at 06:57