Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:itpreneurs.itp.report.archive.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file./*  w  w w.j  a v a2s. c  om*/
 */
private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList<String>>();

    System.out.println("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.workbook.getNumberOfSheets();
    // and then iterate through them.
    for (int i = 0; i < numSheets; i++) {

        // Get a reference to a sheet and check to see if it contains
        // any rows.
        sheet = this.workbook.getSheetAt(i);

        if (sheet.getPhysicalNumberOfRows() > 0) {

            // Note down the index number of the bottom-most row and
            // then iterate through all of the rows on the sheet starting
            // from the very first row - number 1 - even if it is missing.
            // Recover a reference to the row and then call another method
            // which will strip the data from the cells and build lines
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

From source file:jacobi.test.util.JacobiDataSource.java

License:Open Source License

/**
 * Get all matrices defined in the specified worksheet.
 * @param name  Name of the worksheet//from www.j a  va2s  .c  o  m
 * @return   Matrices defined
 */
public Map<Integer, Matrix> get(String name) {
    Throw.when().isNull(() -> this.workbook.getSheet(name), () -> "Worksheet " + name + " not found.");
    Map<Integer, Matrix> data = new TreeMap<>();
    Sheet sheet = this.workbook.getSheet(name);
    int k = sheet.getFirstRowNum();
    while (k <= sheet.getLastRowNum()) {
        Integer id = this.getIdFromAnchor(sheet.getRow(k++));
        if (id == null) {
            continue;
        }
        Matrix matrix = this.createMatrix(sheet.getRow(k++));
        k = this.readMatrixElements(sheet, k, matrix);
        data.put(id, matrix);
    }
    return data;
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ?//from  w w w. j  a va 2s.c  o  m
 *
 * @param sheetIndex
 * @return
 */
public int getMaxColumn(int sheetIndex) {
    if (sheetIndex >= sheets) {
        return 0;
    }
    int maxCol = 0;
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    int maxRows = sheet.getLastRowNum();
    int cols = 0;
    Row row = null;
    for (int i = 0; i < maxRows; i++) {
        row = sheet.getRow(i);
        if (row != null) {
            cols = row.getLastCellNum();
            if (cols > maxCol) {
                maxCol = cols;
            }
        }
    }
    return maxCol + 1;
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ?Excel/*from  ww w.  jav a  2 s .com*/
 *
 * @param sheetIndex 0
 * @param rowIndex 0
 * @return
 */
private Row getRow(int sheetIndex, int rowIndex) {
    if (sheetIndex < sheets) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (rowIndex <= sheet.getLastRowNum()) {
            return sheet.getRow(rowIndex);
        }
    }
    return null;
}

From source file:jexcel4py.Jexcel4py.java

public int getRows() {
    Sheet rdSheet = wbRead.getSheetAt(0);
    int rdRows = rdSheet.getLastRowNum();// ?
    return rdRows;
}

From source file:jexcel4py.Jexcel4py.java

public void deliveryExcel(String strInExcelName) throws IOException {
    wbRead = readExcel(strInExcelName);//from ww w .j a v  a 2 s  .c  om
    wbWrite = new HSSFWorkbook();
    Sheet wrSheet = wbWrite.createSheet();
    Sheet rdSheet = wbRead.getSheetAt(0);
    int rdRows = rdSheet.getLastRowNum();// ?
    for (int i = 0; i <= rdRows; i++) {
        Row wrRow = wrSheet.createRow(i);
        Row row = rdSheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            Cell rdCell = row.getCell(j);
            //                System.out.print(rdCell + "\t");

            Cell wrCell = wrRow.createCell(j);
            copyCell(rdCell, wrCell);

        }
        //            System.out.println();
    }

    //        File fileOut = new File(strOutExcelName);
    //        if(fileOut.exists()){
    //            fileOut.delete();
    //        }
    //        FileOutputStream fosFileOut = new FileOutputStream(strOutExcelName);
    //        wbWrite.write(fosFileOut);
    //        fosFileOut.close();
    //        wbRead.close();
    //        wbWrite.close();
}

From source file:jp.co.orangeright.crossheadofficesample2.jsf.ItemFileInterfaceContoroller.java

public String createTodenHomeItemExcel() {
    int count = 0;
    try {//  ww  w  . j a v  a2 s.  c om
        File excelFile = this.getFile("todenhome" + this.dataFile.getSubmittedFileName());
        Workbook workbook = WorkbookFactory.create(excelFile);
        Sheet sheet = workbook.getSheet("Sheet1");
        for (int rowNumber = sheet.getFirstRowNum() + 1; rowNumber <= sheet.getLastRowNum(); rowNumber++) {
            String itemCd = this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(0));
            ItemSearchCondition itemCondition = new ItemSearchCondition();
            itemCondition.setItemcd(itemCd);
            List<Item> itemList = this.itemEjb.findAll(itemCondition);
            if (itemList.size() > 0) {
                //???????
            } else {
                this.itemController.prepareCreate();
                this.itemController.getSelected().setItemcd(itemCd);
                this.itemController.getSelected().setCustomerid(this.customerEjb.find(31925));
                this.itemController.getSelected().setUserid(this.userEjb.find("mitanto"));
                StringBuilder detail = new StringBuilder();
                detail.append("/****** ????? ******/");
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(0)));
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(1)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(13)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(14)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(15)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(16)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("??: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(17)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(18)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(19)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(20)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("WiFi: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(21)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("IoTNo: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(57)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("ID: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(58)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(59)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(60)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("Notion: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(61)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("Notion: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(62)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(79)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("???_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(80)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(81)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("???_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(82)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(83)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(84)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(85)));
                detail.append("/");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(86)));
                detail.append("/");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(87)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("_?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(107)));
                detail.append(System.lineSeparator());
                detail.append("_?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(108)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(109)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(110)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(111)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(112)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(113)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(114)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(115)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(116)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("__??: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(117)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(118)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(119)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(120)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                this.itemController.getSelected().setDetail(detail.toString());
                this.itemController.getSelected().setMemo("");
                this.itemController.create();
                count++;
            }
        }
        excelFile.delete();
        JsfUtil.addSuccessMessage(count + "????");
    } catch (Exception e) {
        return null;
    }
    return null;
}

From source file:jp.qpg.ExcelTo.java

License:Apache License

/**
 * excel to pdf//  w w w . ja va2s.c om
 * 
 * @param book excel workbook
 * @param out output to pdf
 * @param documentSetup document setup
 * @throws IOException I/O exception
 */
public static void pdf(Workbook book, OutputStream out, Consumer<PDFPrinter> documentSetup) throws IOException {
    Objects.requireNonNull(book);
    Objects.requireNonNull(out);
    try (PDFPrinter printer = new PDFPrinter()) {
        printer.documentSetup = Optional.ofNullable(documentSetup);
        for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) {
            Sheet sheet = book.getSheetAt(i);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (rowCount <= 0) {
                logger.info(sheet.getSheetName() + ": empty");
                continue; /* skip blank sheet */
            }
            logger.info(sheet.getSheetName() + ": " + rowCount + " rows");
            printer.println("sheet name: " + sheet.getSheetName());
            printer.println("max row index: " + sheet.getLastRowNum());
            printer.println("max column index: "
                    + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0));
            eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println(
                    '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString())
                            + "] " + value)));
            eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text)));
            printer.newPage();
        }
        printer.getDocument().save(out);
    }
}

