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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.AllenBarr.CallSheetGenerator.CallSheetGeneratorControllerController.java

License:Open Source License

private Contributor makeContributor() {
    Contributor contributor = new Contributor();
    Row contribRow = wbSheet.getRow(contributorSelector.getSelectionModel().getSelectedIndex());
    Row headerRow = wbSheet.getRow(0);/*from ww  w . j  a v  a2 s.  co  m*/
    Integer vanIDColumnIndex = 0;
    Integer lNameColumnIndex = 0;
    Integer fNameColumnIndex = 0;
    Integer mNameColumnIndex = 0;
    Integer suffixColumnIndex = 0;
    Integer salutationColumnIndex = 0;
    Integer spouseColumnIndex = 0;
    Integer mAddressColumnIndex = 0;
    Integer mCityColumnIndex = 0;
    Integer mStateColumnIndex = 0;
    Integer mZipColumnIndex = 0;
    Integer sexColumnIndex = 0;
    Integer ageColumnIndex = 0;
    Integer occupationColumnIndex = 0;
    Integer employerColumnIndex = 0;
    Integer phoneColumnIndex = 0;
    Integer homePhoneColumnIndex = 0;
    Integer cellPhoneColumnIndex = 0;
    Integer workPhoneColumnIndex = 0;
    Integer extColumnIndex = 0;
    Integer emailColumnIndex = 0;
    Integer notesColumnIndex = 0;
    Integer partyColumnIndex = 0;
    for (Cell cell : headerRow) {
        if (null != cell.getStringCellValue()) {
            switch (cell.getStringCellValue()) {
            case "VANID":
                vanIDColumnIndex = cell.getColumnIndex();
                break;
            case "LastName":
                lNameColumnIndex = cell.getColumnIndex();
                break;
            case "FirstName":
                fNameColumnIndex = cell.getColumnIndex();
                break;
            case "MiddleName":
                mNameColumnIndex = cell.getColumnIndex();
                break;
            case "Suffix":
                suffixColumnIndex = cell.getColumnIndex();
                break;
            case "Salutation":
                salutationColumnIndex = cell.getColumnIndex();
                break;
            case "Spouse":
                spouseColumnIndex = cell.getColumnIndex();
                break;
            case "mAddress":
                mAddressColumnIndex = cell.getColumnIndex();
                break;
            case "mCity":
                mCityColumnIndex = cell.getColumnIndex();
                break;
            case "mState":
                mStateColumnIndex = cell.getColumnIndex();
                break;
            case "mZip5":
                mZipColumnIndex = cell.getColumnIndex();
                break;
            case "Sex":
                sexColumnIndex = cell.getColumnIndex();
                break;
            case "Age":
                ageColumnIndex = cell.getColumnIndex();
                break;
            case "Occupation":
                occupationColumnIndex = cell.getColumnIndex();
                break;
            case "Employer":
                employerColumnIndex = cell.getColumnIndex();
                break;
            case "Phone":
                phoneColumnIndex = cell.getColumnIndex();
                break;
            case "CellPhone":
                cellPhoneColumnIndex = cell.getColumnIndex();
                break;
            case "HomePhone":
                homePhoneColumnIndex = cell.getColumnIndex();
                break;
            case "WorkPhone":
                workPhoneColumnIndex = cell.getColumnIndex();
                break;
            case "WorkPhoneExt":
                extColumnIndex = cell.getColumnIndex();
                break;
            case "Email":
                emailColumnIndex = cell.getColumnIndex();
                break;
            case "Notes":
                notesColumnIndex = cell.getColumnIndex();
                break;
            case "Party":
                partyColumnIndex = cell.getColumnIndex();
                break;
            }
        }
    }
    try {
        contributor.setVANID((int) contribRow.getCell(vanIDColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setVANID(Integer.parseInt(contribRow.getCell(vanIDColumnIndex).getStringCellValue()));
    }
    String name = "";
    try {
        name = name.concat(contribRow.getCell(fNameColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        name = name.concat(" " + contribRow.getCell(mNameColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        name = name.concat(" " + contribRow.getCell(lNameColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        name = name.concat(" " + contribRow.getCell(suffixColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    contributor.setName(name);
    try {
        contributor.setSalutation(contribRow.getCell(salutationColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setSex(contribRow.getCell(sexColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setParty(contribRow.getCell(partyColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setPhone(contribRow.getCell(phoneColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setPhone(Double.parseDouble(contribRow.getCell(phoneColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setHomePhone(contribRow.getCell(homePhoneColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setHomePhone(
                Double.parseDouble(contribRow.getCell(homePhoneColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setCellPhone(contribRow.getCell(cellPhoneColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setCellPhone(
                Double.parseDouble(contribRow.getCell(cellPhoneColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setWorkPhone(contribRow.getCell(workPhoneColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setWorkPhone(
                Double.parseDouble(contribRow.getCell(workPhoneColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setWorkExtension(contribRow.getCell(extColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor
                .setWorkExtension(Double.parseDouble(contribRow.getCell(extColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setEmail(contribRow.getCell(emailColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setEmployer(contribRow.getCell(employerColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setOccupation(contribRow.getCell(occupationColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setAge((int) contribRow.getCell(ageColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setAge(Integer.parseInt(contribRow.getCell(ageColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setSpouse(contribRow.getCell(spouseColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setStreetAddress(contribRow.getCell(mAddressColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setCity(contribRow.getCell(mCityColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setState(contribRow.getCell(mStateColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    try {
        contributor.setZip((int) contribRow.getCell(mZipColumnIndex).getNumericCellValue());
    } catch (NullPointerException ex) {

    } catch (IllegalStateException ex) {
        contributor.setZip(Integer.parseInt(contribRow.getCell(mZipColumnIndex).getStringCellValue()));
    }
    try {
        contributor.setNotes(contribRow.getCell(notesColumnIndex).getStringCellValue());
    } catch (NullPointerException ex) {

    }
    ICsvListReader listReader = null;
    try {
        try {
            listReader = new CsvListReader(
                    new FileReader(contributionDirectory.getPath() + "/"
                            + ((String) contributorSelector.getItems()
                                    .get(contributorSelector.getSelectionModel().getSelectedIndex()))
                                            .replaceAll("[\\D]", "")
                            + ".csv"),
                    CsvPreference.STANDARD_PREFERENCE);
            listReader.getHeader(true); // skip the header (can't be used with CsvListReader)

            while ((listReader.read()) != null) {

                // use different processors depending on the number of columns
                final CellProcessor[] processors;
                processors = getProcessors();

                final List<Object> customerList = listReader.executeProcessors(processors);

                Matcher date = dateRegex.matcher(customerList.toString());
                Matcher des = descRegex.matcher(customerList.toString());
                Matcher amt = amtRegex.matcher(customerList.toString());
                date.find();
                des.find();
                amt.find();
                contributor.addDonation(
                        new Donation(new Date(date.group(1)), des.group(1), Double.parseDouble(amt.group(1))));
            }

        } catch (FileNotFoundException | NullPointerException ex) {
            //Logger.getLogger(CallSheetGeneratorControllerController.class.getName()).log(Level.SEVERE, null, ex);
        }

    } catch (IOException ex) {
        //Logger.getLogger(CallSheetGeneratorControllerController.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (listReader != null) {
            try {
                listReader.close();
            } catch (IOException ex) {
                //Logger.getLogger(CallSheetGeneratorControllerController.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return contributor;
}

---

From source file:com.anevis.jfreechartsamplespring.reader.FileReaderServiceImpl.java

@Override
public List<PieChartData> readPieChartDataFromXls(String filepath) {
    InputStream stream;/* w  w w. j  a  va 2  s . c om*/

    try {
        stream = new FileInputStream(filepath);
        List<PieChartData> pieChartDataList = new ArrayList<>();
        HSSFWorkbook workbook = new HSSFWorkbook(stream);

        HSSFSheet sheet = workbook.getSheetAt(0);

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

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            PieChartData data = new PieChartData();

            Cell countryCell = row.getCell(0);
            Cell weightCell = row.getCell(1);

            if (countryCell != null && weightCell != null) {
                data.setCountry(countryCell.getStringCellValue());
                data.setWeight(weightCell.getNumericCellValue());

                pieChartDataList.add(data);
            }
        }

        return pieChartDataList;

    } catch (IOException ex) {
        Logger.getLogger(FileReaderServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
    }

    return null;
}

---

From source file:com.anritsu.mcrepositorymanager.packageinfoparser.RSSParser.java

public void parseRSS() {
    try {/*from w  w w.  j a va  2 s  .c  o  m*/
        FileInputStream file = new FileInputStream(new File(this.filePath));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get package list sheet from the workbook
        String sheetName = "MC " + mcVersion;
        XSSFSheet sheet = workbook.getSheet(sheetName);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() < 3) {
                continue;
            }
            McPackage p = new McPackage();
            p.setMcVersion(mcVersion);
            p.setName(row.getCell(2).getStringCellValue());
            HashSet<String> downloadLinks = new HashSet<>();
            try {
                String link = row.getCell(7).getHyperlink().getAddress();
                downloadLinks.add(link);
                int urlIndex = link.split("/").length;
                String fileName = link.split("/")[urlIndex - 1];
                p.setFileName(fileName);
            } catch (NullPointerException exp) {
                exp.printStackTrace();
            } finally {
                p.setDownloadLinks(downloadLinks);
            }

            p.setPackageVersion(row.getCell(3).getStringCellValue());
            p.setAvailability(row.getCell(4).getStringCellValue());
            availability.add(row.getCell(4).getStringCellValue());

            // Set customers list
            ArrayList<String> cusList = new ArrayList<>();
            String[] customerCell = row.getCell(5).getStringCellValue().split(",");
            for (int i = 0; i < customerCell.length; i++) {
                customers.add(customerCell[i]);
                cusList = new ArrayList<>(Arrays.asList(customerCell));
            }
            //System.out.println("Parsing line " + row.getRowNum());
            LOGGER.log(Level.INFO, "Parsing " + this.filePath + "/" + row.getRowNum());

            p.setCustomerList(new HashSet<>(cusList));
            packageList.add(p);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public String getUniversity() throws Exception {

    String result = null;// w ww.  j av  a  2 s.  co  m

    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(ROW_UNIVERSITY);
        Cell cell = row.getCell(COLUMN_UNIVERSITY);
        result = cell.getStringCellValue().split("-")[1].trim();
    } catch (Throwable ex) {
        String message = "  ?? ?  ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
    return result;
}

---

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public List<User> getUsers() throws Exception {
    List<User> result = new ArrayList<>();
    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);//from   w ww .  jav a  2s . c  o m

        int amountRows = sheet.getPhysicalNumberOfRows();

        for (int i = HEADER_HEIGHT; i < amountRows; i++) {
            User user = new User();

            Row row = sheet.getRow(i);
            Cell cell = row.getCell(COLUMN_FIO);
            String fio = cell.getStringCellValue().trim();
            if (fio != null && !fio.isEmpty()) {
                user.setFIO(fio);
                user.setRow(i);
                result.add(user);
            }

        }
    } catch (Throwable ex) {
        String message = "  ??   ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
    return result;
}

---

From source file:com.arg.arsoft.siantluis.web.controllers.ClaimController.java

@RequestMapping(value = "/uploadFile", method = RequestMethod.POST)
@Transactional/*from  w w w  .j a va 2  s.  com*/
public @ResponseBody String upload(MultipartFile file) throws Exception {

    if (file != null) {
        System.out.println(file.getOriginalFilename());

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        for (int index = 1; index < sheet.getPhysicalNumberOfRows(); index++) {
            Row row = sheet.getRow(index);
            String code = row.getCell(0).getStringCellValue();
            ClaimUpload entity = cuRepository.findByCode(code);

            if (entity == null) {
                entity = new ClaimUpload();
                entity.setId(0);
                entity.setCode(code);
            }
            System.out.print(entity.getId());
            entity.setDesc(row.getCell(1).getStringCellValue());
            entity.setClaimDate(row.getCell(2).getStringCellValue());
            entity.setClaimTime(row.getCell(3).getStringCellValue());
            entity.setClaimLocation(row.getCell(4).getStringCellValue());
            entity.setClaimReason(row.getCell(5).getStringCellValue());
            cuRepository.save(entity);

        }

    }
    return "Success";
}

---

From source file:com.asakusafw.testdata.generator.excel.ExcelTesterRoot.java

License:Apache License

/**
 * Obtains the cell.//from   ww w  . j  av  a  2s . c o  m
 * @param sheet the sheet
 * @param rowIndex row index
 * @param columnIndex column index
 * @return cell string
 */
protected String cell(Sheet sheet, int rowIndex, int columnIndex) {
    Row row = sheet.getRow(rowIndex);
    assertThat(row, not(nullValue()));
    Cell cell = row.getCell(columnIndex);
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    }
    assertThat(cell.getCellType(), is(Cell.CELL_TYPE_STRING));
    return cell.getStringCellValue();
}

---

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractor.java

License:Apache License

private String getStringCell(Sheet sheet, int rowIndex, int colIndex) {
    assert sheet != null;
    Row row = sheet.getRow(rowIndex);
    if (row == null) {
        return "?"; //$NON-NLS-1$
    }/*from w  ww . ja  v a2s .  c o  m*/
    Cell cell = row.getCell(colIndex);
    if (cell == null || cell.getCellType() != Cell.CELL_TYPE_STRING) {
        return "?"; //$NON-NLS-1$
    }
    return cell.getStringCellValue();
}

---

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractor.java

License:Apache License

@Override
public String extractName(Row row) throws FormatException {
    if (row == null) {
        throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$
    }//from   ww  w .  j  a v a 2s . c om
    // strict checking for cell type
    Cell cell = row.getCell(RuleSheetFormat.PROPERTY_NAME.getColumnIndex());
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
        throw new FormatException(MessageFormat.format(
                Messages.getString("DefaultExcelRuleExtractor.errorInvalidNameType"), //$NON-NLS-1$
                RuleSheetFormat.PROPERTY_NAME.getTitle(), cell.getRowIndex() + 1, cell.getColumnIndex() + 1));
    }
    String name = cell.getStringCellValue();
    if (name.isEmpty()) {
        return null;
    }
    return name;
}

---

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractor.java

License:Apache License

private String getStringCell(Row row, RuleSheetFormat item) throws FormatException {
    assert row != null;
    assert item != null;
    Cell cell = row.getCell(item.getColumnIndex());
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return ""; //$NON-NLS-1$
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    }/*www.jav a  2 s . c  om*/
    throw new FormatException(
            MessageFormat.format(Messages.getString("DefaultExcelRuleExtractor.errorInvalidStringCell"), //$NON-NLS-1$
                    item.getTitle(), cell.getRowIndex() + 1, cell.getColumnIndex() + 1));
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• »