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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:org.wso2.carbon.dataservices.sql.driver.processor.reader.ExcelDataReader.java

License:Open Source License

public void populateData() throws SQLException {
    Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook();
    int noOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < noOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        ColumnInfo[] headers = this.extractColumnHeaders(sheet);
        DataTable dataTable = new FixedDataTable(sheetName, headers);

        Iterator<Row> rowItr = sheet.rowIterator();
        while (rowItr.hasNext()) {
            Row row = rowItr.next();
            if (row.getRowNum() != 0) {
                DataRow dataRow = new DataRow(row.getRowNum() - 1);
                Iterator<Cell> cellItr = row.cellIterator();
                int cellIndex = 0;
                while (cellItr.hasNext()) {
                    Cell cell = cellItr.next();
                    DataCell dataCell = new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell));
                    dataRow.addCell(dataCell.getColumnId(), dataCell);
                    cellIndex++;//from  w ww.j a  v  a  2  s.  co  m
                }
                dataTable.addRow(dataRow);
            }
        }
        this.getData().put(dataTable.getTableName(), dataTable);
    }
}

From source file:packtest.IterateCells.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(new FileInputStream(args[0]));
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);//  ww w . j  a  va 2s.c  o m
        System.out.println(wb.getSheetName(i));
        for (Row row : sheet) {
            System.out.println("rownum: " + row.getRowNum());
            for (Cell cell : row) {
                System.out.println(cell.toString());
            }
        }
    }
}

From source file:phoenixplcscadahelper.AnaEkran.java

private static int findRow(HSSFSheet sheet, String cellContent) {
    /*/*from www .  j a  v a2  s.  c  o m*/
     *  This is the method to find the row number
     */

    int rowNum = 0;

    for (Row row : sheet) {
        for (Cell cell : row) {
            if (cell.toString().equals(cellContent)) {
                rowNum = row.getRowNum();
                return rowNum;
            }
        }
    }
    return rowNum;
}

From source file:pl.exsio.ck.model.reader.XlsxEntryReaderImpl.java

License:Open Source License

@Override
public Collection<Entry> readEntries(File file, String progressName, boolean serialsOnly) {
    ProgressPresenter progress = ProgressHelper.showProgressBar(progressName, false);
    Row currentRow = null;
    Cell currentCell = null;//from   w  w w .  j av a  2s .  c  o m
    ArrayList<Entry> entries = new ArrayList<>();
    try {
        XSSFSheet sheet = this.openSheet(file);

        Iterator<Row> rowIterator = sheet.iterator();
        int totalRowCount = sheet.getPhysicalNumberOfRows() - 1;
        int rowCounter = 0;
        while (rowIterator.hasNext()) {
            ProgressHelper.updateProgressBar(progress, (int) (rowCounter * 100 / totalRowCount));
            currentRow = rowIterator.next();
            if (currentRow.getRowNum() > 0) {
                Entry e = new EntryImpl();
                Iterator<Cell> cellIterator = currentRow.cellIterator();
                while (cellIterator.hasNext()) {
                    currentCell = cellIterator.next();
                    if (!this.fillEntryField(currentCell, e, serialsOnly)) {
                        break;
                    }
                }
                if (e.getSerialNo() != null) {
                    entries.add(e);
                }
            }
            rowCounter++;
        }
    } catch (IOException ex) {
        this.log.log("nieudana prba otwarcia pliku " + file.getAbsolutePath());
        this.log.log(ExceptionUtils.getMessage(ex));
    } catch (ParseException ex) {
        this.log.log("nieprawidowy format daty w komrce " + currentRow.getRowNum()
                + CellReference.convertNumToColString(currentCell.getColumnIndex())
                + ". Akceptowalny format to 'yyyy-mm-dd'");
        this.log.log(ExceptionUtils.getMessage(ex));
    }
    System.gc();
    ProgressHelper.hideProgressBar(progress);
    return entries;
}

From source file:ProduseFurnizor.ProduseFurnizor9.java

public ArrayList<Produs> getProduse() {
    ArrayList<Produs> produse = new ArrayList<>();
    try {//www.j  ava 2s .c o  m

        urlFurnizor = new URL(url);
        in = urlFurnizor.openStream();

        HSSFWorkbook workbook = new HSSFWorkbook(in);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() != 0) {
                int stocFurnizor;
                Produs p = new Produs(null, null, null, 0, 0, false);
                //System.out.println("Parcurgem rand " + row.getRowNum());
                String codProdus = row.getCell(0).getStringCellValue().trim();
                String denumireProdus = row.getCell(1).getStringCellValue().trim();
                switch (row.getCell(2).getCellType()) {
                case (Cell.CELL_TYPE_STRING):
                    stocFurnizor = Integer.parseInt(row.getCell(2).getStringCellValue());
                    break;
                case (Cell.CELL_TYPE_NUMERIC):
                    stocFurnizor = (int) Math.round(row.getCell(2).getNumericCellValue());
                    break;
                default:
                    stocFurnizor = 0;
                }

                //System.out.println(codProdus + "/" + denumireProdus + "/" + stocFurnizor);
                p.setFurnizor("Hubners");
                p.setCodProdus(codProdus);
                p.setNume(denumireProdus);
                p.setCantitate(stocFurnizor);
                int cantitateSite = ProduseController.getInstance().getStocSite(p);
                if (cantitateSite != 99999) {
                    p.setCantitateSite(cantitateSite);
                    p.setInSite(true);
                } else {
                    p.setCantitateSite(0);
                }

                double pretFurnizor = row.getCell(3).getNumericCellValue();
                p.setPretFurnizor(pretFurnizor);
                produse.add(p);
            }

        }

    } catch (Exception ex) {
        Logger.getLogger(ProduseFurnizor9.class.getName()).log(Level.SEVERE, null, ex);
    }
    return produse;
}

