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

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

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

Introduction

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

Prototype

int getPhysicalNumberOfRows();

Source Link

Document

Returns the number of physically defined rows (NOT the number of rows in the sheet)

Usage

From source file:com.example.poi.ToCSV.java

License:Apache License

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

    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:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * Purpose of this method to read an Microsoft Workbook in DataCollection dc
 * supplied along with workbook. Each sheet will be a grid in dc with the
 * same name as sheet name.//from   w w w.  ja  v a  2  s . com
 * 
 * @param wb
 *            This is an instance of MS excel workbook(i.e .xls or .xlsx)
 *            created by POI WorkbookFactory.
 * @param dc
 */
public void readAWorkbook(Workbook wb, DataCollection dc) {
    if (wb == null || dc == null) {
        throw new IllegalArgumentException(XLSReader.ILLEGAL_ARGUMENT);
    }

    int nbrSheets = wb.getNumberOfSheets();
    String sheetName = null;
    String gridName = dc.getTextValue("gridName", null);
    Sheet sheet = null;

    int nbrColumns = -1;
    int nbrPhysicalRows = 0;

    for (int k = 0; k < nbrSheets; k++) {

        sheet = wb.getSheetAt(k);
        sheetName = sheet.getSheetName();
        nbrPhysicalRows = sheet.getPhysicalNumberOfRows();
        if (nbrPhysicalRows < 2) {
            Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS);
            // dc.addMessage(XLSReader.INSUFFICIENT_ROWS, sheetName +
            // XLSReader.INSUFFICIENT_DATA_ROWS);
            continue;
        }

        try {
            nbrColumns = this.readASheet(sheet);
            /**
             * swallow all the exceptions during excel sheet reading and put
             * appropriate message. While reading excel following exceptions
             * can come: 1. IllegalStateExcetion if column data type
             * mismatch in excel sheet. 2. ExilityException etc.
             */
        } catch (ExilityException e) {
            String msg = this.replaceMessageParams(XLSReader.EXCEPTION_MSG,
                    new String[] { sheetName, e.getMessage() });
            dc.addError(msg);
            Spit.out(e);
        }

        if (nbrColumns == -1) {
            continue;
        }

        /**
         * This is for little more flexibility to user if they have only one
         * sheet to be read and has supplied a gridName along with service
         * then let set first sheet one as given gridName(In case of simple
         * file upload and read content as grid)
         */
        if (gridName != null) {
            sheetName = gridName;
            gridName = null;
        }

        dc.addGrid(sheetName, this.getGrid());
        Spit.out(sheetName + " added to dc with " + this.rows.size() + " row(s)");
        this.columnsData.clear();
        this.rows.clear();

        // this.printXlSRec(dc.getGrid(sheetName).getRawData());

    }
}

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * Purpose of this method to read rows from given Excel Sheet.
 * /*from w w  w  . j av a2  s  . c  o  m*/
 * @param sheet
 *            an Instance of .ss.usermodel.Sheet class from POI apache.
 * @return -1 if fail to read sheet else number of columns read successfully
 *         from the sheet.
 * @throws ExilityException
 */

public int readASheet(Sheet sheet) throws ExilityException {
    int nonEmptyFirstRowIdx = 0;
    int lastRowIdx = 0;

    int nbrPhysicalRows = sheet.getPhysicalNumberOfRows();
    String sheetName = sheet.getSheetName();

    if (nbrPhysicalRows < 2) {
        Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS);
        return -1;
    }

    try {
        nonEmptyFirstRowIdx = sheet.getFirstRowNum();
        lastRowIdx = sheet.getLastRowNum();

        /*
         * For checking to valid header.First row must be header.
         */

        Row headerRow = sheet.getRow(nonEmptyFirstRowIdx);
        int nbrCol = headerRow.getPhysicalNumberOfCells();

        for (int colIdx = 0; colIdx < nbrCol; colIdx++) {
            Cell hCell = headerRow.getCell(colIdx);

            if (hCell == null || hCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                Spit.out("Error--->Found blank column " + (colIdx + 1) + " in Sheet " + sheetName
                        + XLSReader.INVALID_HEADER);
                this.columnsData.clear();
                return -1;
            }

            String columnName = hCell.getStringCellValue();
            this.setDataType(columnName, colIdx);
        }

    } catch (Exception e) {
        Spit.out(sheetName + XLSReader.INVALID_HEADER);
        Spit.out(e);
        return -1;
    }

    int nbrColumnsInARow = this.columnsData.size();

    /*
     * Loop starts with second data row that is first row(header as column
     * name) excluded.
     */
    Spit.out(sheetName + ":\n");
    for (int rowIdx = (nonEmptyFirstRowIdx + 1); rowIdx <= lastRowIdx; rowIdx++) {
        Row row = sheet.getRow(rowIdx);
        if (row == null) {
            Spit.out(XLSReader.SKIP_BLANK_ROW + rowIdx);
            continue;
        }
        /**
         * readARow() will throws ExilityException if something goes wrong.
         */
        this.readARow(row, nbrColumnsInARow);
    }

    return this.columnsData.size();

}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/**
 * //  ww w.  j a  v  a  2 s  .  c o  m
 * @param inputStream
 * @param dc
 * @return
 */
