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.
}

Answer 2

I work with VSTO and Excel for some time, at a very intense level, so I hope I can share things I've learned over the course of time with you.

Based on the information you've provided, I'd recommend casting to an object array and working with that information instead. Basically you'd access the values as:

object[,] arrayValues = (object[,])ExcelRange.Value2;

arrayValues is a 2D array ([row,column]). Excel populates the array blazingly fast, and of course operations on the array will be very performant (don't worry about the performance of the boxing, it's NOT an issue, believe me).