List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getBooleanCellValue
@Override public boolean getBooleanCellValue()
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action./*ww w.j av a 2s. com*/ * * @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.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) { // Copy cell content destination.setCellType(source.getCellType()); switch (source.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: destination.setCellValue(source.getBooleanCellValue()); break;//from w w w .j av a2s . co m case HSSFCell.CELL_TYPE_FORMULA: case HSSFCell.CELL_TYPE_STRING: destination.setCellValue(source.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: destination.setCellValue(source.getNumericCellValue()); break; } // Copy cell style HSSFCellStyle sourceCellStyle = source.getCellStyle(); HSSFCellStyle destinationCellStyle = workbook.createCellStyle(); destinationCellStyle.setAlignment(sourceCellStyle.getAlignment()); destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom()); destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft()); destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight()); destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop()); destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor()); destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat()); destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern()); destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex())); destinationCellStyle.setHidden(sourceCellStyle.getHidden()); destinationCellStyle.setIndention(sourceCellStyle.getIndention()); destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor()); destinationCellStyle.setLocked(sourceCellStyle.getLocked()); destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor()); destinationCellStyle.setRotation(sourceCellStyle.getRotation()); destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor()); destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment()); destinationCellStyle.setWrapText(sourceCellStyle.getWrapText()); destination.setCellStyle(destinationCellStyle); }
From source file:org.projectforge.excel.ExcelImport.java
License:Open Source License
/** * convert the cell-value to the type in the bean. * //w ww .j a v a2s.c o m * @param cell the cell containing an arbitrary value * @param destClazz the target class * @return a String, Boolean, Date or BigDecimal */ private Object toNativeType(final HSSFCell cell, final Class<?> destClazz) { if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: log.debug("using numeric"); if (Date.class.isAssignableFrom(destClazz)) { return cell.getDateCellValue(); } String strVal = String.valueOf(cell.getNumericCellValue()); strVal = strVal.replaceAll("\\.0*$", ""); return ConvertUtils.convert(strVal, destClazz); case HSSFCell.CELL_TYPE_BOOLEAN: log.debug("using boolean"); return Boolean.valueOf(cell.getBooleanCellValue()); case HSSFCell.CELL_TYPE_STRING: log.debug("using string"); strVal = StringUtils.trimToNull(cell.getStringCellValue()); return ConvertUtils.convert(strVal, destClazz); case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_FORMULA: return new Formula(cell.getCellFormula()); default: return StringUtils.trimToNull(cell.getStringCellValue()); } }
From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java
License:Apache License
protected Object extractCellValue(DfDataTable table, int columnIndex, HSSFRow row, HSSFCell cell) { if (cell == null) { return isEmptyStringTarget(table, columnIndex) ? "" : null; }//from ww w .ja v a 2s.c o m switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { return DfTypeUtil.toTimestamp(cell.getDateCellValue()); } final double numericCellValue = cell.getNumericCellValue(); if (isInt(numericCellValue)) { return new BigDecimal((int) numericCellValue); } return new BigDecimal(Double.toString(numericCellValue)); case HSSFCell.CELL_TYPE_STRING: return processRichStringCellValue(table, columnIndex, row, cell); case HSSFCell.CELL_TYPE_BOOLEAN: boolean b = cell.getBooleanCellValue(); return Boolean.valueOf(b); default: return isEmptyStringTarget(table, columnIndex) ? "" : null; } }
From source file:org.seasar.dbflute.helper.io.xls.DfXlsReader.java
License:Apache License
public Object getValue(int columnIndex, HSSFCell cell, DfDataTable table) { if (cell == null) { if (isEmptyStringTarget(columnIndex, table)) { return "\"\""; // for preventing trimming later } else {/*from ww w . j a v a2 s.c om*/ return null; } } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { return DfTypeUtil.toTimestamp(cell.getDateCellValue()); } final double numericCellValue = cell.getNumericCellValue(); if (isInt(numericCellValue)) { return new BigDecimal((int) numericCellValue); } return new BigDecimal(Double.toString(numericCellValue)); case HSSFCell.CELL_TYPE_STRING: String s = cell.getRichStringCellValue().getString(); if (s != null) { if (isNotTrimTarget(cell, table)) { if (s.length() != s.trim().length()) { s = "\"" + s + "\""; // for preventing trimming later } } else { s = Srl.rtrim(s); } } if ("".equals(s)) { s = null; } if (isEmptyStringTarget(columnIndex, table) && s == null) { s = "\"\""; // for preventing trimming later } if (isCellBase64Formatted(cell)) { return DfTypeUtil.decodeAsBase64(s); } return s; case HSSFCell.CELL_TYPE_BOOLEAN: boolean b = cell.getBooleanCellValue(); return Boolean.valueOf(b); default: if (isEmptyStringTarget(columnIndex, table)) { return "\"\""; } else { return null; } } }
From source file:org.seasar.extension.dataset.impl.XlsReader.java
License:Apache License
/** * ????/*from w w w.j av a 2 s .co m*/ * * @param cell * * @return ? */ public Object getValue(HSSFCell cell) { if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { return TimestampConversionUtil.toTimestamp(cell.getDateCellValue()); } final double numericCellValue = cell.getNumericCellValue(); if (isInt(numericCellValue)) { return new BigDecimal((int) numericCellValue); } return new BigDecimal(Double.toString(numericCellValue)); case HSSFCell.CELL_TYPE_STRING: String s = cell.getRichStringCellValue().getString(); if (s != null) { s = StringUtil.rtrim(s); if (!trimString && s.length() > 1 && s.startsWith("\"") && s.endsWith("\"")) { s = s.substring(1, s.length() - 1); } } if ("".equals(s)) { s = null; } if (isCellBase64Formatted(cell)) { return Base64Util.decode(s); } return s; case HSSFCell.CELL_TYPE_BOOLEAN: boolean b = cell.getBooleanCellValue(); return Boolean.valueOf(b); default: 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./* w w w . j av a 2 s .c o m*/ * * @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.wso2.ws.dataservice.DBUtils.java
License:Apache License
private static OMElement getExcelResult(HSSFWorkbook wb, OMElement queryElement, AxisService axisService) throws AxisFault { OMElement resultElement = null;//from w w w . ja v a 2 s .co m //OMElement excelElement = queryElement.getFirstChildWithName(new QName("excel")); ExcelQuery excelQuery = new ExcelQuery(axisService, queryElement); OMElement result = queryElement.getFirstChildWithName(new QName("result")); String wrapperElementName = result.getAttributeValue(new QName("element")); String rowElementName = result.getAttributeValue(new QName("rowName")); String columnDefalut = result.getAttributeValue(new QName("columnDefault")); OMFactory fac = OMAbstractFactory.getOMFactory(); OMNamespace omNs = fac.createOMNamespace(axisService.getTargetNamespace(), "data"); resultElement = fac.createOMElement(wrapperElementName, omNs); if (!axisService.isElementFormDefault()) { omNs = fac.createOMNamespace("", "data"); } //Retrieve the sheet name, if user has set it in configuration file String sheetName = excelQuery.getWorkBookName(); //this is used to skip header columns, the spread sheet int startReadingFromRow = excelQuery.getStartingRow(); if (startReadingFromRow >= 0) { //rows start from 0 startReadingFromRow = startReadingFromRow - 1; } int maxRowCount = excelQuery.getMaxRowCount(); HSSFSheet sheet = wb.getSheet(sheetName); int rowCount = sheet.getPhysicalNumberOfRows(); //If hasHeaders is set to true, we need first row object in later stage. HSSFRow firstRow = null; if (excelQuery.hasHeaders()) { //assumption : first row is the header row firstRow = sheet.getRow(0); } int processedRowCount = 0; for (int r = 0; r < rowCount; r++) { if (r >= startReadingFromRow) { if (processedRowCount == maxRowCount) { break; } HSSFRow hssfRow = sheet.getRow(r); OMElement row = fac.createOMElement(rowElementName, omNs); if (rowElementName == null) { row = resultElement; } Iterator elements = result.getChildElements(); while (elements.hasNext()) { OMElement element = (OMElement) elements.next(); if (element.getLocalName().equals("element")) { String displayTagName = element.getAttributeValue(new QName("name")); String columnValue = element.getAttributeValue(new QName("column")); short a = 1; short columnNumber; if (excelQuery.hasHeaders()) { //if hasHeaders is set to true, column Names should be specified //get the column number using specified name columnNumber = getExcelColumnNumber(columnValue, firstRow); } else { try { columnNumber = (short) (Short.valueOf(columnValue).shortValue() - a); } catch (NumberFormatException e) { log.error("Column value for element : " + displayTagName + " should be a number.", e); throw new AxisFault( "Column value for element : " + displayTagName + " should be a number."); } } HSSFCell hssfCell = hssfRow.getCell(columnNumber); String elementValue = ""; if (hssfCell != null) { if (HSSFCell.CELL_TYPE_STRING == hssfCell.getCellType()) { elementValue = hssfCell.getRichStringCellValue().getString(); } else if (HSSFCell.CELL_TYPE_BLANK == hssfCell.getCellType()) { //do nothing } else if (HSSFCell.CELL_TYPE_BOOLEAN == hssfCell.getCellType()) { elementValue = String.valueOf(hssfCell.getBooleanCellValue()); } else if (HSSFCell.CELL_TYPE_FORMULA == hssfCell.getCellType()) { elementValue = "{formula}"; } else if (HSSFCell.CELL_TYPE_NUMERIC == hssfCell.getCellType()) { elementValue = String.valueOf(hssfCell.getNumericCellValue()); } } if (columnDefalut == null || columnDefalut.equals("element")) { OMElement rowElement = fac.createOMElement(displayTagName, omNs); rowElement.addChild(fac.createOMText(rowElement, elementValue)); row.addChild(rowElement); } else if (columnDefalut.equals("attribute")) { row.addAttribute(displayTagName, elementValue, omNs); } } } if (rowElementName != null) { resultElement.addChild(row); processedRowCount++; } } //end of if( k >= startReadingFromRow) } //for (int r = 0; r < rowCount; r++) return resultElement; }
From source file:poi.hssf.view.SVTableCellEditor.java
License:Apache License
/** * Gets the tableCellEditorComponent attribute of the SVTableCellEditor object * * @return The tableCellEditorComponent value *///from w w w . j av a2s . c o m public Component getTableCellEditorComponent(JTable table, Object value, boolean isSelected, int row, int column) { System.out.println("GetTableCellEditorComponent"); HSSFCell cell = (HSSFCell) value; if (cell != null) { HSSFCellStyle style = cell.getCellStyle(); HSSFFont f = wb.getFontAt(style.getFontIndex()); boolean isbold = f.getBoldweight() > HSSFFont.BOLDWEIGHT_NORMAL; boolean isitalics = f.getItalic(); int fontstyle = Font.PLAIN; if (isbold) fontstyle = Font.BOLD; if (isitalics) fontstyle = fontstyle | Font.ITALIC; int fontheight = f.getFontHeightInPoints(); if (fontheight == 9) fontheight = 10; //fix for stupid ol Windows Font font = new Font(f.getFontName(), fontstyle, fontheight); editor.setFont(font); if (style.getFillPattern() == HSSFCellStyle.SOLID_FOREGROUND) { editor.setBackground(getAWTColor(style.getFillForegroundColor(), white)); } else editor.setBackground(white); editor.setForeground(getAWTColor(f.getColor(), black)); //Set the value that is rendered for the cell switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: editor.setText(""); break; case HSSFCell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { editor.setText("true"); } else { editor.setText("false"); } break; case HSSFCell.CELL_TYPE_NUMERIC: editor.setText(Double.toString(cell.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_STRING: editor.setText(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_FORMULA: default: editor.setText("?"); } switch (style.getAlignment()) { case HSSFCellStyle.ALIGN_LEFT: case HSSFCellStyle.ALIGN_JUSTIFY: case HSSFCellStyle.ALIGN_FILL: editor.setHorizontalAlignment(SwingConstants.LEFT); break; case HSSFCellStyle.ALIGN_CENTER: case HSSFCellStyle.ALIGN_CENTER_SELECTION: editor.setHorizontalAlignment(SwingConstants.CENTER); break; case HSSFCellStyle.ALIGN_GENERAL: case HSSFCellStyle.ALIGN_RIGHT: editor.setHorizontalAlignment(SwingConstants.RIGHT); break; default: editor.setHorizontalAlignment(SwingConstants.LEFT); break; } } return editor; }
From source file:poi.hssf.view.SVTableCellRenderer.java
License:Apache License
public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) { boolean isBorderSet = false; //If the JTables default cell renderer has been setup correctly the //value will be the HSSFCell that we are trying to render HSSFCell c = (HSSFCell) value; if (c != null) { HSSFCellStyle s = c.getCellStyle(); HSSFFont f = wb.getFontAt(s.getFontIndex()); setFont(SVTableUtils.makeFont(f)); if (s.getFillPattern() == HSSFCellStyle.SOLID_FOREGROUND) { setBackground(SVTableUtils.getAWTColor(s.getFillForegroundColor(), SVTableUtils.white)); } else//from ww w.j a v a 2s . co m setBackground(SVTableUtils.white); setForeground(SVTableUtils.getAWTColor(f.getColor(), SVTableUtils.black)); cellBorder.setBorder(SVTableUtils.getAWTColor(s.getTopBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getRightBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getBottomBorderColor(), SVTableUtils.black), SVTableUtils.getAWTColor(s.getLeftBorderColor(), SVTableUtils.black), s.getBorderTop(), s.getBorderRight(), s.getBorderBottom(), s.getBorderLeft(), hasFocus); setBorder(cellBorder); isBorderSet = true; //Set the value that is rendered for the cell switch (c.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: setValue(""); break; case HSSFCell.CELL_TYPE_BOOLEAN: if (c.getBooleanCellValue()) { setValue("true"); } else { setValue("false"); } break; case HSSFCell.CELL_TYPE_NUMERIC: short format = s.getDataFormat(); double numericValue = c.getNumericCellValue(); if (cellFormatter.useRedColor(format, numericValue)) setForeground(Color.red); else setForeground(null); setValue(cellFormatter.format(format, c.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_STRING: setValue(c.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_FORMULA: default: setValue("?"); } //Set the text alignment of the cell switch (s.getAlignment()) { case HSSFCellStyle.ALIGN_LEFT: case HSSFCellStyle.ALIGN_JUSTIFY: case HSSFCellStyle.ALIGN_FILL: setHorizontalAlignment(SwingConstants.LEFT); break; case HSSFCellStyle.ALIGN_CENTER: case HSSFCellStyle.ALIGN_CENTER_SELECTION: setHorizontalAlignment(SwingConstants.CENTER); break; case HSSFCellStyle.ALIGN_GENERAL: case HSSFCellStyle.ALIGN_RIGHT: setHorizontalAlignment(SwingConstants.RIGHT); break; default: setHorizontalAlignment(SwingConstants.LEFT); break; } } else { setValue(""); setBackground(SVTableUtils.white); } if (hasFocus) { if (!isBorderSet) { //This is the border to paint when there is no border //and the cell has focus cellBorder.setBorder(SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, SVTableUtils.black, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, HSSFCellStyle.BORDER_NONE, isSelected); setBorder(cellBorder); } if (table.isCellEditable(row, column)) { setForeground(UIManager.getColor("Table.focusCellForeground")); setBackground(UIManager.getColor("Table.focusCellBackground")); } } else if (!isBorderSet) { setBorder(noFocusBorder); } // ---- begin optimization to avoid painting background ---- Color back = getBackground(); boolean colorMatch = (back != null) && (back.equals(table.getBackground())) && table.isOpaque(); setOpaque(!colorMatch); // ---- end optimization to aviod painting background ---- return this; }