How to find the next data using Find option

Aspose.Cells Product Family
1
Hi I am trying to find the data from excel.

My code is showing only one entry. not the all entries

Code:-
int num=cells.getMaxDataRow();
int num1=cells.getMaxDataColumn();

for (int n1=11;n1<num;n1++)
{
for(int nColIndex =0;nColIndex<=num1;nColIndex++)
{
Cell cell1=cells.getCell(n1,nColIndex);
FindOptions findOptions = new FindOptions();
findOptions.setLookinType(FindOptions.SEARCH_BY_COLUMNS);
cell1 = cells.find("Niraj",null,findOptions);
System.out.println(cells.getCell( n1,nColIndex).getValue());
//System.out.println("Name of the cell containing formula: " + cell1.getName());
Style style = cells.getCell( n1,nColIndex).getStyle();
style.setColor(Color.MAGENTA);
cell1.setStyle(style);
}
}

2

Hi,

Please see the code below in Java.

What it does is that it finds a string “Test” in all cells and replace them with another string “Rock”.

For example, if cell A1 contains “aasaTestasf”, then after the execution of this code, it will contains "aasaRockasf"

Kindly, see the input and output file generated by the code. Once you will understand the code, you will be able to fix your own code.

Java


String path = “F:\Shak-Data-RW\Downloads\Files\FindStringContains1.xls”;

Workbook workbook = new Workbook();
workbook.open(path);


String stringtofind = “Test”;
String stringtoreplace = “Rock”;

for (int i = 0; i < workbook.getWorksheets().size(); i++)
{
Worksheet worksheet = workbook.getWorksheets().getSheet(i);
Cells cells = worksheet.getCells();
Cell cell;
Cell prevcell = null;

do
{
FindOptions opts = new FindOptions();
opts.setLookinType(FindOptions.MATCH_CONTAINS);

cells.find(stringtofind, prevcell, opts);
cell = cells.find(stringtofind, prevcell, opts);

if (cell == null)
break;

String val = cell.getStringValue().replace(stringtofind, stringtoreplace);
cell.setValue(val);

prevcell = cell;

} while (cell != null);

}

workbook.save(path + “.java.out.xls”);

3

Hi,


Another way to do it. You can enhance your own code segment a bit. See the complete sample code here.

Sample code:
Workbook workbook = new Workbook();
workbook.open(“d:\files\FindBook.xls”);
Cells cells = workbook.getWorksheets().getSheet(0).getCells();


int num=cells.getMaxDataRow();
int num1=cells.getMaxDataColumn();
FindOptions findOptions = new FindOptions();
findOptions.setLookinType(FindOptions.SEARCH_BY_COLUMNS);
Cell prevCell = null;
for (int n1=0;n1<=num;n1++)
{
for(int nColIndex =0;nColIndex<=num1;nColIndex++)
{
Cell cell1=cells.getCell(n1,nColIndex);
cell1 = cells.find(“Niraj”,prevCell,findOptions);
if(cell1!=null)
{
System.out.println(cell1.getValue());
System.out.println("Name of the cell containing formula: " + cell1.getName());
Style style = cells.getCell( n1,nColIndex).getStyle();
style.setColor(Color.MAGENTA);
cell1.setStyle(style);
prevCell = cell1;
}
}
}

Thank you.