List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
From source file:gov.nih.nci.cananolab.service.publication.impl.PublicationExporter.java
License:BSD License
/** * Output Excel report for sample publication summary report. * * @param summaryBean//www . ja v a2 s. co m * @param wb */ private static void exportSummarySheet(PublicationSummaryViewBean summaryBean, HSSFWorkbook wb) { HSSFRow row = null; HSSFFont headerFont = wb.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); Set<String> categories = summaryBean.getPublicationCategories(); if (categories != null && !categories.isEmpty()) { for (String category : categories) { int rowIndex = 0; // Create one work sheet for each category. HSSFSheet sheet = wb.createSheet(category); row = sheet.createRow(rowIndex++); // Output header of report ExportUtils.createCell(row, 0, headerStyle, BIBLIOBRAPHY_INFO); ExportUtils.createCell(row, 1, headerStyle, RESEARCH_CATEGORY); ExportUtils.createCell(row, 2, headerStyle, DESCRIPTION); ExportUtils.createCell(row, 3, headerStyle, PUB_STATUS); // Output data of report SortedMap<String, List<PublicationBean>> pubs = summaryBean.getCategory2Publications(); if (pubs != null && !pubs.isEmpty()) { List<PublicationBean> pubBeans = pubs.get(category); if (pubBeans != null && !pubBeans.isEmpty()) { for (PublicationBean pubBean : pubBeans) { Publication pub = (Publication) pubBean.getDomainFile(); row = sheet.createRow(rowIndex++); // Bibliography Info: cell index = 0. ExportUtils.createCell(row, 0, getBibliographyInfo(pubBean)); // Research Category: cell index = 1. ExportUtils.createCell(row, 1, pub.getResearchArea()); // Description: cell index = 2. if (!StringUtils.isEmpty(pub.getDescription())) { ExportUtils.createCell(row, 2, pub.getDescription()); } // Publication Status: cell index = 3. ExportUtils.createCell(row, 3, pub.getStatus()); } } } } } }
From source file:gov.nih.nci.cananolab.service.publication.impl.PublicationExporter.java
License:BSD License
private static int setDetailSheet(PublicationBean aPub, HSSFWorkbook wb, HSSFSheet sheet, HSSFPatriarch patriarch, int rowIndex) { HSSFFont headerFont = wb.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); Publication publication = (Publication) aPub.getDomainFile(); HSSFRow row = null;/*from www . ja v a2 s .c o m*/ HSSFCell cell = null; // PubMedID Long pubMedId = publication.getPubMedId(); row = sheet.createRow(rowIndex++); int cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Publication Identifier")); if (pubMedId != null && pubMedId.intValue() > 0) { row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(pubMedId.toString())); } else { String oid = publication.getDigitalObjectId(); if (!StringUtils.isEmpty(oid)) { row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(oid)); } else { // row.createCell(cellIndex++).setCellValue( // new HSSFRichTextString("")); } } // publication type row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Publication Type")); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getCategory())); // publication status row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString(PUB_STATUS)); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getStatus())); // Authors String rowHeader = "Authors"; StringBuffer sb = new StringBuffer(); if (publication.getAuthorCollection() != null) { List<Author> authorslist = new ArrayList<Author>(publication.getAuthorCollection()); Collections.sort(authorslist, new Comparator<Author>() { public int compare(Author o1, Author o2) { return (int) (o1.getCreatedDate().compareTo(o2.getCreatedDate())); } }); for (Author author : authorslist) { sb.append(author.getFirstName()); sb.append(' '); sb.append(author.getInitial()); sb.append(' '); sb.append(author.getLastName()); row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString(rowHeader)); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(sb.toString())); rowHeader = ""; sb.setLength(0); } } // research area row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString(RESEARCH_CATEGORY)); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getResearchArea())); // Title row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Title")); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getTitle())); // Journal row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Journal")); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getJournalName())); // Year row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Year")); int year = 0; if (publication.getYear() != null) year = publication.getYear(); if (year > 0) { row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(Integer.toString(year))); } // Volume row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Volume")); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getVolume())); // Pages row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Pages")); String startPage = publication.getStartPage(); String endPage = publication.getEndPage(); if ((!StringUtils.isEmpty(startPage)) || (!StringUtils.isEmpty(endPage))) { row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getJournalName())); } // Description row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString(DESCRIPTION)); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getDescription())); // Uploaded Publication URI row = sheet.createRow(rowIndex++); cellIndex = 0; cell = row.createCell(cellIndex++); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Publication URI")); row.createCell(cellIndex++).setCellValue(new HSSFRichTextString(publication.getUri())); return rowIndex; }
From source file:gov.nih.nci.cananolab.service.sample.impl.CharacterizationExporter.java
License:BSD License
/** * Output Sample Characterization Summary report (==> * bodyCharacterizationSummaryPrintViewTable.jsp) * * @param summaryBean// w w w . ja va2 s. c o m * @param wb * @throws IOException */ private static void outputSummarySheet(List<String> charTypes, CharacterizationSummaryViewBean summaryBean, String downloadURL, HSSFWorkbook wb) throws IOException { HSSFFont headerFont = wb.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); HSSFCellStyle hlinkStyle = wb.createCellStyle(); HSSFFont hlinkFont = wb.createFont(); hlinkFont.setUnderline(HSSFFont.U_SINGLE); hlinkFont.setColor(HSSFColor.BLUE.index); hlinkStyle.setFont(hlinkFont); int charCount = 1; Map<String, SortedSet<CharacterizationBean>> charBeanMap = summaryBean.getType2Characterizations(); for (String type : charTypes) { // Output data of report SortedSet<CharacterizationBean> charBeans = charBeanMap.get(type); if (charBeans != null && !charBeans.isEmpty()) { for (CharacterizationBean charBean : charBeans) { int rowIndex = 0; // Create one work sheet for each Characterization. HSSFSheet sheet = wb.createSheet(charCount++ + "." + charBean.getCharacterizationName()); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 1. Output Characterization type at (0, 0). rowIndex = outputHeader(charBean, sheet, headerStyle, rowIndex); // 2. Output Assay Type (2, 0). rowIndex = outputAssayType(charBean, sheet, headerStyle, rowIndex); // 3. Output POC at (3, 0). rowIndex = outputPOC(charBean, sheet, headerStyle, rowIndex); // 4. Output Characterization Date at (4, 0). rowIndex = outputCharDate(charBean, sheet, headerStyle, rowIndex); // 5. Output Protocol at (5, 0). rowIndex = outputProtocol(charBean, sheet, headerStyle, rowIndex); // 6. Output Properties at (6, 0). rowIndex = outputProperties(charBean, sheet, headerStyle, rowIndex); // 7. Output Design Description at (7, 0). rowIndex = outputDesignDescription(charBean, sheet, headerStyle, rowIndex); // 8. Output Technique and Instruments at (8, 0). rowIndex = outputTechInstruments(charBean, sheet, headerStyle, rowIndex); // 9. Output Characterization Results at (9, 0). rowIndex = outputCharResults(charBean, downloadURL, wb, sheet, headerStyle, hlinkStyle, patriarch, rowIndex); // 10.Output Analysis and Conclusion at (10, 0). rowIndex = outputConclusion(charBean, sheet, headerStyle, rowIndex); } } } }
From source file:gov.nih.nci.cananolab.service.sample.impl.CompositionExporter.java
License:BSD License
/** * Output sample Composition Summary report => * bodyCompositionSummaryView.jsp/*from w w w . jav a2s .c o m*/ * * @param compBean * @param wb */ private static void outputSummarySheet(CompositionBean compBean, String downloadURL, HSSFWorkbook wb) throws IOException { HSSFFont headerFont = wb.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); HSSFCellStyle hlinkStyle = wb.createCellStyle(); HSSFFont hlinkFont = wb.createFont(); hlinkFont.setUnderline(HSSFFont.U_SINGLE); hlinkFont.setColor(HSSFColor.BLUE.index); hlinkStyle.setFont(hlinkFont); int entityCount = 1; entityCount = outputNanomaterialEntities(compBean, wb, headerStyle, hlinkStyle, entityCount, downloadURL); entityCount = outputFunctionalEntities(compBean, wb, headerStyle, hlinkStyle, entityCount, downloadURL); entityCount = outputChemicalEntities(compBean, wb, headerStyle, hlinkStyle, entityCount, downloadURL); outputFilesEntities(compBean, wb, headerStyle, hlinkStyle, entityCount, downloadURL); }
From source file:gov.nih.nci.cananolab.service.sample.impl.SampleExporter.java
License:BSD License
/** * Output advance sample summary report, representing, * bodyAdvancedSampleSearchResult.jsp//from w ww . ja v a 2s . c o m * * @param searchBean * @param wb */ private static void outputSummarySheet(AdvancedSampleSearchBean searchBean, List<AdvancedSampleBean> sampleBeans, String viewSampleUrl, HSSFWorkbook wb) throws IOException { HSSFFont headerFont = wb.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); HSSFCellStyle hlinkStyle = wb.createCellStyle(); HSSFFont hlinkFont = wb.createFont(); hlinkFont.setUnderline(HSSFFont.U_SINGLE); hlinkFont.setColor(HSSFColor.BLUE.index); hlinkStyle.setFont(hlinkFont); int rowIndex = 0; HSSFSheet sheet = wb.createSheet("Advanced Sample Search Report"); // 1.Output Search Criteria. comment out as per Sharon. // rowIndex = outputCriteria(searchBean, sheet, headerStyle, rowIndex); // 2.Output table column headers. rowIndex = outputHeader(sampleBeans.get(0), sheet, headerStyle, rowIndex); // 3.Output each table row. for (AdvancedSampleBean sampleBean : sampleBeans) { rowIndex = outputRow(sampleBean, viewSampleUrl, sheet, hlinkStyle, rowIndex); } }
From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runHSSF(String inputfile) { int size = checkSpecialCharacters(inputfile); int n = inputfile.lastIndexOf("."); String outputfile = getOutputFile(inputfile, "xls"); try {// ww w .j a va 2 s . co m HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle cellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //font.setColor(HSSFColor.WHITE.index); cellStyle.setFont(font); HSSFCellStyle linkCellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont linkfont = wb.createFont(); linkfont.setColor(HSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); //CreationHelper helper = wb.getCreationHelper(); CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file String[] line; int r = 0; Cell cell = null; //"C7419","Acanthoma","C0846967","Acanthoma","MDR","Acanthoma","10059394","LT" // skip heading HSSFHyperlink url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); HSSFSheet sheet = null; int page_num = 1; Row row = null; line = reader.readNext(); int lcv = 0; int row_count = 0; Vector w = new Vector(); while ((line = reader.readNext()) != null) { if (lcv % PAGE_SIZE == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel + " (Page " + page_num + ")"; } sheet = wb.createSheet(sheetLabel); for (int j = 0; j < HEADINGS.length; j++) { sheet.autoSizeColumn(j); } row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } sheet.createFreezePane(0, 1); // this will freeze the header row page_num++; } String s4 = (String) line[4]; if (s4.compareTo("NCI") == 0) { w.add("NCI Line number: " + r); } else if (s4.compareTo("NCI") != 0) { r++; row = sheet.createRow((short) r); row_count++; row = sheet.createRow((short) r); row_count++; for (int i = 0; i < HEADINGS.length; i++) { cell = row.createCell(i); int codeCol = codeColumn[i]; cell.setCellValue(line[i]); if (NCIT_LINK && codeCol == LINK_NCIT) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCItHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (NCIM_LINK && codeCol == LINK_NCIM) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCImHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (SOURCE_LINK && codeCol == LINK_OTHER) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; String source = line[SOURCE_INDEX]; if (localNameMap.containsKey(source)) { url_link.setAddress(getSourceHyperlink(source, code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } } lcv++; } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); System.out.println("row_count: " + row_count); System.out.println("NCI: " + w.size()); for (int i = 0; i < w.size(); i++) { String t = (String) w.elementAt(i); System.out.println(t); } } catch (Exception ex) { ex.printStackTrace(); } }
From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runHSSF(Vector data_vec, String outputfile) { if (codeColumn == null) { setDefault();// w ww .j ava 2s.c o m } //int size = 0;//checkSpecialCharacters(inputfile); int size = data_vec.size(); String[] line = null; try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle cellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //font.setColor(HSSFColor.WHITE.index); cellStyle.setFont(font); HSSFCellStyle linkCellStyle = wb.createCellStyle(); //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont linkfont = wb.createFont(); linkfont.setColor(HSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); //CreationHelper helper = wb.getCreationHelper(); //CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file int r = 0; Cell cell = null; //"C7419","Acanthoma","C0846967","Acanthoma","MDR","Acanthoma","10059394","LT" // skip heading HSSFHyperlink url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); HSSFSheet sheet = null; int page_num = 1; Row row = null; //line = reader.readNext(); int lcv = 0; int row_count = 0; Vector w = new Vector(); String nextLine = null; Vector values = null; for (int k = 0; k < data_vec.size(); k++) { nextLine = (String) data_vec.elementAt(k); int k1 = k + 1; line = toArray(nextLine); //if (lcv % PAGE_SIZE == 0) { if (lcv == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel;// + " (Page " + page_num + ")"; } sheet = wb.createSheet(sheetLabel); for (int j = 0; j < HEADINGS.length; j++) { sheet.autoSizeColumn(j); } row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } sheet.createFreezePane(0, 1); // this will freeze the header row page_num++; } String s4 = line[4]; if (s4.compareTo("NCI") == 0) { w.add("NCI Line number: " + r); } else { //if (s4.compareTo("NCI") != 0) { r++; row = sheet.createRow((short) r); row_count++; for (int i = 0; i < HEADINGS.length; i++) { cell = row.createCell(i); int codeCol = codeColumn[i]; cell.setCellValue(line[i]); if (NCIT_LINK && codeCol == LINK_NCIT) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCItHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (NCIM_LINK && codeCol == LINK_NCIM) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; url_link.setAddress(getNCImHyperlink(code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } else if (SOURCE_LINK && codeCol == LINK_OTHER) { url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); String code = line[i]; String source = line[SOURCE_INDEX]; if (localNameMap.containsKey(source)) { url_link.setAddress(getSourceHyperlink(source, code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } } lcv++; } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:gov.nih.nci.evs.reportwriter.formatter.AsciiToExcelFormatter.java
License:BSD License
public Boolean convert(String textfile, String delimiter, String outfile) throws Exception { Vector<String> headings = getColumnHeadings(textfile, delimiter); Vector<Integer> maxChars = getColumnMaxChars(textfile, delimiter); // Note: Special Case for CDISC STDM Terminology report. int extensible_col = -1; if (_specialCases_CDISC) extensible_col = findColumnIndicator(headings, "Extensible"); int heading_height_multiplier = 1; for (int i = 0; i < maxChars.size(); i++) { String heading = (String) headings.elementAt(i); int maxCellLen = maxChars.elementAt(i); int maxTokenLen = getMaxTokenLength(heading); if (maxTokenLen > maxCellLen) { maxCellLen = maxTokenLen;/*from ww w . j av a2 s. co m*/ maxChars.setElementAt(new Integer(maxCellLen), i); } if (maxCellLen < MAX_CODE_WIDTH) { Vector<String> tokens = parseData(heading, " "); if (tokens.size() > heading_height_multiplier) heading_height_multiplier = tokens.size(); } } Boolean[] a = findWrappedColumns(textfile, delimiter, MAX_WIDTH); // Note: The max column number allowed in an Excel spreadsheet is 256 int[] b = new int[255]; for (int i = 0; i < 255; i++) { b[i] = 0; } File file = new File(textfile); String absolutePath = file.getAbsolutePath(); _logger.debug("Absolute Path: " + absolutePath); String filename = file.getName(); _logger.debug("filename: " + filename); int m = filename.indexOf("."); String workSheetLabel = filename.substring(0, m); int n = workSheetLabel.indexOf("__"); workSheetLabel = workSheetLabel.substring(0, n); _logger.debug("workSheetLabel: " + workSheetLabel); if (workSheetLabel.compareTo("") == 0) return Boolean.FALSE; String pathName = file.getPath(); _logger.debug("Path: " + pathName); BufferedReader br = getBufferReader(textfile); FileOutputStream fout = new FileOutputStream(outfile); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet ws = wb.createSheet(workSheetLabel); HSSFCellStyle toprow = wb.createCellStyle(); HSSFCellStyle highlightedrow = wb.createCellStyle(); HSSFCellStyle cs = wb.createCellStyle(); // Note: GF20673 shade top row HSSFFont font = wb.createFont(); font.setColor(HSSFColor.BLACK.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); toprow.setFont(font); if (extensible_col == -1) { toprow.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); } else { //toprow.setFillForegroundColor(HSSFColor.YELLOW.index); toprow.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); } toprow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); toprow.setAlignment(HSSFCellStyle.VERTICAL_CENTER); toprow.setWrapText(true); highlightedrow.setFont(font); //highlightedrow.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //highlightedrow.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); highlightedrow.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); highlightedrow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); highlightedrow.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // highlightedrow.setWrapText(true); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); cs.setAlignment(HSSFCellStyle.VERTICAL_TOP); HSSFRow wr = null; int rownum = 0; // int baseline_height = 15; int baseline_height = 12; while (true) { String line = br.readLine(); if (line == null) break; // line = line.trim(); Note: 090512 first value could be empty if (line.length() <= 0) continue; Vector<String> v = parseData(line, delimiter); wr = ws.createRow(rownum); // wr.setHeightInPoints(60); if (rownum == 0) { wr.setHeightInPoints(baseline_height * heading_height_multiplier); } else { wr.setHeightInPoints(baseline_height); if (ADJUST_HEIGHT) { int num_lines = getHeightInPoints(v, ADJUST_HEIGHT, MAX_CELL_WIDTH); wr.setHeightInPoints(baseline_height * num_lines); } } // Note: Special Case for CDISC STDM Terminology report. boolean highlight_row = false; if (_specialCases_CDISC) highlight_row = extensible_col != -1 && v.elementAt(extensible_col).trim().length() > 0; for (int i = 0; i < v.size(); i++) { HSSFCell wc = wr.createCell(i); if (rownum == 0) { wc.setCellStyle(toprow); } else if (a[i].equals(Boolean.TRUE)) { wc.setCellStyle(cs); wc.setCellType(HSSFCell.CELL_TYPE_STRING); if (highlight_row) wc.setCellStyle(highlightedrow); } else { if (highlight_row) wc.setCellStyle(highlightedrow); } String s = (String) v.elementAt(i); s = s.trim(); if (s.length() > b[i]) { b[i] = s.length(); } if (s.equals("")) { s = null; } wc.setCellValue(s); if (_ncitCodeColumns.contains(i) && rownum > 0 && s != null && s.length() > 0) { try { wc.setCellFormula("HYPERLINK(\"" + getNCItCodeUrl(s) + "\", \"" + s + "\")"); } catch (Exception e) { ExceptionUtils.print(_logger, e, "The following string is too large to be a " + "valid NCIt code (" + filename + "): " + s); } } } rownum++; } br.close(); for (int i = 0; i < 255; i++) { if (b[i] != 0) { int multiplier = b[i]; if (i < headings.size()) { Integer int_obj = (Integer) maxChars.elementAt(i); multiplier = int_obj.intValue(); } // Note(GF20673): 315 is the magic number for this font and size int colWidth = multiplier * 315; // Fields like definition run long, some sanity required if (colWidth > 20000) { colWidth = 20000; } // _logger.debug("Calculated column width " + i + ": " + // colWidth); ws.setColumnWidth(i, colWidth); } } // Note(GF20673): Freeze top row ws.createFreezePane(0, 1, 0, 1); wb.write(fout); fout.close(); return Boolean.TRUE; }
From source file:gov.nih.nci.ncicb.cadsr.cdebrowser.process.GetExcelDownload.java
License:BSD License
public void generateExcelFile(String filename, DBUtil dbUtil) throws Exception { Connection cn = null;/* ww w . j a v a 2s. c o m*/ Statement st = null; ResultSet rs = null; PrintWriter pw = null; String where = ""; DataElementSearchBean desb = null; DESearchQueryBuilder deSearch = null; String source = null; HSSFWorkbook wb = null; FileOutputStream fileOut = null; source = getStringInfo("src"); String RAI = ""; try { RAI = "'" + CaDSRUtil.getNciRegistryId() + "'"; } catch (IOException e) { RAI = DEFAULT_RAI; } try { //String dataSource = getStringInfo("SBREXT_DSN"); //cn = dbUtil.getConnection(); -- Commented for JBoss deployment //ApplicationParameters ap = ApplicationParameters.getInstance("cdebrowser"); dbUtil.getOracleConnectionFromContainer(); //getConnectionFromContainer(); went back to original call cn = dbUtil.getConnection(); st = cn.createStatement(); if ("deSearch".equals(source) || "deSearchPrior".equals(source)) { desb = (DataElementSearchBean) getInfoObject("desb"); deSearch = (DESearchQueryBuilder) getInfoObject(ProcessConstants.DE_SEARCH_QUERY_BUILDER); where = deSearch.getXMLQueryStmt(); } else if ("cdeCart".equals(source) || "cdeCartPrior".equals(source)) { HttpServletRequest myRequest = (HttpServletRequest) getInfoObject("HTTPRequest"); HttpSession userSession = myRequest.getSession(false); CDECart cart = (CDECart) userSession.getAttribute(CaDSRConstants.CDE_CART); Collection items = cart.getDataElements(); CDECartItem item = null; boolean firstOne = true; StringBuffer whereBuffer = new StringBuffer(""); Iterator itemsIt = items.iterator(); while (itemsIt.hasNext()) { item = (CDECartItem) itemsIt.next(); if (firstOne) { whereBuffer.append("'" + item.getId() + "'"); firstOne = false; } else { whereBuffer.append(",'" + item.getId() + "'"); } } where = whereBuffer.toString(); } else { throw new Exception("No result set to download"); } String sqlStmt = "SELECT DE_EXCEL_GENERATOR_VIEW.*," + RAI + " as \"RAI\" FROM DE_EXCEL_GENERATOR_VIEW " + "WHERE DE_IDSEQ IN " + " ( " + where + " ) "; //+" ORDER BY PREFERRED_NAME "; rs = st.executeQuery(sqlStmt); List colInfo = this.initColumnInfo(source); wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); int rowNumber = 0; HSSFCellStyle boldCellStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldCellStyle.setFont(font); boldCellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL); // Create a row and put the column header in it HSSFRow row = sheet.createRow(rowNumber++); short col = 0; for (int i = 0; i < colInfo.size(); i++) { ColumnInfo currCol = (ColumnInfo) colInfo.get(i); if (currCol.type.indexOf("Array") >= 0) { for (int nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) { ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI); HSSFCell cell = row.createCell(col++); cell.setCellValue(currCol.displayName + nestedCol.displayName); cell.setCellStyle(boldCellStyle); } } else { HSSFCell cell = row.createCell(col++); cell.setCellValue(currCol.displayName); cell.setCellStyle(boldCellStyle); } } int maxRowNumber = 0; while (rs.next()) { row = sheet.createRow(rowNumber); col = 0; for (int i = 0; i < colInfo.size(); i++) { ColumnInfo currCol = (ColumnInfo) colInfo.get(i); if (currCol.type.indexOf("Array") >= 0) { ARRAY array = null; if (currCol.type.equalsIgnoreCase("Array")) { array = ((OracleResultSet) rs).getARRAY(currCol.rsColumnName); } else if (currCol.type.equalsIgnoreCase("StructArray")) { STRUCT struct = ((OracleResultSet) rs).getSTRUCT(currCol.rsColumnName); Object[] valueStruct = struct.getAttributes(); array = (ARRAY) valueStruct[currCol.rsIndex]; } if ((array != null) && (array.length() != 0)) { ResultSet nestedRs = array.getResultSet(); int nestedRowNumber = 0; while (nestedRs.next()) { row = sheet.getRow(rowNumber + nestedRowNumber); if (row == null) { row = sheet.createRow(rowNumber + nestedRowNumber); maxRowNumber = rowNumber + nestedRowNumber; } STRUCT valueStruct = null; // STRUCT valueStruct = (STRUCT) nestedRs.getObject(2); try { valueStruct = (STRUCT) nestedRs.getObject(2); } catch (SQLException sqlEx) { //sqlEx.printStackTrace(); } if (valueStruct != null) { Datum[] valueDatum = valueStruct.getOracleAttributes(); for (short nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) { ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI); HSSFCell cell = row.createCell((short) (col + nestedI)); if (nestedCol.rsSubIndex < 0) { if (valueDatum[nestedCol.rsIndex] != null) { if (nestedCol.type.equalsIgnoreCase("Number")) { cell.setCellValue( ((NUMBER) valueDatum[nestedCol.rsIndex]).floatValue()); } else if (nestedCol.type.equalsIgnoreCase("Date")) { cell.setCellValue(((DATE) valueDatum[nestedCol.rsIndex]) .dateValue().toString()); } else { String stringCellValue = ((CHAR) valueDatum[nestedCol.rsIndex]) .stringValue(); cell.setCellValue(StringUtils .updateDataForSpecialCharacters(stringCellValue)); // cell.setCellValue( // ((CHAR) valueDatum[nestedCol.rsIndex]).stringValue()); } } } else { STRUCT nestedStruct = (STRUCT) valueDatum[nestedCol.rsIndex]; Datum[] nestedDatum = nestedStruct.getOracleAttributes(); if (nestedCol.type.equalsIgnoreCase("Number")) { //changed the conversion from stringValue from floatValue 07/11/2007 to fix GF7664 Prerna cell.setCellValue( ((NUMBER) nestedDatum[nestedCol.rsSubIndex]).stringValue()); } else if (nestedCol.type.equalsIgnoreCase("String")) { String stringCellValue = ((CHAR) nestedDatum[nestedCol.rsSubIndex]) .toString(); cell.setCellValue(StringUtils .updateDataForSpecialCharacters(stringCellValue)); // cell.setCellValue( // ((CHAR) nestedDatum[nestedCol.rsSubIndex]).toString()); } } } } nestedRowNumber++; } } col += currCol.nestedColumns.size(); } else if (currCol.type.equalsIgnoreCase("Struct")) { STRUCT struct = ((OracleResultSet) rs).getSTRUCT(currCol.rsColumnName); Object[] valueStruct = struct.getAttributes(); HSSFCell cell = row.createCell(col++); cell.setCellValue( StringUtils.updateDataForSpecialCharacters((String) valueStruct[currCol.rsIndex])); } else { row = sheet.getRow(rowNumber); HSSFCell cell = row.createCell(col++); // Changed the way date is displayed in Excel in 4.0 String columnName = ((ColumnInfo) colInfo.get(i)).rsColumnName; if (currCol.type.equalsIgnoreCase("Date")) { cell.setCellValue( (rs.getDate(columnName) != null) ? (rs.getDate(columnName)).toString() : ""); } else { /* if (columnName.equals("RAI")) { if (rowNumber == 1) cell.setCellValue(RAI); else cell.setCellValue(""); } else { */ cell.setCellValue(StringUtils.updateDataForSpecialCharacters(rs.getString(columnName))); //} } } } if (maxRowNumber > rowNumber) rowNumber = maxRowNumber + 2; else rowNumber += 2; } fileOut = new FileOutputStream(filename); wb.write(fileOut); } catch (Exception ex) { log.error("Exception caught in Generate Excel File", ex); ex.printStackTrace(); throw ex; } finally { try { if (rs != null) { rs.close(); } if (st != null) { st.close(); } if (cn != null) { cn.close(); // Uncommented for JBoss deployment } if (fileOut != null) { fileOut.close(); } } catch (Exception e) { log.debug("Unable to perform clean up due to the following error ", e); } } }
From source file:gov.nih.nci.ncicb.cadsr.cdebrowser.struts.actions.CompareCDEAction.java
License:BSD License
public ActionForward downloadToExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { DynaActionForm hrefCRFForm = (DynaActionForm) form; CDECompareList cdeList = (CDECompareList) this.getSessionObject(request, CDE_COMPARE_LIST); // create a new excel workbook HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet();/* w w w . j a va 2 s . co m*/ short rowNumber = 0; short colNumber = 0; //create bold cell style HSSFCellStyle boldCellStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldCellStyle.setFont(font); boldCellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(rowNumber++); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("Data Element"); cell.setCellStyle(boldCellStyle); List cdeColl = cdeList.getCdeList(); addNewRow(sheet, rowNumber++, "Public ID", boldCellStyle, cdeColl, "CDEId"); addNewRow(sheet, rowNumber++, "Long Name", boldCellStyle, cdeColl, "longName"); addNewRow(sheet, rowNumber++, "Document Text", boldCellStyle, cdeColl, "longCDEName"); addNewRow(sheet, rowNumber++, "Definition", boldCellStyle, cdeColl, "preferredDefinition"); addNewRow(sheet, rowNumber++, "Owned by Context", boldCellStyle, cdeColl, "contextName"); addNewRow(sheet, rowNumber++, "Used by Context", boldCellStyle, cdeColl, "usingContexts"); addNewRow(sheet, rowNumber++, "Origin", boldCellStyle, cdeColl, "origin"); addNewRow(sheet, rowNumber++, "Workflow Status", boldCellStyle, cdeColl, "aslName"); addNewRow(sheet, rowNumber++, "Registration Status", boldCellStyle, cdeColl, "registrationStatus"); addNewRow(sheet, rowNumber++, "Short Name", boldCellStyle, cdeColl, "preferredName"); addNewRow(sheet, rowNumber++, "Version", boldCellStyle, cdeColl, "version"); row = sheet.createRow(rowNumber++); row = sheet.createRow(rowNumber++); cell = row.createCell((short) 0); cell.setCellValue("Data Element Concept"); cell.setCellStyle(boldCellStyle); List cdeConceptList = new ArrayList(); for (int i = 0; i < cdeColl.size(); i++) cdeConceptList.add(i, ((DataElement) cdeColl.get(i)).getDataElementConcept()); addNewRow(sheet, rowNumber++, "Public ID", boldCellStyle, cdeConceptList, "publicId"); addNewRow(sheet, rowNumber++, "Long Name", boldCellStyle, cdeConceptList, "longName"); addNewRow(sheet, rowNumber++, "Short Name", boldCellStyle, cdeConceptList, "preferredName"); addNewRow(sheet, rowNumber++, "Definition", boldCellStyle, cdeConceptList, "preferredDefinition"); addNewRow(sheet, rowNumber++, "Context", boldCellStyle, cdeConceptList, "contextName"); addNewRow(sheet, rowNumber++, "Conceptual Domain Short Name", boldCellStyle, cdeConceptList, "CDPrefName"); addNewRow(sheet, rowNumber++, "Object Class Short Name", boldCellStyle, cdeConceptList, "objectClass.preferredName"); addNewRow(sheet, rowNumber++, "Property Short Name", boldCellStyle, cdeConceptList, "property.preferredName"); addNewRow(sheet, rowNumber++, "Origin", boldCellStyle, cdeConceptList, "origin"); addNewRow(sheet, rowNumber++, "Workflow Status", boldCellStyle, cdeConceptList, "aslName"); row = sheet.createRow(rowNumber++); row = sheet.createRow(rowNumber++); cell = row.createCell((short) 0); cell.setCellValue("Value Domain"); cell.setCellStyle(boldCellStyle); addNewRow(sheet, rowNumber++, "Public ID", boldCellStyle, cdeColl, "valueDomain.publicId"); addNewRow(sheet, rowNumber++, "Long Name", boldCellStyle, cdeColl, "valueDomain.longName"); addNewRow(sheet, rowNumber++, "Short Name", boldCellStyle, cdeColl, "valueDomain.preferredName"); addNewRow(sheet, rowNumber++, "Definition", boldCellStyle, cdeColl, "valueDomain.preferredDefinition"); addNewRow(sheet, rowNumber++, "Data Type", boldCellStyle, cdeColl, "valueDomain.datatype"); addNewRow(sheet, rowNumber++, "Unit of Measure", boldCellStyle, cdeColl, "valueDomain.unitOfMeasure"); addNewRow(sheet, rowNumber++, "Display Format", boldCellStyle, cdeColl, "valueDomain.displayFormat"); addNewRow(sheet, rowNumber++, "Maximum Length", boldCellStyle, cdeColl, "valueDomain.maxLength"); addNewRow(sheet, rowNumber++, "Minimum Length", boldCellStyle, cdeColl, "valueDomain.minLength"); addNewRow(sheet, rowNumber++, "Decimal Place", boldCellStyle, cdeColl, "valueDomain.decimalPlace"); addNewRow(sheet, rowNumber++, "High Value", boldCellStyle, cdeColl, "valueDomain.highValue"); addNewRow(sheet, rowNumber++, "Low Value", boldCellStyle, cdeColl, "valueDomain.lowValue"); addNewRow(sheet, rowNumber++, "Value Domain Type", boldCellStyle, cdeColl, "valueDomain.VDType"); addNewRow(sheet, rowNumber++, "Conceptual Domain Short Name", boldCellStyle, cdeColl, "valueDomain.CDPrefName"); addNewRow(sheet, rowNumber++, "Representation", boldCellStyle, cdeColl, "valueDomain.representation.longName"); addNewRow(sheet, rowNumber++, "Origin", boldCellStyle, cdeColl, "valueDomain.origin"); addNewRow(sheet, rowNumber++, "Workflow Status", boldCellStyle, cdeColl, "valueDomain.aslName"); addNewRow(sheet, rowNumber++, "Version", boldCellStyle, cdeColl, "valueDomain.version"); List pvTitles = new ArrayList(); pvTitles.add(0, "Value"); pvTitles.add(1, "Value Meaning"); pvTitles.add(2, "Description"); List pvProperties = new ArrayList(); pvProperties.add(0, "shortMeaningValue"); pvProperties.add(1, "shortMeaning"); pvProperties.add(2, "shortMeaningDescription"); rowNumber += this.exportObjects(sheet, rowNumber, "Permissible Values", "valueDomain.validValues", boldCellStyle, cdeColl, pvProperties, pvTitles); List refDocPropertyTitles = new ArrayList(); refDocPropertyTitles.add(0, "Document Name"); refDocPropertyTitles.add(1, "Document Type"); refDocPropertyTitles.add(2, "Document Text"); List refDocProperties = new ArrayList(); refDocProperties.add(0, "docName"); refDocProperties.add(1, "docType"); refDocProperties.add(2, "docText"); rowNumber += this.exportObjects(sheet, rowNumber, "Reference Document", "refereceDocs", boldCellStyle, cdeColl, refDocProperties, refDocPropertyTitles); List csPropertyTitles = new ArrayList(); csPropertyTitles.add(0, "CS* Short Name"); csPropertyTitles.add(1, "CS* Definition"); csPropertyTitles.add(2, "CSI* Name"); csPropertyTitles.add(3, "CSI* Type"); List csProperties = new ArrayList(); csProperties.add(0, "classSchemeName"); csProperties.add(1, "classSchemeDefinition"); csProperties.add(2, "classSchemeItemName"); csProperties.add(3, "classSchemeItemType"); rowNumber += this.exportObjects(sheet, rowNumber, "Classifications", "classifications", boldCellStyle, cdeColl, csProperties, csPropertyTitles); row = sheet.createRow(rowNumber++); row = sheet.createRow(rowNumber++); cell = row.createCell((short) 0); cell.setCellValue("Data Element Derivation"); cell.setCellStyle(boldCellStyle); addNewRow(sheet, rowNumber++, "Derivation Type", boldCellStyle, cdeColl, "derivedDataElement.type.name"); addNewRow(sheet, rowNumber++, "Rule", boldCellStyle, cdeColl, "derivedDataElement.rule"); addNewRow(sheet, rowNumber++, "Method", boldCellStyle, cdeColl, "derivedDataElement.methods"); addNewRow(sheet, rowNumber++, "Concatenation Character", boldCellStyle, cdeColl, "derivedDataElement.concatenationCharacter"); List dedPropertyTitles = new ArrayList(); dedPropertyTitles.add(0, "Long Name"); dedPropertyTitles.add(1, "Context"); dedPropertyTitles.add(2, "Public ID"); dedPropertyTitles.add(3, "Version"); List dedProperties = new ArrayList(); dedProperties.add(0, "longName"); dedProperties.add(1, "contextName"); dedProperties.add(2, "CDEId"); dedProperties.add(3, "version"); rowNumber += this.exportObjects(sheet, rowNumber, "Component Data Elements", "derivedDataElement.dataElementDerivation", boldCellStyle, cdeColl, dedProperties, dedPropertyTitles); CDEBrowserParams params = CDEBrowserParams.getInstance(); String excelFilename = params.getXMLDownloadDir() + "compareCDEs" + ".xls"; FileOutputStream fileOut = new FileOutputStream(excelFilename); wb.write(fileOut); fileOut.close(); File f = new File(excelFilename); String ctype = ContentTypeHelper.getContentType(f.getName()); response.setContentType(ctype); response.setContentLength((int) f.length()); response.setHeader("Content-Disposition", "attachment;filename=\"" + f.getName() + "\""); response.setHeader("Pragma", "public"); response.setHeader("Expires", "0"); response.setHeader("Cache-Control", "max-age=0"); try { // create buffer byte[] buffer = new byte[1024]; int r = 0; // write out file FileInputStream fin = new FileInputStream(f); OutputStream out = response.getOutputStream(); while ((r = fin.read(buffer, 0, buffer.length)) != -1) { out.write(buffer, 0, r); } try { fin.close(); out.flush(); out.close(); } catch (Exception e) { } out = null; fin = null; buffer = null; } catch (Exception ex) { String msg = ex.getMessage(); response.setContentType("text/html"); response.setContentLength(msg.length()); PrintWriter out = response.getWriter(); out.println("Unexpected error"); out.flush(); out.close(); } return null; }