From source file:raphdine.comptes.utils.ImportExcel.java

private Ecriture extractEntity(Row row) {
    try {// w ww . j  a v a  2s .c  o  m
        LOGGER.debug("Row {} value 3 => {}", row.getRowNum(), row.getCell(3));

        Ecriture e = new Ecriture();

        e.setDate(DateUtils.dateToCalendar(row.getCell(0).getDateCellValue()));
        e.setCategorie(extractCategorie(row.getCell(1).getStringCellValue()));
        e.setIntitule(row.getCell(2).getStringCellValue());
        if (row.getCell(3) != null) {
            e.setNumeroCheque(extractNumeroCheque(row.getCell(3).getNumericCellValue()));
        }
        if (row.getCell(4) != null) {
            e.setRemboursement(row.getCell(4).getStringCellValue());
        }
        if (row.getCell(5) == null) {
            e.setPasseEnBanque(Boolean.FALSE);
        } else {
            e.setPasseEnBanque(Boolean.TRUE);
        }
        if (row.getCell(6) != null) {
            final float debit = (float) row.getCell(6).getNumericCellValue();
            BigDecimal bD = new BigDecimal(debit);
            e.setDebit(debit == 0 ? null : bD);
        }
        if (row.getCell(7) != null) {
            final float credit = (float) row.getCell(7).getNumericCellValue();
            BigDecimal bD = new BigDecimal(credit);
            e.setCredit(credit == 0 ? null : bD);
        }
        if (e.getCredit() == null && e.getDebit() == null || e.getCredit() != null && e.getDebit() != null) {
            LOGGER.error("Erreur de cohrence dbit {}/crdit {} ({},{})", e.getDebit(), e.getCredit(),
                    row.getCell(6) != null && row.getCell(7) != null,
                    row.getCell(6) == null && row.getCell(7) == null);
            throw new IllegalArgumentException("Erreur de cohrence dbit/crdit");
        }
        return e;
    } catch (Exception e) {
        appendLogFile(String.valueOf(row.getRowNum()));
        LOGGER.error(e, "Erreur ligne {}", row.getRowNum());
        return null;
    }
}

From source file:rapture.dp.invocable.workflow.ProcessFile.java

License:Open Source License

