Example usage for org.apache.poi.xssf.usermodel XSSFDataValidationHelper createValidation

List of usage examples for org.apache.poi.xssf.usermodel XSSFDataValidationHelper createValidation

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFDataValidationHelper
  6. createValidation

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFDataValidationHelper createValidation.

Prototype

public DataValidation createValidation(DataValidationConstraint constraint,
            CellRangeAddressList cellRangeAddressList)

Source Link

Usage

From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java

License:Apache License

private void createTicketTypeDropDown(Sheet formSheet, Sheet ticketTypeSheet, String[] ticketTypes) {
    XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet) formSheet);
    CellRangeAddressList ticketCellAddress = new CellRangeAddressList(7, 100, 6, 7);

    DataValidationConstraint constraint = validationHelper
            .createFormulaListConstraint(ticketTypeSheet.getSheetName() + "!$A$1:$A$" + ticketTypes.length);

    //        constraint = validationHelper.createExplicitListConstraint(ticketTypes);
    DataValidation dataValidation = validationHelper.createValidation(constraint, ticketCellAddress);
    dataValidation.setSuppressDropDownArrow(true);
    formSheet.addValidationData(dataValidation);
}

From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java

License:Open Source License

/**
 * Excel Workbook?./*w  ww. j a v a  2  s .co  m*/
 * 
 * @param colDesc 1717?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???1717
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??1717
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?1717
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * Excel Workbook?./* w w w.ja v a  2  s. co  m*/
 * 
 * @param colDesc
 *            "?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {

    // ???
    List<String> colFieldList = getColumnFieldList(titlesList);

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    // ?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    // ??1717
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // 
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < titlesList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(titlesList.get(i));
    }
    ClassInfo classInfo = null;
    // ?
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        if (classInfo == null)
            classInfo = ClassUtil.getClassInfo(obj.getClass());
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName);
            Object value = reflexField.getValue(obj);

            // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            // ??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date || value instanceof Timestamp) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue(sdf.format((Date) value));
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    // ??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    // 
    for (int i = 0; i < titlesList.size(); i++) {
        // ??
        sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:com.frameworkset.platform.util.POIExcelUtil2007.java

License:Open Source License

/**
 * Excel Workbook?./*from w w  w  .j a v a2  s.c  o  m*/
 * 
 * @param colDesc ?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private void insertDropBox(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col,
        String ref) {/*from   w w w.  j  a  va 2  s  .  c o  m*/
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createFormulaListConstraint(ref);
    CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
    XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
    validation.setShowErrorBox(true);
    validation.setSuppressDropDownArrow(true);
    validation.setShowPromptBox(true);
    sheetTracing.addValidationData(validation);
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private void insertCondition(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col,
        String min, String max) {
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createIntegerConstraint(OperatorType.BETWEEN, min, max);
    // dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"0000011", "0000021", "0000031"});
    CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
    XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
    validation.setShowErrorBox(true);/* w  w  w .  ja v a2  s .c om*/
    validation.setSuppressDropDownArrow(true);
    validation.setShowPromptBox(true);
    sheetTracing.addValidationData(validation);
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private void insertDecCondition(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col) {
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createDecimalConstraint(OperatorType.GREATER_OR_EQUAL, "0", "");
    // dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"0000011", "0000021", "0000031"});
    CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
    XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
    validation.setShowErrorBox(true);/*from   w  ww  .  j a  v  a  2 s  . co  m*/
    validation.setSuppressDropDownArrow(true);
    validation.setShowPromptBox(true);
    sheetTracing.addValidationData(validation);
}

From source file:output.ExcelM3Upgrad.java

private void writeMigration() {
    Sheet sheet = workbook.getSheetAt(0);
    workbook.setSheetName(0, "Migration");
    sheet.setDisplayGridlines(false);//w  w  w  .ja v  a  2 s.co  m
    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");
}