List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected boolean isValidSpreadsheet(Workbook workBook) throws IllegalStateException { boolean isValid = false; // get version information from registration sheet Sheet regSheet = workBook.getSheet(SHEET_TYPE_REGISTRATION); log.debug("Validating: {}", regSheet.getSheetName()); isValid = isValidRegistrationSheet(regSheet); // continue validating result sheets if (isValid) { for (int i = 0; i < workBook.getNumberOfSheets(); i++) { Sheet sheet = workBook.getSheetAt(i); if (sheet != null && !SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName()) && !SHEET_TYPE_DUMMY.equals(sheet.getSheetName())) { log.debug("Validating: {}", sheet.getSheetName()); isValid = isValidResultSheet(sheet); if (!isValid) { log.warn("[{}] Invalid result sheet", sheet.getSheetName()); break; }//from www . ja va2 s .c om } } } else { log.warn("[{}] Invalid registration sheet", regSheet.getSheetName()); } return isValid; }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected List<Event> parseEvents(Workbook workBook) throws IllegalStateException { evaluator = workBook.getCreationHelper().createFormulaEvaluator(); List<Event> events = new CopyOnWriteArrayList<Event>(); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { Sheet sheet = workBook.getSheetAt(i); if (isValidResultSheet(sheet)) { log.debug("Parsing: {}", sheet.getSheetName()); Row firstRow = sheet.getRow(4); // first row with event results if (firstRow != null) { Cell cell = firstRow.getCell(1); // first cell with event results if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content Event event = parseEventDetails(sheet); event.setLive(workBook.getActiveSheetIndex() == i); List<Result> results = new CopyOnWriteArrayList<Result>(); for (int j = 4; j < sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { Result result = parseResultRow(row, event); if (result.getFirstname() != null && result.getSurname() != null) { results.add(result); }//from w w w. j a v a 2s . c o m } } if (!results.isEmpty()) { event.setResults(results); events.add(event); } } } } } return events; }
From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.ImportTextTableDialog.java
License:Open Source License
/** * Load from the data source.<br>//from ww w . j a v a2s. c om * * @param evt * @throws Exception */ private void importButtonActionPerformed(ActionEvent evt) throws Exception { if (checkDataSourceError() == false) return; boolean importAll = importAllCheckBox.isSelected(); /* * Get start line number. If "transfer" check box is true, then start * reading from the second line. */ int startLineNumber; final int spinnerNumber = Integer.parseInt(startRowSpinner.getValue().toString()); if (transferNameCheckBox.isSelected()) { startLineNumber = spinnerNumber; } else { startLineNumber = spinnerNumber - 1; } final String commentChar = commentLineTextField.getText(); /* * Get import flags */ final int colCount = previewPanel.getPreviewTable().getColumnModel().getColumnCount(); importFlag = new boolean[colCount]; for (int i = 0; i < colCount; i++) { importFlag[i] = ((AttributePreviewTableCellRenderer) previewPanel.getPreviewTable().getCellRenderer(0, i)).getImportFlag(i); } /* * Get Attribute Names */ final String[] attributeNames; final List<String> attrNameList = new ArrayList<String>(); Object curName = null; for (int i = 0; i < colCount; i++) { curName = previewPanel.getPreviewTable().getColumnModel().getColumn(i).getHeaderValue(); if (attrNameList.contains(curName)) { int dupIndex = 0; for (int idx = 0; idx < attrNameList.size(); idx++) { if (curName.equals(attrNameList.get(idx))) { dupIndex = idx; break; } } if (importFlag[i] && importFlag[dupIndex]) { final JLabel label = new JLabel("Duplicate Attribute Name Found: " + curName); label.setForeground(Color.RED); JOptionPane.showMessageDialog(this, label); return; } } if (curName == null) { attrNameList.add("Column " + i); } else { attrNameList.add(curName.toString()); } } attributeNames = attrNameList.toArray(new String[0]); /* * Get attribute data types */ // final byte[] attributeTypes = new byte[previewPanel.getPreviewTable() // .getColumnCount()]; final Byte[] test = previewPanel.getDataTypes(previewPanel.getSelectedSheetName()); final Byte[] attributeTypes = new Byte[test.length]; for (int i = 0; i < test.length; i++) { attributeTypes[i] = test[i]; } // for (int i = 0; i < attributeTypes.length; i++) { // attributeTypes[i] = attributeDataTypes.get(i); // } final List<Integer> aliasList = new ArrayList<Integer>(); String mappingAttribute = ID; if (dialogType != NETWORK_IMPORT) { /* * Get column indecies for alias */ JTable curTable = aliasTableMap.get(previewPanel.getSelectedSheetName()); if (curTable != null) { for (int i = 0; i < curTable.getModel().getRowCount(); i++) { if ((Boolean) curTable.getModel().getValueAt(i, 0) == true) { aliasList.add(i); } } } /* * Get mapping attribute */ mappingAttribute = mappingAttributeComboBox.getSelectedItem().toString(); } ObjectType objType = null; if (dialogType != NETWORK_IMPORT) { if (nodeRadioButton.isSelected()) { objType = NODE; } else if (edgeRadioButton.isSelected()) { objType = EDGE; } else { objType = NETWORK; } } /* * Switch readers based on the dialog type. */ switch (dialogType) { case SIMPLE_ATTRIBUTE_IMPORT: /* * Case 1: Attribute table import. */ // Extract URL from the text table. final URL source = new URL(targetDataSourceTextField.getText()); // Make sure primary key index is up-to-date. keyInFile = primaryKeyComboBox.getSelectedIndex(); // Build mapping parameter object. final AttributeMappingParameters mapping; final List<String> del; System.out.println("IsCytoscapeAttributeFile " + previewPanel.isCytoscapeAttributeFile(source)); if (previewPanel.isCytoscapeAttributeFile(source)) { del = new ArrayList<String>(); del.add(" += +"); } else { del = checkDelimiter(); } mapping = new AttributeMappingParameters(objType, del, listDelimiter, keyInFile, mappingAttribute, aliasList, attributeNames, attributeTypes, listDataTypes, importFlag, caseSensitive); if (source.toString().endsWith(SupportedFileType.EXCEL.getExtension()) || source.toString().endsWith(SupportedFileType.OOXML.getExtension())) { /* * Read one sheet at a time */ InputStream is = null; Workbook wb = null; try { is = source.openStream(); wb = WorkbookFactory.create(is); } finally { if (is != null) { is.close(); } } // Load all sheets in the table for (int i = 0; i < wb.getNumberOfSheets(); i++) { final Sheet sheet = wb.getSheetAt(i); loadAnnotation(new ExcelAttributeSheetReader(sheet, mapping, startLineNumber, importAll), source.toString()); } } else { loadAnnotation(new DefaultAttributeTableReader(source, mapping, startLineNumber, null, importAll), source.toString()); } break; case ONTOLOGY_AND_ANNOTATION_IMPORT: /* * Case 2: Import Ontology and its annotation. */ final String selectedOntologyName = ontologyComboBox.getSelectedItem().toString(); final String ontologySourceLocation = ontologyUrlMap.get(selectedOntologyName); /* * If selected ontology is not loaded, load it first. */ if (Cytoscape.getOntologyServer().getOntologyNames().contains(selectedOntologyName) == false) loadOntology(ontologySourceLocation, selectedOntologyName); /* * Now, load & map annotation. */ final String annotationSource = annotationUrlMap.get(annotationComboBox.getSelectedItem()); final URL annotationSourceUrl = new URL(annotationSource); if (previewPanel.getFileType() == FileTypes.GENE_ASSOCIATION_FILE) { /* * This is a Gene Association file. */ GeneAssociationReader gaReader = null; keyInFile = this.primaryKeyComboBox.getSelectedIndex(); InputStream is = null; try { is = URLUtil.getInputStream(annotationSourceUrl); gaReader = new GeneAssociationReader(selectedOntologyName, is, mappingAttribute, importAll, keyInFile, caseSensitive); } catch (Exception e) { if (is != null) { is.close(); } throw e; } loadGeneAssociation(gaReader, selectedOntologyName, annotationSource); } else { /* * This is a custom annotation file. */ final int ontologyIndex = ontologyInAnnotationComboBox.getSelectedIndex(); final AttributeAndOntologyMappingParameters aoMapping = new AttributeAndOntologyMappingParameters( objType, checkDelimiter(), listDelimiter, keyInFile, mappingAttribute, aliasList, attributeNames, attributeTypes, listDataTypes, importFlag, ontologyIndex, selectedOntologyName, caseSensitive); final OntologyAnnotationReader oaReader = new OntologyAnnotationReader(annotationSourceUrl, aoMapping, commentChar, startLineNumber); loadAnnotation(oaReader, annotationSource); } break; case NETWORK_IMPORT: /* * Case 3: read as network table (Network + Edge Attributes) */ // Extract URL from the text table. /* * Now multiple files are supported. */ URL[] sources = new URL[inputFiles.length]; for (int i = 0; i < sources.length; i++) { sources[i] = inputFiles[i].toURI().toURL(); } //final URL networkSource = new URL(targetDataSourceTextField.getText()); final int sourceColumnIndex = networkImportPanel.getSourceIndex(); final int targetColumnIndex = networkImportPanel.getTargetIndex(); final String defaultInteraction = defaultInteractionTextField.getText(); final int interactionColumnIndex = networkImportPanel.getInteractionIndex(); final NetworkTableMappingParameters nmp = new NetworkTableMappingParameters(checkDelimiter(), listDelimiter, attributeNames, attributeTypes, null, importFlag, sourceColumnIndex, targetColumnIndex, interactionColumnIndex, defaultInteraction); NetworkTableReader reader; String networkName; boolean multi = true; if (sources.length == 1) multi = false; for (int i = 0; i < sources.length; i++) { if (sources[i].toString().endsWith(SupportedFileType.EXCEL.getExtension()) || sources[i].toString().endsWith(SupportedFileType.OOXML.getExtension())) { // Extract name from the sheet name. InputStream is = null; Workbook wb = null; try { is = sources[i].openStream(); wb = WorkbookFactory.create(is); } finally { if (is != null) { is.close(); } } Sheet sheet = wb.getSheetAt(0); networkName = wb.getSheetName(0); reader = new ExcelNetworkSheetReader(networkName, sheet, nmp, startLineNumber); } else { // Get name from URL. if ((commentChar != null) && (commentChar.length() != 0) && transferNameCheckBox.isSelected()) { startLineNumber++; } final String[] parts = sources[i].toString().split("/"); networkName = parts[parts.length - 1]; reader = new NetworkTableReader(networkName, sources[i], nmp, startLineNumber, commentChar); } loadNetwork(networkName, reader, sources[i], multi); } if (multi) { StringBuilder builder = new StringBuilder(); builder.append("The following networks are loaded:\n\n"); for (File f : inputFiles) { builder.append(f.getName() + "\n"); } JOptionPane.showMessageDialog(this, builder.toString(), "Multiple Networks Loaded", JOptionPane.INFORMATION_MESSAGE); } break; default: return; } Cytoscape.firePropertyChange(Cytoscape.ATTRIBUTES_CHANGED, null, null); dispose(); }
From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java
License:Open Source License
/** * Load file and show preview.// w w w . j a va 2s. c o m * * @param sourceURL * @param delimiters * @param renderer * renderer for this table. Can be null. * @param size * @param commentLineChar * TODO * @param startLine * TODO * @throws IOException */ public void setPreviewTable(URL sourceURL, List<String> delimiters, TableCellRenderer renderer, int size, final String commentLineChar, final int startLine) throws IOException { TableCellRenderer curRenderer = renderer; if ((commentLineChar != null) && (commentLineChar.trim().length() != 0)) this.commentChar = commentLineChar; else this.commentChar = null; /* * If rendrer is null, create default one. */ if (curRenderer == null) { curRenderer = new AttributePreviewTableCellRenderer(0, new ArrayList<Integer>(), AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST, AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST, null, TextFileDelimiters.PIPE.toString()); } /* * Reset current state */ for (int i = 0; i < tableTabbedPane.getTabCount(); i++) tableTabbedPane.removeTabAt(i); previewTables = new HashMap<String, JTable>(); TableModel newModel; fileTypeLabel.setVisible(true); if (sourceURL.toString().endsWith(SupportedFileType.EXCEL.getExtension()) || sourceURL.toString().endsWith(SupportedFileType.OOXML.getExtension())) { fileTypeLabel.setIcon(SPREADSHEET_ICON.getIcon()); fileTypeLabel.setText("Excel" + '\u2122' + " Workbook"); InputStream is = null; final Workbook wb; try { is = sourceURL.openStream(); wb = WorkbookFactory.create(is); } catch (InvalidFormatException e) { e.printStackTrace(); throw new IllegalArgumentException("Could not read Excel file. Maybe the file is broken?"); } finally { if (is != null) is.close(); } if (wb.getNumberOfSheets() == 0) throw new IllegalStateException("No sheet found in the workbook."); /* * Load each sheet in the workbook. */ logger.debug("# of Sheets = " + wb.getNumberOfSheets()); Sheet sheet = wb.getSheetAt(0); logger.debug("Sheet name = " + wb.getSheetName(0) + ", ROW = " + sheet.rowIterator().hasNext()); newModel = parseExcel(sourceURL, size, curRenderer, sheet, startLine); if (newModel.getRowCount() == 0) throw new IllegalStateException("No data found in the Excel sheet."); DataTypeUtil.guessTypes(newModel, wb.getSheetName(0), dataTypeMap); listDataTypeMap.put(wb.getSheetName(0), initListDataTypes(newModel)); addTableTab(newModel, wb.getSheetName(0), curRenderer); } else { if (isCytoscapeAttributeFile(sourceURL)) { fileTypeLabel.setText("Cytoscape Attribute File"); fileTypeLabel.setIcon(new ImageIcon(Cytoscape.class.getResource("images/icon48.png"))); newModel = parseText(sourceURL, size, curRenderer, null, 1); } else { fileTypeLabel.setText("Text File"); fileTypeLabel.setIcon(TEXT_FILE_ICON.getIcon()); newModel = parseText(sourceURL, size, curRenderer, delimiters, startLine); } String[] urlParts = sourceURL.toString().split("/"); final String tabName = urlParts[urlParts.length - 1]; DataTypeUtil.guessTypes(newModel, tabName, dataTypeMap); listDataTypeMap.put(tabName, initListDataTypes(newModel)); addTableTab(newModel, tabName, curRenderer); } if (getFileType() == FileTypes.GENE_ASSOCIATION_FILE) { fileTypeLabel.setText("Gene Association"); fileTypeLabel.setToolTipText("This is a fixed-format Gene Association file."); } loadFlag = true; }
From source file:eu.esdihumboldt.hale.io.xls.ui.XLSInstanceImportConfigurationPage.java
License:Open Source License
/** * @see eu.esdihumboldt.hale.ui.HaleWizardPage#onShowPage(boolean) *///from ww w . j a va2s . co m @Override protected void onShowPage(boolean firstShow) { if (!firstShow) { setErrorMessage(null); } try { Workbook wb = WorkbookFactory.create(getWizard().getProvider().getSource().getInput()); int numberOfSheets = wb.getNumberOfSheets(); String[] items = new String[numberOfSheets]; for (int i = 0; i < numberOfSheets; i++) { items[i] = wb.getSheetAt(i).getSheetName(); } sheetSelection.setItems(items); } catch (Exception e) { setErrorMessage("Cannot load Excel file!"); setPageComplete(false); return; } super.onShowPage(firstShow); sheetSelection.select(0); setPageComplete(true); }
From source file:eu.esdihumboldt.hale.io.xls.ui.XLSSchemaTypePage.java
License:Open Source License
@Override protected void onShowPage(boolean firstShow) { URI newLocation = getWizard().getProvider().getSource().getLocation(); if (!firstShow && newLocation != null && !newLocation.equals(oldLocation)) { sheetNum = 0;/*from ww w . ja v a2s . c o m*/ } try { Workbook wb = WorkbookFactory.create(getWizard().getProvider().getSource().getInput()); int numberOfSheets = wb.getNumberOfSheets(); if (sheetNum >= numberOfSheets) { sheetNum = 0; } ArrayList<String> items = new ArrayList<String>(); for (int i = 0; i < numberOfSheets; i++) { items.add(wb.getSheetAt(i).getSheetName()); // only add items if there is a header (no empty sheet) Row row = wb.getSheetAt(i).getRow(0); if (row == null && newLocation != null && !newLocation.equals(oldLocation)) { sheetNum++; } } sheet.setItems(items.toArray(new String[items.size()])); sheet.select(sheetNum); // try to update update(sheetNum); super.onShowPage(firstShow); // Overwrite super string field editor value Sheet sheet = wb.getSheetAt(sheetNum); setStringFieldEditorValue(sheet.getSheetName()); oldLocation = newLocation; } catch (OldExcelFormatException e) { // the setup is not in a valid state clearPage(); clearSuperPage(); setErrorMessage( "Old excel format detected (format 5.0/7.0 (BIFF5)). Please convert the excel file to BIFF8 from Excel versions 97/2000/XP/2003."); setPageComplete(false); } catch (Exception e) { clearPage(); clearSuperPage(); setErrorMessage("Excel file cannot be loaded!"); setPageComplete(false); } }
From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java
License:EUPL
protected List<String> getSheetNames(Workbook workbook) { return IntStream.range(0, workbook.getNumberOfSheets()).mapToObj(i -> workbook.getSheetName(i)) .collect(toList());//from w w w. j a v a2s.c om }
From source file:FilesManager.ExcelParser.java
public static List<HardCopy> readExcelData(String fileName) { List<HardCopy> fileList = new ArrayList<>(); try {//from w w w .j a va2 s . com //Create the input stream from the xlsx/xls file FileInputStream fis = new FileInputStream(fileName); //Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; if (fileName.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } //Get the number of sheets in the xlsx file int numberOfSheets = workbook.getNumberOfSheets(); //loop through each of the sheets for (int i = 0; i < numberOfSheets; i++) { //Get the nth sheet from the workbook Sheet sheet = workbook.getSheetAt(i); //every sheet has rows, iterate over them Iterator<Row> rowIterator = sheet.iterator(); int index = 0; while (rowIterator.hasNext()) { index++; if (index == 1) { rowIterator.next(); continue; } //Get the row object Row row = rowIterator.next(); //Every row has columns, get the column iterator and iterate over them Iterator<Cell> cellIterator = row.cellIterator(); HardCopy f = null; try { List<String> listStrings = new LinkedList<>(); for (int j = 0; j < 6; j++) { Cell c = row.getCell(j); if (c != null) listStrings.add(c.toString()); else listStrings.add(""); } int s = listStrings.size(); f = new HardCopy(listStrings.get(0), listStrings.get(1), listStrings.get(2), listStrings.get(3), (s > 4) ? listStrings.get(4) : "", (s > 5) ? listStrings.get(5) : ""); } catch (Exception e) { e.printStackTrace(); } fileList.add(f); } //end of rows iterator } //end of sheets for loop //close file input stream fis.close(); } catch (IOException e) { e.printStackTrace(); } int k = 1; for (HardCopy file : fileList) { file.key = k++; } return fileList; }
From source file:fll.util.ExcelCellReader.java
License:Open Source License
/** * Get the names of all sheets in the specified stream. * //from ww w .j a v a 2 s . c om * @throws IOException * @throws InvalidFormatException */ public static List<String> getAllSheetNames(final InputStream stream) throws InvalidFormatException, IOException { final List<String> sheetNames = new LinkedList<String>(); final Workbook workbook = createWorkbook(stream); final int numSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numSheets; ++i) { sheetNames.add(workbook.getSheetName(i)); } return sheetNames; }
From source file:fr.paris.lutece.plugins.appointment.service.ClosingDayService.java
License:Open Source License
/** * Import the closing dates of a given file * /* w w w .ja v a2s.c om*/ * @param item * the file in input * @return the list of the closing dates in the file * @throws IOException * if error during reading file */ public static List<LocalDate> getImportClosingDays(FileItem item) throws IOException { HashSet<LocalDate> listDays = new HashSet<LocalDate>(); FileInputStream fis = null; Workbook workbook = null; String strExtension = FilenameUtils.getExtension(item.getName()); if (StringUtils.equals(MARK_EXCEL_EXTENSION_XLSX, strExtension)) { try { fis = (FileInputStream) item.getInputStream(); // Using XSSF for xlsx format, for xls use HSSF workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); // looping over each workbook sheet for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.iterator(); // iterating over each row while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() > 1) { Iterator<Cell> cellIterator = row.cellIterator(); // Iterating over each cell (column wise) in a // particular row. while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); // The Cell Containing String will is name. if (cell.getColumnIndex() == 3) { String strdate = StringUtils.EMPTY; if (cell.getCellType() == 0) { Instant instant = cell.getDateCellValue().toInstant(); LocalDate localDate = instant.atZone(ZoneId.systemDefault()).toLocalDate(); strdate = localDate.format(Utilities.getFormatter()); } if (StringUtils.isNotEmpty(strdate) && strdate.matches(MARK_FORMAT_DATE_REGEX)) { LocalDate date = LocalDate.parse(strdate, Utilities.getFormatter()); listDays.add(date); } } } } } } } finally { if (fis != null) { fis.close(); } if (workbook != null) { workbook.close(); } } } return new ArrayList<LocalDate>(listDays); }