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

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

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

Introduction

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

Prototype


@Override
public int getPhysicalNumberOfCells()

Source Link

Document

gets the number of defined cells (NOT number of cells in the actual row!).

Usage

From source file:org.metaeffekt.core.inventory.processor.reader.InventoryReader.java

License:Apache License

@Override
protected void readLicenseMetaDataHeader(HSSFRow row) {
    for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = row.getCell(i);/*from   w  w w.  j  ava2s .co m*/
        if (cell != null) {
            columnMap.put(i, cell.getStringCellValue());
        }
    }
}

From source file:org.projectforge.excel.ExcelImport.java

License:Open Source License

/**
 * returns the columnnames found/*ww  w. ja  va 2  s  . c  o m*/
 * 
 * @return list of columnnames
 */
public List<String> getColumnNames() {
    final HSSFSheet sheet = work.getSheetAt(activeSheet);
    final HSSFRow columnNames = sheet.getRow(columnNameRow);
    final List<String> list = new ArrayList<String>();
    for (int column = 0; column < columnNames.getPhysicalNumberOfCells(); column++) {
        if (columnNames.getCell(column) == null) {
            continue;
        }
        final String columnName = columnNames.getCell(column).getStringCellValue();
        if (columnName != null) {
            list.add(columnName.trim());
        }
    }
    return list;
}

From source file:org.projectforge.excel.ExcelImport.java

License:Open Source License

/**
 * convert a single row to an object.//from   w  w w  .ja va 2 s  . c o m
 * 
 * @param row the row containing the values.
 * @param columnNames the row containing the column-names.
 * @param rowNum the current rownum
 * @return a new created object populated with the values.
 * @throws InstantiationException if the object creation fails.
 * @throws IllegalAccessException if the object creation fails or the invoked setter is not public.
 * @throws InvocationTargetException if the object creation fails with an exception or the setter threw an exception.
 * @throws NoSuchMethodException if the setter for the property name is not existant.
 */
private T convertToBean(final HSSFRow row, final HSSFRow columnNames, final int rowNum)
        throws InstantiationException, IllegalAccessException, InvocationTargetException,
        NoSuchMethodException {
    if (row == null) {
        log.debug("created no bean for row#" + rowNum);
        return null;
    }
    final T o = clazzFactory.newInstance(row);
    if (columnNames == null) {
        return null;
    }
    for (int column = 0; column < columnNames.getPhysicalNumberOfCells(); column++) {
        if (columnNames.getCell(column) == null) {
            continue;
        }
        String columnName = columnNames.getCell(column).getStringCellValue();
        if (columnName != null) {
            columnName = columnName.trim();
        }
        String propName = columnName;
        if (columnToPropertyMap != null) {
            final String mapName = columnToPropertyMap.get(columnName);
            if (mapName != null) {
                propName = mapName.trim();
            }
        }
        try {
            final Class<?> destClazz = PropertyUtils.getPropertyType(o, propName);
            if (propName == null || destClazz == null) {
                log.debug("Skipping column " + columnName);
                continue;
            }
            final Object value = toNativeType(row.getCell(column), destClazz);
            log.debug("Setting property=" + propName + " to " + value + " class="
                    + ClassUtils.getShortClassName(value, "null"));
            PropertyUtils.setProperty(o, propName, value);
        } catch (final ConversionException e) {
            log.warn(e);
            throw new ExcelImportException("Falscher Datentyp beim Excelimport", new Integer(row.getRowNum()),
                    columnName);
        } catch (final Exception e) {
            log.warn(e);
            throw new ExcelImportException("Falscher Datentyp beim Excelimport", new Integer(row.getRowNum()),
                    columnName);
        }
    }
    if (log.isDebugEnabled() == true) {
        log.debug("created bean " + o + " for row#" + rowNum);
    }
    return o;
}

From source file:org.rti.zcore.dar.utils.PoiUtils.java

License:Apache License

