topic Re: Identify and capture Hidden rows from excel file in Talend Studio

Identify and capture Hidden rows from excel file

AbhishekBoga — Tue, 21 May 2019 11:50:47 GMT

Hi All,

Is there any inbuild component in Talend where I can identify the hidden rows from excel file and then store it in a table?

I didn't found any component which can work like this.

Re: Identify and capture Hidden rows from excel file

akumar2301 — Tue, 21 May 2019 13:28:49 GMT

Hello ,

You can identity hidden row using Java

 Workbook workbook = WorkbookFactory.create(new File(.......));
  Sheet = workbook.getSheetAt(0);
  Iterator<Row> row Iter = sheet.iterator();
  while(rowIter.hasNext()) {
    Row row = rowIter.next();
    if(row.getZeroHeight()) {
        System.out.println(row.getRowNum() + " Rownumber is hidden"  );
    }
  }

Re: Identify and capture Hidden rows from excel file

AbhishekBoga — Wed, 22 May 2019 10:13:30 GMT

Hi Abhishek,

Thanks for the code.

I am getting error message as "rowIter cannot be resolved"

I have added the code in tJava component as below:

Workbook workbook = WorkbookFactory.create("D:/Project Docs/Data Lake/Source Files/Fetch_Line_Number.xlsx");
Sheet = workbook.getSheetAt(0);
Iterator<Row> row Iter = sheet.iterator();
while(rowIter.hasNext()) {
Row row = rowIter.next();
if(row.getZeroHeight()) {
System.out.println(row.getRowNum() + " Rownumber is hidden" );
}
}

Re: Identify and capture Hidden rows from excel file

akumar2301 — Wed, 22 May 2019 12:56:45 GMT

Hello Try below code

org.apache.poi.xssf.usermodel.XSSFWorkbook workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook("C:/xxxxxx/in.xlsx");
org.apache.poi.xssf.usermodel.XSSFSheet sheet= workbook.getSheetAt(0);
java.util.Iterator<org.apache.poi.ss.usermodel.Row> rowIter = sheet.iterator();
while(rowIter.hasNext()) {
org.apache.poi.ss.usermodel.Row row = rowIter.next();
if(row.getZeroHeight()) {
System.out.println((row.getRowNum()+ 1) + " Rownumber is hidden" );
}
}