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

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

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

Introduction

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

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ??/*w  w  w  .  ja v  a  2s . co m*/
 * 
 * @param sheet 
 * @param rangeAddress 
 */
public static void clearCell(Sheet sheet, CellRangeAddress rangeAddress) {
    int fromRowIndex = rangeAddress.getFirstRow();
    int fromColumnIndex = rangeAddress.getFirstColumn();

    int toRowIndex = rangeAddress.getLastRow();
    int toColumnIndex = rangeAddress.getLastColumn();

    // ???
    List<Row> removeRowList = new ArrayList<Row>();
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (fromRowIndex <= row.getRowNum() && row.getRowNum() <= toRowIndex) {
            Set<Cell> removeCellSet = new HashSet<Cell>();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                if (fromColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= toColumnIndex) {
                    removeCellSet.add(cell);
                }
            }
            for (Cell cell : removeCellSet) {
                row.removeCell(cell);
            }
        }
        if (row.getLastCellNum() == -1) {
            removeRowList.add(row);
        }
    }
    for (Row row : removeRowList) {
        sheet.removeRow(row);
    }
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ??????<BR>/*from   w ww  .jav  a 2 s. c om*/
 * Cell?CELL_TYPE_BLANK???????????????
 * 
 * @see Workbook#cloneSheet(int) cloneSheet(int)
 * @param sheet 
 * @deprecated poi-3.5-beta7-20090607.jar??
 */
public static void prepareCloneSheet(Sheet sheet) {

    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                cell.setCellValue("");
            }
        }
    }
}

From source file:org.bbreak.excella.reports.ReportsTestUtil.java

License:Open Source License

/**
 * //from  ww w .ja  v  a 2  s  .  co  m
 * 
 * @param expected 
 * @param actual 
 * @param isActCopyOfExp ??????true
 * @throws ReportsCheckException 
 */
