CODEWITHSUNDEEP
c#exceloffice-interopexcel-interop
Evaldas B
Evaldas B

Reputation: 2594

Excel Interop read only filtered rows

Original non filtered table

Original non filtered table

Filtered table

enter image description here

I am trying to read a .xlsx file using Interop.Excel. The xlRange variable seems to have a strange behavior, when I set it to display only filtered cells(visible):

Excel.Range xlRange = xlWorksheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);

Debugging:

When table is not filtered:

xlRange.Count: 15 //Total amount of elements in the table

rowCount: 5 //Thats including header

When table is filtered:

xlRange.Count: 9 //This is correct

rowCount: 1 //This should be 3 (including header)

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(Directory.GetCurrentDirectory() + "\\Example.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        //new line
        if (j == 1)
            Console.Write("\r\n");

        //write the value to the console
        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
            Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
    }
}

Having in mind that xlRange.Count is 9 I should be able to access all 3 rows manually disregarding the rowCount variable but xlRange seems to be the same original non filtered range:

Console.WriteLine(xlRange.Cells[1, 1]);//Writes ID, Correct
Console.WriteLine(xlRange.Cells[2, 1]);//Writes 1, Should be 2
Console.WriteLine(xlRange.Cells[3, 1]);//Writes 2, Should be 4
Console.WriteLine(xlRange.Cells[4, 1]);//Writes 3, Should not be able to acces this element element at all because xlRange.Count is 9

Upvotes: 3

Views: 991

Answers (1)

Hambone
Hambone

Reputation: 16397

I suspect what you want is to iterate the .Rows property instead of being literal about the row/column. Something like this:

foreach (Excel.Range row in xlRange.Rows)
{
    for (int j = 1; j <= colCount; j++)
    {
        //write the value to the console
        if (row.Cells[1, j] != null && row.Cells[1, j].Value2 != null)
            Console.Write(row.Cells[1, j].Value2.ToString() + "\t");
    }

    Console.WriteLine();
}

When you specify a row, column within a range I suspect it will go to that exact row (relative to the range).

Give it a try and let me know.

Upvotes: 5

Related Questions