List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet
Sheet createSheet();
From source file:com.exilant.exility.core.XlxUtil.java
License:Open Source License
/** * Very specific requirement for saving labels. If the file exists, append * only missing labels//w w w. j a v a2 s . com * * @param fileName * @param rows * @return true if we are able to save the file */ public boolean appendMissingOnes(String fileName, String[][] rows) { File file = new File(fileName); Workbook workbook; Sheet sheet; if (file.exists()) { /** * read spreadsheet */ try { InputStream is = new FileInputStream(file); workbook = WorkbookFactory.create(is); is.close(); Spit.out(fileName + " read into a workbook."); } catch (Exception e) { Spit.out(fileName + " is not saved because of an error while reading existing contents. " + e.getMessage()); Spit.out(e); return false; } sheet = workbook.getSheetAt(0); if (sheet == null) { sheet = workbook.createSheet(); } } else { Spit.out(fileName + " does not exist. New file will be created."); /** * first time this is being saved. */ workbook = this.getWorkbookForFile(fileName); sheet = workbook.createSheet(); } if (sheet.getLastRowNum() > 0) { this.addMissingRows(sheet, rows); } else { this.addRows(sheet, rows); } return this.save(workbook, fileName); }
From source file:com.github.autoprimer3.Primer3ResultViewController.java
License:Open Source License
private void writePrimersToExcel(final File f) throws IOException { final Service<Void> service = new Service<Void>() { @Override/* w w w . j a va 2 s.c o m*/ protected Task<Void> createTask() { return new Task<Void>() { @Override protected Void call() throws IOException { BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f)); Workbook wb = new XSSFWorkbook(); CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); CreationHelper createHelper = wb.getCreationHelper(); Sheet listSheet = wb.createSheet(); Sheet detailsSheet = wb.createSheet(); Row row = null; int rowNo = 0; int sheetNo = 0; wb.setSheetName(sheetNo++, "List"); wb.setSheetName(sheetNo++, "Details"); row = listSheet.createRow(rowNo++); String header[] = { "Primer", "Sequence", "Product Size (bp)" }; for (int col = 0; col < header.length; col++) { Cell cell = row.createCell(col); cell.setCellValue(header[col]); } updateMessage("Writing primers . . ."); updateProgress(0, data.size() * 3); int n = 0; for (Primer3Result r : data) { n++; updateMessage("Writing primer list " + n + " . . ."); row = listSheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue(r.getName() + "F"); cell = row.createCell(col++); cell.setCellValue(r.getLeftPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); updateProgress(n, data.size() * 3); updateMessage("Writing primer list " + n + " . . ."); row = listSheet.createRow(rowNo++); col = 0; cell = row.createCell(col++); cell.setCellValue(r.getName() + "R"); cell = row.createCell(col++); cell.setCellValue(r.getRightPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); n++; updateProgress(n, data.size() * 3); } rowNo = 0; row = detailsSheet.createRow(rowNo++); ArrayList<String> detailsHeader = new ArrayList<>(Arrays.asList("Name", "Other IDs", "Left Primer", "Right Primer", "Product Size (bp)", "Region", "in-silico PCR")); if (ispcrResCol.isVisible()) { detailsHeader.add("in-silico PCR Results"); } for (int col = 0; col < detailsHeader.size(); col++) { Cell cell = row.createCell(col); cell.setCellValue(detailsHeader.get(col)); } int m = 0; for (Primer3Result r : data) { m++; updateMessage("Writing details for pair " + m + " . . ."); row = detailsSheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue(r.getName()); cell = row.createCell(col++); cell.setCellValue(r.getTranscripts()); cell = row.createCell(col++); cell.setCellValue(r.getLeftPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getRightPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); cell = row.createCell(col++); cell.setCellValue(r.getRegion()); cell = row.createCell(col++); if (r.getIsPcrUrl() != null) { cell.setCellValue("isPCR"); org.apache.poi.ss.usermodel.Hyperlink hl = createHelper .createHyperlink(org.apache.poi.ss.usermodel.Hyperlink.LINK_URL); hl.setAddress(r.getIsPcrUrl()); cell.setHyperlink(hl); cell.setCellStyle(hlink_style); } else { cell.setCellValue(""); } if (ispcrResCol.isVisible()) { cell = row.createCell(col++); if (r.getIsPcrResults() != null) { cell.setCellValue(r.getIsPcrResults()); } else { cell.setCellValue(""); } } updateProgress(n + m, data.size() * 3); } updateMessage("Wrote " + data.size() + " primer pairs to file."); wb.write(bo); bo.close(); return null; } }; } }; service.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Action response = Dialogs.create().title("Done").masthead("Finished writing") .message("Primers successfully written to " + f.getAbsolutePath() + "\n\nDo you want to open " + "this file now?") .actions(Dialog.ACTION_YES, Dialog.ACTION_NO).styleClass(Dialog.STYLE_CLASS_NATIVE) .showConfirm(); if (response == Dialog.ACTION_YES) { try { openFile(f); } catch (IOException ex) { Action openFailed = Dialogs.create().title("Open failed") .masthead("Could not open output file") .message("Exception encountered when attempting to open " + "the saved file. See below:") .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(ex); } } progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); service.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Dialogs writeCancelled = Dialogs.create().title("Writing Cancelled") .masthead("Cancelled writing to file").message("User cancelled writing primers to file.") .styleClass(Dialog.STYLE_CLASS_NATIVE); writeCancelled.showInformation(); progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); service.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Action writeFailed = Dialogs.create().title("Writing failed") .masthead("Could not write primers to file") .message("Exception encountered when attempting to write " + "primers to file. See below:") .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(e.getSource().getException()); progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); progressBar.setVisible(true); progressBar.progressProperty().bind(service.progressProperty()); summaryLabel.textProperty().bind(service.messageProperty()); closeButton.setDisable(true); closeMenuItem.setDisable(true); checkIsPcrButton.setText("Cancel"); checkIsPcrButton.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent actionEvent) { service.cancel(); } }); service.start(); }
From source file:com.github.mutationmapper.MutationMapperResultViewController.java
License:Open Source License
private void writeResultsToExcel(final File f) throws IOException { final Service<Void> service = new Service<Void>() { @Override/*from w w w . j a va 2s . c o m*/ protected Task<Void> createTask() { return new Task<Void>() { @Override protected Void call() throws IOException { BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f)); Workbook wb = new XSSFWorkbook(); //CellStyle hlink_style = wb.createCellStyle(); //Font hlink_font = wb.createFont(); //hlink_font.setUnderline(Font.U_SINGLE); //hlink_font.setColor(IndexedColors.BLUE.getIndex()); //hlink_style.setFont(hlink_font); //CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(); Row row = null; int rowNo = 0; row = sheet.createRow(rowNo++); String header[] = { "#", "Symbol", "Gene", "Transcript", "Genomic Coordinate", "Ref", "Var", "CDS", "Consequence", "CDS Consequence", "Protein Consequence", "Exon/Intron", "Colocated Variants", "Polyphen", "Sift", "Seq Input" }; for (int col = 0; col < header.length; col++) { Cell cell = row.createCell(col); cell.setCellValue(header[col]); } updateMessage("Writing results . . ."); updateProgress(0, displayData.size()); int n = 0; for (MutationMapperResult r : displayData) { n++; updateMessage("Writing result " + n + " . . ."); row = sheet.createRow(rowNo++); int col = 0; ArrayList<String> resultsToWrite = getResultArray(r); for (String s : resultsToWrite) { Cell cell = row.createCell(col++); cell.setCellValue(s); } updateProgress(n, displayData.size()); } updateMessage("Wrote " + displayData.size() + " results to file."); wb.write(bo); bo.close(); return null; } }; } }; service.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Alert alert = new Alert(AlertType.CONFIRMATION); alert.setTitle("File Saved"); alert.setHeaderText("Displayed results saved to " + f.getName()); alert.setContentText("Open this file now?"); ButtonType yesButton = ButtonType.YES; ButtonType noButton = ButtonType.NO; alert.getButtonTypes().setAll(yesButton, noButton); Optional<ButtonType> response = alert.showAndWait(); if (response.get() == yesButton) { try { openFile(f); } catch (Exception ex) { Alert openError = getExceptionDialog(ex); openError.setTitle("Open Failed"); openError.setHeaderText("Could not open ouput file."); openError.setContentText( "Exception encountered when attempting to open " + "the saved file. See below:"); openError.showAndWait(); } } } }); service.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Alert writeCancelled = new Alert(AlertType.INFORMATION); writeCancelled.setTitle("Cancelled writing to file"); writeCancelled.setHeaderText("User cancelled writing primers to file."); writeCancelled.showAndWait(); } }); service.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Alert writeFailed = getExceptionDialog(e.getSource().getException()); writeFailed.setTitle("Write Failed"); writeFailed.setHeaderText("Could not write ouput file."); writeFailed.setContentText( "Exception encountered when attempting to write " + "results to file. See below:"); writeFailed.showAndWait(); } }); service.start(); }
From source file:com.github.svrtm.xlreport.ABuilder.java
License:Apache License
void init(final Workbook wb, final int maxrow, final Class<?> rowClass) { this.maxrow = maxrow; this.wb = wb; this.sheet = wb.createSheet(); this.cacheFont = new HashMap<Font_p, Font>(2); this.cacheCellStyle = new HashMap<CellStyle_p, CellStyle>(); this.cacheDataFormat = new HashMap<String, Short>(2); this.rowClass = rowClass; }
From source file:com.github.wnameless.workbookaccessor.WorkbookReader.java
License:Apache License
/** * Creates a {@link WorkbookReader} by given {@link Workbook}. Assumes there * is a header included in the spreadsheet. * /*from w w w . jav a 2 s . c o m*/ * @param workbook * a {@link Workbook} */ public WorkbookReader(Workbook workbook) { this.workbook = workbook; if (workbook.getNumberOfSheets() == 0) workbook.createSheet(); sheet = workbook.getSheetAt(0); setHeader(); }
From source file:com.github.wnameless.workbookaccessor.WorkbookReaderTest.java
License:Apache License
@Test public void testConstructor() { assertTrue(reader instanceof WorkbookReader); assertTrue(readerNH instanceof WorkbookReader); assertTrue(new WorkbookReader(new File(BASE_DIR + "PII_20130328154417.xls")) instanceof WorkbookReader); Workbook wb = new HSSFWorkbook(); wb.createSheet(); assertTrue(new WorkbookReader(wb) instanceof WorkbookReader); assertTrue(WorkbookReader.open(wb) instanceof WorkbookReader); assertTrue(WorkbookReader.open(new File(BASE_DIR + "PII_20130328154417.xls")) instanceof WorkbookReader); assertTrue(WorkbookReader.open(BASE_DIR + "PII_20130328154417.xls") instanceof WorkbookReader); }
From source file:com.github.wnameless.workbookaccessor.WorkbookWriter.java
License:Apache License
/** * Creates a {@link WorkbookWriter} by given {@link Workbook}. * /* w w w . j av a 2 s.co m*/ * @param workbook * a {@link Workbook} */ public WorkbookWriter(Workbook workbook) { this.workbook = workbook; if (workbook.getNumberOfSheets() == 0) workbook.createSheet(); sheet = workbook.getSheetAt(0); }
From source file:com.github.wnameless.workbookaccessor.WorkbookWriterTest.java
License:Apache License
@Test public void testConstructor() { assertTrue(writer instanceof WorkbookWriter); assertTrue(new WorkbookWriter(new HSSFWorkbook()) instanceof WorkbookWriter); Workbook wb = new HSSFWorkbook(); wb.createSheet(); assertTrue(new WorkbookWriter(wb) instanceof WorkbookWriter); assertTrue(WorkbookWriter.open(wb) instanceof WorkbookWriter); assertTrue(WorkbookWriter.openXLSX() instanceof WorkbookWriter); assertTrue(WorkbookWriter.openXLS() instanceof WorkbookWriter); }
From source file:com.globalsight.everest.qachecks.DITAQAChecker.java
License:Apache License
@SuppressWarnings("unused") private Sheet getSheet(Workbook p_workbook, int index) { Sheet sheet = p_workbook.getSheetAt(index); if (sheet == null) sheet = p_workbook.createSheet(); return sheet; }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.CommentsAnalysisReportGenerator.java
License:Apache License
private Sheet getSheet(Workbook p_workbook, int index) { Sheet sheet = p_workbook.getSheetAt(index); if (sheet == null) sheet = p_workbook.createSheet(); return sheet; }