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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. 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:adams.data.io.output.ExcelSpreadSheetWriter.java

License:Open Source License

/**
 * Performs the actual writing. The caller must ensure that the writer gets
 * closed.//w w w  . ja v  a2  s  .  c  om
 *
 * @param content   the spreadsheet to write
 * @param out      the writer to write the spreadsheet to
 * @return      true if successfully written
 */
@Override
protected boolean doWrite(SpreadSheet[] content, OutputStream out) {
    boolean result;
    Workbook workbook;
    Sheet sheet;
    Row row;
    adams.data.spreadsheet.Row spRow;
    adams.data.spreadsheet.Cell spCell;
    Cell cell;
    int i;
    int n;
    int count;
    CellStyle styleDate;
    CellStyle styleDateTime;
    CellStyle styleTime;
    HashSet<String> names;
    String name;

    result = true;

    try {
        if (getWriteOOXML())
            workbook = new XSSFWorkbook();
        else
            workbook = new HSSFWorkbook();
        styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT);
        styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT);
        styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT);

        count = 0;
        names = new HashSet<>();
        for (SpreadSheet cont : content) {
            if (m_Stopped)
                return false;

            sheet = workbook.createSheet();
            if (cont.getName() != null) {
                name = cont.getName().replace("'", "");
                if (names.contains(name))
                    name += (count + 1);
            } else {
                name = m_SheetPrefix + (count + 1);
            }
            names.add(name);
            workbook.setSheetName(count, name);

            // header
            row = sheet.createRow(0);
            for (i = 0; i < cont.getColumnCount(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(cont.getHeaderRow().getCell(i).getContent());
            }

            // data
            for (n = 0; n < cont.getRowCount(); n++) {
                if (m_Stopped)
                    return false;
                row = sheet.createRow(n + 1);
                spRow = cont.getRow(n);
                for (i = 0; i < cont.getColumnCount(); i++) {
                    cell = row.createCell(i);
                    spCell = spRow.getCell(i);
                    if ((spCell == null) || spCell.isMissing()) {
                        if (m_MissingValue.length() > 0)
                            cell.setCellValue(m_MissingValue);
                        else
                            cell.setCellType(Cell.CELL_TYPE_BLANK);
                        continue;
                    }

                    if (spCell.isFormula() && !m_OutputAsDisplayed) {
                        cell.setCellFormula(spCell.getFormula().substring(1));
                    } else {
                        if (spCell.isDate()) {
                            cell.setCellValue(spCell.toDate());
                            cell.setCellStyle(styleDate);
                        } else if (spCell.isTime()) {
                            cell.setCellValue(spCell.toTime());
                            cell.setCellStyle(styleTime);
                        } else if (spCell.isDateTime()) {
                            cell.setCellValue(spCell.toDateTime());
                            cell.setCellStyle(styleDateTime);
                        } else if (spCell.isNumeric()) {
                            cell.setCellValue(Utils.toDouble(spCell.getContent()));
                        } else {
                            cell.setCellValue(spCell.getContent());
                        }
                    }
                }
            }

            // next sheet
            count++;
        }

        // save
        workbook.write(out);
    } catch (Exception e) {
        result = false;
        getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e);
    }

    return result;
}

From source file:adams.data.io.output.ExcelStreamingSpreadSheetWriter.java

License:Open Source License

/**
 * Performs the actual writing. The caller must ensure that the writer gets
 * closed./*from  ww w  . ja v  a 2 s.com*/
 *
 * @param content   the spreadsheet to write
 * @param out      the writer to write the spreadsheet to
 * @return      true if successfully written
 */
