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

Introduction

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

Prototype

boolean getBooleanCellValue();

Source Link

Document

Get the value of the cell as a boolean.

Usage

From source file:com.beyondb.io.ExcelControl.java

@Override
public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception {
    try {//  w  w w.j  a  v a  2  s . c  o m
        //OPCPackage pkg = OPCPackage.open(file);
        //            InputStream m_InputStream = new FileInputStream(m_File);
        Sheet sheet = null;
        //            if (!m_InputStream.markSupported()) {
        //                m_InputStream = new PushbackInputStream(m_InputStream, 8);
        //            } 
        //            if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
        //                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream);
        //                 sheet  = (Sheet)hSSFWorkbook.getSheetAt(0);
        //            
        //             } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
        //                XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File));
        //               sheet  = (Sheet)xSSFWorkbook.getSheetAt(0);
        //             }
        //             else {
        //                throw new IllegalArgumentException("excel?poi??");
        //            }
        sheet = getSheet();
        if (sheet != null) {
            if (sheet.getLastRowNum() == 0) {
                throw new Exception("Excel");
            }
            //?
            m_RowNum = sheet.getLastRowNum() + 1;

            //                m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
            m_ColumnNum = sheet.getRow(0).getLastCellNum();
            m_TableStr = new Object[m_RowNum][m_ColumnNum];

            for (int rindex = 0; rindex < m_RowNum; rindex++) {
                Row row = sheet.getRow(rindex);
                for (int cindex = 0; cindex < m_ColumnNum; cindex++) {
                    Cell cell = row.getCell(cindex);

                    if (cell == null) {
                        m_TableStr[rindex][cindex] = "";
                    } else {
                        String value = "";
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //                          System.out.println(cell.getRichStringCellValue().getString());                          
                            value = cell.getRichStringCellValue().getString().replace("\n", "");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                //                                System.out.println(cell.getDateCellValue());
                                value = cell.getDateCellValue().toString();
                            } else {

                                DecimalFormat df = new DecimalFormat("#");
                                value = String.valueOf(cell.getNumericCellValue());
                                double d = cell.getNumericCellValue();
                                int dInt = (int) d;
                                BigDecimal b1 = new BigDecimal(value);
                                BigDecimal b2 = new BigDecimal(Integer.toString(dInt));
                                double dPoint = b1.subtract(b2).doubleValue();
                                if (dPoint == 0) {
                                    //?
                                    value = df.format(cell.getNumericCellValue());
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            //                            System.out.println(cell.getBooleanCellValue());
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            //                            System.out.println(cell.getCellFormula());
                            value = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                        default:
                            //                            System.out.println();
                            value = "";
                        }
                        m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value;
                    }
                }
            }
        }

    } catch (IOException | InvalidFormatException e) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e);
        throw e;

    } catch (Exception ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex);

        throw ex;
    } finally {
        m_InputStream.close();
    }

    return m_TableStr;
}

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

License:Apache License

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

    checkCondition();/*from   w  ww  .  ja va 2 s.  co  m*/

    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   www.  j  a va2  s .  co m
    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.workbook.CsvWriter.java

License:Apache License

@Override
public void write(Workbook wb) throws IOException {

    int numSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numSheets; i++) {

        File curOutputFile = getCurrentOutputFile(filePath, numSheets, i);

        CSVWriter pw = new CSVWriter(new OutputStreamWriter(new FileOutputStream(curOutputFile)),
                CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, "\r\n");

        try {/*from w  w  w .ja  va 2 s.c o  m*/
            Sheet sheet = wb.getSheetAt(i);
            for (Row row : sheet) {
                List<String> cells = new ArrayList<String>();
                String cellValue = "";
                for (Cell cell : row) {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean cellValueBoolean = cell.getBooleanCellValue();
                        cellValue = cellValueBoolean ? "true" : "false";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "<error: " + cell.getErrorCellValue() + ">";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellValue = cell.getCellFormula();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double cellValueDouble = cell.getNumericCellValue();
                        cellValue = Double.toString(cellValueDouble);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    default:
                        break;
                    }

                    cells.add(cellValue);
                }
                String[] typeExample = new String[cells.size()];
                String[] cellArray = cells.toArray(typeExample);
                pw.writeNext(cellArray);
            }
        } finally {
            pw.close();
        }
    }
}

From source file:com.canoo.webtest.plugins.exceltest.ExcelCellUtils.java

License:Open Source License

