In VSTO Excel, how to detect data in cells?

I was wondering if anyone knew how 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 to figure this out.

I am writing an importer that will import data right into the active worksheet (if it has not been modified), or create a new worksheet and import into it instead. I am currently looping through the entire sheet, and there is some noticeable lag-time in my import.

I would appreciate any help in the matter. Thank you!

Answers


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

I think this should do it for you!

Mike


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

HTH, James


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.


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

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

Need Your Help

How can Socket.close() fail (no exception is thrown)?

java sockets

In a multithreaded application, I have one thread which reads data from a socket connection until a reading timeout occurs. It then sets a closing flag, sends itself an interrupt signal and does some

Illustrator exported .svg files won't render in HTML

html5 css3 svg adobe-illustrator

I am not sure if this is a coincidence, but it seems that if I export svg files from Illustrator or even try to use svg files I find on the web that were exported from Illustrator, they don't rende...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.