Example usage for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. getNumericCellValue

Introduction

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

Prototype

@Override
public double getNumericCellValue()

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:db.pj.util.excel.LicenseImporter.java

License:Open Source License

@Override
public List<String> importExcel() {
    // get workbook
    FileInputStream inputStream = null;
    XSSFWorkbook workbook = null;//from   ww w .  ja v  a2 s  . c o m
    List<String> result = new ArrayList<String>();
    result.add("");
    try {
        inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception e) {
        result.set(0, "");
        return result;
    }

    int success = 0, fail = 0; // number of tuples
    UserDao userDao = DaoFactory.getUserDao();
    LicenseDao licenseDao = DaoFactory.getLicenseDao();
    AdministrationDao administrationDao = DaoFactory.getAdministrationDao();

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;

    int rows = sheet.getPhysicalNumberOfRows();
    for (int i = 1; i < rows; i++) {
        row = sheet.getRow(i);
        if (row == null)
            continue;

        int cells = row.getPhysicalNumberOfCells();
        String[] values = new String[cells];

        // read data to an array of strings
        for (short j = 0; j < cells; j++) {
            cell = row.getCell(j);
            if (cell == null)
                values[j] = null;
            else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        double d = cell.getNumericCellValue();
                        Date date = new Date(DateUtil.getJavaDate(d).getTime());
                        values[j] = date.toString();
                    } else {
                        values[j] = (int) (cell.getNumericCellValue()) + "";
                    }
                    break;
                default:
                    values[j] = cell.getStringCellValue();
                    break;
                }
            }
        }

        User user = new User();
        user.setUserID(IDGenerator.generateUserID(values[5] + values[6]));
        user.setUserPwd(StringUtil.MD5(user.getUserID()));
        user.setUserName(values[0]);
        user.setUserGender(XMLRepertory.genderStr2Num(values[1]));
        user.setUserIctype(Integer.parseInt(values[5]));
        user.setUserIcno(values[6]);
        user.setUserBdate(MyDateUtil.str2Date(values[2]));
        user.setUserPhone("00000000");
        user.setUserNation(values[3]);
        user.setUserAddr(values[4]);
        user.setUserHead("user/default.png");
        user.setUserHealth(0);
        user.setUserState(5);
        user.setUserPdate(new Date(System.currentTimeMillis()));

        License license = new License();
        license.setLicnID(values[11]);
        license.setLicnType(values[8]);
        license.setLicnGdate(MyDateUtil.str2Date(values[7]));
        license.setLicnVdlen(Integer.parseInt(values[9]));
        // if point>=12, set invalid
        license.setLicnVinfo(Integer.parseInt(values[12]));
        license.setLicnValid((license.getLicnVinfo() < 12) ? 1 : 0);
        // license is not valid, reset health and state
        if (license.getLicnValid() == 0) {
            user.setUserHealth(-1);
            user.setUserState(0);
        }
        user.setLicense(license);
        license.setUser(user);
        // get administration and check whether it is valid
        Administration administration = administrationDao.queryAdministrationByID(values[10]);
        if (administration == null) {
            fail++;
            result.add("" + values[11] + "");
        } else {
            license.setAdministration(administration);
            boolean re1 = userDao.addUser(user);
            boolean re2 = licenseDao.addLicense(license);

            if (re1 || re2)
                success++;
            else {
                fail++;
                result.add("" + values[11] + "");
            }
        }
    }

    result.set(0, "" + success + "" + fail + "");
    return result;
}

From source file:db.pj.util.excel.PenaltyImporter.java

