Example usage for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getNumericCellValue

Introduction

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

Prototype

public double getNumericCellValue()

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:ro.nextreports.engine.exporter.util.XlsUtil.java

License:Apache License

/**
 * Copy a cell to another cell/*w  ww .  ja  v  a 2s  .c o m*/
 * 
 * @param oldCell cell to be copied
 * @param newCell cell to be created
 * @param styleMap style map
 */
public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
            newCell.setCellStyle(oldCell.getCellStyle());
        } else {
            int stHashCode = oldCell.getCellStyle().hashCode();
            HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                styleMap.put(stHashCode, newCellStyle);
            }
            newCell.setCellStyle(newCellStyle);
        }
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

From source file:shouQiSystem.user.ReadDriverEXL.java

private String getValue(HSSFCell hssfCell) {
    DecimalFormat df = new DecimalFormat("#");
    switch (hssfCell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            System.out.println(sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString());
            return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString();
        }//from   w  w w .  j av  a2s.  co m
        System.out.println(df.format(hssfCell.getNumericCellValue()).toString());
        return df.format(hssfCell.getNumericCellValue()).toString();

    case HSSFCell.CELL_TYPE_STRING:
        System.out.println(hssfCell.getStringCellValue());
        return hssfCell.getStringCellValue();
    case HSSFCell.CELL_TYPE_FORMULA:
        return hssfCell.getCellFormula();
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return hssfCell.getBooleanCellValue() + "";
    case HSSFCell.CELL_TYPE_ERROR:
        return hssfCell.getErrorCellValue() + "";
    }
    return "";
}

From source file:spires.printing.Encoded_baptism.java

public static List<Encoded_baptism> showExcelData(List sheetData, String path) {

    FileInputStream fis;/*from w w  w .j  av a  2 s.c om*/
    List<Encoded_baptism> datas = new ArrayList();
    try {
        fis = new FileInputStream(path);
        int r = 0;
        int r_set = 1;
        int id = 0;
        for (int i = 0; i < sheetData.size(); i++) {

            List list = (List) sheetData.get(i);
            int size = list.size();
            String[] record = new String[15];
            int record_size = 0;
            for (int j = 0; j < list.size(); j++) {

                CellReference cellReference = new CellReference("B3");
                HSSFCell cell = (HSSFCell) list.get(j);
                HSSFDataFormatter hdf = new HSSFDataFormatter();
                String data = "";
                if (j >= 14) {
                    break;
                }
                if (cell.getCellType() == 0) {
                    if (j == 5 || j == 8) {
                        data = "" + DateType.sf.format(getRoundedDate(cell.getNumericCellValue())) + "";
                    } else {
                        data = "" + cell.getNumericCellValue();
                    }
                } else {
                    data = cell.getStringCellValue();
                }

                record[record_size] = data;
                //                    System.out.print(data + " | ");
                record_size++;
            }
            //                System.out.println("");
            Date d = getRoundedDate(FitIn.toDouble(record[3]));
            String da = DateType.sf.format(d);

            int page_no = FitIn.toInt(record[0]);
            int index_no = FitIn.toInt(record[1]);
            String fname = record[2];
            String mi = record[3];
            String lname = record[4];
            String date_of_baptism = record[5];
            String parish_priest = record[6];
            String minister = record[7];
            String date_of_birth = record[8];
            String place_of_birth = record[9];
            String place_of_baptism = "";
            String father = record[10];
            String mother = record[11];
            String sponsors = record[12];
            String remarks = record[13];

            if (page_no != 0) {

                if (fname.equalsIgnoreCase("n/a")) {
                    fname = "";
                }
                if (mi.equalsIgnoreCase("n/a")) {
                    mi = "";
                }
                if (lname.equalsIgnoreCase("n/a")) {
                    lname = "";
                }
                if (parish_priest.equalsIgnoreCase("n/a")) {
                    parish_priest = "";
                }
                if (minister.equalsIgnoreCase("n/a")) {
                    minister = "";
                }
                if (place_of_birth.equalsIgnoreCase("n/a")) {
                    place_of_birth = "";
                }
                if (place_of_baptism.equalsIgnoreCase("n/a")) {
                    place_of_baptism = "";
                }
                if (father.equalsIgnoreCase("n/a")) {
                    father = "";
                }
                if (mother.equalsIgnoreCase("n/a")) {
                    mother = "";
                }
                if (sponsors.equalsIgnoreCase("n/a")) {
                    sponsors = "";
                }
                if (remarks.equalsIgnoreCase("n/a")) {
                    remarks = "";
                }
                Encoded_baptism to = new Encoded_baptism(page_no, index_no, fname, mi, lname, date_of_baptism,
                        parish_priest, minister, date_of_birth, place_of_birth, place_of_baptism, father,
                        mother, sponsors, remarks);
                datas.add(to);
            }

        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Encoded_Funeral.class.getName()).log(Level.SEVERE, null, ex);
    }

    return datas;
}

