Example usage for org.apache.poi.ss.usermodel Row getLastCellNum

List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getLastCellNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getLastCellNum.

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:fll.util.ExcelCellReader.java

License:Open Source License

/**
 * @see fll.util.CellFileReader#readNext()
 *//*from   w w  w  .j a v a 2s  . c o  m*/
@SuppressFBWarnings(value = "PZLA_PREFER_ZERO_LENGTH_ARRAYS", justification = "Return null rather than zero length array so that we know when we hit EFO")
public String[] readNext() throws IOException {
    if (lineNumber >= sheet.getLastRowNum()) {
        return null;
    }

    ++lineNumber;
    final Row row = sheet.getRow(lineNumber);
    if (null == row) {
        return new String[0];
    }

    final List<String> data = new LinkedList<String>();
    for (int cellIdx = 0; cellIdx < row.getLastCellNum(); ++cellIdx) {
        final Cell cell = row.getCell(cellIdx, Row.RETURN_NULL_AND_BLANK);
        if (null == cell) {
            data.add(null);
        } else {
            final String str;
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                final double d = cell.getNumericCellValue();
                // test if a date!
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // make sure to format times like we expect them
                    final Date date = HSSFDateUtil.getJavaDate(d);
                    str = TournamentSchedule.DATE_FORMAT_AM_PM_SS.get().format(date);
                } else {
                    // check for integer
                    if (FP.equals(d, Math.round(d), 1e-10)) {
                        str = String.valueOf((int) d);
                    } else {
                        str = formatter.formatCellValue(cell, formulaEvaluator);
                    }
                }
            } else {
                str = formatter.formatCellValue(cell, formulaEvaluator);
            }
            data.add(str);
        }
    }
    return data.toArray(new String[data.size()]);
}

From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java

/**
* Given a sheet, this method deletes a column from a sheet and moves
* all the columns to the right of it to the left one cell.
*
* Note, this method will not update any formula references.
*
* @param sheet//www.j  a v  a  2 s .c om
* @param column
*/
private static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int iii = 0; iii < sheet.getLastRowNum() + 1; iii++) {
        Row row = sheet.getRow(iii);
        // if no row exists here; then nothing to do; next!
        if (row == null) {
            continue;
        }

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn) {
            maxColumn = lastColumn;
        }

        if (lastColumn < columnToDelete) {
            continue;
        }

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null) {
                row.removeCell(oldCell);
            }
            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int ccc = 0; ccc < maxColumn; ccc++) {
        sheet.setColumnWidth(ccc, sheet.getColumnWidth(ccc + 1));
    }
}

From source file:fsart.diffTools.converter.ToCSV.java

License:Apache License

/**
 * Called to convert a row of cells into a line of data that can later be
 * output to the CSV file./*from   w  ww .jav  a2s .  co m*/
 *
 * @param row An instance of either the HSSFRow or XSSFRow classes that
 *            encapsulates information about a row of cells recovered from
 *            an Excel workbook.
 */
private void rowToCSV(Row row) {
    Cell cell = null;
    int lastCellNum = 0;
    ArrayList<String> csvLine = new ArrayList<String>();

    // Check to ensure that a row was recovered from the sheet as it is
    // possible that one or more rows between other populated rows could be
    // missing - blank. If the row does contain cells then...
    if (row != null) {

        // Get the index for the right most cell on the row and then
        // step along the row from left to right recovering the contents
        // of each cell, converting that into a formatted String and
        // then storing the String into the csvLine ArrayList.
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                csvLine.add("");
            } else {
                if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
                    csvLine.add(this.formatter.formatCellValue(cell));
                } else {
                    csvLine.add(this.formatter.formatCellValue(cell, this.evaluator));
                }
            }
        }
        // Make a note of the index number of the right most cell. This value
        // will later be used to ensure that the matrix of data by the CSV file
        // is square.
        if (lastCellNum > this.maxRowWidth) {
            this.maxRowWidth = lastCellNum;
        }
    }
    this.csvData.add(csvLine);
}

From source file:generate.CopyRow.java

/**
 * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is
 * already occupied, shift all rows down to make room.
 *
 * @param workbook//from w  w  w.  jav  a 2 s .  c  om
 * @param worksheet
 * @param from
 * @param to
 */
