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

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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.vaadin.addon.spreadsheet.SpreadsheetHandlerImpl.java

@Override
public void onPaste(String text) {
    Workbook workbook = spreadsheet.getWorkbook();
    Sheet activesheet = workbook.getSheetAt(workbook.getActiveSheetIndex());

    CellReference selectedCellReference = spreadsheet.getSelectedCellReference();

    String[] lines;/*from   w w w .jav  a 2  s. c o m*/
    if (text.indexOf("\r\n") > -1) {
        lines = text.split("\r\n");
    } else if (text.indexOf("\n") > -1) {
        lines = text.split("\n");
    } else {
        lines = text.split("\r");
    }

    int pasteHeight = lines.length;
    int pasteWidth = 1;
    for (String line : lines) {
        String[] tokens = splitOnTab(line);
        pasteWidth = Math.max(pasteWidth, tokens.length);
    }

    int rowIndex = selectedCellReference.getRow();
    int colIndex = selectedCellReference.getCol();

    // Check for protected cells at target
    for (int i = 0; i < pasteHeight; i++) {
        Row row = activesheet.getRow(rowIndex + i);
        if (row != null) {
            for (int j = 0; j < pasteWidth; j++) {
                Cell cell = row.getCell(colIndex + j);
                if (spreadsheet.isCellLocked(cell)) {
                    protectedCellWriteAttempted();
                    return;
                }
            }
        }
    }

    CellValueCommand command = new CellValueCommand(spreadsheet);
    CellRangeAddress affectedRange = new CellRangeAddress(rowIndex, rowIndex + pasteHeight - 1, colIndex,
            colIndex + pasteWidth - 1);
    command.captureCellRangeValues(affectedRange);

    for (int i = 0; i < pasteHeight; i++) {
        String line = lines[i];
        Row row = activesheet.getRow(rowIndex + i);
        if (row == null) {
            row = activesheet.createRow(rowIndex + i);
        }
        String[] tokens = splitOnTab(line);
        for (int j = 0; j < pasteWidth; j++) {
            Cell cell = row.getCell(colIndex + j);
            if (cell == null) {
                cell = row.createCell(colIndex + j);
            }
            if (j < tokens.length) {
                String cellContent = tokens[j];
                Double numVal = SpreadsheetUtil.parseNumber(cell, cellContent, spreadsheet.getLocale());
                if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else {
                    cell.setCellValue(cellContent);
                }
            } else {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
                spreadsheet.markCellAsDeleted(cell, true);
            }

            spreadsheet.getCellValueManager().markCellForUpdate(cell);
            spreadsheet.getCellValueManager().getFormulaEvaluator().notifyUpdateCell(cell);
        }
    }

    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    spreadsheet.updateMarkedCells();
    // re-set selection to copied area
    spreadsheet.setSelectionRange(rowIndex, colIndex, rowIndex + pasteHeight - 1, colIndex + pasteWidth - 1);

    fireCellValueChangeEvent(affectedRange);
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

public void reloadActiveSheetColumnRowStyles() {
    final Workbook workbook = spreadsheet.getWorkbook();

    if (spreadsheet.getState().rowIndexToStyleIndex == null) {
        spreadsheet.getState().rowIndexToStyleIndex = new HashMap<Integer, Integer>(
                workbook.getNumCellStyles());
    } else {// w  ww .jav a  2s .c o m
        spreadsheet.getState().rowIndexToStyleIndex.clear();
    }
    if (spreadsheet.getState().columnIndexToStyleIndex == null) {
        spreadsheet.getState().columnIndexToStyleIndex = new HashMap<Integer, Integer>(
                workbook.getNumCellStyles());
    } else {
        spreadsheet.getState().columnIndexToStyleIndex.clear();
    }
    if (spreadsheet.getState().lockedColumnIndexes == null) {
        spreadsheet.getState().lockedColumnIndexes = new HashSet<Integer>();
    } else {
        spreadsheet.getState().lockedColumnIndexes.clear();
    }
    if (spreadsheet.getState().lockedRowIndexes == null) {
        spreadsheet.getState().lockedRowIndexes = new HashSet<Integer>();
    } else {
        spreadsheet.getState().lockedRowIndexes.clear();
    }

    Sheet activeSheet = spreadsheet.getActiveSheet();
    for (int i = 0; i < spreadsheet.getRows(); i++) {
        Row row = activeSheet.getRow(i);
        if (row != null && row.getRowStyle() != null) {
            int styleIndex = row.getRowStyle().getIndex();
            spreadsheet.getState().rowIndexToStyleIndex.put(i + 1, styleIndex);
            if (row.getRowStyle().getLocked()) {
                spreadsheet.getState().lockedRowIndexes.add(i + 1);
            }
        } else {
            if (spreadsheet.isActiveSheetProtected()) {
                spreadsheet.getState().lockedRowIndexes.add(i + 1);
            }
        }
    }

    for (int i = 0; i < spreadsheet.getColumns(); i++) {
        if (activeSheet.getColumnStyle(i) != null) {
            int styleIndex = activeSheet.getColumnStyle(i).getIndex();
            spreadsheet.getState().columnIndexToStyleIndex.put(i + 1, styleIndex);
            if (activeSheet.getColumnStyle(i).getLocked()) {
                spreadsheet.getState().lockedColumnIndexes.add(i + 1);
            }
        }
    }
}

From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java

License:Apache License

@Override
public List<Requirement> importFile(boolean header) throws RequirementImportException, VMException {
    queue.clear();/*  w  ww.j ava  2  s .com*/
    List<Integer> errors = new ArrayList<>();
    HashMap<String, Object> parameters = new HashMap<>();
    List<Object> result;
    if (toImport == null) {
        throw new RequirementImportException("message.requirement.import.file.null");
    } else if (!toImport.exists()) {
        throw new RequirementImportException("message.requirement.import.file.invalid");
    } else {
        //Excel support
        if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) {
            try {
                Workbook wb = loadFile();
                org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);
                int rows = sheet.getPhysicalNumberOfRows();
                int r = 0;
                if (header) {
                    //Skip header row
                    r++;
                }
                for (; r < rows; r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }
                    if (row.getCell(0) == null) {
                        LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r);
                        break;
                    }
                    int cells = row.getPhysicalNumberOfCells();
                    if (cells < 2) {
                        LOG.log(Level.INFO, "Processing row: {0}", r);
                        LOG.warning(ResourceBundle
                                .getBundle("com.validation.manager.resources.VMMessages", Locale.getDefault())
                                .getString("message.requirement.import.missing.column")
                                .replaceAll("%c", "" + cells));
                        errors.add(r);
                    } else {
                        Requirement requirement = new Requirement();
                        LOG.log(Level.FINE, "Row: {0}", r);
                        for (int c = 0; c < cells; c++) {
                            Cell cell = row.getCell(c);
                            String value = "";
                            if (cell != null) {
                                switch (cell.getCellTypeEnum()) {
                                case FORMULA:
                                    value = cell.getCellFormula();
                                    break;
                                case NUMERIC:
                                    value = "" + cell.getNumericCellValue();
                                    break;
                                case STRING:
                                    value = cell.getStringCellValue();
                                    break;
                                default:
                                    value = "";
                                    break;
                                }
                            }
                            //Remove any extra spaces.
                            value = value.trim();
                            switch (c) {
                            case 0:
                                //Unique ID
                                LOG.fine("Setting id");
                                requirement.setUniqueId(value);
                                break;
                            case 1:
                                //Description
                                LOG.fine("Setting desc");
                                requirement.setDescription(value);
                                break;
                            case 2:
                                //Optional Requirement type
                                LOG.fine("Setting requirement type");
                                parameters.clear();
                                parameters.put("name", value);
                                result = namedQuery("RequirementType.findByName", parameters);
                                if (result.isEmpty()) {
                                    //Assume a default
                                    parameters.clear();
                                    parameters.put("name", "SW");
                                    result = namedQuery("RequirementType.findByName", parameters);
                                }
                                requirement.setRequirementTypeId((RequirementType) result.get(0));
                                break;
                            case 3:
                                //Optional notes
                                LOG.fine("Setting notes");
                                requirement.setNotes(value);
                                break;
                            default:
                                throw new RequirementImportException("Invalid column detected: " + c);
                            }
                            LOG.fine(value);
                        }
                        //This shouldn't be null
                        assert rsn != null : "Requirement Spec Node is null?";
                        requirement.setRequirementSpecNode(rsn);
                        parameters.clear();
                        parameters.put("status", "general.open");
                        result = namedQuery("RequirementStatus.findByStatus", parameters);
                        requirement.setRequirementStatusId((RequirementStatus) result.get(0));
                        assert requirement.getUniqueId() != null
                                && !requirement.getUniqueId().isEmpty() : "Invalid requirement detected!";
                        try {
                            if (!exists(requirement) && !queue.containsKey(requirement.getUniqueId())) {
                                queue.put(requirement.getUniqueId(), requirement);
                            }
                        } catch (IllegalOrphanException | NonexistentEntityException ex) {
                            Exceptions.printStackTrace(ex);
                        }
                    }
                }
            } catch (InvalidFormatException | IOException ex) {
                LOG.log(Level.SEVERE, null, ex);
            } finally {
                try {
                    if (inp != null) {
                        inp.close();
                    }
                } catch (IOException ex) {
                    LOG.log(Level.SEVERE, null, ex);
                }
            }
        } else if (toImport.getName().endsWith(".xml")) {
            throw new RequirementImportException("XML importing not supported yet.");
        } else if (toImport.getName().endsWith(".doc") || toImport.getName().endsWith(".docx")) {
            try {
                TableExtractor te = new TableExtractor(toImport);
                List<DefaultTableModel> tables = te.extractTables();
                Requirement requirement = new Requirement();
                LOG.log(Level.INFO, "Imported {0} tables!", tables.size());
                int count = 1;
                for (DefaultTableModel model : tables) {
                    int rows = model.getRowCount();
                    int cols = model.getColumnCount();
                    LOG.log(Level.INFO, "Processing table {0} with {1} " + "rows and {2} columns.",
                            new Object[] { count, rows, cols });
                    for (int r = 0; r < rows; r++) {
                        for (int c = 0; c < cols; c++) {
                            String value = (String) model.getValueAt(rows, cols);
                            switch (c) {
                            case 0:
                                //Unique ID
                                LOG.fine("Setting id");
                                requirement.setUniqueId(value);
                                break;
                            case 1:
                                //Description
                                LOG.fine("Setting desc");
                                requirement.setDescription(value);
                                break;
                            case 2:
                                //Requirement type
                                LOG.fine("Setting requirement type");
                                parameters.clear();
                                parameters.put("name", value);
                                result = namedQuery("RequirementType.findByName", parameters);
                                if (result.isEmpty()) {
                                    //Assume a default
                                    parameters.clear();
                                    parameters.put("name", "SW");
                                    result = namedQuery("RequirementType.findByName", parameters);
                                }
                                requirement.setRequirementTypeId((RequirementType) result.get(0));
                                break;
                            case 3:
                                //Optional notes
                                LOG.fine("Setting notes");
                                requirement.setNotes(value);
                                break;
                            default:
                                throw new RuntimeException("Invalid column detected: " + c);
                            }
                        }
                    }
                }
            } catch (IOException | ClassNotFoundException ex) {
                Exceptions.printStackTrace(ex);
            }
        } else {
            throw new RequirementImportException("Unsupported file format: " + toImport.getName());
        }
        StringBuilder sb = new StringBuilder("Rows with erros:\n");
        errors.stream().forEach((line) -> {
            sb.append(line).append('\n');
        });
        if (!errors.isEmpty()) {
            getDefault().lookup(MessageHandler.class).info(sb.toString());
        }
        return new ArrayList(queue.values());
    }
}

