java 使用poi导出Excel，设置单元格保护不可编辑，设置下拉框

一、需求：
1、第一列 不可编辑（对应Excel中的保护工作表），其他列可以编辑
2、第五列和第十四列为下拉框
3、可以在导出后新增行数据
二、期间遇到的问题
1、无法设置成不可编辑
2、解决1后未满足需求3，即在导出的Excel中直接编辑新行是提示写保（原因是Excel单元格默认是锁定状态，而保护工作表是保护的锁定的单元格），思路：直接操作列
3、解决2后，发现直接编辑新行时下拉框没起到作用，只是导出的数据有下拉框

注意：
1、解决setDefaultColumnStyle无效的方法
你会发现，设置好style以后，column会隐藏起来，原因在createCellStyle的时候，默认宽度为0，所以会被隐藏起来了，所以需要设置宽度

//设置列格式，注释1sheet.setColumnWidth(i, 4000); //设置宽度//行号，样式sheet.setDefaultColumnStyle(i, unlockstyle);

说明：不考虑代码规范性，只作为参考使用

解决问题方法：
1、设置单元格保护（灵感来自于Excel操作，先锁定，再设置保护工作表）

//单元格锁定的样式XSSFCellStyle lockstyle = workBook.createCellStyle();lockstyle.setLocked(true);lockstyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());//单元格不锁定的样式XSSFCellStyle unlockstyle = workBook.createCellStyle();unlockstyle.setLocked(false);//这里设置单元格样式，需要保护的就设置成锁定，不需要保护的就设置成不锁定........// 设置锁定的单元格为写保护 注释2//sheet表加密：等效excel的审阅菜单下的保护工作表//sheet.protectSheet(new String("333"));//333是密码sheet.enableLocking();CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();sheetProtection.setSelectLockedCells(false);sheetProtection.setSelectUnlockedCells(false);sheetProtection.setFormatCells(true);sheetProtection.setFormatColumns(true);sheetProtection.setFormatRows(true);sheetProtection.setInsertColumns(true);sheetProtection.setInsertRows(false);sheetProtection.setInsertHyperlinks(true);sheetProtection.setDeleteColumns(true);sheetProtection.setDeleteRows(true);sheetProtection.setSort(false);sheetProtection.setAutoFilter(false);sheetProtection.setPivotTables(true);sheetProtection.setObjects(true);sheetProtection.setScenarios(true);

2、设置列默认未锁定

//设置列格式，注释1sheet.setColumnWidth(i, 4000); //设置宽度//行号，样式sheet.setDefaultColumnStyle(i, unlockstyle);

3、设置下拉框，没找到直接设置列的方法，所以将行设置的大了些

//运输方式String[] textList1 = new String[]{"值1","值二","值三"};XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper           .createExplicitListConstraint(textList1);//操作类型String[] textList2 = {"I","U","D"};XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper            .createExplicitListConstraint(textList2);//运输方式下拉 首行，末行，首列，末列addressList1 = new CellRangeAddressList(1,65536,4,4);dataValidation1 = (XSSFDataValidation) dvHelper.createValidation(        dvConstraint1, addressList1);      // 07默认setSuppressDropDownArrow(true);      // validation.setSuppressDropDownArrow(true);      // validation.setShowErrorBox(true);      sheet.addValidationData(dataValidation1);      //操作类型下拉 首行，末行，首列，末列      addressList2 = new CellRangeAddressList(1,65536,13,13);dataValidation2 = (XSSFDataValidation) dvHelper.createValidation(        dvConstraint2, addressList2);      // 07默认setSuppressDropDownArrow(true);      // validation.setSuppressDropDownArrow(true);      // validation.setShowErrorBox(true);sheet.addValidationData(dataValidation2);

所有代码