private List<Sheet> getSheets(InputStream inputStream, DataCollection dc) {
    List<Sheet> sheets = new ArrayList<Sheet>();
    Workbook workbook = null;
    boolean valuesSheetFound = false;
    try {
        workbook = WorkbookFactory.create(inputStream);
        int n = workbook.getNumberOfSheets();
        for (int i = 0; i < n; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int nbrRows = sheet.getPhysicalNumberOfRows();
            String sheetName = sheet.getSheetName();
            if (nbrRows > 0) {
                sheets.add(sheet);

                if (!valuesSheetFound && sheetName.equals(CommonFieldNames.VALUES_TABLE_NAME)) {
                    /*
                     * this is supposed to be the first one. swap it if
                     * required
                     */
                    if (i != 0) {
                        sheets.add(i, sheets.get(0));
                        sheets.add(0, sheet);
                    }
                    valuesSheetFound = true;
                }
            }

        }
    } catch (Exception e) {
        String msg = "Error while reading spread sheet. " + e.getMessage();
        Spit.out(msg);
        if (dc != null) {
            dc.addError(msg);
        }
    }
    return sheets;
}

From source file:com.ggvaidya.scinames.ui.DatasetImporterController.java

License:Open Source License

private void displayPreview() {
    filePreviewTextArea.setText("");
    if (currentFile == null)
        return;//from  w ww .  j a  va  2s .  c o  m

    if (currentFile.getName().endsWith("xls") || currentFile.getName().endsWith("xlsx")) {
        // Excel files are special! We need to load it special and then preview it.
        ExcelImporter imp;

        String excelPreviewText;
        try {
            imp = new ExcelImporter(currentFile);
            List<Sheet> sheets = imp.getWorksheets();

            StringBuffer preview = new StringBuffer();
            preview.append("Excel file version " + imp.getWorkbook().getSpreadsheetVersion() + " containing "
                    + sheets.size() + " sheets.\n");
            for (Sheet sh : sheets) {
                preview.append(
                        " - " + sh.getSheetName() + " contains " + sh.getPhysicalNumberOfRows() + " rows.\n");

                // No rows?
                if (sh.getPhysicalNumberOfRows() == 0)
                    continue;

                // Header row?
                Row headerRow = sh.getRow(0);
                boolean headerEmitted = false;

                for (int rowIndex = 1; rowIndex < sh.getPhysicalNumberOfRows(); rowIndex++) {
                    if (rowIndex >= 10)
                        break;

                    Row row = sh.getRow(rowIndex);

                    if (!headerEmitted) {
                        preview.append(
                                "  - " + String.join("\t", ExcelImporter.getCellsAsValues(headerRow)) + "\n");
                        headerEmitted = true;
                    }
                    preview.append("  - " + String.join("\t", ExcelImporter.getCellsAsValues(row)) + "\n");
                }

                preview.append("\n");
            }

            excelPreviewText = preview.toString();
        } catch (IOException ex) {
            excelPreviewText = "Could not open '" + currentFile + "': " + ex;
        }

        filePreviewTextArea.setText(excelPreviewText);

        return;
    }

    // If we're here, then this is some sort of text file, so let's preview the text content directly.
    try {
        LineNumberReader reader = new LineNumberReader(new BufferedReader(new FileReader(currentFile)));

        // Load the first ten lines.
        StringBuffer head = new StringBuffer();
        for (int x = 0; x < 10; x++) {
            head.append(reader.readLine());
            head.append('\n');
        }

        reader.close();
        filePreviewTextArea.setText(head.toString());
    } catch (IOException ex) {
        filePreviewTextArea.setBackground(BACKGROUND_RED);
        filePreviewTextArea.setText("ERROR: Could not load file '" + currentFile + "': " + ex);
    }
}

From source file:com.github.xiilei.ecdiff.Processor.java

License:Apache License

