复杂条件在JXLS-Reader循环中的loopbreakcondition。

huangapple go评论108阅读模式
英文:

Complex conditionals in JXLS-Reader loopbreakcondition

问题

我需要使用jxls-reader(http://jxls.sourceforge.net/reference/reader.html)的一些帮助。

我可以很好地处理循环遍历工作表,其中一行表示特定的Java对象,但在嵌套循环的情况下我遇到了困难。

我需要将上面的Excel工作表映射到以下的POJO类:

    class Student {
        private String name;
        private String dob;
        private List<Class> classes;
        //getters & setters
    }
    
    class Class {
        private String name;
        private String link;
        //getters & setters
    }

我的问题是如何定义loopbreakcondition以读取内部对象。请注意,Class对象的loopbreakcondition需要满足以下条件:

 - 下一行的第一个单元格不为空,或者
 - 下一行的第一个单元格为空,并且当前列的下一个单元格也为空(用于终止最后一行)

我该如何在loopbreakcondition中表达上述布尔条件。我有以下代码,但我知道是错误的:

    <workbook>
        <worksheet name="Students">
            <section startRow="0" endRow="0">
                <!--空的表头部分-->
            </section>
            <loop startRow="1" endRow="1" items="students" var="student" varType="com.test.Student">
                <section startRow="1" endRow="1">
                    <mapping row="1" col="0">student.name</mapping>
                    <mapping row="1" col="1">student.dob</mapping>
                </section>
                <loopbreakcondition>
                    <rowcheck offset="0">
                        <cellcheck offset="0"> <!--在下一个空单元格处终止循环-->
                        </cellcheck>
                    </rowcheck>
                </loopbreakcondition>
    
                <loop startRow="1" endRow="1" items="student.classes" var="class" varType="com.test.Class">
                    <section startRow="1" endRow="1">
                        <mapping row="1" col="2">class.name</mapping>
                        <mapping row="1" col="3">class.link</mapping>
                    </section>
                    <loopbreakcondition>
                        <rowcheck offset="0">
                            <cellcheck offset="0"> <!--在下一个空单元格处终止循环-->
                            </cellcheck>
                        </rowcheck>
                    </loopbreakcondition>
                </loop>
            </loop>
        </worksheet>
    </workbook>

这是否可行?
是否有其他库可以帮助我实现这一点?
还是编写自己的解析器并使用apache-poi会更好?

谢谢。
英文:

I need some help using the jxls-reader(http://jxls.sourceforge.net/reference/reader.html).

I am comfortable with looping a sheet where one row represents a particular java object, but in the case of nested loops I am stumped.

复杂条件在JXLS-Reader循环中的loopbreakcondition。

I need to map the above excel sheet to the following pojo:

class Student {
    private String name;
    private String dob;
    private List&lt;Class&gt; class;
    //getters &amp; setters
}

class Class {
    private String name;
    private String link;
    //getters &amp; setters
}

My problem is defining the lookbreakcondition to read the inner object. Notice that the loopbreakcondition for the Class object requires something along the following lines:

  • The first cell of the next row is non-empty OR
  • The first cell of the next row is empty AND the next cell of the current column is empty (for breaking the last row)

How do I express the above boolean condition in the loopbreakcondition. I have the following but I know it is wrong:

&lt;workbook&gt;
    &lt;worksheet name=&quot;Students&quot;&gt;
        &lt;section startRow=&quot;0&quot; endRow=&quot;0&quot;&gt;
            &lt;!--Empty Header Section--&gt;
        &lt;/section&gt;
        &lt;loop startRow=&quot;1&quot; endRow=&quot;1&quot; items=&quot;students&quot; var=&quot;student&quot; varType=&quot;com.test.Student&quot;&gt;
            &lt;section startRow=&quot;1&quot; endRow=&quot;1&quot;&gt;
                &lt;mapping row=&quot;1&quot; col=&quot;0&quot;&gt;student.name&lt;/mapping&gt;
                &lt;mapping row=&quot;1&quot; col=&quot;1&quot;&gt;student.dob&lt;/mapping&gt;
            &lt;/section&gt;
            &lt;loopbreakcondition&gt;
                &lt;rowcheck offset=&quot;0&quot;&gt;
                    &lt;cellcheck offset=&quot;0&quot;&gt; &lt;!--Breaks on next empty cell--&gt;&lt;/cellcheck&gt;
                &lt;/rowcheck&gt;
            &lt;/loopbreakcondition&gt;

            &lt;loop startRow=&quot;1&quot; endRow=&quot;1&quot; items=&quot;student.class&quot; var=&quot;class&quot; varType=&quot;com.test.Class&quot;&gt;
                &lt;section startRow=&quot;1&quot; endRow=&quot;1&quot;&gt;
                    &lt;mapping row=&quot;1&quot; col=&quot;2&quot;&gt;class.name&lt;/mapping&gt;
                    &lt;mapping row=&quot;1&quot; col=&quot;3&quot;&gt;class.link&lt;/mapping&gt;
                &lt;/section&gt;
                &lt;loopbreakcondition&gt;
                    &lt;rowcheck offset=&quot;0&quot;&gt;
                        &lt;cellcheck offset=&quot;0&quot;&gt; &lt;/cellcheck&gt;
                    &lt;/rowcheck&gt;
                &lt;/loopbreakcondition&gt;
            &lt;/loop&gt;
        &lt;/loop&gt;
    &lt;/worksheet&gt;
&lt;/workbook&gt;

Is this even possible?
Are there any other libraries that will help me achieve this?
Or am I better off writing my own parser using apache-poi?

Thank You.

答案1

得分: 1

以下是翻译好的代码部分:

你可以使用apache-poi来读取Excelxls或xlsx(XSSF)文件更多信息请查阅[apache-poi文档][1][Apache POI-HSSF vs POI-XSSF][2]在以下示例中将Class类的类名更改为StudentClass因为class是Java中的关键字

尝试以下解决方案

Studnet.java

public class Student {
    private String name;
    private Date dob; //根据给定的Excel文件,dob是日期格式
    private ArrayList<StudentClass> studentClassList;

    //getters & setters
}

StudentClass.java

public class StudentClass {
    private String name;
    private String link;

    //getters & setters
}

ExcelFileReader.java

public class ExcelFileReader() {
    private String studentName; //用于存储先前的学生姓名
    private List<Student> listStudent; //用于收集学生

    public void readData() {
        listStudent = new ArrayList<Student>();
        studentName = "";
        FileInputStream excelFile = new FileInputStream(new File(INPUT_EXCEL_FILE_NAME));
        Workbook workbook = new XSSFWorkbook(excelFile); //使用输入的Excel文件创建工作簿
        Sheet sheet = workbook.getSheetAt(0); //获取Excel文件的第一个工作表
        int rowCount = sheet.getPhysicalNumberOfRows(); //获取行数

        for(int i = 1; i < rowCount; i++) { //i初始化为1,以排除第一行(第一行ID为0,包含列名)
            if(rowCount > 1){ //忽略第一行,因为此时尚未创建任何学生
                listStudent.add(student); //将学生添加到学生列表中
                //根据您的需求在此处使用已创建的学生
            }

            Row currentRow = sheet.getRow(i); //获取当前行
            if(!studentName.equals(currentRow.getCell(0).getStringCellValue())){ //如果当前学生姓名与上一个学生姓名不相等,则创建新的学生条目
                Student student = new Student();
                student.setName(currentRow.getCell(0).getStringCellValue()); //第一个单元格的ID为0
                student.setDob(currentRow.getCell(1).getDateCellValue()); //第二个单元格的ID为1
                studentName = student.getName(); //将当前学生姓名分配给studentName变量以进行将来的验证

                if(!currentRow.getCell(2).getStringCellValue().equals(null) && !currentRow.getCell(2).getStringCellValue().equals("")){ //检查学生是否在同一行上有一个班级(与新学生条目的第一行)
                    StudentClass studentClass = new StudentClass();
                    studentClass.setName(currentRow.getCell(2).getStringCellValue()); //第三个单元格的ID为2
                    studentClass.setLink(currentRow.getCell(3).getStringCellValue());
                    student.getStudentClassList().add(studentClass); //将相关的StudentClass添加到Student的StudentClassList中。请在StudentClass中初始化StudentClassList
                }
            }else{ //当学生有多个班级时执行此代码块
                StudentClass studentClass = new StudentClass();
                studentClass.setName(currentRow.getCell(2).getStringCellValue());
                studentClass.setLink(currentRow.getCell(3).getStringCellValue());
                student.getStudentClassList().add(studentClass);
            }
        }
    }
}
英文:

you can use the apache-poi for read excel(xls or xlsx(XSSF)) file. for more info, apache-poi documentation, Apache POI-HSSF vs POI-XSSF. changed the class name of Class to StudentClass in following example. because class is a keyword in java

try with following solution,

Studnet.java

public class Student {
private String name;
private Date dob; //as per given excel file, dob is a date format
private ArrayList&lt;StudentClass&gt; studentClassList;
//getters &amp; setters
}

StudentClass.java

public class StudentClass {
private String name;
private String link;
//getters &amp; setters
}

ExcelFileReader.java

public class ExcelFileReader() {
private String studentName; //for store the previous student name
private List&lt;Student&gt; listStudent; //for collect the students
public void readData() {
listStudent = new ArrayList&lt;Student&gt;();
studentName = &quot;&quot;;
FileInputStream excelFile = new FileInputStream(new File(INPUT_EXCEL_FILE_NAME));
Workbook workbook = new XSSFWorkbook(excelFile); //create the workbook using input excel file
Sheet sheet = workbook.getSheetAt(0); //get the first sheet of excel file
int rowCount = sheet.getPhysicalNumberOfRows(); //get the row count
for(int i = 1; i &lt; rowCount; i++) { //i initialized with 1 for exclude the first row(first row id is 0 and it hold the column names)
if(rowCount&gt;1){ //ignore the first row. because at this time no any student created
listStudent.add(student); //add student into student list
//you can use the created student in here as per your requirement
}
Row currentRow = sheet.getRow(i); //get current row
if(!studentName.equals(currentRow.getCell(0).getStringCellValue())){ //if not equals current student name with previous student name then create new student entry
Student student = new Student();
student.setName(currentRow.getCell(0).getStringCellValue()); //first cell id is 0
studnet.setDob(currentRow.getCell(1).getDateCellValue()); //second cell id is 1
studentName = student.getName(); //assign the current student name to studentName variable for future validation
if(!currentRow.getCell(2).getStringCellValue().equals(null) &amp;&amp; !currentRow.getCell(2).getStringCellValue().equals(&quot;&quot;)){ //check the student has a class at same row(first row with new student entry)
StudentClass studentClass = new StudentClass();
studentClass.setName(currentRow.getCell(2).getStringCellValue()); //third cell id is 2
studentClass.setLink(currentRow.getCell(3).getStringCellValue());
student.getStudentClassList.add(studentClass); //add relevant StudnetClass into StudentClassList of Student. please initialized the StudentClassList in StudentClass
}
}else{ //this code block execute when student has more than one class
StudentClass studentClass = new StudentClass();
studentClass.setName(currentRow.getCell(2).getStringCellValue());
studentClass.setLink(currentRow.getCell(3).getStringCellValue());
student.getStudentClassList.add(studentClass);
}
}
}
}

huangapple
  • 本文由 发表于 2020年9月12日 13:16:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/63857067.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定