Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

Introduction

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

Prototype

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:com.b2international.snowowl.datastore.server.importer.ExcelUtilities.java

License:Apache License

private static String extractAsString(final Cell cell, final boolean formatNumber) {
    String value = "";

    if (cell == null) {
        return value;
    }//www.java2 s  .co  m

    FormulaEvaluator formulaEvaluator = cell.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();

    int type = cell.getCellType();

    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        if (formatNumber) {
            value = convertToString(cell.getNumericCellValue());
        } else {
            value = convertToStringWithoutFormat(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        CellValue cellValue = formulaEvaluator.evaluate(cell);
        value = cellValue.getStringValue(); //type should be checked
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        //do nothing, sctId is null
        break;
    default:
        LOGGER.log(Level.SEVERE, "Unsupported cell type:" + type + " for cell: " + cell);
        break;
    }
    return null == value ? "" : value;
}

---

From source file:com.beans.AddressBookMBean.java

private ArrayList<Addressbook> readExcel(UploadedFile file) {

    ArrayList<Addressbook> adbooks = new ArrayList<Addressbook>();
    InputStreamReader reader = null;
    try {// ww  w  . j a  v  a  2 s .c  o m

        File myFile = new File(file.getFileName());
        FileUtils.copyInputStreamToFile(file.getInputstream(), myFile);
        FileInputStream fis = new FileInputStream(myFile);
        // Finds the workbook instance for XLSX file
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        // Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        // Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        while (rowIterator.hasNext()) {
            Addressbook add = new Addressbook();
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            int i = 0;
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();
                cell.setCellType(Cell.CELL_TYPE_STRING);
                System.out.print("Cell:" + i + ":" + cell.getStringCellValue() + "\t");
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (i == 0) {
                        add.setPhonenumber(cell.getStringCellValue());
                    } else {
                        add.setName(cell.getStringCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (i == 0) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        add.setPhonenumber(cell.getStringCellValue());
                    }
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                default:
                }
                i++;
            }
            if (add.getPhonenumber() != null) {
                adbooks.add(add);
            }
        }
        System.out.println("");
    } catch (IOException ex) {
        Logger.getLogger(AddressBookMBean.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
    }
    return adbooks;
}

---

From source file:com.benasmussen.maven.plugin.i18n.io.ResourceReader.java

License:Apache License

/**
 * Get all resource definitions and column index
 * //w  ww.  j  ava 2s .c o  m
 * <pre>
 * de,
 * de_DE
 * en,
 * en_GB,
 * en_US
 * </pre>
 * 
 * @return
 */
public Map<String, Integer> getLocaleDefinitions(String sheetName) {
    int maxCols = 300;
    Map<String, Integer> localeDefinitions = new HashMap<String, Integer>();

    CellRangeAddress cellRange = CellRangeAddress.valueOf(localeCell);

    Sheet sheet = getSheetByName(sheetName);
    Row row = sheet.getRow(cellRange.getFirstRow());

    int colIndex = cellRange.getFirstColumn();
    for (int idx = colIndex; idx < (maxCols + colIndex); idx++) {
        Cell cell = row.getCell(idx);
        if (cell != null) {
            localeDefinitions.put(cell.getStringCellValue(), idx);
        } else {
            break;
        }
    }

    return localeDefinitions;
}

---

From source file:com.benasmussen.maven.plugin.i18n.io.ResourceReader.java

License:Apache License

/**
 * Get cell value as string/*from  w  ww . j  a v  a 2s  .  c om*/
 * 
 * @param sheetName
 * @param row
 * @param col
 * @return
 */
public String getCellValue(String sheetName, int row, int col) {
    Sheet sheet = getSheetByName(sheetName);
    Row r = sheet.getRow(row);
    if (r != null) {
        Cell c = r.getCell(col);
        if (c != null) {
            return c.getStringCellValue();
        }
    }

    return null;
}

---

From source file:com.binlist.binlistreader.BinlistReader.java

