AutoFilter and Copy filtered data in Excel file to other range using Aspose.Cells for .NET C# API

carickhoffr · January 16, 2009, 1:36pm

Hello,

I am using AutoFilter to filter about 60 rows of data to about 11 (that are not blank). The code is below.

sht.AutoFilter.Range = "K1:N1"

sht.AutoFilter.MathcNonBlanks(0)

The rows filter fine. Now, I want to copy columns K through N for these filtered rows to another range in the spreadsheet (P through S). How do I copy just these filtered rows? I tried looping through rows and using GetRowHeight figuring that they are hidden and their row height would be 0 but they are not.

Thanks,

Rich

amjad.sahi · January 16, 2009, 11:40pm

Hi,

Well, I tested your scenario a bit and it works fine. I can get 0 value for the hidden rows. Which version of Aspose.Cells you are using? Could you try the attached version.

If you still find the issue, kindly post your template file, complete sample code and output file here, we will check it soon.

Thank you.

carickhoffr · January 20, 2009, 10:58am

Here is my code. I attached test.xls. When the code gets to the GetRowHeight loop, the first visible cell should be cell 11 but all cells still have a height >0. I attached test.xls.

wb.Open("test.xls")

Dim sheet As Aspose.Cells.Worksheet = wb.Worksheets("Sheet1")

sheet.AutoFilter.Range = "K1:N1"

sheet.AutoFilter.MathcNonBlanks(0)

Dim intCounter As Integer = 1

For iRow As Integer = 1 To 60

' Copy data only if row not hidden by filter

If sheet.Cells.GetRowHeight(iRow) <> 0 Then

sheet.Cells.CreateRange("K" + CStr(iRow), "N" + CStr(iRow)).Copy(sheet.Cells.CreateRange("P" + CStr(intCounter), "S" + CStr(intCounter)))

intCounter = intCounter + 1

End If

nausherwan.aslam · January 20, 2009, 12:12pm

Hi,

Thank you for considering Aspose.

Well, you need to use the sheet.AutoFilter.Refresh() method after setting the auto-filter options to hide / un-hide the rows. Please see the modified code,

Sample Code

'Instantiating a Workbook object

Dim wb As Workbook = New Workbook()

wb.Open("C:\test.xls")