From source file:spires.printing.Encoded_Funeral.java

public static List<to_encoded> showExcelData(List sheetData, String path) {

    FileInputStream fis;//from w w  w. j  ava2  s  .co  m
    List<to_encoded> datas = new ArrayList();
    try {
        fis = new FileInputStream(path);
        int r = 0;
        int r_set = 1;
        int id = 0;
        for (int i = 0; i < sheetData.size(); i++) {
            List list = (List) sheetData.get(i);
            int size = list.size();
            String[] record = new String[11];
            int record_size = 0;
            for (int j = 0; j < list.size(); j++) {

                CellReference cellReference = new CellReference("B3");
                HSSFCell cell = (HSSFCell) list.get(j);
                HSSFDataFormatter hdf = new HSSFDataFormatter();
                String data = "";
                if (j >= 11) {
                    break;
                }
                if (j >= 0 && j <= 2 || j == 7 || j == 10) {
                    data = "" + cell.getNumericCellValue();
                } else if (j == 7 || j == 10) {
                    data = "" + DateType.sf.format(getRoundedDate(cell.getNumericCellValue())) + "";

                } else {
                    data = cell.getStringCellValue();
                }
                record[record_size] = data;
                System.out.print(data + " | ");
                record_size++;
            }
            String index_no = record[0];
            String book_no = record[1];
            String page_no = record[2];
            String date_of_burial = record[7];
            String price = record[9];
            String fname = record[3];
            String mi = record[4];
            String lname = record[5];
            String residence = record[8];
            String informant = record[6];
            String remarks = "";
            String parents = record[6];
            String date_of_burial2 = record[10];
            to_encoded t = new to_encoded(index_no, book_no, page_no, date_of_burial, price, fname, mi, lname,
                    residence, informant, remarks, parents, date_of_burial2, 0);

            if (record[0] != null) {
                datas.add(t);
                System.out.println("");
            }

        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Encoded_Funeral.class.getName()).log(Level.SEVERE, null, ex);
    }

    return datas;
}

From source file:test.ExcelUtil.java

License:Apache License

/**
 * , /*from w  ww  .j a v  a2  s . c o  m*/
 * 
 * @param cell
 * @return
 */
