Finding table boundaries in Excel using C#

Question

I am trying to create a program in C# that reads in an Excel file, stores the information in memory, and then have the ability to perform calculations on that information.

Right now, I'm able to read in the information though I'm hard-coding how many rows and columns exist. The problem is that I may get an Excel sheet that consists of a 2 by 2 table and another Excel sheet that is a 500 by 300 table. I'm trying to create a method that will find out what the table's boundaries are by finding the first NULL cell in the "date" column and the header column.

I found a bit of code that looks like Convert.ToString((object)dateCol) == "" to try and find the first NULL cell. Unfortunately (in an if statement) that bit of code seems to be targeting all the cells whether they are NULL or not. Same problem occurs if I try to do a straight comparison (if (dateCol == null)).

Thanks, Jesse

What toolkit/library are you using to read and process the Excel file? There's not quite enough information in your question to know what to suggest. If you're using interop, then just use .Rows.Count/.Columns.Count on the sheet object. — Clinton Pierce

Jesse Smothermon Jesse Smothermon · Accepted Answer · 2011-02-10T20:08:26

I got it working. Here's the code for people who are curious.

public static void findingTableBounds()
    {
        for (int column = 1; column < currentRow; column++)
        {
            double? dateCol;
            dateCol = ((Excel.Range)workSheet.Cells[currentRow, 1]).Value2;
            if (dateCol == null)
            {
                Console.WriteLine("Total Row: {0}", totalRow);
                currentRow = 2;           
            }
            else
            {
                currentRow++;
                totalRow++;
            }
        }


        for (int row = 1; row < currentCol; row++)
        {
            double? headerRow;
            headerRow = ((Excel.Range)workSheet.Cells[1, currentCol]).Value2;
            if (headerRow == null)
            {
                Console.WriteLine("Total Column: {0}", totalCol);
                currentCol = 2;
            }
            else
            {
                currentCol++;
                totalCol++;
            }
        }
    }

Note I made the currentRow and currentCol equal to 2 so that I could start reading the values from the table once I found the dimensions. It's not entirely necessary to do that, you can easily use other variables but I decided to use these variables later.

UPDATE With the help of other coders I got a bit of code for VB that automatically finds the last row of a table. The code is below:

Dim totalRow As Integer

totalRow = Range("A1").End(xlDown).Row

Seeing how there are methods in VB for this, it makes me feel like there might be methods in C# to do the same thing... I just don't know what they are. Anyway the "End" method can also be used for finding the left, upper, and right boundaries as well, the Row method seems to extract the current row that the cursor in excel is on (here the cursor jumps to the last row because of the End method)

Jesse

Finding table boundaries in Excel using C#

1 Answers