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

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

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

Introduction

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

Prototype

@Override
public short getLastCellNum()

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:no.abmu.common.excel.PoiExcelImpl.java

License:Open Source License

private List<String> getKeyNames() {

    HSSFRow row = activeSheet.getRow(keyRowIdx);
    if (row == null) {
        String errorMessage = "The Excel sheet '" + getActiveSheetName() + "' does not have row '" + keyRowIdx
                + "'";
        logger.error(errorMessage);//from w w w.  java  2 s. com
        throw new IllegalStateException(errorMessage);
    }

    List<String> keyNames = new ArrayList<String>();
    for (int columnNumber = 0; columnNumber < row.getLastCellNum(); columnNumber++) {

        String keyName = getStringValue(row, columnNumber);
        if (keyName == null) {
            keyNames.add("");
        } else {
            keyNames.add(keyName.trim());
        }
    }

    return keyNames;
}

From source file:no.uio.medicine.virsurveillance.parsers.XlsPopulationParser.java

private void readCountries(String inputFile) {
    try {//from ww w  .j  ava  2  s .  c o  m
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(inputFile));
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        //            
        HSSFSheet sheet = wb.getSheetAt(1); //page with the information of the countries
        HSSFRow row;
        HSSFCell cell;

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

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

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

        HSSFRow header = sheet.getRow(0);

        int ccInd = 0;
        int regInd = 0;
        int igInd = 0;
        int namInd = 0;
        for (int i = 0; i < header.getLastCellNum(); i++) {
            if (header.getCell(i).toString().equalsIgnoreCase("Country Code")) {
                ccInd = i;
            }
            if (header.getCell(i).toString().equalsIgnoreCase("Region")) {
                regInd = i;
            }
            if (header.getCell(i).toString().equalsIgnoreCase("IncomeGroup")) {
                igInd = i;
            }
            if (header.getCell(i).toString().equalsIgnoreCase("TableName")) {
                namInd = i;
            }
        }

        for (int r = 1; r < rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {
                CountryData cd;
                if (row.getCell(regInd) != null && row.getCell(igInd) != null) {
                    cd = new CountryData(row.getCell(namInd).toString().replace("'", "`"),
                            row.getCell(ccInd).toString().replace("'", "`"),
                            row.getCell(regInd).toString().replace("'", "`"),
                            row.getCell(igInd).toString().replace("'", "`"));

                } else {
                    cd = new CountryData(row.getCell(namInd).toString(), row.getCell(ccInd).toString(), "", "",
                            true);

                }
                countries.add(cd);

            }
        }
    } catch (Exception ioe) {
        ioe.printStackTrace();
        System.out.println("##### ERROR: It looks like " + inputFile
                + " is not the appropriate type of file or it is not propperly structured");
    }
}

From source file:no.uio.medicine.virsurveillance.parsers.XlsPopulationParser.java

private void readAndStorePopulations(String inputFile) {
    try {/*from w w w.  j a v a  2s.  c  om*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(inputFile));
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        //            
        HSSFSheet sheet = wb.getSheetAt(0); //page with the population of the countries
        HSSFRow row;
        HSSFCell cell;

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

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

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

        //Start reading countries;
        HSSFRow header = sheet.getRow(3);
        ArrayList<Integer> years = new ArrayList<>();
        for (int i = 4; i < header.getLastCellNum(); i++) {
            years.add(Integer.parseInt(header.getCell(i).toString()));

        }

        for (int r = 4; r < rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {
                String countryName = row.getCell(1).toString();

                int count = 0;
                for (int i = 4; i < row.getLastCellNum(); i++) {
                    if (row.getCell(i) != null) {
                        //System.out.print(years.get(count)+": "+Float.parseFloat(row.getCell(i).toString())+ " - ");
                        try {
                            sqlM.addPopulation(countryName, years.get(count), row.getCell(i).toString());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    count++;
                }

            }
        }
    } catch (Exception ioe) {
        ioe.printStackTrace();
        System.out.println("##### ERROR: It looks like " + inputFile
                + " is not the appropriate type of file or it is not propperly structured");
    }
}

From source file:org.apache.cocoon.generation.HSSFGenerator.java

License:Apache License

/**
 * Returns the max column index of the given sheet
 * @param sheet/*from w  w  w .j  a v a 2 s .co  m*/
 * @return the max column index
 */
private int getMaxCol(HSSFSheet sheet) {
    int max = -1;
    HSSFRow row = null;
    Iterator rows = sheet.rowIterator();
    while (rows.hasNext()) {
        row = (HSSFRow) rows.next();
        int lastNum = row.getLastCellNum();
        if (lastNum > max) {
            max = lastNum;
        }
    }
    return max;
}

From source file:org.apache.directory.studio.ldapbrowser.core.jobs.ExportXlsRunnable.java

License:Apache License

/**
 * {@inheritDoc}/*from  w  w  w.j  av  a  2s .c om*/
 */
public void run(StudioProgressMonitor monitor) {
    monitor.beginTask(BrowserCoreMessages.jobs__export_xls_task, 2);
    monitor.reportProgress(" "); //$NON-NLS-1$
    monitor.worked(1);

    Preferences coreStore = BrowserCorePlugin.getDefault().getPluginPreferences();
    String valueDelimiter = coreStore.getString(BrowserCoreConstants.PREFERENCE_FORMAT_XLS_VALUEDELIMITER);
    int binaryEncoding = coreStore.getInt(BrowserCoreConstants.PREFERENCE_FORMAT_XLS_BINARYENCODING);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Export"); //$NON-NLS-1$

    // header
    HSSFRow headerRow = sheet.createRow(0);
    LinkedHashMap<String, Integer> attributeNameMap = new LinkedHashMap<String, Integer>();
    if (this.exportDn) {
        int cellNum = 0;
        attributeNameMap.put("dn", cellNum); //$NON-NLS-1$
        createStringCell(headerRow, cellNum).setCellValue("dn"); //$NON-NLS-1$
    }

    // max export
    if (searchParameter.getCountLimit() < 1 || searchParameter.getCountLimit() > MAX_COUNT_LIMIT) {
        searchParameter.setCountLimit(MAX_COUNT_LIMIT);
    }

    // export
    try {
        int count = 0;
        exportToXls(browserConnection, searchParameter, sheet, headerRow, count, monitor, attributeNameMap,
                valueDelimiter, binaryEncoding, this.exportDn);
    } catch (Exception e) {
        monitor.reportError(e);
    }

    // column width
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        HSSFRow row = sheet.getRow(i);
        for (short j = 0; row != null && j <= row.getLastCellNum(); j++) {
            HSSFCell cell = row.getCell(j);
            if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                String value = cell.getStringCellValue();

                if ((short) (value.length() * 256 * 1.1) > sheet.getColumnWidth(j)) {
                    sheet.setColumnWidth(j, (short) (value.length() * 256 * 1.1));
                }
            }
        }
    }

    try {
        FileOutputStream fileOut = new FileOutputStream(exportXlsFilename);
        wb.write(fileOut);
        fileOut.close();
    } catch (Exception e) {
        monitor.reportError(e);
    }
}

From source file:org.apache.nutch.parse.msexcel.ExcelExtractor.java

License:Apache License

protected String extractText(InputStream input) throws Exception {

    StringBuffer resultText = new StringBuffer(SBUF_SIZE); // TODO FIXME MC
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return resultText.toString();
    }//from   w w w . j a  va  2s  .  c o  m

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                if ((cell = row.getCell((short) k)) != null) {
                    /*if(HSSFDateUtil.isCellDateFormatted(cell) == true) {
                        resultText += cell.getDateCellValue().toString() + " ";
                      } else
                     */
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        resultText.append(cell.getStringCellValue()).append(" ");
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        Double d = new Double(cell.getNumericCellValue());
                        resultText.append(d.toString()).append(" ");
                    }
                    /* else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
                         resultText += cell.getCellFormula() + " ";
                       } 
                     */
                }
            }
        }
    }
    return resultText.toString();
}

