Need to search row in excelwhich have perticular data

[GUM]

Hi Team,

I am using Java + Aspose 8.5.1.0

My requirement is to get row, who is satisfying the data. Suppose below rows present in excel file, and requirment is get row number, where I get

test8

test9

test10

test1	test2	test3
test4	-	test5
-	test8	test7
test8	test9	test10
-	-	test11
-	test8	test13
test14	test15	test16

Any help for this search. I try the below code, but it search perticular cell data only.

String text = "test";

FindOptions findOptions = new FindOptions();

findOptions.setCaseSensitive(false);

findOptions.setLookInType(LookInType.VALUES);

findOptions.setSeachOrderByRows(true);

Cell foundCell = cells.find(text, null, findOptions);

//If a cell found with the value

if (foundCell != null)

{

int row = foundCell.getRow();

String name = foundCell.getName();

System.out.println(name);

//Your code goes here.

}

[amjad.sahi]

Hi,

Thanks for providing us some details and sample code.

Well, I am afraid, MS Excel only allows to search data present in cell, it does not include multiple cells’ data within the rows/columns by inserting it into “Find What” Text box in one go for its search criteria. If you do find such an option in MS Excel to accomplish your task in MS Excel manually, let us know with details, sample file and steps involved, we will check it soon. Aspose.Cells follows MS Excel standards and specifications, so if Ms Excel does not allow this, Aspose.Cells also cannot accomplish the task in one go. I think you may write your own code to try to search those cells (one by one) using your search criteria (see the document for your reference here: http://www.aspose.com/docs/display/cellsjava/Find+or+Search+Data ) and get the relevant row index/number (by using Cell.getRow()/getColumn method(s)) to store into some arrays/list, so you may find out the underlying rows/cols which contains your desired data into different cells.

Hope, this helps a bit.

Thank you.

[GUM]

Thanks Amjad for your reply.

Does Aspose have any way to search any String in perticular column only like “Sample” should be present in B column only of excel?

[GUM]

I think instead of search, I can go with filter for those values inside perticular columns.

I will try from my side.

If required will revert back to you

Thanks

[GUM]

Hi Amjad,

I tied below code for filtering, but it just select the test8 in first column filter, but didnt get applied on that column. It shows me all rows in worksheet. If I open the 1st col filter then only test8 value is selected. So it means filter get selected but not get applied.

I am expecting one row, but getting all rows with 1st col filter selected value test8.

Does I need to invoke any other method to get apply that selected filter

Workbook workbook = new Workbook(“D:\ASPOSE\Book1.xlsx”);

Worksheet sheet = workbook.getWorksheets().get(0);

sheet.getAutoFilter().setRange(“A1:B1”);

sheet.getAutoFilter().addFilter(0, “test8”);

workbook.save(“D:\ASPOSE\BookFilter.xlsx”);

System.out.println(“Done!!!”);

[amjad.sahi]

Hi,

Could you add the following line of code (in bold) to your code segment if it works fine.

e.g

Sample code:

Workbook workbook = new Workbook(“D:\ASPOSE\Book1.xlsx”);

Worksheet sheet = workbook.getWorksheets().get(0);

sheet.getAutoFilter().setRange(“A1:B1”);

sheet.getAutoFilter().addFilter(0, “test8”);

sheet.getAutoFilter().refresh();

workbook.save(“D:\ASPOSE\BookFilter.xlsx”);

System.out.println(“Done!!!”);

Let us know if you still have any issue.

Thank you.

[GUM]

Thanks Amjad.

It worked for me. But I need that filter row number actually.

I wrote my own logic to get row which have input values by using search. but in case of filter it is less code if we get that filtered row number

Can you please help me in this.

[amjad.sahi]

Hi,

Well, to find the row index/number you need to use Find and Search options provided by Aspose.Cells APIs as you were doing previously. All you need to do is specify your area where the search would be done and you would search the string/text with respect to your find options. Once you find the cell, you will use Cell.getRow() method to get the row index/number of the searched row. See the sample code segment below for your reference.

e.g

Sample code:

…

//Specify the area range. e.g B2 to last data cell in B column;

int startRow = 1;

int startCol = 1;

rowCount = worksheet.getCells().getMaxDataRow();

colCount =1;

FindOptions findOptions = new FindOptions();

findOptions.setRange(CellArea.createCellArea(startRow, startCol, rowCount, colCount));

…

Hope this helps a bit.

Thank you.