public void diff() {
    try {//ww  w . j ava  2 s.c  o m
        logger.info("start ,src:" + job.getSrc() + ",dist:" + job.getDist());
        Store store = this.getStoreFromSrc();
        Workbook wb = readExcelFileByext(job.getDist());
        this.font = wb.createFont();
        this.font.setColor((short) 0xa);
        int rows_len = 0, i = 0, max_cells_len = 0;
        Sheet sheet = wb.getSheetAt(job.getDistSheet());
        rows_len = sheet.getPhysicalNumberOfRows();
        logger.info("Dist,open " + sheet.getSheetName() + " with " + rows_len + " rows");
        for (i = 0; i < rows_len; i++) {
            Row row = sheet.getRow(i);
            max_cells_len = row.getPhysicalNumberOfCells();
            if (!job.checkDistIndex(max_cells_len)) {
                logger.warn("Dist,The length of columns is too small at row " + i + ",length:" + max_cells_len);
                continue;
            }
            if (job.isByrow()) {
                cellComparer(store.get(i), row.getCell(job.getDistColumnIndex()));
            } else {
                cellComparer(store.get(getStringCellValue(row.getCell(job.getDistColumnIdIndex()))),
                        row.getCell(job.getDistColumnIndex()));
            }
        }
        try (FileOutputStream out = new FileOutputStream(job.getOutFileName())) {
            wb.write(out);
        }
        logger.info("output file:" + job.getOutFileName());
    } catch (Exception e) {
        logger.fatal(e.getMessage(), e);
        //                e.printStackTrace();
    }
}

From source file:com.github.xiilei.ecdiff.Processor.java

License:Apache License

public Store getStoreFromSrc() throws IOException {
    Workbook wb = readExcelFileByext(job.getSrc());
    Row row = null;/*  w ww .ja v  a  2s .c om*/
    int max_cells_len = 0;
    int rows_len = 0;
    Sheet sheet = wb.getSheetAt(job.getSrcSheet());
    rows_len = sheet.getPhysicalNumberOfRows();
    Store store = new Store(rows_len);
    logger.info("Src,open " + sheet.getSheetName() + " with " + rows_len + " rows");
    for (int i = 0; i < rows_len; i++) {
        row = sheet.getRow(i);
        max_cells_len = row.getPhysicalNumberOfCells();
        if (!job.checkSrcIndex(max_cells_len)) {
            logger.warn("Src,The length of columns is too small at row " + i + ",length:" + max_cells_len);
            continue;
        }
        if (job.isByrow()) {
            store.put(i, row.getCell(job.getSrcColumnIndex()));
        } else {
            store.put(getStringCellValue(row.getCell(job.getSrcColumnIdIndex())),
                    row.getCell(job.getSrcColumnIndex()));
        }
    }
    return store;
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.SummaryReportGenerator.java

License:Apache License

/**
 * Creates Leveraging Sheet/*from   ww  w  . j a  v a2  s .c  om*/
 */
private void createCostsSheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options,
        Map<String, ReportWordCount> p_wordCounts) throws Exception {
    int rowLen = p_sheet.getPhysicalNumberOfRows();
    int colLen = p_sheet.getRow(2).getPhysicalNumberOfCells();
    int wordTotalCol = colLen - 2;
    int row = ROWNUMBER, column = colLen - 1;
    int costCol;
    Map<String, Double> p_ratesMap = null;
    for (int r = 2; r < rowLen + ROWNUMBER; r++) {
        Row theRow = getRow(p_sheet, r);
        theRow.removeCell(getCell(theRow, column));
    }
    p_sheet.removeColumnBreak(column);
    // Rates Columns
    for (int dis = column - 1; column < colLen + dis - 2; column++) {
        Cell cell_From = p_sheet.getRow(row).getCell(column - dis);
        Cell cell_To = getCell(p_sheet.getRow(row), column);
        cell_To.setCellValue(cell_From.getStringCellValue());
        cell_To.setCellStyle(cell_From.getCellStyle());
        p_sheet.setColumnWidth(column, p_sheet.getColumnWidth(column - dis));
        // Adds Rates for Match Type
        for (int rateRow = row + 1; rateRow <= rowLen; rateRow++) {
            String matchType = p_sheet.getRow(ROWNUMBER).getCell(column).getStringCellValue();
            String targetLocale = p_sheet.getRow(rateRow).getCell(0).getStringCellValue();
            double rate = getRate(matchType, targetLocale, p_ratesMap);
            addNumberCell(p_sheet, column, rateRow, rate, getMoneyStyle(p_workbook));
        }
    }

    // Cost Columns Head
    costCol = column;
    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostWithLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostWithLeveraging.setCellValue(bundle.getString("lb_report_costWithLeveraging"));
    cell_CostWithLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostNoLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostNoLeveraging.setCellValue(bundle.getString("lb_report_costNoLeveraging"));
    cell_CostNoLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 15 * 256);
    Cell cell_Savings = getCell(getRow(p_sheet, row), column++);
    cell_Savings.setCellValue(bundle.getString("lb_savings"));
    cell_Savings.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_Percent = getCell(getRow(p_sheet, row), column++);
    cell_Percent.setCellValue("%");
    cell_Percent.setCellStyle(getHeaderOrangeStyle(p_workbook));
    // Cost Columns Data
    for (row = ROWNUMBER + 1; row < (rowLen + ROWNUMBER); row++) {
        String leveragingForm = getCostWithLeveraging(1, wordTotalCol - 1, wordTotalCol, (row + 1));
        String noLeveragingForm = getColumnName(wordTotalCol) + (row + 1) + "*"
                + getColumnName(wordTotalCol + 5) + (row + 1);
        String savingForm = getColumnName(costCol + 1) + (row + 1) + "-" + getColumnName(costCol) + (row + 1);
        String percent = getColumnName(costCol + 2) + (row + 1) + "/" + getColumnName(costCol + 1) + (row + 1);

        Row theRow = getRow(p_sheet, row);
        Cell cell_Leveraging = getCell(theRow, costCol);
        cell_Leveraging.setCellFormula(leveragingForm);
        cell_Leveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_NoLeveraging = getCell(theRow, costCol + 1);
        cell_NoLeveraging.setCellFormula(noLeveragingForm);
        cell_NoLeveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_Saving = getCell(theRow, costCol + 2);
        cell_Saving.setCellFormula(savingForm);
        cell_Saving.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_PercentData = getCell(theRow, costCol + 3);
        cell_PercentData.setCellFormula(percent);
        cell_PercentData.setCellStyle(getPercentStyle(p_workbook));
    }

    if (rowLen > 1) {
        row = rowLen + 1;
        column = 1;
        for (; column < colLen - 1; column++) {
            Cell cell_Total = getCell(getRow(p_sheet, row), column);
            cell_Total.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
            cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }
        for (; column < costCol; column++) {
            Cell cell = getCell(getRow(p_sheet, row), column);
            cell.setCellValue("");
            cell.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }

        // Summary Cost Columns
        Cell cell_SumLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumNoLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumNoLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumNoLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumSaving = getCell(getRow(p_sheet, row), column);
        cell_SumSaving.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumSaving.setCellStyle(getMoneySumStyle(p_workbook));

        String percent = getColumnName(column - 1) + (row + 1) + "/" + getColumnName(column - 2) + (row + 1);
        Cell cell_AvgPercent = getCell(getRow(p_sheet, row), column);
        cell_AvgPercent.setCellFormula(percent);
        cell_AvgPercent.setCellStyle(getPercentSumStyle(p_workbook));
    }
}

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