public static void copyRow(Workbook workbook, Sheet worksheet, Integer from, Integer to) {
    Row sourceRow = worksheet.getRow(from);
    Row newRow = worksheet.getRow(to);

    if (alreadyExists(newRow))
        worksheet.shiftRows(to, worksheet.getLastRowNum(), 1, true, true);
    else {
        newRow = worksheet.createRow(to);
    }
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);
        if (oldCell != null) {
            copyCellStyle(workbook, oldCell, newCell);
            copyCellComment(oldCell, newCell);
            copyCellHyperlink(oldCell, newCell);
            copyCellDataTypeAndValue(oldCell, newCell);
        }
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
}

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   www  . j  av a 2s.c o  m*/
                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:gov.anl.cue.arcane.engine.matrix.MatrixModel.java

License:Open Source License

/**
 * Reads the dimensions for a matrix model from a spreadsheet.
 * This method is necessary because Excel spreadsheets
 * do not reliably store the row and column dimension
 * in the meta-information. The values that are stored
 * there are not guaranteed to be correct in all cases.
 *
 * @param fileName            the file name
 * @return the matrix dimensions//from ww  w  . j a  v  a  2 s  . co m
 */
public static MatrixDimensions readDimensions(String fileName) {

    // Create the results holder.
    MatrixDimensions matrixDimensions = new MatrixModel.MatrixDimensions();

    // Try to read the spreadsheet.
    try {

        // Attempt to open the spreadsheet.
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName)));

        // Scan the sheets.
        Iterator<XSSFSheet> sheets = workbook.iterator();

        // Skip the first sheet.
        XSSFSheet sheet = sheets.next();

        // Move to the sheet for the first variable.
        sheet = sheets.next();

        // Find the number of rows.
        matrixDimensions.rows = sheet.getLastRowNum();

        // Prepare to check the first row.
        Iterator<Row> rowIterator = sheet.iterator();

        // Check the header row length
        Row row = rowIterator.next();
        matrixDimensions.columns = row.getLastCellNum() - 2;

        // Close the workbook.
        workbook.close();

        // Catch errors.
    } catch (Exception e) {

        // Note an error.
        matrixDimensions = null;

    }

    // Return the results.
    return matrixDimensions;

}

From source file:gov.nih.nci.cananolab.util.ExcelParser.java

License:BSD License

/**
 * Parse secondary StanShaw Excel spreadsheet and store data in a 3-layer map.
 * 1st layer: sample map, key is sample name (261-13-4), value is the 2nd layer map.
 * 2nd layer: assay map, key is assay name (Aorta 1), value is the 3rd layer map.
 * 3rd layer: datum map, there are always 3 entries in this map, for example,  
 *            key is datum name Median (M), value is 9.02194E-08.
 *            key is datum name Mean (M), value is 7.96025E-08.
 *            key is datum name SEM (M), value is 6.12968E-09.
 *  /*  w w  w. j  ava 2s .  co  m*/
 * @param fileName
 * @return a 3-layer map
 * @throws IOException
 */
public SortedMap<String, SortedMap<String, SortedMap<String, Double>>> twoWayParse(String fileName)
        throws IOException {
    InputStream inputStream = null;
    SortedMap<String, SortedMap<String, SortedMap<String, Double>>> dataMatrix = new TreeMap<String, SortedMap<String, SortedMap<String, Double>>>();
    try {
        inputStream = new BufferedInputStream(new FileInputStream(fileName));
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        Workbook wb = new HSSFWorkbook(fs);
        Sheet sheet1 = wb.getSheetAt(0);
        //printSheet(sheet1);
        // Sheet must contain >= 2 rows (header + data).
        if (sheet1.getLastRowNum() < 1) {
            return dataMatrix;
        }
        // Sheet must contain >= 5 columns (assay, sample + 3 datums).   
        Row firstRow = sheet1.getRow(0);
        if (firstRow.getLastCellNum() < 4) {
            return dataMatrix;
        }
        // Iterate sheet from 2nd row and populate the data matrix.
        for (int rowIndex = 1; rowIndex <= sheet1.getLastRowNum(); rowIndex++) {
            Row row = sheet1.getRow(rowIndex);

            //1.get sampleName key for 1st layer map, assayName key for 2 layer map.
            String sampleName = row.getCell(1).getStringCellValue();
            String assayName = row.getCell(0).getStringCellValue();

            //2.find sampleMap in dataMatrix, if null create & store new sampleMap.
            SortedMap<String, SortedMap<String, Double>> sampleMap = dataMatrix.get(sampleName);
            if (sampleMap == null) {
                sampleMap = new TreeMap<String, SortedMap<String, Double>>();
                dataMatrix.put(sampleName, sampleMap);
            }

            //3.find assayMap in sampleMap, if null create & store new assayMap.
            SortedMap<String, Double> assayMap = sampleMap.get(assayName);
            if (assayMap == null) {
                assayMap = new TreeMap<String, Double>();
                sampleMap.put(assayName, assayMap);
            }

            //4.iterate row from col-2 to last column, store datum value.
            for (int colIndex = 2; colIndex <= row.getLastCellNum(); colIndex++) {
                Cell cell = row.getCell(colIndex);
                if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String datumName = firstRow.getCell(colIndex).getStringCellValue();
                    assayMap.put(datumName, cell.getNumericCellValue());
                }
            }
        }
    } finally {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (Exception e) {
            }
        }
        //this.print2ndMatrix(dataMatrix);
    }
    return dataMatrix;
}

