Sujeet Raman

Sujeet Raman

  • 859
  • 927
  • 357.5k

How to get correct count of filtered excel in c# interop

Dec 11 2021 9:55 AM

Issue is my code always return one row instead of 13. I need to create a text file based on the filtered rows.But i am not getting the correct count and filtered rows.I will add and remove other clms .but row has issue.I need to get rows having colum value 01

 xlRange.AutoFilter(17, "01", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);

filtering but not giving correct counts of filtered row

public static void ExportToTextFile(Excel._Worksheet xlWorksheet, Excel.Workbook xlWorkbook, Excel.Application xlApp, string wbname)
{
    //creating .txt file with new coloumns
    Excel._Worksheet xlWorksheetNew = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[1]; // excel sheet
    xlWorksheet.Copy(xlWorksheetNew); //copying from another xl
    Excel.Range excelRange = xlWorksheetNew.UsedRange;
    xlWorksheetNew.Cells[1, 18] = "FLAG";
    Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheetNew.UsedRange;
  xlRange.AutoFilter(17,"01",Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);
    int Columns = xlWorksheetNew.UsedRange.Columns.Count;
    int Rows = filteredRange.Rows.Count; //NOT GIVING CORRECT COUNT
    ((Excel.Range)xlWorksheetNew.Columns["P:Q"]).Delete();
    ((Excel.Range)xlWorksheetNew.Columns["C"]).Delete();
    ((Excel.Range)xlWorksheetNew.Columns["A:B"]).Delete();
    int AfterdeletColumns = xlWorksheetNew.UsedRange.Columns.Count;
    int afterdeletRows = filteredRange.Rows.Count;
     xlWorksheetNew.Visible = Excel.XlSheetVisibility.xlSheetHidden;
    ExportToTextFile(Rows, Columns, xlWorksheetNew);
}

Answers (2)