public void addTableFromSheet(final Connection conn, final Sheet sheet) throws SQLException {
    final int numRows = sheet.getPhysicalNumberOfRows();
    if (numRows < 2) {
        //Not enough or can't determine
    }//  ww w  .j av  a 2s.  c  o m
    final List<Entry<String, ExcelType>> types = extractTypes(sheet);
    final String tableName = Utils.cleanUp(sheet.getSheetName());
    {
        final String dropStatement = "DROP TABLE IF EXISTS `" + tableName + "`;";
        conn.createStatement().execute(dropStatement);
        System.out.println(dropStatement);
    }
    {
        final String createStatement = getCreateTable(tableName, types);
        System.out.println(createStatement);
        conn.createStatement().execute(createStatement);
    }

    int rowCount = 0;
    for (final Row row : new IteratorWrapper<Row>(sheet.iterator())) {
        if (rowCount > 0) {
            final String insert = createInsert(tableName, types, row);
            if (insert != null) {
                System.out.println(insert);
                conn.createStatement().execute(insert);
            }
        }
        rowCount++;
    }
}

From source file:com.ifeng.vdn.ip.repository.app.IPCheckApp.java

License:Apache License

public static void main(String[] args) {
    Workbook wb = null;//from w w  w  . j  a  va2 s .  c  o m
    PrintWriter pw = null;
    try {

        pw = new PrintWriter(new FileOutputStream("src/test/resources/data/CDN_BAD.txt"), true);

        AliIPAddressChecker ipChecker = new AliIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/CDN_BAD.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        //Cell locCell = null;

        List<String> ips = new ArrayList<String>();

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(0);

            ips.add(ipCell.getStringCellValue());
        }

        for (String ip : ips) {
            AliIPBean bean = (AliIPBean) ipChecker.ipcheck(ip);
            pw.println(ip + "-" + bean.getIpString());
        }

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        if (pw != null) {
            pw.flush();
            pw.close();
        }
    }

}