From source file:jp.qpg.ExcelTo.java

License:Apache License

/**
 * excel to text/*from  w  w w  . j  a v a  2  s .  c om*/
 * 
 * @param book excel workbook
 * @param out output to text
 */
public static void text(Workbook book, OutputStream out) {
    Objects.requireNonNull(book);
    Objects.requireNonNull(out);
    try (PrintStream printer = Try.to(() -> new PrintStream(out, true, System.getProperty("file.encoding")))
            .get()) {
        for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) {
            Sheet sheet = book.getSheetAt(i);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (rowCount <= 0) {
                logger.info(sheet.getSheetName() + ": empty");
                continue; /* skip blank sheet */
            }
            logger.info(sheet.getSheetName() + ": " + rowCount + " rows");
            printer.println("sheet name: " + sheet.getSheetName());
            printer.println("max row index: " + sheet.getLastRowNum());
            printer.println("max column index: "
                    + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0));
            eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println(
                    '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString())
                            + "] " + value)));
            sheet.getCellComments().entrySet().forEach(entry -> {
                printer.println("[comment " + entry.getKey() + "] " + entry.getValue().getString());
            });
            eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text)));
            printer.println("--------");
        }
    }
}

From source file:kaflib.utils.FileUtils.java

License:Open Source License

/**
 * Reads a spreadsheet to a set of matrices (one per worksheet).
 * @param file// w ww  .  j  av  a  2s  .com
 * @return
 * @throws Exception
 */
public static Map<String, Matrix<String>> readXLSX(final File file, final boolean columnTitles)
        throws Exception {
    Map<String, Matrix<String>> matrices = new HashMap<String, Matrix<String>>();
    Workbook workbook = null;
    workbook = new XSSFWorkbook(new FileInputStream(file));

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        String name = workbook.getSheetName(i);
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        Matrix<String> matrix = new Matrix<String>();

        int start = 0;
        if (columnTitles) {
            Row row = sheet.getRow(0);
            if (row != null) {
                List<String> labels = new ArrayList<String>();
                for (int k = 0; k < row.getLastCellNum(); k++) {
                    labels.add(row.getCell(k).toString());
                }
                matrix.setColumnLabels(labels);
            }
            start = 1;
        }
        for (int j = start; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            for (int k = 0; k <= row.getLastCellNum(); k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    matrix.set(j - start, k, cell.toString());
                }
            }
        }
        matrices.put(name, matrix);
    }
    workbook.close();
    return matrices;
}