C# Convert Number stored as Text to Number when exporting to Excel

Question

I export data from datagrid to Excel and one column with numbers is with error 'numbers stored as text'. I am trying to convert them using following code, but its not working. Could you suggest?

The problem is that then column headers are moved up and data is broken.

  worksheet.Range["E5", "E5"].EntireColumn.TextToColumns();
  worksheet.Columns[5].NumberFormat = "# ##0,00";

Problem shown

Store *numbers* not text to begin with. You aren't exporting to Excel with this code, you're using Excel Interop to actually start Excel and set values to fields. Instead of storing text to those values, store numbers - ints, decimals, doubles — Panagiotis Kanavos, Dec 12 '19 at 08:11
You didn't post the code that actually sets the values though — Panagiotis Kanavos, Dec 12 '19 at 08:11
This link may help you. [cell in Excel “Number stored as text”](https://stackoverflow.com/a/7919303/8335151) — 大陸北方網友, Dec 13 '19 at 01:58

score 1 · Answer 1 · answered May 11 '20 at 05:59

This code converts numbers stored in text format to number format. If you want to use it to opposite direction set the fieldinfo to { 1, 2 }.

Range rng = Worksheet.Range["A1:A10""];

rng.TextToColumns(rng, XlTextParsingType.xlDelimited, XlTextQualifier.xlTextQualifierDoubleQuote, Tab: true, FieldInfo: new int[] { 1, 1 }, TrailingMinusNumbers: true);

C# Convert Number stored as Text to Number when exporting to Excel

1 Answers1