Example usage for org.apache.poi.ss.usermodel Row cellIterator

List of usage examples for org.apache.poi.ss.usermodel Row cellIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. cellIterator

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row cellIterator.

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:comparator.Comparator.java

public static void translation() throws IOException {

    //Get the input files
    FileInputStream newMTC = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx"));

    String icdCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\CIM-10\\CIM10GM2014_S_FR_ClaML_2014.10.31.xml";
    String atcCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\ATCcodes\\ATCDPP.CSV";

    //Prepare the output file
    Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\CIM10-treated.csv"),
            "UTF-8"));
    csvW.write('\ufeff');
    Writer csvW2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\ATC-treated.csv"),
            "UTF-8"));
    csvW2.write('\ufeff');
    List<String> translationList = new ArrayList();
    Map<String, String> translatList = new HashMap();
    Map<String, String> translatAtcList = new HashMap();
    Map<String, String> translatAtcList2 = new HashMap();
    String codeTemp = "";
    boolean prefered = false;

    InputStream ips = new FileInputStream(icdCodes);
    //Cp1252 --> ANSI
    InputStreamReader ipsr = new InputStreamReader(ips, "UTF-8");
    BufferedReader br = new BufferedReader(ipsr);
    String ligne;/*from  ww  w  .j a v  a 2s.c  om*/
    Pattern p1 = Pattern.compile("<Class code=\"(.+?)\"");
    Pattern p2 = Pattern.compile("xml:space=\"default\">(.+?)<");
    Pattern p3 = Pattern.compile("(.+?)\\..");
    Pattern pActiveIngredient = Pattern.compile("(?:.*;){8}\"(.+?)\";(?:.*;)\"(.+?)\";(?:.*;){5}.*");
    Pattern pActiveIngredient2 = Pattern.compile("(?:.*;){4}\"(.+?)\";(?:.*;){5}\"(.+?)\";(?:.*;){5}.*");
    Matcher m1;
    Matcher m2;
    Matcher m3;
    Matcher mActiveIngredient;
    Matcher mActiveIngredient2;

    while ((ligne = br.readLine()) != null) {
        m1 = p1.matcher(ligne);
        m2 = p2.matcher(ligne);

        if (ligne.matches("</Class>")) {
            prefered = false;
            codeTemp = "";
        }

        if (m1.find()) {
            codeTemp = m1.group(1);
        }

        if (ligne.matches("(.*)kind=\"preferred\"(.*)")) {
            prefered = true;
        }

        if (m2.find() && prefered == true) {
            translatList.put(codeTemp, m2.group(1));
            prefered = false;
        }

        //si traduction franais ET anglais
        if (ligne.matches(".*<FR_OMS>.*</FR_OMS>.*") && ligne.matches(".*<EN_OMS>.*</EN_OMS>.*")) {
            translationList.add(ligne.replace("\u00A0", " "));
        }
    }
    br.close();

    ips = new FileInputStream(atcCodes);
    //Cp1252 --> ANSI
    ipsr = new InputStreamReader(ips, "UTF-8");
    br = new BufferedReader(ipsr);

    while ((ligne = br.readLine()) != null) {
        mActiveIngredient = pActiveIngredient.matcher(ligne);
        mActiveIngredient2 = pActiveIngredient2.matcher(ligne);
        if (mActiveIngredient.find()) {
            translatAtcList.put(mActiveIngredient.group(1), mActiveIngredient.group(2));
        }
        if (mActiveIngredient2.find()) {
            translatAtcList2.put(mActiveIngredient.group(1), mActiveIngredient.group(2));
        }
    }
    br.close();

    //Get the workbook instance for XLS file 
    XSSFWorkbook newMtcWorkbook = new XSSFWorkbook(newMTC);
    XSSFSheet newMtcSheet;
    Iterator<Row> newMtcRowIterator;
    Iterator<Cell> newMtcCellIterator;
    int newMtcCol;
    boolean newMtcColFound;
    ArrayList newMtcCodes;
    ArrayList newMtcCodes2;
    Row newMtcRow;
    Row newMtcRow2;
    Cell newMtcCell;

    //Get the sheet from the MTC workbook
    for (int i = 0; i < newMtcWorkbook.getNumberOfSheets(); i++) {
        newMtcSheet = newMtcWorkbook.getSheetAt(i);

        //Get iterator to all the rows in current MTC sheet
        newMtcRowIterator = newMtcSheet.iterator();

        //And process the file matching or throw out the file that has no equivalent
        if (newMtcSheet.getSheetName().equals("VS21_IllnessesandDisorders")) {
            newMtcCol = 0;
            newMtcColFound = false;
            newMtcCodes = new ArrayList();

            //For each row, iterate through each columns
            //Get iterator to all cells of current row
            //In MTC
            while (newMtcRowIterator.hasNext()) {
                newMtcRow = newMtcRowIterator.next();

                if (newMtcColFound == false) {
                    newMtcCellIterator = newMtcRow.cellIterator();

                    while (newMtcCellIterator.hasNext()) {
                        newMtcCell = newMtcCellIterator.next();
                        if (newMtcCell.getCellType() == 1 && newMtcCell.getStringCellValue().equals("Code")) {
                            newMtcCol = newMtcCell.getColumnIndex();
                            newMtcColFound = true;
                            break;
                        }
                    }
                } else {
                    newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                    newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim());
                }
            }

            for (int j = 0; j < newMtcCodes.size(); j++) {
                csvW.write(newMtcCodes.get(j) + ";");

                if (translatList.containsKey(newMtcCodes.get(j))) {
                    csvW.write(translatList.get(newMtcCodes.get(j)));
                } else {
                    m3 = p3.matcher((String) newMtcCodes.get(j));
                    if (m3.find() && translatList.containsKey(m3.group(1))) {
                        csvW.write(translatList.get(m3.group(1)));
                    }
                }

                /*for (int k=0; k<translationList.size(); k++) {
                String frTrad = "";
                        
                if (translationList.get(k).trim().contains("<EN_OMS>"+newMtcCodes.get(j)+"</EN_OMS>")) {
                    Pattern p = Pattern.compile("<FR_OMS>(.+?)</FR_OMS>");
                    Matcher m = p.matcher(translationList.get(k).trim());
                    if (m.find()){
                        frTrad = m.group(1);
                        translationList.remove(k);
                    }
                    csvW.write(StringUtils.capitalize(frTrad));
                }
                }*/
                csvW.write("\n");
            }
        } else if (newMtcSheet.getSheetName().equals("VS3_ActiveIngredient")) {
            newMtcCol = 0;
            newMtcColFound = false;
            newMtcCodes = new ArrayList();
            newMtcCodes2 = new ArrayList();

            //For each row, iterate through each columns
            //Get iterator to all cells of current row
            //In MTC
            while (newMtcRowIterator.hasNext()) {
                newMtcRow = newMtcRowIterator.next();
                newMtcRow2 = newMtcRow;

                if (newMtcColFound == false) {
                    newMtcCellIterator = newMtcRow.cellIterator();

                    while (newMtcCellIterator.hasNext()) {
                        newMtcCell = newMtcCellIterator.next();
                        if (newMtcCell.getCellType() == 1
                                && newMtcCell.getStringCellValue().equals("English Display Name")) {
                            newMtcCol = newMtcCell.getColumnIndex();
                            newMtcColFound = true;
                            break;
                        }
                    }
                } else {
                    newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                    newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim());
                    newMtcRow2.getCell(newMtcCol - 1, Row.CREATE_NULL_AS_BLANK)
                            .setCellType(Cell.CELL_TYPE_STRING);
                    newMtcCodes2.add(newMtcRow.getCell(newMtcCol - 1).getStringCellValue().trim());
                }
            }

            for (int j = 0; j < newMtcCodes.size(); j++) {
                csvW2.write(newMtcCodes2.get(j) + ";");
                csvW2.write(newMtcCodes.get(j) + ";");

                if (translatAtcList.containsKey(newMtcCodes.get(j))) {
                    csvW2.write(translatAtcList.get(newMtcCodes.get(j)));
                } else if (translatAtcList2.containsKey(newMtcCodes2.get(j))) {
                    csvW2.write(translatAtcList.get(newMtcCodes.get(j)));
                } else {
                    System.out.println(newMtcCodes.get(j));
                }

                csvW2.write("\n");
            }
        }
    }

    csvW.close();
    csvW2.close();
    newMTC.close();

}

From source file:connect.Bootstrap.java

public static boolean bootstrap() {
    Connection conn = DatabaseConnectionManager.connect();

    try {/*from   w w  w.j  ava  2s  . c o m*/

        int noOfLines = 0;

        ArrayList<Data> list = new ArrayList<>();
        Set<Customer> clist = new HashSet<>();
        Set<Outlet> olist = new HashSet<>();

        InputStream is = new FileInputStream(new File("./excel/SMUX - Outlet Data V1.xlsx"));

        StreamingReader reader = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
                .sheetIndex(0) // index of sheet to use (defaults to 0)
                .read(is); // InputStream or File for XLSX file (required)
        int counter = 0;
        for (Row r : reader) {
            counter++;

            //initialize a data object
            Data data = new Data(0, 0, "NULL", 0, "NULL", "", "Outlet", 0, 0, "", "", 0, 0, 0);

            if (noOfLines > 0) {
                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = r.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    int cellIndex = cell.getColumnIndex();

                    switch (cellIndex) {
                    case 0: //customer id
                        int custid = (int) cell.getNumericCellValue();
                        data.setCustomerId(custid);
                        break;
                    case 1: //age
                        try {
                            int age = (int) cell.getNumericCellValue();
                            data.setAge(age);
                        } catch (Exception e) {
                            //leave it as 0
                        }

                        break;
                    case 2: //gender
                        String gender = cell.getStringCellValue();
                        data.setGender(gender);
                        //System.out.println("case 2");
                        break;
                    case 3: //transact id
                        data.setTransactId((int) cell.getNumericCellValue());
                        break;
                    case 4: //transact date
                        data.setTransactDate(df.format(cell.getDateCellValue()));
                        break;
                    case 5: //transact time
                        time.setTime(cell.getDateCellValue());
                        data.setTransactTime(df2.format(time.getTime()));
                        break;
                    case 6: //outlet
                        data.setOutlet(cell.getStringCellValue());
                        break;
                    case 7: //outlet district
                        data.setOutletDistrict((int) cell.getNumericCellValue());
                        break;
                    case 8: //transact details id
                        data.setTransactDetailsId((int) cell.getNumericCellValue());
                        break;
                    case 9: //item
                        //try {
                        data.setItem(cell.getStringCellValue());
                        break;
                    case 10: //item description
                        data.setItemDesc(cell.getStringCellValue());
                        break;
                    case 11: //quantity
                        data.setQuantity((int) cell.getNumericCellValue());
                        break;
                    case 12: //price
                        data.setPrice(cell.getNumericCellValue());
                        break;
                    case 13: //spending
                        double spending = cell.getNumericCellValue();
                        data.setSpending(spending);
                        break;
                    default:
                    }
                }
                list.add(data);
            }

            noOfLines++;
            if (noOfLines == 32740) {

                //establish connection, sql, execute sql
                try {
                    String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    PreparedStatement pstmt = null;
                    //upload by batches
                    conn.setAutoCommit(false);
                    //total 556581

                    pstmt = conn.prepareStatement(sql);
                    //loop through user list
                    for (Data d : list) {
                        pstmt.setInt(1, d.getCustomerId());
                        pstmt.setInt(2, d.getAge());
                        pstmt.setString(3, d.getGender());
                        pstmt.setInt(4, d.getTransactId());
                        pstmt.setString(5, d.getTransactDate());
                        pstmt.setString(6, d.getTransactTime());
                        pstmt.setString(7, d.getOutlet());
                        pstmt.setInt(8, d.getOutletDistrict());
                        pstmt.setInt(9, d.getTransactDetailsId());
                        pstmt.setString(10, d.getItem());
                        pstmt.setString(11, d.getItemDesc());
                        pstmt.setInt(12, d.getQuantity());
                        pstmt.setDouble(13, d.getPrice());
                        pstmt.setDouble(14, d.getSpending());
                        pstmt.addBatch();
                    }

                    //System.out.println(pstmt);
                    pstmt.executeBatch();
                    pstmt.close();
                    conn.commit();
                    System.out.println("current counter = " + counter);
                } catch (SQLException k) {
                    k.printStackTrace();
                }

                noOfLines = 1;
                //System.out.println("batch submitted");
            } else if (counter > 556560 && counter <= 556580) {
                try {
                    String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    PreparedStatement pstmt = null;
                    //upload by batches
                    conn.setAutoCommit(false);
                    //total 556581

                    pstmt = conn.prepareStatement(sql);
                    //loop through user list
                    for (Data d : list) {
                        pstmt.setInt(1, d.getCustomerId());
                        pstmt.setInt(2, d.getAge());
                        pstmt.setString(3, d.getGender());
                        pstmt.setInt(4, d.getTransactId());
                        pstmt.setString(5, d.getTransactDate());
                        pstmt.setString(6, d.getTransactTime());
                        pstmt.setString(7, d.getOutlet());
                        pstmt.setInt(8, d.getOutletDistrict());
                        pstmt.setInt(9, d.getTransactDetailsId());
                        pstmt.setString(10, d.getItem());
                        pstmt.setString(11, d.getItemDesc());
                        pstmt.setInt(12, d.getQuantity());
                        pstmt.setDouble(13, d.getPrice());
                        pstmt.setDouble(14, d.getSpending());
                        //pstmt.addBatch();
                    }
                    //System.out.println(pstmt);
                    pstmt.executeUpdate();
                    //conn.commit();
                    System.out.println("current counter = " + counter);
                } catch (SQLException k) {
                    k.printStackTrace();
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    return true;
}

From source file:connect.LoadCategory.java

public static boolean loadCategory() {
    Connection conn = DatabaseConnectionManager.connect();
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    ResultSet rs = null;/*  w ww.  j a  v a2 s .  c o  m*/

    try {
        int noOfLines = 0;

        HashMap<String, FoodCategory> foodCategoryHashMap = new HashMap<>();
        InputStream is = new FileInputStream(new File("./excel/category.xlsx"));

        StreamingReader reader = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
                .sheetIndex(0) // index of sheet to use (defaults to 0)
                .read(is); // InputStream or File for XLSX file (required)
        int counter = 0;
        for (Row r : reader) {
            counter++;

            FoodCategory foodCategory = new FoodCategory("", "", 0.0, "", "", "", "");
            if (noOfLines > 0) {

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = r.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    int cellIndex = cell.getColumnIndex();
                    switch (cellIndex) {
                    case 0:
                        foodCategory.setItem_id(cell.getStringCellValue());
                        break;
                    case 1:
                        foodCategory.setItemDesc(cell.getStringCellValue());
                        break;
                    case 2:
                        foodCategory.setCourse(cell.getStringCellValue());
                        break;
                    case 3:
                        foodCategory.setOrigin(cell.getStringCellValue());
                        break;
                    case 4:
                        foodCategory.setTags(cell.getStringCellValue());
                        break;
                    case 5:
                        foodCategory.setHotcold(cell.getStringCellValue());
                        break;
                    default:
                    }
                }
                foodCategoryHashMap.put(foodCategory.getItem_id() + "|" + foodCategory.getItemDesc(),
                        foodCategory);
            }
            noOfLines++;
        }

        pstmt = conn.prepareStatement(SELECTUNIQUECATEGORY);
        rs = pstmt.executeQuery();

        ArrayList<FoodCategory> foodCategoryList = new ArrayList<>();
        while (rs.next()) {
            String item = rs.getString(1);
            String itemdesc = rs.getString(2);
            double price = rs.getDouble(3);
            foodCategoryList.add(new FoodCategory(item, itemdesc, price));
        }

        conn.setAutoCommit(false);
        pstmt2 = conn.prepareStatement(INSERTCATEGORYSQL);

        for (FoodCategory foodCategory : foodCategoryList) {
            pstmt2.setString(1, foodCategory.getItem_id());
            pstmt2.setString(2, foodCategory.getItemDesc());
            pstmt2.setDouble(3, foodCategory.getPrice());

            String key = foodCategory.getItem_id() + "|" + foodCategory.getItemDesc();
            FoodCategory mapFoodCategory = foodCategoryHashMap.get(key);
            if (mapFoodCategory == null) {
                continue;
            }

            pstmt2.setString(4, mapFoodCategory.getCourse());
            pstmt2.setString(5, mapFoodCategory.getOrigin());
            pstmt2.setString(6, mapFoodCategory.getTags());
            pstmt2.setString(7, mapFoodCategory.getHotcold());
            pstmt2.addBatch();
        }
        pstmt2.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt2 != null) {
            try {
                pstmt2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    return true;
}

From source file:controller.UploadExcelStudentsFile.java

public String readFromExcel(String path, MyPerson p) {
    String messages = "";
    String ColumnsMesages = "";

    try {//from ww  w.ja  va2 s  . c o m
        FileInputStream file;
        file = new FileInputStream(new File(path));

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

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

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        int rowCount = 0;
        boolean isEverythingIsOK = true;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            rowCount++;
            System.out.println("rcount:" + rowCount);

            if (rowCount > 1) {
                //For each row, iterate through all the columns
                int Code = 0;
                String Fname = "", Lname = "", Email = "";
                String password = "";
                int userType = 0;
                int level = 0;
                String gender = "";
                Iterator<Cell> cellIterator = row.cellIterator();
                int ColumnCount = 0;
                int rowCountMesages = rowCount - 1;

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

                    //Check the cell type and format accordingly
                    System.out.println("ccount:" + ColumnCount);

                    /*  switch (cell.getCellType()) {
                     case Cell.CELL_TYPE_NUMERIC:
                     System.out.println(cell.getNumericCellValue() + "");
                     System.out.println("numeric type case:" + Cell.CELL_TYPE_NUMERIC);// numeric type case:0
                     System.out.println("type:" + cell.getCellType());//type:0
                     break;
                     case Cell.CELL_TYPE_STRING:
                     System.out.println(cell.getStringCellValue());
                            
                     System.out.print("string type case:" + Cell.CELL_TYPE_STRING + "");//string type case:0
                     System.out.println("type:" + cell.getCellType());//type:1
                            
                     break;
                     }*/
                    if (ColumnCount == 1) {
                        if (cell.getCellType() == 0) {
                            Code = (int) cell.getNumericCellValue();
                            password = String.valueOf(Code);
                            System.out.println("Code:" + Code);
                            System.out.println("pass:" + password);

                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'>First Column is Code must be an integer please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 2) {
                        if (cell.getCellType() == 1) {

                            Fname = cell.getStringCellValue();
                            System.out.println("Fname:" + Fname);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += " <font color='red'>Column number 2  is Fname  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 3) {
                        if (cell.getCellType() == 1) {

                            Lname = cell.getStringCellValue();
                            System.out.println("Lname:" + Lname);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'> Column number 3 is Lname  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br>";
                        }
                    } /*else if(ColumnCount==4){
                      String Pass=cell.getStringCellValue();
                      System.out.println("Pass:"+Pass);
                              
                      }*/ else if (ColumnCount == 4) {
                        if (cell.getCellType() == 1) {

                            Email = cell.getStringCellValue();
                            System.out.println("Email:" + Email);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += " <font color='red'>Column number 4  is Email  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 5) {
                        if (cell.getCellType() == 1) {

                            gender = cell.getStringCellValue();
                            System.out.println("gender:" + gender);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'> Column number 5  is gender  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 6) {
                        if (cell.getCellType() == 0) {

                            userType = (int) cell.getNumericCellValue();
                            System.out.println("userType:" + userType);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += " <font color='red'> Column  number 6 is userType  must be an integer please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 7) {
                        if (cell.getCellType() == 0) {

                            level = (int) cell.getNumericCellValue();
                            System.out.println("level:" + level);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'> Column number 7 is level  must be an integer please  in student number ("
                                    + rowCountMesages + ")</font><br/>";

                        }
                    }
                } //end of celIterator

                int rowAffected = 0;
                if (isEverythingIsOK) {
                    try {
                        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss a");
                        String RegistrationDate = sdf1.format(new Date());
                        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddhhmmss");
                        String MyUniversityCodeString = sdf2.format(new Date()) + p.getFaculityID() + Code;

                        rowAffected = p.RegisterUser(Code, Fname, Lname, Email, password, userType, 1, gender,
                                p.getFaculityID(), p.getUniversityID(), RegistrationDate, 1,
                                MyUniversityCodeString);

                        if (rowAffected > 0) {
                            //response.sendRedirect("MyAccount.jsp?page=CourseViewPOSTContents&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+"");
                            //response.sendRedirect("MyAccount.jsp?page=viewPost&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+"");
                            messages += "<font color='blue'>Student(" + rowCountMesages
                                    + ")was Adding Successfully  ^_^</font>" + "<br/>";
                            System.out.println(
                                    "<script type='text/javascript' > alert('Student was Adding Successfully  ^_^ ');history.back();</script>");
                        } else {
                            messages += "<font color='red'> Student(" + rowCountMesages
                                    + ")was Adding Failed  ^_^</font>" + "<br/>";

                            System.out.println(
                                    "<script type='text/javascript' > alert('Student was Failed ^_^ ');history.back();</script>");
                        }

                    } catch (Exception ex) {
                        System.err.println("Add Students Error" + ex.getMessage());
                        messages += "<font color='red'>Adding Students Error" + ex.getMessage()
                                + "</font><br/>";
                        messages += "<center><a href='index.jsp' >Home</a></center>";
                    }
                } //end of if IsEverythingIsOk Or Not
                System.out.println("");

            } //end of if this is not first row
        } //end of while rowIterator
        file.close();
    } //end of try 
    catch (Exception e) {
        e.printStackTrace();
        messages += "<font color='red'>" + e.getMessage() + "</font><br/>";
    }
    messages += ColumnsMesages;

    return messages;
}

From source file:controller.VisitasController.java

public void autoSizeColumns(Workbook workbook) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {
            Row row = sheet.getRow(8);

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                sheet.autoSizeColumn(columnIndex);
            }/*from   w w  w . ja  v  a  2  s .com*/

        }
    }
}

From source file:coverageqc.data.DoNotCall.java

public static DoNotCall populate(Row xslxHeadingRow, Row xslxDataRow, Integer calltype) {
    DoNotCall donotcall = new DoNotCall();
    int columnNumber;
    int cellIndex;
    String[] headerArray;//from   www  .  ja  va 2s .  c  o m
    HashMap<String, Integer> headings = new HashMap<String, Integer>();

    columnNumber = xslxHeadingRow.getLastCellNum();
    headerArray = new String[columnNumber];

    Iterator<Cell> cellIterator = xslxHeadingRow.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        cellIndex = cell.getColumnIndex();
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            headerArray[cellIndex] = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            headerArray[cellIndex] = Double.toString(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            headerArray[cellIndex] = cell.getStringCellValue();
            break;
        default:
            headerArray[cellIndex] = "";
        }

    } //end while celliterator

    for (int x = 0; x < headerArray.length; x++) {
        headings.put(headerArray[x].substring(0, headerArray[x].indexOf("_")), x);
    }

    //String[] dataArray = xslxDataLine.split("\t");
    if (xslxDataRow.getCell(headings.get("HGVSc")) != null) {
        donotcall.hgvsc = xslxDataRow.getCell(headings.get("HGVSc").intValue()).getStringCellValue();

    }
    //donotcall.hgvsp = xslxDataRow.getCell(headings.get("HGVSp").intValue()).getStringCellValue();
    if (xslxDataRow.getCell(headings.get("ENSP")) != null) {
        donotcall.ensp = xslxDataRow.getCell(headings.get("ENSP").intValue()).getStringCellValue();
    }
    if (xslxDataRow.getCell(headings.get("Transcript")) != null) {
        donotcall.transcript = xslxDataRow.getCell(headings.get("Transcript").intValue()).getStringCellValue();
    } else {
        System.out.println(
                "Transcript_27 column entry is negative!  This is essential to do not call! Do not call list needs to be fixed!  Crashing to prevent abnormal behavior!");
        System.exit(1);
    }
    donotcall.coordinate = (long) xslxDataRow.getCell(headings.get("Coordinate").intValue())
            .getNumericCellValue();

    // CallType is the page of the xlsx :
    // 1 => Always_Not_Real
    // 2 => Not_Real_When_Percentage_Low
    // 3 => Undetermined_Importance
    if (calltype == 1) {
        donotcall.callType = "Don't call, always";
    } else if (calltype == 2) {
        donotcall.callType = "If percentage low, don't call";

    } else {
        donotcall.callType = "On lab list, Unknown significance";
    }

    return donotcall;
}

From source file:data.control.dataSheet.java

public ArrayList<Patient> getPatients() {
    ArrayList<XSSFRow> theRows;
    ArrayList<Patient> thePatients = new ArrayList();
    boolean firstRowSkipped = false;

    connect();//w  w  w.  j a  v a  2 s.co  m
    theRows = fetchRows();

    // looping through the rows
    Iterator<XSSFRow> rowIterator = theRows.iterator();

    while (rowIterator.hasNext()) {

        // reading the row
        Row aRow = rowIterator.next();

        if (!firstRowSkipped) {
            firstRowSkipped = true;
            continue;
        }

        Patient aPatient = new Patient();

        // loading the cells
        Iterator<Cell> cellIterator = aRow.cellIterator();

        // looping through the cells
        while (cellIterator.hasNext()) {
            // reading the cell
            Cell cell = cellIterator.next();

            if (cell != null) {
                switch (cell.getColumnIndex()) {

                case 0:
                    // ID
                    aPatient.setID((int) cell.getNumericCellValue());
                    break;
                case 1:
                    // Name
                    aPatient.setName(cell.getStringCellValue());
                    break;
                case 2: // heart rate
                case 3: // heart rate
                case 4: // heart rate
                case 5: // heart rate
                case 6: // heart rate
                    //aPatient.addHeartRate(cell.getNumericCellValue());
                    break;
                case 7: // tempreature
                case 8: // tempreature
                case 9: // tempreature
                case 10:// tempreature
                case 11:// tempreature
                    //aPatient.addTempreature(cell.getNumericCellValue());
                    break;
                case 12:
                    // blood_type
                    aPatient.setBloodType(cell.getStringCellValue());

                    break;
                case 13:
                    // sex
                    aPatient.setSex(cell.getStringCellValue());
                    break;
                case 14:
                    // age
                    aPatient.setAge((int) cell.getNumericCellValue());
                    break;
                case 15:
                    // date_added
                    aPatient.setDateAdded(cell.getDateCellValue());
                    break;
                case 16:
                    // last_updated
                    aPatient.setLastUpdated(cell.getDateCellValue());
                    break;
                case 17:
                    // last_alarmed
                    aPatient.setLastAlarm(cell.getDateCellValue());
                default:
                    break;
                }
            }
        }
        // adding patient to the collection
        if (aPatient.getName() != null) {
            thePatients.add(aPatient);
        }
        //aPatient.printAll();
    }

    //closeConnection();
    return thePatients;
}

From source file:de.escnet.ExcelTable.java

License:Open Source License

public ExcelTable(String excel, String sheetName) throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook(excel);
    evaluator = wb.getCreationHelper().createFormulaEvaluator();
    theme = wb.getTheme();//from w  w  w  . j a v  a2s.c  o m
    sheet = sheetName == null ? wb.getSheetAt(0) : wb.getSheet(sheetName);

    for (Iterator rowIt = sheet.rowIterator(); rowIt.hasNext();) {
        Row row = (Row) rowIt.next();
        for (Iterator cellIt = row.cellIterator(); cellIt.hasNext();) {
            XSSFCell cell = (XSSFCell) cellIt.next();

            int rowIndex = cell.getRowIndex();
            rowMin = Math.min(rowMin, rowIndex);
            rowMax = Math.max(rowMax, rowIndex);

            int colIndex = cell.getColumnIndex();
            colMin = Math.min(colMin, colIndex);
            colMax = Math.max(colMax, colIndex);
        }
    }
}

From source file:de.hk.exceldemo.business.service.ExcelAdapterTest.java

@Test
public void addCell() {
    XSSFWorkbook wb1 = new XSSFWorkbook();
    Sheet sheet1 = wb1.createSheet("testSheet1");
    Row newRow1 = sheet1.createRow(0);/*from www  . ja va  2s . c om*/
    Cell cell1 = newRow1.createCell(0);
    cell1.setCellValue("jawoi");

    XSSFWorkbook wb2 = new XSSFWorkbook();
    Sheet sheet2 = wb2.createSheet("testSheet2");
    Row newRow2 = sheet2.createRow(0);

    cut.addCell(newRow2, 0, cell1);

    Iterator<Cell> celltIt = newRow2.cellIterator();
    assertEquals("jawoi", celltIt.next().getStringCellValue());
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.exporter.ExcelSheetTestBase.java

License:Open Source License

/**
 * Verifies a single line of content/*w  ww.  j  av  a 2  s  .  c  o m*/
 * 
 * @param row
 * @param expContent
 */
protected void verifyRowContent(Row row, Object... expContent) {
    Iterator<Cell> iter = row.cellIterator();
    for (Object element : expContent) {
        if (iter.hasNext()) {
            String nextElement = iter.next().toString();
            assertEquals(element, nextElement);
        } else {
            fail("Received more entries than expected");
        }
    }
}