List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write
private void write(POIFSFileSystem fs) throws IOException
From source file:edu.corgi.uco.Secretary.java
public void downloadReportData() throws IOException { if (!Students.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row0 = sheet.createRow(0); HSSFCell cell0 = row0.createCell(0); cell0.setCellValue("First Name"); HSSFCell cell01 = row0.createCell(1); cell01.setCellValue("Last Name"); HSSFCell cell02 = row0.createCell(2); cell02.setCellValue("Email"); HSSFCell cell03 = row0.createCell(3); cell03.setCellValue("UCO ID"); HSSFCell cell04 = row0.createCell(4); cell04.setCellValue("Appointment Date"); for (int x = 0; x < Students.size(); x++) { HSSFRow row = sheet.createRow(x + 1); HSSFCell cell = row.createCell(0); cell.setCellValue(Students.get(x).getStudentFirstName()); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(Students.get(x).getStudentLastName()); HSSFCell cell2 = row.createCell(2); cell2.setCellValue(Students.get(x).getStudentEmail()); HSSFCell cell3 = row.createCell(3); cell3.setCellValue(Students.get(x).getUcoID()); HSSFCell cell4 = row.createCell(4); cell4.setCellValue(Students.get(x).getMeetingDate().toString()); }/*from w w w . jav a 2s.c o m*/ FacesContext facesContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = facesContext.getExternalContext(); externalContext.setResponseContentType("application/vnd.ms-excel"); externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"report.xls\""); workbook.write(externalContext.getResponseOutputStream()); facesContext.responseComplete(); } }
From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java
License:BSD License
public void testGenerateEXCEL() throws Exception { HealthcareSite hcs = healthcareSiteDao.getById(1100); DateFormat format = new SimpleDateFormat("MM/dd/yyyy"); Date startDate = (Date) format.parse("01/11/1990"); Date endDate = (Date) format.parse("01/01/2007"); String grantNumber = "GRANT-NO 1232"; Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate); String reportingSource = healthcareSiteDao.getById(1000).getName(); summary3Report.setReportingSource(reportingSource); summaryReportService.buildSummary3Report(summary3Report); String xmlString = summaryReportService.generateXML(summary3Report); File file = new File("testReport.xml"); FileWriter fileWriter = new FileWriter(file); fileWriter.write(xmlString);//from ww w . j a v a 2 s .com fileWriter.flush(); fileWriter.close(); System.out.println(xmlString); // creating the workbook and the spreadsheet try { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); InputStream inputStream = new FileInputStream(file); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls"); FileOutputStream output = new FileOutputStream(outputFile); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
From source file:edu.duke.cabig.c3pr.web.report.AdvanceSearchResultsExportController.java
License:BSD License
private void createExcelFile(List<AdvancedSearchRow> searchResultsRowList, List<ViewColumn> resultsViewColumnList) throws Exception { String tempDir = System.getProperty("java.io.tmpdir"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Search Results"); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow((short) 0); int viewColumnCount = 0; for (ViewColumn viewColumn : resultsViewColumnList) { row.createCell((short) viewColumnCount).setCellValue(viewColumn.getColumnTitle()); viewColumnCount++;//from w w w . j a va2s.com } int rowCount = 1; for (AdvancedSearchRow searchRow : searchResultsRowList) { int columnCount = 0; row = sheet.createRow((short) rowCount++); for (AdvancedSearchColumn searchColumn : searchRow.getColumnList()) { if (searchColumn.getValue() != null) row.createCell((short) columnCount++).setCellValue(searchColumn.getValue().toString()); else row.createCell((short) columnCount++).setCellValue(""); } } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(tempDir + File.separator + XLS_SEARCH_RESULTS_FILENAME); wb.write(fileOut); fileOut.close(); }
From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java
License:BSD License
public void generateExcel(String summary3ReportXml, String file) throws Exception { // creating the workbook and the spreadsheet try {//from w ww . ja va2 s .com DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); String fileName = ""; fileName = file; InputStream inputStream = new FileInputStream((fileName + ".xml")); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel"))); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
From source file:edu.jhu.cvrg.services.nodeDataService.DataStaging.java
License:Open Source License
/** Service to make final destination directories and transfer the file into it, via routeToFolder().<BR/> * Assumes that the file was already transfered to the ftp area. * /*from ww w.j av a 2 s . com*/ * @param param0 OMElement containing the parameters:<BR/> * userId, subjectId, fileName, ftpHost, ftpUser, ftpPassword, bExposure * @return ??? always returns SUCCESS ??? * @throws Exception */ @SuppressWarnings("deprecation") public org.apache.axiom.om.OMElement consolidateCsvs(org.apache.axiom.om.OMElement param0) throws Exception { System.out.println("# consolidateCsvs() #"); OMFactory fac = OMAbstractFactory.getOMFactory(); OMNamespace omNs = fac.createOMNamespace("http://www.cvrgrid.org/nodeDataService/", "nodeDataService"); OMElement stageTransferredDataStatus = fac.createOMElement("stageTransferredData", omNs); Iterator iterator = param0.getChildren(); String userId = ((OMElement) iterator.next()).getText(); String chesSubjectIds = ((OMElement) iterator.next()).getText(); String bergSubjectIds = ((OMElement) iterator.next()).getText(); String chesFileNames = ((OMElement) iterator.next()).getText(); String bergFileNames = ((OMElement) iterator.next()).getText(); Boolean isPublic = new Boolean(((OMElement) iterator.next()).getText()).booleanValue(); String ftpHost = ((OMElement) iterator.next()).getText(); String ftpUser = ((OMElement) iterator.next()).getText(); String ftpPassword = ((OMElement) iterator.next()).getText(); String service = ((OMElement) iterator.next()).getText(); long logindatetime = new Long(((OMElement) iterator.next()).getText()).longValue(); String qrsScoreSubjectIds = ((OMElement) iterator.next()).getText(); String qrsScoreFileNames = ((OMElement) iterator.next()).getText(); try { verbose = new Boolean(((OMElement) iterator.next()).getText()).booleanValue(); } catch (NoSuchElementException nseEx) { System.out.println( "'iteration has no more elements' Exception while parsing 'verbose' in consolidateCsvs()"); } String publicOrPrivate = null; if (isPublic) { publicOrPrivate = "public"; } else { publicOrPrivate = "private"; } debugPrintln("userId: \"" + userId + "\""); debugPrintln("chesSubjectIds: \"" + chesSubjectIds + "\""); debugPrintln("bergSubjectIds: \"" + bergSubjectIds + "\""); debugPrintln("chesFileNames: \"" + chesFileNames + "\""); debugPrintln("bergFileNames: \"" + bergFileNames + "\""); debugPrintln("isPublic: \"" + isPublic + "\""); debugPrintln("ftpHost: \"" + ftpHost + "\""); debugPrintln("ftpUser: \"" + ftpUser + "\""); debugPrintln("ftpPassword: \"" + ftpPassword + "\""); debugPrintln("service: \"" + service + "\""); debugPrintln("logindatetime: \"" + logindatetime + "\""); debugPrintln("qrsScoreSubjectIds: \"" + qrsScoreSubjectIds + "\""); debugPrintln("qrsScoreFileNames: \"" + qrsScoreFileNames + "\""); debugPrintln("verbose: \"" + verbose + "\""); String timestamp = utils.generateTimeStamp(); String allResultsFileName = "allResultsFiles_" + userId + "_" + timestamp + ".xls"; String localAllResultsDirectory = findRelativePath("./", localWebPageRoot + sep + downloadDirectory); // put files to be allResults spreadsheet in here. String urlAllResultsDirectory = urlWebPageRoot + downloadDirectory; // URL as seen from the internet that is equivalent to localOutputDirectory above. e.g. http://icmv058.icm.jhu.edu:8080/download try { ApacheCommonsFtpWrapper ftpClient = new ApacheCommonsFtpWrapper(ftpHost, ftpUser, ftpPassword); ftpClient.verbose = verbose; System.out.println(userId + " Ches File Names: " + chesFileNames + "Berg File Names " + bergFileNames); System.out.println(userId + " Ches Subjects: " + chesSubjectIds + "Berg Subjects " + bergSubjectIds); FileOutputStream out = new FileOutputStream(localAllResultsDirectory + sep + allResultsFileName); HSSFWorkbook wbo = new HSSFWorkbook(); int numSheets = 0; if (!chesSubjectIds.equals("")) { HSSFSheet chesSheet = wbo.createSheet(); chesSheet = consolidateAlgorithmFiles(0, chesSheet, chesSubjectIds, userId, chesFileNames, isPublic, ftpClient); wbo.setSheetName(numSheets, "Chesnokov Results"); ++numSheets; } if (!bergSubjectIds.equals("")) { HSSFSheet bergerSheet = wbo.createSheet(); bergerSheet = consolidateAlgorithmFiles(1, bergerSheet, bergSubjectIds, userId, bergFileNames, isPublic, ftpClient); wbo.setSheetName(numSheets, "Berger Results"); ++numSheets; } if (!qrsScoreSubjectIds.equals("")) { HSSFSheet qrsScoreSheet = wbo.createSheet(); qrsScoreSheet = consolidateAlgorithmFiles(1, qrsScoreSheet, qrsScoreSubjectIds, userId, qrsScoreFileNames, isPublic, ftpClient); wbo.setSheetName(numSheets, "QRS-Score Results"); ++numSheets; } wbo.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } debugPrintln(bergSubjectIds); debugPrintln(chesSubjectIds); debugPrintln(qrsScoreSubjectIds); stageTransferredDataStatus.addChild(fac.createOMText(urlAllResultsDirectory + sep + allResultsFileName)); debugPrintln("Returning results URL: \"" + urlAllResultsDirectory + sep + allResultsFileName + "\""); return stageTransferredDataStatus; }
From source file:edu.jhu.cvrg.services.nodeDataService.DataStaging.java
License:Open Source License
/** Service to make final destination directories and transfer the file into it, via routeToFolder().<BR/> * Assumes that the file was already transfered to the ftp area. * /*from w w w . java 2 s . com*/ * @param param0 OMElement containing the parameters:<BR/> * userId, subjectId, fileName, ftpHost, ftpUser, ftpPassword, bExposure * @return ??? always returns SUCCESS ??? * @throws Exception */ @SuppressWarnings("deprecation") public org.apache.axiom.om.OMElement consolidateCsvsForWaveform(org.apache.axiom.om.OMElement param0) throws Exception { System.out.println("# consolidateCsvsForWaveform() #"); OMFactory fac = OMAbstractFactory.getOMFactory(); OMNamespace omNs = fac.createOMNamespace("http://www.cvrgrid.org/nodeDataService/", "nodeDataService"); OMElement stageTransferredDataStatus = fac.createOMElement("stageTransferredData", omNs); Iterator iterator = param0.getChildren(); String userId = ((OMElement) iterator.next()).getText(); String chesSubjectIds = ((OMElement) iterator.next()).getText(); String bergSubjectIds = ((OMElement) iterator.next()).getText(); String chesFileNames = ((OMElement) iterator.next()).getText(); String bergFileNames = ((OMElement) iterator.next()).getText(); Boolean isPublic = new Boolean(((OMElement) iterator.next()).getText()).booleanValue(); String ftpHost = ((OMElement) iterator.next()).getText(); String ftpUser = ((OMElement) iterator.next()).getText(); String ftpPassword = ((OMElement) iterator.next()).getText(); String service = ((OMElement) iterator.next()).getText(); long logindatetime = new Long(((OMElement) iterator.next()).getText()).longValue(); String qrsScoreSubjectIds = ((OMElement) iterator.next()).getText(); String qrsScoreFileNames = ((OMElement) iterator.next()).getText(); try { verbose = new Boolean(((OMElement) iterator.next()).getText()).booleanValue(); } catch (NoSuchElementException nseEx) { System.out.println( "'iteration has no more elements' Exception while parsing 'verbose' in consolidateCsvs()"); } String publicOrPrivate = null; if (isPublic) { publicOrPrivate = "public"; } else { publicOrPrivate = "private"; } debugPrintln("userId: \"" + userId + "\""); debugPrintln("chesSubjectIds: \"" + chesSubjectIds + "\""); debugPrintln("bergSubjectIds: \"" + bergSubjectIds + "\""); debugPrintln("chesFileNames: \"" + chesFileNames + "\""); debugPrintln("bergFileNames: \"" + bergFileNames + "\""); debugPrintln("isPublic: \"" + isPublic + "\""); debugPrintln("ftpHost: \"" + ftpHost + "\""); debugPrintln("ftpUser: \"" + ftpUser + "\""); debugPrintln("ftpPassword: \"" + ftpPassword + "\""); debugPrintln("service: \"" + service + "\""); debugPrintln("logindatetime: \"" + logindatetime + "\""); debugPrintln("qrsScoreSubjectIds: \"" + qrsScoreSubjectIds + "\""); debugPrintln("qrsScoreFileNames: \"" + qrsScoreFileNames + "\""); debugPrintln("verbose: \"" + verbose + "\""); String timestamp = utils.generateTimeStamp(); String allResultsFileName = "allResultsFiles_" + userId + "_" + timestamp + ".xls"; String localAllResultsDirectory = localFtpRoot; // put files to be allResults spreadsheet in here. try { ApacheCommonsFtpWrapper ftpClient = new ApacheCommonsFtpWrapper(ftpHost, ftpUser, ftpPassword); ftpClient.verbose = verbose; System.out.println(userId + " Ches File Names: " + chesFileNames + "Berg File Names " + bergFileNames); System.out.println(userId + " Ches Subjects: " + chesSubjectIds + "Berg Subjects " + bergSubjectIds); FileOutputStream out = new FileOutputStream(localAllResultsDirectory + sep + allResultsFileName); HSSFWorkbook wbo = new HSSFWorkbook(); int numSheets = 0; if (!chesSubjectIds.equals("")) { HSSFSheet chesSheet = wbo.createSheet(); chesSheet = consolidateAlgorithmFiles(0, chesSheet, chesSubjectIds, userId, chesFileNames, isPublic, ftpClient); wbo.setSheetName(numSheets, "Chesnokov Results"); ++numSheets; } if (!bergSubjectIds.equals("")) { HSSFSheet bergerSheet = wbo.createSheet(); bergerSheet = consolidateAlgorithmFiles(1, bergerSheet, bergSubjectIds, userId, bergFileNames, isPublic, ftpClient); wbo.setSheetName(numSheets, "Berger Results"); ++numSheets; } if (!qrsScoreSubjectIds.equals("")) { HSSFSheet qrsScoreSheet = wbo.createSheet(); qrsScoreSheet = consolidateAlgorithmFiles(1, qrsScoreSheet, qrsScoreSubjectIds, userId, qrsScoreFileNames, isPublic, ftpClient); wbo.setSheetName(numSheets, "QRS-Score Results"); ++numSheets; } wbo.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } debugPrintln(bergSubjectIds); debugPrintln(chesSubjectIds); debugPrintln(qrsScoreSubjectIds); stageTransferredDataStatus.addChild(fac.createOMText(localAllResultsDirectory + sep + allResultsFileName)); debugPrintln("Returning results path: \"" + localAllResultsDirectory + sep + allResultsFileName + "\""); return stageTransferredDataStatus; }
From source file:edu.ku.brc.dbsupport.TableModel2Excel.java
License:Open Source License
/** * Converts a tableModel to an Excel Spreadsheet. * @param toFile the file object to write it to. * @param title the title of the spreadsheet. * @param tableModel the table model//from w w w .jav a2 s . c o m * @return a file to a spreadsheet */ public static File convertToExcel(final File toFile, final String title, final TableModel tableModel) { if (toFile == null) { UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_EXISTS", toFile != null ? toFile.getAbsolutePath() : ""); return null; } if (tableModel != null && tableModel.getRowCount() > 0) { try { // create a new file FileOutputStream out; try { out = new FileOutputStream(toFile); } catch (FileNotFoundException ex) { UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_WRITE", toFile != null ? toFile.getAbsolutePath() : ""); return null; } // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet sheet = wb.createSheet(); // declare a row object reference // Header Captions HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // create a style for the header cell HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setFont(headerFont); setBordersOnStyle(headerStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN); short numColumns = (short) tableModel.getColumnCount(); HSSFRow headerRow = sheet.createRow(0); for (int i = 0; i < numColumns; i++) { HSSFCell headerCell = headerRow.createCell((short) i); headerCell.setCellStyle(headerStyle); //add the date to the header cell headerCell.setCellValue(tableModel.getColumnName(i)); sheet.setColumnWidth((short) i, (short) (30 * 256)); } //-------------------------- // done header //-------------------------- // create 3 cell styles HSSFCellStyle oddCellStyle = wb.createCellStyle(); HSSFCellStyle evenCellStyle = wb.createCellStyle(); setBordersOnStyle(oddCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN); setBordersOnStyle(evenCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN); // create 2 fonts objects HSSFFont cellFont = wb.createFont(); //set font 1 to 12 point type cellFont.setFontHeightInPoints((short) 11); oddCellStyle.setFont(cellFont); evenCellStyle.setFont(cellFont); evenCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); oddCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); oddCellStyle.setFillForegroundColor(HSSFColor.WHITE.index); evenCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); oddCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); evenCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // set the sheet name to HSSF Test wb.setSheetName(0, title); for (short rownum = 0; rownum < (short) tableModel.getRowCount(); rownum++) { // create a row HSSFRow row = sheet.createRow(rownum + 1); for (short cellnum = (short) 0; cellnum < numColumns; cellnum++) { // create a numeric cell HSSFCell cell = row.createCell(cellnum); Object dataVal = tableModel.getValueAt(rownum, cellnum); cell.setCellValue(dataVal != null ? dataVal.toString() : ""); // on every other row cell.setCellStyle((rownum % 2) == 0 ? evenCellStyle : oddCellStyle); } } // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); } catch (Exception ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(TableModel2Excel.class, ex); log.error("convertToExcel", ex); //$NON-NLS-1$ } } return toFile; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSExport.java
License:Open Source License
public void writeData(final List<?> data) throws Exception { HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet workSheet = workBook.createSheet(); DocumentSummaryInformation mappings = null; int rowNum = 0; if (config.getFirstRowHasHeaders() && !config.getAppendData()) { writeHeaders(workSheet);/* w w w .java2s .c o m*/ rowNum++; String[] headers = config.getHeaders(); for (int i = 0; i < headers.length; i++) { workSheet.setColumnWidth(i, StringUtils.isNotEmpty(headers[i]) ? (256 * headers[i].length()) : 2560); } WorkbenchTemplate wbTemplate = null; if (data.get(0) instanceof WorkbenchTemplate) { wbTemplate = (WorkbenchTemplate) data.get(0); } else { wbTemplate = ((WorkbenchRow) data.get(0)).getWorkbench().getWorkbenchTemplate(); } mappings = writeMappings(wbTemplate); } //assuming data is never empty. boolean hasTemplate = data.get(0) instanceof WorkbenchTemplate; boolean hasRows = hasTemplate ? data.size() > 1 : data.size() > 0; if (hasRows) { int[] disciplinees; WorkbenchRow wbRow = (WorkbenchRow) data.get(hasTemplate ? 1 : 0); Workbench workBench = wbRow.getWorkbench(); WorkbenchTemplate template = workBench.getWorkbenchTemplate(); int numCols = template.getWorkbenchTemplateMappingItems().size(); int geoDataCol = -1; Vector<Integer> imgCols = new Vector<Integer>(); disciplinees = bldColTypes(template); for (Object rowObj : data) { if (rowObj instanceof WorkbenchTemplate) { continue; } WorkbenchRow row = (WorkbenchRow) rowObj; HSSFRow hssfRow = workSheet.createRow(rowNum++); int colNum; boolean rowHasGeoData = false; for (colNum = 0; colNum < numCols; colNum++) { HSSFCell cell = hssfRow.createCell(colNum); cell.setCellType(disciplinees[colNum]); setCellValue(cell, row.getData(colNum)); } if (row.getBioGeomancerResults() != null && !row.getBioGeomancerResults().equals("")) { geoDataCol = colNum; rowHasGeoData = true; HSSFCell cell = hssfRow.createCell(colNum++); cell.setCellType(HSSFCell.CELL_TYPE_STRING); setCellValue(cell, row.getBioGeomancerResults()); } // if (row.getCardImage() != null) if (row.getRowImage(0) != null) { if (!rowHasGeoData) { colNum++; } int imgIdx = 0; WorkbenchRowImage img = row.getRowImage(imgIdx++); while (img != null) { if (imgCols.indexOf(colNum) < 0) { imgCols.add(colNum); } HSSFCell cell = hssfRow.createCell(colNum++); cell.setCellType(HSSFCell.CELL_TYPE_STRING); String cellValue = img.getCardImageFullPath(); String attachToTbl = img.getAttachToTableName(); if (attachToTbl != null) { cellValue += "\t" + attachToTbl; } setCellValue(cell, cellValue); img = row.getRowImage(imgIdx++); } } } if (imgCols.size() > 0 || geoDataCol != -1) { writeExtraHeaders(workSheet, imgCols, geoDataCol); } } try { // Write the workbook File file = new File(getConfig().getFileName()); if (file.canWrite() || (!file.exists() && file.createNewFile())) { FileOutputStream fos = new FileOutputStream(file); workBook.write(fos); fos.close(); //Now write the mappings. //NOT (hopefully) the best way to write the mappings, but (sadly) the easiest way. //May need to do this another way if this slows performance for big wbs. if (mappings != null) { InputStream is = new FileInputStream(file); POIFSFileSystem poifs = new POIFSFileSystem(is); is.close(); mappings.write(poifs.getRoot(), DocumentSummaryInformation.DEFAULT_STREAM_NAME); fos = new FileOutputStream(file); poifs.writeFilesystem(fos); fos.close(); } } else { UIRegistry.displayErrorDlgLocalized("WB_EXPORT_PERM_ERR"); } } catch (Exception e) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSExport.class, e); throw (e); } }
From source file:edu.sharif.ce.dml.mobisim.diagram.control.EvaluationDiagramUsingHandler.java
License:Open Source License
public void use(EvaluationRecord[] data) { double[] evalDatas = new double[data.length]; try {/*from w w w . jav a 2 s . c om*/ Integer evaluationIndex = labelNumber.get(evaluationColumn); for (int i = 0; i < data.length; i++) { evalDatas[i] = Double.parseDouble(data[i].getValueAt(evaluationIndex)); } } catch (NumberFormatException e) { DevelopmentLogger.logger .debug("Evaluation data parse error. Evaluation column values should be \"Double Parsable\""); } List<ExtractedEvaluationData> extractedEvaluationDataList = new ArrayList<ExtractedEvaluationData>( data.length); //average evaluationColumns Data datas that important+variable+rowName are equals { int rowNameIndex = labelNumber.get(rowNameColumn); List<Integer> variablesIndex = new ArrayList<Integer>(variableColumns.size()); for (String variableColumn : variableColumns) { variablesIndex.add(labelNumber.get(variableColumn)); } List<Integer> importantIndexes = new ArrayList<Integer>(importantColumns.size()); for (String importantColumn : importantColumns) { importantIndexes.add(labelNumber.get(importantColumn)); } for (int i = 0; i < data.length; i++) { EvaluationRecord evaluationRecord = data[i]; List<String> variablesData = new ArrayList<String>(variablesIndex.size()); for (Integer index : variablesIndex) { variablesData.add(evaluationRecord.getValueAt(index)); } List<String> importantData = new ArrayList<String>(importantIndexes.size()); for (Integer index : importantIndexes) { importantData.add(evaluationRecord.getValueAt(index)); } ExtractedEvaluationData eData = new ExtractedEvaluationData( evaluationRecord.getValueAt(rowNameIndex), evalDatas[i], variablesData, importantData); int eIndex = extractedEvaluationDataList.indexOf(eData); if (eIndex < 0) { extractedEvaluationDataList.add(eData); } else { extractedEvaluationDataList.get(eIndex).addValue(eData); } } } //create model objects TreeSet<List<String>> importantVariablesData = new TreeSet<List<String>>(new VariableDataComparator()); List<EvaluationTable> tables = new ArrayList<EvaluationTable>(importantVariablesData.size()); { //create table columns Strings, so should find existed variable columns combinations EvaluationTable.setRowNameColumn(rowNameColumn); EvaluationTable.setImportantVariables(new TreeSet<String>(importantColumns)); TreeSet<List<String>> columns = new TreeSet<List<String>>(new VariableDataComparator()); for (ExtractedEvaluationData evaluationData : extractedEvaluationDataList) { columns.add(evaluationData.variablesData); importantVariablesData.add(evaluationData.importantDatas); } List<List<String>> sortedColumns = new ArrayList<List<String>>(columns.size()); for (List<String> column : columns) { sortedColumns.add(column); } StringBuffer sb = new StringBuffer(); for (String variableName : variableColumns) { sb.append(variableName).append(EvaluationTable.SEPARATOR); } if (variableColumns.size() > 0) { sb.delete(sb.lastIndexOf(EvaluationTable.SEPARATOR), sb.length()); } String variableNames = sb.toString(); for (List<String> importantStrings : importantVariablesData) { //each should be a Evaluation Table EvaluationTable evaluationTable = new EvaluationTable(); evaluationTable.setColumns(sortedColumns); evaluationTable.setOtherVariablesValue(importantStrings); evaluationTable.setVariableNames(variableNames); evaluationTable.setEvaluationColumn(evaluationColumn); tables.add(evaluationTable); } //fill tables rows for (ExtractedEvaluationData extractedEvaluationData : extractedEvaluationDataList) { EvaluationTable tempTable = new EvaluationTable(); tempTable.setOtherVariablesValue(extractedEvaluationData.importantDatas); EvaluationTable table = tables.get(tables.indexOf(tempTable)); table.setData(extractedEvaluationData.rowName, sortedColumns.indexOf(extractedEvaluationData.variablesData), extractedEvaluationData.evaluationData); } } //write to excel files { try { InputStream is = new BufferedInputStream(new FileInputStream(templateFile)); XLSTransformer transformer = new XLSTransformer(); List sheetNames = new ArrayList(); int i = 1; for (EvaluationTable table : tables) { /*StringBuffer sb = new StringBuffer(); List<String> otherValues = table.getOtherVariablesValue(); for (int i1 = 0; i1 < importantColumns.size(); i1++) { sb.append(importantColumns.get(i1)).append("=").append(otherValues.get(i1)).append(EvaluationTable.SEPARATOR); } if (importantColumns.size() > 0) { sb.delete(sb.lastIndexOf(EvaluationTable.SEPARATOR), sb.length()); } else { sb.append("untitled"); } sheetNames.add(sb.toString());*/ sheetNames.add("sheet" + i++); } HSSFWorkbook resultWorkbook = transformer.transformMultipleSheetsList(is, tables, sheetNames, "table", new HashMap(), 0); BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(outputFile)); resultWorkbook.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:edu.swjtu.excel.ExportCarData.java
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("?"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? /* ww w .j ava 2s .c o m*/ HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFCell cell = row.createCell(0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("?"); cell.setCellStyle(style); // ? ?? Connection con = null; try { con = db.getCon(); } catch (ClassNotFoundException | SQLException e1) { e1.printStackTrace(); } ArrayList<Car> list = null; list = new CarDaoImpl().getAllCar(con); for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); Car car = (Car) list.get(i); // ? row.createCell(0).setCellValue(i + 1); row.createCell(1).setCellValue(car.getLicensePlate()); row.createCell(2).setCellValue(car.getBrand()); row.createCell(3).setCellValue(car.getRegistrationDate()); row.createCell(4).setCellValue(car.getInsuranceDate()); row.createCell(5).setCellValue(car.getDrivingLicense()); row.createCell(6).setCellValue(car.getLicense()); row.createCell(7).setCellValue(car.getArrangeId()); row.createCell(8).setCellValue(car.getDriver()); row.createCell(9).setCellValue(car.getNumber()); } //? // FileOutputStream fout = new FileOutputStream("E:/car_data.xls"); // wb.write(fout); // fout.close(); String myexcel = "All_Car"; //? OutputStream out = response.getOutputStream(); //?? response.reset(); //Excel? response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls"); wb.write(out); out.close(); //? System.setOut(new PrintStream(out)); //? out.flush(); //? if (out != null) { out.close(); } try { db.closeCon(con); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }