List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action./*from ww w.j a va2 s . c o 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.openmrs.module.kenyametadatatools.mflsync.MflSyncFromRemoteSpreadsheetTask.java
License:Open Source License
/** * Extracts the value of the given cell/*from ww w .ja v a2s . com*/ * @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 int getIntegerValue(HSSFRow row, int i) { HSSFCell c = row.getCell((short) i); if (c == null) return 0; return (int) c.getNumericCellValue(); }
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);/*ww w. ja va 2s. c o m*/ 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.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Reads a simply formatted, single sheet Excel document into a list of <code>Map</code>. * @param stream an <code>InputStream</code> of the excel document * @param columnNames a List containing the keys to use when mapping the columns into the Map (column 1 goes in the Map key columnNames 1, etc ...) * @param skipRows number of rows to skip, typically 1 to skip the header row * @return the List of Map representing the rows * @throws IOException if an error occurs *//*from w w w. j a v a2s . c om*/ public static List<Map<String, String>> readExcelFile(InputStream stream, List<String> columnNames, int skipRows) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); int sheetLastRowNumber = sheet.getLastRowNum(); List<Map<String, String>> rows = new ArrayList<Map<String, String>>(); for (int j = skipRows; j <= sheetLastRowNumber; j++) { HSSFRow erow = sheet.getRow(j); Map<String, String> row = new HashMap<String, String>(); for (int i = 0; i < columnNames.size(); i++) { String columnName = columnNames.get(i); HSSFCell cell = erow.getCell(i); String s = ""; if (cell != null) { // check if cell contains a number BigDecimal bd = null; try { double d = cell.getNumericCellValue(); bd = BigDecimal.valueOf(d); } catch (Exception e) { // do nothing } if (bd == null) { s = cell.toString().trim(); } else { // if cell contains number trim the tailing zeros so that for example postal code string // does not appear as a floating point number s = bd.toPlainString(); // convert XX.XX000 into XX.XX s = s.replaceFirst("^(-?\\d+\\.0*[^0]+)0*\\s*$", "$1"); // convert XX.000 into XX s = s.replaceFirst("^(-?\\d+)\\.0*$", "$1"); } } Debug.logInfo("readExcelFile cell (" + j + ", " + i + ") as (" + columnName + ") == " + s, MODULE); row.put(columnName, s); } rows.add(row); } return rows; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Helper method to read a String cell and auto trim it. * @param row a <code>HSSFRow</code> value * @param index the column index <code>int</code> value which is then casted to a short * @return a <code>String</code> value *///from w w w .jav a 2 s . c om public String readStringCell(HSSFRow row, int index) { HSSFCell cell = row.getCell(index); if (cell == null) { return null; } // check if cell contains a number BigDecimal bd = null; try { double d = cell.getNumericCellValue(); bd = BigDecimal.valueOf(d); } catch (Exception e) { // do nothing } String s = null; if (bd == null) { s = cell.toString().trim(); } else { // if cell contains number parse it as long s = Long.toString(bd.longValue()); } return s; }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Helper method to read a Long cell and auto trim it. * @param row a <code>HSSFRow</code> value * @param index the column index <code>int</code> value which is then casted to a short * @return a <code>Long</code> value *//*from www .j a v a2 s .c o m*/ public Long readLongCell(HSSFRow row, int index) { HSSFCell cell = row.getCell(index); if (cell == null) { return null; } BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()); if (bd == null) { return null; } return bd.longValue(); }
From source file:org.opentaps.dataimport.ExcelImportServices.java
License:Open Source License
/** * Helper method to read a BigDecimal cell and auto trim it. * @param row a <code>HSSFRow</code> value * @param index the column index <code>int</code> value which is then casted to a short * @return a <code>BigDecimal</code> value *//*from www . j a v a 2 s.co m*/ public BigDecimal readBigDecimalCell(HSSFRow row, int index) { HSSFCell cell = row.getCell(index); if (cell == null) { return null; } return BigDecimal.valueOf(cell.getNumericCellValue()); }
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); }/*from w w w . ja v a 2 s .c o 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; {/*from w w w . j ava 2s . com*/ 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; }