public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp)
        throws ReportsCheckException {

    List<CheckMessage> errors = new ArrayList<CheckMessage>();

    Workbook expectedWorkbook = expected.getWorkbook();
    Workbook actualWorkbook = actual.getWorkbook();

    if (log.isDebugEnabled()) {
        log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual))
                + "] check start!");
    }

    // ----------------------
    // ????
    // ----------------------
    // ??
    String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected));
    String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual));

    if (!isActCopyOfExp) {
        if (!eSheetName.equals(aSheetName)) {
            errors.add(new CheckMessage("??", eSheetName, aSheetName));
        }
    }

    // ?
    String ePrintSetupString = getPrintSetupString(expected.getPrintSetup());
    String aPrintSetupString = getPrintSetupString(actual.getPrintSetup());

    if (!ePrintSetupString.equals(aPrintSetupString)) {
        errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString));
    }

    // ?
    String eHeaderString = getHeaderString(expected.getHeader());
    String aHeaderString = getHeaderString(actual.getHeader());
    if (!eHeaderString.equals(aHeaderString)) {
        errors.add(new CheckMessage("", eHeaderString, aHeaderString));
    }
    String eFooterString = getFooterString(expected.getFooter());
    String aFooterString = getFooterString(actual.getFooter());
    if (!eFooterString.equals(aFooterString)) {
        errors.add(new CheckMessage("", eFooterString, aFooterString));
    }

    // 
    String eBreaksString = getBreaksString(expected);
    String aBreaksString = getBreaksString(actual);
    log.debug(eBreaksString + "/" + aBreaksString);
    if (!eBreaksString.equals(aBreaksString)) {
        errors.add(new CheckMessage("", eBreaksString, aBreaksString));
    }

    // ?
    String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected));
    String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual));
    if (expectedPrintArea != null || actualPrintArea != null) {
        // ????????Null?????????????
        // if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) {
        // errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea));
        // }
        if (!isActCopyOfExp) {
            if (expectedPrintArea == null || actualPrintArea == null
                    || !expectedPrintArea.equals(actualPrintArea)) {
                errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea));
            }
        }
    }

    // (?)
    String ePaneInformationString = getPaneInformationString(expected.getPaneInformation());
    String aPaneInformationString = getPaneInformationString(actual.getPaneInformation());

    if (!ePaneInformationString.equals(aPaneInformationString)) {
        errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea));
    }

    // ??????

    // ?????

    // ?????

    // 

    // 
    if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) {
        errors.add(new CheckMessage("",
                String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines())));
    }

    // ?
    if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) {
        errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()),
                String.valueOf(actual.isDisplayRowColHeadings())));
    }

    // ?
    if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) {
        errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()),
                String.valueOf(actual.isDisplayFormulas())));
    }
    // ??
    if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) {
        errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()),
                String.valueOf(actual.getNumMergedRegions())));
    }

    for (int i = 0; i < actual.getNumMergedRegions(); i++) {

        CellRangeAddress actualAddress = null;
        if (expected instanceof HSSFSheet) {
            actualAddress = ((HSSFSheet) actual).getMergedRegion(i);
        } else if (expected instanceof XSSFSheet) {
            actualAddress = ((XSSFSheet) actual).getMergedRegion(i);
        }

        StringBuffer expectedAdressBuffer = new StringBuffer();
        boolean equalAddress = false;
        for (int j = 0; j < expected.getNumMergedRegions(); j++) {
            CellRangeAddress expectedAddress = null;
            if (expected instanceof HSSFSheet) {
                expectedAddress = ((HSSFSheet) expected).getMergedRegion(j);
            } else if (expected instanceof XSSFSheet) {
                expectedAddress = ((XSSFSheet) expected).getMergedRegion(j);
            }
            if (expectedAddress.toString().equals(actualAddress.toString())) {
                equalAddress = true;
                break;
            }
            CellReference crA = new CellReference(expectedAddress.getFirstRow(),
                    expectedAddress.getFirstColumn());
            CellReference crB = new CellReference(expectedAddress.getLastRow(),
                    expectedAddress.getLastColumn());
            expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]");
        }

        if (!equalAddress) {
            errors.add(new CheckMessage("??", expectedAdressBuffer.toString(),
                    actualAddress.toString()));
        }

    }

    int maxColumnNum = -1;
    if (expected instanceof HSSFSheet) {
        maxColumnNum = HSSF_MAX_COLUMN_NUMBER;
    } else if (expected instanceof XSSFSheet) {
        maxColumnNum = XSSF_MAX_COLUMN_NUMBER;
    }
    for (int i = 0; i < maxColumnNum; i++) {
        try {
            // 
            checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(),
                    actual.getColumnStyle(i));
        } catch (ReportsCheckException e) {
            CheckMessage checkMessage = e.getCheckMessages().iterator().next();
            checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage());
            errors.add(checkMessage);
        }

        // 
        if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) {
            errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)),
                    String.valueOf(actual.getColumnWidth(i))));
        }
    }

    // ???
    if (expected.getLastRowNum() != actual.getLastRowNum()) {
        // ??????
        if (expected.getLastRowNum() < actual.getLastRowNum()) {
            int lastRowIndex = -1;
            if (expected instanceof HSSFSheet) {
                lastRowIndex = 0;
            }
            Iterator<Row> rowIterator = actual.rowIterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                // ?????
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellTypeEnum() != CellType.BLANK) {
                        lastRowIndex = row.getRowNum();
                        break;
                    }
                }
            }
            if (expected.getLastRowNum() != lastRowIndex) {
                errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()),
                        String.valueOf(lastRowIndex)));
            }
        } else {
            errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()),
                    String.valueOf(actual.getLastRowNum())));
        }

    }

    if (errors.isEmpty()) {
        for (int i = 0; i <= expected.getLastRowNum(); i++) {
            try {
                checkRow(expected.getRow(i), actual.getRow(i));
            } catch (ReportsCheckException e) {
                errors.addAll(e.getCheckMessages());
            }
        }
    }

    if (!errors.isEmpty()) {
        if (log.isErrorEnabled()) {
            for (CheckMessage message : errors) {
                log.error("?[" + message.getMessage() + "]");
                log.error(":" + message.getExpected());
                log.error(":" + message.getActual());
            }
        }
        throw new ReportsCheckException(errors);
    }

    if (log.isDebugEnabled()) {
        log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual))
                + "] check end.");
    }

}

