Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:br.unesp.rc.desafio.utils.Spreadsheet.java

public static ArrayList<String> ReadXlsxSpreadsheet(File spreadsheet) {

    /*//  w w  w  .  j  a  v a 2s  . c  o  m
    Constructing File
    */
    ArrayList values = new ArrayList<String>();
    FileInputStream inputStr = null;
    try {
        inputStr = new FileInputStream(spreadsheet);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    Workbook currentSpreadsheetFile = null;

    try {
        XSSFRow row1;
        currentSpreadsheetFile = new XSSFWorkbook(inputStr);
    } catch (IOException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    Sheet sheet = currentSpreadsheetFile.getSheetAt(0);
    Iterator<Row> rowItr = sheet.rowIterator();

    while (rowItr.hasNext()) {
        row1 = (XSSFRow) rowItr.next();
        Iterator<Cell> cellIterator = row1.cellIterator();
        while (cellIterator.hasNext()) {
            String cellValue = "";
            Cell cell = cellIterator.next();

            switch (cell.getCellTypeEnum()) {
            default:
                cellValue = cell.getCellFormula();
                cell.setCellType(CellType.STRING);
                cell.setCellValue(cellValue);
                break;
            case BLANK:
                break;
            case STRING:
                break;
            }
            values.add(cell.getStringCellValue());
            System.out.print(cell.getStringCellValue() + " \t\t ");
        }
        System.out.println();
    }
    try {
        inputStr.close();
    } catch (IOException ex) {
        Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex);
    }

    return values;
}

From source file:business.SongExcelParser.java

public ArrayList<SongContainer> fileToSongFiles(File fileSelected) {
    if (fileSelected instanceof File) {
        try {/*from   w w w . j  a va  2  s. c o  m*/
            Workbook wb = WorkbookFactory.create(fileSelected);
            Sheet sheet = wb.getSheetAt(0);
            return sheetToSongFiles(sheet);
        } catch (Exception e) {
            return new ArrayList<>();
        }
    } else {
        return new ArrayList<>();
    }
}

From source file:business.SongExcelParser.java

public ArrayList<Song> fileToCDSongs(File fileSelected) {
    if (fileSelected instanceof File) {
        try {/*from w  ww.j  av  a  2s  .co  m*/
            Workbook wb = WorkbookFactory.create(fileSelected);
            Sheet sheet = wb.getSheetAt(0);
            return sheetToCDSongs(sheet);
        } catch (Exception e) {
            return new ArrayList<>();
        }
    } else {
        return new ArrayList<>();
    }
}

From source file:Categorization.CategoriesLoader.java

License:Open Source License

public static void load() throws FileNotFoundException, IOException, InvalidFormatException {
    InputStream inp;//from  w  w w . j  ava  2  s.co m
    inp = new FileInputStream(fileName);
    Workbook wb = WorkbookFactory.create(inp);

    //        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
    for (int i = 0; i < 2; i++) {
        echoAsCSV(wb.getSheetAt(i));
    }
    inp.close();
}

From source file:cdiscisa.StreamUtil.java

private static Map<String, String> llenarAbreviaturas(Workbook wbAbrev) {
        Map<String, String> abreviaturas = new HashMap<>();
        Sheet wbListaSheet = wbAbrev.getSheetAt(0);
        Iterator<Row> rowIterator = wbListaSheet.iterator();
        Row row = null;//from   w w w  .j av  a  2 s . c  o  m

        while (rowIterator.hasNext()) {
            row = rowIterator.next();
            if (row.getCell(0) == null || row.getCell(0).toString().isEmpty()) {
                break;
            } else {
                abreviaturas.put(row.getCell(0).getStringCellValue().trim(),
                        row.getCell(1).getStringCellValue().trim());
            }
        }

        return abreviaturas;

    }

From source file:cdiscisa.StreamUtil.java

private static Curso llenarCurso(Workbook wbLista, String unidadCapacitadora, String instructor)
            throws Exception {

        Sheet wbListaSheet = wbLista.getSheetAt(0);

        Curso c = new Curso();

        if (!(wbListaSheet.getRow(2) == null || wbListaSheet.getRow(2).getCell(4) == null
                || wbListaSheet.getRow(2).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.nombre_empresa = wbListaSheet.getRow(2).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "El nombre de la empresa en la Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }//from w  ww  .  j a v a2s .  co  m

        }

        if (!(wbListaSheet.getRow(4) == null || wbListaSheet.getRow(4).getCell(4) == null
                || wbListaSheet.getRow(4).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.nombre_curso = wbListaSheet.getRow(4).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "El nombre de el curso Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        }

        if (!(wbListaSheet.getRow(6) == null || wbListaSheet.getRow(6).getCell(4) == null
                || wbListaSheet.getRow(6).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.nombre_instructor = wbListaSheet.getRow(6).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "El nombre de el instructor Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        }

        if (!(wbListaSheet.getRow(8) == null || wbListaSheet.getRow(8).getCell(4) == null
                || wbListaSheet.getRow(8).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.horas_texto = wbListaSheet.getRow(8).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "La casilla de Horas en la Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        }

        if (!(wbListaSheet.getRow(11) == null || wbListaSheet.getRow(11).getCell(4) == null
                || wbListaSheet.getRow(11).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.razon_social = wbListaSheet.getRow(11).getCell(4).getStringCellValue();
                if (!c.razon_social.equalsIgnoreCase("NUEVA WAL?MART DE MEXICO S DE RL DE C.V.")
                        && !c.razon_social.equalsIgnoreCase("NUEVA WAL-MART DE MEXICO S DE RL DE C.V.")) {
                    c.walmart = false;
                }
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "La casilla de Razn Social de la empresa en la Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        }

        if (!(wbListaSheet.getRow(13) == null || wbListaSheet.getRow(13).getCell(4) == null
                || wbListaSheet.getRow(13).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.rfc_empresa = wbListaSheet.getRow(13).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "La casill de RFC de la empresa en Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        } else {
            JOptionPane.showMessageDialog(null, "El RFC de la empresa no puede estar vacio");
            throw new netoCustomException("Error al leer los datos del curso");
        }

        if (!(wbListaSheet.getRow(15) == null || wbListaSheet.getRow(15).getCell(4) == null
                || wbListaSheet.getRow(15).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.fecha_certificado = wbListaSheet.getRow(15).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "La casilla de la fecha de certificado de la empresa en la Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        }

        if (!(wbListaSheet.getRow(17) == null || wbListaSheet.getRow(17).getCell(4) == null
                || wbListaSheet.getRow(17).getCell(4).getStringCellValue().isEmpty())) {
            try {
                c.fecha_texto_diploma = wbListaSheet.getRow(17).getCell(4).getStringCellValue();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "La casilla de la fecha para diploma de la empresa en la Lista de Participantes parece tener datos no vlidos");
                throw new netoCustomException("Error al leer los datos del curso");
            }
        }

        if (!unidadCapacitadora.isEmpty()) {
            c.uCapacitadora = unidadCapacitadora;
        }

        if (!instructor.isEmpty()) {
            c.capacitador = instructor;
        }

        Calendar cal = Calendar.getInstance();

        if (!(wbListaSheet.getRow(4) == null || wbListaSheet.getRow(4).getCell(6) == null)) {
            if (wbListaSheet.getRow(4).getCell(6).getCellType() == 1) {
                cal.set(Calendar.DAY_OF_MONTH,
                        Integer.parseInt(wbListaSheet.getRow(4).getCell(6).getStringCellValue()));
            } else {
                cal.set(Calendar.DAY_OF_MONTH, (int) wbListaSheet.getRow(4).getCell(6).getNumericCellValue());
            }
        }
        if (!(wbListaSheet.getRow(6) == null || wbListaSheet.getRow(6).getCell(6) == null)) {
            cal.set(Calendar.MONTH, Integer.parseInt(wbListaSheet.getRow(6).getCell(6).getStringCellValue()) - 1);
        }
        if (!(wbListaSheet.getRow(8) == null || wbListaSheet.getRow(8).getCell(6) == null)) {
            cal.set(Calendar.YEAR, (int) wbListaSheet.getRow(8).getCell(6).getNumericCellValue());
        }

        c.fecha_inicio = cal.getTime();

        if (!(wbListaSheet.getRow(4) == null || wbListaSheet.getRow(4).getCell(7) == null)) {
            if (wbListaSheet.getRow(4).getCell(7).getCellType() == 1) {
                cal.set(Calendar.DAY_OF_MONTH,
                        Integer.parseInt(wbListaSheet.getRow(4).getCell(7).getStringCellValue()));
            } else {
                cal.set(Calendar.DAY_OF_MONTH, (int) wbListaSheet.getRow(4).getCell(7).getNumericCellValue());
            }
        }
        if (!(wbListaSheet.getRow(6) == null || wbListaSheet.getRow(6).getCell(7) == null)) {
            cal.set(Calendar.MONTH, Integer.parseInt(wbListaSheet.getRow(6).getCell(7).getStringCellValue()) - 1);
        }
        if (!(wbListaSheet.getRow(8) == null || wbListaSheet.getRow(8).getCell(7) == null)) {
            cal.set(Calendar.YEAR, (int) wbListaSheet.getRow(8).getCell(7).getNumericCellValue());
        }

        c.fecha_termino = cal.getTime();

        return c;
    }

From source file:cdiscisa.StreamUtil.java

private static ArrayList<Participante> llenarParticipantes(Workbook wbLista, String savePath) throws Exception {

        ArrayList<Participante> listaParticipantes = new ArrayList<>();
        Sheet wbListaSheet = wbLista.getSheetAt(0);
        Iterator<Row> rowIterator = wbListaSheet.iterator();
        String regex = "[A-Z]{1}[AEIOU]{1}[A-Z]{2}[0-9]{2}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}[HM]{1}(AS|BC|BS|CC|CS|CH|CL|CM|DF|DG|GT|GR|HG|JC|MC|MN|MS|NT|NL|OC|PL|QT|QR|SP|SL|SR|TC|TS|TL|VZ|YN|ZS|NE)[0-9A-Z]{5}$";
        /*//from w w w  .j  a va 2 s.  co  m
        "[A-Z]{2}([AEIOU]{1}|X)[A-Z]{2}[0-9]{2}" +
        "[0-9]{2}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}" +
        "[HM]{1}" +
        "(AS|BC|BS|CC|CS|CH|CL|CM|DF|DG|GT|GR|HG|JC|MC|MN|MS|NT|NL|OC|PL|QT|QR|SP|SL|SR|TC|TS|TL|VZ|YN|ZS|NE)" +
        "[0-9A-Z]{5}$";
        */
        Boolean sw = false;
        File file = new File(savePath + File.separator + "CURP_incorrecto.csv");
        if (!file.exists()) {
            file.createNewFile();
        }

        FileWriter fw = new FileWriter(file.getAbsoluteFile());
        fw.write("");

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();
            try {
                if (row.getCell(2) != null && row.getCell(2).getStringCellValue().equalsIgnoreCase("# Det.")) {
                    break;
                }
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "Error leyendo la columna Determinante del archivo Excel de Lista de participantes ");
            }
        }

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);

            if (row.getCell(2) == null || row.getCell(2).getStringCellValue().isEmpty()) {
                break;
            }

            Participante p = new Participante();

            if (row.getCell(2) != null && row.getCell(2).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(2).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.determinante = row.getCell(2).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo determinante del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(3) != null && row.getCell(3).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(3).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.sucursal = row.getCell(3).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la sucursal del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(4) != null && row.getCell(4).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(4).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.nombre = row.getCell(4).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Nombre del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(5) != null && row.getCell(5).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(5).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.apellidos = row.getCell(5).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Apellidos del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(6) != null && row.getCell(6).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(6).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.curp = row.getCell(6).getStringCellValue().trim();
                    Pattern pat = Pattern.compile(regex);

                    Matcher mat = pat.matcher(p.curp);

                    if (!mat.matches()) {
                        fw.append(p.curp + "," + p.nombre + " " + p.apellidos + "\n");
                        sw = true;
                        //JOptionPane.showMessageDialog(null,"El CURP " + p.curp + " del participante " + p.nombre + " " + p.apellidos + " parece estar mal formado, favor de revisarlo");
                    } else {
                        p.curp_malformed = false;
                    }
                    /*if (p.curp.length()>18){
                        throw new netoCustomException("Hay un error en el CURP: " + p.curp + " de " + p.nombre + " " + p.apellidos + ".\n Debera tener 18 caracteres, y tiene " + p.curp.length());
                    } */
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna CURP del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(7) != null && row.getCell(7).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(7).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.area_puesto = row.getCell(7).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Area Puesto del archivo Excel de Lista de participantes  ");
                }
            }

            if (row.getCell(8) != null && row.getCell(8).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(8).getCellType() != Cell.CELL_TYPE_ERROR) {
                try {
                    p.area_tematica = row.getCell(8).getStringCellValue().trim();
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Area Tematica del archivo Excel de Lista de participantes  ");
                }
            }

            p.aprovado = false;
            if (row.getCell(9) != null && row.getCell(9).getCellType() != Cell.CELL_TYPE_BLANK
                    && row.getCell(9).getCellType() != Cell.CELL_TYPE_ERROR
                    && row.getCell(9).getStringCellValue().equalsIgnoreCase("Aprobado")) {
                try {
                    p.aprovado = true;
                } catch (Exception ex) {
                    JOptionPane.showMessageDialog(null,
                            "Error leyendo la columna Aprobado del archivo Excel de Lista de participantes  ");
                }
            }

            listaParticipantes.add(p);

        }

        fw.close();

        if (sw) {
            JOptionPane.showMessageDialog(null,
                    "Algunos participantes parecen tener el CURP incorrecto. Este archivo contiene los errores: \n\n"
                            + savePath + File.separator + "CURP_incorrecto.csv");
        }

        return listaParticipantes;
        // method body
    }

From source file:ch.ledcom.log4jtools.config.ConfigReader.java

License:Apache License

public List<CategorizationFilter> loadConfig(InputStream in) throws InvalidFormatException, IOException {
    List<CategorizationFilter> result = new ArrayList<CategorizationFilter>();
    Workbook wb = WorkbookFactory.create(in);
    Sheet sheet = wb.getSheetAt(0);
    int i = 1;//from   ww  w. j a va  2 s  . c om
    while (true) {
        Row row = sheet.getRow(i++);

        String description = readCell(row, 0);
        if (description == null) {
            break;
        }
        String loggerName = readCell(row, 1);
        Level level = readCell(row, 2) == null ? (Level) null : Level.toLevel(readCell(row, 2));
        Pattern messagePattern = readPattern(row, 3);
        Pattern throwablePattern = readPattern(row, 4);
        String category = readCell(row, 5);
        String bugTrackerRef = readCell(row, 6);

        CategorizationFilter filter = new CategorizationFilter(description, loggerName, level, messagePattern,
                throwablePattern, category, bugTrackerRef);
        result.add(filter);
    }
    return result;
}

From source file:ch.ReadData.java

public void read_data_only(String path) {
    try (FileInputStream file = new FileInputStream(new File(path))) {
        update_halls = con.prepareStatement(JDBCConnection.Update_halls_colleges);
        PreparedStatement prepstm = con.prepareStatement(JDBCConnection.Insert_Into_temptable);
        PreparedStatement student_insert = con.prepareStatement(JDBCConnection.Insert_Into_studentcourse);
        con.setAutoCommit(false);//w w  w.jav a 2 s. c  om
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);
        String college_name, student_name, course_name, lecturer;
        int student_id, course_id, course_sec;
        for (org.apache.poi.ss.usermodel.Row row : sheet) {
            course_name = row.getCell(6).getStringCellValue();
            if (course_name.contains("")) {
                student_id = (int) row.getCell(0).getNumericCellValue();
                student_name = row.getCell(1).getStringCellValue();
                college_name = row.getCell(2).getStringCellValue();
                course_id = (int) row.getCell(3).getNumericCellValue();
                course_sec = (int) row.getCell(5).getNumericCellValue();
                course_name = row.getCell(4).getStringCellValue();
                lecturer = row.getCell(7).getStringCellValue();
                prepstm.setInt(1, student_id);
                prepstm.setString(2, student_name);
                prepstm.setString(3, college_name);
                prepstm.setInt(4, course_id);
                prepstm.setInt(5, course_sec);
                prepstm.setString(6, course_name);
                prepstm.setString(7, lecturer);
                student_insert.setInt(1, student_id);
                student_insert.setInt(2, course_id);
                student_insert.setInt(3, course_sec);
                student_insert.addBatch();
                prepstm.addBatch();
            }
        }
        prepstm.executeBatch();
        student_insert.executeBatch();
        PreparedStatement prep = con.prepareStatement(JDBCConnection.Insert_new_college);
        Statement stm = con.createStatement();
        ResultSet r = stm.executeQuery(JDBCConnection.Select_collegs);
        int college_id = 1;
        PreparedStatement update_college_name = con.prepareStatement(JDBCConnection.Update_college_name);
        while (r.next()) {
            prep.setInt(1, college_id * 100);
            prep.setString(2, r.getString("college_name"));
            update_college_name.setInt(1, college_id * 100);
            update_college_name.setString(2, r.getString("college_name"));
            update_college_name.addBatch();
            update_halls.setInt(1, college_id * 100);
            update_halls.setString(2, r.getString("college_name"));
            update_halls.addBatch();
            prep.addBatch();
            college_id++;
        }
        prep.executeBatch();
        update_college_name.executeBatch();
        ResultSet courses_result = stm.executeQuery(JDBCConnection.Select_courses);
        PreparedStatement add_courses = con.prepareStatement(JDBCConnection.Insert_courses);
        while (courses_result.next()) {
            add_courses.setInt(1, courses_result.getInt("course_id"));
            add_courses.setString(2, courses_result.getString("course_name"));
            add_courses.setInt(3, courses_result.getInt("total_students"));
            add_courses.addBatch();
        }
        add_courses.executeBatch();
        ResultSet lecturer_result = stm.executeQuery(JDBCConnection.Select_luecturers);
        PreparedStatement add_lecturer = con.prepareStatement(JDBCConnection.Insert_Lecturers);
        int lecturer_id = 1000;
        PreparedStatement update_lecturers = con.prepareStatement(JDBCConnection.Update_lecturer_name);
        while (lecturer_result.next()) {
            add_lecturer.setInt(1, lecturer_id);
            add_lecturer.setString(2, lecturer_result.getString("lecturer"));
            add_lecturer.setInt(3, lecturer_result.getInt("college_name"));
            add_lecturer.addBatch();
            update_lecturers.setInt(1, lecturer_id);
            update_lecturers.setString(2, lecturer_result.getString("lecturer"));
            update_lecturers.addBatch();
            lecturer_id += 1;
        }
        add_lecturer.executeBatch();
        update_lecturers.executeBatch();
        ResultSet courses_sections_set = stm.executeQuery(JDBCConnection.Select_Courses_Sections);
        PreparedStatement add_courses_sections = con.prepareStatement(JDBCConnection.Insert_Course_Section);
        while (courses_sections_set.next()) {
            add_courses_sections.setInt(1, courses_sections_set.getInt("course_id"));
            add_courses_sections.setInt(2, courses_sections_set.getInt("course_sec"));
            add_courses_sections.setInt(3, courses_sections_set.getInt("lecturer"));
            add_courses_sections.setInt(4, courses_sections_set.getInt("number_of_students"));
            add_courses_sections.setInt(5, courses_sections_set.getInt("college_name"));
            add_courses_sections.addBatch();
        }
        add_courses_sections.executeBatch();
        PreparedStatement update_students = con.prepareStatement(JDBCConnection.Insert_Students);
        ResultSet get_students = stm.executeQuery(JDBCConnection.Select_Students);
        while (get_students.next()) {
            update_students.setInt(1, get_students.getInt("student_id"));
            update_students.setString(2, get_students.getString("student_name"));
            update_students.setInt(3, get_students.getInt("college_name"));
            update_students.addBatch();
        }
        update_students.executeBatch();
        Statement drop = con.createStatement();
        drop.executeUpdate(JDBCConnection.Drop_temptable);
        con.commit();
    } catch (IOException | SQLException | org.apache.poi.openxml4j.exceptions.InvalidFormatException
            | EncryptedDocumentException ex) {
        Logger.getLogger(ReadData.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:ch.ReadData.java

public void readHalls(String path) {
    try (FileInputStream file = new FileInputStream(new File(path))) {
        PreparedStatement prepstm = con.prepareStatement(JDBCConnection.insert_halls);
        con.setAutoCommit(false);/*from   w ww.  j  a v  a2s  .  c  o  m*/
        //Get the workbook instance for XLS file
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);
        String college_name;
        int hall_number, capacity;
        for (org.apache.poi.ss.usermodel.Row row : sheet) {
            hall_number = (int) row.getCell(0).getNumericCellValue();
            college_name = row.getCell(1).getStringCellValue();
            capacity = (int) row.getCell(2).getNumericCellValue();
            prepstm.setInt(1, hall_number);
            prepstm.setString(2, college_name);
            prepstm.setInt(3, capacity);
            prepstm.addBatch();
        }
        prepstm.executeBatch();
        update_halls.executeBatch();
        //con.prepareStatement(colleges_update_query).executeUpdate();
        con.commit();
    } catch (IOException | SQLException | org.apache.poi.openxml4j.exceptions.InvalidFormatException
            | EncryptedDocumentException ex) {
        Logger.getLogger(ReadData.class.getName()).log(Level.SEVERE, null, ex);
    }
}