@Override
public List<String> importExcel() {
    // get workbook
    FileInputStream inputStream = null;
    XSSFWorkbook workbook = null;/*from   ww  w. j  a  v a  2 s. co m*/
    List<String> result = new ArrayList<String>();
    result.add("");
    try {
        inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception e) {
        result.set(0, "");
        return result;
    }

    int success = 0, fail = 0; // number of tuples
    LicenseDao licenseDao = DaoFactory.getLicenseDao();
    PenaltyDao penaltyDao = DaoFactory.getPenaltyDao();

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;

    int rows = sheet.getPhysicalNumberOfRows();
    int cells = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int i = 1; i < rows; i++) {
        row = sheet.getRow(i);
        if (row == null)
            continue;

        String[] values = new String[cells];

        // read data to an array of strings
        for (short j = 0; j < cells; j++) {
            cell = row.getCell(j);
            if (cell == null)
                values[j] = null;
            else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        double d = cell.getNumericCellValue();
                        Date date = new Date(DateUtil.getJavaDate(d).getTime());
                        values[j] = date.toString();
                    } else {
                        values[j] = (int) (cell.getNumericCellValue()) + "";
                    }
                    break;
                default:
                    values[j] = cell.getStringCellValue();
                    break;
                }
            }
        }

        License license = licenseDao.queryLicenseByUserID(values[0]);

        if (license == null) {
            fail++;
            result.add("" + values[0] + "," + values[1] + "," + values[2]
                    + "");
        } else {

            Penalty penalty = new Penalty();
            penalty.setPnltID(IDGenerator.generateUUID());
            penalty.setPnltType(Integer.parseInt(values[3]));
            penalty.setPnltTime(MyDateUtil.str2Date(values[1]));
            penalty.setPnltInfo(values[2]);
            // set penalty point and limit
            if (penalty.getPnltType() == 5) {
                penalty.setPnltPoint(Integer.parseInt(values[5]));
                license.addPnltPoint(penalty.getPnltPoint());
            } else {
                penalty.setPnltPoint(0);
                license.setLicnValid(0);
                if (penalty.getPnltType() == 3)
                    license.setLicnLimit(MyDateUtil.getLimitDate(penalty.getPnltTime(), 100));
                else
                    license.setLicnLimit(
                            MyDateUtil.getLimitDate(penalty.getPnltTime(), Integer.parseInt(values[4])));
            }
            boolean re1 = licenseDao.updateLicense(license);
            penalty.setLicense(license);
            boolean re2 = penaltyDao.addPenalty(penalty);

            if (re1 && re2)
                success++;
            else {
                fail++;
                result.add("" + values[0] + "," + values[1] + "," + values[2]
                        + "");
            }
        }

    }

    result.set(0, "" + success + "" + fail + "");
    return result;
}

From source file:db.pj.util.excel.Phase4GradeImporter.java

