HSSFWorkbook下载xls表格模板及导入excel数据

效果图：（有列置灰、隐藏、下拉列表功能）

下载excel模板代码:

private AjaxResult myBatchImportTemplateSelect(List<ProjectTaskUserBatch> batchTasks, HttpServletResponse response) throws Exception {
    ArrayList<Map<String, Object>> rows = CollUtil.newArrayList();
    com.smartsite.common.utils.poi.ExcelUtil excelUtil = new com.smartsite.common.utils.poi.ExcelUtil<>(null);
    // 通过工具类创建writer，默认创建xls格式
    String test = excelUtil.getAbsoluteFile("绑定人员数据.xls");
    ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getWriter(test);
    Workbook wb = writer.getWorkbook();
    Sheet sheet = writer.getSheet();
    int i = 1;
    for (ProjectTaskUserBatch task : batchTasks) {
        Map<String, Object> row1 = new LinkedHashMap<>();
        row1.put("任务id", task.getTaskId());
        row1.put("任务编码", task.getTaskCode());
        row1.put("任务名称", task.getTaskName());
        row1.put("绑定单位ID", task.getProjectTaskUnitId());
        row1.put("单位类型", task.getUnitType());
        row1.put("单位名称", task.getDeptName());
        row1.put("岗位ID", task.getRoleId());
        row1.put("岗位名称", task.getRoleName());
        ProjectTaskUserBatch taskUser = new ProjectTaskUserBatch();
        taskUser.setRoleId(task.getRoleId());
        taskUser.setProjectTaskUnitId(task.getProjectTaskUnitId());
        List<SysUser> sysUsers = userService.selectUserListByTaskUser(taskUser);
        String[] loginNmaes = sysUsers.stream().map(b -> b.getLoginName()).collect(Collectors.toList()).stream().distinct().toArray(String[]::new);
        if (CollectionUtils.isEmpty(sysUsers)) {
            row1.put("用户登录名", "");
            writer.addSelect(8, i, new String[]{});
        }else{
            row1.put("用户登录名", loginNmaes[0]);
        }
        // 此处没有数据也要新建一个sheet页，以保持索引一致
        wb = dropDownList(wb, sheet, loginNmaes, i, i, 8, 8, "person"+ (i + 1) , i);
        i++;
        rows.add(row1);
    }
    // 一次性写出内容，使用默认样式，强制输出标题
    writer.write(rows, true);
    writer.autoSizeColumnAll();
    writer.renameSheet("绑定人员数据");
    writer.setColumnWidth(0, 12);
    writer.setColumnWidth(1, 8);
    writer.setColumnWidth(2, 18);
    writer.setColumnWidth(4, 12);
    writer.setColumnWidth(5, 26);
    writer.setColumnWidth(7, 12);
    sheet.setColumnHidden(3,true); // 隐藏列
    sheet.setColumnHidden(6,true);
    CellStyle style = writer.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    // 置灰特定列
    for (int j = 1; j <= batchTasks.size(); j++) {
        writer.setStyle(style, 0, j);
        writer.setStyle(style, 1, j);
        writer.setStyle(style, 2, j);
        writer.setStyle(style, 3, j);
        writer.setStyle(style, 4, j);
        writer.setStyle(style, 5, j);
        writer.setStyle(style, 6, j);
        writer.setStyle(style, 7, j);
    }
    try {
        //out为OutputStream，需要写出到的目标流
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        OutputStream out = new FileOutputStream(test);
        writer.flush(out);
        // 关闭writer，释放内存
        writer.close();
        wb.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    } catch (Exception e) {
        throw new BusinessException(e.getMessage());
    }
    return AjaxResult.success("绑定人员数据.xls");
}

/**
 * @param wb HSSFWorkbook对象
 * @param realSheet 需要操作的sheet对象
 * @param datas 下拉的列表数据
 * @param startRow 开始行
 * @param endRow 结束行
 * @param startCol 开始列
 * @param endCol 结束列
 * @param hiddenSheetName 隐藏的sheet名
 * @param hiddenSheetIndex 隐藏的sheet索引
 * @return
 * @throws Exception
 */
