How to apply a filter to a excel sheet and copy the resulting rows to another sheet

Aspose.Cells Product Family
1

Hi,
How to apply a multiple filters to a excel sheet and copy the resulting rows to another sheet using Aspose C#. If any example would be grateful. Thanks in advance.

2

@kiran5388,

See the following sample code for your needs for your reference:
e.g.
Sample code:

//Create a new workbook
            Workbook workbook = new Workbook();

            //Get the first worksheet in the workbook
            Worksheet sheet = workbook.Worksheets[0];

            //Get the cells collection in the sheet
            Cells cells = sheet.Cells;

            //Put some values into cells
            cells["A1"].PutValue("Fruit");
            cells["B1"].PutValue("Total");
            cells["A2"].PutValue("Apple");
            cells["B2"].PutValue(1000);
            cells["A3"].PutValue("Orange");
            cells["B3"].PutValue(2500);
            cells["A4"].PutValue("Bananas");
            cells["B4"].PutValue(2500);
            cells["A5"].PutValue("Orange");
            cells["B5"].PutValue(1000);
            cells["A6"].PutValue("Grape");
            cells["B6"].PutValue(2000);           

            //Represents the range to which the specified AutoFilter applies
            sheet.AutoFilter.Range = "A1:B6";
            //Add your desired filters
            sheet.AutoFilter.AddFilter(0, "Orange");
            sheet.AutoFilter.Refresh();


            ExportTableOptions options = new ExportTableOptions();
            options.ExportColumnName = true;
            options.PlotVisibleRows = true;

            //Exporting the contents of all visiable rows
            DataTable dataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, options);
            var dtCloned = dataTable.Clone();

            foreach (DataRow row in dataTable.Rows)
            {
                //If the row is not null
                if (!row.IsNull(0))
                    dtCloned.ImportRow(row);
            }


            //Import the datatable in the new  sheet.
            workbook.Worksheets[workbook.Worksheets.Add()].Cells.ImportData(dtCloned,0,0, new ImportTableOptions ());

            workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

3

@kiran5388
And you can copy range to other worksheet as the following codes:
…
sheet.AutoFilter.Refresh();

        Range sourceRange = sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1);
          Range destRnage = workbook.Worksheets[workbook.Worksheets.Add()].Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1);
        PasteOptions pasteOptions = new PasteOptions();
        pasteOptions.OnlyVisibleCells = true;
        pasteOptions.PasteType = PasteType.Default;
        destRnage.Copy(sourceRange, pasteOptions);
        workbook.Save(dir +"dest.xlsx");
4

image.png (3.7 KB)

Thanks for the above solution.
How can I filter the multiple data, as in attached png.
I want to filter “Orange” from column A, “1000” from Column B and “CANADA” from Column C.

So the Result should be Orange 1000 CANADA

5

Is there any better way than this?
sheet.AutoFilter.AddFilter(0, “Orange”);
sheet.AutoFilter.AddFilter(1, “1000”);
sheet.AutoFilter.AddFilter(2, “CANADA”);

And also I want to add the filtered data to the “Filtered Sheet” data sheet already mentioned in the template.

6

@kiran5388,

Do you find any issue using the above code segment, please elaborate?

You may do that easily. Just don’t add new sheet, only get the your “Filtered Sheet” using the line of code:
e.g
Sample code:

Range destRnage = workbook.Worksheets["Filtered Sheet"].Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1);