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

List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getNumericCellValue

Introduction

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

Prototype

double getNumericCellValue();

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:excel.Excel.java

/**
 * @param args the command line arguments
 * @throws java.io.IOException//from w ww .  j  ava2s .  c om
 */
public static void main(String args[]) throws IOException {
    Thread a;

    //        String nameFile = "C:\\Users\\dfcastellanosc.SOPORTECOS\\Downloads\\Files\\Informacin Etapa Productiva.xlsx";

    //        Process p = Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " + nameFile);

    FileInputStream file = new FileInputStream(
            new File("C:\\Users\\dfcastellanosc.SOPORTECOS\\Documents\\registroempleados.xlsx"));

    try (XSSFWorkbook workbook = new XSSFWorkbook(file)) {
        XSSFSheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();

        Row row;

        while (rowIterator.hasNext()) {

            row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();

            Cell celda;

            while (cellIterator.hasNext()) {

                celda = cellIterator.next();

                switch (celda.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:

                    if (DateUtil.isCellDateFormatted(celda)) {

                        if (celda.getColumnIndex() == 17) {
                            System.out.println("|" + celda.getDateCellValue() + "|");
                        } else {
                            System.out.print("|" + celda.getDateCellValue() + "|");
                        }

                    } else {

                        Double ds = celda.getNumericCellValue();
                        Long pt = ds.longValue();

                        if (celda.getColumnIndex() == 17) {
                            System.out.println("|" + pt + "|");
                        } else {
                            System.out.print("|" + pt + "|");
                        }
                    }
                    break;

                case Cell.CELL_TYPE_STRING:
                    if (celda.getColumnIndex() == 17) {
                        System.out.println("|" + celda.getStringCellValue() + "|");
                    } else {
                        System.out.print("|" + celda.getStringCellValue() + "|");
                    }

                    break;

                case Cell.CELL_TYPE_BOOLEAN:

                    if (celda.getColumnIndex() == 17) {
                        System.out.println("|" + celda.getBooleanCellValue() + "|");
                    } else {
                        System.out.print("|" + celda.getBooleanCellValue() + "|");
                    }

                    break;

                }

            }

        }
        workbook.close();
    }

}

From source file:Excel.ExcelToJTable.java

static void fillData(File file) {
    try {//from ww  w.  j  a v  a2  s .co  m
        FileInputStream fs = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(fs);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            if (row.getRowNum() == 0) {
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    columns.add(cell.getStringCellValue());
                }
            } else {
                dataTemp = new Vector();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        dataTemp.add(cell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        dataTemp.add(cell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        dataTemp.add("");
                        break;
                    default:
                        break;
                    }
                }
                data.add(dataTemp);
                fs.close();
            }
        }

    } catch (Exception ex) {
        System.out.print("Cause: \n" + ex.getCause() + "\n Message: \n" + ex.getMessage() + "\n Stack Trace: \n"
                + Arrays.toString(ex.getStackTrace()));
    }
}

From source file:excel.Reader.java

