Cannot convert type 'double' to 'string' for Excel values

Question

I'm loading an Excel file as indicated in many places over the web.

OpenFileDialog chooseFile = new OpenFileDialog();
            chooseFile.Filter = "Excel files (*.xls,*.xlsl)|*.xls;*.xlsx";
            if (chooseFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                selectedFileName = chooseFile.FileName;                
                textBox1.Text = selectedFileName;
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                Workbook wb = excel.Workbooks.Open(selectedFileName);                
                Sheets excelSheets = wb.Worksheets;
                string currentSheet = "Sheet 1";
                excelWorksheet = (Worksheet)excelSheets.get_Item(currentSheet);
                Range usedRange = excelWorksheet.UsedRange;
                Range lastCell = usedRange.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
                lastRow = lastCell.Row;
                lastColumn = lastCell.Column;

                //release com object you won't need.
                Marshal.ReleaseComObject(excel);
                Marshal.ReleaseComObject(wb);

            }

Troubles come when I try to get the single value from a cell with this function:

private string getCellValue(Worksheet Sheet, int Row, int Column)
        {            
            var cellValue = (string)(Sheet.Cells[Row, Column] as Range).Value;            
            return cellValue;
        }

This code works fine for a lot of cells, but suddenly get stuck to one cell raising this exception:

Cannot convert type 'double' to 'string'

This is weird because it perfectly works with all the other cells and converts everything to string. It seems not even to give any trouble with blank cells. I've also specified every cell from the excel file to be "text", but really has no clue for this behavior.

Also tried an explicit conversion in this way.

private string getCellValue(Worksheet Sheet, int Row, int Column)
        {            
            string cellValue = Sheet.Cells[Row, Column].Value.ToString();
            return cellValue;
        }

And now the raised exception is:

Cannot perform runtime binding on a null reference

Solution 1

The Text property can be used to retrieve the text contents from a Cell. So, you could try to use the property as follows:

private string getCellValue(Worksheet Sheet, int Row, int Column)
    {            
        string cellValue = Sheet.Cells[Row, Column].Text.ToString();
        return cellValue;
    }

Solution 2

try this

private string getCellValue(Worksheet Sheet, int Row, int Column)
{
    object cellValue = Sheet.Cells(Row, Column).Value;
    if (cellValue != null) {
        return Convert.ToString(cellValue);
    } else {
        return string.Empty;
    }
}

Solution 3

Try to use this code below:

private string getCellValue(Worksheet Sheet, int Row, int Column)
{            
    string cellValue = Sheet.Cells[Row, Column].Text.ToString();
    return cellValue;
}

Hope it works!

Comments

[sparaflAsh]

Thanks. It works also in this simpler version. var cellValue = (string)(Sheet.Cells[Row, Column] as Range).Text;

[Santiago Villafuerte]

Be cautious when using .Text fastexcel.wordpress.com/2011/11/30/…