List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle
@Override
public HSSFCellStyle createCellStyle()
From source file:gov.guilin.ExcelView.java
License:Open Source License
/** * ?Excel/*ww w . j av a 2 s . co m*/ * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
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//from w w w . j a va 2 s .com * @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);// www . ja v a2 s . c o m Publication publication = (Publication) aPub.getDomainFile(); HSSFRow row = null; 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//from w w w. j a v a 2 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 a2 s . 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 ww w. ja v a2 s . co 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 {/*from w w w . j a v a 2s .c o 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();//from w ww . j a v a 2 s. 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;/*ww w .j a va2 s .com*/ 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;//from w w w. j a v a 2s . c om 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); } } }