From source file:org.beanfuse.transfer.importer.reader.ExcelItemReader.java

License:Open Source License

/**
 * ????/* w ww.  ja va2  s  . c  om*/
 * 
 * @param sheet
 * @param rowIndex
 * @return
 */
protected String[] readLine(HSSFSheet sheet, int rowIndex) {
    HSSFRow row = sheet.getRow(rowIndex);
    if (logger.isDebugEnabled()) {
        logger.debug("values count:{}" + row.getLastCellNum());
    }
    List attrList = new ArrayList();
    for (short i = 0; i < row.getLastCellNum(); i++) {
        HSSFCell cell = row.getCell(i);
        if (null != cell) {
            String attr = cell.getRichStringCellValue().getString();
            if (StringUtils.isEmpty(attr)) {
                break;
            } else {
                attrList.add(attr.trim());
            }
        } else {
            break;
        }
    }
    String[] attrs = new String[attrList.size()];
    attrList.toArray(attrs);
    logger.debug("has attrs {}", attrs);
    return attrs;
}

From source file:org.beanfuse.transfer.importer.reader.ExcelItemReader.java

License:Open Source License

public Object read() {
    HSSFSheet sheet = workbook.getSheetAt(sheetNum);
    if (indexInSheet > sheet.getLastRowNum()) {
        return null;
    }/*from w  w w .  j  av  a2s.  c o m*/
    HSSFRow row = sheet.getRow(indexInSheet);
    indexInSheet++;
    // ,
    if (row == null) {
        return new Object[attrCount];
    } else {
        Object[] values = new Object[((attrCount != 0) ? attrCount : row.getLastCellNum())];
        for (short k = 0; k < values.length; k++) {
            String celValue = getCelValue(row.getCell(k));
            if (null != celValue) {
                celValue = celValue.trim();
            }
            values[k] = celValue;
        }
        return values;
    }
}

From source file:org.beangle.commons.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * ????/*from   www .  jav  a2 s .  c  o m*/
 * 
 * @param sheet a {@link org.apache.poi.hssf.usermodel.HSSFSheet} object.
 * @param rowIndex a int.
 * @return an array of {@link java.lang.String} objects.
 */
protected String[] readLine(HSSFSheet sheet, int rowIndex) {
    HSSFRow row = sheet.getRow(rowIndex);
    logger.debug("values count:{}", row.getLastCellNum());
    List<String> attrList = CollectUtils.newArrayList();
    for (int i = 0; i < row.getLastCellNum(); i++) {
        HSSFCell cell = row.getCell(i);
        if (null != cell) {
            String attr = cell.getRichStringCellValue().getString();
            if (Strings.isEmpty(attr)) {
                break;
            } else {
                attrList.add(attr.trim());
            }
        } else {
            break;
        }
    }
    String[] attrs = new String[attrList.size()];
    attrList.toArray(attrs);
    logger.debug("has attrs {}", attrs);
    return attrs;
}

From source file:org.beangle.commons.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * <p>//from w  ww  . ja va2  s . c  o  m
 * read.
 * </p>
 * 
 * @return a {@link java.lang.Object} object.
 */
public Object read() {
    HSSFSheet sheet = workbook.getSheetAt(sheetNum);
    if (indexInSheet > sheet.getLastRowNum()) {
        return null;
    }
    HSSFRow row = sheet.getRow(indexInSheet);
    indexInSheet++;
    // ,
    if (row == null) {
        return new Object[attrCount];
    } else {
        Object[] values = new Object[((attrCount != 0) ? attrCount : row.getLastCellNum())];
        for (int k = 0; k < values.length; k++) {
            values[k] = getCellValue(row.getCell(k));
        }
        return values;
    }
}