/**
 * This utility is a version of HSSF.main that does not use deprecated methods.
 * It is helpful in figuring out what row a filed is on when outputting Excel files via POI.
 * @param pathExcelMaster/*from w w w.  ja va  2 s .c om*/
 */
public static void testExcelOutput(String pathExcelMaster) {

    try {
        //HSSF hssf = new HSSF(args[ 0 ]);

        System.out.println("Data dump:\n");
        //HSSFWorkbook wb = hssf.hssfworkbook;
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster));
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            System.out.println("Sheet " + k);
            HSSFSheet sheet = wb.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();

            for (int r = 0; r < rows; r++) {
                //HSSFRow row   = sheet.getPhysicalRowAt(r);
                HSSFRow row = sheet.getRow(r);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("ROW " + row.getRowNum());
                    for (int c = 0; c < cells; c++) {
                        //HSSFCell cell  = row.getPhysicalCellAt(c);
                        HSSFCell cell = row.getCell(c);
                        String value = null;
                        if (cell != null) {
                            switch (cell.getCellType()) {

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

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

                            case HSSFCell.CELL_TYPE_STRING:
                                //value = "STRING value=" + cell.getStringCellValue();
                                HSSFRichTextString str = cell.getRichStringCellValue();
                                value = "STRING value=" + str;
                                break;

                            default:
                            }
                            //System.out.println("CELL col=" + cell.getCellNum()  + " VALUE=" + value);
                            System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:org.wso2.ws.dataservice.DBUtils.java

License:Apache License

private static short getExcelColumnNumber(String columnName, HSSFRow headerRow) throws AxisFault {
    int noOfCells = headerRow.getPhysicalNumberOfCells();
    short columnNo = -1;
    for (int a = 0; a < noOfCells; a++) {
        HSSFCell cell = headerRow.getCell((short) a);
        if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
            if (columnName.equals(cell.getRichStringCellValue().getString())) {
                columnNo = (short) a;
                break;
            }//  w  ww.j  av a  2 s . c  o  m
        } else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
            try {
                double columnNameInDouble = Double.valueOf(columnName).doubleValue();
                if (columnNameInDouble == cell.getNumericCellValue()) {
                    columnNo = (short) a;
                    break;
                }
            } catch (NumberFormatException e) {
                log.error("Numeric value expected for Column Name : " + columnName, e);
                throw new AxisFault("Numeric value expected for Column Name : " + columnName, e);
            }
        }
    }
    return columnNo;
}

From source file:pe.gob.mef.gescon.web.ui.BaseLegalMB.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//from  w w  w.  j  a v  a2s .c  om

    //Para los datos
    HSSFCellStyle centerStyle = wb.createCellStyle();
    centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle centerGrayStyle = wb.createCellStyle();
    centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFCellStyle grayBG = wb.createCellStyle();
    grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int i = 1;
    for (BaseLegal b : this.getListaBaseLegal()) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            HSSFCell cell = row.getCell(j);
            if (i % 2 == 0) {
                if (j > 0) {
                    cell.setCellStyle(centerGrayStyle);
                } else {
                    cell.setCellStyle(grayBG);
                    cell.setCellValue(b.getVnumero());
                }
            } else {
                if (j > 0) {
                    cell.setCellStyle(centerStyle);
                } else {
                    cell.setCellValue(b.getVnumero());
                }
            }
        }
        i++;
    }

    // Para la cabecera
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(font);

    for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) {
        HSSFCell cell = header.getCell(j);
        cell.setCellStyle(headerStyle);
        sheet.autoSizeColumn(j);
    }
}