public static void main(String[] args) {
    String result[] = null;//from  w ww  . j  a  v a  2  s  .  c o m

    String folder = "/opt/";
    String sourceFileName = "binlist.xls";
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(folder + sourceFileName);

        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Iterator<Cell> cellIterator = null;
        Row row = null;
        Cell cell = null;
        int cellNo = 0;
        String binno = "";
        String longUrl = "";
        String shortUrl = "";
        int rownum = 0;
        while (rowIterator.hasNext()) {
            rownum++;
            if (rownum == 1)
                continue;

            row = rowIterator.next();
            cellIterator = row.cellIterator();
            cellNo = 0;
            binno = "";
            longUrl = "";
            shortUrl = "";

            cell = row.getCell(4);
            if (cell != null && cell.getStringCellValue() != null) {
                binno = cell.getStringCellValue();
            }

            if (binno != null && binno.length() > 5) {
                result = postRequestV3(binno);
                System.out.println("rownum..:" + rownum + " binno..:" + binno + " result..:" + result.length);
                if (result != null) {
                    row.getCell(5).setCellValue(checkNull(result[0], ""));
                    row.getCell(6).setCellValue(checkNull(result[1], ""));
                    row.getCell(7).setCellValue(checkNull(result[2], ""));
                }
            }

        }

        System.out.println("rownum..:" + rownum);
        fis.close();
        FileOutputStream out = new FileOutputStream(folder + "newfile/" + sourceFileName);
        workbook.write(out);
        out.close();

        workbook.close();
        workbook = null;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (fis != null)
            try {
                fis.close();
            } catch (IOException ex) {
                Logger.getLogger(BinlistReader.class.getName()).log(Level.SEVERE, null, ex);
            }
        fis = null;
    }

}

---

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();//from   w ww . j a  v a 2  s  .c  om

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

---

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();//from   w w w  .ja va2  s .c  om
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}

---

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.reader.DataTableReaderExcel.java

License:Apache License

private void readCell(Record rec, FieldDef fieldDef, Cell cell) throws Exception {
    if (cell == null) {
        return;/*from   w  w w.j  av  a 2s .  c o m*/
    }
    switch (fieldDef.getType()) {
    case STRING:
        String cellStringValue = "";
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cellStringValue = String.valueOf(cell.getNumericCellValue());
        } else {
            cellStringValue = cell.getStringCellValue();
        }
        log.debug("String cell; value: " + cellStringValue);
        rec.setFieldValue(fieldDef.getName(), cellStringValue);
        break;
    case DATE:
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_STRING:
            break;
        case Cell.CELL_TYPE_NUMERIC:
            Date cellDateValue = cell.getDateCellValue();
            GregorianCalendar cal = new GregorianCalendar();
            cal.setTime(cellDateValue);
            rec.setFieldValue(fieldDef.getName(), cal);
            break;
        }
        break;
    case HYPERLINK:
        throw new Exception("DataTableReaderExcel does not yet support HYPERLINK field type");
    default:
    }
}

---

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java

License:Apache License

/**
 * Called by the TemplateReader.// w  ww . ja v  a 2  s  .  c o  m
 *
 * @param sheet
 *            the sheet
 * @param templateSheet
 *            the template sheet
 * @throws Exception
 *             the exception
 */
private void populateColumns(Sheet sheet, TemplateSheet templateSheet) throws Exception {
    Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap();
    Row headerRow = sheet.getRow(0);
    if (headerRow == null) {
        throw new Exception("No header row found! Please create one.");
    }

    Row styleRow = sheet.getRow(1);
    if (styleRow == null) {
        throw new Exception(
                "Sheet name " + templateSheet.getSheetName() + ": No style row found! Please create one.");
    }

    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        TemplateColumn column = new TemplateColumn();
        Cell headerCell = headerRow.getCell(i);
        Cell styleCell = styleRow.getCell(i);

        if (headerCell == null) {
            throw new Exception("The following column appears to be empty: " + i);
        }

        if (styleCell == null) {
            throw new Exception("The following style position is not defined: " + i);
        }

        String columnName = headerCell.getStringCellValue();
        // We want to use the style cell (row below) as the header will
        // always be text.
        Integer cellType = styleCell.getCellType();

        column.setColumnPos(i);
        column.setCellStyle(styleCell.getCellStyle());
        column.setColumnName(columnName);
        column.setCellType(cellType);
        if (cellType == Cell.CELL_TYPE_FORMULA) {
            column.setCellFormula(styleCell.getCellFormula());
        }

        columnMap.put(columnName, column);
    }

    populateColumnMappings(columnMap);
}

---

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java

License:Apache License

/**
 * Populate columns.//from w w w .  ja v a 2  s .  co  m
 *
 * @param sheet
 *            the sheet
 * @param templateSheet
 *            the template sheet
 * @param columnNames
 *            the column names
 * @throws Exception
 *             the exception
 */
private void populateColumns(Sheet sheet, TemplateSheet templateSheet, List<String> columnNames)
        throws Exception {
    Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap();
    Row headerRow = sheet.getRow(0);

    if (headerRow == null) {
        throw new Exception("No header row found!");
    }

    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        Cell headerCell = headerRow.getCell(i);
        if (headerCell == null) {
            throw new Exception("The following header column is empty: " + i);
        }

        String headerName = headerCell.getStringCellValue().trim();
        if (columnNames.contains(headerName)) {
            log.info("Processing user specified column: " + headerName);
            TemplateColumn column = new TemplateColumn();

            column.setColumnPos(i);
            column.setColumnName(headerName);

            columnMap.put(headerName, column);
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »