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:math.page.KnapsackTest.java

License:Apache License

public static List<Total> test4() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "total.xlsx";
    File file = new File(path);
    // Workbook workbook = Workbook.getWorkbook(file);
    Workbook workbook = WorkbookFactory.create(file);
    // Sheet sheet = workbook.getSheet(0);
    Sheet sheet = workbook.getSheetAt(0);
    List<Total> bags = new ArrayList<Total>();
    try {//  w  ww  .j ava2s  .c o m

        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            // Cell[] cells = sheet.getRow(row);
            // System.out.println(cells[0].getContents());
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }

            Total total = new Total();
            total.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());
            total.setTotal(integer);
            bags.add(total);
        }
        Arrays.sort(bags.toArray());
    } catch (Exception e) {
        e.printStackTrace();
    }

    return bags;
}

From source file:midas.sheeco.exceptions.SpreadsheetViolation.java

License:Apache License

private static Object getCellValue(final Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }//from   w w w.j  a  v  a 2 s. com
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    }
    throw new UnsupportedOperationException("CellType " + cell.getCellType() + " is invalid");
}

From source file:midas.sheeco.type.adapter.SpreadsheetBooleanAdapterTest.java

License:Apache License

@Test
public void testNumericTrue() {
    SpreadsheetBooleanAdapter adapter = new SpreadsheetBooleanAdapter();

    Cell cell = mock(Cell.class);
    when(cell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
    when(cell.getNumericCellValue()).thenReturn(2d);

    Boolean value = adapter.fromSpreadsheet(cell);
    Assert.assertTrue(value);// w w w .j  ava2s  .co m
}

From source file:midas.sheeco.type.adapter.SpreadsheetBooleanAdapterTest.java

License:Apache License

@Test
public void testNumericFalse() {
    SpreadsheetBooleanAdapter adapter = new SpreadsheetBooleanAdapter();

    Cell cell = mock(Cell.class);
    when(cell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
    when(cell.getNumericCellValue()).thenReturn(0d);

    Boolean value = adapter.fromSpreadsheet(cell);
    Assert.assertFalse(value);/*from  www. java 2s  .  c  o m*/
}

From source file:mindbodymerge.Parser.java

/**
 * Parses the Sales data excel sheet that was passed in, and creates Array List populated with the member's names (memberList), the items the members
 * purchase (myzouItemList) and the price of the items that they paid (itemPriceList)
 * All array lists are indexed to the same person (i.e. memberList.get(0) bought myzouItemList.get(0) and paid itemPriceList.get(0))
 * Parsing of the excel files are hard coded to the columns that held the correct data fields, and will need to be changed if the format of the excel 
 * file changes//from   ww  w.  j  av a2  s  .  co m
 *
 * @param memberList: list of members who purchased items
 * @param myzouItemList: list of what the members purchased
 * @param itemPriceList: list of how much the member paid
 * 
 * @return void
 *
*/
private void parseSales(ArrayList<String> memberList, ArrayList<String> myzouItemList,
        ArrayList<String> itemPriceList) {

    try {
        //Variable to be used in determining which columns of the excel file should be read from used in the while loop
        int iterationCount = 0;

        //Used for parsing of the item price into an item string
        Double itemNumber;
        String itemString;

        //Holds mocode
        String mocode;

        //Uses Apache POI .XLSX file parser to get the data, as treating it like a normal .CSV will give weird data
        //sales is the sales data.xlsx file
        FileInputStream fis = new FileInputStream(sales);

        Database db = new Database();

        //Open WorkBook (.XLSX file)
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Open first sheet of workbook, shouldn't have to change unless the formatting of the sales data.xlsx file changes to a different sheet
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        //Iterator for moving through each row of the sheet
        Iterator<Row> rowIterator = mySheet.iterator();

        //This first call of the iterator is to move the iterator past the labels at the top of the columns in the excel sheet
        rowIterator.next();

        //Move through each row of the excel file
        while (rowIterator.hasNext()) {
            //Move to next row
            Row row = rowIterator.next();
            //Iterator for the cells in the row
            Iterator<Cell> cellIterator = row.cellIterator();

            //Reset the iterationCount to 0 so the while loop below knows what column we are in
            iterationCount = 0;

            while (cellIterator.hasNext()) {
                //Move to the next cell
                Cell cell = cellIterator.next();

                //The second column (column B) holds the list of member names, so we read from that column to the memberList
                if (iterationCount == 1) {
                    //Get rid of all the spaces so matching is easier
                    memberList.add(cell.getStringCellValue().replaceAll("\\s", ""));
                }
                //The fourth column (column D) holds the list of purchased items, so we read from the column to the memberList
                else if (iterationCount == 3) {
                    mocode = db.getMocode(cell.getStringCellValue());
                    myzouItemList.add(mocode);
                }
                //The 17th column (column Q) holds the list of amount paid for the items with tax
                //Make sure that you choose the column that holds the actual amount paid (e.g. the row with negative numbers showing credit charges and tax)
                //number is taken in as a double, and formatted as a string to be added
                else if (iterationCount == 16) {
                    itemNumber = cell.getNumericCellValue();
                    itemString = String.format("%.2f", itemNumber);
                    itemPriceList.add(itemString);
                }

                //Move counter to next cell
                iterationCount++;
            }
        }
        //Test block for ensuring the lists are correct, the sizes should all be equal
        //            System.out.println(memberList.size());
        //            System.out.println(myzouItemList.size());
        //            System.out.println(itemPriceList.size());
        //            
        //            System.out.println(memberList);
        //            System.out.println(myzouItemList);
        //            System.out.println(itemPriceList);
    } catch (IOException e) {

    }
}

From source file:mindbodymerge.Parser.java

/**
 * Parses the membership.XLSX file to make a dictionary of student names and student numbers
 * Format is membershipDictionary.get(i) is the student number and membershipDictionary.get(i+1) is the student name for that student number
 * This is only because in the excel file the student number comes first, if this changes you will have to change the logic in the parseMemberList method
 * Almost the exact same logic as parseSales method, so look at that if this is confusing
 *
 * @param membershipDictionary: dictionary of student numbers/names where the student number membershipDictionary.get(i) correlates to the student name membershipDictionary.get(i+1)
*///from w  ww  .  j a  v a  2 s.co  m
private void parseMembership(ArrayList<String> membershipDictionary) {
    //IOException
    try {
        int iterationCount;
        Double studentNumber;
        String studentString;

        FileInputStream fis = new FileInputStream(membership);

        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        Iterator<Row> rowIterator = mySheet.iterator();
        rowIterator.next();

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

            iterationCount = 0;

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

                //This is the only part in which this is different from parseSales method
                //In the current format of the excel sheet, there are some rows in the middle of the sheet which contains column headers, not the data we want
                //In order to get rid of this data, we check if the data in column A is a number,
                //If it isn't, it's column header data and we skip over it by setting the iterationCount to 2 (which is past any data we need)
                if (iterationCount == 0) {
                    //Check if the cellType is numeric by comparing it to 0 because Cell.CELL_TYPE_NUMERIC == 0)
                    if (cell.getCellType() == 0) {
                        studentNumber = cell.getNumericCellValue();
                        studentString = String.format("%.0f", studentNumber);
                        membershipDictionary.add(studentString);
                    } else
                        iterationCount = 2;
                }
                //Get the student name that correlates to the student number we just parsed
                else if (iterationCount == 1) {
                    //Have to replace some super weird unicode garbage from the data
                    membershipDictionary
                            .add(cell.getStringCellValue().replaceAll("\u00a0", "").replaceAll(" ", ""));
                }

                iterationCount++;
            }
        }
        //Testing block, should have a student number followed by a student name for each person in the excel sheet
        //            System.out.println(membershipDictionary.size());
        //            System.out.println(membershipDictionary);
    } catch (IOException e) {

    }
}

