In VSTO Excel, how to detect data in cells?

Question

A process to quickly detect whether there is data in a given worksheet or not, without actually looping through all of the rows/columns of the worksheet.

For my current process, I am currently looping through the entire sheet, and there is some noticeable lag-time in my import.

Answer 1

To avoid looping and take advantage of nearly instantaneous execution speed, you can use the Excel.WorksheetFunction.CountA method, which returns the same result as the =CountA() worksheet function.

Assuming that your Excel.Application reference is named 'excelApp' and your Excel.Worksheet reference is named 'worksheet', you can use code like the following in C# 4.0:

// C# 4.0
int dataCount = (int)excelApp.WorksheetFunction.CountA(worksheet.Cells);

if (dataCount == 0)
{
    // All cells on the worksheet are empty.
}
else
{
    // There is at least one cell on the worksheet that has non-empty contents.
}

In C# 3.0 and below, it's a bit more verbose, because you have to explicitly provide the missing optional arguments:

// C# 3.0 and below
int dataCount = (int)excelApp.WorksheetFunction.CountA(
    worksheet.Cells, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

if (dataCount == 0)
{
    // All cells on the worksheet are empty.
}
else
{
    // There is at least one cell on the worksheet that has non-empty contents.
}