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

男娘i 2022-06-04 10:10 4362阅读 0赞

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

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

//设置列格式，注释1
    sheet.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、设置列默认未锁定**

//设置列格式，注释1
    sheet.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;
        }
    
        }