List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:data.services.SequenceService.java
public void updateFromXml(File fl, Long seqId) throws Exception { try {/* w w w. ja va2 s. com*/ Sequence seq = sequenceDao.find(seqId); if (seq != null) { FileInputStream fi = new FileInputStream(fl); int i = 0; int m = 1; try { List<SequenceValueRange> newSVrList = new ArrayList(); HSSFWorkbook workbook = new HSSFWorkbook(fi); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> it = sheet.iterator(); it.next(); while (it.hasNext()) { m++; Row row = it.next(); Cell uidc = row.getCell(0); String uid = ""; if (uidc != null) { uid = StringAdapter.HSSFSellValue(uidc).replace(" ", "").trim(); if (uid.contains(".")) { int point = uid.indexOf("."); uid = uid.substring(0, point); } } Cell aminCell = row.getCell(1); Long amin = null; if (aminCell != null) { String supstr = StringAdapter.HSSFSellValue(aminCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { amin = StringAdapter.toLong(supstr); } } Cell minCell = row.getCell(2); Long min = null; if (minCell != null) { String supstr = StringAdapter.HSSFSellValue(minCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { min = StringAdapter.toLong(supstr); } } Cell maxCell = row.getCell(3); Long max = null; if (maxCell != null) { String supstr = StringAdapter.HSSFSellValue(maxCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { max = StringAdapter.toLong(supstr); } } Cell amaxCell = row.getCell(4); Long amax = null; if (amaxCell != null) { String supstr = StringAdapter.HSSFSellValue(amaxCell); if (supstr.contains(".")) { int point = supstr.indexOf("."); supstr = supstr.substring(0, point); } if (!supstr.equals("")) { amax = StringAdapter.toLong(supstr); } } BaseParam bp = baseParamService.getBaseParam(uid); if (bp != null) { SequenceValueRange vr = new SequenceValueRange(); vr.setUid(uid); vr.setName(bp.getName()); vr.setAmin(amin); vr.setValueMin(min); vr.setValueMax(max); vr.setAmax(amax); vr.setSequence(seq); if (validate(vr, "??? , ? " + m + "; ")) { newSVrList.add(vr); } } } List<SequenceValueRange> oldSVrList = seq.getSequenceValueRanges(); for (SequenceValueRange vr : oldSVrList) { sequenceValueRangeService.delete(vr.getId()); } for (SequenceValueRange vr : newSVrList) { sequenceValueRangeService.create(vr); } workbook.close(); } catch (Exception e) { //addError(StringAdapter.getStackTraceException(e) + "str:"+i+"; "); throw new Exception(StringAdapter.getStackTraceException(e) + "str: " + m + "; "); } fi.close(); } } catch (Exception e) { //addError(" xml"); //addError(e.getMessage()); throw new Exception(StringAdapter.getStackTraceException(e)); } }
From source file:de.chott.jfreechartsample.reader.FileReaderService.java
/** * Liest mit Hilfe der Apache-Poi-Library ein XLS-File aus und gibt die Daten darin als Liste von PieChartData-Objekten zurck. * //from ww w.j a v a 2 s. c o m * @param stream Das File als Resource-InputStream * @return eine Liste der PieChartData * @throws IOException */ public List<PieChartData> readPieChartDataFromXls(InputStream stream) throws IOException { List<PieChartData> retVal = new ArrayList<>(); HSSFWorkbook workbook = new HSSFWorkbook(stream); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); PieChartData data = new PieChartData(); Cell countryCell = row.getCell(0); Cell weightCell = row.getCell(1); if (countryCell != null && weightCell != null) { data.setCountry(countryCell.getStringCellValue()); data.setWeight(weightCell.getNumericCellValue()); retVal.add(data); } } return retVal; }
From source file:dias.m20150711_get_armband_data.java
public Matrix m20150711_get_armband_data() { eedouble = 0;// ww w . j a v a2 s .co m gsrdouble = 0; phys_actdouble = 0; sleepdouble = 0; try { FileInputStream file = new FileInputStream(new File(DIAS.bodymediaFileUrl)); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); int s = 0; int i = 0; int j = 0; int kx = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); i++; s = 0; while (cellIterator.hasNext()) { j++; org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: armband_data.set(i, j, cell.getNumericCellValue()); if (kx == 28) armband_data_with_time.set(i, 5, cell.getNumericCellValue()); // Heat-Flux Average if (kx == 27) armband_data_with_time.set(i, 4, cell.getNumericCellValue()); //Sleep Classification if (kx == 26) armband_data_with_time.set(i, 3, cell.getNumericCellValue()); //Activity Class if (kx == 25) armband_data_with_time.set(i, 2, cell.getNumericCellValue()); //Distance if (kx == 24) armband_data_with_time.set(i, 1, cell.getNumericCellValue()); //Speed if (kx == 23) armband_data_with_time.set(i, 0, cell.getNumericCellValue()); //MET 's kx++; break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equals("NAN")) { if (s == 0) { i--; s = 1; } } break; } } kx = 0; j = 0; } file.close(); s = 0; eedouble = 0; gsrdouble = 0; sleepdouble = 0; phys_actdouble = 0; eedouble = armband_data.get(7164, 18); gsrdouble = armband_data.get(7164, 14); sleepdouble = armband_data.get(7164, 16); phys_actdouble = armband_data.get(7164, 17); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return armband_data; }
From source file:edms.core.Config.java
License:Open Source License
public static void convertToXls(InputStream inStream, java.io.File outputFile) { // For storing data into CSV files StringBuffer cellDData = new StringBuffer(); try {//from w w w . jav a 2 s .c o m FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(inStream); // 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: cellDData.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellDData.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellDData.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellDData.append("" + ","); break; default: cellDData.append(cell + ","); } } } fos.write(cellDData.toString().getBytes()); fos.close(); } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } }
From source file:edu.rice.rems.Scheduler.java
License:Open Source License
/** * Extract shift schedule data from excel file * * @param xlsFile//from w w w . j ava 2 s. c o m * @return data */ private static ArrayList<ArrayList<String>> extractData(File xlsFile) { ArrayList<ArrayList<String>> data = new ArrayList<>(); try { FileInputStream file = new FileInputStream(xlsFile); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); ArrayList<String> newRow = new ArrayList<String>(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); newRow.add(cell.getStringCellValue()); } data.add(newRow); } file.close(); } catch (Exception e) { e.printStackTrace(); return null; } return data; }
From source file:eu.itesla_project.entsoe.util.BoundaryPointXlsParser.java
License:Mozilla Public License
public Map<String, BoundaryPoint> parse(InputStream is) throws IOException { Map<String, BoundaryPoint> boundaryPoints = new HashMap<>(); HSSFWorkbook workbook = new HSSFWorkbook(is); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); rowIterator.next();//www . j av a 2s . c o m rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell boundaryPointNameCell = row.getCell(13); Cell borderFromCell = row.getCell(14); Cell borderToCell = row.getCell(15); String boundaryPointName = boundaryPointNameCell.getStringCellValue(); if (boundaryPointName.equals("-")) { continue; } Country borderFrom = toCountry(borderFromCell.getStringCellValue()); Country borderTo = toCountry(borderToCell.getStringCellValue()); boundaryPoints.put(boundaryPointName, new BoundaryPoint(boundaryPointName, borderFrom, borderTo)); } return boundaryPoints; }
From source file:fr.amapj.service.engine.excelreader.ExcelReader.java
License:Open Source License
public List<String[]> readFile(String fileName, int nbCol) throws IOException { List<String[]> res = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(fileName)); HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); DataFormatter df = new DataFormatter(Locale.FRANCE); while (rowIterator.hasNext()) { Row row = rowIterator.next();/*from w w w .j a v a 2s . c o m*/ String[] strCell = new String[nbCol]; for (int i = 0; i < strCell.length; i++) { strCell[i] = getValue(row, i, df); } res.add(strCell); } file.close(); return res; }
From source file:fr.amapj.service.engine.generator.excel.samples.DiagonalBorder.java
License:Open Source License
/** * Permet la lecture d'un fichier excel et l'affichage des proprits de style *///from www.ja v a2s . c om private void readExcelFile() throws Exception { FileInputStream file = new FileInputStream(new File("test1.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell cell = row.getCell(0); System.out.println("Affichage des infos de la cellule 1 de la ligne " + i); displayCell(cell); i++; } file.close(); }
From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java
License:Open Source License
public static int getHSSFStartRow(String file, int sheet, int col, String code) { try {/* ww w . j a v a 2s . c o m*/ FileInputStream fis = new FileInputStream(new File(file)); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(fis); try { fis.close(); } catch (Exception ex) { ex.printStackTrace(); } //Get first sheet from the workbook HSSFSheet hSSFSheet = workbook.getSheetAt(sheet); if (col == -1) { return 1; } //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = hSSFSheet.iterator(); //Get iterator to all cells of current row int lcv = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row == null) return -1; //if (row.getCell(0).getStringCellValue().compareTo(code) == 0 || if (row.getCell(col).getStringCellValue().compareTo(code) == 0) { return lcv; } lcv++; } } catch (Exception ex) { ex.printStackTrace(); } return -1; }
From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java
License:Open Source License
public static int getHSSFEndRow(String file, int sheet, int col, String code) { int num = -1; try {/*from w ww.j a v a 2 s. c o m*/ FileInputStream fis = new FileInputStream(new File(file)); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(fis); try { fis.close(); } catch (Exception ex) { ex.printStackTrace(); } //Get first sheet from the workbook HSSFSheet hSSFSheet = workbook.getSheetAt(sheet); if (col == -1) { return hSSFSheet.getLastRowNum(); } //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = hSSFSheet.iterator(); //Get iterator to all cells of current row int lcv = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row == null) return -1; //if (row.getCell(0).getStringCellValue().compareTo(code) == 0 || if (row.getCell(col).getStringCellValue().compareTo(code) == 0) { num = lcv; } lcv++; } } catch (Exception ex) { ex.printStackTrace(); } return num; }