Example usage for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getStringCellValue

Introduction

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

Prototype

public String getStringCellValue()

Source Link

Document

get the value of the cell as a string - for numeric cells we throw an exception.

Usage

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) {

    // Copy cell content
    destination.setCellType(source.getCellType());
    switch (source.getCellType()) {
    case HSSFCell.CELL_TYPE_BOOLEAN:
        destination.setCellValue(source.getBooleanCellValue());
        break;/*from  w  w w.  j  a  v a2s  .com*/
    case HSSFCell.CELL_TYPE_FORMULA:
    case HSSFCell.CELL_TYPE_STRING:
        destination.setCellValue(source.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        destination.setCellValue(source.getNumericCellValue());
        break;
    }

    // Copy cell style
    HSSFCellStyle sourceCellStyle = source.getCellStyle();
    HSSFCellStyle destinationCellStyle = workbook.createCellStyle();
    destinationCellStyle.setAlignment(sourceCellStyle.getAlignment());
    destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
    destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
    destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight());
    destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop());
    destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
    destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat());
    destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor());
    destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor());
    destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern());
    destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex()));
    destinationCellStyle.setHidden(sourceCellStyle.getHidden());
    destinationCellStyle.setIndention(sourceCellStyle.getIndention());
    destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
    destinationCellStyle.setLocked(sourceCellStyle.getLocked());
    destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
    destinationCellStyle.setRotation(sourceCellStyle.getRotation());
    destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
    destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
    destinationCellStyle.setWrapText(sourceCellStyle.getWrapText());
    destination.setCellStyle(destinationCellStyle);
}

From source file:org.projectforge.business.fibu.datev.BuchungssatzExcelImporter.java

License:Open Source License

/**
 * Dummerweise ist im DATEV-Export die Spalte SH zweimal vertreten. Da wir SH aber fr Haben/Soll auswerten mssen, mssen die Spalten
 * unterschiedlich heien. Die zweite Spalte wird hier in SH2 umbenannt, sofern vorhanden.
 * @param sheet/*  w w  w. j av  a  2 s.c o  m*/
 */
private void rename2ndSH(final HSSFSheet sheet) {
    try {
        final HSSFRow row = sheet.getRow(ROW_COLUMNNAMES);
        if (row == null) {
            return;
        }
        short numberOfSH = 0;
        for (int col = 0; col < MAX_COLUMNS; col++) {
            final HSSFCell cell = row.getCell(col);
            if (cell == null) {
                break;
            }
            final String name = cell.getStringCellValue();
            log.debug("Processing column '" + name + "'");
            if ("SH".equals(cell.getStringCellValue()) == true) {
                numberOfSH++;
                if (numberOfSH == 2) {
                    log.debug("Renaming 2nd column 'SH' to 'SH2' (column no. " + col + ").");
                    cell.setCellValue("SH2");
                }
            }
        }
    } catch (final Exception ex) {
        log.error(ex.getMessage(), ex);
        throw new UserException(
                ThreadLocalUserContext.getLocalizedString("finance.datev.import.error.titleRowMissed"));
    }
}

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

License:Open Source License

/**
 * convert the cell-value to the type in the bean.
 * //from  w  w w. ja  va 2s  .  c  om
 * @param cell the cell containing an arbitrary value
 * @param destClazz the target class
 * @return a String, Boolean, Date or BigDecimal
 */
private Object toNativeType(final HSSFCell cell, final Class<?> destClazz) {
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        log.debug("using numeric");
        if (Date.class.isAssignableFrom(destClazz)) {
            return cell.getDateCellValue();
        }
        String strVal = String.valueOf(cell.getNumericCellValue());
        strVal = strVal.replaceAll("\\.0*$", "");
        return ConvertUtils.convert(strVal, destClazz);
    case HSSFCell.CELL_TYPE_BOOLEAN:
        log.debug("using boolean");
        return Boolean.valueOf(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_STRING:
        log.debug("using string");
        strVal = StringUtils.trimToNull(cell.getStringCellValue());
        return ConvertUtils.convert(strVal, destClazz);
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_FORMULA:
        return new Formula(cell.getCellFormula());
    default:
        return StringUtils.trimToNull(cell.getStringCellValue());
    }
}

From source file:org.projectforge.fibu.datev.BuchungssatzExcelImporter.java

License:Open Source License

/**
 * Dummerweise ist im DATEV-Export die Spalte SH zweimal vertreten. Da wir SH aber fr Haben/Soll auswerten mssen, mssen die Spalten
 * unterschiedlich heien. Die zweite Spalte wird hier in SH2 umbenannt, sofern vorhanden.
 * @param sheet/*from   w  w  w.  java 2s.c  o m*/
 */
private void rename2ndSH(final HSSFSheet sheet) {
    try {
        final HSSFRow row = sheet.getRow(ROW_COLUMNNAMES);
        if (row == null) {
            return;
        }
        short numberOfSH = 0;
        for (int col = 0; col < MAX_COLUMNS; col++) {
            final HSSFCell cell = row.getCell(col);
            if (cell == null) {
                break;
            }
            final String name = cell.getStringCellValue();
            log.debug("Processing column '" + name + "'");
            if ("SH".equals(cell.getStringCellValue()) == true) {
                numberOfSH++;
                if (numberOfSH == 2) {
                    log.debug("Renaming 2nd column 'SH' to 'SH2' (column no. " + col + ").");
                    cell.setCellValue("SH2");
                }
            }
        }
    } catch (final Exception ex) {
        log.error(ex.getMessage(), ex);
        throw new UserException(PFUserContext.getLocalizedString("finance.datev.import.error.titleRowMissed"));
    }
}

