Example usage for org.apache.poi.ss.usermodel Cell getRowIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getRowIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getRowIndex

Introduction

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

Prototype

int getRowIndex();

Source Link

Document

Returns row index of a row in the sheet that contains this cell

Usage

From source file:com.jeefuse.system.log.web.imports.excel.GsysLoginlogExcelImport.java

License:GNU General Public License

/**
 * populate model./*  w w  w  .ja  va 2  s.  com*/
 * 
 * @generated
 */
@Override
protected GsysLoginlog readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysLoginlog model = new GsysLoginlog();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysLoginlogField gsysLoginlogField = GsysLoginlogField.valueOfFieldLabel(columnName);
        if (null == gsysLoginlogField) {
            gsysLoginlogField = GsysLoginlogField.valueOfFieldName(columnName);
        }
        if (null == gsysLoginlogField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysLoginlogField);
        InvalidValue[] invalidValues = GsysLoginlogValidate.validateProperty(model, gsysLoginlogField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysLoginlogField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.jeefuse.system.log.web.imports.excel.GsysOperatelogExcelImport.java

License:GNU General Public License

/**
 * populate model./*w  ww .j a v a  2s.com*/
 * 
 * @generated
 */
@Override
protected GsysOperatelog readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysOperatelog model = new GsysOperatelog();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysOperatelogField gsysOperatelogField = GsysOperatelogField.valueOfFieldLabel(columnName);
        if (null == gsysOperatelogField) {
            gsysOperatelogField = GsysOperatelogField.valueOfFieldName(columnName);
        }
        if (null == gsysOperatelogField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysOperatelogField);
        InvalidValue[] invalidValues = GsysOperatelogValidate.validateProperty(model, gsysOperatelogField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysOperatelogField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.jeefuse.system.param.web.imports.excel.GsysParameterExcelImport.java

License:GNU General Public License

/**
 * populate model./* w  w w . ja v  a  2  s .c  o m*/
 * 
 * @generated
 */
@Override
protected GsysParameter readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysParameter model = new GsysParameter();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysParameterField gsysParameterField = GsysParameterField.valueOfFieldLabel(columnName);
        if (null == gsysParameterField) {
            gsysParameterField = GsysParameterField.valueOfFieldName(columnName);
        }
        if (null == gsysParameterField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysParameterField);
        InvalidValue[] invalidValues = GsysParameterValidate.validateProperty(model, gsysParameterField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysParameterField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.jeefuse.system.security.web.imports.excel.GsysFunctionExcelImport.java

License:GNU General Public License

/**
 * populate model.//from   w  ww .  j ava2  s .c o m
 * 
 * @generated
 */
@Override
protected GsysFunction readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysFunction model = new GsysFunction();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysFunctionField gsysFunctionField = GsysFunctionField.valueOfFieldLabel(columnName);
        if (null == gsysFunctionField) {
            gsysFunctionField = GsysFunctionField.valueOfFieldName(columnName);
        }
        if (null == gsysFunctionField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysFunctionField);
        InvalidValue[] invalidValues = GsysFunctionValidate.validateProperty(model, gsysFunctionField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysFunctionField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.lapis.jsfexporter.excel.ExcelExportType.java

License:Apache License

@Override
public Row exportRow(IExportRow row) {
    Row xlsRow = sheet.createRow(rowCount++);
    int cellIndex = 0;
    for (IExportCell cell : row.getCells()) {
        boolean cellIsUsed;
        do {//from   w ww .j  a va2 s . c  o  m
            cellIsUsed = false;
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress region = sheet.getMergedRegion(i);
                if (region.isInRange(xlsRow.getRowNum(), cellIndex)) {
                    cellIsUsed = true;
                    cellIndex += region.getLastColumn() - region.getFirstColumn() + 1;
                }
            }
        } while (cellIsUsed);

        Cell xlsCell = xlsRow.createCell(cellIndex++);
        xlsCell.setCellValue(cell.getValue());

        if (cell.getColumnSpanCount() > 1 || cell.getRowSpanCount() > 1) {
            sheet.addMergedRegion(new CellRangeAddress(xlsCell.getRowIndex(),
                    xlsCell.getRowIndex() + cell.getRowSpanCount() - 1, xlsCell.getColumnIndex(),
                    xlsCell.getColumnIndex() + cell.getColumnSpanCount() - 1));
            cellIndex += cell.getColumnSpanCount() - 1;
        }
    }
    return xlsRow;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Map<String, String> managementMap = BscKpiCode.getManagementMap(false);
    //Map<String, String> calculationMap = BscKpiCode.getCalculationMap(false);
    int itemCols = 4;
    int mrRow = row;
    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);

        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);

            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                KpiVO kpi = objective.getKpis().get(kx);

                Row contentRow = sh.createRow(row++);
                contentRow.setHeight((short) 4000);

                int cell = 0;

                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(perspective.getName(), perspective.getScore(),
                            perspective.getWeight(), perspective.getTarget(), perspective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(/*  w  w w .  j av a 2  s . c o  m*/
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0 && ox == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("PERSPECTIVES",
                                perspective.getTarget(), perspective.getMin(), perspective.getScore(), "", "",
                                0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }
                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(objective.getName(), objective.getScore(),
                            objective.getWeight(), objective.getTarget(), objective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0 && kx == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("OBJECTIVES",
                                objective.getTarget(), objective.getMin(), objective.getScore(), "", "", 0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }
                for (int i = 0; i < itemCols; i++) {
                    //String content = this.getKpisContent(kpi, managementMap, calculationMap);
                    String content = this.getKpisContent(kpi, managementMap);
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("KPI", kpi.getTarget(),
                                kpi.getMin(), kpi.getScore(), kpi.getCompareType(), kpi.getManagement(),
                                kpi.getQuasiRange());
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }

            }

        }

    }

    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);
        sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow() - 1, 0, 3));

        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);
            sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow() - 1, 4, 7));

            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                sh.addMergedRegion(new CellRangeAddress(mrRow + kx, mrRow + kx, 8, 11));
            }

            mrRow += objective.getKpis().size();
        }

    }

    return row++;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createDateRange(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {
    String frequency = (String) context.get("frequency");
    String startYearDate = StringUtils.defaultString((String) context.get("startYearDate")).trim();
    String endYearDate = StringUtils.defaultString((String) context.get("endYearDate")).trim();
    String startDate = StringUtils.defaultString((String) context.get("startDate")).trim();
    String endDate = StringUtils.defaultString((String) context.get("endDate")).trim();
    String date1 = startDate;//  w  w w.j  a v  a 2  s  .c  o  m
    String date2 = endDate;
    if (BscMeasureDataFrequency.FREQUENCY_QUARTER.equals(frequency)
            || BscMeasureDataFrequency.FREQUENCY_HALF_OF_YEAR.equals(frequency)
            || BscMeasureDataFrequency.FREQUENCY_YEAR.equals(frequency)) {
        date1 = startYearDate + "/01/01";
        date2 = endYearDate + "/12/" + SimpleUtils.getMaxDayOfMonth(Integer.parseInt(endYearDate), 12);
    }
    Map<String, Object> headContentMap = new HashMap<String, Object>();
    this.fillHeadContent(context, headContentMap);

    XSSFCellStyle cellStyleLabel = wb.createCellStyle();
    cellStyleLabel.setFillForegroundColor(
            new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor())));
    cellStyleLabel.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFFont cellFontLabel = wb.createFont();
    cellFontLabel.setBold(false);
    cellFontLabel
            .setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor())));
    cellStyleLabel.setFont(cellFontLabel);
    cellStyleLabel.setWrapText(true);
    //cellStyleLabel.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyleLabel.setBorderBottom(BorderStyle.THIN);
    cellStyleLabel.setBorderTop(BorderStyle.THIN);
    cellStyleLabel.setBorderRight(BorderStyle.THIN);
    cellStyleLabel.setBorderLeft(BorderStyle.THIN);

    int cols = 4 + vision.getPerspectives().get(0).getObjectives().get(0).getKpis().get(0).getDateRangeScores()
            .size();
    int cell = 0;
    for (int i = 0; i < cols; i++) {
        String content = "Frequency: " + BscMeasureDataFrequency.getFrequencyMap(false).get(frequency)
                + " Date range: " + date1 + " ~ " + date2 + "\n"
                + StringUtils.defaultString((String) headContentMap.get("headContent"));
        Row headRow = sh.createRow(row);
        headRow.setHeight((short) 700);
        Cell headCell1 = headRow.createCell(cell);
        headCell1.setCellValue(content);
        headCell1.setCellStyle(cellStyleLabel);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;

    int kpiCols = 4;
    int kpiRows = 2;
    for (PerspectiveVO perspective : vision.getPerspectives()) {
        for (ObjectiveVO objective : perspective.getObjectives()) {
            for (KpiVO kpi : objective.getKpis()) {
                cell = 0;

                for (int r = 0; r < kpiRows; r++) {
                    Row contentRow = sh.createRow(row++);
                    contentRow.setHeight((short) 400);

                    for (int c = 0; c < kpiCols; c++) {
                        XSSFCellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setFillForegroundColor(
                                new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        XSSFFont cellFont = wb.createFont();
                        cellFont.setBold(false);
                        cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                        cellStyle.setFont(cellFont);
                        cellStyle.setWrapText(true);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        Cell contentCell1 = contentRow.createCell(c);
                        contentCell1.setCellValue(kpi.getName());
                        contentCell1.setCellStyle(cellStyle);

                    }

                    cell = 4;
                    if (r == 0) { // date

                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(dateRangeScore.getDate());
                            contentCell1.setCellStyle(cellStyle);
                        }

                    }
                    if (r == 1) { // score

                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(
                                    "      " + BscReportSupportUtils.parse2(dateRangeScore.getScore()));
                            contentCell1.setCellStyle(cellStyle);

                            byte[] imgBytes = BscReportSupportUtils.getByteIcon(kpi, dateRangeScore.getScore());
                            if (null != imgBytes) {
                                SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                        contentCell1.getColumnIndex());
                            }

                        }

                    }

                }

                sh.addMergedRegion(new CellRangeAddress(row - 2, row - 1, 0, kpiCols - 1));

            }
        }
    }

    return row++;
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * /*from   w w w . j  av a  2  s . com*/
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void addNumberValidation(Cell cell) {

    if (cell != null) {

        Sheet sheet = cell.getSheet();// w w w .j a  va2 s .  co  m
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createNumericConstraint(ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, "1.00",
                        "1000000000000.00");
        CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                cell.getColumnIndex(), cell.getColumnIndex());
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                addressList);
        validation.setErrorStyle(ErrorStyle.STOP);
        validation.createErrorBox("Error", "Only numeric values are allowed");
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?//  www  .  j  a v a 2 s  .c o m
 * @param cell 
 * @param map
 * @param oldString
 * @throws Exception 
 */
