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

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

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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Put the next row in the dvd row given in parameter.
 * Return SCAN_COMPLETED if there is no more row in the spreadsheet, or GOT_ROW if a row was successfully put in the dvd row.
 * /*from w ww  .  j  av a 2s .c  o  m*/
 * Uses the attribute currentRow to save the previous row fetched.
 * 
 * @param sheet
 * @param dvdr
 * @param numberOfLogicalColumnsInvolved
 * @param columnIndexes
 * @return SCAN_COMPLETED or GOT_ROW
 * @throws SQLException
 */
private int createNextRow(Sheet sheet, DataValueDescriptor[] dvdr) {
    boolean gotData = false;

    /*
     * Find the next row to return.
     * 
     * currentRow should currently point to the last row returned.
     * If that's null, then start from first row.
     * Else, search for the next non-empty row (until we hit the end of the prescribed range).
     */
    if (currentRow == null)
        currentRow = sheet.getRow(firstRowIndex + (firstRowIsMetaData ? 1 : 0));
    else {
        int nextRowIndex = currentRow.getRowNum() + 1;
        currentRow = null;

        if (stopScanOnFirstEmptyRow) {
            currentRow = sheet.getRow(nextRowIndex);
        } else {
            while (currentRow == null && nextRowIndex <= lastRowIndex) {
                currentRow = sheet.getRow(nextRowIndex);
                nextRowIndex++;
            }
        }
    }

    /*
     * If we've run out of spreadsheet (currentRow == null) or gone out of the prescribed range,
     * then scan complete - return that.
     */
    if (currentRow == null || currentRow.getRowNum() > lastRowIndex) {
        return SCAN_COMPLETED;
    }

    /*
     * Get the offset of the first column in the spreadsheet.
     * Note: this is used when iterating below, so that we can correctly relate 
     * the actual column in the spreadsheet to the correct 'column' in the 
     * DataValueDescriptor [] representing the row.
     */
    int columnOffset = firstColumnIndex;

    //Figure out how many columns there are
    int numberOfColumns = lastColumnIndex - firstColumnIndex + 1;

    for (int i = 0; i < numberOfColumns; i++) {
        /*
         * Note: i is used to refer to the index of the DataValueDescriptor which represents
         * the actual spreadsheet column (at i + columnOffset) in the DataValueDescriptor[] 
         * representing this row. 
         */

        Cell cell = currentRow.getCell(i + columnOffset);

        if (cell == null) {
            dvdr[i].setToNull();
        } else {
            try {
                int cellValueType = cell.getCellType();

                if (cellValueType == Cell.CELL_TYPE_FORMULA)
                    cellValueType = cell.getCachedFormulaResultType();

                switch (cellValueType) {

                case Cell.CELL_TYPE_STRING:
                    dvdr[i].setValue(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                        dvdr[i].setValue(new java.sql.Date(cell.getDateCellValue().getTime()));
                    else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        dvdr[i].setValue(cell.getStringCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_BOOLEAN:
                    dvdr[i].setValue(cell.getBooleanCellValue());
                    break;

                default:
                    dvdr[i].setToNull();
                    break;
                }

                //If a cell has data that is not null - then flag that we actually have data to return
                if (!dvdr[i].isNull())
                    gotData = true;

            } catch (Exception e) {
                dvdr[i].setToNull();
                logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_MAP_LT_ERROR, "Excel cell [spreadsheet "
                        + sheet.getSheetName() + "; row " + cell.getRow().getRowNum() + "; column "
                        + cell.getColumnIndex() + "; value " + cell
                        + "] could not be mapped into the logical table because of the column logical type: "
                        + e);
            }
        }
    }

    if (!gotData && stopScanOnFirstEmptyRow) {
        logger.logInfo(
                "Ending GExcel table scan on first empty row (as no row limit was specified in the ending cell config constraint)");
        return SCAN_COMPLETED;
    }

    return GOT_ROW;
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * looks for the column definition and initializes the following attributes :
 * /*from  w w w  . j  a  v a 2 s . c  o  m*/
 * - numberOfColumns
 * - columnIndexes
 * - columnNames
 *
 * If a column which contains no values is ignored.
 * 
 * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet.
 * Else, they will be automatically generated : COLUMN1, COLUMN2...
 * 
 * @param sheet
 */
private void findColumns(Sheet sheet) {
    numberOfColumns = 0;

    columnIndexes = new ArrayList<Integer>();
    columnNames = new ArrayList<String>();

    Row firstRow = sheet.getRow(firstRowIndex);

    int columnLabelIndex = 1;

    if (firstRowIsMetaData) {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());

            int cellType = cell.getCellType();
            if (Cell.CELL_TYPE_FORMULA == cellType) {
                cellType = cell.getCachedFormulaResultType();
                //                System.out.println("cell type is now getCachedFormulaResultType() = " + cellType );
            }

            //Build the column names depending on it's type
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //                case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!!

                //                   System.out.println("cell type string" );

                // Note: Javadoc on method getStringCellValue() states:
                // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. 
                // For formulaCells that are not string Formulas, we throw an exception"

                ++numberOfColumns;
                columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed
                break;

            case Cell.CELL_TYPE_NUMERIC:

                //                   System.out.println("cell type numeric " + 
                //                         ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) );

                ++numberOfColumns;
                columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString()
                        : "" + cell.getNumericCellValue());
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                //                   System.out.println("cell type boolean" );

                ++numberOfColumns;
                columnNames.add("" + cell.getBooleanCellValue());
                break;

            default:

                //                   System.out.println("cell type default" );

                ++numberOfColumns;
                columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex);
                break;
            }

            columnLabelIndex++;
        }
    } else {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());
            columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++);
        }
    }
}

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  v  a 2  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();
        }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliBatchIPAddressCheckerTest.java

