HSSF opi： excel中设置某列不可编辑

HSSF opi： excel中设置某列不可编辑

 

原理：

使用保护工作表的方式：

  excel中默认所有列都为选中状态，而保护工作表时保护的是锁定状态的列。 因此，要设置某列不可编辑的实现方式如下：

  1）、将要设置为不可编辑的列的锁定状态改为未锁定。

  2）、保护工作表。

代码：

String filePath = physicalDeploymentPath+File.separator+"Excel"+File.separator+excelName+".xls";

            OutputStream os = new FileOutputStream(filePath);

            HSSFWorkbook book = new HSSFWorkbook();

            

            HSSFCellStyle cellStyle = book.createCellStyle();

        cellStyle.setAlignment(HSSFCellStyle. ALIGN_CENTER );

        cellStyle.setFillPattern(HSSFCellStyle. SOLID_FOREGROUND );

        cellStyle.setFillForegroundColor(( short ) 40);

            

            HSSFCellStyle canEditStyle = book.createCellStyle();

            canEditStyle.setLocked( false);   //设置列的锁定状态为未锁定

             for (ExportExcel ee : lstExcel) {

                  HSSFSheet sheet = book.createSheet(ee.getCategory().getName());

                  HSSFRow row = sheet.createRow(0);

//                row.setRowStyle(bStyle);

                   // 列头

                   for ( int i = 0; i < exportTitleNameList.size(); i++) {

                        row.createCell(i).setCellValue(exportTitleNameList.get(i).toString());

                  }

                   // 列宽

//                for (int i = 0; i < exportTitleNameList.size(); i++) {

                  sheet.setColumnWidth(0, ( short ) ( 50 * 250 ) );

                  sheet.setColumnWidth(1, ( short ) ( 50 * 160 ) );

                  sheet.setColumnWidth(2, ( short ) ( 50 * 60 ) );

                  sheet.setColumnWidth(3, ( short ) ( 50 * 60 ) );

                  sheet.setColumnWidth(4, ( short ) ( 50 * 160 ) );

                  sheet.setColumnWidth(5, ( short ) ( 50 * 160 ) );

                  sheet.setColumnWidth(6, ( short ) ( 50 * 60 ) );

//                }

                  

                  String[] list = new String[ee.getTypeLst().size()];

                   int x = 0;

                   for (EntityType et:ee.getTypeLst()){

                        list[x] = et.getSubType();

                        x++;

                  }

                  Map<String, String> stateNames = new HashMap<String, String>();

                  stateNames.put( "CHECKIN" , "检入" );

                  stateNames.put( "CHECKOUT" , "检出" );

                  stateNames.put( "RELEASE" , "发布" );

                   int j = ee.getLstDocs().size();

                   for ( int i = 0; i < j; i++) {

                        Document doc = ee.getLstDocs().get(i);

                        String state = "" ;

                        DocumentRevision docrev = doc.getCurrentRevision();

                         if ((doc.getState().name().equals( "EDIT" ) || doc.getState().name().equals( "RELEASE" )) && docrev.getStatus() != null ) {

                              state = stateNames.get(docrev.getStatus().name());

                        } else {

                              state = stateNames.get(doc.getState().name());

                        }

                        row = sheet.createRow(i+1);

                        row.createCell(0).setCellValue(doc.getName());

                        row.createCell(1).setCellValue(doc.getKeyword());

                        row.createCell(2).setCellValue(state);

                        row.createCell(3).setCellValue(doc.getDocumentType().getSubType());

                        row.createCell(4).setCellValue(doc.getOrganizationUnit());

                        row.createCell(5).setCellValue(doc.getCurrentRevision().getRemark());

                        row.createCell(6).setCellValue(doc.getCurrentRevision().getRevision());

                         //以下列可以编辑

                        row.getCell(1).setCellStyle(canEditStyle);

                        row.getCell(4).setCellStyle(canEditStyle);

                        row.getCell(5).setCellStyle(canEditStyle);

                  }

                  

                   //创建空的列和行,使可编辑

                   for ( int i=j;i<j+2000;i++){

                        row = sheet.createRow(i+1);

                        row.createCell(0);

                        row.createCell(1);

                        row.createCell(2);

                        row.createCell(3);

                        row.createCell(4);

                        row.createCell(5);

                        row.createCell(6);

                         //以下列可以编辑

                        row.getCell(1).setCellStyle(canEditStyle);   //将未锁定样式添加至要设置为不可编辑的列。

                        row.getCell(4).setCellStyle(canEditStyle);

                        row.getCell(5).setCellStyle(canEditStyle);

                  }

                  

                   // 生成下拉列表

                   //从第2行第5列开始,到第ee.getLstDocs().size()+1行第5列有效

                  CellRangeAddressList regions = new CellRangeAddressList(1,j+2000 , 3, 3); //ee.getLstDocs().size()

                  

                   // 生成下拉框内容

                  DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);

                  

                   // 绑定下拉框和作用区域

                  HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);

                  

                   // 对sheet页生效

                  sheet.addValidationData(data_validation);

                   //设置密码保护文档不可以编辑,括号内是密码

                  sheet.protectSheet(new Date().toString());    //保护工作表