public void print() {
    System.out.println("START PRINT");
    SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
    int columnWidth = 15;
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sheet = wb.getSheetAt(0);//from ww  w.j  a v a 2  s  .co  m
    for (Row row : sheet) {
        //System.out.print("r");
        for (Cell cell : row) {
            //CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
            //System.out.print(cellRef.formatAsString());
            //System.out.print(" - ");
            // System.out.print("c");
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //System.out.print("s");
                System.out.printf("%-" + columnWidth + "s", cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //System.out.print("d");
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.printf("%-" + columnWidth + "s", df.format(cell.getDateCellValue()));
                } else {
                    if ((cell.getNumericCellValue() % 1.0) != 0.0)
                        System.out.printf("%-" + columnWidth + ".2f", cell.getNumericCellValue());
                    else
                        System.out.printf("%-" + columnWidth + ".0f", cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //System.out.print("b");
                System.out.printf("%-" + columnWidth + "s", cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                CellValue val = evaluator.evaluate(cell);
                //System.out.print("f");
                switch (val.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.printf("%-" + columnWidth + "s", val.getStringValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.printf("%-" + columnWidth + ".2f", val.getNumberValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.printf("%-" + columnWidth + "s", val.getBooleanValue());
                    break;
                default:
                    System.out.printf("%-" + columnWidth + "s", "");
                }
                break;
            default:
                System.out.print("");
            }
        }
        System.out.println();
    }
}

From source file:excel2sql.util.ExcelReader.java

public ArrayList<String> read(int pageIndex) {
    ArrayList<String> queryes = new ArrayList<String>();

    boolean first = true;
    ArrayList<String> params = new ArrayList<String>();
    ArrayList<String> values = new ArrayList<String>();

    try {//from ww w. jav a2  s  .c om
        FileInputStream file = new FileInputStream(new File(path));

        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(pageIndex);

        Iterator<Row> rowIterator = sheet.iterator();

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

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (first) {
                        params.add(String.valueOf((int) Math.floor(cell.getNumericCellValue())));
                    } else {
                        values.add(String.valueOf((int) Math.floor(cell.getNumericCellValue())));
                    }

                    break;
                case Cell.CELL_TYPE_STRING:
                    if (first) {
                        params.add(cell.getStringCellValue());
                    } else {
                        values.add(cell.getStringCellValue());
                    }
                    break;
                }
            }

            String[] stockParams = new String[params.size()];
            stockParams = params.toArray(stockParams);

            String[] stockValues = new String[values.size()];
            stockValues = values.toArray(stockValues);

            values.clear();
            if (!first) {
                queryes.add(QueryBuilder.getQuery(sheet.getSheetName(), stockParams, stockValues));
            }
            first = false;
        }
        file.close();
        return queryes;
    } catch (IOException ioe) {
        ioe.printStackTrace();
    }
    return null;
}

From source file:ExcelFx.ParseAndWrite.Parser.java

/**
 * ? ? ? ?? ??/*w w w.jav  a 2s .com*/
 *
 * @param patchName   xls/xlsx 
 * @param pageNumber ? ? ?   
 * @return  ? Excel 
 * @throws NullPointerException
 * @throws FileNotFoundException
 * @throws IOException
 */
public RowList parseInitalData(String patchName, Integer pageNumber) throws NullPointerException, IOException {

    System.out.println("parse inital data in " + patchName + " at page " + pageNumber);

    File file = new File(patchName);

    if (!file.exists()) {
        System.out.println("Error file on patch not exists");
    }
    FileInputStream in = new FileInputStream(file);

    switch (patchName.substring(patchName.lastIndexOf("."), patchName.length())) {
    case ".xls": {
        this.wb = new HSSFWorkbook(in);
        break;
    }
    case ".xlsx": {
        this.wb = new XSSFWorkbook(in);
        break;
    }
    default: {
        System.out.println("error wrong file format");
    }

    }

    Sheet sheet = this.wb.getSheetAt(pageNumber);

    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        int cellType;
        this.list.add(new ExcelRow());
        Row row = sheet.getRow(i);
        int jMax = sheet.getRow(0).getPhysicalNumberOfCells();
        try {
            for (int j = 0; j < jMax; j++) {

                Cell cell = row.getCell(j);

                try {
                    cellType = cell.getCellType();
                } catch (NullPointerException e) {
                    this.list.get(i).add("null");

                    continue;
                }

                switch (cellType) {
                case Cell.CELL_TYPE_STRING:
                    this.list.get(i).add(cell.getStringCellValue().trim().toLowerCase());
                    //System.out.println(cell.getStringCellValue());

                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    this.list.get(i).add(String.valueOf(cell.getNumericCellValue()).trim().toLowerCase());
                    //System.out.println(cell.getNumericCellValue());
                    break;
                default:

                    this.list.get(i).add("null");
                    //System.out.println("null");
                    break;
                }
            }
        } catch (NullPointerException e) {
            System.err.println(e + " in " + i + " row");

        }

    }
    System.out.println("parse complited just read " + this.list.size() + " row ");
    System.out.println("-----------------------------------------------------------");
    return this.list;

}

From source file:ExcelInputSimulation.ExcelReader.java

public void readExcel() {
    try {//from w  ww.  j av  a2s  . c  o  m
        this.workbook = new XSSFWorkbook(this.file);
    } catch (IOException ex) {

    }
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    StatisticsGenerator gen = new StatisticsGenerator();
    int counterrow = 0;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        if (counterrow > 0) {
            int i = 0;
            while (cellIterator.hasNext()) {
                Customer temp = new Customer();
                Cell cell = cellIterator.next();
                if (i == 0) {
                    temp.setNumber((int) cell.getNumericCellValue());
                    System.out.print(cell.getNumericCellValue() + "\t");
                } else if (i == 1) {
                    temp.setJenis(cell.getStringCellValue());
                } else if (i == 2) {
                    String temprealtime = cell.getStringCellValue();
                    temp.setArrivaltime(gen.convertToRealTime(temprealtime));
                    System.out.print(temprealtime + "\t");
                } else if (i == 3) {
                    String temprealtime = cell.getStringCellValue();
                    temp.setArrivaltime(gen.convertToRealTime(temprealtime));
                    System.out.print(temprealtime + "\t");
                } else if (i == 4) {
                    String tempboolean = cell.getStringCellValue();
                    if (tempboolean.equals("Y")) {
                        temp.setToPoliklinik(true);
                    } else {
                        temp.setToPoliklinik(false);
                    }
                    System.out.print(tempboolean + "\t");
                }
                i++;

            }
        }
        System.out.println("");
        counterrow++;
    }

    try {
        file.close();
    } catch (IOException ex) {

    }

}

From source file:ExcelRead.CrbRead.java

