C# - Excel.Range, Workbook and Worksheet...

[haiaw]

Hi
I have:

while (((Excel.Range)matrix.Cells[currentGoalRow, firstGoalColumn]).Value2 != null)
{
Now I want to print out the name of column and row where the null occured
}

The result should be : "I found null in column AB row 34"

Anybody know how to do that?

[poochi]

1. Get the used range
2. Call "get_Value()" method on the range to get all the values in a single shot , otherwise you will have performance problem.
3. Loop thro' the array (#2 output). If you find null value then construct the column name manually


1. Getting used range

It is better to use the last cell special cell (xlCellTypeLastCell) to find the used range. The other solutions I tried never worked for me.

Here is an example

private Excel.Range GetUsedRange(Excel.Worksheet excelSheet, Excel.Range allCells, out int rowCount, out int columnCount)
{
	Excel.Range rangedCells = null;
	rowCount = 0;
	columnCount = 0;

	if (allCells != null)
	{
		Excel.Range lastCell = null;

		try
		{
			// Get the last cell to find out the number of
			// columns and rows
			lastCell = allCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, missingValue);

			// Get the last row and column information
			rowCount = lastCell.Row;
			columnCount = lastCell.Column;

			// we don't need last cell reference anymore. Release it here.
			Marshal.ReleaseComObject(lastCell);
			lastCell = null;

			if ((rowCount > 0) && (columnCount > 0))
			{
				// find the used range
				// User range string should be something like "A1:D4" etc. 
				// To find the last cell index, we do the following thing.
				string lastColumn = GetLastColumnName(columnCount);

				string usedRange = "A1:" + lastColumn + rowCount.ToString();
				rangedCells = excelSheet.get_Range(usedRange, missingValue);
			}
		}
		finally
		{
			// release the COM objects
		}
	}
	return rangedCells;
}

private string GetLastColumnName(int lastColumnIndex)
{
	string lastColumn = "";

	// check whether the column count is > 26
	if (lastColumnIndex > 26)
	{
		// If the column count is > 26, the the last column index will be something 
		// like "AA", "DE", "BC" etc

		// Get the first letter
		// ASCII index 65 represent char. 'A'. So, we use 64 in this calculation as a starting point
		char first = Convert.ToChar(64 + ((lastColumnIndex - 1)/26));

		// Get the second letter
		char second = Convert.ToChar(64 + (lastColumnIndex%26 == 0? 26 : lastColumnIndex%26));

		// Concat. them
		lastColumn = first.ToString() + second.ToString();
	}
	else
	{
		// ASCII index 65 represent char. 'A'. So, we use 64 in this calculation as a starting point
		lastColumn =  Convert.ToChar(64 + lastColumnIndex).ToString();
	}
	return lastColumn;
}

2. Getting values from the used range.

object[,] values = rangedCells.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault) as object[,];

3. Getting the column name.

use the same "GetLastColumnName" method used in #1 to get the column name.