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