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:javaapp.ParseDebtorData.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();//  www  . j a va  2s.  co  m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3
                    || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if (rec.length() == 5 || rec.length() == 8) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.print(rpps+"|"+dval+"|"+cval);
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ParseDebtorTransactionsData.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";
    String filter = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();//w ww  . jav a 2s.co  m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2
                    || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    if (cell.getColumnIndex() == 23) {
                        filter = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if (rec.length() == 5 || rec.length() == 8) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;

            if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) {
                if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled")
                        || filter.equalsIgnoreCase("")) {
                    continue;
                }

            }

            // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){
            //System.out.println(rpps+"|"+dval+"|"+cval);
            //}
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.SimpleExcelReaderExample.java

public static ArrayList<String> parseReport(int sno, int c1, int c2, int c3, int c4, int c5, int c6, String tbl)
        throws IOException {

    String excelFilePath = "GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheetAt(sno);
    String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Number ---> " + sno + "---> Name : " + sname + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();//from w  ww.  j  a v  a2  s. co m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3
                    || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if (rec.length() == 5 || rec.length() == 8) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.print(rpps+"|"+dval+"|"+cval);
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapplication1.AttendancePayment.java

private static void checkName() throws FileNotFoundException, IOException {
    //stores the input from the formatted text field
    idNum = Integer.parseInt(jInputField.getText());

    //clears the input field
    jInputField.setText("");

    //System.out.println(idNum);

    for (Row row : sheet) {
        Cell cell1 = row.getCell(ID_COL);

        if ((int) cell1.getNumericCellValue() == idNum) {
            String paidStatus = null;
            //foundID = true;
            Cell lastName = row.getCell(NAMELAST_COL);
            Cell firstName = row.getCell(NAMEFIRST_COL);
            Cell grade = row.getCell(GRADE_COL);

            addingName = (int) grade.getNumericCellValue() + "    " + lastName.getStringCellValue() + " "
                    + firstName.getStringCellValue();

            try {
                paidStatus = row.getCell(PAIDSTATUS_COL).toString();
            } catch (Exception e) {
                e.printStackTrace();/*from   w ww  .j  av a  2  s. co m*/
            }

            //System.out.println(paidStatus);

            if ("Y".equals(paidStatus)) {
                jDisplayArea.setText(addingName + "\n \n" + "Is already paid for");
                jDisplayArea.setBackground(Color.red);
            } else {
                row.getCell(PAIDSTATUS_COL).setCellValue("Y");
                jDisplayArea.setText("Everything Worked");
                jDisplayArea.setBackground(Color.green);
            }
            try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
                workbook.write(fileOut);
            }

        }
    }
}

From source file:javaapplication1.BackTrack.java

private void btnBackTrackActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnBackTrackActionPerformed
    // TODO add your handling code here:
    //  Excel ob = null;
    /*   int len[][]=(int[][]) Excel.data.toArray();
               //from  ww w  . j  a v a 2  s.com
       for(int i=0 ;i<len.length ;i++)*/
    int i = 1, j = 1;
    float samplesec = Float.parseFloat(txtSamples.getText());
    if (samplesec == 0) {
        samplesec = 1;
    }
    float th = Float.parseFloat(txtThreshold.getText());

    try {
        DefaultTableModel defmodel = new DefaultTableModel();

        tblTH.setModel(defmodel);
        defmodel.setColumnIdentifiers(new Object[] { "Seconds", "Value" });

        String temp;
        temp = txtFilename.getText() + ".xlsx";
        FileInputStream file = new FileInputStream(new File(temp));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        // int i=1,j=1;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                dataTemp.add(cell.getNumericCellValue());
                //  System.out.print(dataTemp.get(0).toString());
                if (cell.getNumericCellValue() > th) {

                    defmodel.addRow(new Object[] { (i / samplesec), cell.getNumericCellValue() });
                }
                i++;

            }
            j++;
            data.add(dataTemp);

        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:javaapplication1.Excel.java

private void btnImportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnImportActionPerformed
    // TODO add your handling code here:
    try {//from  ww  w  . ja  va2s  .  com
        String temp;
        temp = txtfileName.getText() + ".xlsx";
        FileInputStream file = new FileInputStream(new File(temp));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 1, j = 1;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                dataTemp.add(cell.getNumericCellValue());
                //  System.out.print(dataTemp.get(0).toString());
                if (j == 1) {
                    col.add("S" + i);
                    i++;
                }
            }
            j++;
            data.add(dataTemp);

        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    printData();
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ???/* w w w. jav  a 2 s  .  c om*/
 *
 * @param c ?
 * @return
 */
private String getCellStringValue(Cell c) {
    if (c == null) {
        return "";
    }
    String value = null;
    NumberFormat nf = NumberFormat.getInstance();
    nf.setGroupingUsed(false);
    nf.setMaximumFractionDigits(12);
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(c.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
        } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
            value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                    c.getCellStyle().getDataFormatString());
        } else {
            value = nf.format(c.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = c.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        value = c.getCellFormula();
        break;
    }
    return value == null ? "" : value.trim();
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ???//from w  w w . ja  v  a2 s. c o  m
 *
 * @param c ?
 * @return
 */
private String getCellStringFormatValue(Cell c) {
    if (c == null) {
        return "";
    }
    String value = null;
    NumberFormat nf = NumberFormat.getInstance();
    nf.setGroupingUsed(false);
    nf.setMaximumFractionDigits(12);
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(c.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
        } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
            value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                    c.getCellStyle().getDataFormatString());
        } else {
            value = nf.format(c.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = c.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        return c.getCellFormula();
    }
    return value == null ? "" : value.trim();
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ???/*from   w  ww  . j  a  v  a 2 s .c o  m*/
 *
 * @param c ?
 * @return
 */
private Object getCellValue(Cell c) {
    if (c == null) {
        return null;
    }
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return c.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return c.getDateCellValue();
        }
        return c.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        return c.getStringCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return c.getCellFormula();
    }
    return null;
}

From source file:javafx.JavaFX.java

private String[] getValues(Row r) {
    int n = tree.getColumns().size();

    String[] values = new String[n];

    for (int i = 0; i < n; i++) {
        Cell cell = r.getCell(i);
        if (cell != null) {
            int type = cell.getCellType();

            if (type == Cell.CELL_TYPE_STRING) {
                values[i] = cell.getStringCellValue();
            } else if (type == Cell.CELL_TYPE_NUMERIC) {
                values[i] = String.valueOf(cell.getNumericCellValue());
            } else {
                values[i] = "";
            }/*from   ww w .  j  a v  a 2 s. c  om*/

        } else {
            values[i] = "";
        }
    }

    return values;
}