List of usage examples for org.apache.poi.ss.usermodel Row getPhysicalNumberOfCells
int getPhysicalNumberOfCells();
From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java
License:Open Source License
private TableModel parseExcel(final URL sourceURL, int size, TableCellRenderer renderer, final Sheet sheet, int startLine) throws IOException { if (size == -1) size = Integer.MAX_VALUE; int maxCol = 0; final Vector<Object> data = new Vector<Object>(); int rowCount = 0; Row row; while (((row = sheet.getRow(rowCount)) != null) && (rowCount < size)) { if (rowCount >= startLine) { Vector<Object> rowVector = new Vector<Object>(); if (maxCol < row.getPhysicalNumberOfCells()) { maxCol = row.getPhysicalNumberOfCells(); }//from w w w.j a v a 2 s. c o m for (short j = 0; j < maxCol; j++) { Cell cell = row.getCell(j); if (cell == null) { rowVector.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowVector.add(cell.getRichStringCellValue().getString()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { final Double dblValue = cell.getNumericCellValue(); final Integer intValue = dblValue.intValue(); if (intValue.doubleValue() == dblValue) { rowVector.add(intValue.toString()); } else { rowVector.add(dblValue.toString()); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowVector.add(Boolean.toString(cell.getBooleanCellValue())); } else if ((cell.getCellType() == Cell.CELL_TYPE_BLANK) || (cell.getCellType() == Cell.CELL_TYPE_ERROR)) { rowVector.add(null); } else { rowVector.add(null); } } data.add(rowVector); } rowCount++; } return new DefaultTableModel(data, this.getDefaultColumnNames(maxCol, sourceURL)); }
From source file:eu.esdihumboldt.hale.io.csv.ui.LookupTablePage.java
License:Open Source License
private String[] readHeader() { LookupTableImport provider = getWizard().getProvider(); List<String> items = new ArrayList<String>(); try {// w w w . j av a 2 s .c o m if (provider instanceof CSVLookupReader) { CSVReader reader = CSVUtil.readFirst(getWizard().getProvider()); return reader.readNext(); } else { Workbook workbook; // write xls file String file = provider.getSource().getLocation().getPath(); String fileExtension = file.substring(file.lastIndexOf("."), file.length()); if (fileExtension.equals(".xls")) { workbook = new HSSFWorkbook(provider.getSource().getInput()); } // write xlsx file else if (fileExtension.equals(".xlsx")) { workbook = new XSSFWorkbook(provider.getSource().getInput()); } else return new String[0]; Sheet sheet = workbook.getSheetAt(0); Row currentRow = sheet.getRow(0); for (int cell = 0; cell < currentRow.getPhysicalNumberOfCells(); cell++) { items.add(currentRow.getCell(cell).getStringCellValue()); } return items.toArray(new String[0]); } } catch (IOException e) { return new String[0]; } }
From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java
License:Open Source License
private void checkHeader(Sheet sheet, List<String> headerNames) throws Exception { Row header = sheet.getRow(sheet.getFirstRowNum()); assertEquals("There are not enough header cells.", headerNames.size(), header.getPhysicalNumberOfCells()); for (Cell cell : header) { assertTrue("Not expecting header cell value.", headerNames.contains(cell.getStringCellValue())); }/*from w ww. java 2s .c o m*/ }
From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java
License:Open Source License
private void checkFirstDataRow(Sheet sheet, List<String> firstDataRow) { Row datarow = sheet.getRow(sheet.getFirstRowNum() + 1); assertEquals("There are not enough data cells.", firstDataRow.size(), datarow.getPhysicalNumberOfCells()); for (Cell cell : datarow) { assertTrue("Not expecting data value.", firstDataRow.contains(cell.getStringCellValue())); }/*from w ww . j a v a 2 s. c o m*/ }
From source file:features.UfmCreateXmlFilesForTestData.java
/** * Read the test data in Excel data file and convert data to HashMap and return as output. * @param locOfFile: location of test data file(Excel file with UFM test cases information). * argument is a specifier that is relative to the url argument. *///www . j a v a 2 s .com public static HashMap getUfmTestData(String locOfFile) { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream(new File(locOfInputUfmExcelFile)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); int noOfRows = sheet.getLastRowNum(); //System.out.println("no of rows:" + noOfRows); int i = 0; Row rowWithColumnNames = sheet.getRow(2); int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells(); //System.out.println(noOfColumns); String testCaseName = ""; String columnNamesAndValuesOfOneRow = ""; //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // System.out.println(rowIterator for (int m = 0; m < noOfRows; m++) { //System.out.println("Ieration number : " + m); Row rowCurrent = rowIterator.next(); if (m <= 3) { continue; } testCaseName = String.valueOf(rowCurrent.getCell(0)); // System.out.println("test case name " + testCaseName); for (int p = 0; p < 84; p++) { //Igonre the columns without any column name in test case excel file if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") { continue; } columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":" + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";"; } rowData.put(testCaseName, columnNamesAndValuesOfOneRow); columnNamesAndValuesOfOneRow = ""; } file.close(); return rowData; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:FLCSS.floridaautomationtestsuite.ufm.UfmCreateXmlFilesForTestData.java
/** * Read the test data in Excel data file and convert data to HashMap and return as output. * @param locOfFile: location of test data file(Excel file with UFM test cases information). * argument is a specifier that is relative to the url argument. *///from ww w . j a v a 2s .co m public static HashMap getUfmTestData(String locOfFile) { HashMap<String, String> rowData = new HashMap<String, String>(); try { DataFormatter formatter = new DataFormatter(); FileInputStream file = new FileInputStream( new File("C:\\Users\\23319\\Downloads\\FLCSS-java(5)\\a1\\src\\QATP_R0.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); int noOfRows = sheet.getLastRowNum(); //System.out.println("no of rows:" + noOfRows); int i = 0; Row rowWithColumnNames = sheet.getRow(2); int noOfColumns = rowWithColumnNames.getPhysicalNumberOfCells(); //System.out.println(noOfColumns); String testCaseName = ""; String columnNamesAndValuesOfOneRow = ""; // HashMap<String,String> headerColumnNames = new HashMap<String,String>(); // //int[][] rowWithData = new int[5][]; // for (i = 0; i < 84; i++) { // // System.out.println("hello"); // headerColumnNames.put(formatter.formatCellValue((rowWithColumnNames.getCell(i))),""); // // System.out.println(headerColumnNames.size()); // } //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // System.out.println(rowIterator for (int m = 0; m < noOfRows; m++) { //System.out.println("Ieration number : " + m); Row rowCurrent = rowIterator.next(); if (m <= 3) { continue; } testCaseName = String.valueOf(rowCurrent.getCell(0)); // System.out.println("test case name " + testCaseName); for (int p = 0; p < 84; p++) { //Igonre the columns without any column name in test case excel file if (formatter.formatCellValue(rowWithColumnNames.getCell(p)) == "") { continue; } columnNamesAndValuesOfOneRow = columnNamesAndValuesOfOneRow + formatter.formatCellValue((rowWithColumnNames.getCell(p))).trim() + ":" + formatter.formatCellValue((rowCurrent.getCell(p))).trim() + ";"; } rowData.put(testCaseName, columnNamesAndValuesOfOneRow); columnNamesAndValuesOfOneRow = ""; } file.close(); return rowData; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:github.srlee309.lessWrongBookCreator.excelReader.SummaryFileReader.java
License:Open Source License
private ArrayList<BookSummarySection> getBookSummarySections(Workbook wb) { ArrayList<BookSummarySection> bookSummarySections = new ArrayList<BookSummarySection>(); HashSet<String> bookNames = new HashSet<String>(); HashSet<String> sequenceNames = new HashSet<String>(); Sheet postsSheet = wb.getSheetAt(0); Iterator<Row> rowIterator = postsSheet.iterator(); String currBook = ""; String currSequence = ""; if (rowIterator.hasNext()) { rowIterator.next(); // skip first row with column headers while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); int column = 0; // increment the column we are looking for the value from if the book, sequence or title are not provided column += Math.abs(row.getPhysicalNumberOfCells() - row.getLastCellNum()); PostSummarySection postSummarySection = new PostSummarySection(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); column++;/*from ww w . j a v a 2 s. com*/ if (cell.getCellType() == Cell.CELL_TYPE_STRING) { switch (column) { case 1: currBook = cell.getStringCellValue(); break; case 2: currSequence = cell.getStringCellValue(); break; case 3: postSummarySection.setTitle(cell.getStringCellValue()); break; case 4: postSummarySection.setUrl(cell.getStringCellValue()); break; case 5: postSummarySection.setSummary(cell.getStringCellValue()); break; } } } if (!bookNames.contains(currBook)) { BookSummarySection bookSummarySection = new BookSummarySection(currBook); bookSummarySections.add(bookSummarySection); bookNames.add(currBook); } if (sequenceNames.contains(currSequence)) { for (BookSummarySection bookSummarySection : bookSummarySections) { SequenceSummarySection sequenceSummarySection = bookSummarySection .getSequenceSummarySection(currSequence); if (sequenceSummarySection != null) { if (!postSummarySection.getUrl().isEmpty()) { sequenceSummarySection.addPostSummarySection(postSummarySection); } } } } else { if (!postSummarySection.getUrl().isEmpty()) { SequenceSummarySection sequenceSummarySection = new SequenceSummarySection(currSequence); sequenceSummarySection.addPostSummarySection(postSummarySection); for (BookSummarySection bookSummarySection : bookSummarySections) { if (bookSummarySection.getTitle().equals(currBook)) { bookSummarySection.addSequenceSummarySection(sequenceSummarySection); } } sequenceNames.add(currSequence); } } } HashMap<String, String> sequenceTitleAndSummaries = new HashMap<String, String>(); HashMap<String, String> bookTitlesAndSummaries = new HashMap<String, String>(); if (wb.getNumberOfSheets() == 1) { logger.info( "There is no second sheet or third sheet found. Therefore, there are no sequence or book summaries found. Perhaps, the excel file is not in the proper format." + newLine); } else if (wb.getNumberOfSheets() == 2) { logger.info( "There is no third sheet found. Therefore, there are no book summaries found. Perhaps, the excel file is not in the proper format." + newLine); sequenceTitleAndSummaries = getTitlesAndSummaries(wb.getSheetAt(1)); } else { sequenceTitleAndSummaries = getTitlesAndSummaries(wb.getSheetAt(1)); bookTitlesAndSummaries = getTitlesAndSummaries(wb.getSheetAt(2)); } for (BookSummarySection bookSummarySection : bookSummarySections) { String bookSummary = bookTitlesAndSummaries.get(bookSummarySection.getTitle()); if (bookSummary != null) { bookSummarySection.setSummary(bookSummary); } for (SequenceSummarySection sequenceSummarySection : bookSummarySection .getSequenceSummarySections()) { String sequenceSummary = sequenceTitleAndSummaries.get(sequenceSummarySection.getTitle()); if (sequenceSummary != null) { sequenceSummarySection.setSummary(sequenceSummary); } } } } else { logger.info( "There were no rows found in the first sheet. Therefore, no posts were found. Perhaps, the excel file is not in the proper format" + newLine); } return bookSummarySections; }
From source file:ie.cmrc.tabular.excel.ExcelTable.java
License:Apache License
/** * Extracts the header of a given sheet in the form of a {@code FieldMap<Integer>} that stores the indexes of each field. * The header must be in the first row of the sheet. * @param sheet Excel Sheet ({@code org.apache.poi.ss.usermodel.Sheet}) to extract the header from * @return A hash map mapping each colum name with its index. If the sheet is null or empty or the first row is empty, then and empty {@link ie.cmrc.tabular.FieldMapHeader} is returned. *///from ww w. j ava 2 s .c om private FieldMapHeader<Integer> parseHeader(Sheet sheet) { FieldMapHeader<Integer> sheetHeader = new FieldMapHeader<Integer>(); if (sheet != null) { int rows = sheet.getPhysicalNumberOfRows(); if (rows > 0) { Row row = sheet.getRow(0); if (row != null) { int n = row.getPhysicalNumberOfCells(); if (n > 0) { for (int i = 0; i < n; i++) { Cell cell = row.getCell(i); if (cell != null) { ExcelTableCell sc = new ExcelTableCell(cell); String colName = sc.getStringValue(); if (colName != null) colName = colName.trim(); Term field = new Term(colName); sheetHeader.put(field, i); } } } } } } return sheetHeader; }
From source file:io.unravellingtechnologies.excalibur.Sheet.java
License:Open Source License
/** * Initializes the sheet header structure. * // w w w . j av a 2 s.c o m * @param sheet Sheet POI object used to initialize the header of this sheet. */ private void setSheetHeader(XSSFSheet sheet) { if (logger.isDebugEnabled()) { logger.debug("Setting sheet header..."); } org.apache.poi.ss.usermodel.Row firstRow = sheet.getRow(sheet.getFirstRowNum()); if (firstRow.getPhysicalNumberOfCells() == 0) { return; } for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) { Cell cell = it.next(); sheetHeader.put(cell.getColumnIndex(), cell.getStringCellValue()); } if (logger.isDebugEnabled()) { logger.debug("Finished setting the sheet header."); } }
From source file:it.cineca.pst.huborcid.service.AbstractFileService.java
License:Open Source License
@Transactional(propagation = Propagation.REQUIRES_NEW) public void uploadFileOrcid(MultipartFile file, Application application, String typeEntity) throws IOException { log.debug(String.format("Method uploadFileOrcid START, file.name=[%s]", file.getOriginalFilename())); Integer maxColumn = 0;//from w w w . j a v a2 s . c o m ResultUploadOrcidEntity resultUploadOrcid = new ResultUploadOrcidEntity(); try { resultUploadOrcid.setApplication(application); resultUploadOrcid.setFileNameUpload(file.getOriginalFilename()); resultUploadOrcid.setStatus("PROGRESS"); resultUploadOrcid.setEntityType(typeEntity); resultUploadOrcidEntityRepository.save(resultUploadOrcid); OrcidAccessToken orcidAccessToken = new OrcidAccessToken(); InputStream fileInputStream = new BufferedInputStream(file.getInputStream()); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); boolean withErrors = false; Iterator<Row> rowIterator = sheet.iterator(); OrcidOAuthClient clientOrcid = new OrcidOAuthClient(orcidApiType); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == 0) { maxColumn = row.getPhysicalNumberOfCells(); } else { try { String valueCellLocalId = ""; String valueCellOrcid = ""; Cell cell = row.getCell(0); if (cell != null) { valueCellLocalId = cell.getStringCellValue(); } cell = row.getCell(1); if (cell != null) { valueCellOrcid = cell.getStringCellValue(); } List<RelPersonApplication> listPersApp = relPersonApplicationRepository .findAllByApplicationIsAndLastIsTrueAndOrcidIsOrLocalIdIs(application, valueCellOrcid, valueCellLocalId); if (listPersApp.size() == 1) { RelPersonApplication persApp = listPersApp.get(0); orcidAccessToken.setAccess_token(persApp.getOauthAccessToken()); orcidAccessToken.setOrcid(persApp.getPerson().getOrcid()); createAppendEntity(clientOrcid, orcidAccessToken, sheet, row); writeResultRow(row, maxColumn, "", true); } else if (listPersApp.size() == 0) { writeResultRow(row, maxColumn, "Utente non trovato", false); withErrors = true; } else if (listPersApp.size() > 1) { writeResultRow(row, maxColumn, "Errore di sistema: anagrafiche duplicate", false); withErrors = true; } } catch (Exception e) { writeResultRow(row, maxColumn, e.getMessage(), false); withErrors = true; } } } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); workbook.close(); byte[] fileResult = baos.toByteArray(); resultUploadOrcid.setStatus("COMPLETED"); resultUploadOrcid.setWithErrors(withErrors); resultUploadOrcid.setFileResult(fileResult); resultUploadOrcidEntityRepository.save(resultUploadOrcid); } catch (Exception e) { log.debug(String.format("Method uploadFileOrcid, exception=[%s]", e.getMessage())); resultUploadOrcid.setStatus("ERROR"); resultUploadOrcidEntityRepository.save(resultUploadOrcid); } log.debug("Method uploadFileOrcid END"); }