Example usage for org.apache.poi.ss.usermodel Sheet protectSheet

List of usage examples for org.apache.poi.ss.usermodel Sheet protectSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. protectSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet protectSheet.

Prototype

public void protectSheet(String password);

Source Link

Document

Sets the protection enabled as well as the password

Usage

From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

License:Open Source License

private File createExcelTempFile(Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map,
        AbstractEntity abstractEntity, String nomFichierSansExtension) throws IOException {
    Workbook wb = new XSSFWorkbook();
    Map<Service, List<ArticleDemande>> mapServiceListeArticleDemande = map.get(abstractEntity);

    for (Service service : mapServiceListeArticleDemande.keySet()) {
        Sheet sheet = wb
                .createSheet(service.getDirection().getLibelleCourt() + "-" + service.getLibelleCourt());
        sheet.protectSheet(configService.getMotDePasseVerrouExcel());

        construitEntete(wb, mapServiceListeArticleDemande, abstractEntity, service, sheet);
        remplitLigneArticle(wb, mapServiceListeArticleDemande.get(service), sheet);

        // Taille automatique des colonnes selon le contenu
        for (int i = 0; i < 20; i++) {
            sheet.autoSizeColumn(i);/* w w  w .  ja v a 2  s  . co m*/
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 768);
        }
    }
    // Ecriture dans le fichier
    File result = File.createTempFile(nomFichierSansExtension, ".xlsx");
    FileOutputStream fileStream = new FileOutputStream(result);
    wb.write(fileStream);
    return result;
}

From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java

License:Open Source License

public void createDataValidator(Workbook wb, Sheet sheet, String[] data, String dataValidationName) {
    String reference = null;//w  ww .  j  a  v  a  2 s.com

    /*
     * se escriben las listas con los datos en cada sheet deseada
     */
    for (int i = 0; i < data.length; i++) {
        final Row fila = sheet.createRow(i);
        final Cell celda = fila.createCell(0);
        celda.setCellValue(data[i]);
    }

    // se protege el sheet para evitar que el usuario lo modifique
    sheet.protectSheet("marlo-ciat");

    /*
     * se crea referencia la lista de datos con el que se creara el data validator
     */
    final Name namedCountry = wb.createName();
    namedCountry.setNameName(dataValidationName);
    reference = sheet.getSheetName() + "!$A$1:$A$" + data.length; // area de referencia
    namedCountry.setRefersToFormula(reference);

}

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

/**
 * Write protect Sheet by setting password works only for xls output at the moment
 *///ww  w.ja  v  a 2s.  c o  m
protected void protectSheet(Sheet sheet, String password) {
    if (sheet instanceof HSSFSheet) {
        // Write protect Sheet by setting password
        // works only for xls output at the moment
        sheet.protectSheet(password);
    }
}

From source file:output.ExcelM3Upgrad.java

