List of usage examples for org.apache.poi.ss.usermodel PatternFormatting setFillPattern
void setFillPattern(short fp);
From source file:domain.Excel.java
public void reporteSesionPeso() { // cargarLogo(); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:E1")); sheet.addMergedRegion(CellRangeAddress.valueOf("A2:E2")); sheet.addMergedRegion(CellRangeAddress.valueOf("A3:E4")); /*Name REPORT*/ HSSFFont FontNameReport = wb.createFont(); FontNameReport.setFontName("Calibri"); FontNameReport.setFontHeightInPoints((short) 11); FontNameReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FontNameReport.setColor(HSSFColor.DARK_RED.index); HSSFCellStyle styleNameReport = wb.createCellStyle(); styleNameReport.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleNameReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleNameReport.setFont(FontNameReport); Row row = sheet.createRow(0);/*from w ww . java 2 s . co m*/ Cell cell = row.createCell(0); cell.setCellValue("REPORTE DE SESIONES POR PESOS"); cell.setCellStyle(styleNameReport); /**/ /*DATE REPORT*/ HSSFFont FontDateReport = wb.createFont(); FontDateReport.setFontName("Calibri"); FontDateReport.setFontHeightInPoints((short) 10); FontDateReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FontDateReport.setColor(HSSFColor.BLACK.index); HSSFCellStyle styleDateReport = wb.createCellStyle(); styleDateReport.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleDateReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleDateReport.setFont(FontDateReport); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue("FECHA DE REPORTE: " + formatoDateTime.format(new Date())); cell.setCellStyle(styleDateReport); /*Etiqueta parametro*/ HSSFFont FontParametroReport = wb.createFont(); FontParametroReport.setFontName("Calibri"); FontParametroReport.setFontHeightInPoints((short) 9); FontParametroReport.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); FontParametroReport.setColor(HSSFColor.BLACK.index); HSSFCellStyle styleParamReport = wb.createCellStyle(); styleParamReport.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleParamReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleParamReport.setFont(FontParametroReport); row = sheet.createRow(2); cell = row.createCell(0); String etiqueta_parametro = ""; switch (tipo) { case 1: etiqueta_parametro = "Sesion de dia " + formatoDate.format(fecha_ini); break; case 2: etiqueta_parametro = "Sesiones del " + formatoDate.format(fecha_ini) + " al " + formatoDate.format(fecha_fin); break; case 3: etiqueta_parametro = "Sesiones del animal " + animal.arete_visual; break; } cell.setCellValue(etiqueta_parametro); cell.setCellStyle(styleParamReport); /**/ sheet.createRow(5).createCell(0).setCellValue("Arete Visual"); sheet.getRow(5).createCell(1).setCellValue("Arete Electronico"); sheet.getRow(5).createCell(2).setCellValue("Fecha"); sheet.getRow(5).createCell(3).setCellValue("Peso"); sheet.getRow(5).createCell(4).setCellValue("Corral"); sheet.setColumnWidth(0, 16 * Unidad); sheet.setColumnWidth(1, 16 * Unidad); sheet.setColumnWidth(2, 16 * Unidad); sheet.setColumnWidth(3, 16 * Unidad); sheet.setColumnWidth(4, 16 * Unidad); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); //Condition 1: Cell Value Is greater than 70 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.DARK_RED.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); //Condition 2: Cell Value Is less than 50 (Green Fill) ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.LT, "50"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.DARK_RED.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.WHITE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A6:E6") }; sheetCF.addConditionalFormatting(regions, rule1, rule2); Integer fila_inicial = 6; for (int i = 0; i < this.t_tabla.getRowCount(); i++) { sheet.createRow(fila_inicial + i).createCell(0).setCellValue(t_tabla.getValueAt(i, 1).toString()); sheet.getRow(fila_inicial + i).getCell(0).setCellStyle(styleCenter); for (int j = 1; j < 5; j++) { sheet.getRow(fila_inicial + i).createCell(j).setCellValue(t_tabla.getValueAt(i, j + 1).toString()); sheet.getRow(fila_inicial + i).getCell(j).setCellStyle(styleCenter); } sheet.getRow(fila_inicial + i).getCell(3) .setCellValue(Double.parseDouble(t_tabla.getValueAt(i, 4).toString())); sheet.getRow(fila_inicial + i).getCell(3).setCellStyle(styleRight); } cargarLogo(); }
From source file:domain.Excel.java
private void reporteSesionMedicina() { sheet.addMergedRegion(CellRangeAddress.valueOf("A1:I1")); sheet.addMergedRegion(CellRangeAddress.valueOf("A2:I2")); sheet.addMergedRegion(CellRangeAddress.valueOf("A3:I4")); /*Name REPORT*/ HSSFFont FontNameReport = wb.createFont(); FontNameReport.setFontName("Calibri"); FontNameReport.setFontHeightInPoints((short) 11); FontNameReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FontNameReport.setColor(HSSFColor.DARK_RED.index); HSSFCellStyle styleNameReport = wb.createCellStyle(); styleNameReport.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleNameReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleNameReport.setFont(FontNameReport); Row row = sheet.createRow(0);//from w w w . j av a 2s.c om Cell cell = row.createCell(0); cell.setCellValue("REPORTE DE SESIONES POR MEDICAMENTOS"); cell.setCellStyle(styleNameReport); /**/ /*DATE REPORT*/ HSSFFont FontDateReport = wb.createFont(); FontDateReport.setFontName("Calibri"); FontDateReport.setFontHeightInPoints((short) 10); FontDateReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FontDateReport.setColor(HSSFColor.BLACK.index); HSSFCellStyle styleDateReport = wb.createCellStyle(); styleDateReport.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleDateReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleDateReport.setFont(FontDateReport); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue("FECHA DE REPORTE: " + formatoDateTime.format(new Date())); cell.setCellStyle(styleDateReport); /*Etiqueta parametro*/ HSSFFont FontParametroReport = wb.createFont(); FontParametroReport.setFontName("Calibri"); FontParametroReport.setFontHeightInPoints((short) 9); FontParametroReport.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); FontParametroReport.setColor(HSSFColor.BLACK.index); HSSFCellStyle styleParamReport = wb.createCellStyle(); styleParamReport.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleParamReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleParamReport.setFont(FontParametroReport); row = sheet.createRow(2); cell = row.createCell(0); String etiqueta_parametro = ""; switch (tipo) { case 1: etiqueta_parametro = "Sesion de dia " + formatoDate.format(fecha_ini); break; case 2: etiqueta_parametro = "Sesiones del " + formatoDate.format(fecha_ini) + " al " + formatoDate.format(fecha_fin); break; case 3: etiqueta_parametro = "Sesiones del animal " + animal.arete_visual; break; } cell.setCellValue(etiqueta_parametro); cell.setCellStyle(styleParamReport); /**/ sheet.createRow(5).createCell(0).setCellValue("Arete Visual"); sheet.getRow(5).createCell(1).setCellValue("Arete Electronico"); sheet.getRow(5).createCell(2).setCellValue("Codigo"); sheet.getRow(5).createCell(3).setCellValue("Medicamento"); sheet.getRow(5).createCell(4).setCellValue("Fecha"); sheet.getRow(5).createCell(5).setCellValue("Corral"); sheet.getRow(5).createCell(6).setCellValue("Dosis"); sheet.getRow(5).createCell(7).setCellValue("Costo"); sheet.getRow(5).createCell(8).setCellValue("Importe"); sheet.setColumnWidth(0, 15 * Unidad); sheet.setColumnWidth(1, 20 * Unidad); sheet.setColumnWidth(2, 15 * Unidad); sheet.setColumnWidth(3, 20 * Unidad); sheet.setColumnWidth(4, 20 * Unidad); sheet.setColumnWidth(5, 20 * Unidad); sheet.setColumnWidth(6, 15 * Unidad); sheet.setColumnWidth(7, 15 * Unidad); sheet.setColumnWidth(8, 15 * Unidad); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); //Condition 1: Cell Value Is greater than 70 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.DARK_RED.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); //Condition 2: Cell Value Is less than 50 (Green Fill) ConditionalFormattingRule rule2 = sheetCF .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.LT, "50"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.DARK_RED.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.WHITE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A6:I6") }; sheetCF.addConditionalFormatting(regions, rule1, rule2); Integer fila_inicial = 6; for (int i = 0; i < this.t_tabla.getRowCount(); i++) { agregarValor(fila_inicial + i, 0, t_tabla.getValueAt(i, 1).toString(), styleCenter); for (int j = 0; j < 9; j++) { agregarValor(fila_inicial + i, j, t_tabla.getValueAt(i, j + 1).toString(), styleCenter); } } cargarLogo(); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Highlight cells based on their values *///from ww w. j ava 2 s .c om 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:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Highlight multiple cells based on a formula *//*from w ww . jav a 2s . 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:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight items that are in a list on * the worksheet./*www .j a va2s . com*/ */ static void inList(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Codes"); sheet.createRow(1).createCell(0).setCellValue("AA"); sheet.createRow(2).createCell(0).setCellValue("BB"); sheet.createRow(3).createCell(0).setCellValue("GG"); sheet.createRow(4).createCell(0).setCellValue("AA"); sheet.createRow(5).createCell(0).setCellValue("FF"); sheet.createRow(6).createCell(0).setCellValue("XX"); sheet.createRow(7).createCell(0).setCellValue("CC"); sheet.getRow(0).createCell(2).setCellValue("Valid"); sheet.getRow(1).createCell(2).setCellValue("AA"); sheet.getRow(2).createCell(2).setCellValue("BB"); sheet.getRow(3).createCell(2).setCellValue("CC"); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(3).setCellValue( "<== Use Excel conditional formatting to highlight items that are in a list on the worksheet"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to shade alternating rows on the * worksheet//from ww w . j a v a 2 s. c om */ 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// w w w . ja v a 2 s . c o m */ 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: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;/*w w w . java 2s . 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 www.ja v a2 s. c o 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(); }