@Override
public List<String> importExcel() {
    // get workbook
    FileInputStream inputStream = null;
    XSSFWorkbook workbook = null;/* w w  w. jav  a  2s  .  co  m*/
    List<String> result = new ArrayList<String>();
    result.add("");
    try {
        inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception e) {
        result.set(0, "");
        return result;
    }

    int success = 0, fail = 0; // number of tuples
    UserDao userDao = DaoFactory.getUserDao();
    StaffDao staffDao = DaoFactory.getStaffDao();
    ScheduleDao scheduleDao = DaoFactory.getScheduleDao();
    ExaminationDao examinationDao = DaoFactory.getExaminationDao();
    LicenseDao licenseDao = DaoFactory.getLicenseDao();
    WarningDao warningDao = DaoFactory.getWarningDao();

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;

    int rows = sheet.getPhysicalNumberOfRows();
    int cells = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int i = 1; i < rows; i++) {
        row = sheet.getRow(i);
        if (row == null)
            continue;

        String[] values = new String[cells];

        // read data to an array of strings
        for (short j = 0; j < cells; j++) {
            cell = row.getCell(j);
            if (cell == null)
                values[j] = null;
            else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    values[j] = (int) (cell.getNumericCellValue()) + "";
                    break;
                default:
                    values[j] = cell.getStringCellValue();
                    break;
                }
            }
        }

        User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]);
        Schedule schedule = scheduleDao.queryScheduleByID(values[3]);
        Examination examination = null;

        if (user == null) {
            fail++;
            String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + "";
            result.add("" + ic + "" + values[3]
                    + "");
        } else if (schedule == null) {
            fail++;
            String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1] + "";
            result.add(
                    "" + ic + "" + values[3] + "");
        } else {
            examination = examinationDao.queryExaminationByUserAndSchedule(user, schedule);
            if (examination == null) {
                fail++;
                String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1]
                        + "";
                result.add("" + ic + "" + values[3]
                        + "");
                Warning warning = new Warning();
                warning.setWarnID(IDGenerator.generateUUID());
                warning.setUser(user);
                warning.setSchedule(schedule);
                warning.setWarnCont(StringUtil.buildPhase14Warning(values));
                warningDao.addWarning(warning);
            } else {
                examination.setStaff(staffDao.queryStaffByStaffID(values[2]));
                examination.setExamScore1(Integer.parseInt(values[4]));
                examination.setExamPass(examination.getExamScore1() >= 60 ? 1 : 0);
                examination.setExamFinfo(StringUtil.buildGradeFinfo(values[5]));

                if (examination.getExamPass() == 1 && user.getUserState() == 4) {
                    boolean re = examinationDao.updateExamination(examination);
                    if (re)
                        success++;
                    else {
                        fail++;
                        String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1]
                                + "";
                        result.add("" + ic + "" + values[3]
                                + "");
                    }

                    user.setUserState(5);

                    License license = licenseDao.queryLicenseByUserID(user.getUserID());
                    if (license == null) {
                        License newLicn = new License();
                        newLicn.setLicnID(IDGenerator.generateLicenseID(user.getUserID()));
                        newLicn.setLicnType("C1");
                        newLicn.setLicnGdate(schedule.getSchdDate());
                        newLicn.setLicnVdlen(10);
                        newLicn.setLicnValid(1);
                        newLicn.setLicnVinfo(0);
                        newLicn.setAdministration(schedule.getAdministration());
                        newLicn.setLicnLimit(null);
                        newLicn.setUser(user);

                        user.setLicense(license);

                        userDao.updateUser(user);
                        licenseDao.addLicense(newLicn);

                    } else {
                        license.setLicnValid(1);
                        license.setLicnGdate(schedule.getSchdDate());
                        license.setLicnLimit(null);
                        license.setLicnVinfo(0);
                        licenseDao.updateLicense(license);
                    }
                } else if (user.getUserState() < 4) {
                    fail++;
                    String ic = (Integer.parseInt(values[0]) == 1 ? "" : "") + values[1]
                            + "";
                    result.add("" + ic + "" + values[3]
                            + "");
                }

            }
        }

    }

    result.set(0, "" + success + "" + fail + "");
    return result;
}

From source file:de.escnet.ExcelTable.java

License:Open Source License

private String getCellValue(XSSFCell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";

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

    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellString();

    case Cell.CELL_TYPE_FORMULA:
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            return "";

        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cellValue.getBooleanValue());

        case Cell.CELL_TYPE_NUMERIC:
            return getNumericValue(cellValue.getNumberValue());

        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();

        case Cell.CELL_TYPE_ERROR:
        case Cell.CELL_TYPE_FORMULA:
        default:/*from  w  w  w.  j a va  2s.  c  om*/
            throw new IllegalStateException("Illegal cell type: " + cell.getCellType());
        }

    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue().toString();
        }

        return getNumericValue(cell.getNumericCellValue());

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

    default:
        throw new IllegalStateException("Illegal cell type: " + cell.getCellType());
    }
}

From source file:dias.Load.java

