使用poi根據(jù)Excel模板填充數(shù)據(jù)

遇到的問題（模板文件會被修改 類似有緩存）

1. 通過 File方式創(chuàng)建Workbook時源模板文件會被修改，Workbook中會殘留之前的數(shù)據(jù)，但模板文件 xls中并不會體現(xiàn)

    File excelFile = GetLocalFileUtil.getFile("temp.xlsx", "fileTemp");
    //通過file創(chuàng)建
    XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook(excelFile)
   

   解決方案
   使用流創(chuàng)建Workbook

    File excelFile = GetLocalFileUtil.getFile("temp.xlsx", "fileTemp");
    //通過流創(chuàng)建workbook
    XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook(new FileInputStream(excelFile));
    //=======================2007之下版本===========================//
    POIFSFileSystem poifsFileSystem = new POIFSFileSystem(excelFile);
    Workbook workbook = WorkbookFactory.create(poifsFileSystem);
   

模板樣例

image.png

完整代碼 只針對xssf

public void generatorFile(String username, List<DataBean> dataBean){
    File excelFile = GetLocalFileUtil.getFile("temp.xlsx", "fileTemp");
    XSSFWorkbook workbook = null;
    FileOutputStream fileOutputStream = null;
    try {
        workbook = XSSFWorkbookFactory.createWorkbook(new FileInputStream(excelFile));

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);

        Sheet sheet = workbook.getSheetAt(0);
        //處理title
        handlerTitle(currentMonth, username, sheet);
        //處理內容
        handlerRowData(sheet, cellStyle, dataBean);

        String filename = "%1$s-%2$d月任務明細表.xlsx";
        String formatFilename = String.format(filename, sysUserCard.getUsername(), currentMonth);
        String exportFilePath = FileUtil.getExportFilePath(excelUploadPath);
        File saveFile = FileUtil.getSaveFilePath(exportFilePath, formatFilename);
        fileOutputStream = new FileOutputStream(saveFile);
        workbook.write(fileOutputStream);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (fileOutputStream != null) {
            try {
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
private void handlerTitle(int currentMonth, String username, Sheet sheet) {
    Row titleRow = sheet.getRow(0);
    Cell titleRowCell = titleRow.getCell(0);
    String titleContent = titleRowCell.getStringCellValue();
    String formatRowCellValue = String.format(titleContent, username, currentMonth,
            DateUtil.convertDateToString(new Date(), DateUtil.SIMPLE_DATE_TIME_PATTERN));
    titleRowCell.setCellValue(formatRowCellValue);
}

private void handlerRowData(Sheet sheet, CellStyle cellStyle, List<DataBean> dataBean) {
    Row headRow = sheet.getRow(1);
    int physicalNumberOfCells = headRow.getPhysicalNumberOfCells();
    for (int i = 0; i < taskCardList.size(); i++) {
        int lastNewRowNum = sheet.getLastRowNum();
        Row newRow = sheet.createRow(lastNewRowNum + 1);

        TaskCard taskCard = taskCardList.get(i);
        String taskId = taskCard.getParentId();
        Task task = daoTask.findOne("taskId", taskId);

        String projectId = task.getParentId();
        TaskProject taskProject = daoTaskProject.findOne("taskId", projectId);

        for (int cellIndex = 0; cellIndex < physicalNumberOfCells; cellIndex++) {
            Cell cell = newRow.createCell(cellIndex);
            cell.setCellStyle(cellStyle);

            Comment cellComment = headRow.getCell(cellIndex).getCellComment();
            String comment = "";
            if (cellComment != null) {
                comment = cellComment.getString().toString().trim();
            }
            switch (comment) {
                case "number":
                    cell.setCellValue(i + 1);
                    break;
                case "projectName":
                    cell.setCellValue(dataBean.getProjectName());
                    break;
                case "taskName":
                    cell.setCellValue(dataBean.getTaskName());
                    break;
                case "subTaskName":
                    cell.setCellValue(dataBean.getSubTaskName());
                    break;
                case "taskDesc":
                    cell.setCellValue(dataBean.getTaskDetail());
                    break;
                case "taskType":
                    cell.setCellValue(dataBean.getTaskType());
                    break;
                case "taskStatus":
                    cell.setCellValue(dataBean.getTaskStatus());
                    break;
                case "startTime":
                    cell.setCellValue(dataBean.getStartTime());
                    break;
            }
        }
    }
}