List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:service.EventService.java
public void readXls(MultipartFile fileXls, Long[] tagIds, Long pkId, Long campaignId, Boolean update) throws Exception { Campaign campaign = campaignDao.find(campaignId); //List<String> uniqs = campaignDao.getUniqs(campaignId, pkId); if (!Objects.equals(campaign.getStatus(), Campaign.CLOSE)) { PersonalCabinet pk = personalCabinetDao.find(pkId); HashMap<String, String> commentMap = new HashMap(); List<Client> clientsListForSave = new ArrayList(); List<Client> clientsListForUpdate = new ArrayList(); List<Event> eventsListForSave = new ArrayList(); HashMap<String, Client> addedInPkClientsMap = getClientsMapInPk(pkId); HashMap<String, Client> addedInCampaignClientsMap = getClientsMapInCampaign(campaignId, pkId); //HashMap<String, Client> addingClientsMapForSave = new HashMap(); List<Client> noContactList = new ArrayList(); List<Integer> noUniqueIdList = new ArrayList(); InputStream fis = fileXls.getInputStream(); HSSFWorkbook inputWorkbook = new HSSFWorkbook(fis); int sheetCount = inputWorkbook.getNumberOfSheets(); boolean nomoreclients = false; for (int i = 0; i < sheetCount; i++) { HSSFSheet hss = inputWorkbook.getSheetAt(i); int rowCount = 0; Iterator<Row> it = hss.iterator(); while (it.hasNext()) { rowCount++;// w w w.j a v a 2s .c om Row rw = it.next(); if (!(StringAdapter.getString(rw.getCell(0))).trim() .equals("? ")) { String uid = StringAdapter.HSSFSellValue(rw.getCell(0)); String name = StringAdapter.HSSFSellValue(rw.getCell(1)); String secretaryPhone = HSSFPhoneValue(rw.getCell(2)); String comment = StringAdapter.HSSFSellValue(rw.getCell(3)); String contface = StringAdapter.HSSFSellValue(rw.getCell(4)); String lprPhone = HSSFPhoneValue(rw.getCell(5)); String namelpr = StringAdapter.HSSFSellValue(rw.getCell(6)); String adress = StringAdapter.HSSFSellValue(rw.getCell(7)); if (uid.equals("") && (!name.equals("") || !secretaryPhone.equals("") || !lprPhone.equals(""))) { noUniqueIdList.add(rowCount); } else if (!uid.equals("") && !name.equals("") && (!secretaryPhone.equals("") || !lprPhone.equals(""))) { // if (!addedInPkClientsMap.keySet().contains(uid)) { Client cl = new Client(); cl.setUniqueId(uid); cl.setNameCompany(name); cl.setNameSecretary(contface); cl.setNameLpr(namelpr); cl.setPhoneSecretary(secretaryPhone); cl.setPhoneLpr(lprPhone); cl.setAddress(adress); commentMap.put(uid, comment); cl.setCabinet(pk); if (validate(cl)) { if ((secretaryPhone != null && !secretaryPhone.equals("")) || (lprPhone != null && !lprPhone.equals(""))) { if (adminService.mayAddClient(pkId)) { clientsListForSave.add(cl); } else { nomoreclients = true; } } else { noContactList.add(cl); } } } else { Client cl = addedInPkClientsMap.get(uid); if (update) { cl.setUniqueId(uid); cl.setNameCompany(name); cl.setNameSecretary(contface); cl.setNameLpr(namelpr); cl.setPhoneSecretary(secretaryPhone); cl.setPhoneLpr(lprPhone); cl.setAddress(adress); commentMap.put(uid, comment); cl.setCabinet(pk); if (validate(cl)) { if ((secretaryPhone != null && !secretaryPhone.equals("")) || (lprPhone != null && !lprPhone.equals(""))) { clientsListForUpdate.add(cl); } else { noContactList.add(cl); } } } // ?, if (!addedInCampaignClientsMap.keySet().contains(uid)) { Event event = new Event(); event.setCabinet(pk); event.setClient(cl); event.setUniqueId(uid); event.setCampaign(campaign); event.setComment(StringAdapter.getString(commentMap.get(cl.getUniqueId()))); event.setStatus(Event.UNASSIGNED); if (validate(event)) { eventsListForSave.add(event); } } } } } } if (nomoreclients) { addError( " ? ?? ? ? "); } } if (noContactList.isEmpty() && noUniqueIdList.isEmpty()) { for (Client cl : clientsListForSave) { clientDao.save(cl); if (tagIds != null && tagIds.length > 0) { tagService.addTagsToClient(cl.getId(), tagIds, pkId); } Event event = new Event(); event.setCabinet(pk); event.setClient(cl); event.setUniqueId(cl.getUniqueId()); event.setCampaign(campaign); event.setComment(StringAdapter.getString(commentMap.get(cl.getUniqueId()))); event.setStatus(Event.UNASSIGNED); if (validate(event)) { eventsListForSave.add(event); } } for (Client cl : clientsListForUpdate) { clientDao.update(cl); if (tagIds != null && tagIds.length > 0) { tagService.addTagsToClient(cl.getId(), tagIds, pkId); } } for (Event ev : eventsListForSave) { eventDao.save(ev); addEventComment(" ", EventComment.CREATE, ev, pkId); addEventComment(": " + ev.getComment(), EventComment.COMMENTED, ev, pkId); } } else { if (!noContactList.isEmpty()) { String err = "? ? : "; for (Client cl : noContactList) { err += cl.getUniqueId() + "; "; } addError(err + " ? ? ."); } if (!noUniqueIdList.isEmpty()) { String err = "? ? ?: "; for (Integer rc : noUniqueIdList) { err += rc + "; "; } addError(err); } } } else { addError("?? ."); } }
From source file:soc.scar.service.excel.ProjectExcelService.java
public void load() throws IOException { featuresTypeList = new ArrayList<>(); featuresList = new ArrayList<>(); modulesList = new ArrayList<>(); checkNextFeatureType = false;/*from w w w .j a v a2 s . c o m*/ try { file = new FileInputStream(new File( "/Users/migueljimenezromero/NetBeansProjects/scarProject/scar/src/main/webapp/data/prueba.xls")); //Get the workbook instance for XLS file workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheet("Configuracin de alcance"); //Iterate through each rows from first sheet rowIterator = sheet.iterator(); getFeatureType(); file.close(); } catch (FileNotFoundException ex) { Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:StockForecast.Stock.java
private void converttoraw() throws Exception { File fileTxt = new File("RawData.txt"); FileInputStream file = new FileInputStream(new File("RawData.xls")); PrintWriter writer = new PrintWriter(fileTxt); writer.print(""); writer.close();//from w ww . j a v a 2 s . co m FileWriter fw = new FileWriter(fileTxt.getAbsoluteFile()); BufferedWriter bw = new BufferedWriter(fw); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); 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: // System.out.print(cell.getNumericCellValue() + ","); bw.write(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + ","); bw.write(cell.getStringCellValue() + ","); break; } } bw.write("\n"); } bw.close(); file.close(); fw.close(); }
From source file:temp.ExcelReader.java
public static void readFromFile(File file) { try {/*from w ww.ja va 2 s. c o m*/ // XSSFWorkbook workbookXls; // can load up every sheet as previews HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file)); System.out.println("Number of sheets:" + workbook.getNumberOfSheets()); //say I want sheet 0 HSSFSheet sheet = workbook.getSheetAt(5); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // Iterator<Cell> cellI = row.cellIterator(); // // while(cellI.hasNext()){ // Cell cell = cellI.next(); // //This is wrong -> the blank cell should not return 0! // //use toString would work; but it could cause the formulas to go wrong // // // // System.out.print(cell + "|"); // } for (int i = 0; i < row.getLastCellNum(); i++) { System.out.print(row.getCell(i) + "|"); //this you won't miss any cells! right way to do } System.out.println(""); } } catch (FileNotFoundException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Test.LeerExcel.java
public static void main(String args[]) throws IOException { FileInputStream file = new FileInputStream(new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\")); // Crear el objeto que tendra el libro de Excel HSSFWorkbook workbook = new HSSFWorkbook(file); /*//from w w w .j a va 2 s .c o m * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda while (rowIterator.hasNext()) { row = rowIterator.next(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; while (cellIterator.hasNext()) { celda = cellIterator.next(); // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celda)) { System.out.println(celda.getDateCellValue()); } else { System.out.println(celda.getNumericCellValue()); } System.out.println(celda.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(celda.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(celda.getBooleanCellValue()); break; } } } // cerramos el libro excel workbook.close(); }
From source file:update2viva.ConvertXLSX.java
static void readXls(File inputFile) { try {//from w w w. j a v a2 s. c o m // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Cell cell; Row row; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK: System.out.println(" "); break; default: System.out.println(cell); } } } } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } }
From source file:uploads.uploadAllFacilities.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();// www . jav a 2 s . c o m dbConn conn = new dbConn(); checker_county = checker_district = ""; county_name = county_id = district_name = district_id = hf_name = hf_id = ""; checker_dist = checker_hf = mflcode = 0; file_source = new File("C:\\Users\\Geofrey Nyabuto\\Desktop\\hf\\supported_hf.xls"); System.out.println(" The file path is: " + file_source); FileInputStream fileInputStream = new FileInputStream(file_source); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("Sheet2"); Iterator rowIterator = worksheet.iterator(); int i = 0, y = 0; while (rowIterator.hasNext()) { county_name = county_id = district_name = district_id = hf_name = hf_id = ""; mflcode = 0; HSSFRow rowi = worksheet.getRow(i); HSSFCell cell1 = rowi.getCell((short) 2); county_name = cell1.getStringCellValue(); HSSFCell cell2 = rowi.getCell((short) 3); district_name = cell2.getStringCellValue(); HSSFCell cell3 = rowi.getCell((short) 1); hf_name = cell3.getStringCellValue(); HSSFCell cell4 = rowi.getCell((short) 0); mflcode = (int) cell4.getNumericCellValue(); district_name = district_name.toUpperCase(); county_name = county_name.toUpperCase(); IdGenerator IG = new IdGenerator(); // hf_id=IG.sec+""+IG.micro; String county_che = "SELECT * FROM county WHERE county_name=?"; conn.pst = conn.conn.prepareStatement(county_che); conn.pst.setString(1, county_name); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { county_id = conn.rs.getString(1); } System.out.println("county : " + county_id); if (county_id.length() == 0) { county_id = IG.current_id(); String county_inserter = "INSERT INTO county (county_name) VALUES (?)"; conn.pst = conn.conn.prepareStatement(county_inserter); conn.pst.setString(1, county_name); conn.pst.executeUpdate(); String getdistid = "SELECT max(county_id)FROM county"; conn.rs = conn.st.executeQuery(getdistid); if (conn.rs.next() == true) { county_id = conn.rs.getString(1); } } String check_dist = "SELECT * FROM district WHERE district_name=? && county_id=?"; conn.pst = conn.conn.prepareStatement(check_dist); conn.pst.setString(1, district_name); conn.pst.setString(2, county_id); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { district_id = conn.rs.getString(1); } System.out.println("district : " + district_id); if (district_id.length() == 0) { district_id = IG.current_id(); String dist_inserter = "INSERT INTO district (county_id,district_name) VALUES (?,?)"; conn.pst = conn.conn.prepareStatement(dist_inserter); conn.pst.setString(1, county_id); conn.pst.setString(2, district_name); conn.pst.executeUpdate(); String getdistid = "SELECT max(district_id)FROM district"; conn.rs = conn.st.executeQuery(getdistid); if (conn.rs.next() == true) { district_id = conn.rs.getString(1); } } // if(checker_dist>0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String check_hf = "SELECT COUNT(hf_id) FROM health_facility WHERE hf_name=? && district_id=?"; conn.prest = conn.conn.prepareStatement(check_hf); conn.prest.setString(1, hf_name); conn.prest.setString(2, district_id); conn.rs = conn.prest.executeQuery(); if (conn.rs.next() == true) { checker_hf = conn.rs.getInt(1); } if (checker_hf == 0) { // ADD THE NEW HEALTH FACILITY TO THE SYSTEM......................... hf_id = IG.current_id(); String inserter = "INSERT INTO health_facility (hf_id,hf_name,mflcode,district_id) " + " VALUES(?,?,?,?)"; conn.prest = conn.conn.prepareStatement(inserter); conn.prest.setString(1, hf_id); conn.prest.setString(2, hf_name); conn.prest.setInt(3, mflcode); conn.prest.setString(4, district_id); conn.prest.executeUpdate(); System.out.println("" + i + " added : " + hf_name); } else { System.out.println("HEALTH FACILITY AT POSITION : " + i + " AL READY ADDED : " + hf_name); } // } // else{ // System.out.println("MISSING DISTRICT AT POSITION : "+i+" for the district "+district_name); // } i++; } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } }
From source file:util.DocumentFunction.java
public static String readXlsFile(String filename) { StringBuilder text = new StringBuilder(); try {//from w w w .j a v a2 s .c o m FileInputStream file = new FileInputStream(new File(filename)); //Create Workbook instance holding reference to .xlsx file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first/desired sheet from the workbook for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); boolean breakPoint = true; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: text.append(cell.getNumericCellValue() + " "); break; case Cell.CELL_TYPE_STRING: text.append(cell.getStringCellValue() + " "); break; case Cell.CELL_TYPE_BLANK: breakPoint = false; break; } } if (breakPoint) { text.append("\n"); } } } file.close(); } catch (Exception e) { e.printStackTrace(); } return text.toString(); }