public static String cell2string(HSSFCell cell, HSSFFormulaEvaluator evaluator) {
    if (cell == null) {
        return null;
    }
    String str = null;
    final int cellType = cell.getCellType();

    switch (cellType) {
    case HSSFCell.CELL_TYPE_STRING:
        str = "" + cell.getRichStringCellValue().getString().trim();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            str = "" + dateFormat.format(cell.getDateCellValue());
        } else {
            str = String.valueOf(cell.getNumericCellValue());

            //modify by cyyan 2008-09-23 19:17:28 
            //excelE, ;
            //E, 15, (15, )
            // 15, 0, 0
            //            str = "" + new BigDecimal(numberStr).setScale(15, BigDecimal.ROUND_HALF_UP);
            //            
            //            //modify yanchangyou 2008-09-26 18:01:43 
            //            // .0000* , 0, 
            //            if (str.indexOf('.') != -1) {
            //               str = str.replaceAll("(\\.)?0*$", "");
            //            }            

            /*
             * , 
             */
            //            if (str.indexOf('.') != -1) { //
            //               int index = str.length();
            //               for (int i = str.length()-1; i > -1; i--) {
            //                  if (str.charAt(i) == '0') {
            //                     index = i;
            //                  } else if (str.charAt(i) == '.'){
            //                     index = i;
            //                     break;
            //                  } else {
            //                     break;
            //                  }
            //               }
            //               str = str.substring(0, index);
            //            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        str = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        str = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        str = "" + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        if (evaluator == null) {
            str = "" + cell.getRichStringCellValue().getString();
        } else {
            str = "" + evaluator.evaluate(cell).getNumberValue();
        }

        break;
    }

    return (str == null || str.trim().equals("")) ? null : str.trim();
}

From source file:tis.TPlan.java

/**
 * @author Lucia Budinsk /*from  ww  w . ja v  a 2s  . c om*/
 * na?ta excelovsk tabuku do hashmapy - vo formte k? = meno prvku a hodnota = arraylist stringov z celho riadku
 * ak nejak bunka v tabuke nie je vyplnen, do hashmapy sa zape przdny string
 * @param file nzov sboru, ktor ideme ?ta 
 */
public void nacitaj(String file) {
    try {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;
        int rows; //pocet riadkov
        rows = sheet.getPhysicalNumberOfRows();
        int cols = 0; // pocet stlpcov
        int tmp = 0;
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols) {
                    cols = tmp;
                }
            }
        }

        for (int r = 0; r < rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {
                String meno = "";
                ArrayList<String> list = new ArrayList<>();
                for (int c = 0; c < cols; c++) {
                    cell = row.getCell(c);
                    if (cell != null) {
                        if (c == 0) {
                            meno = cell.getStringCellValue();
                        }
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            list.add("" + cell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            list.add(cell.getStringCellValue());
                            break;
                        default:
                            list.add("");
                            break;
                        }
                    }
                }
                excel.put(meno, list); //do hashmapy prirad cel riadok tabuku s k?om meno prvku   
                //System.out.println(meno + list);
            }
        }
    } catch (Exception ioe) {
        ioe.printStackTrace();
    }

}

From source file:tournoi.ExcelToBDD.java

public boolean insertJoueur(String filePath) {
    boolean flag = true;
    db = new DBConnection();
    con = db.getConnection();//from www . j a v  a2  s.  c o m
    try {
        // ??excel
        FileInputStream fin = new FileInputStream(filePath); //
        HSSFWorkbook workbook = new HSSFWorkbook(fin);// 
        HSSFSheet sheet = workbook.getSheetAt(0);// 
        HSSFRow row = null;// excel
        HSSFCell cell = null;
        HSSFCell cell2 = null;
        HSSFCell cell3 = null;
        HSSFCell cell4 = null;
        HSSFCell cell5 = null;
        HSSFCell cell6 = null;
        HSSFCell cell7 = null;

        int totalRow = sheet.getLastRowNum();// excel?
        System.out.println(totalRow);
        // ?
        for (int i = 1; i <= totalRow; i++) {
            row = sheet.getRow(i);
            cell = row.getCell(0);
            int id_joueur = (int) cell.getNumericCellValue();
            cell2 = row.getCell(1);
            String nom = cell2.getRichStringCellValue().toString();
            cell3 = row.getCell(2);
            String prenom = cell3.getRichStringCellValue().toString();
            cell4 = row.getCell(3);
            int national_point = (int) cell4.getNumericCellValue();
            cell5 = row.getCell(4);
            String categorie = cell5.getRichStringCellValue().toString();
            cell6 = row.getCell(5);
            String federation = cell6.getRichStringCellValue().toString();
            cell7 = row.getCell(6);
            String ligne = cell7.getRichStringCellValue().toString();

            //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // "
            String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','"
                    + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')";
            pst = con.prepareStatement(sql);
            pst.execute();
            System.out.println("Import rows " + i);
        }
        //con.commit();
        fin.close();
        System.out.println("Success import excel to mysql table");
    } catch (FileNotFoundException e) {
        flag = false;
        System.out.println("MYSQL ERROR:" + e.getMessage());
    } catch (IOException ex) {
        flag = false;
        System.out.println("MYSQL ERROR:" + ex.getMessage());
    } catch (SQLException exx) {
        flag = false;
        System.out.println("MYSQL ERROR:" + exx.getMessage());
    } finally {

        try {
            pst.close();
            con.close();
        } catch (SQLException e) {
            System.out.println("MYSQL ERROR:" + e.getMessage());
        }
    }
    return flag;
}

