POI 导出Excel，部分单元格的锁定和背景至灰

先来看看效果



直接上代码

public void doExport(HttpServletRequest request, HttpServletResponse response, List<ProductionPlanActualIEO> list) {
		//这是我封装的一个个方法，其目的是设置导出文件的名字，不需要的可以不写
        ExportHelper.prepareExportXlsx(response, EXPORT_FILE_NAME);
        //获取模板路径
        File file = templateFileManager.get(EXPORT_FILE_MODEL_NAME);
        try (FileInputStream templateStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(templateStream);
             OutputStream outputStream = response.getOutputStream()) {
            //创建中间内容
            writeTableData(workbook, list);
            workbook.write(outputStream);
        } catch (Exception e) {
            throw new BusinessException(SystemResultStatus.EXCEL_WRITING_ERROR);
        }
    }


/**
*这里面才是锁定单元格，和背景至灰
**/
private void writeTableData(Workbook workbook, List<ProductionPlanActualIEO> list) {
        Sheet sheet = workbook.getSheetAt(0);
        //要先将sheet保护起来。里面输入密码。也可以为“”
        sheet.protectSheet("");
        //设置样式，非锁定状态。true为锁定
        CellStyle unlockStyle = workbook.createCellStyle();
        unlockStyle.setLocked(false);
        //设置样式，背景颜色为灰色
        CellStyle greyStyle = workbook.createCellStyle();
        greyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        greyStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //我从第3行开始将数据导出到Excel中
        int rowNum = ROW_START_OF_DATA;
        for(ProductionPlanActualIEO data : list){
        	//一条数据一行，下面会进行++操作
            Row currentRow = sheet.getRow(rowNum);
            if (currentRow == null) {
                currentRow = sheet.createRow(rowNum);
            }
            //拿到或者创建单元格
            Cell cellId = getOrCreateCell(currentRow,COL_ID);
            //至灰，后面大致相同
            cellId.setCellValue(data.getId());

            Cell cellPlanNo = getOrCreateCell(currentRow,COL_PLAN_NO);
            cellPlanNo.setCellValue(data.getPlanNumber());
            cellPlanNo.setCellStyle(greyStyle);

            Cell cellPlanDate = getOrCreateCell(currentRow,COL_PLAN_DATE);
            cellPlanDate.setCellValue(data.getPlanDate());
            cellPlanDate.setCellStyle(greyStyle);

            Cell cellPlanClass = getOrCreateCell(currentRow,COL_PLAN_CLASS);
            cellPlanClass.setCellValue(data.getPlanShift());
            cellPlanClass.setCellStyle(greyStyle);

            Cell cellMaterielNo = getOrCreateCell(currentRow,COL_MATERIEL_NO);
            cellMaterielNo.setCellValue(data.getMaterialCode());
            cellMaterielNo.setCellStyle(greyStyle);

            Cell cellPlanNum = getOrCreateCell(currentRow,COL_PLAN_NUMBER);
            cellPlanNum.setCellValue(data.getPlanAmount().toString());
            cellPlanNum.setCellStyle(greyStyle);

            Cell cellUseTime = getOrCreateCell(currentRow,COL_USE_TIME);
            cellUseTime.setCellValue(data.getUseTime());
            cellUseTime.setCellStyle(greyStyle);


            Cell cellPlanConfirm = getOrCreateCell(currentRow,COL_PLAN_CONFIRM);
            cellPlanConfirm.setCellValue(data.getConfirmPlanAmount());


            Cell cellPlanAchievements = getOrCreateCell(currentRow,COL_PLAN_ACHIEVEMENTS);
            cellPlanAchievements.setCellValue(data.getActualAmount());

			//这里是设置可以编辑的单元格，也就是图中看到的可以编辑的cell
            if(data.getStaus().equals("0")){
                cellPlanConfirm.setCellStyle(unlockStyle);
                cellPlanAchievements.setCellStyle(unlockStyle);
            }else{
                cellPlanConfirm.setCellStyle(greyStyle);
                cellPlanAchievements.setCellStyle(greyStyle);
            }
            rowNum++;
        }
    }


 /**
     * 新建或获取单元格
     * @param row
     * @param colNum
     * @return
     */
    private Cell getOrCreateCell(Row row, int colNum) {
        Cell cell = row.getCell(colNum);
        if (cell == null) {
            cell = row.createCell(colNum);
        }
        return cell;
    }

这里可以跳过，我自己做的封装（部分）-----------------------------------------

public static void prepareExportXlsx(HttpServletResponse response, String filename) {
        String formattedFilename = formatFilename(filename, SUFFIX_XLSX);
        prepareResponse(response, formattedFilename);
    }
    public static String formatFilename(String filename, String suffix) {
        String user = "system";
        if (Context.isUserContext()) {
            user = Context.getUsername();
        }
        return String.format(FILENAME_PATTERN, filename, user, DateFormatUtils.format(new Date(), DEFAULT_TIME_FORMAT), suffix);
    }
    public static void prepareResponse(HttpServletResponse response, String filename) {
        try {
            String parsedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.name());
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.setHeader(HEADER_CONTENT_DISPOSITION,
                    "attachment; filename=" + parsedFilename);
            response.setHeader(HEADER_FILENAME, parsedFilename);
            response.setContentType(DEFAULT_CONTENT_TYPE);
        } catch (UnsupportedEncodingException e) {
            throw new IllegalStateException(e);
        }
    }

跳过结束-----------------------------------------------------------------------------------

有疑问的可以给我留言，有时间会回复！