@Override
protected boolean doWrite(SpreadSheet[] content, OutputStream out) {
    boolean result;
    SXSSFWorkbook workbook;
    Sheet sheet;
    Row row;
    adams.data.spreadsheet.Row spRow;
    adams.data.spreadsheet.Cell spCell;
    Cell cell;
    int i;
    int n;
    int count;
    CellStyle styleDate;
    CellStyle styleDateTime;
    CellStyle styleTime;
    HashSet<String> names;
    String name;

    result = true;

    try {
        workbook = new SXSSFWorkbook(m_MaxRows);
        styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT);
        styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT);
        styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT);

        count = 0;
        names = new HashSet<>();
        for (SpreadSheet cont : content) {
            if (m_Stopped)
                return false;

            sheet = workbook.createSheet();
            if (cont.getName() != null) {
                name = cont.getName().replace("'", "");
                if (names.contains(name))
                    name += (count + 1);
            } else {
                name = m_SheetPrefix + (count + 1);
            }
            names.add(name);
            workbook.setSheetName(count, name);

            // header
            row = sheet.createRow(0);
            for (i = 0; i < cont.getColumnCount(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(cont.getHeaderRow().getCell(i).getContent());
            }

            // data
            for (n = 0; n < cont.getRowCount(); n++) {
                if (m_Stopped)
                    return false;

                row = sheet.createRow(n + 1);
                spRow = cont.getRow(n);
                for (i = 0; i < cont.getColumnCount(); i++) {
                    cell = row.createCell(i);
                    spCell = spRow.getCell(i);
                    if ((spCell == null) || spCell.isMissing()) {
                        if (m_MissingValue.length() > 0)
                            cell.setCellValue(m_MissingValue);
                        else
                            cell.setCellType(Cell.CELL_TYPE_BLANK);
                        continue;
                    }

                    if (spCell.isFormula() && !m_OutputAsDisplayed) {
                        cell.setCellFormula(spCell.getFormula().substring(1));
                    } else {
                        if (spCell.isDate()) {
                            cell.setCellValue(spCell.toDate());
                            cell.setCellStyle(styleDate);
                        } else if (spCell.isTime()) {
                            cell.setCellValue(spCell.toTime());
                            cell.setCellStyle(styleTime);
                        } else if (spCell.isDateTime()) {
                            cell.setCellValue(spCell.toDateTime());
                            cell.setCellStyle(styleDateTime);
                        } else if (spCell.isNumeric()) {
                            cell.setCellValue(Utils.toDouble(spCell.getContent()));
                        } else {
                            cell.setCellValue(spCell.getContent());
                        }
                    }
                }
            }

            // next sheet
            count++;
        }

        // save
        workbook.write(out);
    } catch (Exception e) {
        result = false;
        getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e);
    }

    return result;
}

From source file:ADP_Streamline.MatrixReader.java

public String CellIteration(XSSFSheet sheet, String columnletter, int rownum, int columncount, int rowcount)
        throws Exception {

    if (columncount > 0) {
        int column = (int) columnletter.charAt(0) + columncount;
        columnletter = Character.toString((char) column);
    }/* www.  j a  v a 2s.c om*/

    CellReference cr = new CellReference(columnletter + (rownum + rowcount));
    Row row = sheet.getRow(cr.getRow());
    String Roles = row.getCell(cr.getCol()).getStringCellValue();

    return Roles;
}

From source file:ambit2.core.io.IteratingXLSReader.java

License:Open Source License

public Object next() {
    IAtomContainer mol = null;//  www.  j a v a2s  .c o  m
    Map properties = new Hashtable();
    try {
        Row row = (Row) iterator.next();

        for (int col = 0; col < getNumberOfColumns(); col++) {
            Cell cell = row.getCell(col);
            Object value = null;
            if (cell != null)
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    value = "";
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        value = cell.getStringCellValue();
                        break;
                    } catch (Exception x) {
                        try {
                            value = cell.getNumericCellValue();
                        } catch (Exception z) {
                            logger.log(Level.WARNING, x.getMessage(), x);
                        }
                    }
                }
            else
                value = "";
            try {
                if (smilesIndex == col) {
                    try {
                        mol = sp.parseSmiles(value.toString());
                        properties.put(AmbitCONSTANTS.SMILES, value.toString());
                    } catch (InvalidSmilesException x) {
                        logger.warning("Invalid SMILES!\t" + value);
                        properties.put(AmbitCONSTANTS.SMILES, "Invalid SMILES");
                    }
                } else if (col < getNumberOfColumns())
                    properties.put(getHeaderColumn(col), value);
            } catch (Exception x) {
                logger.log(Level.WARNING, x.getMessage(), x);
            }

        }
        if (mol == null)
            mol = SilentChemObjectBuilder.getInstance().newInstance(IMolecule.class);
        mol.setProperties(properties);
        processRow(mol);
    } catch (Exception x) {
        logger.log(Level.SEVERE, x.getMessage(), x);
    }
    return mol;

}

From source file:android_connector.ExcelReader.java

/**
 * Gibt den Wert einer Zelle zurck./* ww  w.j a v a 2s.c o  m*/
 * @param cellName Name der Zelle
 * @return alle Zellen dieses Namens
 */
