List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:org.opencms.search.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * // w w w . j a v a 2 s.c om * @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 ((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(); }
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action./* w ww. j ava 2 s. co m*/ * * @throws ServiceException */ public void doOK() throws ServiceException { PersistenceManager pm = this.getPm(); ApplicationContext app = this.getApp(); this.doRefresh(); if (!this.hasPermission) { this.errorMessage = "no permission to run this wizard"; } else { String location = app.getTempFileName(UPLOAD_FILE_FIELD_NAME, ""); try { if (new File(location + ".INFO").exists() && new File(location).exists() && (new File(location).length() > 0)) { String contentMimeType = null; String contentName = null; try { // mimeType and name BufferedReader r = new BufferedReader(new FileReader(location + ".INFO")); contentMimeType = r.readLine(); contentName = r.readLine(); r.close(); new File(location + ".INFO").delete(); } catch (Exception ignore) { } if ((contentName != null) && !contentName.isEmpty() && (contentMimeType != null) && !contentMimeType.isEmpty()) { // the calling object determines which columns are optional/required in the spreadsheet ProductConfigurationTypeSet productConfigurationTypeSet = null; ProductConfigurationType productConfigurationType = null; PropertySet propertySet = null; CrxObject crxObject = null; String callerName = null; String callerParentName = null; ImportTarget importTarget = ImportTarget.NA; // case 1: // required: Property_name // ProductConfigurationTypeSet_name // ProductConfigurationType_name if (this.getObject() instanceof org.opencrx.kernel.product1.jmi1.Segment) { importTarget = ImportTarget.ProductSegment; } // case 2: // required: Property_name // ProductConfigurationType_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) else if (this.getObject() instanceof ProductConfigurationTypeSet) { importTarget = ImportTarget.ProductConfigurationTypeSet; productConfigurationTypeSet = (ProductConfigurationTypeSet) this.getObject(); callerName = ((ProductConfigurationTypeSet) this.getObject()).getName(); } // case 3: // required: Property_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) // ProductConfigurationType_name (if provided, then only Properties of matching ProductConfigurationTypes are considered) else if (this.getObject() instanceof ProductConfigurationType) { importTarget = ImportTarget.ProductConfigurationType; productConfigurationType = (ProductConfigurationType) this.getObject(); callerName = ((ProductConfigurationType) this.getObject()).getName(); RefObject_1_0 parentObj = (RefObject_1_0) pm .getObjectById(this.getObject().refGetPath().getParent().getParent()); if (parentObj instanceof ProductConfigurationTypeSet) { callerParentName = ((ProductConfigurationTypeSet) parentObj).getName(); } } // case 4: // required: Property_name // optional: PropertySet_name (if provided, then only Properties of matching PropertySets are considered) else if (this.getObject() instanceof PropertySet) { importTarget = ImportTarget.PropertySet; propertySet = (PropertySet) this.getObject(); callerName = propertySet.getName(); } // case 5: // required: PropertySet_name // Property_name else if (this.getObject() instanceof CrxObject) { importTarget = ImportTarget.CrxObject; crxObject = (CrxObject) this.getObject(); } // Get product segment org.opencrx.kernel.product1.jmi1.Segment productSegment = Products.getInstance() .getProductSegment(pm, this.getProviderName(), this.getSegmentName()); int idxProperty_dtype = -1; int idxProperty_name = -1; int idxProperty_description = -1; int idxProperty_value = -1; int idxPropertySet_name = -1; int idxPropertySet_description = -1; int idxProductConfigurationTypeSet_name = -1; int idxProductConfigurationTypeSet_description = -1; int idxProductConfigurationType_name = -1; int idxProductConfigurationType_description = -1; // verify whether File exists // Read workbook Workbook wb = null; try { wb = WorkbookFactory.create(new FileInputStream(location)); } catch (Exception e) { this.errorMessage = e.getMessage(); } if (wb != null) { //for (int i = 0; i < workbook.getNumberOfSheets(); i++) { // read first sheet only!!! for (int i = 0; i < 1; i++) { Sheet sheet = wb.getSheetAt(i); int linesRead = 0; int propertiesUpdated = 0; this.importReport += ""; Iterator<Row> rows = sheet.rowIterator(); int nRow = 0; int maxCell = 0; HSSFRow row = null; Map<String, String> attributeMap = new TreeMap<String, String>(); if (rows.hasNext()) { nRow += 1; // read first row with attribute names this.importReport += "<tr class='gridTableHeaderFull'>"; this.importReport += "<td>#</td>"; row = (HSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); int nCell = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > maxCell) { maxCell = nCell; } try { if ((cell.getCellType() == HSSFCell.CELL_TYPE_STRING) && (cell.getStringCellValue() != null)) { boolean isSearchAttribute = false; String cellValue = (cell.getStringCellValue().trim()); attributeMap.put(DECIMAL_FORMAT_0000.format(nCell), cellValue); // get idx of select attributes if (ATTR_PROPERTY_DTYPE.compareToIgnoreCase(cellValue) == 0) { idxProperty_dtype = nCell; } else if (ATTR_PROPERTY_NAME.compareToIgnoreCase(cellValue) == 0) { idxProperty_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTY_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProperty_description = nCell; } else if (ATTR_PROPERTY_VALUE .compareToIgnoreCase(cellValue) == 0) { idxProperty_value = nCell; } else if (ATTR_PROPERTYSET_NAME .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTYSET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_description = nCell; } this.importReport += "<td " + (isSearchAttribute ? "class='searchAttr' title='attribute used for matching'" : "") + ">" + cellValue + "</td>"; } else { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + "[not a string cell]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + ec.getMessage() + "</td>"; } } this.importReport += "</tr>"; } while (rows.hasNext()) { nRow += 1; linesRead += 1; row = (HSSFRow) rows.next(); String propertyType = null; String propertyName = null; String propertyDescription = null; HSSFCell propertyValue = null; String propertySetName = null; String propertySetDescription = null; String productConfigurationTypeSetName = null; String productConfigurationTypeSetDescription = null; String productConfigurationTypeName = null; String productConfigurationTypeDescription = null; String cellId = null; Map<String, Cell> valueMap = new TreeMap<String, Cell>( String.CASE_INSENSITIVE_ORDER); String appendErrorRow = null; this.importReport += "<tr class='gridTableRowFull'>"; this.importReport += "<td id='r" + nRow + "'><b>" + DECIMAL_FORMAT_0000.format(nRow) + "</b></td>"; String jsBuffer = ""; try { Iterator<Cell> cells = row.cellIterator(); int nCell = 0; int currentCell = 0; appendErrorRow = null; while (cells.hasNext()) { //HSSFCell cell = (HSSFCell)row.getCell((short)0); HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > currentCell) { this.importReport += "<td colspan=\"" + (nCell - currentCell) + "\" class=\"empty\"> </td>"; } currentCell = nCell + 1; try { cellId = "id='r" + nRow + (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString().toUpperCase() + "'"; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue().trim(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); if (nCell == idxProperty_dtype) { propertyType = cellValue; } else if (nCell == idxProperty_name) { propertyName = cellValue; } else if (nCell == idxProperty_description) { propertyDescription = cellValue; } else if (nCell == idxProperty_value) { propertyValue = cell; } else if (nCell == idxPropertySet_name) { propertySetName = cellValue; } else if (nCell == idxPropertySet_description) { propertySetDescription = cellValue; } else if (nCell == idxProductConfigurationTypeSet_name) { productConfigurationTypeSetName = cellValue; } else if (nCell == idxProductConfigurationTypeSet_description) { productConfigurationTypeSetDescription = cellValue; } else if (nCell == idxProductConfigurationType_name) { productConfigurationTypeName = cellValue; } else if (nCell == idxProductConfigurationType_description) { productConfigurationTypeDescription = cellValue; } this.importReport += "<td " + cellId + ">" + (cellValue != null ? (cellValue.replace("\r\n", EOL_HTML)).replace("\n", EOL_HTML) : "") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (nCell == idxProperty_value) { propertyValue = cell; } BigDecimal cellValue = new BigDecimal( cell.getNumericCellValue()); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + cellValue + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (nCell == idxProperty_value) { propertyValue = cell; } boolean cellValue = cell.getBooleanCellValue(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + (cellValue ? "TRUE" : "FALSE") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + " class=\"empty\"> </td>"; } else { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + "[cell-type (" + cell.getCellType() + ") not supported]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + cell.getCellFormula() + "</td>"; } } if (nCell < maxCell) { this.importReport += "<td colspan=\"" + (maxCell - nCell) + "\" class=\"empty\"></td>"; } } catch (Exception e) { this.importReport += "<td class='err' colspan=\"" + (maxCell + 2) + "\">ERROR in Attribute Row!</td>"; } // process row Property property = null; if (this.isSupportedDtypeValue(propertyType, propertyValue)) { /* case 1 */ if (importTarget == ImportTarget.ProductSegment && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from Product Segment)';"; if (propertySetName == null || propertySetName.isEmpty()) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (org.opencrx.kernel.product1.jmi1.ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 2 */ } else if (importTarget == ImportTarget.ProductConfigurationTypeSet && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationTypeSet)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerName != null && productConfigurationTypeSetName != null && callerName.equals(productConfigurationTypeSetName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 3 */ } else if (importTarget == ImportTarget.ProductConfigurationType && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationType)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerParentName != null && productConfigurationTypeSetName != null && callerParentName .equals(productConfigurationTypeSetName))) && ((productConfigurationTypeName == null || productConfigurationTypeName.isEmpty()) || (callerName != null && productConfigurationTypeName != null && callerName .equals(productConfigurationTypeName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm.getObjectById( property.refGetPath().getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 4 */ } else if (importTarget == ImportTarget.PropertySet && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from PropertySet)';"; if (((propertySetName == null || propertySetName.isEmpty()) || (callerName != null && propertySetName != null && callerName.equals(propertySetName))) && (productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, null, propertySet, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 5 */ } else if (importTarget == ImportTarget.CrxObject && propertyName != null && !propertyName.isEmpty() && propertySetName != null && !propertySetName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from CrxObject)';"; if ((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { //createOrUpdatePropertyOfPropertySet property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, crxObject, null, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } } else { // incomplete and/or inconsistent row --> disregard this row jsBuffer += "$('r" + nRow + "').title += 'incomplete and/or inconsistent row';"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CELL VALUE TYPE NOT SUPPORTED</td></tr>"; } this.importReport += "</tr>"; if (property != null) { propertiesUpdated++; cellId = "r" + nRow + ATTR_PROPERTY_NAME.toUpperCase(); jsBuffer += "try{$('r" + nRow + "').className += ' ok';$('" + cellId + "').className=' ok';$('" + cellId + "').innerHTML = '<a href=\"" + this.getSelectObjectHref(property) + "\" target=\"_blank\"><b>' + " + "$('" + cellId + "').innerHTML +" + "'</b></a>'}catch(e){};"; cellId = "r" + nRow + ATTR_PROPERTY_VALUE.toUpperCase(); if (propertyValue != null) { jsBuffer += "try{$('" + cellId + "').className='ok';}catch(e){};"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>VERIFY Property DTYPE/NAME/VALUE</td></tr>"; jsBuffer += "$('r" + nRow + "').className += 'nok';"; } if (appendErrorRow != null) { this.importReport += appendErrorRow; } valueMap = null; this.importReport += "<tr style=\"display:none;\">"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += jsBuffer.length() > 0 ? "<script language='javascript' type='text/javascript'>" + jsBuffer + "</script>" : ""; this.importReport += " </td>"; this.importReport += "</tr>"; } /* while */ // Spacer this.importReport += "<tr class='gridTableRowFull' style=\"background-color:white;\">"; this.importReport += " <td colspan='" + (maxCell + 2) + "'> </td>"; this.importReport += "</tr>"; // Summary this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += " Sheet: <b>" + wb.getSheetName(i) + "</b> |"; this.importReport += " data lines <b>read: " + linesRead + "</b><br>"; this.importReport += "</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td> </td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">Created / Updated</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td>Properties</td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">" + propertiesUpdated + "</td>"; this.importReport += "</tr>"; if (linesRead != propertiesUpdated) { this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td class=\"err\" colspan=\"" + (maxCell + 2) + "\">WARNING: some data lines were not processed due to data errors (e.g. multiple matches, missing name, etc.)</td>"; this.importReport += "</tr>"; } } } } } else { this.errorMessage = "No Excel workbook selected"; } } finally { new File(location).delete(); } } }
From source file:org.openmicroscopy.shoola.util.file.SheetInfo.java
License:Open Source License
/** Auto-sizes all columns to fit contents. */ void sizeAllColumnsToFit() { Map<Integer, Integer> colMap = new HashMap<Integer, Integer>(); Iterator rowIterator = sheet.iterator();//sheet.rowIterator(); HSSFCell cell;/*from ww w . ja v a 2 s . com*/ HSSFRow row; Iterator k; while (rowIterator.hasNext()) { row = (HSSFRow) rowIterator.next(); k = row.cellIterator(); while (k.hasNext()) { cell = (HSSFCell) k.next(); colMap.put(cell.getColumnIndex(), cell.getRowIndex()); } } Iterator<Integer> colIterator = colMap.keySet().iterator(); int col; while (colIterator.hasNext()) { col = colIterator.next(); sizeColumnToFit(col); } }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
/** * Copy sheet to sheet, from start row to end row. * @param from from Sheet//ww w. ja v a 2s. c o m * @param to to Sheet * @param fromRowCnt start row number * @param toRowCnt length of copying rows */ @SuppressWarnings("unchecked") public static void copySheet(HSSFSheet from, HSSFSheet to, int fromRowCnt, int toRowCnt) { HSSFRow fromRow = null; HSSFRow toRow = null; for (int i = fromRowCnt; i <= toRowCnt; i++) { fromRow = from.getRow(i); toRow = to.createRow(i); Iterator<HSSFCell> iterator = fromRow.cellIterator(); short col = 0; while (iterator.hasNext()) { HSSFCell cell = iterator.next(); addCell(toRow, col++, cell.getStringCellValue(), cell.getCellStyle()); } } }
From source file:org.sakaiproject.search.component.adapter.contenthosting.XLContentDigester.java
License:Educational Community License
public void loadContent(Writer writer, ContentResource contentResource) { if (contentResource != null && contentResource.getContentLength() > maxDigestSize) { throw new RuntimeException( "Attempt to get too much content as a string on " + contentResource.getReference()); }/*from w w w .j ava 2s .c o m*/ if (contentResource == null) { throw new RuntimeException("Null contentResource passed the loadContent"); } InputStream contentStream = null; try { contentStream = contentResource.streamContent(); POIFSFileSystem fs = new POIFSFileSystem(contentStream); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator<Cell> 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) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; } } } } } catch (Exception e) { throw new RuntimeException("Failed to read content for indexing ", e); } finally { if (contentStream != null) { try { contentStream.close(); } catch (IOException e) { log.debug(e); } } } }
From source file:org.terrier.indexing.MSExcelDocument.java
License:Mozilla Public License
/** Get the reader appropriate for this InputStream. This involves converting the Excel document to a stream of words. On failure returns null and sets EOD to true, so no terms can be read from the object. //from w w w . ja v a2s .c om Uses the property <tt>indexing.excel.maxfilesize.mb</tt> to determine if the file is too big to open @param docStream */ @SuppressWarnings("unchecked") //poi version used is for Java 1.4. protected Reader getReader(InputStream docStream) { if (MAXFILESIZE > 0 && (filename == null || new File(filename).length() > MAXFILESIZE)) { logger.warn("WARNING: Excel document " + filename + " is too large for POI. Ignoring."); EOD = true; return null; } try { CharArrayWriter writer = new CharArrayWriter(); //opening the file system POIFSFileSystem fs = new POIFSFileSystem(docStream); //opening the work book HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //got the i-th sheet from the work book 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) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; } } } } return new CharArrayReader(writer.toCharArray()); } catch (Exception e) { logger.warn("WARNING: Problem converting excel document" + e); EOD = true; return null; } }
From source file:org.testeditor.core.importer.ExcelFileImporter.java
License:Open Source License
/** * Iterates through the cells in a row an creates a {@link TestDataRow} * Object.//from w w w .j a va 2s . com * * @param row * row in excel sheet * @return TestDataRow */ @SuppressWarnings("rawtypes") private TestDataRow getTestDataRow(HSSFRow row) { int id = 0; Iterator cells = row.cellIterator(); TestDataRow testDataRow = new TestDataRow(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); for (int i = id; i < cell.getColumnIndex(); i++) { testDataRow.add(""); } id = cell.getColumnIndex() + 1; if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { getTestDataNumericCell(testDataRow, cell); } else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) { testDataRow.add(cell.getStringCellValue()); } else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) { testDataRow.add(String.valueOf(cell.getBooleanCellValue())); } else if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(row.getSheet().getWorkbook()); CellValue cv = fe.evaluate(cell); testDataRow.add(cv.formatAsString()); } else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) { testDataRow.add(""); } else { if (LOGGER.isInfoEnabled()) { LOGGER.info("getTestData :: Unknown cell type"); } } } return testDataRow; }
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * /*from www . j a v a 2 s. c om*/ * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final Reader getContent(final File f) { Reader reader = null; setType("EXCEL"); try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f)); HSSFWorkbook workbook = new HSSFWorkbook(fs); 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) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; default: // skip } } } } setSummary(getSummaryFromContent(writer.toString())); return new CharArrayReader(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents for: " + f.getName(), e); } return reader; }
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * /* w ww. java2 s .com*/ * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final String getContent(final InputStream is) { try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook workbook = new HSSFWorkbook(fs); 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) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; default: // skip } } } } return new String(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents", e); } return ""; }
From source file:POS.migrate.Excel_to_db_inventory_items.java
public static void main(String[] args) { System.setProperty("pool_db", "db_smis_dumaguete_refreshments"); String file = "C:\\Users\\Ronescape\\Documents\\Excel Files\\Ray Buenavista\\encode.xls"; if (file == null || file.isEmpty()) { return;/*from w ww .ja v a2s . c o m*/ } FileInputStream fis = null; final List sheetData = new ArrayList(); try { fis = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell); } sheetData.add(data); } } catch (IOException e) { throw new RuntimeException(e); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(Excel_to_db_inventory_items.class.getName()).log(Level.SEVERE, null, ex); } } } List<Excel_to_db_inventory_items> datas = Excel_to_db_inventory_items.showExcelData(sheetData, file); int with_qty = 0; for (Excel_to_db_inventory_items encoded : datas) { if (FitIn.toDouble(encoded.qty) > 0) { with_qty++; } int id = -1; String barcode = "" + FitIn.toInt(encoded.item_code); if (barcode.equalsIgnoreCase("n/a")) { barcode = ""; } String description = encoded.description; String generic_name = ""; String category = encoded.category; if (category.equalsIgnoreCase("n/a")) { category = ""; } String category_id = ""; String classification = encoded.classification; if (classification.equalsIgnoreCase("n/a")) { classification = ""; } String classification_id = ""; String sub_classification = encoded.sub_classification; if (sub_classification.equalsIgnoreCase("n/a")) { sub_classification = ""; } String sub_classification_id = ""; double product_qty = 0; double conversion = 1; double selling_price = FitIn.toDouble(encoded.selling_price); String date_added = DateType.now(); String user_name = ""; String item_type = "Regular"; int status = 1; String supplier = ""; int fixed_price = 0; double cost = FitIn.toDouble(encoded.cost); String supplier_id = ""; int multi_level_pricing = 0; int vatable = 0; double reorder_level = 0; double markup = 0; String barcodes = encoded.barcode; if (barcodes.equalsIgnoreCase("n/a")) { barcodes = ""; } String brand = encoded.brand; if (brand.equalsIgnoreCase("n/a")) { brand = ""; } String brand_id = ""; String model = encoded.model; if (brand.equalsIgnoreCase("n/a")) { model = ""; } String model_id = ""; int selling_type = 1; String branch = "Dumaguete-Main Branch"; String branch_code = "1"; String location = "Warehouse"; String location_id = "1"; String unit = "[" + encoded.unit + ":" + encoded.selling_price + "/1.0^1]"; // System.out.println("Unit: "+unit); int is_uploaded = 0; int allow_negative_inventory = 0; int auto_order = 1; Inventory.to_inventory to = new Inventory.to_inventory(id, barcode, description, generic_name, category, category_id, classification, classification_id, sub_classification, sub_classification_id, product_qty, unit, conversion, selling_price, date_added, user_name, item_type, status, supplier, fixed_price, cost, supplier_id, multi_level_pricing, vatable, reorder_level, markup, barcodes, brand, brand_id, model, model_id, selling_type, branch, branch_code, location, location_id, false, is_uploaded, allow_negative_inventory, auto_order); Inventory.add_inventory(to); //encoding String item_code = "" + FitIn.toInt(encoded.item_code); String branch_id = "1"; double qty = FitIn.toDouble(encoded.qty); String screen_name = "administrator"; String sheet_no = "1"; String counted_by = "admin"; String checked_by = "admin"; String user_id = MyUser.getUser_id(); String user_screen_name = MyUser.getUser_screen_name(); String remarks = ""; Encoding_inventory.to_encoding_inventory en = new Encoding_inventory.to_encoding_inventory(id, item_code, barcodes, description, branch, branch_id, location, location_id, qty, date_added, user_name, screen_name, sheet_no, 0, counted_by, checked_by, cost, selling_price, user_id, user_screen_name, remarks); Encoding_inventory.add_encoding_inventory(en); } System.out.println("Count: " + with_qty); Alert.set(1, ""); }