List of usage examples for org.apache.poi.ss.usermodel FontFormatting setFontStyle
void setFontStyle(boolean italic, boolean bold);
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight duplicate entries in a column. *//*ww w. java 2 s . c om*/ static void formatDuplicates(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Code"); sheet.createRow(1).createCell(0).setCellValue(4); sheet.createRow(2).createCell(0).setCellValue(3); sheet.createRow(3).createCell(0).setCellValue(6); sheet.createRow(4).createCell(0).setCellValue(3); sheet.createRow(5).createCell(0).setCellValue(5); sheet.createRow(6).createCell(0).setCellValue(8); sheet.createRow(7).createCell(0).setCellValue(0); sheet.createRow(8).createCell(0).setCellValue(2); sheet.createRow(9).createCell(0).setCellValue(8); sheet.createRow(10).createCell(0).setCellValue(6); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted. " + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)"); }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight payments that are due in the next thirty days. * In this example, Due dates are entered in cells A2:A4. *///from w ww . j a v a 2 s . co m static void expiry(Sheet sheet) { CellStyle style = sheet.getWorkbook().createCellStyle(); style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm")); sheet.createRow(0).createCell(0).setCellValue("Date"); sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29"); sheet.createRow(2).createCell(0).setCellFormula("A2+1"); sheet.createRow(3).createCell(0).setCellFormula("A3+1"); for (int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted"); }
From source file:domain.Excel.java
public void relleno(String rango, short color_relleno, short color_letra) { SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(color_relleno); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(color_letra); CellRangeAddress[] regions = { CellRangeAddress.valueOf(rango) }; sheetCF.addConditionalFormatting(regions, rule1, rule1); }/*from w w w.j ava2 s . c o m*/
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 ww w . ja va 2 s . c om 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 ww .ja v a2s .co m*/ 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:net.ceos.project.poi.annotated.core.ConditionalFormattingHandler.java
License:Apache License
/** * Apply the conditional formatting according the values defined at the * respective annotation. Is only available at the declared object, in * another words, at the linked {@link Sheet} with the object. * /* w w w . j a v a2 s.com*/ * @param configCriteria * the {@link XConfigCriteria} * @param conditionalFomat * the {@link XlsConditionalFormat} * @throws ConfigurationException */ protected static void applyCondition(XConfigCriteria configCriteria, XlsConditionalFormat conditionalFomat) throws ConfigurationException { // Define a Conditional Formatting rule, which triggers formatting // according the developer definition and applies patternFormatting SheetConditionalFormatting sheet = configCriteria.getSheet().getSheetConditionalFormatting(); /* apply all rules defined */ int i = 0; ConditionalFormattingRule[] rules = new ConditionalFormattingRule[conditionalFomat.rules().length]; XlsConditionalFormatRules[] rulesAnnotated = conditionalFomat.rules(); for (XlsConditionalFormatRules rule : rulesAnnotated) { ConditionalFormattingRule setRule = sheet.createConditionalFormattingRule(rule.operator(), rule.formula1(), StringUtils.isNotBlank(rule.formula2()) ? rule.formula2() : null); CellStyle decorator = null; try { decorator = configCriteria.getCellStyle(conditionalFomat.decorator()); } catch (ElementException e) { throw new ConfigurationException(ExceptionMessage.CONFIGURATION_DECORATOR_MISSING.getMessage(), e); } /* add FontFormatting */ FontFormatting fontFormat = setRule.createFontFormatting(); Font f = configCriteria.getWorkbook().getFontAt(decorator.getFontIndex()); fontFormat.setFontStyle(f.getItalic(), f.getBold()); fontFormat.setFontColorIndex(f.getColor()); fontFormat.setUnderlineType(f.getUnderline()); /* add BorderFormatting */ BorderFormatting borderFormat = setRule.createBorderFormatting(); borderFormat.setBorderBottom(decorator.getBorderBottom()); borderFormat.setBorderTop(decorator.getBorderTop()); borderFormat.setBorderLeft(decorator.getBorderLeft()); borderFormat.setBorderRight(decorator.getBorderRight()); borderFormat.setBottomBorderColor(decorator.getBottomBorderColor()); borderFormat.setTopBorderColor(decorator.getTopBorderColor()); borderFormat.setLeftBorderColor(decorator.getLeftBorderColor()); borderFormat.setRightBorderColor(decorator.getRightBorderColor()); /* add PatternFormatting */ PatternFormatting patternFormat = setRule.createPatternFormatting(); patternFormat.setFillBackgroundColor(decorator.getFillForegroundColor()); /* join rule */ rules[i++] = setRule; } /* Define a region */ CellRangeAddress[] regions = { CellRangeAddress.valueOf(CellFormulaConverter .calculateRangeAddressFromTemplate(configCriteria, conditionalFomat.rangeAddress())) }; /* Apply Conditional Formatting rule defined above to the regions */ sheet.addConditionalFormatting(regions, rules); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight duplicate entries in a * column./*from w w w. j ava2s .c o m*/ */ static void formatDuplicates(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Code"); sheet.createRow(1).createCell(0).setCellValue(4); sheet.createRow(2).createCell(0).setCellValue(3); sheet.createRow(3).createCell(0).setCellValue(6); sheet.createRow(4).createCell(0).setCellValue(3); sheet.createRow(5).createCell(0).setCellValue(5); sheet.createRow(6).createCell(0).setCellValue(8); sheet.createRow(7).createCell(0).setCellValue(0); sheet.createRow(8).createCell(0).setCellValue(2); sheet.createRow(9).createCell(0).setCellValue(8); sheet.createRow(10).createCell(0).setCellValue(6); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted. " + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)"); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to highlight payments that are due in * the next thirty days. In this example, Due dates are entered in cells * A2:A4.// w ww .j av a 2 s . c o m */ static void expiry(Sheet sheet) { CellStyle style = sheet.getWorkbook().createCellStyle(); style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm")); sheet.createRow(0).createCell(0).setCellValue("Date"); sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29"); sheet.createRow(2).createCell(0).setCellFormula("A2+1"); sheet.createRow(3).createCell(0).setCellFormula("A3+1"); for (int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =A2=A1 (White Font) ConditionalFormattingRule rule1 = sheetCF .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)"); FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted"); }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;/* ww w . j a va 2 s . co m*/ //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
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 ww w.j a 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); }