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:com.mto.excel.model.MergeWorkbook.java

License:Open Source License

private void addRow(Row row, int cellOffset) {
    Row newRow = sheet.createRow(rowOffset);
    rowOffset++;/*from   w ww  .  j a va2 s.c om*/
    for (int i = cellOffset; i < row.getLastCellNum(); i++) {
        Cell c = row.getCell(i);
        if (c == null) {
            continue;
        }

        Cell newCell = newRow.createCell(i, c.getCellType());

        switch (c.getCellType()) {
        case CELL_TYPE_STRING:
            newCell.setCellValue(helper.createRichTextString(c.getStringCellValue()));
            break;
        case CELL_TYPE_BOOLEAN:
            newCell.setCellValue(c.getBooleanCellValue());
            break;
        case CELL_TYPE_NUMERIC:
            newCell.setCellValue(c.getNumericCellValue());
            break;
        default:
            return;
        }
    }
}

From source file:com.mum.processexceldata.ReadExcel.java

public static void main(String args[]) {
    try {/*from w  ww.java2s.c o m*/
        FileInputStream file = new FileInputStream(
                new File("C:\\Users\\demodem\\Downloads\\Calling_Codes.xls"));
        System.out.println("File:" + file);
        // HSSFWorkbook book = new HSSFWorkbook(file);

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
        //System.out.println("sheet    :"+sheet);
        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

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

                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
                }
            }
            System.out.println("");
        }
        file.close();
        FileOutputStream out = new FileOutputStream(new File("C:\\Users\\demodem\\Downloads\\test.xls"));
        workbook.write(out);
        out.close();

    } catch (Exception e) {
        System.out.println("Error in reading the file.");
    }
}

From source file:com.murilo.excel.ExcelHandler.java

private String stringrizeCell(Cell x) {

    switch (x.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(x.getBooleanCellValue());
    case Cell.CELL_TYPE_NUMERIC:
        return String.valueOf(x.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return x.getStringCellValue();
    case Cell.CELL_TYPE_FORMULA:
        switch (x.getCachedFormulaResultType()) {
        case Cell.CELL_TYPE_NUMERIC:
            return String.valueOf(x.getNumericCellValue());
        case Cell.CELL_TYPE_STRING:
            return x.getStringCellValue();
        case Cell.CELL_TYPE_BLANK:
            return "";
        }/*from  www .j a v  a  2s  . com*/
    }

    return null;
}

From source file:com.mycompany.asyncreq.Main.java

@SuppressWarnings("empty-statement")
public static ArrayList<String> GenData() {

    ArrayList<String> rootobject = new ArrayList<String>();

    try {/*ww w.  java  2 s. com*/

        FileInputStream file = new FileInputStream(new File("src/main/resources/Eu3.xlsx"));
        FileOutputStream ffout = new FileOutputStream(new File("trade.json"));
        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

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

        //Iterate through each rows from first sheet
        Iterator rowIterator = sheet.iterator();
        Root r;
        String addr;
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();

            //For each row, iterate through each columns
            Iterator cellIterator = row.cellIterator();

            Cell cell = (Cell) row.getCell(0);

            switch (cell.getCellType()) {

            case Cell.CELL_TYPE_NUMERIC:
                addr = "http://comtrade.un.org/api/get?max=50000&type=C&freq=M&px=HS&ps=2014&r=804&p="
                        + (int) cell.getNumericCellValue() + "&rg=All&cc=All&fmt=json";

                rootobject.add(addr);
                break;

            }

        }

    } catch (IOException e) {
        e.printStackTrace();
    }

    return rootobject;
}

From source file:com.mycompany.bandaru_exam.ReadfromExcel.java

