How to read Excel cell value?

Question

Looking for help on how to read a few cell values from Excel. I thought I'd start with one.

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false,    Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
var cell = (Excel.Range)excelWorksheet.Cells[1, 1];

Though I am having no luck. I want to be able to read the value of the cell and store it somewhere, and then have it check every few seconds for the value.

Answer 1

You seriously want to take a look at EPPLus: http://epplus.codeplex.com/

It's available from NuGet for usage in your projects. It's by far the most easy and clean way of interfacing between C# and an excel document. It's also free, btw.

Tutorial: http://www.codeproject.com/Articles/680421/Create-Read-Edit-Advance-Excel-Report-in#1

private DataTable WorksheetToDataTable(ExcelWorksheet oSheet)
{
int totalRows = oSheet.Dimension.End.Row;
int totalCols = oSheet.Dimension.End.Column;
DataTable dt = new DataTable(oSheet.Name);
DataRow dr = null;
for (int i = 1; i <= totalRows; i++)
{
    if (i > 1) dr = dt.Rows.Add();
    for (int j = 1; j <= totalCols; j++)
    {
        if (i == 1)
            dt.Columns.Add(oSheet.Cells[i, j].Value.ToString());
        else
            dr[j - 1] = oSheet.Cells[i, j].Value.ToString();
    }
}
return dt;

}

This reads all the columns and rows in EPPLus.

Answer 2

You need to retrieve the .Value or .Value2 property of the cell:

if (cell.Value2 == null)
    // cell is blank
else if(cell.Value2 is string)
    // cell is text
else if (cell.Value is double)
    // cell is number;
else if (cell.Value2 is double)
    // cell is date

Note the last two if's. .Value2 will return a double for both numbers and dates, but .Value will return a double only for numbers. The order that you check is important.

Answer 3

Something like:

Worksheet sheet = excelWorkbook.Worksheets["Sheet1"] as Worksheet;
Range rng = sheet.Cells[1,1] as Range;
object value = rng.Value;

...should do what you want. Try this and if you get any errors let me know.

The value will either be a string, double, DateTime, bool or int value.

Aside from the ints, these are quite self-explanatory. The int values represent the different error codes that are possible, e.g. #VALUE!

(Incidentally, rather than referencing the Excel PIA, I would recommend usingn NetOffice, which is available free via NuGet, to reference the Excel object model. Advantage being that you are not tied to a specific version of Excel when you deploy)