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.

Numidia

I know this is old, but in addition to the answers turn off these events, populate worksheet, then turn them back on: currentInstance.EnableEvents = false/true; currentInstance.ScreenUpdating = false/true; – Thwack

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

Mccreery

The cast in int doesn't work, because CountA return a double. – Telepathist

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

Toombs

Thanks James, I am working on a VSTO add-in for excel and often run into issues that can be quickly answered by someone in the know, but time consuming to find on the internet. I'll be sure to make more use of stackoverflow since there's so many VSTO experts out here! – Numidia

@coder4life, do you know if there's a way to do the same "bulk" fetching for properties like cell color? I've used a method just like you described for values for months now, but I've yet to find a way to be able to fetch all of the colors (range.Interior.Color) without going row-by-row, col-by-col. And its painfully slow! Thanks! – Jeannettajeannette

Actually, I opened a new thread for this color-fetching question, just so it's easier to find for other people, rather than looking through comments. The url is https://mcmap.net/q/1494376/-getting-excel-cell-properties-in-bulk – Jeannettajeannette

Doesn't this imply that one knows the range to get? Also doesn't this imply to over-get what is needed at times. I use what you are saying for both reading and writing, but this comment though valid doesn't help with the question at hand. Or am I missing something? – Molech

Answer 3

I found the following solution, which is also instantaneous but I'm not sure how accurate it is... it has passed all my tests thus far.

Here it is for anyone who wants to know:

Worksheet sheet = (Worksheet)this.Application.ActiveSheet;
Range usedRange = sheet.UsedRange;
bool isUsed = (usedRange.Count > 1);
if (usedRange.Count == 1)
{
  isUsed = (usedRange.Value2 != null) &&
           (!string.IsNullOrEmpty(usedRange.Value2.ToString()));
}

if(isUsed)
{
  // worksheet cells not empty
}

I suppose this is a lot simpler than blowing up the clipboard every time I do the check or counting all the non-empty cells in the worksheet. Thanks Mikael and Mike, I appreciate both your answers.

Numidia

momad, your approach is not bad, but it is (a) more work, and (b) will incorrectly report some worksheets as containing data when it does not, because the UsedRange can be affected by such things as formatting in the cells, not just values. If you make use of the Application.WorksheetFunction.CountA method, instead, you would pickup all data and only data in one line, instead of five. – Mccreery

Both are much better than using the clipboard for sure. The clipboard way will also take into account cells with "white space", which the other ways won't, but that's a special case :) – Wesleywesleyan

Mike, I didn't know about the formatting. If that's the case, then I definately agree that the worksheet function CountA is a better approach. However, in my specific scenario, I don't want to want to override a cell even if the change is only a formatting change. Thanks for the clarification! – Numidia

Shouldn't IsNullOrEmpty be IsNullOrWhiteSpace? If you take an empty cell and call String.Length(cell.ToString()) i think it returns 18 even though the actual cell is and has always been empty – Dawnedawson

Answer 4

How about?

public static bool IsSheetEmpty(int sheetNo)
{
    bool isEmpty = false;

    if (sheetNo <= Globals.ThisAddIn.Application.Worksheets.Count)
    {
        Worksheet ws = Globals.ThisAddIn.Application.Worksheets[sheetNo];

        if (ws.UsedRange.Address.ToString() == "$A$1" && String.IsNullOrWhiteSpace(ws.get_Range("A1").Value2))
        {
            isEmpty = true;
        }
    }
    else
    {
        // or add your own error handling when sheetNo is not found
    }

    return isEmpty;
}

Example call

bool isFirstEmpty = IsSheetEmpty(1);

Dawnedawson

Answer 5

This should be pretty fast:

    private void CheckForContent()
    {
        Worksheet activeSheet = ActiveSheet;
        var range = activeSheet.get_Range("A1", GetExcelColumnName(activeSheet.Columns.Count)+activeSheet.Rows.Count.ToString() );
        range.Select();
        range.Copy();
        string text = Clipboard.GetText().Trim();
        if(string.IsNullOrEmpty(text))
        {
            MessageBox.Show("No text");
        }
    }

    private string GetExcelColumnName(int columnNumber)
    {
        int dividend = columnNumber;
        string columnName = String.Empty;
        int modulo;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
            dividend = (int)((dividend - modulo) / 26);
        }
        return columnName;
    }

Wesleywesleyan

select and copy are never fast ;) – Dawnedawson