question

sreeprasad-9894 avatar image
0 Votes"
sreeprasad-9894 asked RLWA32-6355 commented

How to get correct Row count of filtered excel in c# interoperobility

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);

This line filtering but not giving correct counts of filtered row

what i have tried:

 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);
    
         }
dotnet-csharpdotnet-cli
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@sreeprasad-9894, Based on your description, I need to confirm some questions with you. First, I noted that you didn't define the filteredRange in your code, where it comes from? Second, Could you provide your example excel file? It will be better for us to analyze your problem.

0 Votes 0 ·

1 Answer

RLWA32-6355 avatar image
0 Votes"
RLWA32-6355 answered RLWA32-6355 commented
· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

this is not working for me :(

0 Votes 0 ·

This was my simple test -

Excel sheet before filtering -

156824-beforefilter.png

Test code snippet -

                 _Application xlApp = (_Application) Marshal.GetActiveObject("Excel.Application");
                 _Worksheet xlSheet = (_Worksheet) xlApp.ActiveSheet;
                 Range r = xlSheet.UsedRange;  //("A1", "C11");
                 var af = r.AutoFilter(3, "A");
                 int afrows = xlSheet.AutoFilter.Range.Columns[1].SpecialCells(XlCellType.xlCellTypeVisible).Cells.Count - 1;
                 Console.WriteLine("Filtered range contains {0} rows", afrows);

Excel sheet after filter applied -

156788-afterfilter.png

Program output -

156835-results.png


0 Votes 0 ·
beforefilter.png (8.0 KiB)
afterfilter.png (5.2 KiB)
results.png (4.4 KiB)

'object' does not contain a definition for 'SpecialCells' and no extension method 'SpecialCells' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
var af = xlRange.AutoFilter(17, "01");
int afrows = xlWorksheetNew.AutoFilter.Range.Columns[1].SpecialCells(Excel.XlCellType.xlCellTypeVisible).Cells.Count - 1;



0 Votes 0 ·
Show more comments