List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:comparator.Comparator.java
public static void translation() throws IOException { //Get the input files FileInputStream newMTC = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); String icdCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\CIM-10\\CIM10GM2014_S_FR_ClaML_2014.10.31.xml"; String atcCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\ATCcodes\\ATCDPP.CSV"; //Prepare the output file Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\CIM10-treated.csv"), "UTF-8")); csvW.write('\ufeff'); Writer csvW2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\ATC-treated.csv"), "UTF-8")); csvW2.write('\ufeff'); List<String> translationList = new ArrayList(); Map<String, String> translatList = new HashMap(); Map<String, String> translatAtcList = new HashMap(); Map<String, String> translatAtcList2 = new HashMap(); String codeTemp = ""; boolean prefered = false; InputStream ips = new FileInputStream(icdCodes); //Cp1252 --> ANSI InputStreamReader ipsr = new InputStreamReader(ips, "UTF-8"); BufferedReader br = new BufferedReader(ipsr); String ligne;// w w w. j a v a2 s . co m Pattern p1 = Pattern.compile("<Class code=\"(.+?)\""); Pattern p2 = Pattern.compile("xml:space=\"default\">(.+?)<"); Pattern p3 = Pattern.compile("(.+?)\\.."); Pattern pActiveIngredient = Pattern.compile("(?:.*;){8}\"(.+?)\";(?:.*;)\"(.+?)\";(?:.*;){5}.*"); Pattern pActiveIngredient2 = Pattern.compile("(?:.*;){4}\"(.+?)\";(?:.*;){5}\"(.+?)\";(?:.*;){5}.*"); Matcher m1; Matcher m2; Matcher m3; Matcher mActiveIngredient; Matcher mActiveIngredient2; while ((ligne = br.readLine()) != null) { m1 = p1.matcher(ligne); m2 = p2.matcher(ligne); if (ligne.matches("</Class>")) { prefered = false; codeTemp = ""; } if (m1.find()) { codeTemp = m1.group(1); } if (ligne.matches("(.*)kind=\"preferred\"(.*)")) { prefered = true; } if (m2.find() && prefered == true) { translatList.put(codeTemp, m2.group(1)); prefered = false; } //si traduction franais ET anglais if (ligne.matches(".*<FR_OMS>.*</FR_OMS>.*") && ligne.matches(".*<EN_OMS>.*</EN_OMS>.*")) { translationList.add(ligne.replace("\u00A0", " ")); } } br.close(); ips = new FileInputStream(atcCodes); //Cp1252 --> ANSI ipsr = new InputStreamReader(ips, "UTF-8"); br = new BufferedReader(ipsr); while ((ligne = br.readLine()) != null) { mActiveIngredient = pActiveIngredient.matcher(ligne); mActiveIngredient2 = pActiveIngredient2.matcher(ligne); if (mActiveIngredient.find()) { translatAtcList.put(mActiveIngredient.group(1), mActiveIngredient.group(2)); } if (mActiveIngredient2.find()) { translatAtcList2.put(mActiveIngredient.group(1), mActiveIngredient.group(2)); } } br.close(); //Get the workbook instance for XLS file XSSFWorkbook newMtcWorkbook = new XSSFWorkbook(newMTC); XSSFSheet newMtcSheet; Iterator<Row> newMtcRowIterator; Iterator<Cell> newMtcCellIterator; int newMtcCol; boolean newMtcColFound; ArrayList newMtcCodes; ArrayList newMtcCodes2; Row newMtcRow; Row newMtcRow2; Cell newMtcCell; //Get the sheet from the MTC workbook for (int i = 0; i < newMtcWorkbook.getNumberOfSheets(); i++) { newMtcSheet = newMtcWorkbook.getSheetAt(i); //Get iterator to all the rows in current MTC sheet newMtcRowIterator = newMtcSheet.iterator(); //And process the file matching or throw out the file that has no equivalent if (newMtcSheet.getSheetName().equals("VS21_IllnessesandDisorders")) { newMtcCol = 0; newMtcColFound = false; newMtcCodes = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (newMtcRowIterator.hasNext()) { newMtcRow = newMtcRowIterator.next(); if (newMtcColFound == false) { newMtcCellIterator = newMtcRow.cellIterator(); while (newMtcCellIterator.hasNext()) { newMtcCell = newMtcCellIterator.next(); if (newMtcCell.getCellType() == 1 && newMtcCell.getStringCellValue().equals("Code")) { newMtcCol = newMtcCell.getColumnIndex(); newMtcColFound = true; break; } } } else { newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim()); } } for (int j = 0; j < newMtcCodes.size(); j++) { csvW.write(newMtcCodes.get(j) + ";"); if (translatList.containsKey(newMtcCodes.get(j))) { csvW.write(translatList.get(newMtcCodes.get(j))); } else { m3 = p3.matcher((String) newMtcCodes.get(j)); if (m3.find() && translatList.containsKey(m3.group(1))) { csvW.write(translatList.get(m3.group(1))); } } /*for (int k=0; k<translationList.size(); k++) { String frTrad = ""; if (translationList.get(k).trim().contains("<EN_OMS>"+newMtcCodes.get(j)+"</EN_OMS>")) { Pattern p = Pattern.compile("<FR_OMS>(.+?)</FR_OMS>"); Matcher m = p.matcher(translationList.get(k).trim()); if (m.find()){ frTrad = m.group(1); translationList.remove(k); } csvW.write(StringUtils.capitalize(frTrad)); } }*/ csvW.write("\n"); } } else if (newMtcSheet.getSheetName().equals("VS3_ActiveIngredient")) { newMtcCol = 0; newMtcColFound = false; newMtcCodes = new ArrayList(); newMtcCodes2 = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (newMtcRowIterator.hasNext()) { newMtcRow = newMtcRowIterator.next(); newMtcRow2 = newMtcRow; if (newMtcColFound == false) { newMtcCellIterator = newMtcRow.cellIterator(); while (newMtcCellIterator.hasNext()) { newMtcCell = newMtcCellIterator.next(); if (newMtcCell.getCellType() == 1 && newMtcCell.getStringCellValue().equals("English Display Name")) { newMtcCol = newMtcCell.getColumnIndex(); newMtcColFound = true; break; } } } else { newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim()); newMtcRow2.getCell(newMtcCol - 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); newMtcCodes2.add(newMtcRow.getCell(newMtcCol - 1).getStringCellValue().trim()); } } for (int j = 0; j < newMtcCodes.size(); j++) { csvW2.write(newMtcCodes2.get(j) + ";"); csvW2.write(newMtcCodes.get(j) + ";"); if (translatAtcList.containsKey(newMtcCodes.get(j))) { csvW2.write(translatAtcList.get(newMtcCodes.get(j))); } else if (translatAtcList2.containsKey(newMtcCodes2.get(j))) { csvW2.write(translatAtcList.get(newMtcCodes.get(j))); } else { System.out.println(newMtcCodes.get(j)); } csvW2.write("\n"); } } } csvW.close(); csvW2.close(); newMTC.close(); }
From source file:controller.UploadExcelStudentsFile.java
public String readFromExcel(String path, MyPerson p) { String messages = ""; String ColumnsMesages = ""; try {//from w w w .ja v a 2 s . co m FileInputStream file; file = new FileInputStream(new File(path)); //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); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); int rowCount = 0; boolean isEverythingIsOK = true; while (rowIterator.hasNext()) { Row row = rowIterator.next(); rowCount++; System.out.println("rcount:" + rowCount); if (rowCount > 1) { //For each row, iterate through all the columns int Code = 0; String Fname = "", Lname = "", Email = ""; String password = ""; int userType = 0; int level = 0; String gender = ""; Iterator<Cell> cellIterator = row.cellIterator(); int ColumnCount = 0; int rowCountMesages = rowCount - 1; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); ColumnCount++; //Check the cell type and format accordingly System.out.println("ccount:" + ColumnCount); /* switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue() + ""); System.out.println("numeric type case:" + Cell.CELL_TYPE_NUMERIC);// numeric type case:0 System.out.println("type:" + cell.getCellType());//type:0 break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); System.out.print("string type case:" + Cell.CELL_TYPE_STRING + "");//string type case:0 System.out.println("type:" + cell.getCellType());//type:1 break; }*/ if (ColumnCount == 1) { if (cell.getCellType() == 0) { Code = (int) cell.getNumericCellValue(); password = String.valueOf(Code); System.out.println("Code:" + Code); System.out.println("pass:" + password); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'>First Column is Code must be an integer please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 2) { if (cell.getCellType() == 1) { Fname = cell.getStringCellValue(); System.out.println("Fname:" + Fname); } else { isEverythingIsOK = false; ColumnsMesages += " <font color='red'>Column number 2 is Fname must be a String please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 3) { if (cell.getCellType() == 1) { Lname = cell.getStringCellValue(); System.out.println("Lname:" + Lname); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'> Column number 3 is Lname must be a String please in student number (" + rowCountMesages + ")</font><br>"; } } /*else if(ColumnCount==4){ String Pass=cell.getStringCellValue(); System.out.println("Pass:"+Pass); }*/ else if (ColumnCount == 4) { if (cell.getCellType() == 1) { Email = cell.getStringCellValue(); System.out.println("Email:" + Email); } else { isEverythingIsOK = false; ColumnsMesages += " <font color='red'>Column number 4 is Email must be a String please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 5) { if (cell.getCellType() == 1) { gender = cell.getStringCellValue(); System.out.println("gender:" + gender); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'> Column number 5 is gender must be a String please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 6) { if (cell.getCellType() == 0) { userType = (int) cell.getNumericCellValue(); System.out.println("userType:" + userType); } else { isEverythingIsOK = false; ColumnsMesages += " <font color='red'> Column number 6 is userType must be an integer please in student number (" + rowCountMesages + ")</font><br/>"; } } else if (ColumnCount == 7) { if (cell.getCellType() == 0) { level = (int) cell.getNumericCellValue(); System.out.println("level:" + level); } else { isEverythingIsOK = false; ColumnsMesages += "<font color='red'> Column number 7 is level must be an integer please in student number (" + rowCountMesages + ")</font><br/>"; } } } //end of celIterator int rowAffected = 0; if (isEverythingIsOK) { try { SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss a"); String RegistrationDate = sdf1.format(new Date()); SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddhhmmss"); String MyUniversityCodeString = sdf2.format(new Date()) + p.getFaculityID() + Code; rowAffected = p.RegisterUser(Code, Fname, Lname, Email, password, userType, 1, gender, p.getFaculityID(), p.getUniversityID(), RegistrationDate, 1, MyUniversityCodeString); if (rowAffected > 0) { //response.sendRedirect("MyAccount.jsp?page=CourseViewPOSTContents&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+""); //response.sendRedirect("MyAccount.jsp?page=viewPost&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+""); messages += "<font color='blue'>Student(" + rowCountMesages + ")was Adding Successfully ^_^</font>" + "<br/>"; System.out.println( "<script type='text/javascript' > alert('Student was Adding Successfully ^_^ ');history.back();</script>"); } else { messages += "<font color='red'> Student(" + rowCountMesages + ")was Adding Failed ^_^</font>" + "<br/>"; System.out.println( "<script type='text/javascript' > alert('Student was Failed ^_^ ');history.back();</script>"); } } catch (Exception ex) { System.err.println("Add Students Error" + ex.getMessage()); messages += "<font color='red'>Adding Students Error" + ex.getMessage() + "</font><br/>"; messages += "<center><a href='index.jsp' >Home</a></center>"; } } //end of if IsEverythingIsOk Or Not System.out.println(""); } //end of if this is not first row } //end of while rowIterator file.close(); } //end of try catch (Exception e) { e.printStackTrace(); messages += "<font color='red'>" + e.getMessage() + "</font><br/>"; } messages += ColumnsMesages; return messages; }
From source file:Dao.DAO_Index.java
public void upload(InputStream fis, int numSheet) throws IOException, ParseException, Exception { SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm"); tiketDAO = new DAO_Tiket(); // tiketWilayahDAO = new DAO_TiketWilayah(); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(numSheet); Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from ww w . j ava2 s .c om // System.out.println(row.getCell(5).toString()); // tiketWilayahDAO.insert(row); tiketDAO.insert(row); } // tiketWilayahDAO.display(); tiketDAO.display(); }
From source file:Dao.XlsBillDao.java
public ArrayList<WorkItemBean> ReadXLS(File f) { WorkDao wdao = new WorkDao(); FileInputStream fis = null;/*from w ww. j a v a2 s . c o m*/ ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>(); try { String pono = null; XSSFRow row = null; //fis = new FileInputStream(new File("D:\\WO-2015-2008.xlsx")); fis = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); int i = 0; while (rowIterator.hasNext()) { i++; row = (XSSFRow) rowIterator.next(); if (i == 1) { Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { pono = cell.getStringCellValue(); pono = pono.substring((pono.indexOf(":") + 1)); } } } if (i > 2) { WorkItemBean bean = new WorkItemBean(); // System.out.println("ROW" + i); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= 0) { //System.out.print("COLUMN"); if (cell.getColumnIndex() == 1) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setITEM_ID(String.valueOf(cell.getNumericCellValue())); } else { bean.setITEM_ID(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 2) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); bean.setITEM_DESC(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 3) { //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); bean.setUOM(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 4) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setQTY((float) cell.getNumericCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setQTY(Float.parseFloat(cell.getStringCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 5) { //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setRATE((float) cell.getNumericCellValue()); } else { bean.setRATE(Float.parseFloat(cell.getStringCellValue())); } } else if (cell.getColumnIndex() == 7) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setPLANT(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setPLANT(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 8) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setCC(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setCC(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else if (cell.getColumnIndex() == 9) { if (wdao.isProjWO(pono)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setPROJ(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setPROJ(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { bean.setPROJ("-"); } } else if (cell.getColumnIndex() == 10) { if (wdao.isProjWO(pono)) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setTASK(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setTASK(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { bean.setTASK("-"); } } else if (cell.getColumnIndex() == 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setCMT(Integer.toString((int) cell.getNumericCellValue())); //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t "); } else { bean.setCMT(cell.getStringCellValue()); //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t "); } } else { } } } //System.out.println(); itm.add(bean); } } Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "READ DONE !!"); fis.close(); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } finally { try { fis.close(); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } } for (WorkItemBean i : itm) { if (i.getQTY() != 0) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.INFO, "ITEM_ID : {0} QTY:{1} PLANT:{2} CC:{3}", new Object[] { i.getITEM_ID(), i.getQTY(), i.getPLANT(), i.getCC() }); } } return itm; }
From source file:Dao.XlsWoDao.java
public ArrayList<WorkItemBean> ReadXLS(File f) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "READ XLS CALLED"); FileInputStream fis = null;// w ww . j a v a2 s . c o m ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>(); try { XSSFRow row = null; // fis = new FileInputStream(new File("D:\\CreateWO_Tmp.xlsx")); fis = new FileInputStream(f); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); int i = 0; while (rowIterator.hasNext()) { // System.out.println("NEW ROW"); i++; row = (XSSFRow) rowIterator.next(); if (i == 1) { Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 0) { } } } if (i > 3) { WorkItemBean bean = new WorkItemBean(); // System.out.println("ROW" + i); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { // System.out.println("NEW COLUMN"); Cell cell = cellIterator.next(); if (cell.getColumnIndex() >= 0) { // System.out.print("COLUMN"); if (cell.getColumnIndex() == 0) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setITEM_ID(String.valueOf(t)); } else { bean.setITEM_ID(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 1) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { bean.setRATE((float) cell.getNumericCellValue()); } else { bean.setRATE(Float.parseFloat(cell.getStringCellValue())); } } else if (cell.getColumnIndex() == 2) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setPLANT(String.valueOf(t)); } else { bean.setPLANT(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 3) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setPROJ(String.valueOf(t)); } else { bean.setPROJ(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 4) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setTASK(String.valueOf(t)); } else { bean.setTASK(cell.getStringCellValue()); } } else if (cell.getColumnIndex() == 5) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { int t = (int) cell.getNumericCellValue(); bean.setCMT(String.valueOf(t)); } else { bean.setCMT(cell.getStringCellValue()); } } } } // if (bean.getITEM_ID() != null || !"".equals(bean.getITEM_ID())) { itm.add(bean); // } } } } catch (FileNotFoundException ex) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } finally { try { fis.close(); } catch (IOException ex) { Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } } return itm; }
From source file:DB.TopStockDescriptionList.java
public static List<TopStockDescription> readFromFileExcel(String fileName) { List<TopStockDescription> tempTSD = new ArrayList(); String longName = ""; String shortName = ""; String explanation = ""; try {// www .jav a 2 s .c om ForcastUi.consoleLog("Opening filename: " + fileName); FileInputStream fIP = openExcelFileOrCreate(fileName); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row; while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); // ? Cell cell = cellIterator.next(); longName = cell.getStringCellValue(); if (cellIterator.hasNext()) { cell = cellIterator.next(); shortName = cell.getStringCellValue(); if (shortName.isEmpty()) continue; } else continue; if (cellIterator.hasNext()) { cell = cellIterator.next(); explanation = cell.getStringCellValue(); if (shortName.isEmpty()) continue; } else continue; tempTSD.add(new TopStockDescription(shortName.trim(), longName.trim(), explanation.trim(), false)); } fIP.close(); } catch (FileNotFoundException e) { ErrorMessages.printErrorMsg(ErrorMessages.FILENOTFOUND, fileName); ForcastUi.consoleLog(e.getMessage()); e.printStackTrace(); } catch (IOException e) { ErrorMessages.printErrorMsg(ErrorMessages.FILECOR, fileName); ForcastUi.consoleLog(e.getMessage()); e.printStackTrace(); } catch (Exception ex) { ErrorMessages.printErrorMsg(ErrorMessages.FILECOR, fileName); ForcastUi.consoleLog(ex.getMessage()); Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex); } return tempTSD; }
From source file:de.prokimedo.service.IcdServiceImpl.java
public List readExcel(MultipartFile file) { List<Icd> listIcd = new ArrayList(); try {/*w w w. j av a 2 s .c o m*/ File inputWorkbook = convert(file); FileInputStream fis = new FileInputStream(inputWorkbook); // Finds the workbook instance for XLSX file 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 while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (!row.getCell(0).toString().equals("Diagnose")) { Icd icd = new Icd(row.getCell(1).toString(), row.getCell(0).toString(), row.getCell(2).toString()); listIcd.add(icd); } } inputWorkbook.delete(); } catch (IOException ex) { Logger.getLogger(MedikamentServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } // try { // // File inputWorkbook = convert(file); // // Workbook w; // try { // w = Workbook.getWorkbook(inputWorkbook); // // Get the first sheet // Sheet sheet = w.getSheet(0); // //loop over first 10 column and lines // // for (int i = 1; i < sheet.getRows(); i++) { // // for (int j = 1; j < sheet.getColumns(); j++) { // Icd icd = new Icd(sheet.getCell(1, i).getContents(), sheet.getCell(0, i).getContents(), sheet.getCell(2, i).getContents()); // listIcd.add(icd); // // } // // } catch (BiffException e) { // System.out.println("BiffException"); // } catch (IOException ex) { // Logger.getLogger(ImageServiceImpl.class.getName()).log(Level.SEVERE, null, ex); // } // inputWorkbook.delete(); // // } catch (IOException | IndexOutOfBoundsException ex) { // Logger.getLogger(ImageServiceImpl.class.getName()).log(Level.SEVERE, null, ex); // } return listIcd; }
From source file:de.prokimedo.service.MedikamentServiceImpl.java
public List readExcel(MultipartFile file) { List<Medikament> listMed = new ArrayList(); try {/*from w w w . j a va 2s . c o m*/ File inputWorkbook = convert(file); FileInputStream fis = new FileInputStream(inputWorkbook); // Finds the workbook instance for XLSX file 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 while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (!row.getCell(1).toString().equals("PZN")) { Medikament med = new Medikament(null, row.getCell(2).toString(), row.getCell(1).toString(), row.getCell(4).toString(), row.getCell(6).toString(), row.getCell(3).toString(), row.getCell(7).toString()); listMed.add(med); } } inputWorkbook.delete(); } catch (IOException ex) { Logger.getLogger(MedikamentServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } return listMed; }
From source file:de.tum.in.socket.server.ReadExcel.java
License:Apache License
/** * Loads the retrieved data from excel to the list *///from w ww .ja v a 2 s.c o m private static List<RealtimeData> loadDataToList(final XSSFSheet sheet, final List<RealtimeData> data, final RealtimeData bluetoothData) throws InstantiationException, IllegalAccessException, SecurityException, NoSuchFieldException { final Row column = sheet.getRow(0); final String columnNames[] = getColumnNames(column); final Iterator<Row> rowIterator = sheet.iterator(); int rowOne = 0; while (rowIterator.hasNext()) { final Row row = rowIterator.next(); final Iterator<Cell> cellIterator = row.cellIterator(); final RealtimeData newRecord = bluetoothData.getClass().newInstance(); if (rowOne > 0) { int i = 0; while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); final String columnName = columnNames[i++]; final Field f1 = bluetoothData.getClass().getDeclaredField(columnName.trim()); f1.setAccessible(true); f1.set(newRecord, getTypeValue(f1.getType(), cell)); } data.add(newRecord); } rowOne++; } return data; }
From source file:edms.core.Config.java
License:Open Source License
public static void convertToXlsx(InputStream inStream, java.io.File outputFile) { // For storing data into CSV files StringBuffer cellValue = new StringBuffer(); try {// w w w . j a v a2 s . c om FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(inStream); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // 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: cellValue.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellValue.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellValue.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellValue.append("" + ","); break; default: cellValue.append(cell + ","); } } } fos.write(cellValue.toString().getBytes()); fos.close(); } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } }