CODEWITHSUNDEEP
javareaderjxls
P.Paulsen
P.Paulsen

Reputation: 3

How to add the row num to the employee loop variable?

I want to add the current row number of the excel row to the mapped Employee variable.

From the example below: I want to know that the employee "Yuri" is in excel row number 8.

But I can't find any way to access it. XLSRowCursor has it but how can I add it to the mapped bean? I know that the reader uses the current processing row number when writing an exceptions and POI has it too.

A simple self row counting solution on my side isn't a valid idea, because we use the skip row at error mechanism.

Any tips or hints?

The xml file:

 <?xml version="1.0" encoding="ISO-8859-1"?>
   <workbook>
     ....
    <loop startRow="7" endRow="7" items="department.staff" var="employee" varType="net.sf.jxls.reader.sample.Employee">
      <section startRow="7" endRow="7">
       <mapping row="7" col="0">employee.name</mapping>
       <mapping row="7" col="1">employee.age</mapping>
       <mapping row="7" col="3">employee.payment</mapping>
       <mapping row="7" col="4">employee.bonus</mapping>
     </section>
  ...
 </loop>
 </worksheet>
</workbook>

The excel file: Employees

6 Name  Age Birth Date  Payment Bonus   Total   Superior Name
7 Oleg  32  2-Jan-74    2000    20,00%  2400    Maxim
8 Yuri  29  26-Sep-77   1800    15,00%  2070    Oleg
9 Leonid    30  12-Feb-76   1700    20,00%  2040    Oleg
10 Alex 28  18-Aug-78   1600    20,00%  1920    Oleg
11 Employee Payment Totals:         7100        8430

Upvotes: 0

Views: 877

Answers (2)

Sentary
Sentary

Reputation: 2026

I had the same issue, but it was resolved by a trick: use a static variable that is initialised automatically before every parsing to store the row number in an additional attribute in the bean

The bean will have an additional attribute "rowNumber" which will contain the row number in Excel file, and a static variable "position" that will be incremented at each object instantiation (which means the processing of a new line in Excel file)

package com.test.parser.model;

public class Employee{

    private String name;
    private String age;
    private String payment;

    private static int position = 2;

    private int rowNumber;

    public Employee(){
        setRowNumber(position++);
    }

    public static void resetRowNumber() {
        position = 2;
    }

    public int getRowNumber() {
        return rowNumber;
    }

    public void setRowNumber(int rowNumber) {
        this.rowNumber = rowNumber;
    }
}

Before the JXLS reading, the static value is reset to the start row (in my case it's the second row) via the static method Employee.resetRowNumber() like below:

XLSReader mainReader = ReaderBuilder.buildFromXML(<InputStream of XML Mapping File>);
List<Employee> employeeList = new ArrayList<Employee>();
Employee.resetRowNumber();
beans.put("department.staff", employeeList);
mainReader.read(inputXLS, beans);

At the end of parsing, we will be able to get the row number via Employee.getRowNumber().

Upvotes: 0

Leonid Vysochyn
Leonid Vysochyn

Reputation: 1264

You can extend SimpleBlockReaderImpl and override its read(XLSRowCursor cursor, Map beans) method to use teh XLSRowCursor to get the current Excel row and inject it into the bean.

Currently there is no automatic way to inject your own CustomBlockReader implementation via XML so you would have to do it manually by getting all the sheet readers from XLSReader and replacing inner block readers with your custom instances.

Upvotes: 1

Related Questions