public static String getCellValueAt(final Cell cell) {
    if (null == cell) {
        return "";
    }//  w w w . j av  a  2 s .  c  o m
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        return asStringTrimInts(cell.getNumericCellValue());
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case Cell.CELL_TYPE_ERROR:
        return "Error Code " + String.valueOf(cell.getErrorCellValue() & 0xFF);
    ///CLOVER:OFF there are currently no other types.  Potentially more in future?
    default:
        LOG.warn("Cell Type not supported: " + cell.getCellType());
        return "";
    ///CLOVER:ON
    }
}

From source file:com.celtris.exparse.parser.ExcelReader.java

License:Apache License

public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction)
        throws IOException, InstantiationException, IllegalAccessException {

    FileInputStream file = new FileInputStream(new File(absolutePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);

    Iterator<Sheet> sheetIterator = workbook.iterator();
    List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets());
    int sheetCount = 0;
    while (sheetIterator.hasNext()) {
        sheetCount++;// w  w w  .jav  a  2s. co  m

        ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass);
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.iterator();

        int rowCount = 0;

        // Evaluating header
        if (headerExtraction) {
            if (rowIterator.hasNext()) {

                rowCount++;

                Field[] fields = excelModelClass.getFields();
                List<String> heaaderStr = new ArrayList<String>(fields.length);

                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String cellStrValue = cell.getStringCellValue();

                    heaaderStr.add(cellStrValue);
                }
                excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName());
            }
        }

        while (rowIterator.hasNext()) {
            rowCount++;
            Row row = rowIterator.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            List<String> rowStr = new ArrayList<String>(excelParser.parameterCount());
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellStrValue = "";
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        objFormulaEvaluator.evaluate(cell);
                        cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    } else {

                        cellStrValue = Double.toString(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellStrValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case BLANK:

                default:
                    break;
                }
                rowStr.add(cellStrValue);
            }

            excelParser.processRow(rowStr, rowCount, sheet.getSheetName());
        }

        SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(),
                sheetCount);
        sheetDataList.add(sheetData);
    }

    file.close();
    workbook.close();
    return sheetDataList;
}

From source file:com.citrix.g2w.webdriver.util.ReadExcelReport.java

License:Open Source License

/**
 * Method to get the cell value and convert to String.
 * //  w w w .  j  a  va 2s  .  co m
 * @param cell
 * @return String
 */
private String getValue(Cell cell) {

    switch (cell.getCellType()) {

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() + "";

    case Cell.CELL_TYPE_NUMERIC:
        return ((int) cell.getNumericCellValue()) + "";

    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

    case Cell.CELL_TYPE_BLANK:
        return "";

    default:
        return "";
    }
}

From source file:com.cloudera.sa.ExcelRecordReader.java

License:Apache License

private Text getCellValue(Cell cell) {
    Text out = new Text();
    CellType cellType = cell.getCellTypeEnum();

    if (cellType == CellType.STRING) {
        out.set(cell.getStringCellValue());
    } else if (cellType == CellType.NUMERIC) {
        out.set(String.valueOf(cell.getNumericCellValue()));
    } else if (cellType == CellType.FORMULA) {
        out.set(cell.getCellFormula());//from   w w  w.  j  a v a2  s.  co m
    } else if (cellType == CellType.ERROR) {
        out.set(String.valueOf(cell.getErrorCellValue()));
    } else if (cellType == CellType.BOOLEAN) {
        out.set(String.valueOf(cell.getBooleanCellValue()));
    } else {
        out.set("");
    }

    return out;
}

From source file:com.cmcti.cmts.domain.service.impl.CustomerMacMappingLocalServiceImpl.java

License:Open Source License

private String getStringCellValue(Cell cell) {
    String value = null;//from  ww w . j  av a  2  s  .co  m
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        value = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        break;
    case Cell.CELL_TYPE_FORMULA:
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        double doubleValue = cell.getNumericCellValue();
        if (doubleValue == (int) doubleValue) {
            value = String.format("%d", (int) doubleValue);
        } else {
            value = String.format("%s", doubleValue);
        }

        break;
    }

    return value;
}

From source file:com.cn.util.Units.java

/**
 * ?Excel?//from  w w  w.  jav a 2s . c o m
 *
 * @param cell
 * @return
 */
public static String getCellValue(Cell cell) {
    String result = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = String.valueOf(cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    default:
        result = cell.getStringCellValue();
        break;
    }
    return result.trim();
}