List of usage examples for org.apache.poi.ss.usermodel CellStyle setFillPattern
void setFillPattern(FillPatternType fp);
From source file:org.fenixedu.academic.ui.struts.action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java
License:Open Source License
private CellStyle headerBackgroundStyle(final HSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(FillPatternType.BIG_SPOTS); return style; }
From source file:org.forzaframework.util.ExcelUtils.java
License:Apache License
public static CellStyle getDefaultHeaderCellStyle(Workbook wb, Boolean defaultFormat) { CellStyle headerCellStyle = null; if (defaultFormat != null && defaultFormat) { //Le damos formato a los encabezados headerCellStyle = wb.createCellStyle(); headerCellStyle.setBorderBottom(BorderStyle.DOTTED); headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //Creamos el tipo de fuente Font headerFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); headerFont.setBold(Boolean.TRUE); headerFont.setColor(Font.COLOR_NORMAL); headerFont.setFontHeightInPoints((short) 8); headerCellStyle.setFont(headerFont); }// w ww . j a v a2 s . c om return headerCellStyle; }
From source file:org.generationcp.breeding.manager.crossingmanager.util.CrossingManagerExporter.java
License:Open Source License
private HashMap<String, CellStyle> createStyles(HSSFWorkbook wb) { HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>(); // set cell style for labels in the description sheet CellStyle labelStyle = wb.createCellStyle(); labelStyle.setFillForegroundColor(IndexedColors.BROWN.getIndex()); labelStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); Font labelFont = wb.createFont(); labelFont.setColor(IndexedColors.WHITE.getIndex()); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); labelStyle.setFont(labelFont);//ww w .ja va2s . com styles.put(LABEL_STYLE, labelStyle); // set cell style for headings related to Conditions/Factors CellStyle factorHeadingStyle = wb.createCellStyle(); factorHeadingStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex()); factorHeadingStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); factorHeadingStyle.setAlignment(CellStyle.ALIGN_CENTER); Font factorHeadingfont = wb.createFont(); factorHeadingfont.setColor(IndexedColors.WHITE.getIndex()); factorHeadingfont.setBoldweight(Font.BOLDWEIGHT_BOLD); factorHeadingStyle.setFont(factorHeadingfont); styles.put(FACTOR_HEADING_STYLE, factorHeadingStyle); // set cell style for headings related to Constants/Variates CellStyle variateHeadingStyle = wb.createCellStyle(); variateHeadingStyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); variateHeadingStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); variateHeadingStyle.setAlignment(CellStyle.ALIGN_CENTER); Font variateHeadingFont = wb.createFont(); variateHeadingFont.setColor(IndexedColors.WHITE.getIndex()); variateHeadingFont.setBoldweight(Font.BOLDWEIGHT_BOLD); variateHeadingStyle.setFont(variateHeadingFont); styles.put(VARIATE_HEADING_STYLE, variateHeadingStyle); //set cell style for numeric values (left alignment) CellStyle numericStyle = wb.createCellStyle(); numericStyle.setAlignment(CellStyle.ALIGN_LEFT); styles.put(NUMERIC_STYLE, numericStyle); return styles; }
From source file:org.grible.excel.ExcelFile.java
License:Open Source License
public String saveToFile(Table table, String filePath) { try {/*from w w w . ja v a 2 s . c o m*/ FileOutputStream fileOut = new FileOutputStream(filePath); Sheet worksheet = workbook.createSheet(table.getName()); Row row1 = worksheet.createRow(0); Font keyFont = workbook.createFont(); keyFont.setColor(HSSFColor.WHITE.index); keyFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle keyCellStyle = workbook.createCellStyle(); keyCellStyle.setFont(keyFont); keyCellStyle.setFillForegroundColor(HSSFColor.BLACK.index); keyCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); keyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Key[] keys = null; String[][] values = null; if (ServletHelper.isJson()) { keys = table.getTableJson().getKeys(); values = table.getTableJson().getValues(); } else { keys = table.getKeys(); values = table.getValues(); } for (int i = 0; i < keys.length; i++) { Cell cell = row1.createCell(i); cell.setCellValue(keys[i].getName()); cell.setCellStyle(keyCellStyle); } for (int i = 0; i < values.length; i++) { Row excelRow = worksheet.createRow(i + 1); for (int j = 0; j < values[i].length; j++) { Cell cell = excelRow.createCell(j); cell.setCellValue(values[i][j]); } } workbook.write(fileOut); fileOut.flush(); fileOut.close(); return "success"; } catch (Exception e) { return e.getLocalizedMessage(); } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("rawtypes") private static void writeHead(boolean useTemplate, Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) { Integer headRowIndex = sheetProcessor.getHeadRowIndex(); if (headRowIndex == null) { return;/*from www .ja va 2s .c o m*/ } Workbook wookbook = sheet.getWorkbook(); // use theme CellStyle style = null; if (!useTemplate && sheetProcessor.getTheme() != null) { int theme = sheetProcessor.getTheme(); if (theme == ExcelWriteTheme.BASE) { style = wookbook.createCellStyle(); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillForegroundColor((short) 44); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); // style.setBottomBorderColor((short) 44); style.setAlignment(CellStyle.ALIGN_CENTER); } // freeze Pane if (sheetProcessor.getHeadRowIndex() != null && sheetProcessor.getHeadRowIndex() == 0) { sheet.createFreezePane(0, 1, 0, 1); } } Row row = sheet.getRow(headRowIndex); if (row == null) { row = sheet.createRow(headRowIndex); } for (Map.Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> entry : sheetProcessor .getFieldMapping().export().entrySet()) { Map<Integer, ExcelWriteFieldMappingAttribute> map = entry.getValue(); if (map != null) { for (Map.Entry<Integer, ExcelWriteFieldMappingAttribute> entry2 : map.entrySet()) { String head = entry2.getValue().getHead(); Integer colIndex = entry2.getKey(); Cell cell = row.getCell(colIndex); if (cell == null) { cell = row.createCell(colIndex); } // use theme if (!useTemplate && sheetProcessor.getTheme() != null) { cell.setCellStyle(style); } cell.setCellValue(head); } } } }
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;//w w w . j av a 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:org.jaffa.qm.finder.apis.ExcelExportService.java
License:Open Source License
public static Workbook generateExcel(QueryServiceConfig master, QueryServiceConfig child, String sheetName) throws ClassNotFoundException, NoSuchMethodException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { Workbook wb = null;/*from w w w . ja v a 2s . c o m*/ String legacyExport = (String) ContextManagerFactory.instance() .getProperty("jaffa.widgets.exportToExcel.legacy"); if (legacyExport != null && legacyExport.equals("T")) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(100); } try { // Creating worksheet Sheet sheet = null; if (sheetName != null) sheet = wb.createSheet(sheetName); else sheet = wb.createSheet(); // creating a custom palette for the workbook CellStyle style = wb.createCellStyle(); style = wb.createCellStyle(); // setting the foreground color to gray style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); // Setting the border for the cells style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); // setting font weight Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); int rowNum = 0; Row headerRow = sheet.createRow(rowNum); int colIndex = 0; for (Object o : master.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } // Generate the Excel output by creating a simple HTML table if (child != null) { for (Object o : child.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } } // Invoke the query and obtain an array of Graph objects Object[] queryOutput = invokeQueryService(master.getCriteriaClassName(), master.getCriteriaObject(), master.getServiceClassName(), master.getServiceClassMethodName()); // Add the data rows if (queryOutput != null) { for (Object row : queryOutput) { Object[] detailQueryOutput = new Object[0]; if (child == null) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object o : master.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; if (mapping.startsWith("appFields.")) { mapping = mapping.substring(10); try { Object[] appFields = (Object[]) PropertyUtils.getProperty(row, "applicationFields"); for (Object field : appFields) { String name = (String) PropertyUtils.getProperty(field, "name"); if (name.equals(mapping)) { value = (String) PropertyUtils.getProperty(field, "value"); } } } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + mapping, e); } } else { try { value = PropertyUtils.getProperty(row, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + mapping, e); } } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } else { //child is not null // load the child rows String detailCriteriaObject = child.getCriteriaObject(); for (int i = 0; i < child.getMasterKeyFieldNames().length; i++) { String kfn = child.getMasterKeyFieldNames()[i]; try { String keyValue = (String) PropertyUtils.getProperty(row, kfn); detailCriteriaObject = detailCriteriaObject.replace("{" + i + "}", keyValue); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Key property not found: " + kfn, e); } } detailQueryOutput = invokeQueryService(child.getCriteriaClassName(), detailCriteriaObject, child.getServiceClassName(), "query"); // add the child columns if (detailQueryOutput != null && detailQueryOutput.length > 0) { for (Object detailRow : detailQueryOutput) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object obj : master.getColumnModel()) { String masterMapping = (String) ((DynaBean) obj).get("mapping"); Object masterValue = null; try { masterValue = PropertyUtils.getProperty(row, masterMapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + masterMapping, e); } dataRow.createCell(colNum).setCellValue(format(masterValue, (DynaBean) obj)); colNum += 1; } for (Object o : child.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; try { value = PropertyUtils.getProperty(detailRow, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found in child result: " + mapping, e); } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } } } } } } catch (Exception e) { e.printStackTrace(); } return wb; }
From source file:org.jaffa.ria.finder.apis.ExcelExportService.java
License:Open Source License
public static Workbook generateExcel(QueryServiceConfig master, QueryServiceConfig child, String sheetName) throws ClassNotFoundException, NoSuchMethodException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { Workbook wb = null;/*from ww w . j a v a2 s .c o m*/ String legacyExport = (String) ContextManagerFactory.instance() .getProperty("jaffa.widgets.exportToExcel.legacy"); if (legacyExport != null && legacyExport.equals("T")) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(100); } try { // Creating worksheet Sheet sheet = null; if (sheetName != null) { if (sheetName.length() > 31) sheetName = sheetName.substring(0, 31); char replaceChar = '_'; sheetName = sheetName.replace('\u0003', replaceChar).replace(':', replaceChar) .replace('/', replaceChar).replace("\\\\", Character.toString(replaceChar)) .replace('?', replaceChar).replace('*', replaceChar).replace(']', replaceChar) .replace('[', replaceChar); sheet = wb.createSheet(sheetName); } else sheet = wb.createSheet(); // creating a custom palette for the workbook CellStyle style = wb.createCellStyle(); style = wb.createCellStyle(); // setting the foreground color to gray style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); // Setting the border for the cells style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); // setting font weight Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); int rowNum = 0; Row headerRow = sheet.createRow(rowNum); int colIndex = 0; for (Object o : master.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } // Generate the Excel output by creating a simple HTML table if (child != null) { for (Object o : child.getColumnModel()) { String columnTitle = (String) ((DynaBean) o).get("header"); if (columnTitle == null || columnTitle.length() == 0) columnTitle = (String) ((DynaBean) o).get("mapping"); headerRow.createCell(colIndex).setCellValue(columnTitle); Cell cell = headerRow.getCell(colIndex); cell.setCellStyle(style); sheet.autoSizeColumn(colIndex); colIndex += 1; } } // Invoke the query and obtain an array of Graph objects Object[] queryOutput = invokeQueryService(master.getCriteriaClassName(), master.getCriteriaObject(), master.getServiceClassName(), master.getServiceClassMethodName()); // Add the data rows if (queryOutput != null) { for (Object row : queryOutput) { Object[] detailQueryOutput = new Object[0]; if (child == null) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object o : master.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; try { value = PropertyUtils.getProperty(row, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + mapping, e); } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } else { //child is not null // load the child rows String detailCriteriaObject = child.getCriteriaObject(); for (int i = 0; i < child.getMasterKeyFieldNames().length; i++) { String kfn = child.getMasterKeyFieldNames()[i]; try { String keyValue = (String) PropertyUtils.getProperty(row, kfn); detailCriteriaObject = detailCriteriaObject.replace("{" + i + "}", keyValue); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Key property not found: " + kfn, e); } } detailQueryOutput = invokeQueryService(child.getCriteriaClassName(), detailCriteriaObject, child.getServiceClassName(), "query"); // add the child columns if (detailQueryOutput != null && detailQueryOutput.length > 0) { for (Object detailRow : detailQueryOutput) { rowNum += 1; Row dataRow = sheet.createRow((short) rowNum); int colNum = 0; // extract the columns from master object for (Object obj : master.getColumnModel()) { String masterMapping = (String) ((DynaBean) obj).get("mapping"); Object masterValue = null; try { masterValue = PropertyUtils.getProperty(row, masterMapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found: " + masterMapping, e); } dataRow.createCell(colNum).setCellValue(format(masterValue, (DynaBean) obj)); colNum += 1; } for (Object o : child.getColumnModel()) { String mapping = (String) ((DynaBean) o).get("mapping"); Object value = null; try { value = PropertyUtils.getProperty(detailRow, mapping); } catch (Exception e) { if (log.isDebugEnabled()) log.debug("Property not found in child result: " + mapping, e); } dataRow.createCell(colNum).setCellValue(format(value, (DynaBean) o)); colNum += 1; } } } } } } } catch (Exception e) { e.printStackTrace(); } return wb; }
From source file:org.jboss.dashboard.displayer.table.ExportTool.java
License:Apache License
private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 12); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();/*from w w w . jav a2s.c o m*/ style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(titleFont); style.setWrapText(false); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex()); styles.put("header", style); Font cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 10); cellFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(3))); styles.put("integer_number_cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4))); styles.put("decimal_number_cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); styles.put("text_cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); style.setFont(cellFont); style.setWrapText(false); style.setDataFormat(wb.createDataFormat() .getFormat(DateFormatConverter.convert(LocaleManager.currentLocale(), dateFormatPattern))); styles.put("date_cell", style); return styles; }
From source file:org.jcvi.ometa.utils.JsonProducer.java
License:Open Source License
public void jsonHelper(String projectNames, String attributes, String screenAttributes, String sorting, String fileName, String filePath, String domain) { String PROJECT_STATUS = "Project Status"; try {//from w w w.j a v a 2s . c om JSONObject json = new JSONObject(); File directory = new File(filePath); if (!directory.exists() || !directory.isDirectory()) { if ((new File(directory.getParent())).canWrite()) directory.mkdir(); else throw new Exception(); } //Json file Creation File tempFile = new File(filePath + File.separator + fileName + "_temp.json"); FileWriter fileWriter = new FileWriter(tempFile); BufferedWriter bufferedWriter = new BufferedWriter(fileWriter); //Normal status data retrieval LookupValue tempLookupValue; List<String> projectNameList = new ArrayList<String>(); if (projectNames.contains(",")) projectNameList.addAll(Arrays.asList(projectNames.split(","))); else projectNameList.add(projectNames); List<String> availableAttributes = new ArrayList<String>(); availableAttributes.add("Sample Name"); List<Project> projects = pseEjb.getProjects(projectNameList); List<Long> projectIds = new ArrayList<Long>(); Map<String, Long> projectNameVsId = new HashMap<String, Long>(); for (Project project : projects) { projectIds.add(project.getProjectId()); projectNameVsId.put(project.getProjectName(), project.getProjectId()); } List<ProjectMetaAttribute> allProjectMetaAttributes = pseEjb.getProjectMetaAttributes(projectIds); for (ProjectMetaAttribute pma : allProjectMetaAttributes) { if (!availableAttributes.contains(pma.getLookupValue().getName())) availableAttributes.add(pma.getLookupValue().getName()); } List<SampleMetaAttribute> allSampleMetaAttributes = pseEjb.getSampleMetaAttributes(projectIds); for (SampleMetaAttribute sma : allSampleMetaAttributes) { if (!availableAttributes.contains(sma.getLookupValue().getName())) availableAttributes.add(sma.getLookupValue().getName()); } List<EventMetaAttribute> allEventMetaAttributes = pseEjb.getEventMetaAttributes(projectIds); for (EventMetaAttribute ema : allEventMetaAttributes) { if (!availableAttributes.contains(ema.getLookupValue().getName())) availableAttributes.add(ema.getLookupValue().getName()); } List<String> parameterizedAttributes = null; if (attributes == null || attributes.equals("") || "ALL".equals(attributes)) { parameterizedAttributes = availableAttributes; } else { parameterizedAttributes = new ArrayList<String>(); ArrayList<String> tokenizedAttribute = new ArrayList<String>(Arrays.asList(attributes.split(","))); for (String tempAttribute : tokenizedAttribute) { if (availableAttributes.contains(tempAttribute)) parameterizedAttributes.add(tempAttribute); } } parameterizedAttributes.removeAll(Arrays.asList(forbiddenAttributes)); /*------------ XLS Part ------------*/ //Excel file Creation Workbook workBook = new HSSFWorkbook(); Sheet workSheet = workBook.createSheet(); int cellIndex = 0, rowIndex = 0; Row singleRow = workSheet.createRow(rowIndex++); Cell headerCell = null; //Header row cell style CellStyle style = workBook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex()); style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font font = workBook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); /*------------ XLS Part END ------------*/ List<String> attributeList = new ArrayList<String>(); for (String tempAttribute : parameterizedAttributes) { attributeList.add(tempAttribute); headerCell = singleRow.createCell(cellIndex++); headerCell.setCellValue(tempAttribute); headerCell.setCellStyle(style); } if (screenAttributes == null || screenAttributes.equals("") || screenAttributes.equals("ALL")) { json.put("attributes", attributeList); } else { json.put("attributes", Arrays.asList(screenAttributes.split(","))); } json.put("sorting", (sorting == null || sorting.isEmpty() || sorting.equals("-") ? null : sorting)); json.put("projectNames", projectNames); List<ProjectAttribute> allProjectAttributes = pseEjb.getProjectAttributes(projectIds); Map<Long, List<ProjectAttribute>> projIdVsAttributes = new HashMap<Long, List<ProjectAttribute>>(); for (ProjectAttribute pa : allProjectAttributes) { List<ProjectAttribute> paList = projIdVsAttributes.get(pa.getProjectId()); if (paList == null) { paList = new ArrayList<ProjectAttribute>(); projIdVsAttributes.put(pa.getProjectId(), paList); } paList.add(pa); } List<Sample> allSamplesAllProjects = pseEjb.getSamplesForProjects(projectIds); Map<Long, List<Sample>> projectIdVsSampleList = new HashMap<Long, List<Sample>>(); for (Sample sample : allSamplesAllProjects) { List<Sample> thisProjectsSamples = projectIdVsSampleList.get(sample.getProjectId()); if (thisProjectsSamples == null) { thisProjectsSamples = new ArrayList<Sample>(); projectIdVsSampleList.put(sample.getProjectId(), thisProjectsSamples); } thisProjectsSamples.add(sample); } /************* Main LOOP starts *****************/ List<JSONObject> sampleList = new ArrayList<JSONObject>(); List<String> statusList = new ArrayList<String>(); List<JSONObject> sumList = new ArrayList<JSONObject>(); for (Project project : projects) { JSONObject currSum = new JSONObject(); if (project.getIsPublic() == 0) continue; Long tempProjectId = project.getProjectId(); List<ProjectAttribute> paList = projIdVsAttributes.get(tempProjectId); Map<String, Object> projectAttrMap = new HashMap<String, Object>(); if (paList != null) { for (ProjectAttribute pa : paList) { ProjectMetaAttribute projectMeta = pa.getMetaAttribute(); tempLookupValue = projectMeta.getLookupValue(); projectAttrMap.put(tempLookupValue.getName(), ModelValidator.getModelValue(tempLookupValue, pa)); if (projectMeta.getLabel() != null) { //add another key-value pair for a labeled attribute projectAttrMap.put(projectMeta.getLabel(), ModelValidator.getModelValue(tempLookupValue, pa)); } } } if (!projectAttrMap.containsKey(Constants.ATTR_PROJECT_NAME)) projectAttrMap.put(Constants.ATTR_PROJECT_NAME, project.getProjectName()); currSum.put("p_n", project.getProjectName()); currSum.put("p_s", projectAttrMap.get(PROJECT_STATUS)); currSum.put("p_g", projectAttrMap.get("Project Group")); List<Long> sampleIdList = getSampleIdList(getSamplesFromList(projectIdVsSampleList, tempProjectId)); Map<Long, List<SampleAttribute>> sampleIdVsAttributeList = getSampleVsAttributeList(sampleIdList); Map<Long, List<Event>> sampleIdVsEventList = getSampleIdVsEventList(sampleIdList); List<Sample> samplesForProject = getSamplesFromList(projectIdVsSampleList, tempProjectId); currSum.put("tot", samplesForProject.size()); for (Sample sample : samplesForProject) { Map<String, Object> sampleAttrMap = new HashMap<String, Object>(); sampleAttrMap.putAll(projectAttrMap); sampleAttrMap.put(Constants.ATTR_SAMPLE_NAME, sample.getSampleName()); sampleAttrMap.put("sampleId", sample.getSampleId()); List<SampleAttribute> sampleAttributes = sampleIdVsAttributeList.get(sample.getSampleId()); if (sampleAttributes != null && sampleAttributes.size() > 0) { for (SampleAttribute sa : sampleAttributes) { if (sa.getMetaAttribute() == null) continue; SampleMetaAttribute sampleMeta = sa.getMetaAttribute(); tempLookupValue = sampleMeta.getLookupValue(); Object sav = ModelValidator.getModelValue(tempLookupValue, sa); sampleAttrMap.put(tempLookupValue.getName(), sav); if (sampleMeta.getLabel() != null) { //add another key-value pair for a labeled attribute sampleAttrMap.put(sampleMeta.getLabel(), sav); } if (SAMPLE_STATUS.equals(tempLookupValue.getName())) { String currStatus = (String) sav; if (!statusList.contains(currStatus)) //add new status value statusList.add(currStatus); currSum.put(currStatus, currSum.has(currStatus) ? currSum.getInt(currStatus) + 1 : 1); //count } } } List<Event> sampleEvents = sampleIdVsEventList.get(sample.getSampleId()); if (sampleEvents != null && sampleEvents.size() > 0) { Map<Long, List<EventAttribute>> eventIdVsAttributes = getEventIdVsAttributeList( sampleEvents, tempProjectId); //skip sample status value in event attributes String[] skipArrForEventAttribute = { "Sample Status" }; for (Event evt : sampleEvents) { List<EventAttribute> eventAttributes = eventIdVsAttributes.get(evt.getEventId()); if (eventAttributes == null) continue; sampleAttrMap.putAll(CommonTool.getAttributeValueMap(eventAttributes, false, skipArrForEventAttribute)); } } if (!sampleAttrMap.containsKey("Organism")) { //manually add Organism attribute if not exist for GCID projects sampleAttrMap.put("Organism", ""); } JSONObject sampleJsonObj = new JSONObject(); for (String key : sampleAttrMap.keySet()) { //this is custom decorating process for json data file only //in status.shtml page, link on an organism should land to the project page rather than sample detail page if (key.equals("Organism")) { String organismVal = (String) sampleAttrMap.get(key); if (organismVal == null) { //get different attribute value for GCID projects organismVal = (String) sampleAttrMap.get("Species Source Common Name(CS4)"); } sampleJsonObj.put("OrganismUrl", (PROD_SERVER_ADDRESS + Constants.SAMPLE_DETAIL_URL + "iss=true" + "&projectName=" + project.getProjectName() + "&projectId=" + project.getProjectId() + "&sampleName=" + sampleAttrMap.get("Sample Name") + "&sampleId=" + sampleAttrMap.get("sampleId")).replaceAll("\\\"", "\\\\\"")); if (domain != null && !"none".equals(domain)) { String projectGroup = (String) sampleAttrMap.get("Project Group"); organismVal = convertIntoATag(String.format(Constants.PROJECT_SPECIFIC_PAGE, domain, //hostName != null && hostName.contains("spike") ? fileName + "-dev" : fileName, (projectGroup == null ? "" : projectGroup.toLowerCase()), project.getProjectName().replaceAll(" ", "_")), organismVal); } sampleJsonObj.put(key, organismVal); } else { sampleJsonObj.put(key, CommonTool.decorateAttribute(sampleAttrMap, key, project)); } } sampleList.add(sampleJsonObj); cellIndex = 0; singleRow = workSheet.createRow(rowIndex++); for (String tempAttribute : parameterizedAttributes) { singleRow.createCell(cellIndex++) .setCellValue(sampleAttrMap.get(tempAttribute) != null ? "" + sampleAttrMap.get(tempAttribute) : ""); } } sumList.add(currSum); } JSONObject sumMap = new JSONObject(); sumMap.put("s_l", statusList); sumMap.put("data", sumList); json.put("sums", sumMap); json.put("samples", sampleList); //bufferedWriter.write("]"); bufferedWriter.write(json.toString()); bufferedWriter.close(); if (tempFile.exists() && tempFile.length() > 0) { File dataFile = new File(filePath + File.separator + fileName + ".json"); tempFile.renameTo(dataFile); FileOutputStream fileOut = new FileOutputStream(filePath + File.separator + fileName + ".xls"); workBook.write(fileOut); fileOut.close(); } else throw new Exception("Failure in retrieving data for " + fileName + ". File does not exist or file size is zero."); logger.info("[JsonProducer-MBean] JsonProducer process succeeded for " + projectNames); } catch (Exception ex) { logger.info("[JsonProducer-MBean] JsonProducer failed for " + projectNames); ex.printStackTrace(); /*if( hostName.contains( "dmzweb" ) ) { //Send error notification for DMZs only new EmailSender().send( "json", "[PST]Failure in generating Json Data file on : " + hostName, ex.toString() ); }*/ } }