List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write
@SuppressWarnings("resource") public final void write(OutputStream stream) throws IOException
From source file:org.ohdsi.jCdmBuilder.EtlReport.java
License:Apache License
/** * Generates the patient-specific Problem report * /* w ww.j av a 2s. co m*/ * @return The full path to the generated Problem report */ public String generateProblemReport() { StringUtilities.outputWithTime("Generating ETL problem list"); String filename = generateETLProblemListFilename(folder); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Problems"); addRow(sheet, "Table", "Problem", "Person_id"); for (Problem problem : problems.values()) { for (String personId : problem.personId) addRow(sheet, problem.table, problem.problemType, personId); // for (int i = 0; i < Math.min(MAX_REPORT_PROBLEM, problem.count); i++) // addRow(sheet, problem.table, problem.problemType, problem.personId.get(i)); if (problem.count > MAX_REPORT_PROBLEM) addRow(sheet, problem.table, problem.problemType, "in " + (problem.count - MAX_REPORT_PROBLEM) + " other persons"); } try { FileOutputStream out = new FileOutputStream(new File(filename)); workbook.write(out); out.close(); } catch (IOException e) { throw new RuntimeException(e.getMessage()); } return filename; }
From source file:org.ohdsi.jCdmBuilder.EtlReport.java
License:Apache License
/** * Generates the overall ETL report// w w w . ja va 2 s. com * * @param codeToConceptMaps * the codeToConceptMaps used in the ETL * @return the full path to the ETL report */ public String generateETLReport(CodeToConceptMap... codeToConceptMaps) { StringUtilities.outputWithTime("Generating ETL report"); String filename = generateFilename(folder); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Overview"); addRow(sheet, "Source tables"); addRow(sheet, ""); addRow(sheet, "Table name", "Number of records"); for (String table : incomingTableRowCounts) addRow(sheet, table, Integer.valueOf(incomingTableRowCounts.getCount(table))); addRow(sheet, ""); addRow(sheet, "CDM tables"); addRow(sheet, ""); addRow(sheet, "Table name", "Number of records"); for (String table : outgoingTableRowCounts) addRow(sheet, table, Integer.valueOf(outgoingTableRowCounts.getCount(table))); addRow(sheet, ""); addRow(sheet, "Number of problems encountered", Long.valueOf(totalProblemCount)); addRow(sheet, ""); addRow(sheet, "Mapping", "Mapped unique codes", "Unmapped unique codes", "Mapped total codes", "Unmapped total codes"); for (CodeToConceptMap codeToConceptMap : codeToConceptMaps) { int uniqueMapped = 0; int uniqueUnmapped = 0; long totalMapped = 0; long totalUnmapped = 0; CountingSet<String> codeCounts = codeToConceptMap.getCodeCounts(); for (String code : codeCounts) { if (!codeToConceptMap.getConceptId(code).equals(0)) { uniqueMapped++; totalMapped += codeCounts.getCount(code); } else { uniqueUnmapped++; totalUnmapped += codeCounts.getCount(code); } } addRow(sheet, codeToConceptMap.getName(), Integer.valueOf(uniqueMapped), Integer.valueOf(uniqueUnmapped), Long.valueOf(totalMapped), Long.valueOf(totalUnmapped)); } sheet = workbook.createSheet("Problems"); addRow(sheet, "Table", "Description", "Nr of rows"); for (Problem problem : problems.values()) addRow(sheet, problem.table, problem.problemType, Long.valueOf(problem.count)); for (CodeToConceptMap codeToConceptMap : codeToConceptMaps) { sheet = workbook.createSheet(codeToConceptMap.getName()); addRow(sheet, "Frequency", "Source code", "Source code description", "Target concept ID", "Target code", "Target concept description"); CountingSet<String> codeCounts = codeToConceptMap.getCodeCounts(); List<Map.Entry<String, CountingSet.Count>> codes = new ArrayList<Map.Entry<String, CountingSet.Count>>( codeCounts.key2count.entrySet()); reverseFrequencySort(codes); for (Map.Entry<String, CountingSet.Count> code : codes) { CodeData codeData = codeToConceptMap.getCodeData(code.getKey()); if (codeData == null) addRow(sheet, Integer.valueOf(code.getValue().count), code.getKey(), "", Integer.valueOf(0), "", ""); else for (int i = 0; i < codeData.targetConceptIds.length; i++) addRow(sheet, Integer.valueOf(code.getValue().count), code.getKey(), codeData.description, Integer.valueOf(codeData.targetConceptIds[i]), codeData.targetCodes[i], codeData.targetDescriptions[i]); } } try { FileOutputStream out = new FileOutputStream(new File(filename)); workbook.write(out); out.close(); } catch (IOException e) { throw new RuntimeException(e.getMessage()); } return filename; }
From source file:org.openbase.bco.ontology.lib.testing.Measurement.java
License:Open Source License
private void saveMemoryTestValues(final String sheetName, final List<Long> simpleQuMeasuredValues, final List<Long> complexQuMeasuredValues, final DataVolume dataVolume) { XSSFWorkbook workbook; XSSFSheet sheet;// w w w . j a v a2 s. co m Row rowSimple; Row rowComplex; try { FileInputStream excelFile = new FileInputStream(new File(FILE_NAME)); workbook = new XSSFWorkbook(excelFile); sheet = workbook.getSheet(sheetName); rowSimple = sheet.getRow(1); rowComplex = sheet.getRow(2); } catch (IOException ex) { workbook = new XSSFWorkbook(); sheet = workbook.createSheet(sheetName); final Row row = sheet.createRow(0); rowSimple = sheet.createRow(1); rowComplex = sheet.createRow(2); row.createCell(1).setCellValue("ConfigData only"); row.createCell(2).setCellValue("ConfigData and dayData"); row.createCell(3).setCellValue("ConfigData and 4x dayData"); } long sumSimple = 0L; long sumComplex = 0L; for (final long valueSimple : simpleQuMeasuredValues) { sumSimple += valueSimple; } for (final long valueComplex : complexQuMeasuredValues) { sumComplex += valueComplex; } int column = 0; switch (dataVolume) { case CONFIG: column = 1; break; case CONFIG_DAY: column = 2; break; case CONFIG_WEEK: column = 3; break; default: break; } System.out.println("Save date in column: " + column); // mean of simple trigger time final Cell cellMeanSimple = rowSimple.createCell(column); cellMeanSimple.setCellValue(sumSimple / simpleQuMeasuredValues.size()); // mean of complex trigger time final Cell cellMeanComplex = rowComplex.createCell(column); cellMeanComplex.setCellValue(sumComplex / complexQuMeasuredValues.size()); try { final File file = new File(FILE_NAME); file.setReadable(true, false); file.setExecutable(true, false); file.setWritable(true, false); System.out.println("Save data row ..."); final FileOutputStream outputStream = new FileOutputStream(file); workbook.write(outputStream); workbook.close(); } catch (IOException ex) { ExceptionPrinter.printHistory(ex, LOGGER); } }
From source file:org.openbase.bco.ontology.lib.testing.Measurement.java
License:Open Source License
private void putIntoExcelFile(final String sheetName, final List<Long> simpleQuMeasuredValues, final List<Long> complexQuMeasuredValues, int daysCurCount) { // https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/ XSSFWorkbook workbook; XSSFSheet sheet;/*from ww w.jav a2 s.c o m*/ Row row; try { FileInputStream excelFile = new FileInputStream(new File(FILE_NAME)); workbook = new XSSFWorkbook(excelFile); sheet = workbook.getSheet(sheetName); } catch (IOException ex) { workbook = new XSSFWorkbook(); sheet = workbook.createSheet(sheetName); row = sheet.createRow(daysCurCount); row.createCell(0).setCellValue("Days"); row.createCell(1).setCellValue("Triple"); row.createCell(2).setCellValue("Mean of simple trigger"); row.createCell(3).setCellValue("Mean of complex trigger"); } row = sheet.createRow(daysCurCount + 1); System.out.println("simple: " + simpleQuMeasuredValues); System.out.println("simple count: " + simpleQuMeasuredValues.size()); System.out.println("complex: " + complexQuMeasuredValues); System.out.println("complex count: " + complexQuMeasuredValues.size()); long sumSimple = 0L; long sumComplex = 0L; for (final long valueSimple : simpleQuMeasuredValues) { sumSimple += valueSimple; } for (final long valueComplex : complexQuMeasuredValues) { sumComplex += valueComplex; } // number of days final Cell cellDay = row.createCell(0); cellDay.setCellValue(daysCurCount + 1); // number of triple final Cell cellTriple = row.createCell(1); cellTriple.setCellValue(numberOfTriple); // mean of simple trigger time final Cell cellMeanSimple = row.createCell(2); cellMeanSimple.setCellValue(sumSimple / simpleQuMeasuredValues.size()); // mean of complex trigger time final Cell cellMeanComplex = row.createCell(3); cellMeanComplex.setCellValue(sumComplex / complexQuMeasuredValues.size()); try { final File file = new File(FILE_NAME); file.setReadable(true, false); file.setExecutable(true, false); file.setWritable(true, false); System.out.println("Save data row ..."); final FileOutputStream outputStream = new FileOutputStream(file); workbook.write(outputStream); workbook.close(); } catch (IOException ex) { ExceptionPrinter.printHistory(ex, LOGGER); } }
From source file:org.openelis.bean.DataViewReportBean.java
License:Open Source License
/** * Generates an Excel file based on the data in the passed VO, the passed * name and the passed flag/*from w ww.j a v a2 s .c o m*/ * * @param data * the VO that contains the columns, analytes and values selected by * the user; it also contains the query fields used to fetch the data * for generating the file * @param moduleName * the name of a security module for the logged in user; the module's * clause is used to restrict the fetched data to specific records * e.g. organizations; this parameter is usually specified only for * external users * @param showReportableColumnsOnly * if true, only reportable column analytes are shown; this parameter * is usually specified only for external users * @return ReportStatus that contains the path to the generated file * @throws Exception */ private ReportStatus runReport(DataView1VO data, String moduleName, boolean showReportableColumnsOnly) throws Exception { boolean excludeRes, excludeAux; Integer max; String source, value; ReportStatus status; QueryBuilderV2 builder; XSSFWorkbook wb; OutputStream out; Path path; ArrayList<String> headers; List<DataViewResultVO> results, noResAux, auxiliary; ArrayList<Integer> unselAnalyteIds; ArrayList<DataViewAnalyteVO> testAnalytes, auxFields; ArrayList<SampleManager1> sms; ArrayList<SampleManager1.Load> load; HashSet<String> resultValues, auxValues; HashSet<Integer> analysisIds, sampleIds; HashMap<Integer, HashSet<String>> testAnaResMap, auxFieldValMap; HashMap<Integer, SampleManager1> smMap; status = new ReportStatus(); excludeRes = "Y".equals(data.getExcludeResults()); excludeAux = "Y".equals(data.getExcludeAuxData()); builder = new QueryBuilderV2(); builder.setMeta(meta); results = null; auxiliary = null; noResAux = null; testAnaResMap = null; auxFieldValMap = null; load = new ArrayList<SampleManager1.Load>(); headers = new ArrayList<String>(); /* * get the labels to be displayed in the headers for the various * columns; Note: load parameter is changed based on selected columns */ headers = getHeaders(data.getColumns(), moduleName != null, load); /* * always fetch sample and analysis qa events to make sure that * overridden values are not shown */ load.add(SampleManager1.Load.QA); /* * the headers for analyte and value are always shown if results and/or * aux data are not excluded */ if (!excludeRes || !excludeAux) { headers.add(Messages.get().gen_analyte()); headers.add(Messages.get().gen_value()); } status.setMessage(Messages.get().report_fetchingData()); session.setAttribute("DataViewReportStatus", status); analysisIds = new HashSet<Integer>(); sampleIds = new HashSet<Integer>(); /* * get the maximum number of samples allowed by this report */ try { value = systemVariable.fetchByName("data_view_max_samples").getValue(); max = Integer.valueOf(value); } catch (Exception e) { log.log(Level.INFO, Messages.get().systemVariable_missingInvalidSystemVariable("data_view_max_samples"), e); max = DEFAULT_MAX_SAMPLES; } if (excludeRes && excludeAux) { /* * fetch the data for the case when both results and aux data are * excluded; make a set of analysis ids for fetching managers */ noResAux = fetchNoResultAuxData(moduleName, builder, data); for (DataViewResultVO nra : noResAux) { analysisIds.add(nra.getAnalysisId()); sampleIds.add(nra.getSampleId()); } } else { if (!excludeRes) { load.add(SampleManager1.Load.SINGLERESULT); unselAnalyteIds = new ArrayList<Integer>(); testAnalytes = data.getTestAnalytes(); if (testAnalytes != null) { /* * the analytes and results selected by the user are stored * in this map; the row for a result is added to the file if * it's found in the map */ testAnaResMap = new HashMap<Integer, HashSet<String>>(); for (DataViewAnalyteVO ana : testAnalytes) { /* * create the list of analytes not selected by the user * so that a decision can be made about including either * them or the selected analytes or none of the two in * the query to generate the report */ if ("N".equals(ana.getIsIncluded())) { unselAnalyteIds.add(ana.getAnalyteId()); continue; } resultValues = new HashSet<String>(); for (DataViewValueVO res : ana.getValues()) { if ("Y".equals(res.getIsIncluded())) resultValues.add(res.getValue()); } testAnaResMap.put(ana.getAnalyteId(), resultValues); } } /* * fetch results based on the analytes and values selected by * the user */ if (testAnaResMap != null && testAnaResMap.size() > 0) { log.log(Level.FINE, "Before fetching results"); results = fetchResults(moduleName, builder, testAnaResMap, unselAnalyteIds, data); log.log(Level.FINE, "Fetched " + results.size() + " results"); status.setPercentComplete(5); /* * make a set of analysis ids from the fetched results for * fetching the managers */ for (DataViewResultVO res : results) { analysisIds.add(res.getAnalysisId()); sampleIds.add(res.getSampleId()); } } } /* * the user wants to stop the report */ if (ReportStatus.Status.CANCEL.equals(status.getStatus())) { status.setMessage(Messages.get().report_stopped()); return status; } /* * number of samples fetched must not exceed the maximum allowed */ if (sampleIds.size() > max) throw new InconsistencyException( Messages.get().dataView_queryTooBigException(sampleIds.size(), max)); if (!excludeAux) { unselAnalyteIds = new ArrayList<Integer>(); auxFields = data.getAuxFields(); if (auxFields != null) { /* * the analytes and aux values selected by the user are * stored in this map; the row for an aux data is added to * the file if it's found in the map */ auxFieldValMap = new HashMap<Integer, HashSet<String>>(); for (DataViewAnalyteVO af : auxFields) { /* * create the list of analytes not selected by the user * so that a decision can be made about including either * them or the selected analytes or none of the two in * the query to generate the report */ if ("N".equals(af.getIsIncluded())) { unselAnalyteIds.add(af.getAnalyteId()); continue; } auxValues = new HashSet<String>(); for (DataViewValueVO val : af.getValues()) { if ("Y".equals(val.getIsIncluded())) auxValues.add(val.getValue()); } auxFieldValMap.put(af.getAnalyteId(), auxValues); } } /* * fetch aux data based on the analytes and values selected by * the user */ builder.clearWhereClause(); if (auxFieldValMap != null && auxFieldValMap.size() > 0) { log.log(Level.FINE, "Before fetching aux data"); auxiliary = fetchAuxData(moduleName, builder, auxFieldValMap, unselAnalyteIds, sampleIds, data); log.log(Level.FINE, "Fetched " + auxiliary.size() + " aux data"); /* * make a set of sample ids from the fetched aux data for * fetching managers but only if no results were fetched; if * results were fetched, the aux data's sample ids should * already be in the set; this is because aux data were * restricted by the results' sample ids */ if (results == null || results.size() == 0) { for (DataViewResultVO aux : auxiliary) sampleIds.add(aux.getSampleId()); } } } } status.setPercentComplete(25); if ((results == null || results.size() == 0) && (auxiliary == null || auxiliary.size() == 0) && (noResAux == null || noResAux.size() == 0)) throw new NotFoundException(); /* * the user wants to stop the report */ if (ReportStatus.Status.CANCEL.equals(status.getStatus())) { status.setMessage(Messages.get().report_stopped()); return status; } /* * number of samples fetched must not exceed the allowed limit */ if (sampleIds.size() > max) throw new InconsistencyException(Messages.get().dataView_queryTooBigException(sampleIds.size(), max)); /* * create the event log for data view */ source = Messages.get().dataView_eventLogMessage(userCache.getSystemUser().getLoginName(), sampleIds.size()); try { eventLog.add(new EventLogDO(null, dictionaryCache.getIdBySystemName("log_type_report"), source, null, null, Constants.dictionary().LOG_LEVEL_INFO, null, null, null)); } catch (Exception e) { log.log(Level.SEVERE, "Failed to add log entry for: " + source, e); } log.log(Level.FINE, "Before fetching managers"); /* * if analysis ids are present, managers are fetched by them and with * the load element SINGLEANALYSIS; this makes sure that only the * analyses and results linked to the analytes selected by the user are * fetched; otherwise managers are fetched by sample ids */ if (analysisIds.size() > 0) { load.add(SampleManager1.Load.SINGLEANALYSIS); sms = sampleManager1.fetchByAnalyses(DataBaseUtil.toArrayList(analysisIds), status, load.toArray(new SampleManager1.Load[load.size()])); } else { sms = sampleManager1.fetchByIds(DataBaseUtil.toArrayList(sampleIds), status, load.toArray(new SampleManager1.Load[load.size()])); } log.log(Level.FINE, "Fetched " + sms.size() + " managers"); smMap = new HashMap<Integer, SampleManager1>(); for (SampleManager1 sm : sms) smMap.put(getSample(sm).getId(), sm); sms = null; /* * create a workbook from the data structures created above; the passed * status is updated every time a new row is added to the workbook */ wb = getWorkbook(results, auxiliary, noResAux, testAnaResMap, auxFieldValMap, moduleName, showReportableColumnsOnly, headers, data, smMap, status); /* * the user wants to stop the report */ if (ReportStatus.Status.CANCEL.equals(status.getStatus())) { status.setMessage(Messages.get().report_stopped()); return status; } smMap = null; results = null; auxiliary = null; noResAux = null; testAnaResMap = null; auxFieldValMap = null; headers = null; /* * write the workbook to a file and set its path in the status */ if (wb != null) { out = null; try { status.setMessage(Messages.get().report_outputReport()).setPercentComplete(20); path = ReportUtil.createTempFile("dataview", ".xlsx", "upload_stream_directory"); out = Files.newOutputStream(path); wb.write(out); status.setPercentComplete(100).setMessage(path.getFileName().toString()).setPath(path.toString()) .setStatus(ReportStatus.Status.SAVED); } catch (Exception e) { log.log(Level.SEVERE, "Failed to output the file for data view", e); throw e; } finally { try { if (out != null) out.close(); } catch (Exception e) { // ignore } } } return status; }
From source file:org.openelis.bean.ToDoExcelHelperBean.java
License:Open Source License
private Path export(XSSFWorkbook wb, String systemVariableDirectory) throws Exception { Path path;/*w w w . j a v a2s. com*/ OutputStream out; out = null; try { path = ReportUtil.createTempFile("todo", ".xlsx", systemVariableDirectory); out = Files.newOutputStream(path); wb.write(out); } finally { try { if (out != null) out.close(); } catch (Exception e) { log.severe("Could not close output stream for todo"); } } return path; }
From source file:org.openepics.names.ui.export.ExcelExport.java
License:Open Source License
/** * Exports the entities from the database, producing a stream which can be streamed to the user over HTTP. * * @return an Excel input stream containing the exported data *///from w ww . j a v a2 s . c o m public InputStream exportFile() { // final List<NamePartRevision> approvedSectionsRevisions = namePartService.currentApprovedNamePartRevisions(NamePartType.SECTION, false); // final TreeNode sectionsTree = treeNodeManager.filteredNode(namePartTreeBuilder.newNamePartTree(approvedSectionsRevisions, Lists.<NamePartRevision>newArrayList(), true)); deviceTableController().update(); final TreeNode areaStructure = deviceTableController().getFilteredAreaStructure(); final TreeNode deviceStructure = deviceTableController().getFilteredDeviceStructure(); final List<DeviceRecordView> records = deviceTableController().getRecords(); // final List<NamePartRevision> approvedTypeRevisions = namePartService.currentApprovedNamePartRevisions(NamePartType.DEVICE_TYPE, false); // final TreeNode typesTree = treeNodeManager.filteredNode(namePartTreeBuilder.newNamePartTree(approvedTypeRevisions, Lists.<NamePartRevision>newArrayList(), true)); // final List<DeviceRevision> devices = Lists.newArrayList(); // for (DeviceRevision deviceRevision : namePartService.currentDeviceRevisions(false)) { // boolean filteredSection=sessionViewService.isFiltered(deviceRevision.getSection()); // boolean filteredDeviceType=sessionViewService.isFiltered(deviceRevision.getDeviceType()); // if(filteredSection&&filteredDeviceType) devices.add(deviceRevision); // } // final XSSFWorkbook workbook = exportWorkbook(sectionsTree, typesTree, devices); final XSSFWorkbook workbook = exportWorkbook(areaStructure, deviceStructure, records); final InputStream inputStream; try { final File temporaryFile = File.createTempFile("temp", "xlsx"); FileOutputStream outputStream = new FileOutputStream(temporaryFile); workbook.write(outputStream); outputStream.close(); inputStream = new FileInputStream(temporaryFile); temporaryFile.delete(); } catch (IOException e) { throw new RuntimeException(e); } return inputStream; }
From source file:org.optapconf.plannerbenchmark.ConferenceFileIO.java
License:Apache License
@Override public void write(Solution solution, File outputSolutionFile) { Conference conference = (Conference) solution; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Conference"); XSSFRow headerRow = sheet.createRow(0); int x = 1;/*ww w. j a va 2s . co m*/ Map<Room, Integer> roomXMap = new HashMap<>(conference.getRoomList().size()); for (Room room : conference.getRoomList()) { XSSFCell cell = headerRow.createCell(x); cell.setCellValue(room.getName()); roomXMap.put(room, x); x++; } int y = 1; Map<Timeslot, XSSFRow> timeslotRowMap = new HashMap<>(conference.getTimeslotList().size()); for (Timeslot timeslot : conference.getTimeslotList()) { XSSFRow row = sheet.createRow(y); XSSFCell cell = row.createCell(0); cell.setCellValue(timeslot.getDay().getName() + " - " + timeslot.getName()); timeslotRowMap.put(timeslot, row); y++; } for (Talk talk : conference.getTalkList()) { Timeslot timeslot = talk.getTimeslot(); Room room = talk.getRoom(); if (timeslot != null && room != null) { XSSFCell cell = timeslotRowMap.get(timeslot).createCell(roomXMap.get(room)); cell.setCellValue(talk.getTitle()); } else { XSSFCell unassignedCell = sheet.createRow(y).createCell(1); unassignedCell.setCellValue(talk.getTitle()); y++; } } try { try (OutputStream out = new FileOutputStream(outputSolutionFile)) { workbook.write(out); workbook.close(); } } catch (IOException e) { throw new IllegalStateException("Problem writing outputSolutionFile (" + outputSolutionFile + ").", e); } }
From source file:org.ramalapure.userinfoapp.UserInfoApp.java
@Override public void start(Stage primaryStage) { CheckConnection();//from w w w . j a v a 2 s. c o m fillComboBox(); // create transperant stage //primaryStage.initStyle(StageStyle.TRANSPARENT); primaryStage.setTitle("JavaFX 8 Tutorial 61 - Retrive Database Values Into CheckBox"); primaryStage.getIcons().add(new Image("file:user-icon.png")); BorderPane layout = new BorderPane(); Scene newscene = new Scene(layout, 1200, 700, Color.rgb(0, 0, 0, 0)); Group root = new Group(); Scene scene = new Scene(root, 320, 200, Color.rgb(0, 0, 0, 0)); scene.getStylesheets().add(getClass().getResource("Style.css").toExternalForm()); Color foreground = Color.rgb(255, 255, 255, 0.9); //Rectangila Background Rectangle background = new Rectangle(320, 250); background.setX(0); background.setY(0); background.setArcHeight(15); background.setArcWidth(15); background.setFill(Color.rgb(0, 0, 0, 0.55)); background.setStroke(foreground); background.setStrokeWidth(1.5); VBox vbox = new VBox(5); vbox.setPadding(new Insets(10, 0, 0, 10)); Label label = new Label("Label"); //label.setTextFill(Color.WHITESMOKE); label.setFont(new Font("SanSerif", 20)); TextField username = new TextField(); username.setFont(Font.font("SanSerif", 20)); username.setPromptText("Username"); username.getStyleClass().add("field-background"); PasswordField password = new PasswordField(); password.setFont(Font.font("SanSerif", 20)); password.setPromptText("Password"); password.getStyleClass().add("field-background"); password.setOnKeyPressed(e -> { if (e.getCode() == KeyCode.ENTER) { try { String query = "select * from UserDatabase where Username=? and Password=?"; pst = conn.prepareStatement(query); pst.setString(1, username.getText()); pst.setString(2, password.getText()); rs = pst.executeQuery(); if (rs.next()) { //label.setText("Login Successful"); primaryStage.setScene(newscene); primaryStage.show(); } else { label.setText("Login Failed"); } username.clear(); password.clear(); pst.close(); rs.close(); } catch (Exception e1) { label.setText("SQL Error"); System.err.println(e1); } } }); Button btn = new Button("Login"); btn.setFont(Font.font("SanSerif", 15)); btn.setOnAction(e -> { try { String query = "select * from UserDatabase where Username=? and Password=?"; pst = conn.prepareStatement(query); pst.setString(1, username.getText()); pst.setString(2, password.getText()); rs = pst.executeQuery(); if (rs.next()) { //label.setText("Login Successful"); primaryStage.setScene(newscene); primaryStage.show(); } else { label.setText("Login Failed"); } username.clear(); password.clear(); pst.close(); rs.close(); } catch (Exception e1) { label.setText("SQL Error"); System.err.println(e1); } }); vbox.getChildren().addAll(label, username, password, btn); root.getChildren().addAll(background, vbox); Button logout = new Button("Logout"); logout.setFont(Font.font("SanSerif", 15)); logout.setOnAction(e -> { primaryStage.setScene(scene); primaryStage.show(); }); layout.setTop(logout); BorderPane.setAlignment(logout, Pos.TOP_RIGHT); BorderPane.setMargin(logout, new Insets(10)); VBox fields = new VBox(5); searchField = new TextField(); searchField.setFont(Font.font("SanSerif", 15)); searchField.setPromptText("Search"); searchField.setMaxWidth(200); Label label1 = new Label("Create New User"); label1.setFont(new Font("Sanserif", 20)); id = new TextField(); id.setFont(Font.font("SanSerif", 15)); id.setPromptText("ID"); id.setMaxWidth(200); fn = new TextField(); fn.setFont(Font.font("SanSerif", 15)); fn.setPromptText("First Name"); fn.setMaxWidth(200); ln = new TextField(); ln.setFont(Font.font("SanSerif", 15)); ln.setPromptText("Last Name"); ln.setMaxWidth(200); em = new TextField(); em.setFont(Font.font("SanSerif", 15)); em.setPromptText("Email"); em.setMaxWidth(200); mobile = new TextField(); mobile.setFont(Font.font("SanSerif", 15)); mobile.setPromptText("Mobile No."); mobile.setMaxWidth(200); un = new TextField(); un.setFont(Font.font("SanSerif", 15)); un.setPromptText("Username"); un.setMaxWidth(200); pw = new PasswordField(); pw.setFont(Font.font("SanSerif", 15)); pw.setPromptText("Password"); pw.setMaxWidth(200); date = new DatePicker(); date.setPromptText("Date of Birth"); date.setMaxWidth(200); date.setStyle("-fx-font-size:15"); ToggleGroup gender = new ToggleGroup(); male = new RadioButton("Male"); male.setToggleGroup(gender); male.setOnAction(e -> { radioButtonLabel = male.getText(); }); female = new RadioButton("Female"); female.setToggleGroup(gender); female.setOnAction(e -> { radioButtonLabel = female.getText(); }); checkBox1 = new CheckBox("Playing"); checkBox1.setOnAction(e -> { checkBoxList.add(checkBox1.getText()); }); checkBox2 = new CheckBox("Singing"); checkBox2.setOnAction(e -> { checkBoxList.add(checkBox2.getText()); }); checkBox3 = new CheckBox("Dancing"); checkBox3.setOnAction(e -> { checkBoxList.add(checkBox3.getText()); }); date.requestFocus(); male.requestFocus(); female.requestFocus(); checkBox1.requestFocus(); checkBox2.requestFocus(); checkBox3.requestFocus(); textArea = new TextArea(); textArea.setFont(Font.font("SanSerif", 12)); textArea.setPromptText("Path Of Selected File Or Files"); textArea.setPrefSize(300, 50); textArea.setEditable(false); fileChooser = new FileChooser(); fileChooser.getExtensionFilters().addAll(new ExtensionFilter("Text Files", "*txt"), new ExtensionFilter("Image Files", "*.png", "*.jpg", "*.gif"), new ExtensionFilter("Audio Files", "*wav", "*.mp3", "*.aac"), new ExtensionFilter("All Files", "*.*")); browse = new Button("Browse"); browse.setFont(Font.font("SanSerif", 15)); browse.setOnAction(e -> { //Single File Selection file = fileChooser.showOpenDialog(primaryStage); if (file != null) { textArea.setText(file.getAbsolutePath()); image = new Image(file.toURI().toString(), 100, 150, true, true);//path, PrefWidth, PrefHeight, PreserveRatio, Smooth imageView = new ImageView(image); imageView.setFitWidth(100); imageView.setFitHeight(150); imageView.setPreserveRatio(true); layout.setCenter(imageView); BorderPane.setAlignment(imageView, Pos.TOP_LEFT); } //Multiple File Selection /*List<File> fileList = fileChooser.showOpenMultipleDialog(primaryStage); if(fileList != null){ fileList.stream().forEach(selectedFiles ->{ textArea.setText(fileList.toString()); }); }*/ }); Button button = new Button("Save"); button.setFont(Font.font("SanSerif", 15)); button.setOnAction(e -> { if (validateNumber() & validateFirstName() & validateLastName() & validateEmaill() & validateMobileNo() & validatePassword() & validateFields()) { try { String query = "INSERT INTO UserDatabase (ID, FirstName, LastName, Email, Username, Password, DOB, Gender, MobileNo, Hobbies, Image) VALUES(?,?,?,?,?,?,?,?,?,?,?)"; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.setString(2, fn.getText()); pst.setString(3, ln.getText()); pst.setString(4, em.getText()); pst.setString(5, un.getText()); pst.setString(6, pw.getText()); pst.setString(7, ((TextField) date.getEditor()).getText()); pst.setString(8, radioButtonLabel); pst.setString(9, mobile.getText()); pst.setString(10, checkBoxList.toString()); fis = new FileInputStream(file); pst.setBinaryStream(11, (InputStream) fis, (int) file.length()); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User has been created."); alert.showAndWait(); pst.execute(); pst.close(); clearFields(); } catch (SQLException | FileNotFoundException e1) { label.setText("SQL Error"); System.err.println(e1); } fillComboBox(); refreshTable(); } }); Button update = new Button("Update"); update.setFont(Font.font("SanSerif", 15)); update.setOnAction(e -> { if (validateNumber() & validateFirstName() & validateLastName() & validateEmaill() & validateMobileNo() & validatePassword() & validateFields()) { try { String query = "update UserDatabase set ID=?, FirstName=?, LastName=?, Email=?, Username=?, Password=?, DOB=?, Gender=?, MobileNo=?, Hobbies=?, Image=? where ID='" + id.getText() + "' "; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.setString(2, fn.getText()); pst.setString(3, ln.getText()); pst.setString(4, em.getText()); pst.setString(5, un.getText()); pst.setString(6, pw.getText()); pst.setString(7, ((TextField) date.getEditor()).getText()); pst.setString(8, radioButtonLabel); pst.setString(9, mobile.getText()); pst.setString(10, checkBoxList.toString()); fis = new FileInputStream(file); pst.setBinaryStream(11, (InputStream) fis, (int) file.length()); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User details has been updated."); alert.showAndWait(); pst.execute(); pst.close(); clearFields(); } catch (SQLException | FileNotFoundException e1) { label.setText("SQL Error"); System.err.println(e1); } fillComboBox(); refreshTable(); } }); fields.getChildren().addAll(searchField, label1, id, fn, ln, em, mobile, un, pw, date, male, female, checkBox1, checkBox2, checkBox3, browse, textArea, button); layout.setLeft(fields); BorderPane.setMargin(fields, new Insets(0, 10, 0, 10)); table = new TableView<>(); TableColumn column1 = new TableColumn("ID"); column1.setMaxWidth(50); column1.setCellValueFactory(new PropertyValueFactory<>("ID")); TableColumn column2 = new TableColumn("First Name"); column2.setMinWidth(80); column2.setCellValueFactory(new PropertyValueFactory<>("firstName")); TableColumn column3 = new TableColumn("Last Name"); column3.setMinWidth(80); column3.setCellValueFactory(new PropertyValueFactory<>("lastName")); TableColumn column4 = new TableColumn("Email"); column4.setMinWidth(150); column4.setCellValueFactory(new PropertyValueFactory<>("email")); TableColumn column5 = new TableColumn("Username"); column5.setMinWidth(80); column5.setCellValueFactory(new PropertyValueFactory<>("username")); TableColumn column6 = new TableColumn("Password"); column6.setMinWidth(80); column6.setCellValueFactory(new PropertyValueFactory<>("password")); TableColumn column7 = new TableColumn("DOB"); column7.setMinWidth(70); column7.setCellValueFactory(new PropertyValueFactory<>("DOB")); TableColumn column8 = new TableColumn("Gender"); column8.setMinWidth(50); column8.setCellValueFactory(new PropertyValueFactory<>("Gender")); TableColumn column9 = new TableColumn("Mobile No."); column9.setMinWidth(70); column9.setCellValueFactory(new PropertyValueFactory<>("MobileNo")); TableColumn column10 = new TableColumn("Hobbies"); column10.setMinWidth(100); column10.setCellValueFactory(new PropertyValueFactory<>("Hobbies")); table.getColumns().addAll(column1, column2, column3, column4, column5, column6, column7, column8, column9, column10); table.setTableMenuButtonVisible(true); ScrollPane sp = new ScrollPane(table); //sp.setContent(table); sp.setPrefSize(600, 200); sp.setHbarPolicy(ScrollPane.ScrollBarPolicy.AS_NEEDED); sp.setVbarPolicy(ScrollPane.ScrollBarPolicy.AS_NEEDED); sp.setFitToHeight(true); sp.setHmax(3); sp.setHvalue(0); sp.setDisable(false); layout.setRight(sp); BorderPane.setMargin(sp, new Insets(0, 10, 10, 10)); Button load = new Button("Load Table"); load.setFont(Font.font("SanSerif", 15)); load.setOnAction(e -> { refreshTable(); }); ComboBox comboBox = new ComboBox(options); comboBox.setMaxHeight(30); comboBox.setOnAction(e -> { try { String query = "select * from UserDatabase where FirstName = ?"; pst = conn.prepareStatement(query); pst.setString(1, (String) comboBox.getSelectionModel().getSelectedItem()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if ("Male".equals(rs.getString("Gender"))) { male.setSelected(true); } else if ("Female".equals(rs.getString("Gender"))) { female.setSelected(true); } else { male.setSelected(false); female.setSelected(false); } } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); Button delete = new Button("Delete User"); delete.setFont(Font.font("SanSerif", 15)); delete.setOnAction(e -> { Alert alert = new Alert(AlertType.CONFIRMATION); alert.setTitle("Confirmation Dialog"); alert.setHeaderText(null); alert.setContentText("Are you sure to delete?"); Optional<ButtonType> action = alert.showAndWait(); if (action.get() == ButtonType.OK) { try { String query = "delete from UserDatabase where id = ?"; pst = conn.prepareStatement(query); pst.setString(1, id.getText()); pst.executeUpdate(); pst.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } } clearFields(); fillComboBox(); refreshTable(); }); exportToXL = new Button("Export To Excel"); exportToXL.setFont(Font.font("Sanserif", 15)); exportToXL.setOnAction(e -> { try { String query = "Select * from UserDatabase"; pst = conn.prepareStatement(query); rs = pst.executeQuery(); //Apache POI Jar Link- //http://a.mbbsindia.com/poi/release/bin/poi-bin-3.13-20150929.zip XSSFWorkbook wb = new XSSFWorkbook();//for earlier version use HSSF XSSFSheet sheet = wb.createSheet("User Details"); XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("First Name"); header.createCell(2).setCellValue("Last Name"); header.createCell(3).setCellValue("Email"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.setColumnWidth(3, 256 * 25);//256-character width sheet.setZoom(150);//scale-150% int index = 1; while (rs.next()) { XSSFRow row = sheet.createRow(index); row.createCell(0).setCellValue(rs.getString("ID")); row.createCell(1).setCellValue(rs.getString("FirstName")); row.createCell(2).setCellValue(rs.getString("LastName")); row.createCell(3).setCellValue(rs.getString("Email")); index++; } FileOutputStream fileOut = new FileOutputStream("UserDetails.xlsx");// befor 2007 version xls wb.write(fileOut); fileOut.close(); Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User Details Exported in Excel Sheet."); alert.showAndWait(); pst.close(); rs.close(); } catch (SQLException | FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); importXLToDB = new Button("Import XL TO DB"); importXLToDB.setFont(Font.font("Sanserif", 15)); importXLToDB.setOnAction(e -> { try { String query = "Insert into UserDatabase(ID, FirstName, LastName, Email) values (?,?,?,?)"; pst = conn.prepareStatement(query); FileInputStream fileIn = new FileInputStream(new File("UserInfo.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(fileIn); XSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); pst.setInt(1, (int) row.getCell(0).getNumericCellValue()); pst.setString(2, row.getCell(1).getStringCellValue()); pst.setString(3, row.getCell(2).getStringCellValue()); pst.setString(4, row.getCell(3).getStringCellValue()); pst.execute(); } Alert alert = new Alert(AlertType.INFORMATION); alert.setTitle("Information Dialog"); alert.setHeaderText(null); alert.setContentText("User Details Imported From Excel Sheet To Database."); alert.showAndWait(); wb.close(); fileIn.close(); pst.close(); rs.close(); } catch (SQLException | FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } refreshTable(); }); HBox hbox = new HBox(5); hbox.getChildren().addAll(load, delete, update, comboBox, exportToXL, importXLToDB); layout.setBottom(hbox); BorderPane.setMargin(hbox, new Insets(10, 0, 10, 10)); ListView list = new ListView(options); list.setMaxSize(100, 250); //layout.setLeft(list); //BorderPane.setMargin(list, new Insets(10)); table.setOnMouseClicked(e -> { try { User user = (User) table.getSelectionModel().getSelectedItem(); String query = "select * from UserDatabase where ID = ?"; pst = conn.prepareStatement(query); pst.setString(1, user.getID()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if (null != rs.getString("Gender")) switch (rs.getString("Gender")) { case "Male": male.setSelected(true); break; case "Female": female.setSelected(true); break; default: male.setSelected(false); female.setSelected(false); break; } else { male.setSelected(false); female.setSelected(false); } // Retrive Hobbies Into CheckBox if (rs.getString("Hobbies") != null) { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); //hobbies in the string formate - [Playing , Dancing] System.out.println(rs.getString("Hobbies")); String checkBoxString = rs.getString("Hobbies").replace("[", "").replace("]", ""); System.out.println(checkBoxString); //now can converert to a list, strip out commas and spaces List<String> hobbylist = Arrays.asList(checkBoxString.split("\\s*,\\s*")); System.out.println(hobbylist); for (String hobby : hobbylist) { switch (hobby) { case "Playing": checkBox1.setSelected(true); break; case "Singing": checkBox2.setSelected(true); break; case "Dancing": checkBox3.setSelected(true); break; default: checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } } } else { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } InputStream is = rs.getBinaryStream("Image"); OutputStream os = new FileOutputStream(new File("photo.jpg")); byte[] content = new byte[1024]; int size = 0; while ((size = is.read(content)) != -1) { os.write(content, 0, size); } os.close(); is.close(); image = new Image("file:photo.jpg", 100, 150, true, true); ImageView imageView1 = new ImageView(image); imageView1.setFitWidth(100); imageView1.setFitHeight(150); imageView1.setPreserveRatio(true); layout.setCenter(imageView1); BorderPane.setAlignment(imageView1, Pos.TOP_LEFT); } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } }); table.setOnKeyReleased(e -> { if (e.getCode() == KeyCode.UP || e.getCode() == KeyCode.DOWN) { try { User user = (User) table.getSelectionModel().getSelectedItem(); String query = "select * from UserDatabase where ID = ?"; pst = conn.prepareStatement(query); pst.setString(1, user.getID()); rs = pst.executeQuery(); while (rs.next()) { id.setText(rs.getString("ID")); fn.setText(rs.getString("FirstName")); ln.setText(rs.getString("LastName")); em.setText(rs.getString("Email")); mobile.setText(rs.getString("MobileNo")); un.setText(rs.getString("Username")); pw.setText(rs.getString("Password")); ((TextField) date.getEditor()).setText(rs.getString("DOB")); if (null != rs.getString("Gender")) switch (rs.getString("Gender")) { case "Male": male.setSelected(true); break; case "Female": female.setSelected(true); break; default: male.setSelected(false); female.setSelected(false); break; } else { male.setSelected(false); female.setSelected(false); } // Retrive Hobbies Into CheckBox if (rs.getString("Hobbies") != null) { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); //hobbies in the string formate - [Playing , Dancing] System.out.println(rs.getString("Hobbies")); String checkBoxString = rs.getString("Hobbies").replace("[", "").replace("]", ""); System.out.println(checkBoxString); //now can converert to a list, strip out commas and spaces List<String> hobbylist = Arrays.asList(checkBoxString.split("\\s*,\\s*")); System.out.println(hobbylist); for (String hobby : hobbylist) { switch (hobby) { case "Playing": checkBox1.setSelected(true); break; case "Singing": checkBox2.setSelected(true); break; case "Dancing": checkBox3.setSelected(true); break; default: checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } } } else { checkBox1.setSelected(false); checkBox2.setSelected(false); checkBox3.setSelected(false); } InputStream is = rs.getBinaryStream("Image"); OutputStream os = new FileOutputStream(new File("photo.jpg")); byte[] content = new byte[1024]; int size = 0; while ((size = is.read(content)) != -1) { os.write(content, 0, size); } os.close(); is.close(); image = new Image("file:photo.jpg", 100, 150, true, true); ImageView imageView1 = new ImageView(image); imageView1.setFitWidth(100); imageView1.setFitHeight(150); imageView1.setPreserveRatio(true); layout.setCenter(imageView1); BorderPane.setAlignment(imageView1, Pos.TOP_LEFT); } pst.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(UserInfoApp.class.getName()).log(Level.SEVERE, null, ex); } } }); FilteredList<User> filteredData = new FilteredList<>(data, e -> true); searchField.setOnKeyReleased(e -> { searchField.textProperty().addListener((observableValue, oldValue, newValue) -> { filteredData.setPredicate((Predicate<? super User>) user -> { if (newValue == null || newValue.isEmpty()) { return true; } String lowerCaseFilter = newValue.toLowerCase(); if (user.getID().contains(newValue)) { return true; } else if (user.getFirstName().toLowerCase().contains(lowerCaseFilter)) { return true; } else if (user.getLastName().toLowerCase().contains(lowerCaseFilter)) { return true; } return false; }); }); SortedList<User> sortedData = new SortedList<>(filteredData); sortedData.comparatorProperty().bind(table.comparatorProperty()); table.setItems(sortedData); }); primaryStage.setScene(scene); primaryStage.show(); }
From source file:org.rapidpm.data.table.export.Table2XLSX.java
License:Apache License
@Override public ByteArrayOutputStream export(final Table table) { final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet xssfSheet = workbook.createSheet(table.getTableName()); final Collection<ColumnInformation> infoList = table.getColumnInfoList(); final XSSFRow xssfHeaderRow = xssfSheet.createRow(0); for (final ColumnInformation information : infoList) { if (information.isExportable()) { final XSSFCell xssfCell = xssfHeaderRow.createCell(information.getSpaltenNr()); xssfCell.setCellValue(information.getSpaltenName()); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); } else {/* w w w.ja v a2s. co m*/ if (logger.isDebugEnabled()) { logger.debug("ColInfo not exportable " + information.getSpaltenName()); } } } final List<Row> tableRowList = table.getRowList(); for (final Row row : tableRowList) { final XSSFRow xssfRow = xssfSheet.createRow(row.getRowNr() + 1); final List<Cell> cellList = row.getCells(); for (final Cell cell : cellList) { if (cell.getColInfo().isExportable()) { final XSSFCell xssfCell = xssfRow.createCell(cell.getColInfo().getSpaltenNr()); final CellTypeEnum cellType = cell.getCellType(); if (cellType.equals(CellTypeEnum.RawData)) { xssfCell.setCellValue(cell.getFormattedValue()); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); //JIRA MOD-32 CellType in Abhngigkeit der ValueClass z.B. Number } else if (cellType.equals(CellTypeEnum.RawLink)) { final XSSFCreationHelper helper = workbook.getCreationHelper(); final XSSFHyperlink xssfHyperlink = helper.createHyperlink(Hyperlink.LINK_URL); xssfHyperlink.setAddress(cell.getFormattedValue()); xssfHyperlink.setLabel(cell.getLabel()); xssfCell.setCellValue(cell.getLabel()); xssfCell.setHyperlink(xssfHyperlink); final CellStyle hlink_style = createHyperLinkStyle(workbook); xssfCell.setCellStyle(hlink_style); } else { } } else { if (logger.isDebugEnabled()) { logger.debug("Cell not exportable "); } } } } final ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); try { workbook.write(outputStream); } catch (IOException e) { logger.error(e); } return outputStream; }