How to convert Text values to number values in Excel 2003 (Number stored as Text), using C#

Question

There appear to be a number of suggestions to do this, non of which appear to work.

Effectively, I'm wanting to change a text value in an Excel sheet to a number (this is a cell that has been set as a number stored as text, and has a green diamond next to it).

This webpage details how to resolve the issue in Excel, through the UI, and I've recorded this as a macro below (but that's VBA)...

Including setting the value to itself:

                Range allCellsRng;
                string lowerRightCell = "AZ500";
                allCellsRng = wSheet.get_Range("A1", lowerRightCell).Cells;
                foreach (Range cell in allCellsRng)
                {
                    if (cell.Value2.ToString().Length > 0)
                    {
                        cell.Value2 = cell.Value2;
                    }
                }

This is a recorded VB Macro, that shows what will resolve the issue, but I'm having problems representing this in C#:

ActiveCell.FormulaR1C1 = "0"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select

Answer 1

Would it not be better to change the format of the cell instead? I believe you should be able to grab the format using

range.NumberFormat

and change that to the actual format you want... You could then set the range for an entire column, or whole sheet.

Answer 2

Ok, so raising it as a question on here triggered a brainwave. This looks to work:

Range cellA1 = wSheet.get_Range("A1", System.Type.Missing);
cellA1.Value2 = "0";
cellA1.Copy(System.Type.Missing);
Range cellAll = wSheet.get_Range("A1:AZ500", System.Type.Missing);
cellAll.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationAdd,
        false, false);

Answer 3

With Clear Office, the code is very easy:

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName);
foreach (Worksheet worksheet in spreadsheetDocument.Workbook.Sheets.OfType<Worksheet>())
{
    foreach (Cell cell in worksheet.GetRange("A1:AZ500"))
    {
        string s = cell.Value as string;
        if (s == null)
            continue;
        double d;
        if (double.TryParse(s, out d))
            cell.Value = d;
        }
    }
spreadsheetDocument.Save();