From source file:com.validation.manager.core.tool.step.importer.StepImporter.java

License:Apache License

@Override
public List<Step> importFile(boolean header) throws TestCaseImportException {
    steps.clear();/* w ww . java  2 s .  c  o m*/
    if (toImport == null) {
        throw new TestCaseImportException("message.step.import.file.null");
    } else if (!toImport.exists()) {
        throw new TestCaseImportException("message.step.import.file.invalid");
    } else {
        //Excel support
        if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) {
            InputStream inp = null;
            try {
                inp = new FileInputStream(toImport);
                org.apache.poi.ss.usermodel.Workbook wb = create(inp);
                org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);
                int rows = sheet.getPhysicalNumberOfRows();
                int r = 0;
                if (header) {
                    //Skip header row
                    r++;
                }
                for (; r < rows; r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }
                    int cells = row.getPhysicalNumberOfCells();
                    if (row.getCell(0) == null) {
                        LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r);
                        break;
                    }
                    if (cells < 2) {
                        throw new TestCaseImportException(RB.getString("message.step.import.missing.column")
                                .replaceAll("%c", "" + cells));
                    }
                    Step step = new Step();
                    step.setRequirementList(new ArrayList<>());
                    HashMap<String, Object> parameters = new HashMap<>();
                    List<Object> result;
                    LOG.log(Level.FINE, "Row: {0}", r);
                    for (int c = 0; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        String value = null;
                        if (cell != null) {
                            switch (cell.getCellType()) {

                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + cell.getNumericCellValue();
                                break;
                            case Cell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            default:
                                //Do nothing.
                            }
                        }
                        switch (c) {
                        case 0:
                            if (value != null) {
                                //Sequence
                                LOG.fine("Setting sequence");
                                Integer val = value.contains(".")
                                        ? valueOf(value.substring(0, value.indexOf(".")))
                                        : valueOf(value);
                                if (!tc.getStepList().isEmpty()) {
                                    int max = 0;
                                    for (Step s : tc.getStepList()) {
                                        if (s.getStepSequence() > max) {
                                            max = s.getStepSequence();
                                        }
                                    }
                                    //Make sure there isn't one on that sequence already
                                    val += max;
                                }
                                step.setStepSequence(val);
                            }
                            break;
                        case 1:
                            if (value != null) {
                                //Text
                                LOG.fine("Setting text");
                                step.setText(value.getBytes("UTF-8"));
                            }
                            break;
                        case 2:
                            //Optional Related requirements
                            if (value != null && !value.trim().isEmpty()) {
                                LOG.fine("Setting related requirements");
                                StringTokenizer st = new StringTokenizer(value, ",");
                                while (st.hasMoreTokens()) {
                                    String token = st.nextToken().trim();
                                    parameters.clear();
                                    parameters.put("uniqueId", token);
                                    result = namedQuery("Requirement.findByUniqueId", parameters);
                                    if (!result.isEmpty()) {
                                        for (Object o : result) {
                                            step.getRequirementList().add((Requirement) o);
                                        }
                                    }
                                }
                            }
                            break;
                        case 3:
                            if (value != null) {
                                //Optional Expected result
                                LOG.fine("Setting expected result");
                                step.setExpectedResult(value.getBytes("UTF-8"));
                            }
                            break;
                        case 4:
                            if (value != null) {
                                //Optional notes
                                LOG.fine("Setting notes");
                                step.setNotes(value);
                            }
                            break;

                        default:
                            throw new RuntimeException("Invalid column detected: " + c);
                        }
                        LOG.fine(value);
                    }
                    step.setTestCase(tc);
                    steps.add(step);
                }
            } catch (InvalidFormatException | IOException ex) {
                LOG.log(Level.SEVERE, null, ex);
            } finally {
                try {
                    if (inp != null) {
                        inp.close();
                    }
                } catch (IOException ex) {
                    LOG.log(Level.SEVERE, null, ex);
                }
            }
        } else if (toImport.getName().endsWith(".xml")) {
            throw new TestCaseImportException("XML importing not supported yet.");
        } else {
            throw new TestCaseImportException("Unsupported file format: " + toImport.getName());
        }
        return steps;
    }
}

