List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator
@Override
public Iterator<Row> rowIterator()
From source file:com.duroty.lucene.parser.MSExcelParser.java
License:Open Source License
/** * DOCUMENT ME!//from ww w . j a va2 s .co m * * @return DOCUMENT ME! * * @throws ParserException DOCUMENT ME! */ private String getContents() throws ParserException { String contents = ""; try { POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workbook = new HSSFWorkbook(fs); StringBuffer buffer = new StringBuffer(); 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) { buffer.append(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { buffer.append(text + " "); } break; } } buffer.append("\n"); /*if (sleep > 0) { try { Thread.sleep(sleep); } catch (Exception ex) { } }*/ } } contents = buffer.toString(); } catch (Exception ex) { throw new ParserException(ex); } return contents; }
From source file:com.elbeesee.poink.transreptor.HSSFSheetToXML.java
License:Open Source License
public void onTransrept(INKFRequestContext aContext) throws Exception { IHSSFSheetRepresentation aIHSSFSheetRepresentation = (IHSSFSheetRepresentation) aContext .sourcePrimary(IHSSFSheetRepresentation.class); HSSFSheet vSheet = aIHSSFSheetRepresentation.getSheetReadOnly(); String vSheetName = vSheet.getSheetName(); StringBuilder vSheetXML = new StringBuilder(); vSheetXML.append("<sheet sheetName=\""); vSheetXML.append(XMLUtils.escape(vSheetName)); vSheetXML.append("\" sheetIndex=\""); vSheetXML.append(vSheet.getWorkbook().getSheetIndex(vSheetName)); vSheetXML.append("\" numRows=\""); vSheetXML.append(vSheet.getPhysicalNumberOfRows()); vSheetXML.append("\">"); // do the rows int i = 0;/* ww w . j a v a 2s . c om*/ for (Iterator<Row> vRowIterator = vSheet.rowIterator(); vRowIterator.hasNext();) { HSSFRow vHSSFRow = (HSSFRow) vRowIterator.next(); IHSSFRowRepresentation vHSSFRowRepresentation = new HSSFRowImplementation(vHSSFRow); String vRowXML = aContext.transrept(vHSSFRowRepresentation, String.class); vSheetXML.append(vRowXML); i = i + 1; } // vSheetXML.append("</sheet>"); INKFResponse vResponse = aContext.createResponseFrom(vSheetXML.toString()); vResponse.setExpiry(INKFResponse.EXPIRY_DEPENDENT); }
From source file:com.essa.main.ReadExcel.java
public static void main(String[] args) { try {//from www. ja v a 2 s .c o m InputStream input = new BufferedInputStream(new FileInputStream("sample.xls")); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); System.out.println("\n"); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) System.out.print(cell.getNumericCellValue() + " "); else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) System.out.print(cell.getStringCellValue() + " "); else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) System.out.print(cell.getBooleanCellValue() + " "); else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("BLANK "); else System.out.print("Unknown cell type"); } } } catch (IOException ex) { ex.printStackTrace(); } }
From source file:com.ferid.app.classroom.edit.EditStudentActivity.java
License:Apache License
/** * Import students form excel/*from ww w . ja v a 2s .c om*/ * @param fileName Excel file name */ private void readXlsFile(String fileName) { ArrayList<String> studentsList = new ArrayList<>(); progressDialog = ProgressDialog.show(this, getString(R.string.wait), getString(R.string.ongoing), true, false); try { // Creating Input Stream File file = new File(fileName); FileInputStream fileInputStream = new FileInputStream(file); // Create a POIFSFileSystem object POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fileInputStream); // Create a workbook using the File System HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); // Get the first sheet from workbook HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); // Iterate through the cells Iterator rowIter = hssfSheet.rowIterator(); StringBuilder studentName; //full name while (rowIter.hasNext()) { studentName = new StringBuilder(""); HSSFRow hssfRow = (HSSFRow) rowIter.next(); Iterator cellIter = hssfRow.cellIterator(); while (cellIter.hasNext()) { HSSFCell currentCell = (HSSFCell) cellIter.next(); if (!currentCell.toString().trim().equals("")) { //put space in between name, surname, etc. if (studentName.toString().length() > 0) { studentName.append(" "); } studentName.append(currentCell.toString()); } } //add to list if (!studentName.toString().equals("")) { studentsList.add(studentName.toString()); } } } catch (Exception e) { progressDialog.dismiss(); excelFileError(); } if (!studentsList.isEmpty()) { new InsertMultipleStudents().execute(studentsList); } else { progressDialog.dismiss(); } }
From source file:com.frameworkset.platform.cms.searchmanager.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * /*from ww w . ja va 2s .com*/ * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (CmsStringUtil.isNotEmpty(excelWb.getSheetName(i))) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if (CmsStringUtil.isNotEmpty(text)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:com.gestec.modelo.controladores.ArchivosView.java
public void HandleFileUpload(FileUploadEvent event) throws IOException { uploadedFile = event.getFile();/*from ww w . j ava2s .c o m*/ InputStream file = uploadedFile.getInputstream(); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet miHoja = workbook.getSheetAt(0); Iterator filas = miHoja.rowIterator(); this.datosTabla.clear(); while (filas.hasNext()) { HSSFRow nuevaFila = (HSSFRow) filas.next(); Iterator celdas = nuevaFila.cellIterator(); String[] addFila = new String[7]; int pos = 0; while (celdas.hasNext()) { addFila[pos] = celdas.next().toString(); pos++; } addFila[6] = "" + efl.ingresarEquipoExcel(addFila); this.datosTabla.add(addFila); } FacesMessage fm = new FacesMessage("Exito", event.getFile().getFileName() + " Fue subido"); FacesContext.getCurrentInstance().addMessage(null, fm); }
From source file:com.hl7.main.ImportDataFromXLStoXML.java
public void displayFromExcel(String xlsPath) { InputStream inputStream = null; try {/*from w ww . j a v a 2s . c om*/ inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; try { // Initializing the XML document DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Element rootElement = document.createElement("categories"); document.appendChild(rootElement); fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); Integer count = workBook.getNumberOfSheets(); ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>(); for (int i = 0; i < count; i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cells = row.cellIterator(); ArrayList<String> rowData = new ArrayList<String>(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); String richTextString = cell.getStringCellValue(); System.out.println("String: " + richTextString); rowData.add(richTextString); } // end while data.add(rowData); } // end while } int numOfProduct = data.size(); for (int i = 0; i < numOfProduct; i++) { Element productElement = document.createElement("categoryName"); rootElement.appendChild(productElement); int index = 0; for (String s : data.get(i)) { String headerString = data.get(0).get(index); if (data.get(0).get(index).equals("image link")) { headerString = "image_link"; } if (data.get(0).get(index).equals("product type")) { headerString = "product_type"; } Element headerElement = document.createElement(headerString); productElement.appendChild(headerElement); headerElement.appendChild(document.createTextNode(s)); index++; } } TransformerFactory tFactory = TransformerFactory.newInstance(); Transformer transformer = tFactory.newTransformer(); // Add indentation to output transformer.setOutputProperty(OutputKeys.INDENT, "yes"); transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2"); DOMSource source = new DOMSource(document); StreamResult result = new StreamResult(new File("products.xml")); // StreamResult result = new StreamResult(System.out); transformer.transform(source, result); } catch (IOException e) { System.out.println("IOException " + e.getMessage()); } catch (ParserConfigurationException e) { System.out.println("ParserConfigurationException " + e.getMessage()); } catch (TransformerConfigurationException e) { System.out.println("TransformerConfigurationException " + e.getMessage()); } catch (TransformerException e) { System.out.println("TransformerException " + e.getMessage()); } }
From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java
@SuppressWarnings("unchecked") private boolean validateExcelCauseCodeContent(HSSFSheet sheet, HSSFCellStyle errorStyle, List<State> steps) { State state = State.findStateByLable(steps, STEP2_LABEL); if (state == null) { state = new State(); state.setDescription("Data validation"); state.setLabel(STEP2_LABEL);// w ww. ja v a2 s .c o m state.setStatus(EStatus.IN_PROGRESS); steps.add(state); } boolean error = false; if (colIndexes == null) { int lastCellNO = sheet.getRow(ROW_ALERT_TYPE).getLastCellNum(); HSSFCell cell = sheet.getRow(ROW_ALERT_TYPE).createCell(lastCellNO + 1); cell.setCellStyle(errorStyle); cell.setCellValue(new HSSFRichTextString(ERROR_UNKONW_TYPE)); error = true; } else { Iterator<Row> rowIter = sheet.rowIterator(); int rowCounter = -1; int totalRows = sheet.getLastRowNum(); int colStart = colIndexes.getColCauseCode(); int colEnd = colIndexes.getColInternalId(); while (rowIter.hasNext()) { HSSFRow row = (HSSFRow) rowIter.next(); rowCounter++; int progress = (int) ((float) rowCounter / totalRows * 100); state.setProgress(progress); if (rowCounter <= ROW_TABLE_HEAD) { continue; } StringBuffer errorMsg = new StringBuffer(); for (int col = colStart; col <= colEnd; col++) { HSSFCell cell = row.getCell(col); if (col == colIndexes.getColInternalId()) { if (!isCauseCodeExists(cell)) { buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID", ERROR_INTERNAL_ID_NOT_EXIST); } else { Long alertTypeId = getAlertTypeId(cell); if (alertTypeId != null && alertTypeId != this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode())) { buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID", ERROR_ALERT_TYPE_NOT_MATCH); } } } if (col == colIndexes.getColCauseCode()) { if (cell == null) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); continue; } HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId()); if (!isCauseCodeExists(causeCodeIdCell)) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); continue; } boolean pass = true; // if no change continue; String alertCauseNameInCell = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { alertCauseNameInCell = cell.getStringCellValue(); } else { pass = false; } if (alertCauseNameInCell == null || "".equals(alertCauseNameInCell.trim())) { pass = false; } if (!pass) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); continue; } if (alertCauseNameInCell.length() > 128) { alertCauseNameInCell = alertCauseNameInCell.substring(0, 128); } String alertCauseNameInDb = getAlertCauseName(causeCodeIdCell); // compare the cc name and cause code name under id. if // not same check it's availability. if same ignore. if (!strCompare(alertCauseNameInDb, alertCauseNameInCell)) { List<AlertCause> acList = null; try { acList = getEntityManager().createNamedQuery("findActiveAlertCauseByNameAndTypeId") .setParameter("alertCauseName", alertCauseNameInCell.trim().toUpperCase()) .setParameter("alertTypeId", this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode())) .getResultList(); if (acList.size() <= 0) { buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)", ERROR_UNKONW_CAUSE_CODE); } } catch (Exception e) { log.error(e.getMessage(), e); } } } if (col == colIndexes.getColTargetDate()) { if (!isDateFormat(cell)) {//CC Target Date is an optional field buildErrorMsg(errorMsg, colIndexes.getColTargetDate(), "CC target date", ERROR_BAD_DATE_FORMAT); } } if (col == colIndexes.getColOwner()) { if (!isOwnerExistsInBluePage(cell)) {//CC Owner is an optional field buildErrorMsg(errorMsg, colIndexes.getColOwner(), "CC owner", ERROR_UNKNOW_OWNER); } } } if (errorMsg.length() > 0) { HSSFCell msgCell = row.createCell(colIndexes.getColMessage()); msgCell.setCellStyle(errorStyle); msgCell.setCellValue(new HSSFRichTextString(errorMsg.toString())); error = true; } } } if (error) { state.setStatus(EStatus.FAILED); } else { if (state.getProgress() == 100 && state.getStatus().getPriority() < EStatus.FINISHED.getPriority()) { state.setStatus(EStatus.FINISHED); } } return error; }
From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java
private void saveCauseCode(HSSFWorkbook wb, String remoteUser, List<State> steps) { HSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIter = sheet.rowIterator(); State state = State.findStateByLable(steps, STEP3_LABEL); if (state == null) { state = new State(); state.setDescription("Persist changes"); state.setLabel(STEP3_LABEL);//ww w. j av a2 s . c om state.setStatus(EStatus.IN_PROGRESS); steps.add(state); } int rowCounter = -1; int totalRows = sheet.getLastRowNum(); while (rowIter.hasNext()) { HSSFRow row = (HSSFRow) rowIter.next(); rowCounter++; int progress = (int) ((float) rowCounter / totalRows * 100); state.setProgress(progress); if (progress == 100) { state.setStatus(EStatus.FINISHED); } if (rowCounter <= ROW_TABLE_HEAD) { continue; } HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId()); long causeCodeId = -1; if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { causeCodeId = Long.valueOf(causeCodeIdCell.getStringCellValue()); } else if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { causeCodeId = Math.round(causeCodeIdCell.getNumericCellValue()); } CauseCode causeCode = (CauseCode) getEntityManager().createNamedQuery("getCauseCodeById") .setParameter("id", causeCodeId).getSingleResult(); String causeCodeName = causeCode.getAlertCause().getName(); HSSFCell causeCodeCell = row.getCell(colIndexes.getColCauseCode()); String colCauseCode = null; if (causeCodeCell != null) { colCauseCode = causeCodeCell.getStringCellValue().trim(); } Date targetDate = causeCode.getTargetDate(); HSSFCell targetDateCell = row.getCell(colIndexes.getColTargetDate()); Date colTargetDate = null; if (targetDateCell != null) { if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(targetDateCell)) { colTargetDate = targetDateCell.getDateCellValue(); } else if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { colTargetDate = convertTextToDate(targetDateCell);//Convert Date Text to Date Object } } String owner = causeCode.getOwner(); HSSFCell ownerCell = row.getCell(colIndexes.getColOwner()); String colOwner = null; if (ownerCell != null) { colOwner = ownerCell.getStringCellValue().trim(); } //Assignee Comments Function Start if (colIndexes.getColAssigneeComments() != -1) { HSSFCell assigneeCommentsCell = row.getCell(colIndexes.getColAssigneeComments()); String assigneeComments = ""; if (assigneeCommentsCell != null && assigneeCommentsCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { assigneeComments = assigneeCommentsCell.getStringCellValue(); } if (assigneeComments != null && !"".equals(assigneeComments.trim())) { updateAssigneeComments(causeCode.getAlertId(), assigneeComments.trim(), colIndexes.getReportName().trim(), remoteUser); } } //Assignee Comments Function End boolean changed = false; if (!strCompare(causeCodeName, colCauseCode) || !dateCompare(targetDate, colTargetDate) || !strCompare(owner, colOwner)) { changed = true; } if (!changed) { continue; } CauseCodeHistory history = new CauseCodeHistory(); history.setCauseCode(causeCode); history.setAlertType(causeCode.getAlertType()); history.setAlertId(causeCode.getAlertId()); history.setAlertCause(causeCode.getAlertCause()); history.setTargetDate(causeCode.getTargetDate()); history.setOwner(causeCode.getOwner()); history.setRecordTime(causeCode.getRecordTime()); history.setRemoteUser(causeCode.getRemoteUser()); if (!strCompare(causeCodeName, colCauseCode)) { try { AlertCause alertCause = null; if ("UNDEFINED".equals(colCauseCode.trim().toUpperCase())) { alertCause = (AlertCause) getEntityManager() .createNamedQuery("findAlertCauseByNameWithoutShowInGui") .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult(); } else { alertCause = (AlertCause) getEntityManager().createNamedQuery("findAlertCauseByName") .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult(); } if (alertCause != null) { causeCode.setAlertCause(alertCause); } } catch (Exception e) { log.error(e.getMessage(), e); } } if (!dateCompare(targetDate, colTargetDate)) { causeCode.setTargetDate(colTargetDate); } if (!strCompare(owner, colOwner)) { causeCode.setOwner(colOwner); } causeCode.setRemoteUser(remoteUser); causeCode.setRecordTime(new Date()); try { getEntityManager().persist(history); getEntityManager().persist(causeCode); getEntityManager().flush(); } catch (Exception e) { log.error(e.getMessage(), e); } } }
From source file:com.isotrol.impe3.idx.oc.extractors.ExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //w w w .j ava2 s. c o m * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring * @deprecated */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (excelWb.getSheetName(i) != null && !excelWb.getSheetName(i).trim().equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator<?> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator<?> it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && (text.length() != 0)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }