List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment
@Override public void setAlignment(HorizontalAlignment align)
From source file:gov.guilin.ExcelView.java
License:Open Source License
/** * ?Excel/*from w ww . jav a 2 s.c o 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.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 . ja va 2 s. c om 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;/*www . ja v a2 s. 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();//from ww w.j a v a 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; }
From source file:gov.nih.nci.ncicb.cadsr.common.downloads.impl.GetExcelDownloadImpl.java
License:BSD License
private void generateExcelFile() throws Exception { Connection cn = null;/*from w w w. j a v a2s . co m*/ Statement st = null; ResultSet rs = null; FileOutputStream fileOut = null; try { HSSFWorkbook 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; List colInfo = this.initColumnInfo(source); 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); } } String sqlStmt = "SELECT * FROM DE_EXCEL_GENERATOR_VIEW " + "WHERE DE_IDSEQ IN " + " ( " + where + " ) "; ConnectionHelper connHelper = new ConnectionHelper(jndiName); cn = connHelper.getConnection(); if (cn == null) { throw new Exception("Cannot get the connection for the JNDI name [" + jndiName + "]"); } st = cn.createStatement(); rs = st.executeQuery(sqlStmt); generateDataRow(rowNumber, sheet, colInfo, rs); String filename = getFileName(); fileOut = new FileOutputStream(filename); wb.write(fileOut); } catch (SQLException e) { log.warn("Database error ", e); } catch (Exception ex) { log.error("Exception caught in Generate Excel File", ex); throw ex; } finally { try { if (rs != null) { rs.close(); } if (st != null) { st.close(); } if (cn != null) { cn.close(); } if (fileOut != null) { fileOut.close(); } } catch (Exception e) { log.debug("Unable to perform clean up due to the following error ", e); } } }
From source file:hr.restart.swing.raExtendedTable.java
License:Apache License
public void exportToXLS(File output) { String fname = output.getName(); if (!fname.endsWith("xls") && fname.indexOf('.') < 0) output = new File(output.getParentFile(), fname + ".xls"); System.out.println("exporting to XLS"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat df = wb.createDataFormat(); String fontFamily = frmParam.getParam("sisfun", "excelFont", "Arial", "Font za export u Excel", true); if (fontFamily == null || fontFamily.length() == 0) fontFamily = "Arial"; int fontSize = 10; String fontSizeTx = frmParam.getParam("sisfun", "excelFontSize", "10", "Veliina fonta za export u Excel, u tokama", true); if (fontSizeTx != null && Aus.getNumber(fontSizeTx) >= 6 && Aus.getNumber(fontSizeTx) <= 72) fontSize = Aus.getNumber(fontSizeTx); HSSFFont font = wb.createFont();//from w w w.j a va 2 s.c o m font.setFontName(fontFamily); font.setFontHeightInPoints((short) fontSize); HSSFFont fontTitle = wb.createFont(); fontTitle.setFontName(fontFamily); fontTitle.setFontHeightInPoints((short) (fontSize * 1.8)); HSSFFont fontSubtitle = wb.createFont(); fontSubtitle.setFontName(fontFamily); fontSubtitle.setFontHeightInPoints((short) (fontSize * 1.5)); HSSFCellStyle csHeader = wb.createCellStyle(); csHeader.setFont(font); csHeader.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); csHeader.setDataFormat(df.getFormat("text")); HSSFCellStyle csFooter = wb.createCellStyle(); csFooter.setFont(font); csFooter.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooter.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooter.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooter.setAlignment(HSSFCellStyle.ALIGN_LEFT); csFooter.setDataFormat(df.getFormat("text")); HSSFCellStyle csFooterNum2 = wb.createCellStyle(); csFooterNum2.setFont(font); csFooterNum2.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooterNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csFooterNum2.setDataFormat(df.getFormat("#,##0.00")); HSSFCellStyle csFooterNum = wb.createCellStyle(); csFooterNum.setFont(font); csFooterNum.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooterNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csFooterNum.setDataFormat(df.getFormat("#")); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setFont(font); csDate.setAlignment(HSSFCellStyle.ALIGN_CENTER); csDate.setDataFormat(df.getFormat("dd.mm.yyyy")); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setFont(fontTitle); csTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); csTitle.setDataFormat(df.getFormat("text")); HSSFCellStyle csSubtitle = wb.createCellStyle(); csSubtitle.setFont(fontSubtitle); csSubtitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); csSubtitle.setDataFormat(df.getFormat("text")); HSSFCellStyle csNum2 = wb.createCellStyle(); csNum2.setFont(font); csNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum2.setDataFormat(df.getFormat("#,##0.00")); HSSFCellStyle csNum3 = wb.createCellStyle(); csNum3.setFont(font); csNum3.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum3.setDataFormat(df.getFormat("#,##0.000")); HSSFCellStyle csNum = wb.createCellStyle(); csNum.setFont(font); csNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum.setDataFormat(df.getFormat("#.#")); HSSFCellStyle csInt = wb.createCellStyle(); csInt.setFont(font); csInt.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csInt.setDataFormat(df.getFormat("#")); HSSFCellStyle csText = wb.createCellStyle(); csText.setFont(font); csText.setAlignment(HSSFCellStyle.ALIGN_LEFT); csText.setDataFormat(df.getFormat("text")); repDynamicProvider dp = repDynamicProvider.getInstance(); boolean sums = dp.hasSumRow(); int cols = getColumnModel().getColumnCount(); int rows = getRowCount() - (sums ? 1 : 0); HSSFSheet sheet = wb.createSheet(); HSSFRow row; HSSFCell cell; short cRow = 0; // header and title row = sheet.createRow(cRow = 0); cell = row.createCell((short) 0); cell.setCellStyle(csText); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(repMemo.getrepMemo().getOneLine()); row = sheet.createRow(++cRow); cell = row.createCell((short) 0); cell.setCellStyle(csTitle); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); if (dp.getTitle().length() > 0) cell.setCellValue(dp.getTitle().substring(1)); else cell.setCellValue(""); if (dp.getSubtitle().length() > 0) { row = sheet.createRow(++cRow); cell = row.createCell((short) 0); cell.setCellStyle(csSubtitle); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(dp.getSubtitle().substring(1)); } for (short c = 0; c < cols; c++) sheet.setColumnWidth(c, (short) (getColumnModel().getColumn(c).getWidth() * 40)); // sections row = sheet.createRow(++cRow); int secRow = 0, firstRow = 0; for (int r = 0; r < rows; r++) { if (r == 0) { row = sheet.createRow(++cRow); for (short c = 0; c < cols; c++) { cell = row.createCell(c); cell.setCellStyle(csHeader); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(getColumnModel().getColumn(c).getHeaderValue().toString()); } if (firstRow == 0) firstRow = cRow; secRow = cRow; } row = sheet.createRow(++cRow); for (short c = 0; c < cols; c++) { cell = row.createCell(c); Object o = getValueAt(r, c); if (o instanceof Number) { if (o instanceof BigDecimal) { BigDecimal bd = (BigDecimal) o; if (bd.scale() == 2) cell.setCellStyle(csNum2); else if (bd.scale() == 3) cell.setCellStyle(csNum3); else cell.setCellStyle(csNum); cell.setCellValue(bd.doubleValue()); } else { String t = dp.getValueAt(r, c); if (Aus.isDigit(t)) { cell.setCellStyle(csInt); cell.setCellValue(((Number) o).doubleValue()); } else { cell.setCellStyle(csText); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(t); } } } else if (o instanceof Date) { cell.setCellStyle(csDate); cell.setCellValue((Date) o); } else { cell.setCellStyle(csText); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(dp.getValueAt(r, c)); } } } System.out.println("sums " + sums); if (sums) { int non = 0; while (non < cols && dp.getValueAt(getRowCount() - 1, non).trim().length() == 0) ++non; if (non < cols) { System.out.println("creating row " + non); row = sheet.createRow(++cRow); if (non > 0) { cell = row.createCell((short) 0); cell.setCellStyle(csFooter); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue("U K U P N O"); if (non > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (non - 1))); } for (short c = (short) non; c < cols; c++) { cell = row.createCell(c); Object o = getValueAt(rows - 1, c); if ((o instanceof BigDecimal) && ((BigDecimal) o).scale() == 2) cell.setCellStyle(csFooterNum2); else cell.setCellStyle(csFooterNum); if (dp.getValueAt(getRowCount() - 1, c).trim().length() != 0) cell.setCellFormula("SUBTOTAL(9;" + xlsRange(firstRow + 1, cRow, c) + ")"); else cell.setCellValue(""); } } } FileOutputStream out = null; try { out = new FileOutputStream(output); wb.write(out); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (out != null) try { out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font);/*from www .j a v a 2 s. c om*/ cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setVerticalAlignment((short) 3); // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); return cellStyle; }
From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); font.setBold(true);/*from w w w.ja v a 2s.co m*/ cellStyle.setFont(font); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_40_PERCENT.getIndex()); cellStyle.setAlignment(HorizontalAlignment.CENTER); return cellStyle; }
From source file:matriz.core.GerarXLS.java
public static void expExcel(String nomeArquivo, String demanda, List<LineMatriz> linhastabela) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet firstSheet = workbook.createSheet("Aba1"); HSSFFont fontBranca = workbook.createFont(); fontBranca.setFontHeightInPoints((short) 16); fontBranca.setColor(HSSFColor.WHITE.index); fontBranca.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFFont fontBrancaMenor = workbook.createFont(); fontBrancaMenor.setFontHeightInPoints((short) 10); fontBrancaMenor.setColor(HSSFColor.WHITE.index); fontBrancaMenor.setBoldweight(Font.BOLDWEIGHT_BOLD); FileOutputStream fos = null;//from ww w . ja v a 2 s. c o m nomeArquivo = nomeArquivo + "/Matriz de Rastreabilidade " + demanda + ".xls"; try { fos = new FileOutputStream(new File(nomeArquivo)); HSSFRow rowa = firstSheet.createRow(0); rowa.createCell(1); // criar titulo HSSFRow row0 = firstSheet.createRow(1); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(fontBranca); style.setFillForegroundColor(HSSFColor.DARK_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); row0.createCell(0).setCellValue("Matriz de classes alteradas na " + demanda); row0.getCell(0).setCellStyle(style); //nome das colunas HSSFRow row1 = firstSheet.createRow(2); HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.DARK_BLUE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); style2.setFont(fontBrancaMenor); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); row1.createCell(0).setCellValue("Sistema"); row1.createCell(1).setCellValue("Mdulo"); row1.createCell(2).setCellValue("Diretrio"); row1.createCell(3).setCellValue("Arquivo"); row1.createCell(4).setCellValue("Ambiente"); row1.createCell(5).setCellValue("Tipo do Arquivo"); row1.createCell(6).setCellValue("Ao"); row1.createCell(7).setCellValue("Data"); row1.createCell(8).setCellValue("Reviso SVN"); row1.createCell(9).setCellValue("Autor"); row1.createCell(10).setCellValue("Motivo da alterao"); row1.getCell(0).setCellStyle(style2); row1.getCell(1).setCellStyle(style2); row1.getCell(2).setCellStyle(style2); row1.getCell(3).setCellStyle(style2); row1.getCell(4).setCellStyle(style2); row1.getCell(5).setCellStyle(style2); row1.getCell(6).setCellStyle(style2); row1.getCell(7).setCellStyle(style2); row1.getCell(8).setCellStyle(style2); row1.getCell(9).setCellStyle(style2); row1.getCell(10).setCellStyle(style2); firstSheet.setColumnWidth(0, 10000); firstSheet.setColumnWidth(1, 9000); firstSheet.setColumnWidth(2, 10000); firstSheet.setColumnWidth(3, 20000); firstSheet.setColumnWidth(4, 10000); firstSheet.setColumnWidth(5, 4000); firstSheet.setColumnWidth(6, 4000); firstSheet.setColumnWidth(7, 4000); firstSheet.setColumnWidth(8, 4000); firstSheet.setColumnWidth(9, 6000); firstSheet.setColumnWidth(10, 10000); HSSFCellStyle style3 = workbook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderTop(HSSFCellStyle.BORDER_THIN); style3.setBorderLeft(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); int i = 3; for (LineMatriz linha : linhastabela) { HSSFRow row = firstSheet.createRow(i); row.createCell(0).setCellValue(linha.getSistema()); row.createCell(1).setCellValue(linha.getModulo()); row.createCell(2).setCellValue(linha.getDiretorio()); row.createCell(3).setCellValue(linha.getArquivo()); row.createCell(4).setCellValue(linha.getAmbiente()); row.createCell(5).setCellValue(linha.getTipoArquivo()); row.createCell(6).setCellValue(linha.getAcao()); row.createCell(7).setCellValue(linha.getData()); row.createCell(8).setCellValue(linha.getRevisao()); row.createCell(9).setCellValue(linha.getAutor()); row.createCell(10).setCellValue(linha.getMotivo()); row.getCell(0).setCellStyle(style3); row.getCell(1).setCellStyle(style3); row.getCell(2).setCellStyle(style3); row.getCell(3).setCellStyle(style3); row.getCell(4).setCellStyle(style3); row.getCell(5).setCellStyle(style3); row.getCell(6).setCellStyle(style3); row.getCell(7).setCellStyle(style3); row.getCell(8).setCellStyle(style3); row.getCell(9).setCellStyle(style3); row.getCell(10).setCellStyle(style3); i++; } // fim do for firstSheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based) 1, //last row (0-based) 0, //first column (0-based) 10 //last column (0-based) )); workbook.write(fos); JOptionPane.showMessageDialog(null, "Arquivo criado em " + nomeArquivo); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, e.getMessage()); System.out.println("Erro ao exportar arquivo"); } finally { try { fos.flush(); fos.close(); } catch (Exception e) { JOptionPane.showMessageDialog(null, e.getMessage()); } } }
From source file:neg.JRViewerComercial.java
License:Open Source License
private void btnExcelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnExcelActionPerformed String queryInf = this.jasperPrint.getProperty("query"); try {//from www . j a va2 s. c om // Se crea el libro excel HSSFWorkbook libro = new HSSFWorkbook(); //Se crea la hoja HSSFSheet hoja = libro.createSheet("Informe"); //Numero de fila de la hoja Excel int num_fila = 1; crearCabeceraHojaExcel(libro, hoja); HSSFCellStyle cs2 = libro.createCellStyle(); cs2.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBottomBorderColor(HSSFColor.BLACK.index); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setLeftBorderColor(HSSFColor.BLACK.index); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); cs2.setRightBorderColor(HSSFColor.BLACK.index); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setTopBorderColor(HSSFColor.BLACK.index); HSSFCellStyle cs3 = libro.createCellStyle(); HSSFDataFormat format = libro.createDataFormat(); cs3.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs3.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs3.setBottomBorderColor(HSSFColor.BLACK.index); cs3.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs3.setLeftBorderColor(HSSFColor.BLACK.index); cs3.setBorderRight(HSSFCellStyle.BORDER_THIN); cs3.setRightBorderColor(HSSFColor.BLACK.index); cs3.setBorderTop(HSSFCellStyle.BORDER_THIN); cs3.setTopBorderColor(HSSFColor.BLACK.index); cs3.setDataFormat(format.getFormat("#,##0")); //Datos ResultSet rs = CSDesktop.datos.select(queryInf); crearFilaHojaExcel(libro, hoja, num_fila, rs, cs2, cs3); FileOutputStream elFichero = null; elFichero = new FileOutputStream("c:\\AplicacionCarSet\\informe_comercial.xls"); libro.write(elFichero); elFichero.close(); elFichero.flush(); String property = "java.io.tmpdir"; String tempDir = System.getProperty(property); System.out.println("OS current temporary directory is " + tempDir); String file = new String("C:\\AplicacionCarSet\\informe_comercial.xls"); Process p = Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " + file); } catch (SQLException ex) { Logger.getLogger(CSResultBuscarPedidoNew.class.getName()).log(Level.SEVERE, null, ex); } catch (UnknownHostException ex) { Logger.getLogger(CSResultBuscarPedidoNew.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CSResultBuscarPedidoNew.class.getName()).log(Level.SEVERE, null, ex); } //System.out.println("Vamoooooos: "+queryInf); }