@SuppressWarnings({ "rawtypes", "unchecked" })
@Override/*from  w  ww . ja  v  a2s. c  om*/
public String invoke(CallingContext ctx) {
    final int BATCH_LOAD_SIZE = 50; // TODO: move to config
    OPCPackage pkg;
    XSSFWorkbook wb;
    List uris = new ArrayList<>();
    // stores all documents for insertion
    List<List<String>> allDocs = new ArrayList<List<String>>();

    String file = Kernel.getDecision().getContextValue(ctx, getWorkerURI(), "filetoupload");
    String blobUri = Kernel.getDecision().getContextValue(ctx, getWorkerURI(), "blobUri");
    String folderName = Kernel.getDecision().getContextValue(ctx, getWorkerURI(), "folderName");

    String repo = "document://data/" + folderName;
    String docUri = repo + "#id";

    try {
        InputStream is = new ByteArrayInputStream(Kernel.getBlob().getBlob(ctx, blobUri).getContent());
        pkg = OPCPackage.open(is);
        wb = new XSSFWorkbook(pkg);
        XSSFSheet sheet = wb.getSheetAt(0);

        log.info("Loading " + sheet.getPhysicalNumberOfRows() + " rows from " + file + ". Batch size is "
                + BATCH_LOAD_SIZE);

        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        int remainder = physicalNumberOfRows % BATCH_LOAD_SIZE;
        int div = physicalNumberOfRows / BATCH_LOAD_SIZE;

        // this only needs to be done once as the uris dont change
        for (int g = 1; g <= BATCH_LOAD_SIZE; g++) {
            uris.add(docUri);
        }
        log.info("created uris list " + uris.size());

        int j = 0;
        int count = 0;
        long startLoadTime = System.currentTimeMillis();
        for (int i = 1; i <= div; i++) {
            List docs = new ArrayList<>();
            // Create a list of documents with size of BATCH_LOAD_SIZE
            for (j = count; j < (BATCH_LOAD_SIZE * i); j++) {
                Row row = sheet.getRow(j);
                Map<String, Object> map = ImmutableMap.of("Row", row.getRowNum(), "DataPeriod",
                        row.getCell(0).toString(), "Industry", row.getCell(3).toString(), "Price",
                        row.getCell(7).toString());
                docs.add(JacksonUtil.jsonFromObject(map));
            }
            allDocs.add(docs);
            count = j;
        }
        long endLoadTime = System.currentTimeMillis();

        ExecutorService executorService = Executors.newCachedThreadPool();
        long startWriteTime = System.currentTimeMillis();
        for (List<String> docList : allDocs) {
            executorService.execute(new InsertData(ctx, docList, uris));
        }
        executorService.shutdown();

        try {
            // TODO: hardcoded timeout.ComparableFutures?
            // Helpful:
            // http://stackoverflow.com/questions/1250643/how-to-wait-for-all-threads-to-finish-using-executorservice
            executorService.awaitTermination(60000L, TimeUnit.MILLISECONDS);
        } catch (InterruptedException e) {
            log.error(e.getStackTrace().toString(), e);
            return "error";
        }
        long endWriteTime = System.currentTimeMillis();
        log.info("Completed parallel load.");

        // handle the remaining rows
        if (remainder > 0) {
            long remStartTime = System.currentTimeMillis();
            for (int k = (count); k < (count + remainder); k++) {
                Row row = sheet.getRow(k);
                Map<String, Object> map = ImmutableMap.of("Row", row.getRowNum(), "DataPeriod",
                        row.getCell(0).toString(), "Industry", row.getCell(3).toString(), "Price",
                        row.getCell(7).toString());
                Kernel.getDoc().putDoc(ctx, docUri, JacksonUtil.jsonFromObject(map));
            }
            long remEndTime = System.currentTimeMillis();
            log.info("Remainders took " + (remEndTime - remStartTime) + "ms");
        }

        log.info("Populated uri " + repo + ". Took " + (endLoadTime - startLoadTime) + "ms. to load data. Took "
                + (endWriteTime - startWriteTime) + "ms. to write data.");
        pkg.close();

        Map<String, RaptureFolderInfo> listDocsByUriPrefix = Kernel.getDoc().listDocsByUriPrefix(ctx, repo, 1);
        log.info("Count from repo is " + listDocsByUriPrefix.size());

        if (listDocsByUriPrefix.size() == sheet.getPhysicalNumberOfRows()) {
            return "ok";
        } else {
            return "error"; // TODO: add error step
        }
    } catch (InvalidFormatException | IOException | RaptureException e) {
        log.error("ProcessFile error", e);
        return "error";
    }
}

From source file:regression.data.GenerateData.java

/**
 * Read data from excel//from  w ww .  j av a 2 s. co m
 *
 * @param file
 * @throws FileNotFoundException
 * @throws IOException
 * @throws InvalidFormatException
 */
public void readExcelDataSet(File file) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream excelFile = new FileInputStream(file);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = firstSheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell firstCell = row.getCell(row.getFirstCellNum());
        Cell secondCell = row.getCell(row.getFirstCellNum() + 1);

        if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC
                && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            try {
                this.points.add(new Point(firstCell.getNumericCellValue(), secondCell.getNumericCellValue()));
            } catch (Exception e) {
                System.err.println("Cannot convert data in row: " + row.getRowNum());
            }
        }

    }
    this.numberOfInstances = points.size();
}

From source file:regression.data.GenerateData.java

public void createWekaFile(File f) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream excelFile = new FileInputStream(f);
    File wekaFile = new File("weka.arff");
    XSSFWorkbook workbook = new XSSFWorkbook(f);
    XSSFSheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = firstSheet.iterator();
    PrintWriter writer = new PrintWriter(wekaFile);
    writer.println("@RELATION logistic");
    writer.println("@ATTRIBUTE x NUMERIC");
    writer.println("@ATTRIBUTE class {1,0}");
    writer.println("@DATA");
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell firstCell = row.getCell(row.getFirstCellNum());
        Cell secondCell = row.getCell(row.getFirstCellNum() + 1);

        if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC
                && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            try {
                writer.println(firstCell.getNumericCellValue() + "," + (int) secondCell.getNumericCellValue());
            } catch (Exception e) {
                System.err.println("Cannot convert data in row: " + row.getRowNum());
            }// ww  w . jav  a  2s .  com
        }

    }
    writer.close();
    this.numberOfInstances = points.size();

}

From source file:regression.data.GenerateLinearData.java

/**
 * Read data from excel/*from   ww  w.j  a v a  2 s .c o m*/
 *
 * @param file
 * @throws FileNotFoundException
 * @throws IOException
 * @throws InvalidFormatException
 */
public void readExcelDataSet(File file) throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream excelFile = new FileInputStream(file);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = firstSheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell firstCell = row.getCell(row.getFirstCellNum());
        Cell secondCell = row.getCell(row.getFirstCellNum() + 1);
        System.out.println(firstCell.getCellType() + " " + secondCell.getCellType());
        if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC
                && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            try {
                this.points.add(new Point(firstCell.getNumericCellValue(), secondCell.getNumericCellValue()));
            } catch (Exception e) {
                System.err.println("Cannot convert data in row: " + row.getRowNum());
            }
        }

    }

}