@Override@Transactional(propagation = Propagation.REQUIRED)public ResponseData exportData(String rowdatas,HttpServletResponse response){    ResponseData responseData = new ResponseData();    try {    JSONArray rowArray = JSONArray.fromObject(rowdatas);    List<RoutingRulesSetDto> resultlist = new ArrayList<RoutingRulesSetDto>();        List<RoutingRulesSetDto> rowlist = (List<RoutingRulesSetDto>) JSONArray.toCollection(rowArray, RoutingRulesSetDto.class);        resultlist = routingRuleMapper.selectByIds(rowlist);        XSSFWorkbook workBook = new XSSFWorkbook();        XSSFSheet sheet = workBook.createSheet();        //设置页名称        workBook.setSheetName(0, "sheet名");        //默认宽度        sheet.setDefaultColumnWidth(15);        //设置title样式        XSSFCellStyle titleStyle = workBook.createCellStyle();        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);        titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());        //单元格锁定的样式        XSSFCellStyle lockstyle = workBook.createCellStyle();        lockstyle.setLocked(true);        lockstyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);        lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());        //单元格不锁定的样式        XSSFCellStyle unlockstyle = workBook.createCellStyle();        unlockstyle.setLocked(false);        //设置导出表头        String[] header = new String[]{"ID(不可编辑)","字段1","字段2","字段3",                "字段4","字段5","字段6","字段7",                "字段8","字段9","字段10","字段11","字段12","字段13"};        //创建表头行        XSSFRow row = sheet.createRow(0);        XSSFCell cell;        //插入Excel表头        for (short i = 0; i < header.length;i++) {            cell =row.createCell(i);            if(i == 0){                cell.setCellStyle(lockstyle);            }else if(i==6 || i==7 || i==8 || i==10 || i==11 || i==12){            //设置列格式，注释1                sheet.setColumnWidth(i, 4000);                 sheet.setDefaultColumnStyle(i, unlockstyle);            }else{                cell.setCellStyle(titleStyle);                //设置列格式，注释1                sheet.setColumnWidth(i, 4000);                 sheet.setDefaultColumnStyle(i, unlockstyle);            }            XSSFRichTextString text = new XSSFRichTextString(header[i]);            cell.setCellValue(text);        }        //下拉列表        CellRangeAddressList addressList1 = null;        XSSFDataValidation dataValidation1 = null;        CellRangeAddressList addressList2 = null;        XSSFDataValidation dataValidation2 = null;        //运输方式        String[] textList1 = new String[]{"值1","值二","值三"};        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);        XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper                .createExplicitListConstraint(textList1);        //操作类型        String[] textList2 = {"I","U","D"};        XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper                    .createExplicitListConstraint(textList2);        //运输方式下拉 首行，末行，首列，末列        addressList1 = new CellRangeAddressList(1,65536,4,4);        dataValidation1 = (XSSFDataValidation) dvHelper.createValidation(                dvConstraint1, addressList1);        // 07默认setSuppressDropDownArrow(true);        // validation.setSuppressDropDownArrow(true);        // validation.setShowErrorBox(true);        sheet.addValidationData(dataValidation1);        //操作类型下拉 首行，末行，首列，末列        addressList2 = new CellRangeAddressList(1,65536,13,13);        dataValidation2 = (XSSFDataValidation) dvHelper.createValidation(                dvConstraint2, addressList2);        // 07默认setSuppressDropDownArrow(true);        // validation.setSuppressDropDownArrow(true);        // validation.setShowErrorBox(true);        sheet.addValidationData(dataValidation2);        //遍历插入数据        for (int i = 0; i < resultlist.size(); i++) {            row = sheet.createRow(i+1);            //                      cell = row.createCell(0);            cell.setCellValue(resultlist.get(i).getId() == null ? "" : resultlist.get(i).getId());            cell.setCellStyle(lockstyle);            //                      cell = row.createCell(1);            cell.setCellValue(resultlist.get(i).getOrCustomerId() == null ? "" : resultlist.get(i).getOrCustomerId());            cell.setCellStyle(unlockstyle);            //          cell = row.createCell(2);            cell.setCellValue(resultlist.get(i).getItemGid() == null ? "" : resultlist.get(i).getItemGid());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(3);            cell.setCellValue(resultlist.get(i).getCommodityGid() == null ? "" : resultlist.get(i).getCommodityGid());            cell.setCellStyle(unlockstyle);            //                      cell = row.createCell(4);            cell.setCellValue(resultlist.get(i).getTransportModeGid() == null ? "" : resultlist.get(i).getTransportModeGid());            cell.setCellStyle(unlockstyle);            //                      cell = row.createCell(5);            cell.setCellValue(resultlist.get(i).getOrSourceLocationGid() == null ? "" : resultlist.get(i).getOrSourceLocationGid());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(6);            cell.setCellValue(resultlist.get(i).getOrSourceLocationName() == null ? "" : resultlist.get(i).getOrSourceLocationName());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(7);            cell.setCellValue(resultlist.get(i).getSourceProvince() == null ? "" : resultlist.get(i).getSourceProvince());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(8);            cell.setCellValue(resultlist.get(i).getSourceCity() == null ? "" : resultlist.get(i).getSourceCity());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(9);            cell.setCellValue(resultlist.get(i).getOrDestLocationGid() == null ? "" : resultlist.get(i).getOrDestLocationGid());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(10);            cell.setCellValue(resultlist.get(i).getOrDestLocationName() == null ? "" : resultlist.get(i).getOrDestLocationName());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(11);            cell.setCellValue(resultlist.get(i).getDestProvince() == null ? "" : resultlist.get(i).getDestProvince());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(12);            cell.setCellValue(resultlist.get(i).getDestCity() == null ? "" : resultlist.get(i).getDestCity());            cell.setCellStyle(unlockstyle);            //            cell = row.createCell(13);            cell.setCellValue("U");            cell.setCellStyle(unlockstyle);        }        // 设置锁定的单元格为写保护 注释2        //sheet表加密：等效excel的审阅菜单下的保护工作表        //sheet.protectSheet(new String("333"));//333是密码        sheet.enableLocking();        CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();        sheetProtection.setSelectLockedCells(false);        sheetProtection.setSelectUnlockedCells(false);        sheetProtection.setFormatCells(true);        sheetProtection.setFormatColumns(true);        sheetProtection.setFormatRows(true);        sheetProtection.setInsertColumns(true);        sheetProtection.setInsertRows(false);        sheetProtection.setInsertHyperlinks(true);        sheetProtection.setDeleteColumns(true);        sheetProtection.setDeleteRows(true);        sheetProtection.setSort(false);        sheetProtection.setAutoFilter(false);        sheetProtection.setPivotTables(true);        sheetProtection.setObjects(true);        sheetProtection.setScenarios(true);        //创建流对象,并写入workBook        ByteArrayOutputStream os = new ByteArrayOutputStream();        try {            workBook.write(os);        } catch (Exception e) {            e.printStackTrace();        }        Date now = new Date();         SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");        String data1 = dateFormat.format( now );         byte[] content = os.toByteArray();        InputStream iStream = new ByteArrayInputStream(content);        response.reset();        response.setContentType("application/vnd.ms-excel;charset=utf-8");        response.setHeader("Content-Disposition", "attachment;filename=" + new String(("导出文件名" +data1+ ".xlsx").getBytes("gbk"), "iso-8859-1"));        ServletOutputStream oStream = response.getOutputStream();        BufferedInputStream bis = null;        BufferedOutputStream bos = null;        try {            bis = new BufferedInputStream(iStream);            bos = new BufferedOutputStream(oStream);            byte[] buff = new byte[2048];            int bytesRead;            while(-1 != (bytesRead = bis.read(buff,0,buff.length))){                bos.write(buff,0 ,bytesRead);            }        } catch (Exception e) {            throw e;        } finally {            try {                if(bis != null){                    bis.close();                }                if(bos != null){                    bos.close();                }                if(iStream != null){                    iStream.close();                }                if(oStream != null){                    oStream.close();                }                if(os != null){                    os.close();                }            } catch (Exception e2) {                e2.printStackTrace();            }        }        responseData.setSuccess(true);        responseData.setCode("S");        return responseData;    } catch (Exception e) {        e.printStackTrace();        responseData.setSuccess(false);        responseData.setCode("E");        responseData.setMessage("程序异常");        return responseData;    }    }