List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetConditionalFormatting
SheetConditionalFormatting getSheetConditionalFormatting();
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to shade alternating rows on the * worksheet//w w w. j a v a 2s .c o m */ static void shadeAlt(Sheet sheet) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:Z100") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows"); sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * Multiple conditional formatting rules can apply to * one cell, some combining, some beating others. * Done in order of the rules added to the * SheetConditionalFormatting object//from w w w. j av a 2s .c om */ static void overlapping(Sheet sheet) { for (int i = 0; i < 40; i++) { int rn = i + 1; Row r = sheet.createRow(i); r.createCell(0).setCellValue("This is row " + rn + " (" + i + ")"); String str = ""; if (rn % 2 == 0) str = str + "even "; if (rn % 3 == 0) str = str + "x3 "; if (rn % 5 == 0) str = str + "x5 "; if (rn % 10 == 0) str = str + "x10 "; if (str.length() == 0) str = "nothing special..."; r.createCell(1).setCellValue("It is " + str); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.getRow(1).createCell(3).setCellValue("Even rows are blue"); sheet.getRow(2).createCell(3).setCellValue("Multiples of 3 have a grey background"); sheet.getRow(4).createCell(3).setCellValue("Multiples of 5 are bold"); sheet.getRow(9).createCell(3).setCellValue("Multiples of 10 are red (beats even)"); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Row divides by 10, red (will beat #1) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),10)=0"); FontFormatting font1 = rule1.createFontFormatting(); font1.setFontColorIndex(IndexedColors.RED.index); // Condition 2: Row is even, blue ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=0"); FontFormatting font2 = rule2.createFontFormatting(); font2.setFontColorIndex(IndexedColors.BLUE.index); // Condition 3: Row divides by 5, bold ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("MOD(ROW(),5)=0"); FontFormatting font3 = rule3.createFontFormatting(); font3.setFontStyle(false, true); // Condition 4: Row divides by 3, grey background ConditionalFormattingRule rule4 = sheetCF.createConditionalFormattingRule("MOD(ROW(),3)=0"); PatternFormatting fill4 = rule4.createPatternFormatting(); fill4.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index); fill4.setFillPattern(PatternFormatting.SOLID_FOREGROUND); // Apply CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:F41") }; sheetCF.addConditionalFormatting(regions, rule1); sheetCF.addConditionalFormatting(regions, rule2); sheetCF.addConditionalFormatting(regions, rule3); sheetCF.addConditionalFormatting(regions, rule4); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * Icon Sets / Multi-States allow you to have icons shown which vary * based on the values, eg Red traffic light / Yellow traffic light / * Green traffic light//from w w w.j a v a2 s . com */ static void iconSets(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Icon Sets"); Row r = sheet.createRow(1); r.createCell(0).setCellValue("Reds"); r.createCell(1).setCellValue(0); r.createCell(2).setCellValue(0); r.createCell(3).setCellValue(0); r = sheet.createRow(2); r.createCell(0).setCellValue("Yellows"); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(5); r.createCell(3).setCellValue(5); r = sheet.createRow(3); r.createCell(0).setCellValue("Greens"); r.createCell(1).setCellValue(10); r.createCell(2).setCellValue(10); r.createCell(3).setCellValue(10); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B1:B4") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_TRAFFIC_LIGHTS); IconMultiStateFormatting im1 = rule1.getMultiStateFormatting(); im1.getThresholds()[0].setRangeType(RangeType.MIN); im1.getThresholds()[1].setRangeType(RangeType.PERCENT); im1.getThresholds()[1].setValue(33d); im1.getThresholds()[2].setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule1); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C1:C4") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_FLAGS); IconMultiStateFormatting im2 = rule1.getMultiStateFormatting(); im2.getThresholds()[0].setRangeType(RangeType.PERCENT); im2.getThresholds()[0].setValue(0d); im2.getThresholds()[1].setRangeType(RangeType.PERCENT); im2.getThresholds()[1].setValue(33d); im2.getThresholds()[2].setRangeType(RangeType.PERCENT); im2.getThresholds()[2].setValue(67d); sheetCF.addConditionalFormatting(regions, rule2); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D1:D4") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_SYMBOLS_CIRCLE); IconMultiStateFormatting im3 = rule1.getMultiStateFormatting(); im3.setIconOnly(true); im3.getThresholds()[0].setRangeType(RangeType.MIN); im3.getThresholds()[1].setRangeType(RangeType.NUMBER); im3.getThresholds()[1].setValue(3d); im3.getThresholds()[2].setRangeType(RangeType.NUMBER); im3.getThresholds()[2].setValue(7d); sheetCF.addConditionalFormatting(regions, rule3); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * Color Scales / Colour Scales / Colour Gradients allow you shade the * background colour of the cell based on the values, eg from Red to * Yellow to Green.//from w w w . j a v a 2 s .com */ static void colourScales(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Colour Scales"); Row r = sheet.createRow(1); r.createCell(0).setCellValue("Red-Yellow-Green"); for (int i = 1; i <= 7; i++) { r.createCell(i).setCellValue((i - 1) * 5); } r = sheet.createRow(2); r.createCell(0).setCellValue("Red-White-Blue"); for (int i = 1; i <= 9; i++) { r.createCell(i).setCellValue((i - 1) * 5); } r = sheet.createRow(3); r.createCell(0).setCellValue("Blue-Green"); for (int i = 1; i <= 16; i++) { r.createCell(i).setCellValue((i - 1)); } sheet.setColumnWidth(0, 5000); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:H2") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingColorScaleRule(); ColorScaleFormatting cs1 = rule1.getColorScaleFormatting(); cs1.getThresholds()[0].setRangeType(RangeType.MIN); cs1.getThresholds()[1].setRangeType(RangeType.PERCENTILE); cs1.getThresholds()[1].setValue(50d); cs1.getThresholds()[2].setRangeType(RangeType.MAX); ((ExtendedColor) cs1.getColors()[0]).setARGBHex("FFF8696B"); ((ExtendedColor) cs1.getColors()[1]).setARGBHex("FFFFEB84"); ((ExtendedColor) cs1.getColors()[2]).setARGBHex("FF63BE7B"); sheetCF.addConditionalFormatting(regions, rule1); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B3:J3") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingColorScaleRule(); ColorScaleFormatting cs2 = rule2.getColorScaleFormatting(); cs2.getThresholds()[0].setRangeType(RangeType.MIN); cs2.getThresholds()[1].setRangeType(RangeType.PERCENTILE); cs2.getThresholds()[1].setValue(50d); cs2.getThresholds()[2].setRangeType(RangeType.MAX); ((ExtendedColor) cs2.getColors()[0]).setARGBHex("FFF8696B"); ((ExtendedColor) cs2.getColors()[1]).setARGBHex("FFFCFCFF"); ((ExtendedColor) cs2.getColors()[2]).setARGBHex("FF5A8AC6"); sheetCF.addConditionalFormatting(regions, rule2); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B4:Q4") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingColorScaleRule(); ColorScaleFormatting cs3 = rule3.getColorScaleFormatting(); cs3.setNumControlPoints(2); cs3.getThresholds()[0].setRangeType(RangeType.MIN); cs3.getThresholds()[1].setRangeType(RangeType.MAX); ((ExtendedColor) cs3.getColors()[0]).setARGBHex("FF5A8AC6"); ((ExtendedColor) cs3.getColors()[1]).setARGBHex("FF63BE7B"); sheetCF.addConditionalFormatting(regions, rule3); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * DataBars / Data-Bars allow you to have bars shown vary * based on the values, from full to empty *//* ww w .j a va 2s. co m*/ static void dataBars(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Data Bars"); Row r = sheet.createRow(1); r.createCell(1).setCellValue("Green Positive"); r.createCell(2).setCellValue("Blue Mix"); r.createCell(3).setCellValue("Red Negative"); r = sheet.createRow(2); r.createCell(1).setCellValue(0); r.createCell(2).setCellValue(0); r.createCell(3).setCellValue(0); r = sheet.createRow(3); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(-5); r.createCell(3).setCellValue(-5); r = sheet.createRow(4); r.createCell(1).setCellValue(10); r.createCell(2).setCellValue(10); r.createCell(3).setCellValue(-10); r = sheet.createRow(5); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(5); r.createCell(3).setCellValue(-5); r = sheet.createRow(6); r.createCell(1).setCellValue(20); r.createCell(2).setCellValue(-10); r.createCell(3).setCellValue(-20); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ExtendedColor color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FF63BE7B"); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:B7") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db1 = rule1.getDataBarFormatting(); db1.getMinThreshold().setRangeType(RangeType.MIN); db1.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule1); color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FF5A8AC6"); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C2:C7") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db2 = rule2.getDataBarFormatting(); db2.getMinThreshold().setRangeType(RangeType.MIN); db2.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule2); color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FFF8696B"); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D2:D7") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db3 = rule3.getDataBarFormatting(); db3.getMinThreshold().setRangeType(RangeType.MIN); db3.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule3); }
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 ww w. jav 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 av 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(); }