public String[] getCellValue(String cellName) {
    Name cellsName = wb.getName(cellName);
    AreaReference areaRef = new AreaReference(cellsName.getRefersToFormula(), version);
    CellReference[] cellRef = areaRef.getAllReferencedCells();
    String[] returnValue = new String[cellRef.length];
    for (int i = 0; i < cellRef.length; i++) {
        Row row = this.sheet.getRow(cellRef[i].getRow());
        Cell cell = row.getCell(cellRef[i].getCol());
        returnValue[i] = differCellType(cell);
    }

    return returnValue;
}

From source file:android_connector.ExcelReader.java

/**
 * Gibt den Wert einer Zelle an einer bestimmten Zelle zurck.
 * @param rowIndex Zeile der Zelle/*from  w w w.j a v a 2  s .  co  m*/
 * @param columnIndex SPalte der Zelle
 * @return Wert der Zelle
 */
public String getCellValueAt(int rowIndex, int columnIndex) {
    Row row = this.sheet.getRow(rowIndex);
    Cell cell = row.getCell(columnIndex);
    return differCellType(cell);
}

From source file:apm.common.utils.excel.ImportExcel.java

License:Open Source License

/**
 * ??/*from w  w w. ja  v a 2s  .c  o m*/
 * @param row ?
 * @param column ???
 * @return ?
 */
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                val = cell.getNumericCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

From source file:at.htlpinkafeld.beans.BenutzerkontoBean.java

/**
 * Method used to load stuff from predefined Excel not currently in use
 *//*  w w w  .j av  a2 s.  c  om*/
