List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:es.SSII2.manager.ExcelManagerMail.java
public void actualizarDnis() throws IOException { int row;//from w w w. jav a 2s . c o m int col = 15; FileInputStream file; file = new FileInputStream(new File(excel)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); for (int i = 0; i < arrayWorkers.size(); i++) { row = i; XSSFRow rowEmail = sheet.getRow(row + 1); //coje la fila XSSFCell cellEmail = rowEmail.createCell(col); //crea la celda cellEmail.setCellValue(arrayWorkers.get(i).getEmail()); //escribe en el excel try (FileOutputStream outFile = new FileOutputStream(new File(excel))) { workbook.write(outFile); } } //for }
From source file:eventHandlers.CompareDrotVSRoster.java
public void colorXLSXFile(String leaveXlsxRoster) throws FileNotFoundException, IOException { File xlsxFile = new File(leaveXlsxRoster); try {//from w ww. j a v a 2 s . c o m FileInputStream fis = new FileInputStream(xlsxFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; // List<String> keyList = new ArrayList<>(); //keep track info of each column while (it.hasNext()) { //keyList.add(it.next().getStringCellValue()); it.next(); numCell++; } if (numCell == GlobalVar.SIGNED_LEAVE_TITLES.length) { // correct xlsx file int rowNum = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //row.getRowStyle(); rowNum++; } for (int i = 1; i < rowNum; i++) { Row row = mySheet.getRow(i); foregroundColorSetUp(row, myWorkBook); //check each row and update foreground color } fis.close(); FileOutputStream output; String targetFile = null; String[] names = leaveXlsxRoster.split("\\."); if (!names[0].equals(leaveXlsxRoster)) { //doesn't contain surfix targetFile = names[0] + "COLORED.xlsx"; } else { targetFile = leaveXlsxRoster + "COLORED.xlsx"; } output = new FileOutputStream(targetFile); myWorkBook.write(output); output.close(); //myWorkBook.write(output); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); } }
From source file:eventHandlers.XLSX2BatchHandler.java
public void exportBatchFile(String xlsxFileName, String batchFileName) throws IOException { File myFile = new File(xlsxFileName); FileOutputStream outputFile = new FileOutputStream(batchFileName); //allow to append PrintStream output = new PrintStream(outputFile); try {//www. ja v a2 s . com FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; List<String> keyList = new ArrayList<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // check if the excel is the leave roster int globalCount = 1; MyDate soDate = new MyDate(); MyDate siDate = new MyDate(); while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<String, String>(); int keys = 0; //index of the cell // reset date every row Boolean soDateAssigned = false; Boolean siDateAssigned = false; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int type = cell.getCellType(); // distinguish the cell content, in the xlsx file, // SSN and dates are CELL_TYPE_NUMERIC // number of leave days is CELL_TYPE_FORMULA // the rest is CELL_TYPE_STRING if (type == HSSFCell.CELL_TYPE_NUMERIC && keys != GlobalVar.LAST4_CELL_INDEX_V1) { // dates DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String dateString = df.formatCellValue(cell); if (!soDateAssigned && !siDateAssigned) { //System.out.println(dateString); //150201 soDate = new MyDate(dateString); soDateAssigned = true; } else if (soDateAssigned && !siDateAssigned) { siDate = new MyDate(dateString); siDateAssigned = true; } else { // do nothing //System.out.println("We should never come here!"); } //String value = (int) cell.getNumericCellValue() + ""; // number of days container.put(keyList.get(keys), dateString); // System.out.println(value); // } else if (type == HSSFCell.CELL_TYPE_STRING // && keys == GlobalVar.SSN_CELL_INDEX){ } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** // String lastFour = cell.getStringCellValue(); //ssnString = ssnString.replace("-",""); lastFour = GlobalVar.last4Generator(lastFour); // System.out.println("SSN: " + lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); // } else if (type == HSSFCell.CELL_TYPE_FORMULA) { // String days = soDate.getDaysDifftoString(siDate); // container.put(keyList.get(keys),days); // //reset the dates } else if (type == HSSFCell.CELL_TYPE_STRING) { String value; if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { String str2 = cell.getStringCellValue(); //System.out.println(str2); value = GlobalVar.getDMOLeaveArea(str2); //System.out.println(value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); //System.out.println(value); } else { value = cell.getStringCellValue(); } container.put(keyList.get(keys), value); } keys++; } // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1)); String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V1)); String lastFour = container.get(keyList.get(GlobalVar.LAST4_CELL_INDEX_V1)); // last four // lastName = DB.getSSN(lastName, lastFour); String signOutDate = container.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1)); String signInDate = container.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1)); String leaveArea = container.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V1)); String leaveType = container.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V1)); //Map<String, String> thisMap = DB.get(thislastName, lastFour); String SSN = DB.getSSN(lastName, lastFour); //System.out.println("xlsx2batchHandler.java" + SSN + "+" + lastName); String first5 = DB.getFirst5(lastName, lastFour); String leaveDays = GlobalVar.computeNumOfDays(new MyDate(signOutDate), new MyDate(signInDate)); int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, leaves); if (leaveMsg == GlobalVar.GOOD_LEAVE) { String lc = GlobalVar.LC; String inputSource = ctrlNum.substring(0, 2); String cycle = GlobalVar.CYCLE; String data = "0SB03" + SSN + first5 + signOutDate + signInDate + leaveType + leaveDays + leaveArea + ctrlNum + "000" + GlobalVar.whiteSpace() + lc + inputSource + cycle + "@" + GlobalVar.globalCountGenerator(globalCount); output.println(data); globalCount++; } else if (leaveMsg == GlobalVar.OVERLAP_LEAVE) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); String msg = "Line" + lineCount + " : " + ctrlNum + " " + lastName + " " + signOutDate + " - " + signInDate; JOptionPane.showMessageDialog(null, msg + "\n is overlapping with a leave posted before."); } else if (leaveMsg == GlobalVar.DUPLICATE_CTRL_NUM) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); JOptionPane.showMessageDialog(null, "Line" + lineCount + " : Duplicate control number " + ctrlNum + " for " + lastName + "."); } } fis.close(); output.close(); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Excel.ExcelToJTable.java
static void fillData(File file) { try {//w ww . j a v a2s.co m FileInputStream fs = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(fs); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() == 0) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); columns.add(cell.getStringCellValue()); } } else { dataTemp = new Vector(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: dataTemp.add(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: dataTemp.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: dataTemp.add(""); break; default: break; } } data.add(dataTemp); fs.close(); } } } catch (Exception ex) { System.out.print("Cause: \n" + ex.getCause() + "\n Message: \n" + ex.getMessage() + "\n Stack Trace: \n" + Arrays.toString(ex.getStackTrace())); } }
From source file:excel2sql.util.ExcelReader.java
public ArrayList<String> read(int pageIndex) { ArrayList<String> queryes = new ArrayList<String>(); boolean first = true; ArrayList<String> params = new ArrayList<String>(); ArrayList<String> values = new ArrayList<String>(); try {/*from w w w. jav a2 s . c o m*/ FileInputStream file = new FileInputStream(new File(path)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(pageIndex); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (first) { params.add(String.valueOf((int) Math.floor(cell.getNumericCellValue()))); } else { values.add(String.valueOf((int) Math.floor(cell.getNumericCellValue()))); } break; case Cell.CELL_TYPE_STRING: if (first) { params.add(cell.getStringCellValue()); } else { values.add(cell.getStringCellValue()); } break; } } String[] stockParams = new String[params.size()]; stockParams = params.toArray(stockParams); String[] stockValues = new String[values.size()]; stockValues = values.toArray(stockValues); values.clear(); if (!first) { queryes.add(QueryBuilder.getQuery(sheet.getSheetName(), stockParams, stockValues)); } first = false; } file.close(); return queryes; } catch (IOException ioe) { ioe.printStackTrace(); } return null; }
From source file:excelcon.ExcelCon.java
@Override public void start(Stage primaryStage) throws FileNotFoundException, IOException { Button btn = new Button(); Button saveB = new Button(); saveB.setText("Save"); Button run = new Button(); run.setText("Run"); TextArea t = new TextArea(); TextArea saveP = new TextArea(); t.setMaxHeight(1);// w ww .j a va2 s . c om t.setWrapText(true); t.setMaxWidth(300); saveP.setMaxHeight(1); saveP.setWrapText(true); saveP.setMaxWidth(300); Label into = new Label(); Label don = new Label(); btn.setText("Browse"); FileChooser chooser = new FileChooser(); chooser.setTitle("Choose"); FileChooser.ExtensionFilter ef = new ExtensionFilter("xlsx", "*.xlsx"); chooser.getExtensionFilters().add(ef); GridPane gridpane = new GridPane(); gridpane.setPadding(new Insets(5)); gridpane.setHgap(10); gridpane.setVgap(10); into.setText("Start by choosing \"xlsx\" file , press Browse\n " + "{TMX,ABO_Open,TMX_ABO_Open" + ",\nVac.,Caps,Inhibit,Sensor,Sensor_Clip,Color}"); t.setText("File Name"); DirectoryChooser dirCh = new DirectoryChooser(); dirCh.setTitle("Save Files"); //System.out.println(selectedDirectory.getPath()); String[][] names = new String[10][2]; names[0][0] = "TMX"; names[0][1] = "TMX_Of:"; names[1][0] = "ABO_Open"; names[2][0] = "ABO"; names[3][0] = "Vac."; names[4][0] = "Sensor_Clip"; names[5][0] = "Color"; names[6][0] = "Sensor"; names[7][0] = "TMX_ABO_Open"; names[8][0] = "Caps"; names[9][0] = "Inhibit"; names[1][1] = "ABO_Open_Of:"; names[2][1] = "ABO_Of:"; names[3][1] = "Vac._Of:"; names[4][1] = "Sensor_Clip_Of:"; names[5][1] = "Color_Of:"; names[6][1] = "Sensor_Of:"; names[7][1] = "TMX_ABO_Open_Of:"; names[8][1] = "Caps_Of:"; names[9][1] = "Inhibit_Of:"; btn.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent event) { try { File file = chooser.showOpenDialog(new Stage()); xls = new FileInputStream(file); t.setText(file.getPath()); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } finally { try { xls.close(); } catch (IOException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } } } }); saveB.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent event) { selectedDirectory = dirCh.showDialog(primaryStage); saveP.setText(selectedDirectory.getPath()); } }); run.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent event) { try { xls = new FileInputStream(t.getText()); XSSFWorkbook wb = new XSSFWorkbook(xls); int sheetNo = wb.getNumberOfSheets(); String error = ""; for (int i = 0; i < sheetNo; i++) { XSSFSheet sheet = wb.getSheetAt(i); error += done(names, sheet, selectedDirectory); } don.setText("if Error :\n" + error + "Done, check your files now"); } catch (IOException ex) { Logger.getLogger(ExcelCon.class.getName()).log(Level.SEVERE, null, ex); } } }); gridpane.add(into, 0, 0, 2, 1); gridpane.add(t, 0, 1); gridpane.add(btn, 1, 1); gridpane.add(saveP, 0, 2); gridpane.add(saveB, 1, 2); gridpane.add(run, 0, 3, 2, 1); gridpane.add(don, 0, 4, 2, 1); t.setVisible(true); StackPane root = new StackPane(); ScrollPane sp = new ScrollPane(); sp.setContent(gridpane); root.getChildren().add(sp); root.setBlendMode(BlendMode.MULTIPLY); Scene scene = new Scene(root, 450, 250, Color.ALICEBLUE); primaryStage.setTitle("Excel txt export"); primaryStage.setScene(scene); primaryStage.show(); }
From source file:ExcelRead.CrbRead.java
public void readFromExcel(String file, JTable table) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next();//from w w w . j a v a 2 s. c om Iterator<Cell> cells = row.iterator(); while (cells.hasNext()) { Cell cell = cells.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { old_reg_cod = String.valueOf((int) cell.getNumericCellValue()); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { old_reg_cod = cell.getStringCellValue(); break; } case 1: name = cell.getStringCellValue(); break; case 2: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { new_reg_cod = String.valueOf((int) cell.getNumericCellValue()); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { new_reg_cod = cell.getStringCellValue(); break; } default: System.out.print("|"); break; } } DefaultTableModel model = (DefaultTableModel) table.getModel(); String[] data = { old_reg_cod, name, new_reg_cod }; model.addRow(data); removeAllFields(); } }
From source file:ExcelRead.PatientRead.java
public void readFromExcel(String file, JTable table) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next();//from www . j ava2 s . c om Iterator<Cell> cells = row.iterator(); while (cells.hasNext()) { Cell cell = cells.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: ID = (int) cell.getNumericCellValue(); // table.setValueAt(ID, i, 0); break; case 1: String[] fullName = cell.getStringCellValue().split(" "); name = fullName[1]; surname = fullName[0]; middleName = fullName[2]; // table.setValueAt(cell.getStringCellValue(), i, 1); break; case 2: SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); birthdate = sdf.format(cell.getDateCellValue()); //table.setValueAt(birthdate, i, 2); break; case 3: sex = cell.getStringCellValue(); //table.setValueAt(sex, i, 3); break; case 4: address = cell.getStringCellValue(); // table.setValueAt(address, i, 4); break; case 5: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { lpu_id = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(lpu_id, i, 5); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { lpu_id = cell.getStringCellValue(); //table.setValueAt(lpu_id, i, 5); break; } case 6: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { crb_id = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(crb_id, i, 6); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { crb_id = cell.getStringCellValue(); // table.setValueAt(crb_id, i, 6); break; } case 7: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { snils = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(snils, i, 7); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { snils = cell.getStringCellValue(); //table.setValueAt(snils, i, 7); break; } case 8: String[] passport = cell.getStringCellValue().split(" "); //table.setValueAt(cell.getStringCellValue(), i, 8); pass_ser = passport[0]; pass_num = passport[1]; break; case 9: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { old_police = String.valueOf((int) cell.getNumericCellValue()); // table.setValueAt(old_police, i, 9); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { old_police = cell.getStringCellValue(); // table.setValueAt(old_police, i, 9); break; } case 10: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { new_police = String.valueOf((int) cell.getNumericCellValue()); //table.setValueAt(new_police, i, 10); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { new_police = cell.getStringCellValue(); //table.setValueAt(new_police, i, 10); break; } case 11: if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { phoneNum = String.valueOf((int) cell.getNumericCellValue()); //table.setValueAt(phoneNum, i, 11); break; } if (Cell.CELL_TYPE_STRING == cell.getCellType()) { phoneNum = cell.getStringCellValue(); //table.setValueAt(phoneNum, i, 11); break; } default: System.out.print("|"); break; } } DefaultTableModel model = (DefaultTableModel) table.getModel(); String[] data = { String.valueOf(ID), surname + " " + name + " " + middleName, birthdate, sex, address, lpu_id, crb_id, snils, pass_ser + " " + pass_num, old_police, new_police, phoneNum }; model.addRow(data); removeAllFields(); } }
From source file:ExcelReadFile.ExcellReadSpecialite.java
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException { List<Specialite> listSpecialite = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(excelFilePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next();//w w w. j a v a 2 s . com Iterator<Cell> cellIterator = nextRow.cellIterator(); Specialite sp = new Specialite(); Admin admin = new Admin(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: sp.setIntitule((String) getCellValue(nextCell)); break; } } listSpecialite.add(sp); } file.close(); return listSpecialite; }
From source file:ExcelReadFile.ExcelReadMedecin.java
public List<Medecin> readBooksFromExcelFile(String excelFilePath) { List<Medecin> listMed = null; try {/* w w w. j a v a 2s. c o m*/ listMed = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(excelFilePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); Medecin med = new Medecin(); Specialite s = new Specialite(); Admin ad = new Admin(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: ad.setId_admin(((Integer) getCellValue(nextCell))); med.setAdmin(serviceAdmin.findAdminById(ad.getId_admin())); break; case 1: s.setId_specialite((((Integer) getCellValue(nextCell)).intValue())); med.setSpecialite(serviceSpecialite.findSpecialiteById(s.getId_specialite())); break; case 2: med.setAdresse((String) getCellValue(nextCell)); break; case 3: med.setDate_naissance((String) getCellValue(nextCell)); break; case 4: med.setLogin((String) getCellValue(nextCell)); break; case 5: med.setNom((String) getCellValue(nextCell)); break; case 6: med.setNumero_telephone((String) getCellValue(nextCell)); break; case 7: med.setPassword((String) getCellValue(nextCell)); break; case 8: med.setPrenom((String) getCellValue(nextCell)); break; } } listMed.add(med); } file.close(); } catch (IOException e) { e.printStackTrace(); } return listMed; }