public void readFromExcel(String file, JTable table) throws IOException {

    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));

    XSSFSheet sheet = wb.getSheetAt(0);/*w  w w .  j  a  v  a2  s  . c  om*/
    Iterator<Row> it = sheet.iterator();
    while (it.hasNext()) {
        Row row = it.next();
        Iterator<Cell> cells = row.iterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            int cellIndex = cell.getColumnIndex();

            switch (cellIndex) {
            case 0:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    old_reg_cod = String.valueOf((int) cell.getNumericCellValue());
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    old_reg_cod = cell.getStringCellValue();
                    break;
                }
            case 1:
                name = cell.getStringCellValue();
                break;
            case 2:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    new_reg_cod = String.valueOf((int) cell.getNumericCellValue());
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    new_reg_cod = cell.getStringCellValue();
                    break;
                }

            default:
                System.out.print("|");
                break;
            }
        }
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        String[] data = { old_reg_cod, name, new_reg_cod };
        model.addRow(data);

        removeAllFields();
    }

}

From source file:ExcelRead.PatientRead.java

public void readFromExcel(String file, JTable table) throws IOException {

    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));

    XSSFSheet sheet = wb.getSheetAt(0);/*from   w  ww  .  j a  v a2s  .  c  om*/
    Iterator<Row> it = sheet.iterator();
    while (it.hasNext()) {
        Row row = it.next();
        Iterator<Cell> cells = row.iterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            int cellIndex = cell.getColumnIndex();

            switch (cellIndex) {
            case 0:
                ID = (int) cell.getNumericCellValue();
                //  table.setValueAt(ID, i, 0);
                break;
            case 1:
                String[] fullName = cell.getStringCellValue().split(" ");

                name = fullName[1];
                surname = fullName[0];
                middleName = fullName[2];
                // table.setValueAt(cell.getStringCellValue(), i, 1);
                break;

            case 2:
                SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
                birthdate = sdf.format(cell.getDateCellValue());
                //table.setValueAt(birthdate, i, 2);

                break;
            case 3:
                sex = cell.getStringCellValue();
                //table.setValueAt(sex, i, 3);
                break;
            case 4:
                address = cell.getStringCellValue();
                // table.setValueAt(address, i, 4);
                break;
            case 5:

                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    lpu_id = String.valueOf((int) cell.getNumericCellValue());
                    // table.setValueAt(lpu_id, i, 5);
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    lpu_id = cell.getStringCellValue();
                    //table.setValueAt(lpu_id, i, 5);
                    break;
                }

            case 6:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    crb_id = String.valueOf((int) cell.getNumericCellValue());
                    // table.setValueAt(crb_id, i, 6);
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    crb_id = cell.getStringCellValue();
                    // table.setValueAt(crb_id, i, 6);
                    break;
                }
            case 7:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    snils = String.valueOf((int) cell.getNumericCellValue());
                    //  table.setValueAt(snils, i, 7);
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    snils = cell.getStringCellValue();
                    //table.setValueAt(snils, i, 7);
                    break;
                }
            case 8:
                String[] passport = cell.getStringCellValue().split(" ");
                //table.setValueAt(cell.getStringCellValue(), i, 8);
                pass_ser = passport[0];
                pass_num = passport[1];
                break;
            case 9:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    old_police = String.valueOf((int) cell.getNumericCellValue());
                    // table.setValueAt(old_police, i, 9);
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    old_police = cell.getStringCellValue();
                    // table.setValueAt(old_police, i, 9);
                    break;
                }
            case 10:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    new_police = String.valueOf((int) cell.getNumericCellValue());
                    //table.setValueAt(new_police, i, 10);
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    new_police = cell.getStringCellValue();
                    //table.setValueAt(new_police, i, 10);
                    break;
                }
            case 11:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    phoneNum = String.valueOf((int) cell.getNumericCellValue());
                    //table.setValueAt(phoneNum, i, 11);
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    phoneNum = cell.getStringCellValue();
                    //table.setValueAt(phoneNum, i, 11);
                    break;
                }
            default:
                System.out.print("|");
                break;
            }
        }
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        String[] data = { String.valueOf(ID), surname + " " + name + " " + middleName, birthdate, sex, address,
                lpu_id, crb_id, snils, pass_ser + " " + pass_num, old_police, new_police, phoneNum };
        model.addRow(data);

        removeAllFields();
    }

}

From source file:ExcelReadFile.ExcellReadSpecialite.java

private Object getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

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

    case (int) Cell.CELL_TYPE_NUMERIC:
        return (int) Math.round(cell.getNumericCellValue());
    }//from w  ww.ja  v a2 s .  c o  m

    return null;
}

From source file:ExcelReadFile.ExcelReadMedecin.java

private Object getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

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

    case Cell.CELL_TYPE_NUMERIC:
        return (int) Math.round(cell.getNumericCellValue());
    }//w ww  . j  ava  2  s.c  om

    return null;
}