public void loadFromExcel(ActionEvent event) throws FileNotFoundException, IOException, ParserException {

    if (excel != null) {

        FacesContext.getCurrentInstance().addMessage(null,
                new FacesMessage("Successful", excel.getFileName() + " successfully uploaded!"));

        XSSFWorkbook workbook = new XSSFWorkbook(excel.getInputstream());

        for (int i = 1; i <= 12; i++) {
            int min = 5;
            LocalDate date = LocalDate.of(2016, i, 1);
            int max = min + date.lengthOfMonth() - 1;

            XSSFSheet sheet = workbook.getSheetAt(i);

            for (int j = min; j <= max; j++) {
                Row row = sheet.getRow(j);
                LocalDateTime start = null;
                LocalDateTime end = null;

                LocalDate day = date.withDayOfMonth((int) row.getCell(1).getNumericCellValue());

                //                DataFormatter formatter = new DataFormatter();
                //                System.out.println(formatter.formatCellValue(row.getCell(2)));
                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

                Cell soll = row.getCell(5);
                Cell ist = row.getCell(6);

                CellValue sollValue = null;
                CellValue istValue = null;

                if (soll != null && ist != null) {
                    sollValue = evaluator.evaluate(soll);
                    istValue = evaluator.evaluate(ist);
                }

                if (sollValue != null && istValue != null) {
                    double dif = sollValue.getNumberValue() - istValue.getNumberValue();

                    if (istValue.getNumberValue() != 0.0) {

                        Cell urlaub = row.getCell(10);

                        if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK
                                && urlaub.getNumericCellValue() != 1.0) {

                            Cell cell = row.getCell(2);

                            //for endtime = row 2
                            if (cell != null) {
                                CellValue cellValue = evaluator.evaluate(cell);
                                if (cellValue != null) {
                                    double time = cellValue.getNumberValue() * 24;

                                    String time2;
                                    DecimalFormat df = new DecimalFormat("00.00");
                                    time2 = df.format(time);
                                    time2 = time2.replace(',', ':');
                                    LocalTime localtime = LocalTime.parse(time2);
                                    start = LocalDateTime.of(day, localtime);
                                }
                            }
                            cell = row.getCell(3);

                            //for endtime = row 3
                            if (cell != null) {
                                CellValue cellValue = evaluator.evaluate(cell);
                                if (cellValue != null) {
                                    double time = cellValue.getNumberValue() * 24;

                                    String time2;
                                    DecimalFormat df = new DecimalFormat("00.00");
                                    time2 = df.format(time);
                                    time2 = time2.replace(',', ':');
                                    LocalTime localtime = LocalTime.parse(time2);
                                    end = LocalDateTime.of(day, localtime);
                                }
                            }

                            int breaktime = 0;
                            cell = row.getCell(4);
                            if (cell != null) {
                                CellValue cellValue = evaluator.evaluate(cell);
                                if (cellValue != null) {
                                    double tempbreaktime = cellValue.getNumberValue() * 24 * 60;
                                    breaktime = (int) tempbreaktime;
                                }
                            }

                            String bemerkung = "";
                            Cell comment = row.getCell(11);
                            if (comment != null) {
                                CellValue value = evaluator.evaluate(comment);

                                if (value != null) {
                                    bemerkung = value.formatAsString();
                                    double d;
                                    try {
                                        d = Double.valueOf(bemerkung);
                                        if (BigDecimal.valueOf(d).scale() > 2) {
                                            d = d * 24 * 60;
                                            LocalTime lt = LocalTime.MIN.plusMinutes((int) (d + 0.5));
                                            bemerkung = lt.format(DateTimeFormatter.ofPattern("HH:mm"));
                                        }
                                    } catch (NumberFormatException e) {
                                        //Value is not castable to double and will be ignored -> best case scenario
                                    }
                                }
                            }

                            if (start != null && end != null) {
                                WorkTime worktime = new WorkTime(user, start, end, breaktime, bemerkung, "");
                                IstZeitService.addIstTime(worktime);

                                if (dif > 0.0) {
                                    LocalDateTime absenceend = end.plusMinutes((int) ((dif * 24 * 60) + 0.5));
                                    Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, end,
                                            absenceend, bemerkung);
                                    a.setAcknowledged(true);
                                    AbsenceService.insertAbsence(a);
                                }
                            }
                        } else if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK
                                && urlaub.getNumericCellValue() == 1.0) {

                            start = LocalDateTime.of(day, LocalTime.MIN);
                            end = start;

                            Absence a = new Absence(user, AbsenceTypeNew.HOLIDAY, start, end);
                            a.setAcknowledged(true);
                            AbsenceService.insertAbsence(a);
                        }
                    } else {
                        Cell cell = row.getCell(2);

                        //for endtime = row 2
                        if (cell != null) {
                            CellValue cellValue = evaluator.evaluate(cell);
                            if (cellValue != null) {
                                double time = cellValue.getNumberValue() * 24;

                                String time2;
                                DecimalFormat df = new DecimalFormat("00.00");
                                time2 = df.format(time);
                                time2 = time2.replace(',', ':');
                                LocalTime localtime = LocalTime.parse(time2);
                                start = LocalDateTime.of(day, localtime);
                            }
                        }
                        cell = row.getCell(3);

                        //for endtime = row 3
                        if (cell != null) {
                            CellValue cellValue = evaluator.evaluate(cell);
                            if (cellValue != null) {
                                double time = cellValue.getNumberValue() * 24;

                                String time2;
                                DecimalFormat df = new DecimalFormat("00.00");
                                time2 = df.format(time);
                                time2 = time2.replace(',', ':');
                                LocalTime localtime = LocalTime.parse(time2);
                                end = LocalDateTime.of(day, localtime);
                            }
                        }
                        String bemerkung = "";
                        Cell comment = row.getCell(11);
                        if (comment != null) {
                            CellValue value = evaluator.evaluate(comment);
                            if (value != null) {
                                bemerkung = value.formatAsString();
                            }
                        }
                        Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, start, end, bemerkung);
                        a.setAcknowledged(true);
                        AbsenceService.insertAbsence(a);
                    }
                }
            }
        }
    }
}

From source file:at.jku.xlwrap.spreadsheet.poi.PoiSheet.java

License:Apache License

@Override
public Cell getCell(int column, int row) throws XLWrapException {
    org.apache.poi.ss.usermodel.Row wholeRow = sheet.getRow(row);
    if (wholeRow == null) {
        //TODO is this really the best way?
        return new NullCell();
    }//  ww w  .j a  v  a  2 s .  co  m
    org.apache.poi.ss.usermodel.Cell cell = wholeRow.getCell(column);
    if (cell == null) {
        return new NullCell();
    }
    return new PoiCell(cell, file, sheet.getSheetName());
}

From source file:at.mukprojects.exclycore.model.ExclyDateTest.java

License:Open Source License

/**
 * Tests the ExclyDate setCell function.
 *///  www  .j  av  a 2s  . co m
@Test
public void testExclyDateSetCell() throws Exception {
    Row row = sheet.createRow(0);

    dateOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getDateCellValue() + " / " + calendar.getTime());
    assertEquals(row.getCell(0).getDateCellValue(), calendar.getTime());

    dateError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    dateBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}