public List getAccountListFromExcel() {
    List accountList = new ArrayList();
    FileInputStream fis = null;// www. j  a  v  a  2  s  .co  m

    try {
        fis = new FileInputStream(FILE_PATH);

        /*
          Use XSSF for xlsx format, for xls use HSSF
        */
        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        /*
        looping over each workbook sheet
        */
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            /*
                iterating over each row
                */
            while (rowIterator.hasNext()) {

                Account account = new Account();
                Row row = (Row) rowIterator.next();

                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();

                    /*
                    checking if the cell is having a String value .
                    */
                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

                        /*
                        Cell with index 0 contains first name 
                        */
                        if (cell.getColumnIndex() == 0) {

                            account.setFirstName(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 1 contains last name
                        */
                        if (cell.getColumnIndex() == 1) {
                            account.setLastName(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 2 contains account number
                        */
                        if (cell.getColumnIndex() == 2) {
                            account.setAccNumber(cell.getStringCellValue());
                        }

                    }

                    /*
                     checking if the cell is having a numeric value
                    */
                    else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        /*
                        Cell with index 3 contains account number
                        */
                        if (cell.getColumnIndex() == 3) {
                            account.setBalance(cell.getNumericCellValue());
                        }

                    }

                }

                /*
                end iterating a row, add all the elements of a row in list
                */
                accountList.add(account);
            }
        }

        fis.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    //Remove Headers
    accountList.remove(0);
    return accountList;
}

From source file:com.mycompany.chartproject.ExcelReader.java

public Map<String, Double> getPieChartData(String repo) {
    Map<String, Double> map = new HashMap<>();
    try {//w  w w  .  java 2 s.c om
        String fileName = "src/main/resources/Stabilityfinal.xlsx";
        String test = fileName;
        //String fileName2 = "src/main/resources/Series.xlsx";
        //String test2 = fileName2;
        FileInputStream file = new FileInputStream(new File(test));

        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheet(repo);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int total = -1;
        int success = 0;
        int failure = 0;
        int unstable = 0;
        int aborted = 0;

        while (rowIterator.hasNext()) {
            ++total;
            Row row = rowIterator.next();

            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:

                    if (DateUtil.isCellDateFormatted(cell)) {

                        System.out.println(cell.getDateCellValue() + "\t\t");

                    } else {
                        System.out.print(cell.getNumericCellValue() + "\t\t");

                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;

                case Cell.CELL_TYPE_STRING:
                    if (cell.getStringCellValue().equalsIgnoreCase("SUCCESS")) {
                        ++success;
                    } else if (cell.getStringCellValue().equalsIgnoreCase("FAILURE")) {
                        ++failure;
                    } else if (cell.getStringCellValue().equalsIgnoreCase("UNSTABLE")) {
                        ++unstable;
                    } else if (cell.getStringCellValue().equalsIgnoreCase("ABORTED")) {
                        ++aborted;
                    }
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;

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

            file.close();
            FileOutputStream out = new FileOutputStream(new File(fileName));
            workbook.write(out);
            out.close();
        }
        System.out.println("Total " + total);
        System.out.println("no. Successful " + success);
        System.out.println("no. Failures " + failure);
        System.out.println("no. Unstable " + unstable);

        int green = ((success * 100 / total));
        double passedPercentage = (double) green / 100;
        System.out.println("Passed: " + passedPercentage);

        int red = ((failure * 100 / total));
        double failedPercentage = (double) red / 100;
        System.out.println("Failed: " + failedPercentage);

        int orange = ((unstable * 100 / total));
        double unstablePercentage = (double) orange / 100;
        System.out.println("Unstable: " + unstablePercentage);

        int abort = ((aborted * 100 / total));
        double abortedPercentage = (double) abort / 100;
        System.out.println("Aborted: " + abortedPercentage);

        map.put("Failed", failedPercentage);

        map.put("Unstable", unstablePercentage);

        map.put("Passed", passedPercentage);

        map.put("Aborted", abortedPercentage);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return map;

}

From source file:com.mycompany.chartproject.ExcelReader.java

public List<ChartSeries> getSeriesChartData(String repo) {
    List<ChartSeries> cs = new ArrayList<>();
    try {// w  ww  . jav a 2 s .  c  om
        String fileName = "src/main/resources/Series.xlsx";
        String test = fileName;
        //String fileName2 = "src/main/resources/Series.xlsx";
        //String test2 = fileName2;
        FileInputStream file = new FileInputStream(new File(test));

        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheet(repo);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        ChartSeries chartSeries = null;
        while (rowIterator.hasNext()) {
            chartSeries = new ChartSeries();

            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                row = rowIterator.next();
            }

            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.println("numeric");
                    switch (cell.getColumnIndex()) {
                    case 1:
                        chartSeries.setTotal((int) cell.getNumericCellValue());
                        break;
                    case 2:
                        chartSeries.setPassed((int) cell.getNumericCellValue());
                        break;
                    case 3:
                        chartSeries.setFailed((int) cell.getNumericCellValue());
                        break;
                    case 4:
                        chartSeries.setSkipped((int) cell.getNumericCellValue());
                        break;
                    }

                    System.out.println(cell.getDateCellValue() + "\t\t");
                    System.out.print(cell.getNumericCellValue() + "\t\t");

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;

                case Cell.CELL_TYPE_STRING:

                    chartSeries.setDate(cell.getStringCellValue());
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;

                }
            }
            System.out.println("");
            cs.add(chartSeries);

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return cs;

}

From source file:com.mycompany.gannaraputakehomeexam.ReadingFromExcel.java

public List getSongsListFromExcel() {
    List songList = new ArrayList();
    FileInputStream fis = null;/*from w  w w  .j  a  v a 2s  . c  o m*/

    try {
        fis = new FileInputStream(FILE_PATH);

        /*
          Use XSSF for xlsx format, for xls use HSSF
        */
        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        /*
        looping over each workbook sheet
        */
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            /*
                iterating over each row
                */
            while (rowIterator.hasNext()) {

                SongsList song = new SongsList();
                Row row = (Row) rowIterator.next();

                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();

                    /*
                    checking if the cell is having a String value .
                    */
                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

                        /*
                        Cell with index 1 contains Album name 
                        */
                        if (cell.getColumnIndex() == 1) {
                            song.setAlbumname(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 2 contains Genre
                        */
                        if (cell.getColumnIndex() == 2) {
                            song.setGenre(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 3 contains Artist name
                        */
                        if (cell.getColumnIndex() == 3) {
                            song.setArtist(cell.getStringCellValue());
                        }

                    }

                    /*
                     checking if the cell is having a numeric value
                    */
                    else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        /*
                        Cell with index 0 contains Sno
                        */
                        if (cell.getColumnIndex() == 0) {
                            song.setSno((int) cell.getNumericCellValue());
                        }

                        /*
                        Cell with index 5 contains Critic score.
                        */
                        else if (cell.getColumnIndex() == 5) {
                            song.setCriticscore((int) cell.getNumericCellValue());
                        }

                        /*
                        Cell with index 4 contains Release date
                        */
                        else if (cell.getColumnIndex() == 4) {
                            Date dateValue = null;

                            if (DateUtil.isCellDateFormatted(cell)) {
                                dateValue = cell.getDateCellValue();
                            }
                            song.setReleasedate(dateValue);
                        }

                    }

                }

                /*
                end iterating a row, add all the elements of a row in list
                */
                songList.add(song);
            }
        }

        fis.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return songList;
}

From source file:com.mycompany.gayamtakehomeexam.ReadfromExcel.java

public List getSongsListFromExcel() {
    List songList = new ArrayList();
    FileInputStream fis = null;/*from  w w  w  . j  a v  a  2s . c o m*/

    try {
        fis = new FileInputStream(FILE_PATH);

        /*
          Use XSSF for xlsx format, for xls use HSSF
        */
        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        /*
        looping over each workbook sheet
        */
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            /*
                iterating over each row
                */
            while (rowIterator.hasNext()) {

                Song song = new Song();
                Row row = (Row) rowIterator.next();

                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();

                    /*
                    checking if the cell is having a String value .
                    */
                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

                        /*
                        Cell with index 1 contains Album name 
                        */
                        if (cell.getColumnIndex() == 1) {
                            song.setAlbumname(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 2 contains Genre
                        */
                        if (cell.getColumnIndex() == 2) {
                            song.setGenre(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 3 contains Artist name
                        */
                        if (cell.getColumnIndex() == 3) {
                            song.setArtist(cell.getStringCellValue());
                        }

                    }

                    /*
                     checking if the cell is having a numeric value
                    */
                    else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        /*
                        Cell with index 0 contains Sno
                        */
                        if (cell.getColumnIndex() == 0) {
                            song.setSno((int) cell.getNumericCellValue());
                        }

                        /*
                        Cell with index 5 contains Critic score.
                        */
                        else if (cell.getColumnIndex() == 5) {
                            song.setCriticscore((int) cell.getNumericCellValue());
                        }

                        /*
                        Cell with index 4 contains Release date
                        */
                        else if (cell.getColumnIndex() == 4) {
                            Date dateValue = null;

                            if (DateUtil.isCellDateFormatted(cell)) {
                                dateValue = cell.getDateCellValue();
                            }
                            song.setReleasedate(dateValue);
                        }

                    }

                }

                /*
                end iterating a row, add all the elements of a row in list
                */
                songList.add(song);
            }
        }

        fis.close();

    } catch (FileNotFoundException e) {
    } catch (IOException e) {
    }
    return songList;
}

From source file:com.mycompany.javaapplicaton3.LerArquivo.java

public static void main(String args[]) {

    logger.info("Hello World!");

    try {//w ww. j  a va 2s  .co  m

        //File excel =  new File ("C:/Users/lprates/Documents/arquivo2013.xlsx");
        //FileInputStream fis = new FileInputStream(excel);

        OPCPackage pkg = OPCPackage.open("C:/Users/lprates/Documents/arquivo2013.xlsx");
        XSSFWorkbook myWorkBook = new XSSFWorkbook(pkg);

        // 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()) {
            Row row = rowIterator.next();

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

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t");
                    break;
                default:
                    System.out.print("Nada");
                }
            }
            System.out.println("");
        }

    } catch (Exception ex) {
        logger.error(ex.toString());
    }

}