From source file:misuExcel.excelAdd.java

License:Open Source License

private String getCellString(Cell cell) {
    try {/*from  ww  w  .  j av  a2s . c om*/
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString().trim();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString().trim();
            } else {
                return String.valueOf(cell.getNumericCellValue()).trim();
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue()).trim();
        case Cell.CELL_TYPE_FORMULA:
            return String.valueOf(cell.getCellFormula()).trim();
        default:
            return cell.getRichStringCellValue().getString().trim();
        }
    } catch (NullPointerException e) {
        JOptionPane.showMessageDialog(null, e.getMessage(), "", JOptionPane.ERROR_MESSAGE);
    }
    return null;
}

From source file:misuExcel.excelSplit.java

License:Open Source License

private String getCellString(Cell cell) {
    try {/*from  w  ww.  ja va 2 s. c o m*/
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString().trim();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString().trim();
            } else {
                return String.valueOf(cell.getNumericCellValue()).trim();
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue()).trim();
        case Cell.CELL_TYPE_FORMULA:
            return String.valueOf(cell.getCellFormula()).trim();
        default:
            return cell.getStringCellValue().trim();
        }
    } catch (NullPointerException e) {
        JOptionPane.showMessageDialog(null, e.getMessage(), "", JOptionPane.ERROR_MESSAGE);
    }
    return null;
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType01() {
    if (wbSheet != null && names != null && list != null) {
        Log.info("list size:" + list.size());
        String strinfo = "";
        for (int i = 0; i < list.size(); i++) {
            ArrayList<Integer> integers = list.get(i);
            Workbook splitWb = null;//from w ww . j  a  v  a 2  s . com
            if (indexType == 1)
                splitWb = new XSSFWorkbook();
            else if (indexType == 2)
                splitWb = new HSSFWorkbook();
            Sheet sheet = splitWb.createSheet("split");
            for (int j = 0; j < integers.size() + splitJpanel.ignore_Row; j++) {
                Row row = null;
                Row copy = null;
                if (j >= splitJpanel.ignore_Row) {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(integers.get(j - splitJpanel.ignore_Row));
                } else {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(j);
                }
                for (int k = 0; k < copy.getLastCellNum(); k++) {
                    Cell cell = row.createCell(k);
                    Cell copyCell = copy.getCell(k);
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
            createWB(splitWb, names.get(i));
            Log.info(names.get(i) + ".xlsx?");
            strinfo += names.get(i) + "." + _index + "?;";
            if (i != 0 && i % 3 == 0) {
                strinfo += "\n";
            }
        } //end for
        JOptionPane.showMessageDialog(null, strinfo);
    }
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType02() {
    if (wbSheet != null && names != null && list != null) {
        Log.info("list size:" + list.size());
        Workbook splitWb = null;/* w w  w .ja va 2s  .  c  o  m*/
        if (indexType == 1)
            splitWb = new XSSFWorkbook();
        else if (indexType == 2)
            splitWb = new HSSFWorkbook();
        for (int i = 0; i < list.size(); i++) {
            ArrayList<Integer> integers = list.get(i);
            Sheet sheet = splitWb.createSheet(names.get(i));
            for (int j = 0; j < integers.size() + splitJpanel.ignore_Row; j++) {
                Row row = null;
                Row copy = null;
                if (j >= splitJpanel.ignore_Row) {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(integers.get(j - splitJpanel.ignore_Row));
                } else {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(j);
                }
                for (int k = 0; k < copy.getLastCellNum(); k++) {
                    Cell cell = row.createCell(k);
                    Cell copyCell = copy.getCell(k);
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
        } //end for
        createWB(splitWb, fileReal + "(cut)");
        JOptionPane.showMessageDialog(null, fileReal + "(cut)." + _index + "?");
    }
}