License:Apache License

@Test
public void wrap() {
    Workbook wb = null;//from w  w  w.  j  a  v a 2 s  .  c om
    try {
        BatchIPAddressChecker<AliIPBean> checker = new AliBatchIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_18.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(1);

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

        List<AliIPBean> locations = checker.check(ips);
        String location = "";

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

            try {
                location = locations.get(i).getIpString();
            } catch (Exception e) {
                location = "ERROR";
            }

            locCell.setCellValue(location);
        }

        /*if(location.length() == (totalRows - 1)){
                   
        }else {
           log.error("Batch executed error");
           throw new RuntimeException("Batch executed error. Some one IP location not be checked.");
        }*/

        wb.write(new FileOutputStream("src/test/resources/data/ip_18_Alibaba.xlsx"));

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

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliDataFactoryTest.java

License:Apache License

@Test
public void importDataFromExcel() {
    Workbook wb = null;/*from   ww w  . j ava  2  s . co  m*/
    String input = "src/test/resources/data/ip_18_Alibaba.xlsx";

    try {

        List<IPModel> ipList = new ArrayList<IPModel>();

        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell totalCell = null;
        Cell locCell = null;

        String location = "";
        int total;
        String ipString = "";

        String[] items = null;

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

            ipCell = row.getCell(1);
            totalCell = row.getCell(2);
            locCell = row.getCell(3);

            try {
                String country = "";
                String area = "";
                String region = "";
                String city = "";
                String isp = "";
                IPModel model = new IPModel();

                ipString = ipCell.getStringCellValue();
                total = (int) totalCell.getNumericCellValue();
                location = locCell.getStringCellValue();

                items = location.split(" ");
                if (items != null) {
                    if (items[0] != null) {
                        country = items[0];
                    }
                    if (items[1] != null) {
                        area = items[1];
                    }
                    if (items[2] != null) {
                        region = items[2];
                    }
                    if (items[3] != null) {
                        city = items[3];
                    }
                    if (items[4] != null) {
                        isp = items[4];
                    }

                    model.setIp(ipString);
                    model.setCountry(country.trim());
                    model.setArea(area.trim());
                    model.setRegion(region.trim());
                    model.setCity(city.trim());
                    model.setIsp(isp.trim());
                    model.setTotal(total);

                    ipList.add(model);
                }

                log.info("IP: {}, Total{}, location: {}", ipString, total, location);

                if ((i % 1000) == 0) {
                    aliDataFactory.importData(ipList);
                    ipList = new ArrayList<IPModel>();
                }

            } catch (Exception e) {
                e.getMessage();
                //log.error(e.getMessage(), e);

            }

        }

        if (ipList.size() > 0) {
            aliDataFactory.importData(ipList);
        }

    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliIPAddressCheckerTest.java

License:Apache License

public void wrap(String input, String output) {
    Workbook wb = null;//from   ww  w  . j  av  a2s  . c  om
    PrintWriter pw = null;
    try {
        pw = new PrintWriter(output, "UTF-8");

        IPAddressChecker checker = new AliIPAddressChecker();
        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        String location = "";
        String ipString = "";

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

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            try {
                ipString = ipCell.getStringCellValue();
                AliIPBean ip = (AliIPBean) checker.ipcheck(ipString);
                location = ip.getIpString();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                location = "ERROR";
            } finally {
                //locCell.setCellValue(location);

                pw.append(ipString + " " + location);
                pw.println();
            }
        }

        wb.write(new FileOutputStream(output));

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

From source file:com.ifeng.vdn.ip.repository.service.impl.IPAddressBaiduCheckerTest.java

License:Apache License

@Test
public void wrap() {

    Workbook wb = null;//from   w ww . j  a v a 2s .  co m

    try {
        IPAddressBaiduChecker checker = new IPAddressBaiduChecker();

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

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

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

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            BaiduIPBean ip = (BaiduIPBean) checker.ipcheck(ipCell.getStringCellValue());
            List<IPLocation> locations = ip.getData();

            if (locations != null && locations.size() > 0) {

                try {
                    locCell.setCellValue(locations.get(0).getLocation());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        wb.write(new FileOutputStream("src/test/resources/data/ip_16_loc.xlsx"));

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

}

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

private List<LogMessage> validateFieldNames(Sheet sheet) {
    List<LogMessage> result = new ArrayList<LogMessage>();
    String nombreHoja = sheet.getSheetName();
    String errDescription = "";
    Row headingRow = sheet.getRow(HEADING_ROW);

    Properties prop = new Properties();
    String propFileName = "excel/column_names.properties";

    InputStream is = null;/*from w w w .  j av  a2s  .  c o  m*/
    try {
        is = getClass().getClassLoader().getResourceAsStream(propFileName);
        if (is != null) {
            prop.load(is);
            for (int i = 1; i <= HEADING_COUNT; i++) {
                String expected = prop.getProperty(Integer.toString(i));
                log.debug("Validando [" + Integer.toString(i) + "] validada como [" + headingRow.getCell(i - 1)
                        + "]");
                String current = headingRow.getCell(i - 1) == null
                        || headingRow.getCell(i - 1).getStringCellValue() == null ? ""
                                : headingRow.getCell(i - 1).getStringCellValue();
                if (!expected.equals(current)) {
                    int charValue = 64 + i;
                    boolean aValue = false;
                    if (charValue > 90) {
                        charValue -= 25;
                        aValue = true;
                    }

                    String columnName = aValue ? "A" : "";
                    columnName += Character.toString((char) charValue);

                    errDescription = "La columna [" + nombreHoja + "]!" + columnName + " tiene el ttulo ["
                            + current + "], " + " se esperaba: [" + expected + "]";
                    log.info(errDescription);
                    result.add(new LogMessage("Validacin de encabezados", errDescription));
                } else {
                    log.debug("columna [" + expected + "] validada");
                }
            }
        } else {
            errDescription = "Imposible abrir configuracin de campos de excel (column_names.properties)";
            log.info(errDescription);
            result.add(new LogMessage("Validacin de encabezados", errDescription));
        }
    } catch (Exception e) {
        errDescription = "Error al validar campos en la hoja [" + nombreHoja + "]: [" + e.getMessage() + "]";
        log.error(errDescription, e);
        result.add(new LogMessage("Validacin de encabezados", errDescription));
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }
    return result;
}

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

private List<LogMessage> readPurchaseOrders(Sheet sheet) {
    List<LogMessage> result = new ArrayList<LogMessage>();

    pos = new ArrayList<PurchaseOrder>();
    int rowIndex = HEADING_ROW + 1;
    Row currentRow = sheet.getRow(rowIndex);
    while (null != currentRow) {
        PurchaseOrder po = getPoFromRow(currentRow);
        if (pos.contains(po)) {
            // validate equal
            PurchaseOrder existent = pos.get(pos.indexOf(po));
            if (po.matchComplete(existent))
                existent.getItems().add(po.getItems().get(0));
            else {
                String msg = String.format(
                        "La partida %d no coincide con la primer partida encontrada %d, se suponen ambas elementos de la misma orden de compra %s",
                        po.getItems().get(0).getSeqItem(), existent.getItems().get(0).getSeqItem(),
                        existent.getPoNumber());
                result.add(new LogMessage("Validacin de Ordenes de Compra", msg));
                log.debug(po.toString() + "*====*" + existent.toString());
            }/*from   w  w  w . j a  v a  2  s .  co  m*/
        } else {
            pos.add(po);
        }
        rowIndex++;
        currentRow = sheet.getRow(rowIndex);
    }

    return result;
}

From source file:com.jaspersoft.ireport.designer.connection.gui.XlsxDataSourceConnectionEditor.java

License:Open Source License

private void jButtonGetColumnsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGetColumnsActionPerformed
    try {//from w  w  w  .  j  a  v a 2 s .co  m

        if (jTextFieldFilename.getText().length() > 0) {
            Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(jTextFieldFilename.getText())));
            Sheet sheet = workbook.getSheetAt(0);

            DefaultTableModel dtm = (DefaultTableModel) jTable1.getModel();
            dtm.setRowCount(0);

            Row row = sheet.getRow(0);
            Map<String, Integer> columnNames = new HashMap<String, Integer>();
            for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                Cell cell = row.getCell(columnIndex);
                String columnName = "";
                if (cell != null) {
                    columnName = cell.toString();
                } else {
                    columnName = "COLUMN_" + columnIndex;
                }

                if (columnName != null && columnName.trim().length() > 0) {
                    dtm.addRow(new Object[] { columnName, new Integer(columnIndex) });
                }
            }

            jTable1.updateUI();

            jCheckBoxFirstRowAsHeader.setSelected(true);
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(this, ex.getMessage(),
                I18n.getString("XlsxDataSourceConnectionEditor.Message.Exception"), JOptionPane.ERROR_MESSAGE); //"message.title.exception"
    }
}