Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

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

Usage

From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java

License:Open Source License

public static String getHSSFHeader(String file, int sheet) {
    StringBuffer buf = new StringBuffer();
    try {//w w w .java  2 s  .  c o  m
        FileInputStream fis = new FileInputStream(new File(file));
        //Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        try {
            fis.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        //Get first sheet from the workbook
        HSSFSheet hSSFSheet = workbook.getSheetAt(sheet);
        HSSFRow row = hSSFSheet.getRow(0);

        int cells = row.getPhysicalNumberOfCells();
        for (int c = 0; c < cells; c++) {
            HSSFCell cell = row.getCell(c);
            String value = null;

            switch (cell.getCellType()) {

            case HSSFCell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;

            case HSSFCell.CELL_TYPE_NUMERIC:
                value = "" + cell.getNumericCellValue();
                break;

            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;

            default:
            }
            buf.append(value);
            if (c < cells - 1) {
                buf.append("|");
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return buf.toString();
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void table(final HSSFSheet sheet, int startIndex, int col, String code, boolean cdisc) {
    resolvedValueSetList = new ArrayList();

    if (sheet == null) {
        return;//from  w  ww  .  ja  v  a 2 s .  c o  m
    }
    if (sheet.getDrawingPatriarch() != null) {
        final List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren();
        for (int i = 0; i < shapes.size(); ++i) {
            if (shapes.get(i) instanceof HSSFPicture) {
                try {
                    // Gain access to private field anchor.
                    final HSSFShape pic = shapes.get(i);
                    final Field f = HSSFShape.class.getDeclaredField("anchor");
                    f.setAccessible(true);
                    final HSSFClientAnchor anchor = (HSSFClientAnchor) f.get(pic);
                    // Store picture cell row, column and picture data.
                    if (!pix.containsKey(anchor.getRow1())) {
                        pix.put(anchor.getRow1(), new HashMap<Short, List<HSSFPictureData>>());
                    }
                    if (!pix.get(anchor.getRow1()).containsKey(anchor.getCol1())) {
                        pix.get(anchor.getRow1()).put(anchor.getCol1(), new ArrayList<HSSFPictureData>());
                    }
                    pix.get(anchor.getRow1()).get(anchor.getCol1())
                            .add(book.getAllPictures().get(((HSSFPicture) pic).getPictureIndex()));
                } catch (final Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    out.append("<table id=\"" + "rvs_table" + "\" width=\"915\" class=\"mt\">\n");
    tr(sheet.getRow(0));
    StringBuffer buf = new StringBuffer();
    tr(sheet.getRow(0), buf);
    String t = buf.toString();
    resolvedValueSetList.add(t);
    int rows = sheet.getPhysicalNumberOfRows();

    for (int i = startIndex; i <= rows; i++) {
        HSSFRow row = sheet.getRow(i);
        if (row != null) {
            if (col != -1) {
                HSSFCell cell = row.getCell(col);
                if (cell != null) {
                    String value = null;
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_FORMULA:
                        value = cell.getCellFormula();
                        break;

                    case HSSFCell.CELL_TYPE_NUMERIC:
                        value = "" + cell.getNumericCellValue();
                        break;

                    case HSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;

                    default:
                    }
                    if ((cdisc && i == startIndex) || (value != null && value.compareTo(code) == 0)) {
                        tr(row);
                        buf = new StringBuffer();
                        tr(row, buf);
                        t = buf.toString();
                        resolvedValueSetList.add(t);
                    }
                }
            } else {
                tr(row);
                buf = new StringBuffer();
                tr(row, buf);
                t = buf.toString();
                resolvedValueSetList.add(t);
            }
        }
    }
    out.append("</table>\n");
    resolvedValueSetIterator = resolvedValueSetList.listIterator();
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

/**
 * (Each Excel sheet row becomes an HTML table row) Generates an HTML table
 * row which has the same height as the Excel row.
 *
 * @param row//from  w w w . j a  va  2  s.  com
 *            The Excel row.
 */
private void tr(final HSSFRow row) {
    if (row == null) {
        return;
    }
    out.append("<tr ");
    // Find merged cells in current row.
    for (int i = 0; i < row.getSheet().getNumMergedRegions(); ++i) {
        final CellRangeAddress merge = row.getSheet().getMergedRegion(i);
        if (merge == null)
            return;
        if (rowIndex >= merge.getFirstRow() && rowIndex <= merge.getLastRow()) {
            mergeStart = merge.getFirstColumn();
            mergeEnd = merge.getLastColumn();
            break;
        }
    }
    out.append("style='");
    if (row.getHeight() != -1) {
        out.append("height: ").append(Math.round(row.getHeight() / 20.0 * 1.33333)).append("px; ");
    }
    out.append("'>\n");
    for (colIndex = 0; colIndex < row.getLastCellNum(); ++colIndex) {
        td(row.getCell(colIndex));
    }
    out.append("</tr>\n");
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void tr(final HSSFRow row, StringBuffer buf) {
    if (row == null) {
        return;//from www.j ava  2s. c  o  m
    }
    buf.append("<tr ");
    // Find merged cells in current row.
    for (int i = 0; i < row.getSheet().getNumMergedRegions(); ++i) {
        final CellRangeAddress merge = row.getSheet().getMergedRegion(i);
        if (merge == null)
            return;
        if (rowIndex >= merge.getFirstRow() && rowIndex <= merge.getLastRow()) {
            mergeStart = merge.getFirstColumn();
            mergeEnd = merge.getLastColumn();
            break;
        }
    }
    buf.append("style='");
    if (row.getHeight() != -1) {
        buf.append("height: ").append(Math.round(row.getHeight() / 20.0 * 1.33333)).append("px; ");

    }
    buf.append("'>");
    for (colIndex = 0; colIndex < row.getLastCellNum(); ++colIndex) {
        td(row.getCell(colIndex));
        td(row.getCell(colIndex), buf);
    }
    buf.append("</tr>");
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void getRowData(Vector v, final HSSFRow row) {
    if (row == null) {
        return;/*  w w  w.ja va 2  s  . c  o m*/
    }

    for (int i = 0; i < row.getSheet().getNumMergedRegions(); ++i) {
        final CellRangeAddress merge = row.getSheet().getMergedRegion(i);
        if (merge == null)
            return;
        if (rowIndex >= merge.getFirstRow() && rowIndex <= merge.getLastRow()) {
            mergeStart = merge.getFirstColumn();
            mergeEnd = merge.getLastColumn();
            break;
        }
    }

    StringBuffer buf = new StringBuffer();
    for (colIndex = 0; colIndex < row.getLastCellNum(); ++colIndex) {
        if (colIndex > 0) {
            buf.append("|");
        }
        String val = getCellData(row.getCell(colIndex));
        if (val != null) {
            buf.append(val);
        }
    }
    v.add(buf.toString());
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static HSSFCell createCell(HSSFSheet sheet, int row, short col) {
    if (sheet == null) {
        return null;
    }/*from   w w  w .  j a v a 2s.  co m*/
    HSSFRow rowc = sheet.getRow(row);
    if (rowc == null) {
        rowc = createRow(sheet, row);
    }
    if (rowc != null) {
        HSSFCell cell = rowc.getCell(col);
        if (cell != null) {
            return cell;
        }
        return rowc.createCell(col);
    }
    return null;
}

From source file:gr.abiss.calipso.domain.ExcelFile.java

License:Open Source License

public ExcelFile(InputStream is) {
    POIFSFileSystem fs = null;//  www  .j ava  2 s. co  m
    HSSFWorkbook wb = null;
    try {
        fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;
    int row = 0;
    int col = 0;
    columns = new ArrayList<Column>();
    //========================== HEADER ====================================
    r = sheet.getRow(row);
    while (true) {
        c = r.getCell((short) col);
        if (c == null) {
            break;
        }
        String value = c.getStringCellValue();
        if (value == null || value.trim().length() == 0) {
            break;
        }
        Column column = new Column(value.trim());
        columns.add(column);
        col++;
    }
    //============================ DATA ====================================
    rows = new ArrayList<List<Cell>>();
    while (true) {
        row++;
        r = sheet.getRow(row);
        if (r == null) {
            break;
        }
        List rowData = new ArrayList(columns.size());
        boolean isEmptyRow = true;
        for (col = 0; col < columns.size(); col++) {
            c = r.getCell((short) col);
            Object value = null;
            switch (c.getCellType()) {
            case (HSSFCell.CELL_TYPE_STRING):
                value = c.getStringCellValue();
                break;
            case (HSSFCell.CELL_TYPE_NUMERIC):
                // value = c.getDateCellValue();
                value = c.getNumericCellValue();
                break;
            case (HSSFCell.CELL_TYPE_BLANK):
                break;
            default: // do nothing
            }
            if (value != null && value.toString().length() > 0) {
                isEmptyRow = false;
                rowData.add(new Cell(value));
            } else {
                rowData.add(null);
            }
        }
        if (isEmptyRow) {
            break;
        }
        rows.add(rowData);
    }
}

From source file:gr.abiss.calipso.util.ExcelUtils.java

License:Open Source License

private HSSFCell getCell(int row, int col) {
    HSSFRow sheetRow = sheet.getRow(row);
    if (sheetRow == null) {
        sheetRow = sheet.createRow(row);
    }//w w  w.  j  a va  2s.  c  o  m
    HSSFCell cell = sheetRow.getCell((short) col);
    if (cell == null) {
        cell = sheetRow.createCell((short) col);
    }
    return cell;
}

From source file:guardias.CalendarioExcel.java

private void leerExcelFile(File excelFile, List<Medico> listadoMedicos) {
    InputStream excelStream = null;
    try {/*  w w w  . j a  v a 2 s  .co m*/
        excelStream = new FileInputStream(excelFile);
        // Representacin del ms alto nivel de la hoja excel.
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(excelStream);
        // Elegimos la hoja que se pasa por parmetro.
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        // Objeto que nos permite leer un fila de la hoja excel, y de aqu extraer el contenido de las celdas.
        HSSFRow hssfRow;
        // Obtengo el nmero de filas ocupadas en la hoja
        int rows = hssfSheet.getLastRowNum();
        // Cadena que usamos para almacenar la lectura de la celda
        Date cellCalendario;
        String cellFestivo;
        String cellPeticion;

        this.leerGuardiasPrevistas(hssfSheet.getRow(FILA_DEFINICION_GUARDIAS_PREVISTAS), listadoMedicos);

        List<DiaCalendario> listadoDias = new ArrayList<>();
        // Para este ejemplo vamos a recorrer las filas obteniendo los datos que queremos            
        for (int r = COMIENZO_FILAS; r < rows; r++) {
            hssfRow = hssfSheet.getRow(r);
            if (hssfRow == null) {
                break;
            } else {
                cellCalendario = hssfRow.getCell(COLUMNA_FECHA_MES).getDateCellValue();

                if (cellCalendario != null) {
                    cellFestivo = hssfRow.getCell(COLUMNA_FESTIVO) == null ? ""
                            : hssfRow.getCell(COLUMNA_FESTIVO).getStringCellValue();
                    LOGGER.log(Level.FINE, "Row: {0} -> [Columna {1}: {2}] [Columna {3}: {4}] ",
                            new Object[] { r, COLUMNA_FECHA_MES, cellFestivo, COLUMNA_FESTIVO, cellFestivo });

                    DiaCalendario diaCalendario = new DiaCalendario();
                    diaCalendario.setTime(cellCalendario.getTime());

                    //Se comprueba si es sabado o Domingo
                    if (TratarFechas.esFinde(diaCalendario)) {
                        diaCalendario.setEsFinde(Boolean.TRUE);
                        //TODO setear nivel de importancia (cada dia del finde tiene una importancia)
                    } else {
                        diaCalendario.setEsFinde(Boolean.FALSE);
                    }

                    if (ES_FESTIVO.equalsIgnoreCase(cellFestivo)) {
                        //Por norma general los festivos se pondrn a dedo
                        diaCalendario.setEsFestivo(Boolean.TRUE);
                    } else {
                        diaCalendario.setEsFestivo(Boolean.FALSE);
                    }

                    //No se tiene en cuenta que tipo de falta tiene (solo tiene que estar vacio)
                    this.obtenerDisponibilidadMedicosEnExcel(listadoMedicos, hssfRow, diaCalendario,
                            listadoMedicos);

                    cellPeticion = hssfRow.getCell(COLUMNA_PETICION) == null ? ""
                            : hssfRow.getCell(COLUMNA_PETICION).getStringCellValue();
                    LOGGER.log(Level.FINE, "----{0}", cellPeticion);
                    diaCalendario.setPeticionMedico(cellPeticion);

                    listadoDias.add(diaCalendario);
                }
            }
        }
        this.setListadoDiasCalendario(listadoDias);
    } catch (FileNotFoundException fileNotFoundException) {
        LOGGER.log(Level.WARNING, "The file not exists (No se encontro el fichero): {0}",
                fileNotFoundException);
    } catch (IOException ex) {
        LOGGER.log(Level.WARNING, "Error in file procesing (Error al procesar el fichero): {0}", ex);
    } catch (ExceptionColumnaDisponibilidad ex) {
        LOGGER.log(Level.WARNING,
                "Error en lectura de celdas al leer la dispoibilidad, hay alguna celda que no es ni "
                        + CONSTANTE_FIESTA + " ni " + CONSTANTE_CONSULTA + "{0}",
                ex);
    } finally {
        try {
            excelStream.close();
        } catch (IOException ex) {
            LOGGER.log(Level.WARNING,
                    "Error in file processing after close it (Error al procesar el fichero despues de cerrarlo): {0}",
                    ex);
        }
    }
}

From source file:guardias.CalendarioExcel.java

private int obtenerGuardiasPrevistasExcel(HSSFRow row, int columnaMedico) {
    Double d = row.getCell(columnaMedico) == null ? ERROR_GUARDIA_PREVISTA
            : row.getCell(columnaMedico).getNumericCellValue();
    return d.intValue();
}