public class EmpHeadPhotoConverter implements Converter<String> {
    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        //根據(jù)圖片的相對路徑或網(wǎng)絡(luò)地址讀取圖片到byte輸出流(ByteArrayOutputStream)
        ByteArrayOutputStream byteArrayOutputStream = null;
        InputStream inputStream=null;
        try {
             inputStream = ClassLoader.getSystemResourceAsStream(value);
            byteArrayOutputStream = new ByteArrayOutputStream();
            int len=-1;
            byte[] bytes = new byte[1024];
            while ((len=inputStream.read(bytes))!=-1){
                byteArrayOutputStream.write(bytes);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (byteArrayOutputStream != null) {
                byteArrayOutputStream.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
        //把輸出流中的圖片字節(jié)數(shù)組作為參數(shù)構(gòu)建writeCellData對象并返回
        //WriteCellData是EasyExcel對單元格數(shù)據(jù)的封裝
        return  new WriteCellData<>(byteArrayOutputStream.toByteArray());
    }
}

@Data
@ContentRowHeight(50)//數(shù)據(jù)行的高度
public class Employee implements Serializable {
    @ExcelProperty("姓名")
    private String realName;
    @ExcelProperty("員工編號")
    private String empNo;
    @ExcelProperty("性別")
    private String sex;
    @ExcelProperty("家庭地址")
    private String address;
    @ExcelProperty("聯(lián)系電話")
    private String phone;
    @ExcelProperty("電子郵箱")
    private String email;
    @ExcelProperty(value = "照片",converter = EmpHeadPhotoConverter.class)
    @ColumnWidth(10)//列寬
    private String headPhoto;
}

@Test
public void writeImage(){
    String exportPath=this.getExportPath();
    String exportFile=exportPath+File.separator+"員工基本信息(包含照片).xlsx";
    List<Employee> list = new ArrayList<>();
    for (int i = 0; i < 3; i++) {
        Employee employee = new Employee();
        employee.setRealName("張三"+i);
        employee.setEmpNo("CH"+(i+1));
        employee.setAddress("北京");
        employee.setEmpNo("zhangsan@163.com");
        employee.setSex("女");
        employee.setPhone("17777xxxxxx");
        //這里是示例,因?yàn)槲野咽纠龍D片放在了classpath下,后面可以根據(jù)圖片名字在classpath下找到
        //在實(shí)際的業(yè)務(wù)開發(fā)過程中,這里應(yīng)該是圖片的相對路徑或網(wǎng)絡(luò)地址
        employee.setHeadPhoto("zhangsan.jpeg");
        list.add(employee);
    }
    EasyExcel.write(exportFile,Employee.class).sheet().doWrite(list);
}

@Test
public void writeWithStyle2() {
    String exportPath = this.getExportPath();
    String exportFile = exportPath + File.separator + "員工基本信息v2.xlsx";
    List<Employee> list = new ArrayList<>();
    Employee employee = new Employee();
    employee.setRealName("張三");
    employee.setEmpNo("CH001");
    employee.setSex("女");
    Employee employee2 = new Employee();
    employee2.setRealName("李四");
    employee2.setEmpNo("CH002");
    employee2.setSex("男");
    list.add(employee);
    list.add(employee2);
    // 頭的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景設(shè)置為紅色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // 內(nèi)容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // 這里需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無法顯示背景顏色.頭默認(rèn)了 FillPatternType所以可以不指定
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // 背景綠色
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentWriteFont = new WriteFont();
    // 字體大小
    contentWriteFont.setFontHeightInPoints((short)20);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // 這個(gè)策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實(shí)現(xiàn)
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    // 這里 需要指定寫用哪個(gè)class去寫，然后寫到第一個(gè)sheet，名字為模板 然后文件流會自動關(guān)閉
    EasyExcel.write(exportFile, Employee.class)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .sheet()
            .doWrite(list);
}

@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
// 頭字體設(shè)置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 內(nèi)容的背景設(shè)置成綠色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 內(nèi)容字體設(shè)置成20
@ContentFontStyle(fontHeightInPoints = 20)

// 字符串的頭背景設(shè)置成粉紅 IndexedColors.PINK.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
// 字符串的頭字體設(shè)置成20
@HeadFontStyle(fontHeightInPoints = 30)
// 字符串的內(nèi)容的背景設(shè)置成天藍(lán) IndexedColors.SKY_BLUE.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
// 字符串的內(nèi)容字體設(shè)置成20
@ContentFontStyle(fontHeightInPoints = 30)

public class SexColourConverter implements Converter<String> {
    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        WriteFont writeFont = new WriteFont();
        if ("男".equals(value)) {
            writeFont.setColor((short) 12);
        }
        if ("女".equals(value)) {
            writeFont.setColor((short) 10);
        }
        writeCellStyle.setWriteFont(writeFont);
        WriteCellData<Object> cellData = new WriteCellData<>();
        cellData.setWriteCellStyle(writeCellStyle);
        cellData.setType(CellDataTypeEnum.STRING);
        cellData.setStringValue(value);
        return cellData;
    }
}

@Data
public class Employee implements Serializable {
    @ExcelProperty("姓名")
    private String realName;
    @ExcelProperty("員工編號")
    private String empNo;
    @ExcelProperty(value = "性別",converter = SexColourConverter.class)
    private String sex;
}
@Test
public void writeWithStyle() {
    String exportPath = this.getExportPath();
    String exportFile = exportPath + File.separator + "員工基本信息v2.xlsx";
    List<Employee> list = new ArrayList<>();
    Employee employee = new Employee();
    employee.setRealName("張三");
    employee.setEmpNo("CH001");
    employee.setSex("女");
    Employee employee2 = new Employee();
    employee2.setRealName("李四");
    employee2.setEmpNo("CH002");
    employee2.setSex("男");
    list.add(employee);
    list.add(employee2);
    EasyExcel.write(exportFile, Employee.class).sheet().doWrite(list);
}

@Data
public class EmpSalary  {
    @ExcelProperty({"基本信息","姓名"})
    private String realName;
    @ExcelProperty({"基本信息","員工編號"})
    private String empNo;
    //,converter = SalaryDateConverter.class
    @ExcelProperty(value = "工資日期")
    private String salaryDate;
    @ExcelProperty({"工資構(gòu)成","基本工資"})
    private Float baseAmount;
    @ExcelProperty({"工資構(gòu)成","全勤獎"})
    private Float fullAttendAmount;
    @ExcelProperty({"工資構(gòu)成","五險(xiǎn)一金"})
    private Float insurance;
    //特別資金
    @ExcelIgnore
    private Float specialAmount;
}

@Test
public void writeHead() {
    String exportPath = this.getExportPath();
    String exportFile = exportPath + File.separator + "員工工資表v3.xlsx";
    EasyExcel.write(exportFile, EmpSalary.class).sheet().doWrite(this.empSalaryData(10));
}

@Test
public void writeHead() {
    String exportPath = this.getExportPath();
    String exportFile = exportPath + File.separator + "員工工資表v4.xlsx";
    List<List<String>> headList = new ArrayList<>();
    List<String> head1 = new ArrayList<>();
    head1.add("基本信息");
    head1.add("姓名");
    headList.add(head1);
    List<String> head2 = new ArrayList<>();
    head2.add("基本信息");
    head2.add("員工編號");
    headList.add(head2);
    List<String> head3 = new ArrayList<>();
    head3.add("工資日期");
    headList.add(head3);
    List<String> head4 = new ArrayList<>();
    head4.add("工資構(gòu)成");
    head4.add("基本工資");
    headList.add(head4);
    List<String> head5 = new ArrayList<>();
    head5.add("工資構(gòu)成");
    head5.add("全勤獎");
    headList.add(head5);
    List<String> head6 = new ArrayList<>();
    head6.add("工資構(gòu)成");
    head6.add("保險(xiǎn)");
    headList.add(head6);
    EasyExcel.write(exportFile).head(headList).sheet().doWrite(this.empSalaryData(10));
}

@Data
public class EmpSalary  {
    @ExcelProperty({"基本信息","姓名"})
    private String realName;
    @ExcelProperty({"基本信息","員工編號"})
    private String empNo;
    @DateTimeFormat("yyyy年MM月")
    @ExcelProperty(value = "工資日期")
    private Date salaryDate;
    @ExcelProperty({"工資構(gòu)成","基本工資"})
    private Float baseAmount;
    @ExcelProperty({"工資構(gòu)成","全勤獎"})
    private Float fullAttendAmount;
    @ExcelProperty({"工資構(gòu)成","五險(xiǎn)一金"})
    private Float insurance;
    //特別資金
    @ExcelIgnore
    @NumberFormat
    private Float specialAmount;
    @NumberFormat("#.##%")
    @ExcelProperty("績效完成百分比")
    private Double jixiao;
}

@Test
public void writeByConverter(){
    String exportPath = this.getExportPath();
    String exportFile = exportPath + File.separator + "員工工資表v5.xlsx";
    List<EmpSalary> list=new ArrayList<>();
    EmpSalary empSalary = new EmpSalary();
    empSalary.setEmpNo("CH" + ( 1));
    empSalary.setRealName("張三" + ( 1));
    empSalary.setSalaryDate(new Date());
    empSalary.setBaseAmount(5000f);
    empSalary.setFullAttendAmount(500f);
    empSalary.setInsurance(300f);
    empSalary.setJixiao(0.9877);
    list.add(empSalary);
    EasyExcel.write(exportFile, EmpSalary.class).sheet("12月").doWrite(list);
}

@Data
public class Student implements Serializable {
    private Integer id;
    private String stuCode;
    private String stuName;
    private String sex;
    private String born;
    private Integer age;
    private String address;
    private String motherName;
    private String fatherName;
    private Integer grade;
    private Integer classNum;
}

@Test
public void readAllSheet(){
    String userDir = System.getProperty("user.dir");
    String importPath = userDir + File.separator + "import";
    File dir = new File(importPath);
    if (!dir.exists()) {
        dir.mkdirs();
    }
    String importFile = importPath + File.separator + "學(xué)生信息表.xlsx";
    StudentReadListener studentReadListener = new StudentReadListener();
    EasyExcel.read(importFile, Student.class, studentReadListener).doReadAll();
    List<Student> students = studentReadListener.getStudents();
    for (Student student : students) {
        System.out.println(student.getStuName());
    }
}

public class SalaryDateConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    //導(dǎo)入的時(shí)候會走這個(gè)方法,導(dǎo)入的轉(zhuǎn)換邏輯可以在這個(gè)方法里實(shí)現(xiàn)
    @Override
    public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        BigDecimal numberValue = cellData.getNumberValue();
        //平時(shí)不要動不動就搞個(gè)util工具類,我曾經(jīng)目睹一個(gè)新同事,用上用不上的也不管,上來在工程里導(dǎo)入了幾十個(gè)工具類,搞得maven依賴沖突
        // org.apache.poi.ss.usermodel.DateUtil是POI的工具類,
        // DateUtil.getJavaDate()的功能就是把以1900-1-1為起點(diǎn)的日期天數(shù)轉(zhuǎn)換成java.util.Date,直接拿來用就好了,基本不用擔(dān)心里面有bug
        Date javaDate = DateUtil.getJavaDate(numberValue.doubleValue());
        //com.alibaba.excel.util.DateUtils是easyexcel封裝的日期轉(zhuǎn)換工具類,能用就用上唄,基本也不用擔(dān)心有bug
        String format = DateUtils.format(javaDate, DateUtils.DATE_FORMAT_10);
        return format;
    }
    //導(dǎo)出的時(shí)候會走這個(gè)方法,導(dǎo)出的轉(zhuǎn)換邏輯可以在這個(gè)方法里實(shí)現(xiàn)
    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }
}

