List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * //from ww w. j av a2 s .c o m * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final String getContent(final InputStream is) { try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; default: // skip } } } } return new String(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents", e); } return ""; }
From source file:pe.crvm.eurekacs.view.MantClientesView.java
private void btnExcelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnExcelActionPerformed //////////////////////// if (lista == null || lista.size() == 0) { return;//w w w . ja va 2s . c o m } try { // El libro //String plantilla = "/pe/egcc/eurekacs/plantillas/Clientes.xls"; String plantilla = "/pe/crvm/eurekacs/plantilla/Clientes.xls"; InputStream isPlantilla = Class.class.getResourceAsStream(plantilla); POIFSFileSystem fs = new POIFSFileSystem(isPlantilla); HSSFWorkbook wb = new HSSFWorkbook(fs, true); // Acceder a la hoja HSSFSheet sheet = wb.getSheetAt(0); // Llenar datos int fila = 0; for (Cliente bean : lista) { fila++; HSSFRow row = sheet.createRow(fila); row.createCell(0).setCellValue(bean.getCodigo()); row.createCell(1).setCellValue(bean.getPaterno()); row.createCell(2).setCellValue(bean.getMaterno()); row.createCell(3).setCellValue(bean.getNombre()); row.createCell(4).setCellValue(bean.getDni()); row.createCell(5).setCellValue(bean.getCiudad()); row.createCell(6).setCellValue(bean.getDireccion()); row.createCell(7).setCellValue(bean.getTelefono()); row.createCell(8).setCellValue(bean.getEmail()); } // Grabar FileOutputStream fileOut = new FileOutputStream( "e:/JAVA/ESCRITORIO/CLASE03/EurecaCS/EurecaCS/Cliente.xls"); wb.write(fileOut); fileOut.close(); Dialogo.info(rootPane, "Proceso ok."); } catch (Exception e) { e.printStackTrace(); Dialogo.error(rootPane, e.getMessage()); } }
From source file:pe.eeob.ndrasistencias.view.ConsultaEstudianteView.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed if (lista == null || lista.size() == 0) { return;//from w w w . j a v a 2s. co m } try { // El libro String plantilla = "/pe/eeob/ndrasistencias/plantillas/Estudiantes.xls"; InputStream isPlantilla = Class.class.getResourceAsStream(plantilla); POIFSFileSystem fs = new POIFSFileSystem(isPlantilla); HSSFWorkbook wb = new HSSFWorkbook(fs, true); // Acceder a la hoja HSSFSheet sheet = wb.getSheetAt(0); // Llenar datos int fila = 0; for (Estudiante bean : lista) { fila++; HSSFRow row = sheet.createRow(fila); row.createCell(0).setCellValue(bean.getDni()); row.createCell(1).setCellValue(bean.getPaterno()); row.createCell(2).setCellValue(bean.getMaterno()); row.createCell(3).setCellValue(bean.getNombre()); row.createCell(4).setCellValue(bean.getEdad()); row.createCell(5).setCellValue(bean.getDistrito()); row.createCell(6).setCellValue(bean.getDni_apoderado()); } // Grabar FileOutputStream fileOut = new FileOutputStream("c://prog/Estudiantes.xls"); wb.write(fileOut); fileOut.close(); Dialogo.info(rootPane, "Los datos fueron exportados correctamente!"); } catch (Exception e) { e.printStackTrace(); Dialogo.error(rootPane, e.getMessage()); } }
From source file:pe.egcc.eurekacs.view.MantClientesView.java
private void btnExcelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnExcelActionPerformed if (lista == null || lista.size() == 0) { return;// w w w . j a va 2s . c o m } try { // El libro String plantilla = "/pe/egcc/eurekacs/plantillas/Clientes.xls"; InputStream isPlantilla = Class.class.getResourceAsStream(plantilla); POIFSFileSystem fs = new POIFSFileSystem(isPlantilla); HSSFWorkbook wb = new HSSFWorkbook(fs, true); // Acceder a la hoja HSSFSheet sheet = wb.getSheetAt(0); // Llenar datos int fila = 0; for (Cliente bean : lista) { fila++; HSSFRow row = sheet.createRow(fila); row.createCell(0).setCellValue(bean.getCodigo()); row.createCell(1).setCellValue(bean.getPaterno()); row.createCell(2).setCellValue(bean.getMaterno()); } // Grabar FileOutputStream fileOut = new FileOutputStream("e://egcc/clientes.xls"); wb.write(fileOut); fileOut.close(); Dialogo.info(rootPane, "Proceso ok."); } catch (Exception e) { e.printStackTrace(); Dialogo.error(rootPane, e.getMessage()); } }
From source file:poi.hpsf.examples.CopyCompare.java
License:Apache License
/** * <p>Runs the example program. The application expects one or two * arguments:</p>// w w w . ja v a 2 s . c o m * * <ol> * * <li><p>The first argument is the disk file name of the POI filesystem to * copy.</p></li> * * <li><p>The second argument is optional. If it is given, it is the name of * a disk file the copy of the POI filesystem will be written to. If it is * not given, the copy will be written to a temporary file which will be * deleted at the end of the program.</p></li> * * </ol> * * @param args Command-line arguments. * @exception MarkUnsupportedException if a POI document stream does not * support the mark() operation. * @exception NoPropertySetStreamException if the application tries to * create a property set from a POI document stream that is not a property * set stream. * @exception java.io.IOException if any I/O exception occurs. * @exception java.io.UnsupportedEncodingException if a character encoding is not * supported. */ public static void main(final String[] args) throws NoPropertySetStreamException, MarkUnsupportedException, UnsupportedEncodingException, IOException { String originalFileName = null; String copyFileName = null; /* Check the command-line arguments. */ if (args.length == 1) { originalFileName = args[0]; File f = TempFile.createTempFile("CopyOfPOIFileSystem-", ".ole2"); f.deleteOnExit(); copyFileName = f.getAbsolutePath(); } else if (args.length == 2) { originalFileName = args[0]; copyFileName = args[1]; } else { System.err.println("Usage: " + CopyCompare.class.getName() + "originPOIFS [copyPOIFS]"); System.exit(1); } /* Read the origin POIFS using the eventing API. The real work is done * in the class CopyFile which is registered here as a POIFSReader. */ final POIFSReader r = new POIFSReader(); final CopyFile cf = new CopyFile(copyFileName); r.registerListener(cf); r.read(new FileInputStream(originalFileName)); /* Write the new POIFS to disk. */ cf.close(); /* Read all documents from the original POI file system and compare them * with the equivalent document from the copy. */ final POIFSFileSystem opfs = new POIFSFileSystem(new FileInputStream(originalFileName)); final POIFSFileSystem cpfs = new POIFSFileSystem(new FileInputStream(copyFileName)); final DirectoryEntry oRoot = opfs.getRoot(); final DirectoryEntry cRoot = cpfs.getRoot(); final StringBuffer messages = new StringBuffer(); if (equal(oRoot, cRoot, messages)) System.out.println("Equal"); else System.out.println("Not equal: " + messages.toString()); }
From source file:poi.hpsf.examples.ModifyDocumentSummaryInformation.java
License:Apache License
/** * <p>Main method - see class description.</p> * * @param args The command-line parameters. * @throws java.io.IOException//ww w .jav a 2 s . co m * @throws MarkUnsupportedException * @throws NoPropertySetStreamException * @throws UnexpectedPropertySetTypeException * @throws WritingNotSupportedException */ public static void main(final String[] args) throws IOException, NoPropertySetStreamException, MarkUnsupportedException, UnexpectedPropertySetTypeException, WritingNotSupportedException { /* Read the name of the POI filesystem to modify from the command line. * For brevity to boundary check is performed on the command-line * arguments. */ File poiFilesystem = new File(args[0]); /* Open the POI filesystem. */ InputStream is = new FileInputStream(poiFilesystem); POIFSFileSystem poifs = new POIFSFileSystem(is); is.close(); /* Read the summary information. */ DirectoryEntry dir = poifs.getRoot(); SummaryInformation si; try { DocumentEntry siEntry = (DocumentEntry) dir.getEntry(SummaryInformation.DEFAULT_STREAM_NAME); DocumentInputStream dis = new DocumentInputStream(siEntry); PropertySet ps = new PropertySet(dis); dis.close(); si = new SummaryInformation(ps); } catch (FileNotFoundException ex) { /* There is no summary information yet. We have to create a new * one. */ si = PropertySetFactory.newSummaryInformation(); } /* Change the author to "Rainer Klute". Any former author value will * be lost. If there has been no author yet, it will be created. */ si.setAuthor("Rainer Klute"); System.out.println("Author changed to " + si.getAuthor() + "."); /* Handling the document summary information is analogous to handling * the summary information. An additional feature, however, are the * custom properties. */ /* Read the document summary information. */ DocumentSummaryInformation dsi; try { DocumentEntry dsiEntry = (DocumentEntry) dir.getEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME); DocumentInputStream dis = new DocumentInputStream(dsiEntry); PropertySet ps = new PropertySet(dis); dis.close(); dsi = new DocumentSummaryInformation(ps); } catch (FileNotFoundException ex) { /* There is no document summary information yet. We have to create a * new one. */ dsi = PropertySetFactory.newDocumentSummaryInformation(); } /* Change the category to "POI example". Any former category value will * be lost. If there has been no category yet, it will be created. */ dsi.setCategory("POI example"); System.out.println("Category changed to " + dsi.getCategory() + "."); /* Read the custom properties. If there are no custom properties yet, * the application has to create a new CustomProperties object. It will * serve as a container for custom properties. */ CustomProperties customProperties = dsi.getCustomProperties(); if (customProperties == null) customProperties = new CustomProperties(); /* Insert some custom properties into the container. */ customProperties.put("Key 1", "Value 1"); customProperties.put("Schl\u00fcssel 2", "Wert 2"); customProperties.put("Sample Number", new Integer(12345)); customProperties.put("Sample Boolean", Boolean.TRUE); customProperties.put("Sample Date", new Date()); /* Read a custom property. */ Object value = customProperties.get("Sample Number"); /* Write the custom properties back to the document summary * information. */ dsi.setCustomProperties(customProperties); /* Write the summary information and the document summary information * to the POI filesystem. */ si.write(dir, SummaryInformation.DEFAULT_STREAM_NAME); dsi.write(dir, DocumentSummaryInformation.DEFAULT_STREAM_NAME); /* Write the POI filesystem back to the original file. Please note that * in production code you should never write directly to the origin * file! In case of a writing error everything would be lost. */ OutputStream out = new FileOutputStream(poiFilesystem); poifs.writeFilesystem(out); out.close(); }
From source file:poi.hssf.usermodel.examples.EmeddedObjects.java
License:Apache License
public static void main(String[] args) throws Exception { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(args[0])); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) { //the OLE2 Class Name of the object String oleName = obj.getOLE2ClassName(); if (oleName.equals("Worksheet")) { DirectoryNode dn = (DirectoryNode) obj.getDirectory(); HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, fs, false); //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets()); } else if (oleName.equals("Document")) { DirectoryNode dn = (DirectoryNode) obj.getDirectory(); HWPFDocument embeddedWordDocument = new HWPFDocument(dn); //System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text()); } else if (oleName.equals("Presentation")) { DirectoryNode dn = (DirectoryNode) obj.getDirectory(); SlideShow embeddedPowerPointDocument = new SlideShow(new HSLFSlideShow(dn)); //System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length); } else {//from w w w . jav a2 s. c om if (obj.hasDirectoryEntry()) { // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is DirectoryNode dn = (DirectoryNode) obj.getDirectory(); for (Iterator entries = dn.getEntries(); entries.hasNext();) { Entry entry = (Entry) entries.next(); //System.out.println(oleName + "." + entry.getName()); } } else { // There is no DirectoryEntry // Recover the object's data from the HSSFObjectData instance. byte[] objectData = obj.getObjectData(); } } } }
From source file:poi.hssf.usermodel.examples.EventExample.java
License:Apache License
/** * Read an excel file and spit out what we find. * * @param args Expect one argument that is the file to read. * @throws java.io.IOException When there is an error processing the file. *//* w w w .ja va2s. c om*/ public static void main(String[] args) throws IOException { // create a new file input stream with the input file specified // at the command line FileInputStream fin = new FileInputStream(args[0]); // create a new org.apache.poi.poifs.filesystem.Filesystem POIFSFileSystem poifs = new POIFSFileSystem(fin); // get the Workbook (excel part) stream in a InputStream InputStream din = poifs.createDocumentInputStream("Workbook"); // construct out HSSFRequest object HSSFRequest req = new HSSFRequest(); // lazy listen for ALL records with the listener shown above req.addListenerForAllRecords(new EventExample()); // create our event factory HSSFEventFactory factory = new HSSFEventFactory(); // process our events based on the document input stream factory.processEvents(req, din); // once all the events are processed close our file input stream fin.close(); // and our document input stream (don't want to leak these!) din.close(); System.out.println("done."); }
From source file:poi.hssf.usermodel.examples.ReadWriteWorkbook.java
License:Apache License
public static void main(String[] args) throws IOException { FileInputStream fileIn = null; FileOutputStream fileOut = null; try {/*from www . j ava 2 s .co m*/ fileIn = new FileInputStream("workbook.xls"); POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(2); if (row == null) row = sheet.createRow(2); HSSFCell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file fileOut = new FileOutputStream("workbookout.xls"); wb.write(fileOut); } finally { if (fileOut != null) fileOut.close(); if (fileIn != null) fileIn.close(); } }
From source file:projekt.servise.impl.ReadDataFromExcelServiceImpl.java
@Override public void getData() { try {//from w w w .ja v a 2s . c om POIFSFileSystem fs = null; try { fs = new POIFSFileSystem(new FileInputStream( "C:/Users/Lenovo/Documents/NetBeansProjects/SoftwareArchitectureProject-master/src/main/java/projekt/Koormused_test.xls")); } catch (FileNotFoundException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } HSSFWorkbook wb = null; try { wb = new HSSFWorkbook(fs); } catch (IOException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; int rows = sheet.getPhysicalNumberOfRows(); int cols = 0; int tmp = 0; for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } Connection conn = DriverManager.getConnection( "jdbc:postgresql://dev.vk.edu.ee:5432/GroupWork?currentSchema=project", "t131566", "t131566"); for (int r = 11; r < rows + 11; r++) { row = sheet.getRow(r); if (row != null) { PreparedStatement preparedStatementRoles; ResultSet resultRoles = null; try { preparedStatementRoles = conn.prepareStatement("SELECT * FROM project.role"); resultRoles = preparedStatementRoles.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultRoles.next()) { conn.setAutoCommit(false); PreparedStatement preparedStatementRole = conn .prepareStatement("INSERT INTO project.role (rolename) VALUES (?)"); preparedStatementRole.setString(1, "teacher"); preparedStatementRole.addBatch(); preparedStatementRole.setString(1, "student"); preparedStatementRole.addBatch(); preparedStatementRole.setString(1, "admin"); preparedStatementRole.addBatch(); preparedStatementRole.executeBatch(); conn.commit(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } PreparedStatement preparedStatementLanguages = null; try { preparedStatementLanguages = conn .prepareStatement("SELECT * FROM project.language where name=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementLanguages.setString(1, StringUtils.trim(row.getCell(13).getStringCellValue())); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } ResultSet resultLanguages = null; try { resultLanguages = preparedStatementLanguages.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultLanguages.next()) { PreparedStatement preparedStatementLanguage = conn .prepareStatement("INSERT INTO project.language (name) VALUES (?)"); preparedStatementLanguage.setString(1, StringUtils.trim(row.getCell(13).getStringCellValue())); preparedStatementLanguage.executeUpdate(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } PreparedStatement preparedStatementTeacher = null; try { preparedStatementTeacher = conn .prepareStatement("SELECT * FROM project.person where lastname=? and firstname=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } String firstname = row.getCell(12).getStringCellValue().split("\\.")[0]; String lastname = row.getCell(12).getStringCellValue().split("\\.")[1]; try { preparedStatementTeacher.setString(1, lastname); preparedStatementTeacher.setString(2, firstname); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } ResultSet resultTeacher = null; try { resultTeacher = preparedStatementTeacher.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultTeacher.next()) { PreparedStatement preparedStatementNewTeacher = conn.prepareStatement( "INSERT INTO project.person (firstname,lastname,roleid) VALUES (?,?,?)"); PreparedStatement preparedStatementTeacherRole = conn .prepareStatement("SELECT * FROM project.role where rolename='teacher'"); int roleId = 0; ResultSet resultTeacherRole = preparedStatementTeacherRole.executeQuery(); if (resultTeacherRole.next()) { roleId = resultTeacherRole.getInt(1); } preparedStatementNewTeacher.setString(1, firstname); preparedStatementNewTeacher.setString(2, lastname); preparedStatementNewTeacher.setInt(3, roleId); preparedStatementNewTeacher.executeUpdate(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } String courseCode = row.getCell(2).getStringCellValue(); Course course = null; try { course = courseService.getByCode(courseCode); } catch (Exception e) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, e); } int courseId = 0; if (course == null) { PreparedStatement preparedStatementCourse = null; try { preparedStatementCourse = conn.prepareStatement( "INSERT INTO project.course (code,name,lectureship) VALUES (?,?,?)"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementCourse.setString(1, row.getCell(2).getStringCellValue()); preparedStatementCourse.setString(2, row.getCell(3).getStringCellValue()); preparedStatementCourse.setString(3, row.getCell(1).getStringCellValue()); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementCourse.executeUpdate(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } else { courseId = course.getId(); } if (courseId != 0) { PreparedStatement preparedStatementLanguageId = null; try { preparedStatementLanguageId = conn .prepareStatement("SELECT * FROM project.language where name=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementLanguageId.setString(1, row.getCell(13).getStringCellValue()); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } int languageId = 0; ResultSet resultLanguageId = null; try { resultLanguageId = preparedStatementLanguageId.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (resultLanguageId.next()) { languageId = resultLanguageId.getInt(1); PreparedStatement preparedStatementTeacherId = conn.prepareStatement( "SELECT * FROM project.person where firstname=? and lastname=?"); preparedStatementTeacherId.setString(1, firstname); preparedStatementTeacherId.setString(2, lastname); int teacherId = 0; ResultSet resultTeacherId = preparedStatementTeacherId.executeQuery(); if (resultTeacherId.next()) { teacherId = resultTeacherId.getInt(1); PreparedStatement preparedStatementExistingCourseData = conn .prepareStatement("SELECT * FROM project.coursedata where courseid=? " + "and practice=? and excercise=? and lecture=? and languageid=? and teacherid=?"); preparedStatementExistingCourseData.setInt(1, courseId); int practice = (int) (row.getCell(6) != null ? row.getCell(6).getNumericCellValue() : 0); int lecture = (int) (row.getCell(5) != null ? row.getCell(5).getNumericCellValue() : 0); int exercise = (int) (row.getCell(7) != null ? row.getCell(7).getNumericCellValue() : 0); preparedStatementExistingCourseData.setInt(2, practice); preparedStatementExistingCourseData.setInt(3, exercise); preparedStatementExistingCourseData.setInt(4, lecture); preparedStatementExistingCourseData.setInt(5, languageId); preparedStatementExistingCourseData.setInt(6, teacherId); ResultSet resultExistingCourseData = preparedStatementExistingCourseData .executeQuery(); if (!resultExistingCourseData.next()) { PreparedStatement preparedStatementCourseData = conn .prepareStatement("INSERT INTO project.coursedata " + "(courseid,practice,lecture,excercise,lecturesperweek,languageid,semester,teacherid) " + "VALUES (?,?,?,?,?,?,?,?)"); preparedStatementCourseData.setInt(1, courseId); preparedStatementCourseData.setInt(2, practice); preparedStatementCourseData.setInt(3, lecture); preparedStatementCourseData.setInt(4, exercise); preparedStatementCourseData.setFloat(5, (float) row.getCell(9).getNumericCellValue()); preparedStatementCourseData.setInt(6, (int) languageId); preparedStatementCourseData.setString(7, row.getCell(14).getStringCellValue()); preparedStatementCourseData.setInt(8, (int) teacherId); preparedStatementCourseData.executeUpdate(); int courseDataId = 0; PreparedStatement preparedStatementLastCourseData = conn .prepareStatement("SELECT id FROM project.coursedata where " + "courseid=? and practice=? and lecture=? and excercise=? and lecturesperweek=? and languageid=? and semester=? and teacherid=?"); preparedStatementLastCourseData.setInt(1, courseId); preparedStatementLastCourseData.setInt(2, practice); preparedStatementLastCourseData.setInt(3, lecture); preparedStatementLastCourseData.setInt(4, exercise); preparedStatementLastCourseData.setFloat(5, (float) row.getCell(9).getNumericCellValue()); preparedStatementLastCourseData.setInt(6, (int) languageId); preparedStatementLastCourseData.setString(7, row.getCell(14).getStringCellValue()); preparedStatementLastCourseData.setInt(8, (int) teacherId); ResultSet resultLastCourseData = preparedStatementLastCourseData .executeQuery(); if (resultLastCourseData.next()) { courseDataId = resultLastCourseData.getInt(1); String[] groupCodes = row.getCell(4).getStringCellValue().split(" "); for (int i = 0; i < groupCodes.length; i++) { PreparedStatement preparedStatementGroupCode = conn .prepareStatement( "SELECT * FROM project.group where groupcode=?"); preparedStatementGroupCode.setString(1, groupCodes[i]); ResultSet resultGroupCode = preparedStatementGroupCode .executeQuery(); if (!resultGroupCode.next()) { PreparedStatement preparedStatementGroup = conn .prepareStatement( "INSERT INTO project.group (groupcode) VALUES (?)"); preparedStatementGroup.setString(1, groupCodes[i]); preparedStatementGroup.executeUpdate(); PreparedStatement preparedStatementLastGroup = conn .prepareStatement( "SELECT id FROM project.group where groupcode=?"); preparedStatementLastGroup.setString(1, groupCodes[i]); int groupId = 0; ResultSet resultLastGroup = preparedStatementLastGroup .executeQuery(); if (resultLastGroup.next()) { groupId = resultLastGroup.getInt(1); PreparedStatement preparedStatementGroupCourseData = conn .prepareStatement( "INSERT INTO project.groupcoursedata (groupid,goursedataid) VALUES (?,?)"); preparedStatementGroupCourseData.setInt(1, groupId); preparedStatementGroupCourseData.setInt(2, courseDataId); preparedStatementGroupCourseData.executeUpdate(); } } } } } } } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } } } } catch (SQLException ioe) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ioe); } }