private void writeMigration() {
    Sheet sheet = workbook.getSheetAt(0);
    workbook.setSheetName(0, "Migration");
    sheet.setDisplayGridlines(false);/*w w w. j  av  a  2  s  . c  om*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    styles = createStyles(workbook);

    int rownum = beginROW;
    int cellnum = beginCOL;
    Row row = sheet.createRow(rownum++);
    for (int k = 0; k < model.getListColumn().length; k++) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng()));
        cell.setCellStyle(styles.get("header"));
        sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden());
        sheet.autoSizeColumn(k);
        dialStatus();
    }
    ArrayList<Integer> listHeader = new ArrayList<>();
    for (int i = 0; i < M3UpdObjModel.header.length; i++) {
        listHeader.add(M3UpdObjModel.header[i]);
    }

    String[] listLevel = i18n.Language.traduce(Ressource.listLevel)
            .toArray(new String[Ressource.listLevel.length]);

    data = model.getData();
    for (int i = 0; i < data.length; i++) {
        busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length);
        row = sheet.createRow(rownum++);
        Object[] objArr = data[i];
        cellnum = beginCOL;
        boolean first = true;
        int j = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof Date) {
                cell.setCellValue((Date) obj);
            } else if (obj instanceof Boolean) {
                if (first) {
                    first = false;
                    if ((Boolean) obj) {
                        cell.setCellValue("Oui");
                    } else {
                        cell.setCellValue("Non");
                    }
                } else {
                    if ((Boolean) obj) {
                        cell.setCellValue("OK");
                    } else {
                        cell.setCellValue("KO");
                    }
                }
            } else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
            if (listHeader.indexOf(218) == j) {
                try {
                    int n = Integer.parseInt(obj.toString().trim());
                    if (n == -1) {
                        cell.setCellValue("ERROR");
                    } else {
                        cell.setCellValue(listLevel[n]);
                    }
                } catch (NumberFormatException ex) {
                    cell.setCellValue("");
                }

            }

            if (j < objArr.length - 3) {
                cell.setCellStyle(styles.get("cell_b_centered_locked"));
            } else {
                cell.setCellStyle(styles.get("cell_b_centered"));
            }
            j++;
            dialStatus();
        }
        dialStatus();
    }

    dialStatus();
    busyDial.setText("Formatage du document");
    CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 1, beginCOL + data[0].length - 1);
    DataValidationConstraint userConstraint;
    DataValidation userValidation;

    if (type == 0) {
        userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel()
                .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = new HSSFDataValidation(userList, userConstraint);
    } else {
        XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        userConstraint = (XSSFDataValidationConstraint) userHelper
                .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect()
                        .toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList);
    }
    sheet.addValidationData(userValidation);

    CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 2, beginCOL + data[0].length - 2);
    DataValidationConstraint migConstraint;
    DataValidation migValidation;

    if (type == 0) {
        migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = new HSSFDataValidation(migList, migConstraint);
    } else {
        XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        migConstraint = (XSSFDataValidationConstraint) migHelper
                .createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList);
    }
    sheet.addValidationData(migValidation);

    CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 3, beginCOL + data[0].length - 3);
    DataValidationConstraint levelConstraint;
    DataValidation levelValidation;

    ArrayList<String> listNameLevel = new ArrayList<>();
    listNameLevel.add("ERROR");
    listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length])
    if (type == 0) {
        levelConstraint = DVConstraint
                .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()]));
        levelValidation = new HSSFDataValidation(levelList, levelConstraint);
    } else {
        XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint(
                i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length]));
        levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList);
    }
    sheet.addValidationData(levelValidation);

    int irow = beginROW;
    int icol = beginCOL + model.getListColumn().length + 2;
    row = sheet.getRow(irow);
    Cell cell = row.createCell(icol);
    sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1));
    cell.setCellValue("Estimation de la charge");
    cell.setCellStyle(styles.get("header"));

    irow++;
    row = sheet.getRow(irow);

    int cpt = 0;
    ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel);
    for (String s : listStringLevel) {
        cell = row.createCell(icol);
        cell.setCellValue(s);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        cell = row.createCell(icol + 1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL);
        cell.setCellFormula(
                "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter
                        + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        irow++;
        row = sheet.getRow(irow);
        cpt++;
    }
    row = sheet.getRow(irow);
    cell = row.createCell(icol);
    cell.setCellValue("Total des charges");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));
    cell = row.createCell(icol + 1);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1));
    cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));

    for (int k = 0; k < model.getListColumn().length + 3; k++) {
        sheet.autoSizeColumn(k);
    }

    sheet.protectSheet("3kles2014");
}

From source file:output.ExcelM3Upgrad.java

private void writeGraph() {
    busyDial.setText("Gnration des graphiques statistiques");
    Sheet s = workbook.getSheetAt(1);
    workbook.setSheetName(1, "Statistiques");

    ArrayList<Integer> listHeader = new ArrayList<>();
    for (int i = 0; i < M3UpdObjModel.header.length; i++) {
        listHeader.add(M3UpdObjModel.header[i]);
    }/*from   ww w  .j a v  a2s. c om*/

    int irow = 4;
    Row row = s.createRow(irow);
    Cell cell = row.createCell(2);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7));
    cell.setCellValue("Rpartition des spcifiques");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    irow = 8;
    row = s.createRow(irow);
    for (int i = 0; i < com.app.main.Ressource.listTypeM3Entity.length; i++) {
        cell = row.createCell(3);
        cell.setCellValue(com.app.main.Ressource.listTypeM3Entity[i]);
        cell = row.createCell(4);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        String columnLetter = CellReference.convertNumToColString(beginCOL);
        cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
                + columnLetter + (beginROW + data.length + 1) + ",\""
                + com.app.main.Ressource.listTypeM3Entity[i] + "\")");
        irow++;
        row = s.createRow(irow);
        dialStatus();
    }

    irow = 4;
    row = s.getRow(irow);
    cell = row.createCell(10);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15));
    cell.setCellValue("Existance des sources");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    int posVal = listHeader.indexOf(199);
    posVal += beginCOL;

    irow = 8;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("OK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnLetter = CellReference.convertNumToColString(posVal);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + data.length + 1) + ",\"" + "Oui" + "\")");

    irow++;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("NOK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    //        columnLetter = CellReference.convertNumToColString(posVal);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + data.length + 1) + ",\"" + "Non" + "\")");

    irow = 24;
    row = s.createRow(irow);
    cell = row.createCell(2);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7));
    cell.setCellValue("Synthse de migration");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    int posMig = listHeader.indexOf(201);
    posMig += beginCOL;
    int posUser = listHeader.indexOf(202);
    posUser += beginCOL;

    irow = 28;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("OK+USER");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnMig = CellReference.convertNumToColString(posMig);
    String columnUser = CellReference.convertNumToColString(posUser);
    cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":"
            + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!"
            + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "<>\"" + ""
            + "\"))");

    irow++;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("OK");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":"
            + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!"
            + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "=\"" + ""
            + "\"))");

    irow++;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("NOK");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":"
            + columnMig + (beginROW + data.length + 1) + ",\"KO\")");

    irow++;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("Somme");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String posSum = CellReference.convertNumToColString(4);
    cell.setCellFormula("SUM(" + posSum + (irow - 2) + ":" + posSum + (irow) + ")");

    posVal = listHeader.indexOf(217);
    posVal += beginCOL;

    irow = 24;
    row = s.getRow(irow);
    cell = row.createCell(10);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15));
    cell.setCellValue("Analyse des objets instanciables");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    irow = 28;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("Class OK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    columnLetter = CellReference.convertNumToColString(posVal);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + data.length + 1) + ",\"" + "" + "\")");

    irow++;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("Class NOK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    columnLetter = CellReference.convertNumToColString(posVal);
    //cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "<>" + "\"&\"" + "*" + "\")");
    cell.setCellFormula("E32-N29");

    s.protectSheet("3kles2014");
}