Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

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

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:BUS.FileManager.java

public Sheet readXLSXFile(File f) throws FileNotFoundException, IOException, InvalidFormatException {
    InputStream ExcelFileToRead = new FileInputStream(f);
    //System.out.println(f.getName());
    XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(ExcelFileToRead);
    XSSFSheet sheet = wb.getSheetAt(0);/*from   ww w.  java2s .c  o m*/
    return sheet;
}

From source file:BUS.FileManager.java

private Sheet readXLSFile(File f) throws FileNotFoundException, IOException, InvalidFormatException {
    InputStream ExcelFileToRead = new FileInputStream(f);
    HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(ExcelFileToRead);
    HSSFSheet sheet = wb.getSheetAt(0);//w w w  . j a  va  2s  .c  o m
    return sheet;
}

From source file:business.SongExcelParser.java

public ArrayList<SongContainer> fileToSongFiles(File fileSelected) {
    if (fileSelected instanceof File) {
        try {/*  w w  w. j a  v a  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 {//  w  w w. j a  v a  2  s .  c  o  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.ja  va2  s  . com*/
    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

/**
     * @param args the command line arguments
     * @throws java.io.IOException/* ww w  .ja v  a 2 s .co m*/
     * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
     */

    public static void main(String[] args) throws IOException, InvalidFormatException, Exception {

        //directorioPath, destFolder, listaPath,unidadCapacitadora,instructor,chkDip,chkDipFirma,chkDipLogo,chkConst,chkConstFirma,chkConstLogo,chkDC3a,chkDC3Firmaa,chkDC3Logoa,chkCompilado, chkDC3CURP
        //Workbook wbLista = WorkbookFactory.create(new File("src/files/FormatoCertificado.xlsx"));
        //Workbook wbDirectorio = WorkbookFactory.create(new File("src/files/DirectorioBAE.xlsx"));
        Workbook wbDirectorio = null, wbLista;

        //file = (BufferedInputStream)cdiscisa.Cdiscisa.class.getClassLoader().getResourceAsStream("files/DC3_base_firma.pdf");

        //JOptionPane.showMessageDialog(null, "directorio " + args[0] +  " Lista : " + args[2]);

        if (args[0].isEmpty()) {
            try {
                InputStream is = new FileInputStream("files/DirectorioBAE.xlsx");
                wbDirectorio = WorkbookFactory.create(is);
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null, "Error al cargar el Directorio de Sucursales \ndirectorio: "
                        + String.valueOf(wbDirectorio) + "\n" + ex.toString());
                return;
            }
        } else {
            try {
                wbDirectorio = WorkbookFactory.create(new File(args[0]));
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "Hubo un error al cargar el Directorio, intenta de nuevo \nDirectorio: " + args[2] + "\n"
                                + ex.toString());
                return;
            }
        }

        wbLista = null;

        if (args[2].isEmpty()) {
            JOptionPane.showMessageDialog(null, "Es necesario proporcionar la lista de participates de este curso");
            return;
        } else {
            try {
                wbLista = WorkbookFactory.create(new File(args[2]));
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null,
                        "Hubo un error al cargar la Lista de participantes, intenta de nuevo \nLista: " + args[2]
                                + "\n" + ex.toString());
                return;
            }
        }

        String nameRegistro;

        if (args[15].isEmpty()) {
            nameRegistro = "files/RegistroPCJorge Razon2016.pdf";
        } else {
            nameRegistro = args[15];
        }

        if (args[16].isEmpty()) {
            JOptionPane.showMessageDialog(null, "Es necesario proporcionar la lista autgrafa de este curso.");
            return;
        }

        ArrayList<Directorio> listaDirectorio = null;
        Map<String, String> abreviaturas = new HashMap<>();

        ArrayList<Participante> listaParticipantes = null;
        Curso c = null;

        Workbook wbAbrev = null;
        try {

            //ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
            //File file = new File(classLoader.getResource("files/abrevcursos.xlsx").toString());
            // BufferedInputStream file= (BufferedInputStream) cdiscisa.Cdiscisa.class.getClassLoader().getClass().getResourceAsStream("files/abrev_cursos.xlsx");
            // File file = new File(cdiscisa.Cdiscisa.class.getClassLoader().getResource("files/abrevcursos.xlsx").getFile());
            //wbAbrev = WorkbookFactory.create(new File(cdiscisa.Cdiscisa.class.getClassLoader().getResource("files/abrev_cursos.xlsx").toString()));
            //InputStream is = new FileInputStream("files/abrevcursos.xlsx");
            InputStream is2 = cdiscisa.Cdiscisa.class.getClassLoader()
                    .getResourceAsStream("files/abrevcursos.xlsx");

            wbAbrev = WorkbookFactory.create(is2);

        } catch (EncryptedDocumentException EDex) {
            JOptionPane.showMessageDialog(null, EDex.getMessage() + "\n" + EDex);
        } catch (IOException ioex) {
            JOptionPane.showMessageDialog(null, ioex.getMessage());
        } catch (InvalidFormatException IFEx) {
            JOptionPane.showMessageDialog(null, IFEx.getMessage());
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());

        }
        //Workbook wbAbrev = WorkbookFactory.create(file);

        try {
            abreviaturas = llenarAbreviaturas(wbAbrev);

        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,
                    "Hubo un error leyendo el archivo de abreviaturas, es posible que contenga celdas faltantes o mal formadas");
            return;
        }

        try {
            listaDirectorio = llenarDirectorio(wbDirectorio);
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,
                    "Hubo un error leyendo el archivo de directorio, es posible que contenga celdas faltantes o mal formadas");
            return;
        }
        try {
            c = llenarCurso(wbLista, args[3], args[4]);
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,
                    "Hubo un error leyendo la infomacion de los cursos, es posible que contenga celdas faltantes o mal formadas");
            return;
        }
        try {
            listaParticipantes = llenarParticipantes(wbLista, args[1]);
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,
                    "Hubo un error leyendo el archivo la Lista de Participantes, es posible que contenga celdas faltantes o mal formadas");
            return;
        }

        Map<String, String> dosc = new HashMap<>();

        if (args[5].equalsIgnoreCase("true")) { //check if diploma checkbox is checked    
            imprimirDiplomas_main(listaParticipantes, c, listaDirectorio, args[6], args[7], args[1], dosc, args[4],
                    abreviaturas); // args 6 y 7 son firma y logo respectivamente      
        }

        if (args[8].equalsIgnoreCase("true")) {
            imprimirConstancias(listaParticipantes, c, listaDirectorio, args[9], args[10], args[1], dosc, args[4],
                    abreviaturas);
        }

        if (args[11].equalsIgnoreCase("true")) {
            imprimirDC3(listaParticipantes, c, args[12], args[13], args[1], dosc, abreviaturas, args[17]);
        }

        if (args[14].equalsIgnoreCase("true")) {
            mergeFiles(dosc, listaParticipantes, listaDirectorio, nameRegistro, args[16]);
        }

        JOptionPane.showMessageDialog(null, "Los documentos se han generado exitosamente");

    }