@Data
public class EmpSalary  {
    @ExcelProperty("姓名")
    private String realName;
    @ExcelProperty("員工編號")
    private String empNo;
    @ExcelProperty(value = "工資日期",converter = SalaryDateConverter.class)
    private String salaryDate;
    @ExcelProperty("工資數(shù)額")
    private Float amount;
}
@Test
public void readByConvert(){
    String userDir = System.getProperty("user.dir");
    String importPath = userDir + File.separator + "import";
    File dir = new File(importPath);
    if (!dir.exists()) {
        dir.mkdirs();
    }
    String importFile = importPath + File.separator + "員工工資表.xlsx";
    EmpSalaryReadListener empSalaryReadListener = new EmpSalaryReadListener();
    EasyExcel.read(importFile, EmpSalary.class, empSalaryReadListener).sheet().doRead();
    List<EmpSalary> empSalaries = empSalaryReadListener.getEmpSalaries();
    System.out.println(empSalaries.size());
}

@Test
public void readManyRow(){
    String userDir = System.getProperty("user.dir");
    String importPath = userDir + File.separator + "import";
    File dir = new File(importPath);
    if (!dir.exists()) {
        dir.mkdirs();
    }
    String importFile = importPath + File.separator + "員工工資表 - 副本.xlsx";
    EmpSalaryReadListener empSalaryReadListener = new EmpSalaryReadListener();
    //數(shù)據(jù)從第三行開始，索引是2
    EasyExcel.read(importFile, EmpSalary.class, empSalaryReadListener).sheet().headRowNumber(2).doRead();
    List<EmpSalary> empSalaries = empSalaryReadListener.getEmpSalaries();
    System.out.println(empSalaries.size());
}

@Data
public class EmpSalary  {
    private String realName;
    private String empNo;
    @ExcelProperty(value = "工資日期",converter = SalaryDateConverter.class)
    private String salaryDate;
    private Float baseAmount;
    private Float fullAttendAmount;
    private Float insurance;
}

public class EmpSalaryReadListener implements ReadListener<EmpSalary> {
    private List<EmpSalary> empSalaries=new ArrayList<>();
    public List<EmpSalary> getEmpSalaries() {
        return empSalaries;
    }
    @Override
    public void invoke(EmpSalary data, AnalysisContext context) {
        empSalaries.add(data);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        for (Integer key : headMap.keySet()) {
            System.out.println("key:"+key+","+headMap.get(key).getStringValue());
        }
        System.out.println("---------");
    }
}