Octopus

如何導(dǎo)入excel

如何導(dǎo)出excel

Octopus 是一個(gè)簡(jiǎn)單的java excel導(dǎo)入導(dǎo)出工具.

下面是一個(gè)excel文件中sheet的數(shù)據(jù)，有四個(gè)學(xué)生信息.

一個(gè)學(xué)生類(lèi)，用來(lái)保存從excel中讀取的學(xué)生信息.

//lombok annotations
@Getter
@Setter
@NoArgsConstructor
@ToString
public class Student {

    @ModelLineNumber
    private int lineNum;

    @ModelProperty(value = "id",blankable = false)
    private String studentId;

    @ModelProperty(value = "name",defaultValue = "anonymous")
    private String name;

    @ModelProperty(value = "sex",wrongMsg = "sex must be M or F",pattern = "^M|F$")
    private String sex;

    @ModelProperty(value = "admission",wrongMsg = "admission must be a date")
    private LocalDate inTime;

    @ModelProperty(value = "score",wrongMsg = "score must be numeric",defaultValue = "100")
    private Double score;

}

用代碼讀取excel，并輸出學(xué)生信息：

InputStream is = getClass().getResourceAsStream("/test.xlsx");
Workbook workbook = WorkbookFactory.create(is);
Sheet sheet = workbook.getSheetAt(0);

//read students with ReusableSheetReader
SheetReader> students = new ReusableSheetReader<>(sheet,1,0,Student.class);

//print students information
for (ModelEntity student:students) {
    System.out.println(student.toString());
}

輸出的學(xué)生信息

SimpleModelEntity(entity=Student(lineNum=2, studentId=20134123, name=John, sex=M, inTime=2013-09-01, score=89.0, gradeAndClazz=null), exceptions=[])
SimpleModelEntity(entity=Student(lineNum=3, studentId=20124524, name=Joyce, sex=F, inTime=null, score=79.0, gradeAndClazz=null), exceptions=[cn.chenhuanming.octopus.exception.DataFormatException: in cell (3,4) ,20123-8-31 can not be formatted to class java.time.LocalDate])
SimpleModelEntity(entity=Student(lineNum=4, studentId=20156243, name=anonymous, sex=null, inTime=2015-05-15, score=94.0, gradeAndClazz=null), exceptions=[cn.chenhuanming.octopus.exception.PatternNotMatchException: P and ^M|F$ don"t match!])
SimpleModelEntity(entity=Student(lineNum=5, studentId=20116522, name=Nemo, sex=F, inTime=2011-02-26, score=100.0, gradeAndClazz=null), exceptions=[])

通過(guò)ModelEntity，可以獲取更多異常信息，例如@ModelProperty的配置信息和所發(fā)生的異常.

完整的測(cè)試用例：src/test/cn/chenhuanming/octopus/core/SheetReaderTest

為了說(shuō)明導(dǎo)出的特性，我們給Student類(lèi)增加一個(gè)屬性GradeAndClazz用來(lái)表示年級(jí)和班級(jí).下面是最終的Student類(lèi)，可以用來(lái)導(dǎo)入導(dǎo)出.

@Getter
@Setter
@NoArgsConstructor
@ToString
public class Student {

    @ModelLineNumber
    private int lineNum;

    @ModelProperty(value = "id",blankable = false)
    private String studentId;

    @ModelProperty(value = "name",defaultValue = "anonymous")
    private String name;

    @ModelProperty(value = "sex",wrongMsg = "sex must be M or F",pattern = "^M|F$")
    private String sex;

    //jackson annotation to format output
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ModelProperty(value = "admission",wrongMsg = "admission must be a date")
    private LocalDate inTime;

    @ModelProperty(value = "score",wrongMsg = "score must be numeric",defaultValue = "100")
    private Double score;

    @ModelIgnore
    private GradeAndClazz gradeAndClazz;

    public Student(String studentId, String name, String sex, LocalDate inTime, Double score,GradeAndClazz gradeAndClazz) {
        this.studentId = studentId;
        this.name = name;
        this.sex = sex;
        this.inTime = inTime;
        this.score = score;
        this.gradeAndClazz = gradeAndClazz;
    }
}

GradeAndClazz類(lèi)，只有年級(jí)和班級(jí)兩個(gè)信息.

@Getter
@Setter
@AllArgsConstructor
public class GradeAndClazz{
    private String grade;
    private String clazz;
}

需要一個(gè)xml來(lái)配置導(dǎo)出的屬性和屬性描述作為表頭

用代碼導(dǎo)出學(xué)生信息

//prepare workbook and stuednts objects
Workbook workbook = new XSSFWorkbook();
String rootPath = this.getClass().getClassLoader().getResource("").getPath();
FileOutputStream os = new FileOutputStream(rootPath+"/export.xlsx");
GradeAndClazz gradeAndClazz = new GradeAndClazz("2014","R6");
Student student1 = new Student("201223","John","M", LocalDate.now(),98.00,gradeAndClazz);
Student student2 = new Student("204354","Tony","M", LocalDate.now(),87.00,gradeAndClazz);
Student student3 = new Student("202432","Joyce","F", LocalDate.now(),90.00,gradeAndClazz);

//write excel with OneSheetExcelWriter
ExcelWriter studentExcelWriter = new OneSheetExcelWriter<>(getClass().getClassLoader().getResourceAsStream("studentExport.xml"));

studentExcelWriter.write(workbook,Arrays.asList(student1,student2,student3));
workbook.write(os);

導(dǎo)出結(jié)果

                                          |    class info      |
id        name    M     admission   score |---------|----------|
                                          |  grade  |   class  |
---------------------------------------------------------------|
201223    John    M     2017-07-06  98.0  |  2014   |   R6     |
204354    Tony    M     2017-07-06  87.0  |  2014   |   R6     |
202432    Joyce   F     2017-07-06  90.0  |  2014   |   R6     |

可以看到，對(duì)于gradeAndClazz屬性，會(huì)用一個(gè)合并單元格來(lái)表示.admission因?yàn)楸?b>@JsonFormat標(biāo)記，因此會(huì)格式化輸出日期。事實(shí)上Octopus會(huì)調(diào)用jackson來(lái)格式化json后再寫(xiě)入excel.

詳細(xì)例子在 src/test/cn/chenhuanming/octopus/core/OneSheetExcelWriterTest