How to convert from Text to Number via code to EXCEL in C#

Question

I have a program that reads data from a text file and exports it to an excel report.

I have a problem in the transcription of the numbers, in the txt file I have data in this format:

8,000000

this value should be regarded as 8 (number) by the report, so I made a routine to remove this ",000000" and it works, here’s the code

//trim porquê o número vem com alguns espaços 
string content = itensCarac[indexColumnText].Replace(",000000", "").Trim();
planConfig.Cells[indexLine, indexColumn++] = content;

Turns out there are numbers in the report in that format:

4.700,000000

In theory the same logic would work. but Excel sees this number as "4.7" (God knows why), it "turned" the point into a comma and killed the 00.

If I don’t apply this logic to the rows where there is a floating point, excel treats them as text, and converting excel to number solves the problem. How the image below shows

That is the question:

How to do this conversion from text to number via code in the correct way?

OBS:

I’ve tried using the

NumberFormat = "0.0";

or the

NumberFormat = "0";

and the results were "4.7"

Answer 1

Use Double.Parse with CultureInfo("pt-br"), if you always have numbers in that column.
Or Double.TryParse if you are not sure if there will always be a number in this column.

In your case your code can look like this, nor need Trim or replace :

string content = Double.Parse(itensCarac[indexColumnText], cultura);
planConfig.Cells[indexLine, indexColumn++] = content;

Below I made a code with the first example "8,000000" and with "4,700,000000". I also added another "4,700,100000" case and two more examples using Tryparse instead of Parse.

using System;
using System.Globalization;//Para o CultureInfo

public class Program
{
    public static void Main()
    {           
        //Setei a cultura para Português Brasil.
        CultureInfo cultura = new CultureInfo("pt-br"); 

        //Teste com 8,000000
        double valor8 = Double.Parse("8,000000", cultura);
        Console.WriteLine(valor8);

        //Teste com 4.700,000000
        double valor4_7 = Double.Parse("4.700,000000", cultura);        
        Console.WriteLine(valor4_7);

        //Teste com 4.700,100000
        double valor4_7_1 = Double.Parse("4.700,100000", cultura);
        Console.WriteLine(valor4_7_1);

        //Teste com TryParse 1.230,15
        double x=0;     
        bool testeEhNumerico = Double.TryParse("1.230,15",NumberStyles.Any,cultura, out x);                     
        Console.WriteLine(x);
        Console.WriteLine(testeEhNumerico);

        //Teste com TryParse caso não venha um número 
        double y=0;     
        bool testeNaoNumerico = Double.TryParse("Não sou numérico!",NumberStyles.Any,cultura, out y);   
        Console.WriteLine(y);
        Console.WriteLine(testeNaoNumerico);

    }
}

The results are :

8
4700
4700.1
1230.15
True
0
False

See the example working with comments on https://dotnetfiddle.net/f0u3io

I tried a very playful code, I hope it’s clear.

Answer 2

I’m not sure if it will help you, but there’s something in the stackoverflow in English: How can I Convert String to Int?

Traduindo is something +- like this:

He tells you to try this:

int x = Int32.Parse(variável);

or in a better way: (Which I believe according to your goal would not be interesting but it’s good to know)

int x = 0;
Int32.TryParse(variável, out x);

Where Int32.TryParse returns a bool which can be used in an if

Returns a bool you can its Return value to make decisions about the Results of the Parsing Attempt:

int x = 0;

if (Int32.Tryparse(Textboxd1.Text, out x)) { // you know that the Parsing Attempt // was Successful }

The Tryparse method is like the Parse method, except the Tryparse method does not throw an Exception if the Conversion fails. It eliminates the need to use Exception Handling to test for a Formatexception in the Event that is invalid and cannot be successfully Parsed. - MSDN

Translating. The Tryparse method is similar to Parse, except that the Tryparse does not throw on any Exception when the conversion fails. This eliminates the need to control exceptions and try to unravel through the formats of these exceptions when the action fails.

Remember you also have the Convert.ToInt

Convert.ToInt16(variável);
Convert.ToInt32(variável);
Convert.ToInt64(variável);

Link to the Microsoft website at English informing on the Convert and in Português

Answer 3

You must be using Excel in Portuguese, but the data is processed originally in English, thus, 4.700,000000 is for Excel (as it is sending the data) equal to 4.7, because in English the symbol for separation of thousands is the comma (disregarded in this case because it comes after the point), and the dot is the decimal separator.

The suggestion is you replace the comma characters in place of a dot and see-verse before sending to Excel, sending the example number in this way:

4,700.000000 (form in English)

Doing this will work!

If send as text, send it like this "=4,700.000000", that Excel will treat as value if the cell is formatted for value, and will automatically switch to notation in Português.