List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * Create or update property./*from w w w . j a v a 2s . c o m*/ * * @param prodConfTypeSet * @param productConfigurationTypeSetName * @param productConfigurationTypeSetDescription * @param prodConfType * @param productConfigurationTypeName * @param productConfigurationTypeDescription * @param crxObject * @param propSet * @param propertySetName * @param propertySetDescription * @param propertyType * @param propertyName * @param propertyDescription * @param propertyValue * @param productSegment * @return */ public Property createOrUpdatePropertyOfPropertySet(ProductConfigurationTypeSet prodConfTypeSet, String productConfigurationTypeSetName, String productConfigurationTypeSetDescription, ProductConfigurationType prodConfType, String productConfigurationTypeName, String productConfigurationTypeDescription, CrxObject crxObject, PropertySet propSet, String propertySetName, String propertySetDescription, String propertyType, String propertyName, String propertyDescription, HSSFCell propertyValue, org.opencrx.kernel.product1.jmi1.Segment productSegment, ApplicationContext app) { PersistenceManager pm = JDOHelper.getPersistenceManager(productSegment); ProductConfigurationTypeSet productConfigurationTypeSet = prodConfTypeSet; ProductConfigurationType productConfigurationType = prodConfType; PropertySet propertySet = propSet; Property property = null; if (prodConfTypeSet != null || productConfigurationTypeSetName != null || prodConfType != null || productConfigurationTypeName != null) { if (productConfigurationTypeSet == null && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty()) { // try to locate productConfigurationTypeSet with respective name (or create new productConfigurationTypeSet) ProductConfigurationTypeSetQuery productConfigurationTypeSetQuery = (ProductConfigurationTypeSetQuery) pm .newQuery(ProductConfigurationTypeSet.class); productConfigurationTypeSetQuery.name().equalTo(productConfigurationTypeSetName); try { pm.currentTransaction().begin(); Iterator<ProductConfigurationTypeSet> pcts = productSegment .getConfigurationTypeSet(productConfigurationTypeSetQuery).iterator(); if (pcts.hasNext()) { productConfigurationTypeSet = pcts.next(); } else { // create new ProductConfigurationTypeSet productConfigurationTypeSet = pm.newInstance(ProductConfigurationTypeSet.class); productConfigurationTypeSet.setName(productConfigurationTypeSetName); productSegment.addConfigurationTypeSet(getUidAsString(), productConfigurationTypeSet); } productConfigurationTypeSet.setDescription(productConfigurationTypeSetDescription); pm.currentTransaction().commit(); //System.out.println("productConfigurationTypeSet found/committed name=" + productConfigurationTypeSet.getName()); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } if (productConfigurationTypeSet != null && productConfigurationType == null && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { // try to locate productConfigurationType with respective name (or create new productConfigurationType) ProductConfigurationTypeQuery productConfigurationTypeFilter = (ProductConfigurationTypeQuery) pm .newQuery(ProductConfigurationType.class); productConfigurationTypeFilter.name().equalTo(productConfigurationTypeName); try { pm.currentTransaction().begin(); Iterator<ProductConfigurationType> pct = productConfigurationTypeSet .getConfigurationType(productConfigurationTypeFilter).iterator(); if (pct.hasNext()) { productConfigurationType = (ProductConfigurationType) pct.next(); } else { // create new ProductConfigurationType productConfigurationType = pm.newInstance(ProductConfigurationType.class); productConfigurationType.setName(productConfigurationTypeName); productConfigurationTypeSet.addConfigurationType(getUidAsString(), productConfigurationType); } productConfigurationType.setDescription(productConfigurationTypeDescription); pm.currentTransaction().commit(); //System.out.println("productConfigurationType found/committed name=" + productConfigurationTypeSet.getName()); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } } else if (crxObject != null) { // try to locate PropertySet with same parent and name (or create new PropertySet) PropertySetQuery propertySetFilter = (PropertySetQuery) pm.newQuery(PropertySet.class); propertySetFilter.name().equalTo(propertySetName); try { pm.currentTransaction().begin(); Iterator<PropertySet> ps = crxObject.getPropertySet(propertySetFilter).iterator(); if (ps.hasNext()) { propertySet = (PropertySet) ps.next(); } else { // create new PropertySet propertySet = pm.newInstance(PropertySet.class); propertySet.setName(propertySetName); crxObject.addPropertySet(getUidAsString(), propertySet); } propertySet.setDescription(propertySetDescription); pm.currentTransaction().commit(); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } if ((propertySet != null || productConfigurationType != null) && propertyType != null && !propertyType.isEmpty() && propertyName != null && !propertyName.isEmpty()) { // try to locate property with same parent and name (or create new property) PropertyQuery propertyQuery = (PropertyQuery) pm.newQuery(Property.class); propertyQuery.name().equalTo(propertyName); Iterator<Property> p = null; if (productConfigurationType != null) { p = productConfigurationType.getProperty(propertyQuery).iterator(); } else { p = propertySet.getProperty(propertyQuery).iterator(); } try { while (p.hasNext() && property == null) { property = p.next(); if (!((property instanceof StringProperty) && (propertyType.equals(PROPERTY_DTYPE_STRING)) || (property instanceof DecimalProperty) && (propertyType.equals(PROPERTY_DTYPE_DECIMAL)) || (property instanceof IntegerProperty) && (propertyType.equals(PROPERTY_DTYPE_INTEGER)) || (property instanceof BooleanProperty) && (propertyType.equals(PROPERTY_DTYPE_BOOLEAN)) || (property instanceof DateTimeProperty) && (propertyType.equals(PROPERTY_DTYPE_DATETIME)) || (property instanceof DateProperty) && (propertyType.equals(PROPERTY_DTYPE_DATE)) || (property instanceof ReferenceProperty) && (propertyType.equals(PROPERTY_DTYPE_REFERENCE)) || (property instanceof UriProperty) && (propertyType.equals(PROPERTY_DTYPE_URI)))) { property = null; } } pm.currentTransaction().begin(); if (propertyType.equals(PROPERTY_DTYPE_STRING)) { if (property == null) { // create new StringProperty property = pm.newInstance(StringProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((StringProperty) property).setStringValue( propertyValue != null ? propertyValue.getStringCellValue().trim() : null); } } else if (propertyType.equals(PROPERTY_DTYPE_DECIMAL)) { if (property == null) { // create new DecimalProperty property = pm.newInstance(DecimalProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((DecimalProperty) property).setDecimalValue( propertyValue != null ? new BigDecimal(propertyValue.getNumericCellValue()) : null); } } else if (propertyType.equals(PROPERTY_DTYPE_INTEGER)) { if (property == null) { // create new IntegerProperty property = pm.newInstance(IntegerProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((IntegerProperty) property).setIntegerValue(propertyValue != null ? (new BigDecimal(propertyValue.getNumericCellValue())).intValue() : null); } } else if (propertyType.equals(PROPERTY_DTYPE_BOOLEAN)) { if (property == null) { // create new BooleanProperty property = pm.newInstance(BooleanProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((BooleanProperty) property).setBooleanValue( propertyValue != null ? propertyValue.getBooleanCellValue() : null); } } else if (propertyType.equals(PROPERTY_DTYPE_DATETIME)) { if (property == null) { // create new DateTimeProperty property = pm.newInstance(DateTimeProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((DateTimeProperty) property).setDateTimeValue(propertyValue != null ? HSSFDateUtil.getJavaDate(propertyValue.getNumericCellValue()) : null); } } else if (propertyType.equals(PROPERTY_DTYPE_DATE)) { if (property == null) { // create new DateTimeProperty property = pm.newInstance(DateProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); if (propertyValue != null) { TimeZone timezone = TimeZone.getTimeZone(app.getCurrentTimeZone()); SimpleDateFormat dateonlyf = new SimpleDateFormat("yyyyMMdd", app.getCurrentLocale()); dateonlyf.setTimeZone(timezone); String date = dateonlyf .format(HSSFDateUtil.getJavaDate(propertyValue.getNumericCellValue())) .substring(0, 8); XMLGregorianCalendar cal = org.w3c.spi2.Datatypes.create(XMLGregorianCalendar.class, date); ((DateProperty) property).setDateValue(cal); } else { ((DateProperty) property).setDateValue(null); } } } else if (propertyType.equals(PROPERTY_DTYPE_REFERENCE)) { if (property == null) { // create new ReferenceProperty property = pm.newInstance(ReferenceProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); BasicObject basicObject = null; if (propertyValue != null) { try { String xri = propertyValue.getStringCellValue().trim(); basicObject = (BasicObject) pm.getObjectById(new Path(xri)); } catch (Exception e) { } } ((ReferenceProperty) property).setReferenceValue(basicObject); } } else if (propertyType.equals(PROPERTY_DTYPE_URI)) { if (property == null) { // create new UriProperty property = pm.newInstance(UriProperty.class); property.setName(propertyName); if (productConfigurationType != null) { productConfigurationType.addProperty(getUidAsString(), property); } else { propertySet.addProperty(getUidAsString(), property); } } if (property != null) { property.setDescription(propertyDescription); ((UriProperty) property).setUriValue( propertyValue != null ? propertyValue.getStringCellValue().trim() : null); } } pm.currentTransaction().commit(); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } return property; }
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action./*from w ww . ja v a2s . c om*/ * * @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.openhealthtools.mdht.cda.generate.C32Generator.java
License:Open Source License
private static String getCellValueAsString(HSSFCell cell) { if (cell != null) { return cell.getStringCellValue(); } else {//w w w.j ava2s . c o m return ""; } }
From source file:org.openmrs.module.kenyametadatatools.mflsync.MflSyncFromRemoteSpreadsheetTask.java
License:Open Source License
/** * Extracts the value of the given cell/*from w w w . j a v a2 s .c o m*/ * @param cell the cell * @return the cell value */ private Object cellValue(HSSFCell cell) { return cell.getCellType() == 0 ? cell.getNumericCellValue() : cell.getStringCellValue(); }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
public static String getStringValue(HSSFRow row, int i) { HSSFCell c = row.getCell((short) i); if (c == null) return ""; return c.getStringCellValue(); }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
public static boolean confirmCellValue(HSSFCell cell, String value) { // if (row == null) // return false; // HSSFCell c1 = row.getCell((short) index); if (cell == null) return false; return value.equals(cell.getStringCellValue()); }
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/*from w ww.j ava 2 s . 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.openswing.swing.importdata.java.ImportFromExcel.java
License:Open Source License
public ArrayList importData(int cols, InputStream in) throws Throwable { // read existing workbook HSSFWorkbook wb = new HSSFWorkbook(new BufferedInputStream(in)); // retrieve existing sheet HSSFSheet s = wb.getSheetAt(0);/*from www.j a v a 2s . com*/ int i = 0; ArrayList rows = new ArrayList(); Object[] rowobj = null; HSSFRow row = null; boolean rowEmpty = true; HSSFCell cell = null; while (true) { rowEmpty = true; rowobj = new Object[cols]; row = s.getRow(i); if (row == null) break; for (short j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { rowobj[j] = new Double(cell.getNumericCellValue()); } else try { rowobj[j] = cell.getDateCellValue(); } catch (Exception ex) { rowobj[j] = cell.getStringCellValue(); } } if (rowobj[j] != null) rowEmpty = false; } if (rowEmpty) break; rows.add(rowobj); i++; } return rows; }
From source file:org.orbeon.oxf.processor.converter.FromXLSConverter.java
License:Open Source License
public ProcessorOutput createOutput(String name) { ProcessorOutput output = new ProcessorOutputImpl(FromXLSConverter.this, name) { public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) { try { // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); // Generate XML from Excel file final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); final org.dom4j.Document d = extractFromXLS(bais); final DOMGenerator domGenerator = new DOMGenerator(d, "from xls output", DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext); domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver); } catch (IOException e) { throw new OXFException(e); }// w w w. j av a2s . co m } private Document extractFromXLS(InputStream inputStream) throws IOException { // Create workbook HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream)); // Create document final NonLazyUserDataElement root = new NonLazyUserDataElement("workbook"); final NonLazyUserDataDocument resultDocument = new NonLazyUserDataDocument(root); // Add elements for each sheet for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); final Element element = new NonLazyUserDataElement("sheet"); resultDocument.getRootElement().add(element); // Go though each cell XLSUtils.walk(workbook.createDataFormat(), sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (targetXPath != null) { int cellType = cell.getCellType(); String value = null; switch (cellType) { case HSSFCell.CELL_TYPE_STRING: case HSSFCell.CELL_TYPE_BLANK: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: double doubleValue = cell.getNumericCellValue(); if (((double) ((int) doubleValue)) == doubleValue) { // This is an integer value = Integer.toString((int) doubleValue); } else { // This is a floating point number value = XMLUtils.removeScientificNotation(doubleValue); } break; } if (value == null) throw new OXFException("Unkown cell type " + cellType + " for XPath expression '" + targetXPath + "'"); addToElement(element, targetXPath, value); } } }); } return resultDocument; } private void addToElement(Element element, String xpath, String value) { StringTokenizer elements = new StringTokenizer(xpath, "/"); while (elements.hasMoreTokens()) { String name = elements.nextToken(); if (elements.hasMoreTokens()) { // Not the last: try to find sub element, otherwise create Element child = element.element(name); if (child == null) { child = new NonLazyUserDataElement(name); element.add(child); } element = child; } else { // Last: add element, set content to value Element child = new NonLazyUserDataElement(name); child.add(Dom4jUtils.createText(value)); element.add(child); } } } }; addOutput(name, output); return output; }
From source file:org.orbeon.oxf.processor.generator.XLSGenerator.java
License:Open Source License
@Override public ProcessorOutput createOutput(String name) { ProcessorOutput output = new ProcessorOutputImpl(XLSGenerator.this, name) { public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) { try { // Read binary content of uploaded Excel file final byte[] fileContent; {/*w ww. j a v a 2 s.c o m*/ final String NO_FILE = "No file was uploaded"; final DocumentInfo requestDocument = readInputAsTinyTree(context, getInputByName(INPUT_REQUEST), XPathCache.getGlobalConfiguration()); final PooledXPathExpression expr = XPathCache.getXPathExpression( requestDocument.getConfiguration(), requestDocument, "/request/parameters/parameter[1]/value", getLocationData()); final Element valueElement = (Element) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (valueElement == null) throw new OXFException(NO_FILE); String type = valueElement.attributeValue(XMLConstants.XSI_TYPE_QNAME); if (type == null) throw new OXFException(NO_FILE); if (type.endsWith("anyURI")) { // Read file from disk String url = valueElement.getStringValue(); InputStream urlInputStream = new URL(url).openStream(); byte[] buffer = new byte[1024]; ByteArrayOutputStream fileByteArray = new ByteArrayOutputStream(); int size; while ((size = urlInputStream.read(buffer)) != -1) fileByteArray.write(buffer, 0, size); urlInputStream.close(); fileContent = fileByteArray.toByteArray(); } else { // Decode base64 fileContent = Base64.decode(valueElement.getStringValue()); } } // Generate XML from Excel file final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); final org.dom4j.Document d = extractFromXLS(bais); final DOMGenerator domGenerator = new DOMGenerator(d, "xls generator output", DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext); domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver); } catch (IOException e) { throw new OXFException(e); } } private Document extractFromXLS(InputStream inputStream) throws IOException { // Create workbook HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream)); // Create document final NonLazyUserDataElement root = new NonLazyUserDataElement("workbook"); final Document resultDocument = new NonLazyUserDataDocument(root); // Add elements for each sheet for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); final Element element = new NonLazyUserDataElement("sheet"); resultDocument.getRootElement().add(element); // Go though each cell XLSUtils.walk(workbook.createDataFormat(), sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (targetXPath != null) { int cellType = cell.getCellType(); String value = null; switch (cellType) { case HSSFCell.CELL_TYPE_STRING: case HSSFCell.CELL_TYPE_BLANK: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: double doubleValue = cell.getNumericCellValue(); if (((double) ((int) doubleValue)) == doubleValue) { // This is an integer value = Integer.toString((int) doubleValue); } else { // This is a floating point number value = XMLUtils.removeScientificNotation(doubleValue); } break; } if (value == null) throw new OXFException("Unkown cell type " + cellType + " for XPath expression '" + targetXPath + "'"); addToElement(element, targetXPath, value); } } }); } return resultDocument; } private void addToElement(Element element, String xpath, String value) { StringTokenizer elements = new StringTokenizer(xpath, "/"); while (elements.hasMoreTokens()) { String name = elements.nextToken(); if (elements.hasMoreTokens()) { // Not the last: try to find sub element, otherwise create Element child = element.element(name); if (child == null) { child = new NonLazyUserDataElement(name); element.add(child); } element = child; } else { // Last: add element, set content to value Element child = new NonLazyUserDataElement(name); child.add(Dom4jUtils.createText(value)); element.add(child); } } } }; addOutput(name, output); return output; }