Convert Excel Cell Value From Text To Number Using C#

I am using Windows Application. In that Application i exported the DataGrid into Excel Successfully... Now the problem is , When i exported from Grid to the Excel Sheet, The cell values are having some green color mark on left top corner in the Excel Sheet... I thought that is type cast problem . How Shall i avoid that Problem.... and How to change the cell value from text to Number ...(i.e)Convert To Number....

Can Anyone tell me the solution of this problem?开发者_JAVA百科

My Code for Formatting That Excel Sheet For Some Range of Values,

wksheet.Range[GetRanges[0].ToString(), GetRanges[GetRanges.Count-2].ToString()].Merge();

wksheet.get_Range(GetRanges[0].ToString(), GetRanges[GetRanges.Count-].ToString()).Interior.Color = Color.FromArgb(192, 0, 0);
                         

I haven't a Windows machine to test on at the moment, but perhaps you would want to try changing the cell format, e.g.:

my_range.NumberFormat = "0.0"; // change number of decimal places as needed

Here's a full example from Microsoft: How to automate Microsoft Excel from Microsoft Visual C#.NET.

The following routine will dynamically fill a spreadsheet from data (text and numbers) in a text file.

The kicker is using an object array to set the values.

StreamReader STRead;
String[] STCells;
object[] cellData;
int temp;
Excel.Range tempRange;
for (int i = 0; i < FileList.Length; i++)
{
    STRead = FileList[i].OpenText();
    int j = 0;
    while (!STRead.EndOfStream)
    {
        STCells = STRead.ReadLine().Split(',');
        cellData = new object[STCells.Length];
        for (int k = 0; k < STCells.Length; k++)
        {
            if (Int32.TryParse(STCells[k], out temp)) 
                cellData[k] = temp;
            else
                cellData[k] = STCells[k];
        }
        tempRange = sheetList[i].get_Range(ReturnCellID(j, 0), 
                                           ReturnCellID(j, STCells.Length - 1));
        j++;
        tempRange.Value2 = cellData;
    }
    STRead.Close();
}

I had this problem with a excel sheet containing both regular numbers and percent (a result of an export of strings from a Crystal Report).

To change all of them at once I made a loop somewhat like this:

//example range
Excel.Range rng = mWorkSheet.get_Range("A1", "H25");
foreach (Excel.Range range in rng)
{
    if (range.Value != null)
    {
        int number;
        bool isNumber = int.TryParse(range.Value.ToString().Trim(), out number);
        if (isNumber)
        {
            range.NumberFormat = "#,##0"; 
            range.Value = number;
        }
        else
        {
            //the percent values were decimals with commas in the string       
            string temp = range.Value.ToString();
            temp = temp.Replace(",", ".");
            range.Value = temp;
        }
    }
}

Result was that both the percentage strings and the numbers got converted into the correct Excel format.

When you assign the value to the cells, try to get all the values in an 2 dimensional array first and then assign the array to the Range. this way its going to be very fast and also i guess the values in the cell will be number. try it out ...hopefully it should work

Change the cell value from text to Number is easy with Excel Jet Cell .NET component.

Cell["A1"].Style.StringFormat = "##.#"; // or "0.0" same as in MS Excel