Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

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

Usage

From source file:com.gnadenheimer.mg.frames.admin.FrameEntidadesAdmin.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    try {//w  ww . ja va  2  s.c  o  m
        JFileChooser fc = new JFileChooser();
        Integer returnVal = fc.showOpenDialog(this);

        if (returnVal == JFileChooser.APPROVE_OPTION) {

            File file = fc.getSelectedFile();
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            HSSFCell cell;

            Integer rows; // No of rows
            rows = sheet.getPhysicalNumberOfRows();

            Integer cols = 0; // No of columns
            Integer tmp = 0;

            // This trick ensures that we get the data properly even if it doesn't start from first few rows
            for (Integer i = 0; i < 10 || i < rows; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                    if (tmp > cols) {
                        cols = tmp;
                    }
                }
            }

            for (Integer r = 1; r <= rows; r++) {
                row = sheet.getRow(r);
                if (row != null) {

                    if (!row.getCell(0).getStringCellValue().equals("")) {
                        TblEntidades miembro = new TblEntidades();
                        miembro.setNombres(row.getCell(0).getStringCellValue());

                        if (row.getCell(1) != null) {
                            miembro.setApellidos(row.getCell(1).getStringCellValue());
                        } else {
                            miembro.setApellidos("");
                        }
                        if (row.getCell(2).getCellType() == Cell.CELL_TYPE_STRING) {
                            if (row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) {
                                miembro.setCtacte(99999);
                            } else {
                                miembro.setCtacte(Integer.valueOf(
                                        row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", "")));
                            }

                        } else if (row.getCell(2).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            miembro.setCtacte((int) (row.getCell(2).getNumericCellValue()));
                        }
                        List<TblEntidades> duplicadoList = entityManager.createQuery(
                                "SELECT t FROM TblEntidades t where t.nombres = '" + miembro.getNombres()
                                        + "' and t.apellidos = '" + miembro.getApellidos() + "' and t.ctacte = "
                                        + miembro.getCtacte().toString(),
                                TblEntidades.class).getResultList();
                        if (duplicadoList.size() > 0) {
                            miembro = null;
                            miembro = duplicadoList.get(0);
                        }

                        if (row.getCell(3) != null) {
                            DecimalFormat df = new DecimalFormat("#0");
                            miembro.setRucSinDv(df.format(row.getCell(3).getNumericCellValue()));
                            if (miembro.getRucSinDv().equals("0")) {
                                miembro.setRucSinDv("44444401");
                            }
                        }
                        if (row.getCell(4) != null) {
                            miembro.setDomicilio(row.getCell(4).getStringCellValue());
                        }
                        if (row.getCell(5) != null) {
                            miembro.setBox((int) row.getCell(5).getNumericCellValue());
                        }
                        if (row.getCell(6) != null) {
                            miembro.setFechaNacimiento(
                                    LocalDateTime.ofInstant(row.getCell(6).getDateCellValue().toInstant(),
                                            ZoneId.systemDefault()).toLocalDate());
                        }
                        if (row.getCell(7) != null) {
                            miembro.setFechaBautismo(
                                    LocalDateTime.ofInstant(row.getCell(7).getDateCellValue().toInstant(),
                                            ZoneId.systemDefault()).toLocalDate());
                            miembro.setIsMiembroActivo(true);
                        } else {
                            miembro.setIsMiembroActivo(false);
                        }
                        if (row.getCell(8) != null) {
                            miembro.setFechaEntradaCongregacion(
                                    LocalDateTime.ofInstant(row.getCell(8).getDateCellValue().toInstant(),
                                            ZoneId.systemDefault()).toLocalDate());
                        }
                        miembro.setAporteMensual(0);
                        miembro.setIdFormaDePagoPreferida(listFormasDePago.get(0));
                        miembro.setIdUser(currentUser.getUser());

                        entityManager.persist(miembro);
                        list.add(miembro);
                    }
                }
            }

        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null,
                Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
    }
}

From source file:com.gnadenheimer.mg.frames.operaciones.ingresos.FrameColectasDetalle.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    try {//from  ww  w.  j av  a2s .c o m
        JFileChooser fc = new JFileChooser();
        Integer returnVal = fc.showOpenDialog(this);

        if (returnVal == JFileChooser.APPROVE_OPTION) {

            File file = fc.getSelectedFile();
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;

            Integer rows; // No of rows
            rows = sheet.getPhysicalNumberOfRows();

            Integer cols = 0; // No of columns
            Integer tmp = 0;

            // This trick ensures that we get the data properly even if it doesn't start from first few rows
            for (Integer i = 0; i < 10 || i < rows; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                    if (tmp > cols) {
                        cols = tmp;
                    }
                }
            }

            for (Integer r = 1; r <= rows; r++) {
                row = sheet.getRow(r);
                if (row != null) {

                    TblEventoDetalle t = new TblEventoDetalle();
                    TblEventos currEvento = (TblEventos) cboFechaColecta.getSelectedItem();
                    t.setFechahora(currEvento.getFecha().atStartOfDay());
                    t.setIdCategoriaArticulo(entityManager.find(TblCategoriasArticulos.class, 1));
                    t.setIdEvento(currEvento);
                    t.setIdUser(currentUser.getUser());

                    Integer ctaCte = 0;
                    if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
                        if (!row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) {
                            ctaCte = Integer
                                    .valueOf(row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", ""));
                        }
                    } else if (row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        ctaCte = (int) (row.getCell(0).getNumericCellValue());
                    }
                    if (ctaCte != 0) {
                        final Integer cc = ctaCte;
                        List<TblEntidades> list = listMiembros;
                        Optional<TblEntidades> value = list.stream().filter(a -> a.getCtacte().equals(cc))
                                .findFirst();
                        if (value.isPresent()) {
                            t.setIdEntidad(value.get());
                            t.setIdFormaDePagoPreferida(tblFormasDePagoList.get(0));
                            if (row.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                t.setMonto((int) (row.getCell(1).getNumericCellValue()));
                                entityManager.persist(t);
                                listEventoDetalle.add(t);
                            }
                        } else {
                            JOptionPane.showMessageDialog(null,
                                    "No hay entidad registrada con Cta.Cte N " + FormatCtaCte.format(ctaCte));
                        }
                    }
                }
            }

        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null,
                Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
    }
}