From source file:pe.gob.mef.gescon.web.ui.BuenaPracticaMB.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//from  ww  w .j  a v  a 2s  .  c  o m

    //Para los datos
    HSSFCellStyle centerStyle = wb.createCellStyle();
    centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle centerGrayStyle = wb.createCellStyle();
    centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFCellStyle grayBG = wb.createCellStyle();
    grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int i = 1;
    for (Conocimiento c : this.getListaBuenaPractica()) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            HSSFCell cell = row.getCell(j);
            if (i % 2 == 0) {
                if (j > 0) {
                    cell.setCellStyle(centerGrayStyle);
                } else {
                    cell.setCellStyle(grayBG);
                    cell.setCellValue(c.getVtitulo());
                }
            } else {
                if (j > 0) {
                    cell.setCellStyle(centerStyle);
                } else {
                    cell.setCellValue(c.getVtitulo());
                }
            }
        }
        i++;
    }

    // Para la cabecera
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(font);

    for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) {
        HSSFCell cell = header.getCell(j);
        cell.setCellStyle(headerStyle);
        sheet.autoSizeColumn(j);
    }
}

From source file:pe.gob.mef.gescon.web.ui.ContenidoMB.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);// w  w  w  . j a  v  a  2  s. co m

    //Para los datos
    HSSFCellStyle centerStyle = wb.createCellStyle();
    centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle centerGrayStyle = wb.createCellStyle();
    centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFCellStyle grayBG = wb.createCellStyle();
    grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int i = 1;
    for (Conocimiento c : this.getListaContenido()) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            HSSFCell cell = row.getCell(j);
            if (i % 2 == 0) {
                if (j > 0) {
                    cell.setCellStyle(centerGrayStyle);
                } else {
                    cell.setCellStyle(grayBG);
                    cell.setCellValue(c.getVtitulo());
                }
            } else {
                if (j > 0) {
                    cell.setCellStyle(centerStyle);
                } else {
                    cell.setCellValue(c.getVtitulo());
                }
            }
        }
        i++;
    }

    // Para la cabecera
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(font);

    for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) {
        HSSFCell cell = header.getCell(j);
        cell.setCellStyle(headerStyle);
        sheet.autoSizeColumn(j);
    }
}

From source file:pe.gob.mef.gescon.web.ui.OportunidadMB.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*from   w ww .j  ava2 s.co m*/

    //Para los datos
    HSSFCellStyle centerStyle = wb.createCellStyle();
    centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle centerGrayStyle = wb.createCellStyle();
    centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFCellStyle grayBG = wb.createCellStyle();
    grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int i = 1;
    for (Conocimiento c : this.getListaOportunidad()) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            HSSFCell cell = row.getCell(j);
            if (i % 2 == 0) {
                if (j > 0) {
                    cell.setCellStyle(centerGrayStyle);
                } else {
                    cell.setCellStyle(grayBG);
                    cell.setCellValue(c.getVtitulo());
                }
            } else {
                if (j > 0) {
                    cell.setCellStyle(centerStyle);
                } else {
                    cell.setCellValue(c.getVtitulo());
                }
            }
        }
        i++;
    }

    // Para la cabecera
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(font);

    for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) {
        HSSFCell cell = header.getCell(j);
        cell.setCellStyle(headerStyle);
        sheet.autoSizeColumn(j);
    }
}

From source file:pe.gob.mef.gescon.web.ui.PreguntaMB.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//  w w  w .  j  a v a  2 s . c  o  m

    //Para los datos
    HSSFCellStyle centerStyle = wb.createCellStyle();
    centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle centerGrayStyle = wb.createCellStyle();
    centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFCellStyle grayBG = wb.createCellStyle();
    grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int i = 1;
    for (Pregunta p : this.getListaPregunta()) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            HSSFCell cell = row.getCell(j);
            if (i % 2 == 0) {
                if (j > 0) {
                    cell.setCellStyle(centerGrayStyle);
                } else {
                    cell.setCellStyle(grayBG);
                    cell.setCellValue(p.getVasunto());
                }
            } else {
                if (j > 0) {
                    cell.setCellStyle(centerStyle);
                } else {
                    cell.setCellValue(p.getVasunto());
                }
            }
        }
        i++;
    }

    // Para la cabecera
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(font);

    for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) {
        HSSFCell cell = header.getCell(j);
        cell.setCellStyle(headerStyle);
        sheet.autoSizeColumn(j);
    }
}