From source file:gov.nih.nci.evs.app.neopl.ExcelToCSV.java

License:Open Source License

private void rowToCSV(Row row) {
    Cell cell = null;/* w  w w  .  j  av  a  2 s  . co m*/
    int lastCellNum = 0;
    ArrayList<String> csvLine = new ArrayList<String>();
    if (row != null) {
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i <= lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                csvLine.add("");
            } else {
                csvLine.add(this.formatter.formatCellValue(cell));
            }
        }
        if (lastCellNum > this.maxRowWidth) {
            this.maxRowWidth = lastCellNum;
        }
    }
    this.csvData.add(csvLine);
}

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

/**
 * returns the index of the next row to read
 * @param sheet/*ww  w.  j  av  a 2 s . c  o  m*/
 * @return
 * @throws IOException 
 */
private int readHeaders(Sheet sheet) throws IOException {
    //in version 1 of the spreadsheet - 
    //row 0 is just a comment
    //row 1 has headers
    //in version 2 of the spreadsheet, row 0 has headers (different headers) than v1

    int rowIndex = 0;
    while (true) {
        columnHeaders.clear();
        Row row = sheet.getRow(rowIndex++);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell c = row.getCell(i);
            String cellText = (c == null ? "" : toString(c).trim());

            if (i == 1) {
                if (cellText.equals("Timestamp")) {
                    version = 2;
                } else if (cellText.equals("Date")) {
                    version = 1;
                } else if (cellText.length() > 0) {
                    throw new IOException("Unsupported spreadsheet format!");
                }
            }
            columnHeaders.add(cellText);
        }
        if (version > 0) {
            break;
        }
        if (rowIndex > 2) {
            throw new IOException("Failure finding headers!");
        }
    }
    return rowIndex;
}

From source file:gridgrid.Web.java

License:Apache License

private synchronized void load(File file) throws IOException {
    if (file.lastModified() > lastModified) {
        map = new HashMap<>();
        InputStream is = new FileInputStream(file);
        Workbook book = new XSSFWorkbook(is);
        Sheet sheet = book.getSheetAt(0);
        int pathCelNum = -1;
        int scriptCellNum = -1;
        int viewCellNum = -1;

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                if (row.getLastCellNum() >= 1 && pathCelNum == -1) {
                    for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
                        if (cell != null) {
                            switch (cell.getStringCellValue()) {
                            case "":
                                pathCelNum = cellIndex;
                                break;
                            case "JavaScript":
                                scriptCellNum = cellIndex;
                                break;
                            case "":
                                viewCellNum = cellIndex;
                                break;
                            }/*from w  w  w  . ja  va 2 s  . co m*/
                        }
                    }

                }

                if (pathCelNum != -1 && row.getCell(pathCelNum) != null && row.getCell(scriptCellNum) != null
                        && row.getCell(viewCellNum) != null) {
                    Cell code = row.getCell(scriptCellNum);
                    String codeStr = code != null ? code.toString() : "";
                    Cell view = row.getCell(viewCellNum);
                    String viewStr = view != null ? view.toString() : "";
                    String path = row.getCell(pathCelNum).toString();
                    map.put(path, new CodeView(codeStr, viewStr));
                }
            }
        }
        is.close();
        lastModified = file.lastModified();
    }
}