From source file:com.virtusa.isq.vtaf.runtime.SeleniumTestBase.java

License:Apache License

/**
 * Adds the values from excel./* ww w. j  a  va 2 s .  com*/
 * 
 * @param path
 *            the path
 * @param index
 *            the index
 * @return the string[][]
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws InvalidFormatException
 *             the invalid format exception
 */
public final String[][] addValuesFromExcel(final String path, final String index)
        throws IOException, InvalidFormatException {

    String cellStringValue = null;
    double cellDoubleValue = 0;
    Boolean cellBooleanValue;
    byte cellErrorValue = 0;
    String[][] arrExcelContent;
    FileInputStream file = null;
    Workbook workbook = null;

    Sheet sheet = null;
    try {
        file = new FileInputStream(new File(path));
        workbook = WorkbookFactory.create(file);
        sheet = workbook.getSheetAt(Integer.parseInt(index));
        Iterator<Row> rowIterator = sheet.iterator();
        arrExcelContent = new String[sheet.getPhysicalNumberOfRows()][];
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNumber = row.getRowNum();
            Iterator<Cell> cellIterator = row.cellIterator();
            arrExcelContent[rowNumber] = new String[sheet.getRow(rowNumber).getPhysicalNumberOfCells()];
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int cellNumber = cell.getColumnIndex();
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    cellStringValue = cell.getStringCellValue();
                    arrExcelContent[rowNumber][cellNumber] = cellStringValue;
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    cellBooleanValue = cell.getBooleanCellValue();
                    arrExcelContent[rowNumber][cellNumber] = cellBooleanValue.toString();
                } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                    cellErrorValue = cell.getErrorCellValue();
                    arrExcelContent[rowNumber][cellNumber] = Byte.toString(cellErrorValue);
                } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    cellStringValue = cell.getCellFormula();
                    arrExcelContent[rowNumber][cellNumber] = cellStringValue;

                } else {

                    cellDoubleValue = cell.getNumericCellValue();
                    arrExcelContent[rowNumber][cellNumber] = Double.toString(cellDoubleValue);
                }
            }

        }
    } finally {
        if (((InputStream) workbook) != null) {
            ((InputStream) workbook).close();
        }
    }
    return arrExcelContent;
}

