List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getColumnIndex
@Override
public int getColumnIndex()
From source file:org.mcisb.excel.ExcelReader.java
License:Open Source License
/** * //from w w w. ja v a2 s. com * @param sheetName */ private void read(final String sheetName) { final HSSFSheet sheet = workbook.getSheet(sheetName); ExcelCell excelCell = null; HSSFRow row = null; int currentRowNumber = 0; while ((row = sheet.getRow(currentRowNumber)) != null) { for (Iterator<?> iterator = row.cellIterator(); iterator.hasNext();) { final Object o = iterator.next(); if (o instanceof HSSFCell) { final HSSFCell cell = (HSSFCell) o; final ExcelCell newExcelCell = new ExcelCell(getValue(cell), currentRowNumber, cell.getColumnIndex()); support.firePropertyChange(EXCEL_CELL, excelCell, newExcelCell); excelCell = newExcelCell; } } currentRowNumber++; } }
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action./*w w w . j ava 2 s . co m*/ * * @throws ServiceException */ public void doOK() throws ServiceException { PersistenceManager pm = this.getPm(); ApplicationContext app = this.getApp(); this.doRefresh(); if (!this.hasPermission) { this.errorMessage = "no permission to run this wizard"; } else { String location = app.getTempFileName(UPLOAD_FILE_FIELD_NAME, ""); try { if (new File(location + ".INFO").exists() && new File(location).exists() && (new File(location).length() > 0)) { String contentMimeType = null; String contentName = null; try { // mimeType and name BufferedReader r = new BufferedReader(new FileReader(location + ".INFO")); contentMimeType = r.readLine(); contentName = r.readLine(); r.close(); new File(location + ".INFO").delete(); } catch (Exception ignore) { } if ((contentName != null) && !contentName.isEmpty() && (contentMimeType != null) && !contentMimeType.isEmpty()) { // the calling object determines which columns are optional/required in the spreadsheet ProductConfigurationTypeSet productConfigurationTypeSet = null; ProductConfigurationType productConfigurationType = null; PropertySet propertySet = null; CrxObject crxObject = null; String callerName = null; String callerParentName = null; ImportTarget importTarget = ImportTarget.NA; // case 1: // required: Property_name // ProductConfigurationTypeSet_name // ProductConfigurationType_name if (this.getObject() instanceof org.opencrx.kernel.product1.jmi1.Segment) { importTarget = ImportTarget.ProductSegment; } // case 2: // required: Property_name // ProductConfigurationType_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) else if (this.getObject() instanceof ProductConfigurationTypeSet) { importTarget = ImportTarget.ProductConfigurationTypeSet; productConfigurationTypeSet = (ProductConfigurationTypeSet) this.getObject(); callerName = ((ProductConfigurationTypeSet) this.getObject()).getName(); } // case 3: // required: Property_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) // ProductConfigurationType_name (if provided, then only Properties of matching ProductConfigurationTypes are considered) else if (this.getObject() instanceof ProductConfigurationType) { importTarget = ImportTarget.ProductConfigurationType; productConfigurationType = (ProductConfigurationType) this.getObject(); callerName = ((ProductConfigurationType) this.getObject()).getName(); RefObject_1_0 parentObj = (RefObject_1_0) pm .getObjectById(this.getObject().refGetPath().getParent().getParent()); if (parentObj instanceof ProductConfigurationTypeSet) { callerParentName = ((ProductConfigurationTypeSet) parentObj).getName(); } } // case 4: // required: Property_name // optional: PropertySet_name (if provided, then only Properties of matching PropertySets are considered) else if (this.getObject() instanceof PropertySet) { importTarget = ImportTarget.PropertySet; propertySet = (PropertySet) this.getObject(); callerName = propertySet.getName(); } // case 5: // required: PropertySet_name // Property_name else if (this.getObject() instanceof CrxObject) { importTarget = ImportTarget.CrxObject; crxObject = (CrxObject) this.getObject(); } // Get product segment org.opencrx.kernel.product1.jmi1.Segment productSegment = Products.getInstance() .getProductSegment(pm, this.getProviderName(), this.getSegmentName()); int idxProperty_dtype = -1; int idxProperty_name = -1; int idxProperty_description = -1; int idxProperty_value = -1; int idxPropertySet_name = -1; int idxPropertySet_description = -1; int idxProductConfigurationTypeSet_name = -1; int idxProductConfigurationTypeSet_description = -1; int idxProductConfigurationType_name = -1; int idxProductConfigurationType_description = -1; // verify whether File exists // Read workbook Workbook wb = null; try { wb = WorkbookFactory.create(new FileInputStream(location)); } catch (Exception e) { this.errorMessage = e.getMessage(); } if (wb != null) { //for (int i = 0; i < workbook.getNumberOfSheets(); i++) { // read first sheet only!!! for (int i = 0; i < 1; i++) { Sheet sheet = wb.getSheetAt(i); int linesRead = 0; int propertiesUpdated = 0; this.importReport += ""; Iterator<Row> rows = sheet.rowIterator(); int nRow = 0; int maxCell = 0; HSSFRow row = null; Map<String, String> attributeMap = new TreeMap<String, String>(); if (rows.hasNext()) { nRow += 1; // read first row with attribute names this.importReport += "<tr class='gridTableHeaderFull'>"; this.importReport += "<td>#</td>"; row = (HSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); int nCell = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > maxCell) { maxCell = nCell; } try { if ((cell.getCellType() == HSSFCell.CELL_TYPE_STRING) && (cell.getStringCellValue() != null)) { boolean isSearchAttribute = false; String cellValue = (cell.getStringCellValue().trim()); attributeMap.put(DECIMAL_FORMAT_0000.format(nCell), cellValue); // get idx of select attributes if (ATTR_PROPERTY_DTYPE.compareToIgnoreCase(cellValue) == 0) { idxProperty_dtype = nCell; } else if (ATTR_PROPERTY_NAME.compareToIgnoreCase(cellValue) == 0) { idxProperty_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTY_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProperty_description = nCell; } else if (ATTR_PROPERTY_VALUE .compareToIgnoreCase(cellValue) == 0) { idxProperty_value = nCell; } else if (ATTR_PROPERTYSET_NAME .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTYSET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_description = nCell; } this.importReport += "<td " + (isSearchAttribute ? "class='searchAttr' title='attribute used for matching'" : "") + ">" + cellValue + "</td>"; } else { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + "[not a string cell]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + ec.getMessage() + "</td>"; } } this.importReport += "</tr>"; } while (rows.hasNext()) { nRow += 1; linesRead += 1; row = (HSSFRow) rows.next(); String propertyType = null; String propertyName = null; String propertyDescription = null; HSSFCell propertyValue = null; String propertySetName = null; String propertySetDescription = null; String productConfigurationTypeSetName = null; String productConfigurationTypeSetDescription = null; String productConfigurationTypeName = null; String productConfigurationTypeDescription = null; String cellId = null; Map<String, Cell> valueMap = new TreeMap<String, Cell>( String.CASE_INSENSITIVE_ORDER); String appendErrorRow = null; this.importReport += "<tr class='gridTableRowFull'>"; this.importReport += "<td id='r" + nRow + "'><b>" + DECIMAL_FORMAT_0000.format(nRow) + "</b></td>"; String jsBuffer = ""; try { Iterator<Cell> cells = row.cellIterator(); int nCell = 0; int currentCell = 0; appendErrorRow = null; while (cells.hasNext()) { //HSSFCell cell = (HSSFCell)row.getCell((short)0); HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > currentCell) { this.importReport += "<td colspan=\"" + (nCell - currentCell) + "\" class=\"empty\"> </td>"; } currentCell = nCell + 1; try { cellId = "id='r" + nRow + (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString().toUpperCase() + "'"; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue().trim(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); if (nCell == idxProperty_dtype) { propertyType = cellValue; } else if (nCell == idxProperty_name) { propertyName = cellValue; } else if (nCell == idxProperty_description) { propertyDescription = cellValue; } else if (nCell == idxProperty_value) { propertyValue = cell; } else if (nCell == idxPropertySet_name) { propertySetName = cellValue; } else if (nCell == idxPropertySet_description) { propertySetDescription = cellValue; } else if (nCell == idxProductConfigurationTypeSet_name) { productConfigurationTypeSetName = cellValue; } else if (nCell == idxProductConfigurationTypeSet_description) { productConfigurationTypeSetDescription = cellValue; } else if (nCell == idxProductConfigurationType_name) { productConfigurationTypeName = cellValue; } else if (nCell == idxProductConfigurationType_description) { productConfigurationTypeDescription = cellValue; } this.importReport += "<td " + cellId + ">" + (cellValue != null ? (cellValue.replace("\r\n", EOL_HTML)).replace("\n", EOL_HTML) : "") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (nCell == idxProperty_value) { propertyValue = cell; } BigDecimal cellValue = new BigDecimal( cell.getNumericCellValue()); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + cellValue + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (nCell == idxProperty_value) { propertyValue = cell; } boolean cellValue = cell.getBooleanCellValue(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + (cellValue ? "TRUE" : "FALSE") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + " class=\"empty\"> </td>"; } else { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + "[cell-type (" + cell.getCellType() + ") not supported]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + cell.getCellFormula() + "</td>"; } } if (nCell < maxCell) { this.importReport += "<td colspan=\"" + (maxCell - nCell) + "\" class=\"empty\"></td>"; } } catch (Exception e) { this.importReport += "<td class='err' colspan=\"" + (maxCell + 2) + "\">ERROR in Attribute Row!</td>"; } // process row Property property = null; if (this.isSupportedDtypeValue(propertyType, propertyValue)) { /* case 1 */ if (importTarget == ImportTarget.ProductSegment && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from Product Segment)';"; if (propertySetName == null || propertySetName.isEmpty()) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (org.opencrx.kernel.product1.jmi1.ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 2 */ } else if (importTarget == ImportTarget.ProductConfigurationTypeSet && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationTypeSet)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerName != null && productConfigurationTypeSetName != null && callerName.equals(productConfigurationTypeSetName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 3 */ } else if (importTarget == ImportTarget.ProductConfigurationType && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationType)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerParentName != null && productConfigurationTypeSetName != null && callerParentName .equals(productConfigurationTypeSetName))) && ((productConfigurationTypeName == null || productConfigurationTypeName.isEmpty()) || (callerName != null && productConfigurationTypeName != null && callerName .equals(productConfigurationTypeName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm.getObjectById( property.refGetPath().getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 4 */ } else if (importTarget == ImportTarget.PropertySet && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from PropertySet)';"; if (((propertySetName == null || propertySetName.isEmpty()) || (callerName != null && propertySetName != null && callerName.equals(propertySetName))) && (productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, null, propertySet, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 5 */ } else if (importTarget == ImportTarget.CrxObject && propertyName != null && !propertyName.isEmpty() && propertySetName != null && !propertySetName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from CrxObject)';"; if ((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { //createOrUpdatePropertyOfPropertySet property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, crxObject, null, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } } else { // incomplete and/or inconsistent row --> disregard this row jsBuffer += "$('r" + nRow + "').title += 'incomplete and/or inconsistent row';"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CELL VALUE TYPE NOT SUPPORTED</td></tr>"; } this.importReport += "</tr>"; if (property != null) { propertiesUpdated++; cellId = "r" + nRow + ATTR_PROPERTY_NAME.toUpperCase(); jsBuffer += "try{$('r" + nRow + "').className += ' ok';$('" + cellId + "').className=' ok';$('" + cellId + "').innerHTML = '<a href=\"" + this.getSelectObjectHref(property) + "\" target=\"_blank\"><b>' + " + "$('" + cellId + "').innerHTML +" + "'</b></a>'}catch(e){};"; cellId = "r" + nRow + ATTR_PROPERTY_VALUE.toUpperCase(); if (propertyValue != null) { jsBuffer += "try{$('" + cellId + "').className='ok';}catch(e){};"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>VERIFY Property DTYPE/NAME/VALUE</td></tr>"; jsBuffer += "$('r" + nRow + "').className += 'nok';"; } if (appendErrorRow != null) { this.importReport += appendErrorRow; } valueMap = null; this.importReport += "<tr style=\"display:none;\">"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += jsBuffer.length() > 0 ? "<script language='javascript' type='text/javascript'>" + jsBuffer + "</script>" : ""; this.importReport += " </td>"; this.importReport += "</tr>"; } /* while */ // Spacer this.importReport += "<tr class='gridTableRowFull' style=\"background-color:white;\">"; this.importReport += " <td colspan='" + (maxCell + 2) + "'> </td>"; this.importReport += "</tr>"; // Summary this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += " Sheet: <b>" + wb.getSheetName(i) + "</b> |"; this.importReport += " data lines <b>read: " + linesRead + "</b><br>"; this.importReport += "</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td> </td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">Created / Updated</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td>Properties</td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">" + propertiesUpdated + "</td>"; this.importReport += "</tr>"; if (linesRead != propertiesUpdated) { this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td class=\"err\" colspan=\"" + (maxCell + 2) + "\">WARNING: some data lines were not processed due to data errors (e.g. multiple matches, missing name, etc.)</td>"; this.importReport += "</tr>"; } } } } } else { this.errorMessage = "No Excel workbook selected"; } } finally { new File(location).delete(); } } }
From source file:org.openmicroscopy.shoola.util.file.SheetInfo.java
License:Open Source License
/** Auto-sizes all columns to fit contents. */ void sizeAllColumnsToFit() { Map<Integer, Integer> colMap = new HashMap<Integer, Integer>(); Iterator rowIterator = sheet.iterator();//sheet.rowIterator(); HSSFCell cell; HSSFRow row;/* www. j a v a2 s . co m*/ Iterator k; while (rowIterator.hasNext()) { row = (HSSFRow) rowIterator.next(); k = row.cellIterator(); while (k.hasNext()) { cell = (HSSFCell) k.next(); colMap.put(cell.getColumnIndex(), cell.getRowIndex()); } } Iterator<Integer> colIterator = colMap.keySet().iterator(); int col; while (colIterator.hasNext()) { col = colIterator.next(); sizeColumnToFit(col); } }
From source file:org.rti.zcore.dar.utils.PoiUtils.java
License:Apache License
/** * This utility is a version of HSSF.main that does not use deprecated methods. * It is helpful in figuring out what row a filed is on when outputting Excel files via POI. * @param pathExcelMaster/* w w w .java 2s .c om*/ */ public static void testExcelOutput(String pathExcelMaster) { try { //HSSF hssf = new HSSF(args[ 0 ]); System.out.println("Data dump:\n"); //HSSFWorkbook wb = hssf.hssfworkbook; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster)); HSSFWorkbook wb = new HSSFWorkbook(fs); for (int k = 0; k < wb.getNumberOfSheets(); k++) { System.out.println("Sheet " + k); HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { //HSSFRow row = sheet.getPhysicalRowAt(r); HSSFRow row = sheet.getRow(r); if (row != null) { int cells = row.getPhysicalNumberOfCells(); System.out.println("ROW " + row.getRowNum()); for (int c = 0; c < cells; c++) { //HSSFCell cell = row.getPhysicalCellAt(c); HSSFCell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA "; value = "FORMULA " + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: //value = "STRING value=" + cell.getStringCellValue(); HSSFRichTextString str = cell.getRichStringCellValue(); value = "STRING value=" + str; break; default: } //System.out.println("CELL col=" + cell.getCellNum() + " VALUE=" + value); System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java
License:Apache License
public boolean isNotTrimTarget(DfDataTable table, HSSFCell cell) { final String tableName = table.getTableDbName(); if (!_notTrimTableColumnMap.containsKey(tableName)) { return false; }//from w w w. j a v a 2s. c o m final List<String> notTrimTargetColumnList = _notTrimTableColumnMap.get(tableName); final DfDataColumn column = table.getColumn(cell.getColumnIndex()); final String target = column.getColumnDbName(); for (String specified : notTrimTargetColumnList) { if (target.equalsIgnoreCase(specified)) { return true; } } return false; }
From source file:org.seasar.dbflute.helper.io.xls.DfXlsReader.java
License:Apache License
public boolean isNotTrimTarget(HSSFCell cell, DfDataTable table) { final String tableName = table.getTableDbName(); if (!_notTrimTableColumnMap.containsKey(tableName)) { return false; }//ww w. j a va2 s.c o m final List<String> notTrimTargetColumnList = _notTrimTableColumnMap.get(tableName); final DfDataColumn column = table.getColumn(cell.getColumnIndex()); final String targetColumnName = column.getColumnDbName(); for (String currentColumnName : notTrimTargetColumnList) { if (targetColumnName.equalsIgnoreCase(currentColumnName)) { return true; } } return false; }
From source file:org.sharegov.cirm.utils.ExcelExportUtil.java
License:Apache License
public Integer searchCriteriaRows1(Json searchCriteria, HSSFCellStyle boldCenterStyle, int totalRecords) { int rowCounter = 0; int cellCounter = 0; //Create Headings and info about the report HSSFRow row = sheet.createRow(rowCounter++); HSSFCell cell = row.createCell(cellCounter++); cell.setCellValue(basicSearchReportHeader); cell.setCellStyle(boldCenterStyle);//www . j a v a 2 s . c om sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 3)); row = sheet.createRow(rowCounter++); cellCounter = 0; cell = row.createCell(cellCounter++); cell.setCellValue(searchCriteriaHeader); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 2)); for (Entry<String, Json> prop : searchCriteria.asJsonMap().entrySet()) { row = sheet.createRow(rowCounter++); cellCounter = 0; cell = row.createCell(cellCounter++); cell.setCellValue(prop.getKey()); cell = row.createCell(cellCounter++); cell.setCellValue(prop.getValue().asString()); } row = sheet.createRow(rowCounter++); row = sheet.createRow(rowCounter++); cellCounter = 0; cell = row.createCell(cellCounter++); cell.setCellValue(totalResults + " : " + totalRecords); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 1)); row = sheet.createRow(rowCounter++); return rowCounter; }
From source file:org.sharegov.cirm.utils.ExcelExportUtil.java
License:Apache License
public Integer searchCriteriaRows(Json allData, HSSFCellStyle boldStyle) { Json basicSCLabels = Json.object().set("type", "SR Type").set("legacy:hasCaseNumber", "SR ID") .set("atAddress", "Address").set("isCreatedBy", "Input By").set("name", "First Name") .set("lastName", "Last Name").set("legacy:hasStatus", "Status") .set("legacy:hasIntakeMethod", "Intake Method").set("legacy:hasPriority", "Priority") .set("serviceQuestion", "SR Question").set("legacy:hasDueDate", "Over Due Date") .set("hasDateCreated", "Created Date").set("legacy:hasServiceCaseActor", "Customer Name") .set("gisColumnName", ""); Json searchCriteria = allData.at("searchCriteria"); OWLOntology ont = ontology();//from w ww .ja v a2s . co m int rowCounter = 0; //int cellCounter = 0; //Create Headings and info about the report HSSFRow row = sheet.createRow(rowCounter++); HSSFCell cell = row.createCell(0); cell.setCellValue(basicSearchReportHeader); cell.setCellStyle(boldStyle); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 3)); row = sheet.createRow(rowCounter++); row = sheet.createRow(rowCounter++); // cellCounter = 0; cell = row.createCell(0); cell.setCellValue(searchCriteriaHeader); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 2)); for (Entry<String, Json> prop : searchCriteria.asJsonMap().entrySet()) { if (prop.getKey().equalsIgnoreCase("sortBy") || prop.getKey().equalsIgnoreCase("caseSensitive") || prop.getKey().equalsIgnoreCase("sortDirection") || prop.getKey().equalsIgnoreCase("currentPage") || prop.getKey().equalsIgnoreCase("itemsPerPage")) continue; if (prop.getKey().equalsIgnoreCase("type")) { if (prop.getValue().isString() && prop.getValue().asString().equalsIgnoreCase("legacy:ServiceCase")) continue; if (prop.getValue().isArray()) { List<Json> typeList = prop.getValue().asJsonList(); if (typeList.size() == 1 && typeList.get(0).asString().equalsIgnoreCase("legacy:ServiceCase")) continue; } } StringBuilder sbQuestion = new StringBuilder(""); StringBuilder sbAnswer = new StringBuilder(""); sbQuestion.append( basicSCLabels.has(prop.getKey()) ? basicSCLabels.at(prop.getKey()).asString() : prop.getKey()); if (prop.getKey().equalsIgnoreCase("type")) { //sbAnswer.append(getEntityLabel(individual(prop.getValue().asString()))); sbAnswer.append("SR TYPE"); } else if (ont.isDeclared(dataProperty(fullIri(prop.getKey())), true)) { sbAnswer.append(prop.getValue().asString()); } else if (ont.isDeclared(objectProperty(fullIri(prop.getKey())), true)) { if (prop.getKey().equals("legacy:hasStatus") || prop.getKey().equals("legacy:hasIntakeMethod") || prop.getKey().equals("legacy:hasPriority")) { sbAnswer.append(getEntityLabel(individual(prop.getValue().at("iri").asString()))); } if (prop.getKey().equals("atAddress")) { if (prop.getValue().has("fullAddress")) sbAnswer.append(prop.getValue().at("fullAddress").asString()); if (prop.getValue().has("Street_Unit_Number")) sbAnswer.append("#").append(prop.getValue().at("Street_Unit_Number").asString()); if (prop.getValue().has("Street_Address_City")) sbAnswer.append(", ").append(ServiceRequestReportUtil.getCity(prop.getValue())); if (prop.getValue().has("Zip_Code")) sbAnswer.append(" - ").append(prop.getValue().at("Zip_Code").asString()); } else if (prop.getKey().equals("legacy:hasServiceCaseActor")) { if (prop.getValue().has("Name")) sbAnswer.append(prop.getValue().at("Name").asString()); if (prop.getValue().has("LastName")) sbAnswer.append(" ").append(prop.getValue().at("LastName").asString()); } else if (prop.getKey().equals("hasGeoPropertySet")) { sbQuestion = new StringBuilder(""); for (Entry<String, Json> geoProp : prop.getValue().asJsonMap().entrySet()) { if (geoProp.getKey().equals("type")) continue; else { sbQuestion.append(geoProp.getKey()); sbAnswer.append(geoProp.getValue().asString()); } } } } else if (ont.containsIndividualInSignature(fullIri(prop.getKey()), true)) { sbQuestion = new StringBuilder(""); sbQuestion.append(getEntityLabel(individual(prop.getKey()))); if (prop.getValue().isString()) sbAnswer.append(getEntityLabel(individual(prop.getValue().asString()))); else if (prop.getValue().isObject()) sbAnswer.append(prop.getValue().at("literal").asString()); } else { continue; } row = sheet.createRow(rowCounter++); // cellCounter = 0; cell = row.createCell(0); cell.setCellValue(sbQuestion.toString()); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 1)); cell = row.createCell(2); cell.setCellValue(sbAnswer.toString()); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + 1)); } row = sheet.createRow(rowCounter++); row = sheet.createRow(rowCounter++); // cellCounter = 0; cell = row.createCell(0); cell.setCellValue(totalResults); cell.setCellStyle(boldStyle); cell = row.createCell(1); cell.setCellValue(allData.at("data").asJsonList().size()); // boldStyle.setFillForegroundColor(HSSFColor.YELLOW.index); // boldStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cell.setCellStyle(boldStyle); row = sheet.createRow(rowCounter++); return rowCounter; }
From source file:org.testeditor.core.importer.ExcelFileImporter.java
License:Open Source License
/** * Iterates through the cells in a row an creates a {@link TestDataRow} * Object.//from w w w . ja v 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:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main//www .j av a 2 s . c om * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }