Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

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   www  .j a  v  a2 s  .  c  o m*/
    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  ww.j  a  va 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. ja v  a  2 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.DAOData.java

public Plan readPlan() {
    ArrayList<Course> courses = new ArrayList<>();
    //takes the sheet you ask for 
    XSSFSheet sheet = workbook.getSheet("PLAN");

    //for to go over the sheet info - like the rows 
    for (Row row : sheet) {
        String code = null;// www  . ja v  a2  s . co  m
        String name = null;
        double credits = 0;
        for (Cell cell : row) {
            if (row.getRowNum() != 0) {
                switch (cell.getColumnIndex()) {
                case 0:
                    name = cell.getStringCellValue();
                    break;
                case 1:
                    code = cell.getStringCellValue();
                    break;
                case 2:
                    credits = cell.getNumericCellValue();
                    break;
                }
            }

        }
        if (code != null) {
            Course course = new Course(code, name, credits);

            courses.add(course);

            code = null;
            name = null;
            credits = 0;
        }

    }
    Plan plan = new Plan(410, 2010, courses);
    return plan;
}

From source file:controller.DAOData.java

public ArrayList<Employee> readProfessors() {
    ArrayList<Employee> employees = new ArrayList<>();
    //takes the sheet you ask for 
    XSSFSheet sheet = workbook.getSheet("PROFESORES");

    //for to go over the sheet info - like the rows 
    for (Row row : sheet) {
        String id = null;/*from  w  ww  . j  a v a  2  s.c om*/
        String name = null;
        String email = null;
        String phone = null;

        for (Cell cell : row) {
            if (row.getRowNum() != 0) {
                switch (cell.getColumnIndex()) {
                case 0:
                    id = cell.getStringCellValue();
                    break;
                case 1:
                    name = cell.getStringCellValue();
                    break;
                case 2:
                    email = cell.getStringCellValue();
                    break;
                case 3:
                    phone = cell.getStringCellValue();
                    break;
                }
            }

        }
        if (id != null) {
            employees.add(new Employee(EEmployeeRol.PROFESSOR, id, name, email, phone));

            id = null;
            name = null;
            email = null;
            phone = null;
        }

    }

    return employees;
}

From source file:controller.DAOData.java

public ArrayList<Object> readGroups() {
    ArrayList<Object> groups = new ArrayList();
    XSSFSheet sheet = workbook.getSheet("OFERTA");

    //for to go over the sheet info - like the rows 
    for (Row row : sheet) {
        String period = null;/*from  w w  w  .java  2  s .c  o  m*/
        Course course = null;
        int numberGroup = 0;
        Employee employee = null;
        ArrayList<Schedule> schedules = new ArrayList();
        String scheduleS = null;
        String classroom = null;

        for (Cell cell : row) {
            if (row.getRowNum() != 0) {
                Schedule schedule = new Schedule();
                switch (cell.getColumnIndex()) {
                case 0:
                    period = cell.getStringCellValue();
                    break;
                case 1:
                    course = School.getInstance().selectCourse(cell.getStringCellValue());
                    break;
                case 2:
                    numberGroup = (int) cell.getNumericCellValue();
                    break;
                case 3:
                    employee = School.getInstance().findEmployee(cell.getStringCellValue());
                    break;
                case 4:
                    scheduleS = cell.getStringCellValue();
                    break;
                case 5:
                    classroom = cell.getStringCellValue();
                    break;

                }
            }

        }
        if (period != null) {
            schedules = identifySchedules(scheduleS, classroom);
            groups.add(new Group(numberGroup, period, true, employee, schedules, course));

            numberGroup = 0;
            course = null;
            period = null;
            employee = null;
            schedules = null;
        }

    }

    return groups;

}

From source file:controller.DAORequest.java

public ArrayList<Object> readRequests() {
    ArrayList<Object> requests = new ArrayList();
    ArrayList<Resolution> resolutions = readResolutions();
    XSSFSheet sheet = workbook.getSheetAt(0);

    for (Row row : sheet) {
        System.out.println("1");
        Date date = null;/*from  w  ww. j  av a  2 s. com*/
        Student affected;
        String carnet = null;
        String name = null;
        String email = null;
        String celStu = null;
        Group group;
        String period = null;
        String course = null;
        int numberGroup = 0;
        EInconsistencie einconsistencie;
        String inconsistencie = null;
        String description = null;
        Person requester;
        String idReq = null;
        String nameReq = null;
        ERequestState reqState;
        String sreqState = null;
        int numRes = 0;
        for (Cell cell : row) {
            if (row.getRowNum() != 0) {

                switch (cell.getColumnIndex()) {
                case 0:
                    date = row.getCell(0).getDateCellValue();
                    break;
                case 1:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        carnet = Integer.toString((int) cell.getNumericCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        carnet = cell.getStringCellValue();
                    break;
                case 2: //es el nombre del estudiante
                    name = cell.getStringCellValue();
                    break;
                case 3:
                    email = cell.getStringCellValue();
                    break;
                case 4:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        celStu = Integer.toString((int) cell.getNumericCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        celStu = cell.getStringCellValue();
                    break;
                case 5:
                    period = cell.getStringCellValue();
                    break;
                case 6:
                    course = cell.getStringCellValue();
                    break;
                case 7:
                    numberGroup = (int) cell.getNumericCellValue();
                    break;
                case 8:
                    inconsistencie = cell.getStringCellValue();
                    break;
                case 9:
                    description = cell.getStringCellValue();
                    break;
                case 10:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        idReq = Integer.toString((int) cell.getNumericCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        idReq = cell.getStringCellValue();
                    break;
                case 11:
                    nameReq = cell.getStringCellValue();
                    break;
                case 12:
                    sreqState = cell.getStringCellValue();
                    break;
                case 13:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        numRes = (int) cell.getNumericCellValue();
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        numRes = Integer.parseInt(cell.getStringCellValue());
                    break;
                }
            }

        }

        if (carnet != null) {
            affected = new Student(carnet, name, email, celStu);
            requester = new Person(idReq, nameReq, null, null);
            group = School.getInstance().selectGroup(period, numberGroup, course);
            einconsistencie = identifyEInconsistencie(inconsistencie);
            if (sreqState == null) {
                sreqState = "PENDIENTE";
            }
            reqState = identifyEReqState(sreqState);

            Request request = new Request(date, description, einconsistencie, reqState, affected, requester,
                    group);
            request.setRequestState(reqState);
            requests.add(request);
            if (numRes != 0) {
                for (Resolution r : resolutions) {
                    if (r.getId() == numRes)
                        request.setResolution(r);
                    System.out.println("linquea numRes: " + numRes + " con ReqId:" + request.getId());
                }
            }
        }

    }

    return requests;
}

From source file:controller.DAORequest.java

private ArrayList<Resolution> readResolutions() {
    ArrayList<Resolution> resolutions = new ArrayList();
    try {//from   w  w w  . j  a  va2 s . c  om
        FileInputStream fis = new FileInputStream(new File("src//files//DatosResolucion.xlsx"));
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {
            int id = 0;
            String attention = null;
            String title = null;
            String intro = null;
            String result = null;
            String resolve = null;
            String notify = null;
            String considerations = null;
            for (Cell cell : row) {
                if (row.getRowNum() != 0) {
                    switch (cell.getColumnIndex()) {
                    case 0:
                        id = (int) cell.getNumericCellValue();
                        break;
                    case 1:
                        attention = cell.getStringCellValue();
                        break;
                    case 2:
                        title = cell.getStringCellValue();
                        break;
                    case 3:
                        intro = cell.getStringCellValue();
                        break;
                    case 4:
                        result = cell.getStringCellValue();
                        break;
                    case 5:
                        resolve = cell.getStringCellValue();
                        break;
                    case 6:
                        notify = cell.getStringCellValue();
                        break;
                    case 7:
                        considerations = cell.getStringCellValue();
                        break;
                    }
                }
            }
            if (id != 0) {
                System.out.println("Resolution: [id: " + id + " attention: " + attention + "\ntitle: " + title
                        + " \nintro: " + intro + " \nresult: " + result + " \nresolve: " + resolve
                        + " \nnotify: " + notify + " \nconsiderations: " + considerations + "\n]");
                resolutions.add(
                        new Resolution(id, attention, title, intro, result, resolve, notify, considerations));
            }
        }

    }

    catch (FileNotFoundException e) {
        System.out.println("No hay archivo que cargar de Resolutions");
    } catch (IOException ex) {
        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    }

    return resolutions;

}

From source file:Controller.ThreadExcelImport.java

@Override
public void run() {

    //******//from  www.  ja v  a  2  s  . c  o m
    // CRIA STREAM DAS PLANILHAS
    // *******************

    // stream planilha 1
    InputStream stream1 = null;
    try {
        stream1 = new FileInputStream(new File(srcFileP1));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook1 = 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)
            .open(stream1);

    // stream planilha 2
    InputStream stream2 = null;
    try {
        stream2 = new FileInputStream(new File(srcFileP2));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook2 = 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)
            .open(stream2);

    //******
    // VERIFICA OS CABECALHOS
    // *******************

    // cabealhos da planilha 1
    Sheet sheet1 = null;
    sheet1 = workbook1.getSheetAt(0);

    // Pega de acordo com o cabealho as opes
    for (Row r : sheet1) {
        if (r.getRowNum() > 0)
            break;
        for (Integer i = 0; i < headerP1.size(); i++) {
            for (Cell c : r) {
                if (c.getStringCellValue().toLowerCase()
                        .equals(headerP1.get(i).getColumnName().toLowerCase())) {
                    // Adiciona o numero da coluna ao header
                    headerP1.get(i).setColumnNumber(c.getColumnIndex());
                    break;
                }
            }

            if (headerP1.get(i).getColumnNumber() == null) {
                // Alguma coluna do template est ausente
                JOptionPane.showMessageDialog(null, "A coluna " + headerP1.get(i).getColumnName().toLowerCase()
                        + " do template no existe como cabealho na planilha 1");
                System.exit(0);
            }

        }
    }
    // cabealhos da planilha 2
    Sheet sheet2 = null;
    sheet2 = workbook2.getSheetAt(0);

    // Pega de acordo com o cabealho as opes
    for (Row r : sheet2) {
        if (r.getRowNum() > 0)
            break;
        for (Integer i = 0; i < headerP2.size(); i++) {
            for (Cell c : r) {
                if (c.getStringCellValue().toLowerCase()
                        .equals(headerP2.get(i).getColumnName().toLowerCase())) {
                    // Adiciona o numero da coluna ao header
                    headerP2.get(i).setColumnNumber(c.getColumnIndex());
                    break;
                }
            }

            if (headerP2.get(i).getColumnNumber() == null) {
                // Alguma coluna do template est ausente
                JOptionPane.showMessageDialog(null, "A coluna " + headerP2.get(i).getColumnName().toLowerCase()
                        + " do template no existe como cabealho na planilha 2");
                System.exit(0);
            }

        }
    }

    //******
    // GRAVA EM MEMRIA A PLANILHA 2 PARA EVITAR O REABRIMENTO DA MESMA A CADA ITERAO DA PLANILHA 1
    // *******************
    stream2 = null;
    try {
        stream2 = new FileInputStream(new File(srcFileP2));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook2 = 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)
            .open(stream2);

    sheet2 = null;
    sheet2 = workbook2.getSheetAt(0);

    for (Row rowP2 : sheet2) {

        if (rowP2.getRowNum() > 0) {
            InterfaceMigracao objInterfaceP2 = Factory.getInstance(templateName);

            // calcula o hash
            String hashChaveP2 = "";
            for (String chaveP2 : colunaChave) {
                Integer columIndex = -1;
                for (Header he2 : headerP2) {
                    if (he2.getColumnName().equals(chaveP2)) {
                        columIndex = he2.getColumnNumber();
                        break;
                    }
                }

                if (columIndex > -1) {
                    Cell cell = null;
                    cell = rowP2.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                    // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                    hashChaveP2 = DigestUtils
                            .sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP2);
                }

            }

            for (Header he2 : headerP2) {

                Cell cell = rowP2.getCell(he2.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                objInterfaceP2.setString(he2.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
                objInterfaceP2.setExcelRowNumber((rowP2.getRowNum() + 1));
                //System.out.println("Novo loop HeaderP2 da linhaP2 " + String.valueOf(rowP2.getRowNum()) + " coluna " + he2.getColumnName() );
            }

            if (hashChaveP2.equals("")) {
                JOptionPane.showMessageDialog(null, "A linha " + String.valueOf((rowP2.getRowNum() + 1))
                        + " da planilha 2 tem as colunas chaves nula");
                System.exit(0);
            } else
                listaP2.put(hashChaveP2, objInterfaceP2);

        }
    }

    // limpa da memoria a workbook2
    try {
        if (workbook2 != null)
            workbook2.close();
    } catch (IOException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }

    // limpa da memoria o stream com workbook2
    if (stream2 != null)
        try {
            stream2.close();
        } catch (IOException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }

    //******
    // FAZ A VALIDAO
    // OBSERVE QUE POR TER FEITO O FOREACH NOS PLANILHAS SE TORNA NECESS?RIO RECRIAR O STREAMING
    // *******************

    // Executa o loop nas linhas da planilha

    stream1 = null;
    try {
        stream1 = new FileInputStream(new File(srcFileP1));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook1 = 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)
            .open(stream1);

    sheet1 = null;
    sheet1 = workbook1.getSheetAt(0);

    InterfaceMigracao objInterfaceP1 = null;

    for (Row rowP1 : sheet1) {

        // Pega o hash dos campos chaves da planilha 1 a fim de localizar na planilha 1
        String hashChaveP1 = "";
        for (String chaveP1 : colunaChave) {
            Integer columIndex = -1;
            for (Header he1 : headerP1) {
                if (he1.getColumnName().equals(chaveP1)) {
                    columIndex = he1.getColumnNumber();
                    break;
                }
            }

            if (columIndex > -1) {
                Cell cell = null;
                cell = rowP1.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1);
            }

        }

        objInterfaceP1 = Factory.getInstance(templateName);
        // objInterfaceP2 = Factory.getInstance(templateName);

        objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
        Notify notify = new Notify();

        if (hashChaveP1.equals(""))
            notify.setLocalizadoP1(false);
        else {
            notify.setLocalizadoP1(true);
            //seta o numero da linha no excel

            // Preenche o objeto de interface da planilha 1 com seus respectivos dados
            for (Header he1 : headerP1) {

                Cell cell = null;
                cell = rowP1.getCell(he1.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                objInterfaceP1.setString(he1.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
            }

            boolean p2Localizado = false;

            // Preenche o objeto de interface da planilha 2 com seus respectivos dados
            if (rowP1.getRowNum() > 0) {
                InterfaceMigracao objInterfaceMigracaoP2 = listaP2.get(hashChaveP1);
                if (objInterfaceMigracaoP2 != null) {
                    p2Localizado = true;
                    notify.setEntidadeP2(objInterfaceMigracaoP2);
                }

            }
            notify.setLocalizadoP2(p2Localizado);

        }

        isRunning = true;

        objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
        notify.setEntidadeP1(objInterfaceP1);
        notify.setTotalRow((sheet1.getLastRowNum() + 1));

        notify.setRunning(isRunning);
        notify.setHeaderP1(headerP1);
        notify.setHeaderP2(headerP2);

        setChanged();
        notifyObservers(notify);

    }

    isRunning = false;
    // Notifica os observadores de que a execuo terminou
    Notify notify = new Notify();
    notify.setRunning(false);
    setChanged();
    notifyObservers(notify);
    listaP2 = null;

}

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);/* w ww .j a  v a2  s  .com*/

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                sheet.autoSizeColumn(columnIndex);
            }

        }
    }
}