From source file:com.wabacus.system.assistant.StandardExcelAssistant.java

License:Open Source License

public void setRegionCellStringValue(Workbook workbook, Sheet sheet, CellRangeAddress region,
        CellStyle cellStyle, String cellvalue) {
    createRowAndColInRegion(sheet, region, cellStyle);
    Row rowTmp = sheet.getRow(region.getFirstRow());
    Cell cellTmp = rowTmp.getCell(region.getFirstColumn());
    cellTmp.setCellStyle(cellStyle);//from  w  ww .  j a v  a  2 s.c  om
    cellTmp.setCellValue(cellvalue);
    sheet.addMergedRegion(region);
}

From source file:com.wabacus.system.assistant.StandardExcelAssistant.java

License:Open Source License

public void setRegionCellRealTypeValue(Workbook workbook, Sheet sheet, CellRangeAddress region,
        CellStyle cellStyle, CellStyle cellStyleWithFormat, String align, Object cellvalue, IDataType typeObj) {
    createRowAndColInRegion(sheet, region, cellStyle);
    Row rowTmp = sheet.getRow(region.getFirstRow());//rowsetRegionStyle()
    Cell cellTmp = rowTmp.getCell(region.getFirstColumn());
    cellTmp.setCellStyle(cellStyle);//w  w  w . j  a v a2 s. c  o  m
    this.setCellValue(workbook, align, cellTmp, cellvalue, typeObj, cellStyleWithFormat);
    sheet.addMergedRegion(region);
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Highlight cells based on their values
 *//*w  ww  .j  a  va2s.c  o  m*/
static void sameCell(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(74);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Cell Value Is   greater than  70   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    // Condition 2: Cell Value Is  less than      50   (Green Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };

    sheetCF.addConditionalFormatting(regions, rule1, rule2);

    sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)");
    sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Highlight multiple cells based on a formula
 *///ww  w.j  av  a2s .co m
static void multiCell(Sheet sheet) {
    // header row
    Row row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("Units");
    row0.createCell(1).setCellValue("Cost");
    row0.createCell(2).setCellValue("Total");

    Row row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue(71);
    row1.createCell(1).setCellValue(29);
    row1.createCell(2).setCellValue(2059);

    Row row2 = sheet.createRow(2);
    row2.createCell(0).setCellValue(85);
    row2.createCell(1).setCellValue(29);
    row2.createCell(2).setCellValue(2059);

    Row row3 = sheet.createRow(3);
    row3.createCell(0).setCellValue(71);
    row3.createCell(1).setCellValue(29);
    row3.createCell(2).setCellValue(2059);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =$B2>75   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75   (Blue Fill)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  Use Excel conditional formatting to check for errors,
 *  and change the font colour to match the cell colour.
 *  In this example, if formula result is  #DIV/0! then it will have white font colour.
 *//*from w  w w . ja v a  2s.  c  o m*/
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}