CODEWITHSUNDEEP
c#excelautomation
subprime
subprime

Reputation: 1215

Get absolute offset for UsedRange

i have a question about automation of excel with C# and i hope you can help me.

Description:
I must find a specific cell in a UsedRange in the current worksheet with a specific value. Therefor i wrote two methods. The first one get the UsedRange from the excel worksheet and the second store the position of founds in a list with row/col in relation of the UsedRange.

The problem:
If the file has empty rows or columns to begin of excel sheet than i get know the absolute position of this specific value in worksheet... How i get that position? 

public object[,] GetUsedRangeFromWorksheet(Worksheet worksheet)
{
  if (worksheet == null)
    throw new ArgumentNullException();

  Range usedRange = null;
  object[,] usedRangeValues = null;

  usedRange = worksheet.UsedRange;
  usedRangeValues = (object[,])usedRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

  return usedRangeValues;
}

public IList<Tuple<int, int>> FindPositionsOfValue(string name, object[,] usedRange) 
{
  if (name == null || name == string.Empty)
    throw new ArgumentNullException();
  if (usedRange == null)
    throw new ArgumentNullException();

  int iRowMax = usedRange.GetLength(0);
  int iColumnMax = usedRange.GetLength(1);
  IList<Tuple<int, int>> lFounds = new List<Tuple<int, int>>();

  for (int iRow = 1; iRow < iRowMax; iRow++)
  {
    for (int iColumn = 1; iColumn < iColumnMax; iColumn++)
      if(usedRange[iRow, iColumn] != null)
        if (usedRange[iRow, iColumn].ToString() == name)
          lFounds.Add(Tuple.Create(iRow, iColumn));
  }

  return lFounds;
}

Upvotes: 0

Views: 1251

Answers (1)

Andy G
Andy G

Reputation: 19367

I am using ActiveSheet in the following code as I don't like the argument-name worksheet - this will only cause confusion, and possibly conflict (if you type it with the wrong case by mistake). Make up a more sensible name ;)

ActiveSheet.UsedRange.Row
ActiveSheet.UsedRange.Column

These will return the row and column number of the first cell in the UsedRange. These are the offsets you are looking for. Store them in variables and add or subtract them as necessary.

Explanation: UsedRange returns, as expected, the used-range in a worksheet. However, if the first cell with any content is D4 then the UsedRange will start from this cell, not from A1.

Upvotes: 1

Related Questions