POI根据员工信息excel模板填充信息

POI根据员工信息excel模板填充信息

模板如下

代码如下

public class ApplicatioinTest {

    private static final String recuritDuty = "招聘负责人：   %s   ";

    // 各信息对应的行
    private static final int baseInfoStartRow = 4;

    private static final int baseInfoinitEndRow = 14;

    private static final int educationInfoStartRow = 16;

    private static final int educationInfoEndRow = 19;

    private static final int workInfoStartRow = 21;

    private static final int workInfoEndRow = 30;

    private static final int technoRow = 31;

    private static final int hobbyRow = 32;

    private static final int recuritRow = 33;

    private static final int entryChannelRow = 34;

    private static final int salaryInfoStartRow = 36;

    private static final int salaryInfoEndRow = 37;

    private static final int relationInfoStartRow = 36;

    private static final int relationInfoEndRow = 37;

    private static final Pattern COLUMN_PATTERN = Pattern.compile("\\$\\{(.*)\\}");

    @Test
    public void testRegex()  {
        Matcher matcher = COLUMN_PATTERN.matcher("${employee_name}");
        if(matcher.find()){
            String group = matcher.group(1);
            System.out.println(group);
        }
    }

    @Test
    public void readExcdelModel() throws Exception {
        FileInputStream fileInputStream = new FileInputStream("D:\\员工信息模板.xlsx");
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);

        XSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        XSSFFont font = cellStyle.getFont();

        Map<String,String> datas = new HashMap<>();
        datas.put("employee_name", "余波");
        datas.put("gender", "男");
        datas.put("interest", "打球");
        datas.put("recurit_name", "方波");

        // 基本信息
        fillCellValue(sheetAt, baseInfoStartRow, baseInfoinitEndRow, datas);
    

        List<Map<String,String>> educations = new ArrayList<>();

        Map<String,String> eduction = new HashMap<>();
        eduction.put("educate_date", "2020-01-03");
        eduction.put("institution", "仲***");

        Map<String,String> eduction2 = new HashMap<>();
        eduction2.put("educate_date", "2020-01-04");
        eduction2.put("institution", "仲***");

        educations.add(eduction);
        educations.add(eduction2);
        // 教育信息
        if(!educations.isEmpty()) {
            fillCellValue(sheetAt, educationInfoStartRow, educationInfoEndRow, educations);
        }

        // 教育信息
        if(!educations.isEmpty()) {
            fillCellValue(sheetAt, workInfoStartRow, workInfoEndRow, educations);
        }

        // 爱好 - 招聘负责人
        fillCellValue(sheetAt, hobbyRow, recuritRow, datas);

        // 入职渠道
        XSSFRow entryChannel = sheetAt.getRow(entryChannelRow);
        String entryChannelName = "前程无忧";
        int entryChannelPhysicalNumberOfCells = entryChannel.getPhysicalNumberOfCells();
        for(int i = 1; i < entryChannelPhysicalNumberOfCells; i++) {
            XSSFCell cell = entryChannel.getCell(i);
            String stringCellValue = cell.getStringCellValue();
            int index = stringCellValue.indexOf(entryChannelName);
            if( index != -1) {
                String prefix = stringCellValue.substring(0, index - 1);
                String suffix = stringCellValue.substring(index - 1);
                cell.setCellValue(blodText(prefix + suffix.replaceFirst("□", "☑"), font, 1, 6));
                break;
            }
        }

        hssfWorkbook.write(new FileOutputStream("D:\\员工信息模板2.xlsx"));
    }

    private void fillCellValue(XSSFSheet sheetAt, int startRow, int endRow, Map<String, String> datas) {
        for(int i = startRow; i<=endRow ; i++ ){
            XSSFRow row = sheetAt.getRow(i);
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();
            for(int j = 0; j < physicalNumberOfCells; j++) {
                XSSFCell cell = row.getCell(j);
                fillCellValue(cell, datas);
            }
        }
    }


    private void fillCellValue(XSSFSheet sheetAt, int startRow, int endRow, List<Map<String, String>> datas) {
        for(int i = startRow; i<=endRow ; i++ ) {
            XSSFRow row = sheetAt.getRow(i);
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();
            Map<String, String> data = datas.size() > (i - startRow) ? datas.get(i - startRow) : new HashMap<>();
            for (int j = 0; j < physicalNumberOfCells; j++) {
                XSSFCell cell = row.getCell(j);
                fillCellValue(cell, data);
            }
        }
    }

    private void fillCellValue(XSSFCell cell, Map<String, String> datas) {
        String stringCellValue = cell.getStringCellValue();
        if("NULL".equals(stringCellValue)) {
            cell.setCellValue("");
        } else {
            Matcher matcher = COLUMN_PATTERN.matcher(stringCellValue);
            if(matcher.find()){
                String group = matcher.group(1);
                stringCellValue = Objects.isNull(datas.get(group)) ?
                        stringCellValue.replace("${" + group + "}", "") :
                        stringCellValue.replace("${" + group + "}", datas.get(group));
                cell.setCellValue( stringCellValue );
            }
        }
    }

    // 字体加粗
    private RichTextString blodText(String content, Font font, int start, int end ) {
        RichTextString richTextString = new XSSFRichTextString(content);
        font.setFontName("宋体");
        font.setBold(true);
        // 字体大小
        short fontHeight = font.getFontHeightInPoints();
        font.setFontHeightInPoints((short) 10);
        richTextString.applyFont(start, end , font);
        font.setFontHeightInPoints(fontHeight);
        font.setBold(false);//粗体显示

        return richTextString;
    }

    // 设置方框打勾
    private RichTextString fillTextRight(String content, Font font){
        RichTextString richTextString = new XSSFRichTextString(content);
        // 设置字体名称
        font.setFontName("Wingdings 2");
        richTextString.applyFont(content.indexOf("\\\u25A1") + 1 , content.indexOf("\\\u25A1") + 2 , font);
        font.setFontName("宋体");
        return richTextString;

    }


    // 设置下划线
    private XSSFRichTextString UnderLineIndex(String content, Font font) {
            font.setUnderline((byte)1);
        XSSFRichTextString richString = new XSSFRichTextString(content);
        richString.applyFont(6, content.length(), font);
        font.setUnderline((byte)0);
        return richString;
    }
}

办公设备租赁，找深圳惠源.