From source file:ch.emad.business.schuetu.xls.ToXLSDumper2.java

License:Apache License

protected byte[] convertModelToXLS(List<Mannschaft> mannschaftenIn, List<Spiel> spieleIn,
        List<Korrektur> korrekturenIn, List<DBAuthUser> usersIn, List<File> filesIn, List<Text> texteIn,
        List<Penalty> penaltyIn, List<Kontakt> kontakteIn) {

    List<Spiel> spiele = spieleIn;
    List<Mannschaft> mannschaften = mannschaftenIn;
    List<Korrektur> korrekturen = korrekturenIn;
    List<DBAuthUser> users = usersIn;
    List<File> file2s = filesIn;
    List<Text> texte = texteIn;
    List<Penalty> penaltys = penaltyIn;
    List<Kontakt> kontakte = kontakteIn;

    if (spiele == null) {
        spiele = new ArrayList();
    } else {/*from  w  w  w  .  j a  v  a 2s . c  om*/
        // setze dummy Mannschaften fuer das speichern der Finale
        Mannschaft m = new Mannschaft();
        m.setId(0l);
        for (Spiel s : spiele) {
            if (s.getMannschaftA() == null) {
                s.setMannschaftA(m);
            }
            if (s.getMannschaftB() == null) {
                s.setMannschaftB(m);
            }
        }

    }
    Collections.sort(spiele, new SpielZeitComperator());

    if (mannschaften == null) {
        mannschaften = new ArrayList();
    }

    if (korrekturen == null) {
        korrekturen = new ArrayList();
    }

    if (users == null) {
        korrekturen = new ArrayList();
    }

    if (file2s == null) {
        file2s = new ArrayList();
    }

    if (texte == null) {
        texte = new ArrayList();
    }

    if (penaltys == null) {
        penaltys = new ArrayList();
    }

    if (kontakte == null) {
        kontakte = new ArrayList();
    }

    Map beans = new HashMap();

    beans.put("mannschaften", mannschaften);
    beans.put("spiele", spiele);
    beans.put("korrekturen", korrekturen);
    beans.put("users", users);
    beans.put("attachements", file2s);
    beans.put("texte", texte);
    beans.put("penaltys", penaltys);
    beans.put("kontakte", kontakte);

    XLSTransformer transformer = new XLSTransformer();
    byte[] arr = readFreshTemplate();
    try {

        InputStream is = new ByteArrayInputStream(arr);
        Workbook wb = WorkbookFactory.create(is);

        transformer.transformWorkbook(wb, beans);

        ByteArrayOutputStream out = new ByteArrayOutputStream();

        wb.write(out);

        return out.toByteArray();

    } catch (Exception e) {
        LOG.error(e.getMessage(), e);
    }
    return null;
}

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);//from ww w  .j  ava 2 s.c om
    int i = 1;
    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);/*from w w  w .j  ava2 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);/*  w  w w .  j a v a 2s  . co  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);
    }
}