From source file:org.celllife.idart.gui.patientAdmin.PatientAdmin.java

License:Open Source License

private void importPatientViralLoad(String fileName) {
    ArrayList<PatientViralLoadDataImport> patients = new ArrayList<PatientViralLoadDataImport>();
    int rowErrors = 0;
    Session sess = HibernateUtil.getNewSession();
    try {/*from ww  w .j a v a  2  s. c o m*/
        FileInputStream file = new FileInputStream(new File(fileName));

        //Create Workbook instance holding reference to .xlsx file
        Workbook workbook = WorkbookFactory.create(file);

        //Get first/desired sheet from the workbook
        Sheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        Integer patientId;
        Boolean highViralLoad;
        Date resultDate;
        Row row = rowIterator.next();

        while (rowIterator.hasNext()) {
            try {
                row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                Cell cell = cellIterator.next();

                patientId = new Integer((int) cell.getNumericCellValue());

                cell = cellIterator.next();
                cell = cellIterator.next();
                cell = cellIterator.next();
                cell = cellIterator.next();
                cell = cellIterator.next();

                highViralLoad = (new BooleanConverter()).convert(cell.getStringCellValue());
                //cell.getStringCellValue().equalsIgnoreCase("Sim") ? true : false; 

                cell = cellIterator.next();

                resultDate = cell.getDateCellValue();
                //(new DateConverter()).convert(cell.getNumericCellValue());
                //DateUtil.getJavaDate(cell.getNumericCellValue());
                patients.add(new PatientViralLoadDataImport(patientId, highViralLoad, resultDate));
            } catch (Exception e) {
                e.printStackTrace();
                rowErrors++;
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    PatientViralLoad latestViralLoad, newViralLoad;
    for (PatientViralLoadDataImport patientDataImport : patients) {
        try {
            latestViralLoad = PatientManager.getLastPatientViralLoad(sess, patientDataImport.getId());
            newViralLoad = new PatientViralLoad();
            if (latestViralLoad == null) {
                newViralLoad = new PatientViralLoad();
                newViralLoad.setHighViralLoad(patientDataImport.getHighViralLoad());
                newViralLoad.setBelongsGaac(false);
                newViralLoad.setRecommendedToCounselor(false);
                newViralLoad.setResultDate(new java.sql.Date(patientDataImport.getResultDate().getTime()));
                newViralLoad.setCounselingDate(null);
                newViralLoad.setGaacNumber(Integer.parseInt("0"));
                newViralLoad.setPatient(PatientManager.getPatient(sess, patientDataImport.getId()));
            } else {
                newViralLoad = new PatientViralLoad();
                newViralLoad.setHighViralLoad(patientDataImport.getHighViralLoad());
                newViralLoad.setBelongsGaac(latestViralLoad.getBelongsGaac());
                newViralLoad.setRecommendedToCounselor(latestViralLoad.getRecommendedToCounselor());
                newViralLoad.setResultDate(new java.sql.Date(patientDataImport.getResultDate().getTime()));
                newViralLoad.setCounselingDate(latestViralLoad.getCounselingDate());
                newViralLoad.setGaacNumber(latestViralLoad.getGaacNumber());
                newViralLoad.setPatient(PatientManager.getPatient(sess, patientDataImport.getId()));
            }

            Transaction tx = null;

            try {

                tx = sess.beginTransaction();

                PatientManager.addPatientViralLoad(sess, newViralLoad);

                sess.flush();
                tx.commit();

            } catch (HibernateException he) {
                if (tx != null) {
                    tx.rollback();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    String message = Messages.getString("PatientAdmin.button.importPatient.error1") + " " + rowErrors + " "
            + Messages.getString("PatientAdmin.button.importPatient.error2") + patients.size()
            + Messages.getString("PatientAdmin.button.importPatient.success");
    JOptionPane.showMessageDialog(null, message);

}

From source file:org.dash.valid.freq.HLAFrequenciesLoader.java

License:Open Source License

private List<String> readHeaderElementsByRace(Row row) {
    List<String> raceHeaders = new ArrayList<String>();

    Iterator<Cell> cellIterator = row.cellIterator();

    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();

        String[] race = cell.getStringCellValue().split(UNDERSCORE);
        raceHeaders.add(cell.getColumnIndex(), race[0]);
    }//from  w  w  w.ja va  2 s  . co  m

    return raceHeaders;
}

From source file:org.dash.valid.freq.HLAFrequenciesLoader.java

License:Open Source License

/**
 * @param row//from   w  ww.j  ava 2s . c o  m
 */
private DisequilibriumElement readDiseqilibriumElementsByRace(Row row, List<String> raceHeaders,
        Locus[] locusPositions) {
    // For each row, iterate through each columns
    Iterator<Cell> cellIterator = row.cellIterator();

    List<FrequencyByRace> frequenciesByRace = new ArrayList<FrequencyByRace>();
    DisequilibriumElementByRace disElement = new DisequilibriumElementByRace();

    int columnIndex;
    String cellValue = null;

    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();

        columnIndex = cell.getColumnIndex();

        if (columnIndex < locusPositions.length) {
            cellValue = cell.getStringCellValue();
            if (!cellValue.contains(GLStringConstants.ASTERISK)) {
                cellValue = locusPositions[columnIndex].getShortName() + GLStringConstants.ASTERISK
                        + cellValue.substring(0, 2) + GLStringUtilities.COLON + cellValue.substring(2);
            }
            disElement.setHlaElement(locusPositions[columnIndex], GLStringConstants.HLA_DASH + cellValue);
        } else {
            if ((locusPositions.length % 2 == 0 && columnIndex % 2 == 0)
                    || (locusPositions.length % 2 != 0 && columnIndex % 2 != 0)) {
                disElement
                        .setFrequenciesByRace(loadFrequencyAndRank(row, cell, frequenciesByRace, raceHeaders));
            }
        }
    }

    return disElement;
}

From source file:org.datanucleus.store.excel.ExcelPersistenceHandler.java

License:Open Source License

/**
 * Deletes a persistent object from the database.
 * @param op The Object Provider of the object to be deleted.
 * @throws NucleusDataStoreException when an error occurs in the datastore communication
 * @throws NucleusOptimisticException thrown if version checking fails on an optimistic transaction for this object
 *//*from   ww  w.j  a v  a  2  s  .com*/
public void deleteObject(ObjectProvider op) {
    // Check if read-only so update not permitted
    assertReadOnlyForUpdateOfObject(op);

    ExecutionContext ec = op.getExecutionContext();
    ManagedConnection mconn = storeMgr.getConnection(ec);
    try {
        AbstractClassMetaData cmd = op.getClassMetaData();
        if (cmd.isVersioned()) {
            NucleusLogger.PERSISTENCE.warn(
                    "This datastore doesn't support optimistic version checks since the datastore file is for a single-connection");
        }

        Workbook wb = (Workbook) mconn.getConnection();
        Table table = ec.getStoreManager().getStoreDataForClass(op.getClassMetaData().getFullClassName())
                .getTable();
        final Sheet sheet = ExcelUtils.getSheetForClass(op, wb, table);

        // Invoke any cascade deletion
        op.loadUnloadedFields();
        op.provideFields(cmd.getAllMemberPositions(), new DeleteFieldManager(op));

        // Delete this object
        long startTime = System.currentTimeMillis();
        if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
            NucleusLogger.DATASTORE_PERSIST.debug(
                    Localiser.msg("Excel.Delete.Start", op.getObjectAsPrintable(), op.getInternalObjectId()));
        }

        int rowId = ExcelUtils.getRowNumberForObjectInWorkbook(op, wb, false, table);
        if (rowId < 0) {
            throw new NucleusObjectNotFoundException("object not found", op.getObject());
        }

        if (storeMgr instanceof XLSStoreManager && sheet.getLastRowNum() == rowId) {
            // Deleting top row which is last row so just remove all cells and leave row
            // otherwise Apache POI throws an ArrayIndexOutOfBoundsException
            Row row = sheet.getRow(rowId);
            Iterator<Cell> it = row.cellIterator();
            while (it.hasNext()) {
                row.removeCell(it.next());
            }
        } else {
            // Deleting top row so remove it
            sheet.removeRow(sheet.getRow(rowId));
            if (sheet.getLastRowNum() > rowId) {
                sheet.shiftRows(rowId + 1, sheet.getLastRowNum(), -1);
            }
        }

        if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
            NucleusLogger.DATASTORE_PERSIST
                    .debug(Localiser.msg("Excel.ExecutionTime", (System.currentTimeMillis() - startTime)));
        }
        if (ec.getStatistics() != null) {
            ec.getStatistics().incrementNumWrites();
            ec.getStatistics().incrementDeleteCount();
        }
    } finally {
        mconn.release();
    }
}

From source file:org.deegree.igeo.dataadapter.LinkedExcelTable.java

License:Open Source License

/**
 * //w  w w  .  jav a  2s  . com
 * @param linkedTableType
 * @param file
 * @param sheetName
 * @throws IOException
 */
public LinkedExcelTable(LinkedFileTableType linkedTableType, File file) throws IOException {
    super(linkedTableType);
    if (file.getAbsolutePath().toLowerCase().endsWith(".xls")) {
        workbook = new HSSFWorkbook(new FileInputStream(file));
    } else {
        workbook = new XSSFWorkbook(new FileInputStream(file));
    }
    sheet = workbook.getSheetAt(0);
    LOG.logDebug("load first excel sheet");

    Iterator<Row> rowIter = sheet.rowIterator();
    Row firstRow = rowIter.next();

    List<String> headerNames = new ArrayList<String>();
    List<Integer> headerTypes = new ArrayList<Integer>();

    for (Iterator<Cell> cit = firstRow.cellIterator(); cit.hasNext();) {
        Cell cell = cit.next();
        String cellValue = cell.getRichStringCellValue().getString();
        headerNames.add(cellValue);
        headerTypes.add(getCellType(cell.getCellType()));
    }
    columnNames = headerNames.toArray(new String[headerNames.size()]);
    types = new int[headerTypes.size()];
    for (int i = 0; i < types.length; i++) {
        types[i] = headerTypes.get(i);
    }
}

From source file:org.deri.tarql.XLSToValues.java

License:Apache License

private String[] getRow(Row row) {
    int i = 0;//String array
    String[] csvdata = new String[row.getLastCellNum()];
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {

        Cell cell = cellIterator.next(); //Fetch CELL
        if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
            csvdata[i] = this.formatter.formatCellValue(cell);
        } else {/*  w ww .  jav a 2  s.  co m*/
            csvdata[i] = this.formatter.formatCellValue(cell, this.evaluator);
        }
        i = i + 1;
    }
    return csvdata;
}

From source file:org.easybatch.extensions.msexcel.MsExcelRecordMapper.java

License:Open Source License

private Map<String, String> toMap(final Row row) {
    Map<String, String> map = new HashMap<>();
    Iterator<Cell> cellIterator = row.cellIterator();
    int i = 0;//from w w w.j  a  va  2s . c  o  m
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        map.put(fields[i++], getCellValue(cell));
    }
    return map;
}