From source file:org.sakaiproject.search.component.adapter.contenthosting.XLContentDigester.java

License:Educational Community License

public void loadContent(Writer writer, ContentResource contentResource) {
    if (contentResource != null && contentResource.getContentLength() > maxDigestSize) {
        throw new RuntimeException(
                "Attempt to get too much content as a string on " + contentResource.getReference());
    }//w w  w  .  j a va2 s  . c  om
    if (contentResource == null) {
        throw new RuntimeException("Null contentResource passed the loadContent");
    }

    InputStream contentStream = null;
    try {
        contentStream = contentResource.streamContent();

        POIFSFileSystem fs = new POIFSFileSystem(contentStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0) {
                            writer.write(num + " ");
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0) {
                            writer.write(text + " ");
                        }
                        break;
                    }
                }
            }
        }

    } catch (Exception e) {
        throw new RuntimeException("Failed to read content for indexing ", e);
    } finally {
        if (contentStream != null) {
            try {
                contentStream.close();
            } catch (IOException e) {
                log.debug(e);
            }
        }
    }

}

From source file:org.sakaiproject.tool.gradebook.ui.SpreadsheetUploadBean.java

License:Educational Community License

private String fromHSSFRowtoCSV(HSSFRow row) {
    StringBuffer csvRow = new StringBuffer();
    int l = row.getLastCellNum();
    for (int i = 0; i < l; i++) {
        HSSFCell cell = row.getCell((short) i);
        String cellValue = "";
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            cellValue = "";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            cellValue = "\"" + cell.getStringCellValue() + "\"";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            double value = cell.getNumericCellValue();
            cellValue = getNumberFormat().format(value);
            cellValue = "\"" + cellValue + "\"";
        }// ww  w  .j  a  va2 s  .  c o  m

        csvRow.append(cellValue);

        if (i < l) {
            csvRow.append(getCsvDelimiter().toCharArray()[0]);
        }
    }
    return csvRow.toString();

}

From source file:org.sns.tool.data.DataGeneratorSources.java

License:Open Source License

protected void readNamesAndCounts(final HSSFSheet sheet, final List<NameAndCount> list) {
    int rowNum = 1;
    while (true) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null)
            break;

        final HSSFCell nameCell = row.getCell((short) 0);
        final String name = nameCell.getStringCellValue();
        if (name == null || name.trim().length() == 0)
            break;

        list.add(new NameAndCount(name, (int) row.getCell((short) 1).getNumericCellValue()));
        rowNum++;/*w w  w  .j  av a  2s  .c o  m*/
    }
}

From source file:org.sns.tool.data.DataGeneratorSources.java

License:Open Source License

protected void readSingleColumn(final HSSFSheet sheet, final List<String> list) {
    int rowNum = 1;
    while (true) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null)
            break;

        final HSSFCell nameCell = row.getCell((short) 0);
        final String data = nameCell.getStringCellValue();
        if (data == null || data.trim().length() == 0)
            break;

        list.add(data);/*from w  w w.  j  a  va2s . com*/
        rowNum++;
    }
}

From source file:org.sns.tool.data.DataGeneratorSources.java

License:Open Source License

protected void readCitiesAndPopulations(final HSSFSheet sheet) {
    String currentState = "UNKNOWN";
    int rowNum = 1;
    while (true) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null)
            break;

        final HSSFCell nameCell = row.getCell((short) 0);
        final String name = nameCell.getStringCellValue();
        if (name == null || name.trim().length() == 0)
            break;

        final HSSFCell zipCodeCell = row.getCell((short) 1);
        if (zipCodeCell == null || zipCodeCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            // states are on a line by themselves so save the current state and move on
            currentState = name;/* w  w w  .  j a  v  a  2  s .  co  m*/
            rowNum++;
            continue;
        }

        final int zipCode = (int) zipCodeCell.getNumericCellValue();
        final int population = (int) row.getCell((short) 2).getNumericCellValue();
        final City city = new City(name, currentState, zipCode, population);

        largestCities.add(city);
        rowNum++;
    }
}

From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java

License:Apache License

public void testExcel() throws Exception {
    AbstractExcelView excelView = new AbstractExcelView() {
        protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request,
                HttpServletResponse response) throws Exception {
            HSSFSheet sheet = wb.createSheet();
            wb.setSheetName(0, "Test Sheet");

            // test all possible permutation of row or column not existing
            HSSFCell cell = getCell(sheet, 2, 4);
            cell.setCellValue("Test Value");
            cell = getCell(sheet, 2, 3);
            setText(cell, "Test Value");
            cell = getCell(sheet, 3, 4);
            setText(cell, "Test Value");
            cell = getCell(sheet, 2, 4);
            setText(cell, "Test Value");
        }//from   www.j a v  a 2s  . c  om
    };

    excelView.render(new HashMap(), request, response);

    POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray()));
    HSSFWorkbook wb = new HSSFWorkbook(poiFs);
    assertEquals("Test Sheet", wb.getSheetName(0));
    HSSFSheet sheet = wb.getSheet("Test Sheet");
    HSSFRow row = sheet.getRow(2);
    HSSFCell cell = row.getCell((short) 4);
    assertEquals("Test Value", cell.getStringCellValue());
}