public Matrix load(Matrix matrice, String filename) throws FileNotFoundException, IOException {

    FileInputStream fis = null;//from  w  ww  .j  a v  a 2s  . c  o  m
    try {

        String fileName = DIAS.excelFilePath + "\\" + filepath + "\\" + filename + ".xlsx";
        fis = new FileInputStream(fileName);
        XSSFWorkbook calismaKitap = new XSSFWorkbook(fis);
        XSSFSheet sheet = calismaKitap.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        int ih = 0;
        int jh = 0;

        while (rows.hasNext()) {
            XSSFRow row = (XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            ih++;
            while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                jh++;
                //     matrice=createnewMatrix(ih,jh, matrice);
                matrice.set(ih - 1, jh - 1, cell.getNumericCellValue());
            }
            jh = 0;
        }
        ih = 0;
        jh = 0;
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return matrice;
}

From source file:dias.Load.java

public double[][][] load3D(double[][][] matrice3d, String filename, int kj)
        throws FileNotFoundException, IOException {
    Matrix matrice = new Matrix(matrice3d.length, matrice3d[0].length);

    FileInputStream fis = null;/*  w  ww  .  j  av  a 2  s  . com*/

    for (int t = 0; t < kj; t++) {

        try {

            String fileName = "D:\\Phd\\Research\\Kamuran`s Code\\" + filepath + "\\" + filename + t + ".xlsx";
            fis = new FileInputStream(fileName);
            XSSFWorkbook calismaKitap = new XSSFWorkbook(fis);
            XSSFSheet sheet = calismaKitap.getSheetAt(0);
            Iterator rows = sheet.rowIterator();
            int ih = 0;
            int jh = 0;

            while (rows.hasNext()) {
                XSSFRow row = (XSSFRow) rows.next();
                Iterator cells = row.cellIterator();
                ih++;
                while (cells.hasNext()) {
                    XSSFCell cell = (XSSFCell) cells.next();
                    jh++;
                    //           matrice=createnewMatrix(ih,jh, matrice);
                    matrice.set(ih - 1, jh - 1, cell.getNumericCellValue());
                }
                jh = 0;
            }
            ih = 0;
            jh = 0;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        matrice3D = new double[matrice.getRowDimension()][matrice.getColumnDimension()][kj];

        for (int i = 0; i < matrice.getRowDimension(); i++)
            for (int j = 0; j < matrice.getColumnDimension(); j++)
                matrice3D[i][j][t] = matrice.get(i, j);
    }

    return matrice3D;
}

From source file:dias.Load.java

public double loaddouble(String filename) throws FileNotFoundException, IOException {
    ///  double[][] matrice=new double[150][150];
    double matrice = 0;
    FileInputStream fis = null;//from  ww w.  j ava 2s . com
    try {

        String fileName = "D:\\Phd\\Research\\Kamuran`s Code\\" + filepath + "\\" + filename + ".xlsx";
        fis = new FileInputStream(fileName);
        XSSFWorkbook calismaKitap = new XSSFWorkbook(fis);
        XSSFSheet sheet = calismaKitap.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        int ih = 0;
        int jh = 0;

        while (rows.hasNext()) {
            XSSFRow row = (XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            ih++;
            while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                jh++;
                matrice = cell.getNumericCellValue();
            }
            jh = 0;
        }
        ih = 0;
        jh = 0;
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return matrice;
}

From source file:dias.load_plsdata730_R_12_withcluster.java

public Matrix loadwithsheetname(String filename, String Sheetname) throws FileNotFoundException, IOException {
    FileInputStream fis = null;/*from  w ww . j a  v a2s. c o m*/
    try {

        String fileName = DIAS.excelFilePath + filename + ".xlsx";
        //////////////////////////////////////////////////Optimization of memory usage/////////////////////////////////////////////////////////////////////////////

        if (Sheetname.equals("x_cluster1")) {
            matrix = new Matrix(1899, 37);
        } else if (Sheetname.equals("x_cluster2")) {
            matrix = new Matrix(1865, 37);
        } else if (Sheetname.equals("x_cluster3")) {
            matrix = new Matrix(1844, 37);
        } else if (Sheetname.equals("x_cluster4")) {
            matrix = new Matrix(1876, 37);
        } else if (Sheetname.equals("x_cluster5")) {
            matrix = new Matrix(2238, 37);
        } else if (Sheetname.equals("y_cluster1")) {
            matrix = new Matrix(1899, 37);
        } else if (Sheetname.equals("y_cluster2")) {
            matrix = new Matrix(1865, 37);
        } else if (Sheetname.equals("y_cluster3")) {
            matrix = new Matrix(1844, 37);
        } else if (Sheetname.equals("y_cluster4")) {
            matrix = new Matrix(1876, 37);
        } else if (Sheetname.equals("y_cluster5")) {
            matrix = new Matrix(2238, 37);
        } else if (Sheetname.equals("thita_mm1")) {
            matrix = new Matrix(1, 37);
        } else if (Sheetname.equals("thita_mm2")) {
            matrix = new Matrix(1, 37);
        } else if (Sheetname.equals("thita_mm3")) {
            matrix = new Matrix(1, 37);
        } else if (Sheetname.equals("thita_mm4")) {
            matrix = new Matrix(1, 37);
        } else if (Sheetname.equals("thita_mm5")) {
            matrix = new Matrix(1, 37);
        } else if (Sheetname.equals("rc1")) {
            matrix = new Matrix(37, 1899);
        } else if (Sheetname.equals("rc2")) {
            matrix = new Matrix(37, 1865);
        } else if (Sheetname.equals("rc3")) {
            matrix = new Matrix(37, 1844);
        } else if (Sheetname.equals("rc4")) {
            matrix = new Matrix(37, 1876);
        } else if (Sheetname.equals("rc5")) {
            matrix = new Matrix(37, 2238);
        }
        //////////////////////////////////////////////////////Optimization of memory usage/////////////////////////////////////////////////////////////////////////////

        fis = new FileInputStream(fileName);
        XSSFWorkbook calismaKitap = new XSSFWorkbook(fis);
        XSSFSheet sheet = calismaKitap.getSheet(Sheetname);
        Iterator rows = sheet.rowIterator();
        int ih = 0;
        int jh = 0;

        while (rows.hasNext()) {
            XSSFRow row = (XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            ih++;
            while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                jh++;
                matrix.set(ih - 1, jh - 1, cell.getNumericCellValue());
            }
            jh = 0;
        }
        ih = 0;
        jh = 0;
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return matrix;
}

From source file:entities.HangNhap.java

public HangNhap(XSSFCell maHangHoa, XSSFCell tenHangHoa, XSSFCell giaMua, XSSFCell soLuong,
        XSSFCell nhomHangHoa) {/*from   w  w  w. ja  v  a2  s .c  om*/

    this.mMaHangHoa = maHangHoa.getStringCellValue();
    this.mTenHangHoa = tenHangHoa.getStringCellValue();
    this.mGiaMua = (int) giaMua.getNumericCellValue();
    this.mSoLuong = (int) soLuong.getNumericCellValue();
    this.mNhomHangHoa = nhomHangHoa.getStringCellValue();

}

From source file:foodbankyfs.FbMainFx.java

private List<String[]> saveSpreadsheetData() {

    // Copy spreadsheet contents into memory
    try {//from  w w w .  ja v  a2 s. co  m

        // Initialize xls reading objects
        FileInputStream fileInputStream = new FileInputStream(spreadsheet);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet worksheet = workbook.getSheet(FbConstants.SHEET_NAME);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        List<String[]> tmpData = new ArrayList();

        // Save XSSF objects for rewrite
        wrksheet = worksheet;
        wrkbook = workbook;

        // Iterate through all rows in the sheet
        for (int rowNum = FbConstants.DATA_ROW_START_INDEX; rowNum < worksheet.getLastRowNum(); rowNum++) {

            // Initialize array that will store cell contents
            String values[] = new String[FbConstants.NUMBER_OF_COLUMNS];
            XSSFRow row = worksheet.getRow(rowNum);

            // Iterate through cells in each row and store values to an array
            for (int cellNum = 0; cellNum < FbConstants.NUMBER_OF_COLUMNS; cellNum++) {
                XSSFCell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK);

                String value = "";

                if (cell != null) {

                    if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateInCell(cell);
                    }
                    // If cell type is numeric convert the number value to a string
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                        double tmpVal = cell.getNumericCellValue();
                        value = String.format("%.0f", tmpVal);
                    }
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        value = cell.getStringCellValue().toLowerCase();

                    }

                }

                // If a cell row has an empty ID do not include it in data
                if (cellNum == 0 && value.equals("")) {
                    break;
                }

                // Initialize value to 0 if cell is empty
                if (value.isEmpty()) {

                    // If value is from email or notes field then put empty instead
                    if (cellNum == FbConstants.EMAIL_FIELD || cellNum == FbConstants.NOTES_FIELD) {
                        value = "empty";
                    } else {
                        value = "0";
                    }

                }

                // Store value in array
                values[cellNum] = value;

            }

            // Store array of values in list
            tmpData.add(values);

        }

        return tmpData;

    } catch (IOException e) {
        System.err.println(e);
    }

    return null;
}