private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = !name.contains(PoiElUtil.FOREACH_NOT_CREATE);
    boolean isShift = name.contains(PoiElUtil.FOREACH_AND_SHIFT);
    name = name.replace(PoiElUtil.FOREACH_NOT_CREATE, PoiElUtil.EMPTY)
            .replace(PoiElUtil.FOREACH_AND_SHIFT, PoiElUtil.EMPTY).replace(PoiElUtil.FOREACH, PoiElUtil.EMPTY)
            .replace(PoiElUtil.START_STR, PoiElUtil.EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) com.qihang.winter.poi.util.PoiPublicUtil.getParamsValue(keys[0], map);
    List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = getAllDataColumns(cell,
            name.replace(keys[0], PoiElUtil.EMPTY));
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    Row row;
    int rowIndex = cell.getRow().getRowNum() + 1;
    //??
    if (its.hasNext()) {
        Object t = its.next();
        cell.getRow().setHeight(columns.get(0).getHeight());
        setForEeachCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map);
    }
    if (isShift) {
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1, cell.getRow().getSheet().getLastRowNum(),
                datas.size() - 1, true, true);
    }
    while (its.hasNext()) {
        Object t = its.next();
        if (isCreate) {
            row = cell.getRow().getSheet().createRow(rowIndex++);
        } else {
            row = cell.getRow().getSheet().getRow(rowIndex++);
            if (row == null) {
                row = cell.getRow().getSheet().createRow(rowIndex - 1);
            }
        }
        row.setHeight(columns.get(0).getHeight());
        setForEeachCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map);
    }
}