public static HSSFWorkbook dropDownList(Workbook wb, Sheet realSheet, String[] datas, int startRow, int endRow,
                                        int startCol, int endCol, String hiddenSheetName, int hiddenSheetIndex)
        throws Exception {

    HSSFWorkbook workbook = (HSSFWorkbook) wb;
    // 创建一个数据源sheet
    HSSFSheet hidden = workbook.createSheet(hiddenSheetName);
    // 数据源sheet页不显示
    workbook.setSheetHidden(hiddenSheetIndex, true);
    // 将下拉列表的数据放在数据源sheet上
    if (datas.length > 0) {
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = 0, length = datas.length; i < length; i++) {
            row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(datas[i]);
        }
        //HSSFName namedCell = workbook.createName();
        //namedCell.setNameName(hiddenSheetName);
        // A1 到 Adatas.length 表示第一列的第一行到datas.length行，需要与前一步生成的隐藏的数据源sheet数据位置对应
        //namedCell.setRefersToFormula(hiddenSheetName + "!$A$1:$A" + datas.length);
        // 指定下拉数据时，给定目标数据范围 hiddenSheetName!$A$1:$A5   隐藏sheet的A1到A5格的数据
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName + "!$A$1:$A" + datas.length);
        CellRangeAddressList addressList = null;
        HSSFDataValidation validation = null;
        row = null;
        cell = null;
        // 单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 循环指定单元格下拉数据
        for (int i = startRow; i <= endRow; i++) {
            row = (HSSFRow) realSheet.createRow(i);
            cell = row.createCell(startCol);
            cell.setCellStyle(style);
            addressList = new CellRangeAddressList(i, i, startCol, endCol);
            validation = new HSSFDataValidation(addressList, constraint);
            realSheet.addValidationData(validation);
        }
    }
    return workbook;
}

导入excel数据代码：

public String batchImportDataSelect(MultipartFile file, Boolean isUpdateSupport, String operName) {
   int successNum = 0;
    int failureNum = 0;
    StringBuilder successMsg = new StringBuilder();
    StringBuilder failureMsg = new StringBuilder();
    // 读取数据
    ExcelReader reader = null;
    try {
        reader = new ExcelReader(file.getInputStream(), 0);
    } catch (IOException e) {
        throw new BusinessException("excel读取异常！");
    }
    List<Map<String, Object>> readAll = reader.readAll();
    for (int i = 0; i < readAll.size(); i++) {
        long taskId = Long.parseLong(String.valueOf(readAll.get(i).get("任务id")));
        String taskCode = String.valueOf(readAll.get(i).get("任务编码"));
//            String taskName = String.valueOf(readAll.get(i).get("任务名称"));
        long projectTaskUnitId = Long.parseLong(String.valueOf(readAll.get(i).get("绑定单位ID")));
        long roleId = Long.parseLong(String.valueOf(readAll.get(i).get("岗位ID")));
        String loginName = String.valueOf(readAll.get(i).get("用户登录名")); // 可能会多个，用逗号隔开
		// 业务代码，将读取数据插入表
        if(StringUtils.isNotEmpty(loginName)) {
            String[] loginNames = Convert.toStrArray(loginName);
            for (String lName : loginNames) {
                try{
                    SysUser sysUser = userMapper.selectUserByLoginName(lName);
                    ProjectTaskUser projectTaskUser = new ProjectTaskUser();
                    projectTaskUser.setProjectTaskId(taskId);
                    projectTaskUser.setProjectTaskUnitId(projectTaskUnitId);
                    projectTaskUser.setRoleId(roleId);
                    projectTaskUser.setUserId(sysUser.getUserId());
                    projectTaskUser.setUserIds(String.valueOf(sysUser.getUserId()));
                    // 验证是否已经绑定这个人员
                    ProjectTaskUser user = projectTaskUserMapper.selectProjectTaskUser(projectTaskUser);
                    if (StringUtils.isNull(user)) {
                        projectTaskUser.setCreateBy(operName);
                        this.insertProjectTaskUser(projectTaskUser);
                        successNum++;
                        successMsg.append("<br/>" + successNum + "、任务 " + taskCode +" 导入用户 " + lName + " 成功");
                    } else if (isUpdateSupport) {
                        projectTaskUser.setId(user.getId());
                        projectTaskUser.setUpdateBy(operName);
                        this.updateProjectTaskUser(projectTaskUser);
                        successNum++;
                        successMsg.append("<br/>" + successNum + "、任务 " + taskCode + "更新用户 " + lName + " 成功");
                    } else {
                        failureNum++;
                        failureMsg.append("<br/>" + failureNum + "、任务 " + taskCode + "导入用户 " + lName  + " 已存在");
                    }
                }catch (Exception e) {
                    failureNum++;
                    String msg = "<br/>" + failureNum + "、任务 " + taskCode + "导入用户 " + lName  + " 失败：";
                    failureMsg.append(msg + e.getMessage());
                    log.error(msg, e);
                }
            }
        }
    }
    if (failureNum > 0) {
        failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
        throw new BusinessException(failureMsg.toString());
    } else if(successNum == 0){
        failureMsg.insert(0, "没有可导入的人员数据！");
        throw new BusinessException(failureMsg.toString());
    } else {
        successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
    }
    return successMsg.toString();
}