From source file:tournoi.ExcelToBDD.java

public boolean insertArbitre(String filePath) {
    boolean flag = true;
    db = new DBConnection();
    con = db.getConnection();// www.j  av a  2s  .  c  om
    try {
        // ??excel
        FileInputStream fin = new FileInputStream(filePath); //
        HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file
        HSSFSheet sheet = workbook.getSheetAt(1);//Get first sheet from the workbook
        System.out.println(workbook.getSheetName(1));
        HSSFRow row = null;// excel
        HSSFCell cell = null;
        HSSFCell cell2 = null;
        HSSFCell cell3 = null;

        int totalRow = sheet.getLastRowNum();// excel?
        System.out.println(totalRow);
        // ?
        for (int i = 1; i <= totalRow; i++) {
            row = sheet.getRow(i);
            cell = row.getCell(0);
            int id_arbitre = (int) cell.getNumericCellValue();
            cell2 = row.getCell(1);
            String nom = cell2.getRichStringCellValue().toString();
            cell3 = row.getCell(2);
            String prenom = cell3.getRichStringCellValue().toString();
            //                cell4 = row.getCell(3);
            //                int national_point = (int) cell4.getNumericCellValue();
            //                cell5 = row.getCell(4);
            //                String categorie = cell5.getRichStringCellValue().toString();

            //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // "
            String sql = "INSERT INTO arbitres VALUES('" + id_arbitre + "','" + nom + "','" + prenom + "')";
            pst = con.prepareStatement(sql);
            pst.execute();
            System.out.println("Import rows " + i);
        }
        //con.commit();
        fin.close();
        System.out.println("Success import excel to mysql table");
    } catch (FileNotFoundException e) {
        flag = false;
        System.out.println("MYSQL ERROR:" + e.getMessage());
    } catch (IOException ex) {
        flag = false;
        System.out.println("MYSQL ERROR:" + ex.getMessage());
    } catch (SQLException exx) {
        flag = false;
        System.out.println("MYSQL ERROR:" + exx.getMessage());
    } finally {

        try {
            pst.close();
            con.close();
        } catch (SQLException e) {
            System.out.println("MYSQL ERROR:" + e.getMessage());
        }
    }
    return flag;
}

From source file:tournoi.ExcelToBDD.java

public boolean insertTable(String filePath) {
    boolean flag = true;
    db = new DBConnection();
    con = db.getConnection();//ww w  . j av a 2  s.c om
    try {
        // ??excel
        FileInputStream fin = new FileInputStream(filePath); //
        HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file
        HSSFSheet sheet = workbook.getSheetAt(2);//Get first sheet from the workbook
        System.out.println(workbook.getSheetName(2));
        HSSFRow row = null;// excel
        HSSFCell cell = null;
        HSSFCell cell2 = null;
        //HSSFCell cell3 = null;

        int totalRow = sheet.getLastRowNum();// excel?
        System.out.println(totalRow);
        // ?
        for (int i = 1; i <= totalRow; i++) {
            row = sheet.getRow(i);
            cell = row.getCell(0);
            int id_table = (int) cell.getNumericCellValue();
            cell2 = row.getCell(1);
            int id_salle = (int) cell2.getNumericCellValue();

            //                cell3 = row.getCell(2);
            //                String prenom = cell3.getRichStringCellValue().toString();
            //                cell4 = row.getCell(3);
            //                int national_point = (int) cell4.getNumericCellValue();
            //                cell5 = row.getCell(4);
            //                String categorie = cell5.getRichStringCellValue().toString();

            //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // "
            String sql = "INSERT INTO tables VALUES('" + id_table + "','" + id_salle + "')";
            pst = con.prepareStatement(sql);
            pst.execute();
            System.out.println("Import rows " + i);
        }
        //con.commit();
        fin.close();
        System.out.println("Success import excel to mysql table");
    } catch (FileNotFoundException e) {
        flag = false;
        System.out.println("MYSQL ERROR:" + e.getMessage());
    } catch (IOException ex) {
        flag = false;
        System.out.println("MYSQL ERROR:" + ex.getMessage());
    } catch (SQLException exx) {
        flag = false;
        System.out.println("MYSQL ERROR:" + exx.getMessage());
    } finally {

        try {
            pst.close();
            con.close();
        } catch (SQLException e) {
            System.out.println("MYSQL ERROR:" + e.getMessage());
        }
    }
    return flag;
}

From source file:tournoi.ExcelToBDD.java

public boolean insertJoueur(String filePath, String table) {
    boolean flag = true;
    db = new DBConnection();
    con = db.getConnection();//ww  w  . ja  va  2s  .  c o m
    try {
        // ??excel
        FileInputStream fin = new FileInputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fin);// 
        HSSFSheet sheet = workbook.getSheetAt(0);// 

        HSSFRow row = null;// excel
        HSSFCell cell = null;
        HSSFCell cell2 = null;
        HSSFCell cell3 = null;
        HSSFCell cell4 = null;
        HSSFCell cell5 = null;
        HSSFCell cell6 = null;
        HSSFCell cell7 = null;
        int totalCol = sheet.getDefaultColumnWidth();// obtenir le nbr de column
        for (int i = 1; i <= totalCol; i++) {

        }

        int totalRow = sheet.getLastRowNum();// excel?
        System.out.println(totalRow);
        // ?
        for (int i = 1; i <= totalRow; i++) {
            row = sheet.getRow(i);
            cell = row.getCell(0);
            int id_joueur = (int) cell.getNumericCellValue();
            cell2 = row.getCell(1);
            String nom = cell2.getRichStringCellValue().toString();
            cell3 = row.getCell(2);
            String prenom = cell3.getRichStringCellValue().toString();
            cell4 = row.getCell(3);
            int national_point = (int) cell4.getNumericCellValue();
            cell5 = row.getCell(4);
            String categorie = cell5.getRichStringCellValue().toString();
            cell6 = row.getCell(5);
            String federation = cell6.getRichStringCellValue().toString();
            cell7 = row.getCell(6);
            String ligne = cell7.getRichStringCellValue().toString();

            //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // "
            String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','"
                    + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')";
            pst = con.prepareStatement(sql);
            pst.execute();
            System.out.println("Import rows " + i);
        }
        //con.commit();
        fin.close();
        System.out.println("Success import excel to mysql table");
    } catch (FileNotFoundException e) {
        flag = false;
        System.out.println("MYSQL ERROR:" + e.getMessage());
    } catch (IOException ex) {
        flag = false;
        System.out.println("MYSQL ERROR:" + ex.getMessage());
    } catch (SQLException exx) {
        flag = false;
        System.out.println("MYSQL ERROR:" + exx.getMessage());
    } finally {

        try {
            pst.close();
            con.close();
        } catch (SQLException e) {
            System.out.println("MYSQL ERROR:" + e.getMessage());
        }
    }
    return flag;
}