List of usage examples for org.apache.poi.ss.usermodel ComparisonOperator EQUAL
byte EQUAL
To view the source code for org.apache.poi.ss.usermodel ComparisonOperator EQUAL.
Click Source Link
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * Checks if the given cell value matches a * {@link ConditionalFormattingRule} of <code>VALUE_IS</code> type. Covers * all cell types and comparison operations. * * @param cell/*from w w w. j a v a 2 s .co m*/ * Target cell * @param rule * Conditional formatting rule to match against. * @return True if the given cells value matches the given * <code>VALUE_IS</code> rule, false otherwise */ protected boolean matchesValue(Cell cell, ConditionalFormattingRule rule) { boolean isFormulaType = cell.getCellType() == Cell.CELL_TYPE_FORMULA; boolean isFormulaStringType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING; boolean isFormulaBooleanType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_BOOLEAN; boolean isFormulaNumericType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC; if (isFormulaType) { try { // make sure we have the latest value for formula cells getFormulaEvaluator().evaluateFormulaCell(cell); } catch (NotImplementedException e) { LOGGER.log(Level.FINEST, e.getMessage(), e); return false; } } // other than numerical types if (cell.getCellType() == Cell.CELL_TYPE_STRING || isFormulaStringType) { // Excel stores conditional formatting strings surrounded with ", so // we must surround the cell value. String cell value from POI is // never null. String quotedStringValue = String.format("\"%s\"", cell.getStringCellValue()); // Excel string comparison ignores case switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return quotedStringValue.equalsIgnoreCase(rule.getFormula1()); case ComparisonOperator.NOT_EQUAL: return !quotedStringValue.equalsIgnoreCase(rule.getFormula1()); } } if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN || isFormulaBooleanType) { // not sure if this is used, since no boolean option exists in // Excel.. Boolean formulaVal = Boolean.parseBoolean(rule.getFormula1()); switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return cell.getBooleanCellValue() == formulaVal; case ComparisonOperator.NOT_EQUAL: return cell.getBooleanCellValue() != formulaVal; } } // numerical types if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || isFormulaNumericType) { double formula1Val = -1; try { formula1Val = Double.valueOf(rule.getFormula1()); } catch (NumberFormatException w) { // non-numeric formatting rules cannot match return false; } switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return cell.getNumericCellValue() == formula1Val; case ComparisonOperator.NOT_EQUAL: return cell.getNumericCellValue() != formula1Val; case ComparisonOperator.LT: return cell.getNumericCellValue() < formula1Val; case ComparisonOperator.LE: return cell.getNumericCellValue() <= formula1Val; case ComparisonOperator.GT: return cell.getNumericCellValue() > formula1Val; case ComparisonOperator.GE: return cell.getNumericCellValue() >= formula1Val; case ComparisonOperator.BETWEEN: boolean lt = cell.getNumericCellValue() >= formula1Val; boolean gt = cell.getNumericCellValue() <= Double.valueOf(rule.getFormula2()); return lt && gt; case ComparisonOperator.NOT_BETWEEN: lt = cell.getNumericCellValue() <= formula1Val; gt = cell.getNumericCellValue() >= Double.valueOf(rule.getFormula2()); return lt && gt; } } return false; }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private static String describeRuleComparisonOperator(ConditionalFormattingRule rule) { StringBuilder sb = new StringBuilder(); sb.append(" comparison:"); switch (rule.getComparisonOperation()) { case ComparisonOperator.LT: sb.append(rule.getFormula1());/*from w ww . j a va2s. c o m*/ sb.append(" < "); sb.append(rule.getFormula2()); break; case ComparisonOperator.LE: sb.append(rule.getFormula1()); sb.append(" <= "); sb.append(rule.getFormula2()); break; case ComparisonOperator.GT: sb.append(rule.getFormula1()); sb.append(" > "); sb.append(rule.getFormula2()); break; case ComparisonOperator.GE: sb.append(rule.getFormula1()); sb.append(" >= "); sb.append(rule.getFormula2()); break; case ComparisonOperator.EQUAL: sb.append(rule.getFormula1()); sb.append(" = "); sb.append(rule.getFormula2()); break; case ComparisonOperator.NOT_EQUAL: sb.append(rule.getFormula1()); sb.append(" != "); sb.append(rule.getFormula2()); break; case ComparisonOperator.BETWEEN: sb.append(rule.getFormula1()); sb.append(" between "); sb.append(rule.getFormula2()); break; case ComparisonOperator.NOT_BETWEEN: sb.append(rule.getFormula1()); sb.append(" not between "); sb.append(rule.getFormula2()); break; case ComparisonOperator.NO_COMPARISON: sb.append(" none "); break; } return sb.toString(); }
From source file:snpviewer.SnpViewer.java
License:Open Source License
public void writeSavedRegionsToFile() { if (savedRegions.size() < 1) { Dialogs.showErrorDialog(null, "No Saved Regions exist to write!", "No Saved Regions", "SnpViewer"); return;//from w ww .j a v a 2 s .c o m } final int flanks = 10; FileChooser fileChooser = new FileChooser(); FileChooser.ExtensionFilter extFilter = new FileChooser.ExtensionFilter("Excel (*.xlsx)", "*.xlsx"); fileChooser.getExtensionFilters().add(extFilter); fileChooser.setTitle("Write regions to Excel file (.xlsx)..."); File rFile = fileChooser.showSaveDialog(mainWindow); if (rFile == null) { return; } else if (!rFile.getName().endsWith(".xlsx")) { rFile = new File(rFile.getAbsolutePath() + ".xlsx"); } final File regionFile = rFile; final Task<Boolean> writeTask = new Task() { @Override protected Boolean call() throws Exception { try { updateProgress(-1, -1); BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(regionFile)); Workbook wb = new XSSFWorkbook(); //first create a summary sheet of all regions Sheet sheet = wb.createSheet(); Row row = null; int rowNo = 0; int sheetNo = 0; wb.setSheetName(sheetNo++, "Summary"); row = sheet.createRow(rowNo++); String header[] = { "Coordinates", "rsIDs", "Size (Mb)" }; for (int col = 0; col < header.length; col++) { Cell cell = row.createCell(col); cell.setCellValue(header[col]); } for (int i = 0; i < savedRegions.size(); i++) { row = sheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue("chr" + savedRegions.get(i).getCoordinateString()); cell = row.createCell(col++); cell.setCellValue(savedRegions.get(i).getIdLine()); cell = row.createCell(col++); double mB = (double) savedRegions.get(i).getLength() / 1000000; cell.setCellValue(mB); } ArrayList<SnpFile> bothFiles = new ArrayList<>(); bothFiles.addAll(affFiles); bothFiles.addAll(unFiles); String prevChrom = new String(); double prog = 0; double total = savedRegions.size() * bothFiles.size() * 2; updateProgress(prog, total); int regCounter = 0; for (RegionSummary reg : savedRegions) { updateMessage("Writing region " + ++regCounter + " of " + savedRegions.size()); //create a sheet for each chromosome if (!reg.getChromosome().equalsIgnoreCase(prevChrom)) { if (!prevChrom.isEmpty()) { CellRangeAddress[] regions = { new CellRangeAddress(0, rowNo, 2, 2 + bothFiles.size()) }; SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AA\""); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"BB\""); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.PALE_BLUE.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule3 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AB\""); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.ROSE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); sheetCF.addConditionalFormatting(regions, rule3, rule2); sheetCF.addConditionalFormatting(regions, rule1); } rowNo = 0; sheet = wb.createSheet(); wb.setSheetName(sheetNo++, reg.getChromosome()); prevChrom = reg.getChromosome(); } else {//pad regions with an empty line rowNo++; } TreeMap<Integer, HashMap<String, String>> coordMap = new TreeMap(); /*coordmap - key is position, key of hashmap * is input filename and value call */ HashMap<Integer, String> coordToId = new HashMap<>(); //coordinate to rs ID try { for (SnpFile f : bothFiles) { updateProgress(prog++, total); if (isCancelled()) { return false; } List<SnpFile.SnpLine> lines = f.getSnpsInRegion(reg.getChromosome(), reg.getStartPos(), reg.getEndPos(), flanks); for (SnpFile.SnpLine snpLine : lines) { if (isCancelled()) { return false; } Integer coord = snpLine.getPosition(); if (!coordMap.containsKey(coord)) { coordMap.put(coord, new HashMap<String, String>()); } String filename = f.inputFile.getName(); String rsId = snpLine.getId(); String call = snpLine.getCall(); coordMap.get(coord).put(filename, call); coordToId.put(coord, rsId); } } row = sheet.createRow(rowNo++); Cell cell = row.createCell(0); cell.setCellValue(reg.getCoordinateString()); row = sheet.createRow(rowNo++); cell = row.createCell(0); cell.setCellValue(reg.getIdLine()); int col = 0; row = sheet.createRow(rowNo++); cell = row.createCell(col++); cell.setCellValue("Position"); cell = row.createCell(col++); cell.setCellValue("rsID"); for (SnpFile f : bothFiles) { updateProgress(prog++, total); cell = row.createCell(col++); if (f.getSampleName() != null && !f.getSampleName().isEmpty()) { cell.setCellValue(f.getSampleName()); } else { cell.setCellValue(f.inputFile.getName()); } } for (Entry current : coordMap.entrySet()) { if (isCancelled()) { return false; } col = 0; Integer coord = (Integer) current.getKey(); row = sheet.createRow(rowNo++); cell = row.createCell(col++); cell.setCellValue(coord); cell = row.createCell(col++); cell.setCellValue(coordToId.get(coord)); HashMap<String, String> fileToCall = (HashMap<String, String>) current.getValue(); for (SnpFile f : bothFiles) { cell = row.createCell(col++); if (fileToCall.containsKey(f.inputFile.getName())) { cell.setCellValue(fileToCall.get(f.inputFile.getName())); } else { cell.setCellValue("-"); } } } } catch (Exception ex) { return false; } } CellRangeAddress[] regions = { new CellRangeAddress(0, rowNo, 2, 2 + bothFiles.size()) }; SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AA\""); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"BB\""); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.PALE_BLUE.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule3 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AB\""); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.ROSE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); sheetCF.addConditionalFormatting(regions, rule3, rule2); sheetCF.addConditionalFormatting(regions, rule1); wb.write(out); updateProgress(total, total); out.close(); } catch (IOException | NumberFormatException ex) { ex.printStackTrace(); return false; } return true; } };//end of task setProgressMode(true); progressBar.progressProperty().bind(writeTask.progressProperty()); progressMessage.textProperty().bind(writeTask.messageProperty()); writeTask.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { if (e.getSource().getValue() == true) { Dialogs.showInformationDialog(null, "Saved regions written " + "to file " + "(" + regionFile.getName() + ")successfully", "Regions Written", "SNP Viewer"); } else { Dialogs.showErrorDialog(null, "Region write failed.", "Write Failed", "SNP Viewer"); } setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); progressMessage.textProperty().unbind(); progressMessage.setText(""); progressTitle.setText(""); } }); writeTask.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); progressMessage.textProperty().unbind(); progressMessage.setText(""); progressTitle.setText("Region write failed!"); Dialogs.showErrorDialog(null, "Error writing region to file\n", "Region write error", "SNP Viewer", e.getSource().getException()); } }); writeTask.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { progressMessage.setText("Region write cancelled"); progressTitle.setText("Cancelled"); setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); Dialogs.showErrorDialog(null, "Error writing region to file\n", "Region write error", "SNP Viewer"); } }); cancelButton.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent actionEvent) { writeTask.cancel(); } }); progressTitle.setText("Writing regions to .xlsx file"); new Thread(writeTask).start(); }
From source file:snpviewer.SnpViewer.java
License:Open Source License
public void writeRegionToFile(final String chromosome, final double start, final double end) { /* get coordinates of selection and report back * write SNPs in region to file//from w w w . j a v a2 s. co m */ FileChooser fileChooser = new FileChooser(); FileChooser.ExtensionFilter extFilter = new FileChooser.ExtensionFilter("Excel (*.xlsx)", "*.xlsx"); fileChooser.getExtensionFilters().add(extFilter); fileChooser.setTitle("Write region to Excel file (.xlsx)..."); File rFile = fileChooser.showSaveDialog(mainWindow); if (rFile == null) { return; } else if (!rFile.getName().endsWith(".xlsx")) { rFile = new File(rFile.getAbsolutePath() + ".xlsx"); } final File regionFile = rFile; final Task<Boolean> writeTask = new Task() { @Override protected Boolean call() throws Exception { try { updateProgress(-1, -1); ArrayList<SnpFile> bothFiles = new ArrayList<>(); bothFiles.addAll(affFiles); bothFiles.addAll(unFiles); TreeMap<Integer, HashMap<String, String>> coordMap = new TreeMap(); /*coordmap - key is position, key of hashmap * is input filename and value call */ HashMap<Integer, String> coordToId = new HashMap<>(); double progress = 0; double total = bothFiles.size() * 5; try { BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(regionFile)); Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(); int rowNo = 0; Row row = sheet.createRow(rowNo++); for (SnpFile f : bothFiles) { if (isCancelled()) { return false; } updateProgress(++progress, total); updateMessage("Reading region in " + f.inputFile.getName()); List<SnpFile.SnpLine> lines = f.getSnpsInRegion(chromosome, (int) start, (int) end); for (SnpFile.SnpLine snpLine : lines) { if (isCancelled()) { return false; } Integer coord = snpLine.getPosition(); if (!coordMap.containsKey(coord)) { coordMap.put(coord, new HashMap<String, String>()); } String filename = f.inputFile.getName(); String rsId = snpLine.getId(); String call = snpLine.getCall(); coordMap.get(coord).put(filename, call); coordToId.put(coord, rsId); } } Cell cell = row.createCell(0); cell.setCellValue( "chr" + chromosome + ":" + coordMap.firstKey() + "-" + coordMap.lastKey()); row = sheet.createRow(rowNo++); cell = row.createCell(0); cell.setCellValue( coordToId.get(coordMap.firstKey()) + ";" + coordToId.get(coordMap.lastKey())); row = sheet.createRow(rowNo++); int colNo = 0; cell = row.createCell(colNo++); cell.setCellValue("Position"); cell = row.createCell(colNo++); cell.setCellValue("rsID"); for (SnpFile f : bothFiles) { cell = row.createCell(colNo++); if (f.getSampleName() != null && f.getSampleName().length() > 0) { cell.setCellValue(f.getSampleName()); } else { cell.setCellValue(f.getInputFileName()); } } progress = coordMap.size(); total = 5 * coordMap.size(); updateMessage("Writing region to file..."); for (Entry current : coordMap.entrySet()) { if (isCancelled()) { return false; } progress += 4; updateProgress(progress, total); row = sheet.createRow(rowNo++); colNo = 0; Integer coord = (Integer) current.getKey(); cell = row.createCell(colNo++); cell.setCellValue(coord); String rsId = coordToId.get(coord); cell = row.createCell(colNo++); cell.setCellValue(rsId); HashMap<String, String> fileToCall = (HashMap<String, String>) current.getValue(); for (SnpFile f : bothFiles) { cell = row.createCell(colNo++); if (fileToCall.containsKey(f.inputFile.getName())) { cell.setCellValue(fileToCall.get(f.inputFile.getName())); } else { cell.setCellValue("-"); } } } CellRangeAddress[] regions = { new CellRangeAddress(0, rowNo, 2, 2 + bothFiles.size()) }; SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AA\""); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"BB\""); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.PALE_BLUE.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); ConditionalFormattingRule rule3 = sheetCF .createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"AB\""); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.ROSE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); sheetCF.addConditionalFormatting(regions, rule3, rule2); sheetCF.addConditionalFormatting(regions, rule1); wb.write(out); out.close(); return true; } catch (IOException ex) { return false; } } catch (Exception ex) { return false; } } };//end of task setProgressMode(true); progressBar.progressProperty().bind(writeTask.progressProperty()); progressMessage.textProperty().bind(writeTask.messageProperty()); writeTask.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { if (e.getSource().getValue() == true) { Dialogs.showInformationDialog(null, "Region written to file " + "(" + regionFile.getName() + ") successfully", "Region Written", "SNP Viewer"); } else { Dialogs.showErrorDialog(null, "Region write failed.", "Write Failed", "SNP Viewer"); } setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); progressMessage.textProperty().unbind(); progressMessage.setText(""); progressTitle.setText(""); } }); writeTask.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); progressMessage.textProperty().unbind(); progressMessage.setText(""); progressTitle.setText("Region write failed!"); Dialogs.showErrorDialog(null, "Error writing region to file\n", "Region write error", "SNP Viewer", e.getSource().getException()); } }); writeTask.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { progressMessage.setText("Region write cancelled"); progressTitle.setText("Cancelled"); setProgressMode(false); progressBar.progressProperty().unbind(); progressBar.progressProperty().set(0); Dialogs.showErrorDialog(null, "Error writing region to file\n", "Region write error", "SNP Viewer"); } }); cancelButton.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent actionEvent) { writeTask.cancel(); } }); progressTitle.setText("Writing region to .xlsx file"); new Thread(writeTask).start(); }