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

您所在的位置：网站首页 › 如何让excel不可编辑 › java 使用poi导出Excel，设置单元格保护不可编辑，设置下拉框

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

2024-06-30 13:41| 来源: 网络整理| 查看: 265

一、需求： 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 resultlist = new ArrayList(); List rowlist = (List) 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; } }

【本文地址】

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

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

今日新闻

推荐新闻