Excel Range in C# using interop.Excel. Range both empty and not empty?

Question

I have a Excel sheet that looks like this. |A1|B1| "BLANK" |D1|E1|F1| I.e. only the first row is populated and the third column is blank. I parse this in C# using interop.excel in the following way:

        Excel.Application exApp = OpenExcel();
        String mySheet = @"C:\c#\rapport.xlsx";
        Excel.Workbook wb = exApp.Workbooks.Open(mySheet);
        Excel.Worksheet ws = wb.Sheets[1];
        Excel.Range row = ws.Rows[1];

I create a new range only containing the non-empty cells in row 1 by

        Excel.Range rng = row.SpecialCells(Excel.XlCellType.xlCellTypeConstants);

Now: if I use rng.Select all the non-empty cells in my sheet is selected. And if I use rng.Count it will return 5, which is the number of non-empty cells in my sheet. But still when I print cell by cell using:

Console.WriteLine(rng[1,i].Value.ToString());

It shows that rng[1, 1-2] contains "A1 and B1", this is nice. But rng[1, 3] is empty or null. And rng[1, 4-7] contains D1-F1.

How is this possible?

My main goal is to store all non-empty values in a string array. But I can't select the right values because my range rng is both empty and non-empty is some weird way.

Answer 1

Try using:

ws.Columns.ClearFormats();
ws.Rows.ClearFormats();

to be sure your Range excludes formatted but empty cells.

Another try could be to get :

Excel.Range rng = row.SpecialCells(Excel.XlCellType.xlCellTypeConstants,Type.Missing);

Answer 2

This solved it! However I didn't use the .Item method to store the values as Rory suggested suggested. Thnx Rory!

        string[] str = new String[rng.Count];
        int i = 0;

        foreach (Excel.Range cell in rng.Cells)
        {
            str[i] = cell.Value.ToString();
            i++;
        }

        for (int j = 0; j < str.Length; j++)
            Console.WriteLine(str[j]);