CODEWITHSUNDEEP
javaexcelapache-poi
simbu94
simbu94

Reputation: 1012

Make column read-only using apache poi

I am using apache-poi for generating the excel file. I need to make the 4th column read-only and the remaining 2 columns will be editable by the user.

I am using XSSFCellStyle to achieve this but it's not working for me.

The entire code is:

Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();

XSSFCellStyle style5 = wb.createCellStyle();
XSSFFont headerFont = wb.createFont();
headerFont.setBold(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style5.setFont(headerFont);
style5.setLocked(true); // this line does not get executed.
styles.put("header", style5);

Upvotes: 11

Views: 18479

Answers (3)

Priyabratta Biswal
Priyabratta Biswal

Reputation: 1

Workbook wb = new HSSFWorkbook(); Sheet sheet =wb.createSheet(thismonth+" , "+thisYear); sheet.protectSheet("password");

the above makes the whole sheet un editable. if u want to make a particular column editable. assign a particular CellStyle to it. and make it editable by .setLocked(false);

Calendar cal = Calendar.getInstance();

    int lastdate = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
    
    String[] monthName = {"January", "February",
            "March", "April", "May", "June", "July",
            "August", "September", "October", "November",
            "December"};

    String thismonth = monthName[cal.get(Calendar.MONTH)];
    int thisYear = cal.get(Calendar.YEAR);
    Workbook wb = new HSSFWorkbook();
    Sheet sheet =wb.createSheet();
    sheet.protectSheet("password");// making the sheet uneaditable.(password 
   protected)

    CellStyle cs2 = wb.createCellStyle();
    cs2.setLocked(false); //cells with this style will be editable.
    cs2.setBorderBottom(BorderStyle.THICK);
    cs2.setBorderTop(BorderStyle.THICK);
    cs2.setBorderLeft(BorderStyle.THICK);
    cs2.setBorderRight(BorderStyle.THICK);
    cs2.setAlignment(HorizontalAlignment.CENTER);
    cs2.setVerticalAlignment(VerticalAlignment.CENTER);
    
    CellStyle cs3 = wb.createCellStyle();//cells with this style will not be editable
    cs3.setBorderBottom(BorderStyle.THICK);
    cs3.setBorderTop(BorderStyle.THICK);
    cs3.setBorderLeft(BorderStyle.THICK);
    cs3.setBorderRight(BorderStyle.THICK);
    cs3.setAlignment(HorizontalAlignment.CENTER);
    cs3.setVerticalAlignment(VerticalAlignment.CENTER);
    
    
    for (int r=2;r<lastdate+2;r++)
    {
        Row rowloop = sheet.createRow(r);
        for (int c=0;c<3;c++)
        {
            if(c== 0)
            {
                Cell cellllop = rowloop.createCell(c);
                cellllop.setCellStyle(cs3);//assigning un editable style to one column
                
                String zero="";
                if(r<11) {
                    zero="0";
                }
                cellllop.setCellValue(zero+(r-1) +"-"+thismonth.substring(0, 3)+"- 
                           "+String.valueOf(thisYear).substring(0, 2));
            }else {
            Cell cellllop = rowloop.createCell(c);
            cellllop.setCellStyle(cs2);
            }
        }
         
    }
     
     

    try {
        FileOutputStream fos = new FileOutputStream(new File("c:/output/XlsxSheet32.xls"));

        wb.write(fos);
        System.out.println("file created");
        fos.close();
        
    } catch (Exception e) {
       e.printStackTrace();
    }

Upvotes: 0

Mani kandan
Mani kandan

Reputation: 369

CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[] { "ReadOnlyCell" });
        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
    dataValidation.setSuppressDropDownArrow(true);

    dataValidation.setEmptyCellAllowed(false);
    dataValidation.createErrorBox("Error", "Cell can not be editable");
    spreadsheet.addValidationData(dataValidation);

Upvotes: 0

Kai
Kai

Reputation: 39631

You have to protect the whole sheet and unlock the cells which should be editable:

String file = "c:\\poitest.xlsx";
FileOutputStream outputStream = new FileOutputStream(file);
Workbook wb = new XSSFWorkbook();

CellStyle unlockedCellStyle = wb.createCellStyle();
unlockedCellStyle.setLocked(false);

Sheet sheet = wb.createSheet();
sheet.protectSheet("password");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("TEST");
cell.setCellStyle(unlockedCellStyle);

wb.write(outputStream);
outputStream.close();

Upvotes: 21

Related Questions