From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTables.java

License:Open Source License

public ExcelExampleTable getTable(String tableDescription) {

    HSSFSheet sheet = workbook.getSheetAt(0);
    int rowIndex = -1;
    int cellnum = -1;
    HSSFCell cell = null;/*ww  w .  j  a  v a  2 s . co m*/
    try {
        for (rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            if (null == row) {
                continue;
            }
            for (cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
                cell = row.getCell(cellnum);
                if ((HSSFCell.CELL_TYPE_STRING == cell.getCellType())
                        && tableDescription.equals(cell.getRichStringCellValue().getString())) {
                    return new ExcelExampleTable(sheet, rowIndex, cellnum);
                }
            }
        }
    } catch (Exception e) {
        throw new IllegalStateException("Problem with cell (row=" + rowIndex + ",cellnum=" + cellnum + ",val="
                + cell + ") getting table [" + tableDescription + "] from [" + xlsFilePath + "]", e);

    }
    throw new IllegalArgumentException("Can't find [" + tableDescription + "] in [" + xlsFilePath + "]");
}

From source file:com.haulmont.mp2xls.object.LocalizationsBatch.java

License:Apache License

protected void readLocalizationFile(File source, String lcFilePath) throws IOException {
    FileInputStream fis = new FileInputStream(source);
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet sheet = wb.getSheetAt(0);

    if (lcFilePath == null)
        projectDirectory = (String) XlsHelper.getCellValue(sheet.getRow(0).getCell(1));
    else//from w  w w .j  a v  a2 s  .co  m
        projectDirectory = lcFilePath;

    int firstDataRowIndex = 6;
    int headingRow = 5;

    for (int currentRowIndex = firstDataRowIndex; !XlsHelper
            .eof(sheet.getRow(currentRowIndex)); currentRowIndex++) {
        HSSFRow row = sheet.getRow(currentRowIndex);

        String path = (String) XlsHelper.getCellValue(row.getCell(0));
        String param = (String) XlsHelper.getCellValue(row.getCell(1));

        Set<MessagesLocalization> localizations;
        if (messagesLocalizations.keySet().contains(path))
            localizations = messagesLocalizations.get(path);
        else {
            localizations = new HashSet<>();
            messagesLocalizations.put(path, localizations);
        }

        for (int col = 2; XlsHelper.getCellValue(sheet.getRow(headingRow).getCell(col)) != null; col++) {
            String localeId = (String) XlsHelper.getCellValue(sheet.getRow(headingRow).getCell(col));
            Object val = XlsHelper.getCellValue(row.getCell(col));
            String value = val != null ? val.toString() : null;
            localeId = localeId.equals("default") ? null : localeId;
            String relPath = path + (localeId == null ? "" : "_" + localeId) + ".properties";

            MessagesLocalization local = null;
            for (MessagesLocalization l : localizations) {
                if (l.getPath().equals(relPath)) {
                    local = l;
                    break;
                }
            }

            if (local == null) {
                local = new MessagesLocalization(localeId, relPath);
                localizationIds.add(localeId);
                localizations.add(local);
            }

            local.getMessages().put(param, value);
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixLeftCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFCell leftCell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (leftCell != null) {
        HSSFCellStyle leftCellStyle = leftCell.getCellStyle();
        if (leftCellStyle.getBorderRight() != cellStyle.getBorderLeft()
                || leftCellStyle.getRightBorderColor() != cellStyle.getLeftBorderColor()) {
            HSSFCellStyle draftLeftStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
            XslStyleHelper.cloneStyleRelations(leftCellStyle, draftLeftStyle);
            draftLeftStyle.setBorderRight(cellStyle.getBorderLeft());
            draftLeftStyle.setRightBorderColor(cellStyle.getLeftBorderColor());
            HSSFCellStyle newLeftStyle = styleCache.getCellStyleByTemplate(draftLeftStyle);
            if (newLeftStyle == null) {
                newLeftStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftLeftStyle);
                styleCache.processCellStyle(newLeftStyle);
            }//from w ww .  j a v  a  2s  .com

            leftCell.setCellStyle(newLeftStyle);
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixRightCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFCell rightCell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (rightCell != null) {
        HSSFCellStyle rightCellStyle = rightCell.getCellStyle();

        if (rightCellStyle.getBorderLeft() != cellStyle.getBorderRight()
                || rightCellStyle.getLeftBorderColor() != cellStyle.getRightBorderColor()) {
            HSSFCellStyle draftRightStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
            XslStyleHelper.cloneStyleRelations(rightCellStyle, draftRightStyle);
            draftRightStyle.setBorderLeft(cellStyle.getBorderRight());
            draftRightStyle.setLeftBorderColor(cellStyle.getRightBorderColor());

            HSSFCellStyle newRightStyle = styleCache.getCellStyleByTemplate(draftRightStyle);
            if (newRightStyle == null) {
                newRightStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftRightStyle);
                styleCache.processCellStyle(newRightStyle);
            }//from  w  w  w . j  a v  a  2s .  c o m

            rightCell.setCellStyle(newRightStyle);
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixUpCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFCell upCell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (upCell != null) {
        HSSFCellStyle upCellStyle = upCell.getCellStyle();

        if (upCellStyle.getBorderBottom() != cellStyle.getBorderTop()
                || upCellStyle.getBottomBorderColor() != cellStyle.getTopBorderColor()) {
            HSSFCellStyle draftUpStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
            XslStyleHelper.cloneStyleRelations(upCellStyle, draftUpStyle);
            draftUpStyle.setBorderBottom(cellStyle.getBorderTop());
            draftUpStyle.setBottomBorderColor(cellStyle.getTopBorderColor());

            HSSFCellStyle newUpStyle = styleCache.getCellStyleByTemplate(draftUpStyle);
            if (newUpStyle == null) {
                newUpStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftUpStyle);
                styleCache.processCellStyle(newUpStyle);
            }/*from   w  ww .  ja  v a2 s. co m*/

            upCell.setCellStyle(newUpStyle);
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixDownCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFRow nextRow = sheet.getRow(rowIndex);
    if (nextRow != null) {
        HSSFCell downCell = nextRow.getCell(columnIndex);
        if (downCell != null) {
            HSSFCellStyle downCellStyle = downCell.getCellStyle();

            if (downCellStyle.getBorderTop() != cellStyle.getBorderBottom()
                    || downCellStyle.getTopBorderColor() != cellStyle.getBottomBorderColor()) {
                HSSFCellStyle draftDownStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
                XslStyleHelper.cloneStyleRelations(downCellStyle, draftDownStyle);
                draftDownStyle.setBorderTop(cellStyle.getBorderBottom());
                draftDownStyle.setTopBorderColor(cellStyle.getBottomBorderColor());

                HSSFCellStyle newDownStyle = styleCache.getCellStyleByTemplate(draftDownStyle);
                if (newDownStyle == null) {
                    newDownStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(),
                            draftDownStyle);
                    styleCache.processCellStyle(newDownStyle);
                }//from   w ww . j a  va  2 s  .c o m

                downCell.setCellStyle(newDownStyle);
            }
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.HSSFCellHelper.java

License:Apache License

public static HSSFCell getCellFromReference(HSSFSheet templateSheet, int colIndex, int rowIndex) {
    HSSFRow row = templateSheet.getRow(rowIndex);
    row = row == null ? templateSheet.createRow(rowIndex) : row;
    HSSFCell cell = row.getCell(colIndex);
    cell = cell == null ? row.createCell(colIndex) : cell;
    return cell;/*from ww w. j  a v a  2s  .  c  o  m*/
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

protected void cleanupCells(HSSFSheet resultSheet) {
    for (int i = resultSheet.getFirstRowNum(); i <= resultSheet.getLastRowNum(); i++) {
        HSSFRow row = resultSheet.getRow(i);
        if (row != null) {
            for (int j = 0; j < row.getLastCellNum(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell != null) {
                    row.removeCell(cell);
                }/*from  ww w.  ja v a2  s. co  m*/
            }
        }
    }
}