List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractorTest.java
License:Apache License
private Sheet sheet(String name) { try (InputStream in = getClass().getResourceAsStream("rule/" + name)) { assertThat(name, in, not(nullValue())); Workbook book = Util.openWorkbookFor(name, in); return book.getSheetAt(0); } catch (IOException e) { throw new AssertionError(e); }/*from w w w . j a v a 2 s . com*/ }
From source file:com.asakusafw.testdriver.excel.ExcelSheetDataModelSourceTest.java
License:Apache License
private ExcelSheetDataModelSource open(String file) throws IOException { URL resource = getClass().getResource("data/" + file); assertThat(file, resource, not(nullValue())); URI uri;/*from ww w . j a v a2s . c o m*/ try { uri = resource.toURI(); } catch (URISyntaxException e) { throw new AssertionError(e); } try (InputStream in = resource.openStream()) { Workbook book = Util.openWorkbookFor(file, in); Sheet sheet = book.getSheetAt(0); return new ExcelSheetDataModelSource(SIMPLE, uri, sheet); } }
From source file:com.asakusafw.testdriver.excel.ExcelSheetSinkTest.java
License:Apache License
/** * many columns./*from ww w. ja v a 2 s .co m*/ * @throws Exception if occur */ @Test public void many_columns() throws Exception { Object[] value = new Object[256]; Map<PropertyName, PropertyType> map = new TreeMap<>(); for (int i = 0; i < value.length; i++) { map.put(PropertyName.newInstance(String.format("p%04x", i)), PropertyType.INT); value[i] = i; } ArrayModelDefinition def = new ArrayModelDefinition(map); File file = folder.newFile("temp.xls"); ExcelSheetSinkFactory factory = new ExcelSheetSinkFactory(file); try (DataModelSink sink = factory.createSink(def, new TestContext.Empty())) { sink.put(def.toReflection(value)); } try (InputStream in = new FileInputStream(file)) { Workbook workbook = Util.openWorkbookFor(file.getPath(), in); Sheet sheet = workbook.getSheetAt(0); Row title = sheet.getRow(0); assertThat(title.getLastCellNum(), is((short) 256)); Row content = sheet.getRow(1); for (int i = 0; i < title.getLastCellNum(); i++) { assertThat(content.getCell(i).getNumericCellValue(), is((double) (Integer) value[i])); } } }
From source file:com.asakusafw.testdriver.excel.ExcelSheetSinkTest.java
License:Apache License
private ExcelSheetDataModelSource open(URL resource) throws IOException { URI uri;/*w ww .ja v a 2 s . c o m*/ try { uri = resource.toURI(); } catch (URISyntaxException e) { throw new AssertionError(e); } try (InputStream in = resource.openStream()) { Workbook book = Util.openWorkbookFor(resource.getFile(), in); Sheet sheet = book.getSheetAt(0); return new ExcelSheetDataModelSource(SIMPLE, uri, sheet); } }
From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractorTest.java
License:Apache License
private Sheet sheet(String name) { try (InputStream in = getClass().getResourceAsStream(name)) { assertThat(name, in, not(nullValue())); Workbook book = new HSSFWorkbook(in); return book.getSheetAt(0); } catch (IOException e) { throw new AssertionError(e); }// w w w .ja v a 2s. c o m }
From source file:com.asakusafw.testdriver.excel.Util.java
License:Apache License
static Sheet extract(URI source) throws IOException { assert source != null; String path = source.getSchemeSpecificPart(); if (isHssf(path) == false && isXssf(path) == false) { LOG.debug("Not an Excel workbook: {}", source); //$NON-NLS-1$ return null; }/*from w w w .ja v a 2 s .c o m*/ String fragment = source.getFragment(); if (fragment == null) { // the first sheet fragment = FRAGMENT_FIRST_SHEET; LOG.debug("Fragment is not set, using first sheet: {}", source); //$NON-NLS-1$ } Matcher matcher = FRAGMENT.matcher(fragment); if (matcher.matches() == false) { LOG.info(MessageFormat.format(Messages.getString("Util.infoUnsupportedUriFragment"), //$NON-NLS-1$ source)); return null; } LOG.debug("Processing Excel workbook: {}", source); //$NON-NLS-1$ URL url = source.toURL(); Workbook book; try (InputStream in = new BufferedInputStream(url.openStream())) { book = openWorkbookFor(path, in); } catch (IOException e) { throw new IOException(MessageFormat.format(Messages.getString("Util.errorFailedToOpenWorkbook"), //$NON-NLS-1$ source)); } if (matcher.group(1) != null) { int sheetNumber = Integer.parseInt(matcher.group(1)); LOG.debug("Opening sheet by index : {}", sheetNumber); //$NON-NLS-1$ try { Sheet sheet = book.getSheetAt(sheetNumber); assert sheet != null; return sheet; } catch (RuntimeException e) { throw new IOException(MessageFormat.format(Messages.getString("Util.errorMissingSheetByIndex"), //$NON-NLS-1$ source, sheetNumber), e); } } else { String sheetName = matcher.group(2); LOG.debug("Opening sheet by name : {}", sheetName); //$NON-NLS-1$ assert sheetName != null; Sheet sheet = book.getSheet(sheetName); if (sheet == null) { throw new IOException(MessageFormat.format(Messages.getString("Util.errorMissingSheetByName"), //$NON-NLS-1$ source, sheetName)); } return sheet; } }
From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java
License:Open Source License
/** * Returns the first row headers (field names) mapped to the column indexes * @return Map<ColumnNumber, ColumnHeader> *///from w ww.j a v a2s .com static SortedMap<Integer, String> getFirstRowHeaders(Workbook hSSFWorkbook, Integer sheetID) { SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>(); if (hSSFWorkbook == null || sheetID == null) { return firstRowMap; } //first search for duplicate columns Set<String> sameColumnNames = new HashSet<String>(); Set<String> columnNames = new HashSet<String>(); Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue()); Row firstRow = sheet.getRow(0); if (firstRow != null) { for (Cell cell : firstRow) { String columnHeader = ExcelImportBL.getStringCellValue(cell); if (columnHeader != null && !"".equals(columnHeader)) { if (columnNames.contains(columnHeader)) { sameColumnNames.add(columnHeader); } else { columnNames.add(columnHeader); } } } } sheet = hSSFWorkbook.getSheetAt(sheetID.intValue()); firstRow = sheet.getRow(0); if (firstRow != null) { for (Cell cell : firstRow) { String columnHeader = ExcelImportBL.getStringCellValue(cell); if (columnHeader != null && !"".equals(columnHeader)) { if (sameColumnNames.contains(columnHeader)) { //for duplicate columns add also the column index columnHeader += " (" + cell.getColumnIndex() + ")"; } firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), columnHeader); } } } return firstRowMap; }
From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java
License:Open Source License
/** * Returns the first row of a sheet where there is the fields name * @return Map<ColumnNumber, FieldLabelName> *///from w w w. j a v a 2s . co m static SortedMap<Integer, String> getFirstRowNumericToLetter(Workbook hSSFWorkbook, Integer sheetID) { SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>(); if (hSSFWorkbook == null || sheetID == null) { return firstRowMap; } Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue()); Row firstRow = sheet.getRow(0); if (firstRow != null) { for (Cell cell : firstRow) { firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), colNumericToLetter(cell.getColumnIndex())); } } return firstRowMap; }
From source file:com.aurel.track.exchange.excel.ExcelImportBL.java
License:Open Source License
/** * Whether an uniqueIdentifierField is specified for any row If not the * corresponding row will be considered new and then we should verify * whether all required fields are present * /*from ww w. j a v a 2s . c o m*/ * @param workbook * @param selectedSheet * @param columnIndex * @return */ private static boolean anyFieldCellsSpecified(Workbook workbook, Integer selectedSheet, Integer columnIndex) { Sheet sheet = workbook.getSheetAt(selectedSheet.intValue()); for (Row row : sheet) { int rowNum = row.getRowNum(); if (rowNum == 0) { // only the data rows are processed (the header row is not // important now) continue; } // not very rigorous but here suffice Object attribute = getStringCellValue(row.getCell(columnIndex)); if (attribute != null && !"".equals(attribute)) { return true; } } return false; }
From source file:com.aurel.track.exchange.excel.ExcelImportBL.java
License:Open Source License
/** * Get the workItems list and validate if all the fields of the excel sheet * are correct/*from w w w . j a v a 2 s.c om*/ * * @param workbook * @param selectedSheet * @param personID * @param locale * @param columnIndexToFieldIDMap * @param fieldIDToColumnIndexMap * @param lastSavedIdentifierFieldIDIsSet * @param defaultValuesMap * @param invalidValueHandlingMap * @param gridErrorsMap * @param rowErrorsMap * @return */ static SortedMap<Integer, TWorkItemBean> getAndValidateGridData(Workbook workbook, Integer selectedSheet, Integer personID, Locale locale, Map<Integer, Integer> columnIndexToFieldIDMap, Map<Integer, Integer> fieldIDToColumnIndexMap, Set<Integer> lastSavedIdentifierFieldIDIsSet, Map<Integer, Integer> defaultValuesMap, Map<Integer, Integer> invalidValueHandlingMap, Map<Integer, Map<Integer, List<Integer>>> rowNoToPseudoFieldsOriginal, Map<Integer, Map<Integer, List<Integer>>> rowNoToPseudoFieldsExcel, Map<Integer, SortedMap<Integer, SortedMap<String, ErrorData>>> gridErrorsMap, Map<Integer, SortedSet<Integer>> rowErrorsMap, Map<Integer, SortedSet<Integer>> requiredFieldErrorsMap, Map<Integer, Integer> rowToParentRow) { SortedMap<Integer, TWorkItemBean> workItemBeansMap = new TreeMap<Integer, TWorkItemBean>(); Sheet sheet = workbook.getSheetAt(selectedSheet.intValue()); // get the column indexes for project and issueType Integer projectColumn = fieldIDToColumnIndexMap.get(SystemFields.INTEGER_PROJECT); Integer issueTypeColumn = fieldIDToColumnIndexMap.get(SystemFields.INTEGER_ISSUETYPE); // Maps to spare additional database accesses for default values Map<Integer, String> defaultShowValuesMap = new HashMap<Integer, String>(); Map<Integer, String> defaultLocalizedFieldLabels = new HashMap<Integer, String>(); Integer originalProject = null; Integer originalIssueType = null; Set<Integer> mandatoryIdentifierFields = ExcelFieldMatchBL.getMandatoryIdentifierFields(); Map<Integer, Map<String, ILabelBean>> systemLookups = loadBaseLookups(personID, locale); Map<String, ILabelBean> projectLookups = systemLookups.get(SystemFields.INTEGER_PROJECT); Map<Integer, Map<Integer, Map<String, ILabelBean>>> projectSpecificLookups = null; if (projectLookups != null) { projectSpecificLookups = loadProjectLookups(GeneralUtils .createIntegerListFromBeanList(GeneralUtils.createListFromCollection(projectLookups.values()))); } boolean projectSpecificIDsActive = ApplicationBean.getInstance().getSiteBean().getProjectSpecificIDsOn(); Map<Integer, TProjectBean> projectBeansMap = new HashMap<Integer, TProjectBean>(); if (projectSpecificIDsActive) { List<TProjectBean> projectBeans = ProjectBL.loadUsedProjectsFlat(personID); if (projectBeans != null) { for (TProjectBean projectBean : projectBeans) { Integer projectID = projectBean.getObjectID(); projectBeansMap.put(projectID, projectBean); String label = projectBean.getLabel(); String projectPrefix = projectBean.getPrefix(); if (projectPrefix == null || "".equals(projectPrefix)) { LOGGER.info("The project " + label + " with ID " + projectID + " has no prefix, consquently project specific item numbers might not be recognized"); } } } } /** * Process the rows only to gather the projects to issueTypes to get the * roles and restrictions once for all issues */ Map<Integer, Set<Integer>> projectToIssueTypesMap = new HashMap<Integer, Set<Integer>>(); for (Row row : sheet) { int rowNum = row.getRowNum(); if (rowNum == 0) { // only the data rows are processed (the header row is not // important now) continue; } SerializableBeanAllowedContext serializableBeanAllowedContext = new SerializableBeanAllowedContext(); serializableBeanAllowedContext.setPersonID(personID); serializableBeanAllowedContext.setNew(true); // get the project and issueType first because the other fields // could depend on these issueTypes // process the project column Integer projectID = null; if (projectColumn != null) { try { projectID = (Integer) getAttributeValue(row.getCell(projectColumn), SystemFields.INTEGER_PROJECT, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); } catch (Exception e) { } } if (projectID == null) { // no project column exists on the sheet: take the default value // which is // surely specified, otherwise it would fail at // validateRequiredColumns() projectID = defaultValuesMap.get(SystemFields.INTEGER_PROJECT); } if (projectID != null) { serializableBeanAllowedContext.setProjectID(projectID); } // process the issueType column Integer issueTypeID = null; if (issueTypeColumn != null) { try { issueTypeID = (Integer) getAttributeValue(row.getCell(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); } catch (Exception e) { } } if (issueTypeID == null) { // no issue type column exists on the sheet: take the default // value which is // surely specified, otherwise it would fail at // validateRequiredColumns() issueTypeID = defaultValuesMap.get(SystemFields.INTEGER_ISSUETYPE); } if (projectID != null) { Set<Integer> issueTypes = projectToIssueTypesMap.get(projectID); if (issueTypes == null) { issueTypes = new HashSet<Integer>(); projectToIssueTypesMap.put(projectID, issueTypes); } if (issueTypeID != null) { issueTypes.add(issueTypeID); } } } Map<Integer, Map<Integer, Map<Integer, TFieldConfigBean>>> projectsToIssueTypesToFieldConfigsMapForBottomUpFields = null; Map<Integer, Map<Integer, Map<String, Object>>> projectsIssueTypesFieldSettingsMapForBottomUpFields = null; Map<Integer, Map<Integer, Map<Integer, Integer>>> fieldRestrictions = AccessBeans .getFieldRestrictions(personID, projectToIssueTypesMap, null, true); Set<Integer> possibleBottomUpFields = FieldRuntimeBL.getPossibleBottomUpFields(); for (Iterator<Integer> iterator = possibleBottomUpFields.iterator(); iterator.hasNext();) { if (!fieldIDToColumnIndexMap.containsKey(iterator.next())) { // remove possible bottom up field if not mapped iterator.remove(); } if (!possibleBottomUpFields.isEmpty()) { // at least one bottom up date was mapped projectsToIssueTypesToFieldConfigsMapForBottomUpFields = FieldRuntimeBL .loadFieldConfigsInContextsAndTargetProjectAndIssueType(projectToIssueTypesMap, possibleBottomUpFields, locale, null, null); projectsIssueTypesFieldSettingsMapForBottomUpFields = FieldRuntimeBL .getFieldSettingsForFieldConfigs(projectsToIssueTypesToFieldConfigsMapForBottomUpFields); } } /** * now process the rows in detail one by one */ Stack<Integer> parentStack = new Stack<Integer>(); Map<Integer, Integer> rowToIndent = new HashMap<Integer, Integer>(); for (Row row : sheet) { int rowNum = row.getRowNum(); if (rowNum == 0) { // only the data rows are processed (the header row is not // important now) continue; } boolean excelValueFound = false; // whether the project column is mapped and excel value if found for // project boolean mappedProject = false; SerializableBeanAllowedContext serializableBeanAllowedContext = new SerializableBeanAllowedContext(); serializableBeanAllowedContext.setPersonID(personID); serializableBeanAllowedContext.setNew(true); // get the project and issueType first because the other fields // could depend on these issueTypes // process the project column Integer projectID = null; if (projectColumn != null) { try { projectID = (Integer) getAttributeValue(row.getCell(projectColumn), SystemFields.INTEGER_PROJECT, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); if (projectID != null) { mappedProject = true; excelValueFound = true; } } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT, e.getMessage()); } catch (ExcelImportInvalidCellValueException e) { addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT, e.getMessage()); } } if (projectID == null) { // no project column exists on the sheet: take the default value // which is // surely specified, otherwise it would fail at // validateRequiredColumns() projectID = defaultValuesMap.get(SystemFields.INTEGER_PROJECT); } if (projectID != null) { serializableBeanAllowedContext.setProjectID(projectID); } // process the issueType column Integer issueTypeID = null; if (issueTypeColumn != null) { try { issueTypeID = (Integer) getAttributeValue(row.getCell(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); if (issueTypeID != null) { excelValueFound = true; } } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, e.getMessage()); } catch (ExcelImportInvalidCellValueException e) { addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, e.getMessage()); } } if (issueTypeID == null) { // no issue type column exists on the sheet: take the default // value which is // surely specified, otherwise it would fail at // validateRequiredColumns() issueTypeID = defaultValuesMap.get(SystemFields.INTEGER_ISSUETYPE); } if (issueTypeID != null) { serializableBeanAllowedContext.setIssueTypeID(issueTypeID); } /* * gather the values for the identifier fields and try to get an * existing workItem by these fields */ Map<Integer, Object> identifierFieldValues = new HashMap<Integer, Object>(); if (lastSavedIdentifierFieldIDIsSet != null && !lastSavedIdentifierFieldIDIsSet.isEmpty()) { for (Integer fieldID : lastSavedIdentifierFieldIDIsSet) { Integer attributeFieldID = fieldID; if (SystemFields.INTEGER_ISSUENO.equals(fieldID) && projectSpecificIDsActive) { attributeFieldID = SystemFields.INTEGER_PROJECT_SPECIFIC_ISSUENO; } Object attributeValue = null; Integer columnIndex = null; try { columnIndex = fieldIDToColumnIndexMap.get(fieldID); attributeValue = getAttributeValue(row.getCell(columnIndex), attributeFieldID, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); if (attributeValue != null) { identifierFieldValues.put(fieldID, attributeValue); excelValueFound = true; } } catch (ExcelImportNotExistingCellValueException e) { if (!SystemFields.INTEGER_PROJECT.equals(fieldID) && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) { // if project or issueType are set as identifier // fields and // have grid error they should be already collected // in gridErrorsMap addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } catch (ExcelImportNotAllowedCellValueException e) { if (!SystemFields.INTEGER_PROJECT.equals(fieldID) && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) { // if project or issueType are set as identifier // fields and // have grid error they should be already collected // in gridErrorsMap addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } catch (ExcelImportInvalidCellValueException e) { if (!SystemFields.INTEGER_PROJECT.equals(fieldID) && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) { // if project or issueType are set as identifier // fields and // have grid error they should be already collected // in gridErrorsMap addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } } } // always initialize the next workItem to null TWorkItemBean workItemBean = null; boolean itemIsNew = false; if (!identifierFieldValues.isEmpty()) { if (identifierFieldValues.get(SystemFields.INTEGER_ISSUENO) != null) { // is issueNo field mapped? if (projectSpecificIDsActive) { // get by project specific itemID String projectSpecificID = null; try { projectSpecificID = (String) identifierFieldValues.get(SystemFields.INTEGER_ISSUENO); } catch (Exception e) { } if (projectSpecificID != null) { // it should be trimmed because in excel the child // issues are indented workItemBean = ItemBL.loadWorkItemByProjectSpecificID(projectID, mappedProject, projectBeansMap, projectSpecificID.trim()); if (workItemBean != null && LOGGER.isDebugEnabled()) { LOGGER.debug("WorkItem " + projectSpecificID + " from row " + rowNum + " found by projectSpecificID"); } } } else { // get by "global" workItemID Integer workItemID = null; try { workItemID = (Integer) identifierFieldValues.get(SystemFields.INTEGER_ISSUENO); } catch (Exception e) { } if (workItemID != null) { workItemBean = ItemBL.loadWorkItemSystemAttributes(workItemID); } if (workItemBean != null && LOGGER.isDebugEnabled()) { LOGGER.debug("WorkItem " + workItemID + " from row " + rowNum + " found by workItemID"); } } if (workItemBean == null) { // the issueNo field is set as identifier and the // corresponding issue does't exist, report as error addGridError(gridErrorsMap, WORKITEM_NOTEXIST_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter( fieldIDToColumnIndexMap.get(SystemFields.INTEGER_ISSUENO)), SystemFields.INTEGER_ISSUENO, identifierFieldValues.get(SystemFields.INTEGER_ISSUENO).toString()); continue; } } if (workItemBean == null) { // workItem was not found by issueNo // (issueNo field was not mapped or issueNo value is missing // from excel or // the issue's project is not accessible if // projectSpecificIDsActive) // try with user defined identifier fields try { workItemBean = ItemBL.loadWorkItemSystemAttributes(identifierFieldValues); } catch (ExcelImportNotUniqueIdentifiersException e) { addRowError(rowErrorsMap, WORKITEM_MORE_THAN_ONE_EXIST, rowNum); continue; } if (workItemBean != null && LOGGER.isDebugEnabled()) { LOGGER.debug("WorkItem from row " + rowNum + " found by user defined identifier fields"); } } if (workItemBean != null) { // existing workItem originalProject = workItemBean.getProjectID(); originalIssueType = workItemBean.getListTypeID(); // is it editable by the current person? if (!AccessBeans.isAllowedToChange(workItemBean, personID)) { addRowError(rowErrorsMap, WORKITEM_NO_EDIT_RIGHT, rowNum); continue; } // load also the custom attributes because when the workItem // will be updated // the custom attributes will also be compared to the // original value ItemBL.loadWorkItemCustomAttributes(workItemBean); serializableBeanAllowedContext.setWorkItemBeanOriginal(workItemBean); serializableBeanAllowedContext.setNew(false); // LOGGER.debug("WorkItem " + workItemBean.getObjectID() + // " from row " + rowNum + " found"); } } boolean missingRequiredFound = false; if (workItemBean == null) { // not existing found by identifier fields, create a new one workItemBean = new TWorkItemBean(); if (identifierFieldValues != null) { // preset the new workItem with the processed identifier // values for (Map.Entry<Integer, Object> identifierEntry : identifierFieldValues.entrySet()) { workItemBean.setAttribute(identifierEntry.getKey(), identifierEntry.getValue()); } } itemIsNew = true; LOGGER.debug("WorkItem from row " + rowNum + " not found. A new one will be created."); } if (projectID != null) { workItemBean.setAttribute(SystemFields.INTEGER_PROJECT, null, projectID); } else { if (itemIsNew) { // project column not mapped addRowError(requiredFieldErrorsMap, SystemFields.INTEGER_PROJECT, rowNum); missingRequiredFound = true; } } if (issueTypeID != null) { workItemBean.setAttribute(SystemFields.INTEGER_ISSUETYPE, null, issueTypeID); } else { if (itemIsNew) { // project column not mapped addRowError(requiredFieldErrorsMap, SystemFields.INTEGER_ISSUETYPE, rowNum); missingRequiredFound = true; } } if (missingRequiredFound) { continue; } Map<Integer, Integer> restrictedFields = null; projectID = workItemBean.getProjectID(); issueTypeID = workItemBean.getListTypeID(); if (projectID != null && issueTypeID != null) { Map<Integer, Map<Integer, Integer>> issueTypeRestrictions = fieldRestrictions.get(projectID); if (issueTypeRestrictions != null) { restrictedFields = issueTypeRestrictions.get(issueTypeID); } if (restrictedFields == null) { // no project or issue type mapped get the restriction now restrictedFields = AccessBeans.getFieldRestrictions(personID, projectID, issueTypeID, true); issueTypeRestrictions = new HashMap<Integer, Map<Integer, Integer>>(); issueTypeRestrictions.put(issueTypeID, restrictedFields); fieldRestrictions.put(projectID, issueTypeRestrictions); } // new values exist if (originalProject != null && originalIssueType != null) { // workItem existed if (!projectID.equals(originalProject) || !issueTypeID.equals(originalIssueType)) { if (!AccessBeans.isAllowedToChange(workItemBean, personID)) { // move not allowed addRowError(rowErrorsMap, WORKITEM_NO_EDIT_RIGHT, rowNum); continue; } } } else { // new workItem if (!AccessBeans.isAllowedToCreate(personID, projectID, issueTypeID)) { // create not allowed addRowError(rowErrorsMap, WORKITEM_NO_CREATE_RIGHT, rowNum); continue; } } } // process the remaining cells Map<Integer, Integer> rowNoToIndentLevel = new HashMap<Integer, Integer>(); for (Cell cell : row) { boolean attributeChanged = false; int columnIndex = cell.getColumnIndex(); Integer fieldID = columnIndexToFieldIDMap.get(columnIndex); Integer fieldForRestriction = fieldID; if (fieldID == null) { // LOGGER.debug("No mapping found for column " + // columnIndex); continue; } if (fieldID.equals(SystemFields.INTEGER_PROJECT) || fieldID.equals(SystemFields.INTEGER_ISSUETYPE) || identifierFieldValues.containsKey(fieldID) || mandatoryIdentifierFields.contains(fieldID)) { // these values are already read continue; } if (fieldID.intValue() < 0) { // pseudo field: now only watchers if (fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST || fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.CONSULTANT_LIST) { fieldForRestriction = FieldsRestrictionsToRoleBL.PSEUDO_COLUMNS.WATCHERS; String watcherValue = getStringCellValue(cell); if (watcherValue == null || "".equals(watcherValue.trim())) { continue; } Map<Integer, List<Integer>> watcherMapOriginal = rowNoToPseudoFieldsOriginal.get(rowNum); if (watcherMapOriginal == null) { watcherMapOriginal = new HashMap<Integer, List<Integer>>(); rowNoToPseudoFieldsOriginal.put(rowNum, watcherMapOriginal); } List<Integer> watcherListOriginal = null; TWorkItemBean workItemBeanOriginal = serializableBeanAllowedContext .getWorkItemBeanOriginal(); if (workItemBeanOriginal != null) { if (fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST) { watcherListOriginal = GeneralUtils.createIntegerListFromBeanList( PersonBL.getDirectInformants(workItemBeanOriginal.getObjectID())); } else { watcherListOriginal = GeneralUtils.createIntegerListFromBeanList( PersonBL.getDirectConsultants(workItemBeanOriginal.getObjectID())); } watcherMapOriginal.put(fieldID, watcherListOriginal); } List<Integer> watcherListExcel = new LinkedList<Integer>(); String[] watcherNames = watcherValue .split("\\" + ConsultedInformedLoaderBL.WATCHER_SPLITTER_VALUES_STRING); if (watcherNames != null) { Map<Integer, List<Integer>> watcherMapExcel = rowNoToPseudoFieldsExcel.get(rowNum); if (watcherMapExcel == null) { watcherMapExcel = new HashMap<Integer, List<Integer>>(); rowNoToPseudoFieldsExcel.put(rowNum, watcherMapExcel); } watcherMapExcel.put(fieldID, watcherListExcel); for (int i = 0; i < watcherNames.length; i++) { String watcherName = watcherNames[i]; Integer objectID = null; try { objectID = getWatcherValue(watcherName, fieldID, systemLookups, watcherListOriginal, serializableBeanAllowedContext, locale); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (objectID != null) { watcherListExcel.add(objectID); excelValueFound = true; } } } attributeChanged = ConsInfBL.watcherChanged(watcherListOriginal, watcherListExcel); } else { if (fieldID.intValue() == ExcelFieldMatchBL.LOCAL_PARENT_PSEUDO_COLUMN) { // local parent - child hierarchy (for new items) Integer pseudoHierarchyColumn = fieldIDToColumnIndexMap .get(ExcelFieldMatchBL.LOCAL_PARENT_PSEUDO_COLUMN); if (pseudoHierarchyColumn != null) { String hierarchyColumn = getStringCellValue(row.getCell(pseudoHierarchyColumn)); if (hierarchyColumn != null && hierarchyColumn.length() > 0) { int previousIndent = 0; if (!parentStack.isEmpty()) { Integer previousRow = parentStack.peek(); if (rowToIndent.get(previousRow) != null) { previousIndent = rowToIndent.get(previousRow).intValue(); } } int actualIndent = hierarchyColumn.length(); rowToIndent.put(rowNum, actualIndent); rowNoToIndentLevel.put(rowNum, actualIndent); if (previousIndent == actualIndent) { // sibling: same parent as the sibling's // parent if (!parentStack.isEmpty()) { // remove the sibling from stack parentStack.pop(); if (!parentStack.isEmpty()) { // if the stack is still not // empty then the peek is teh // parent Integer parentRow = parentStack.peek(); rowToParentRow.put(rowNum, parentRow); } } } else { if (actualIndent > previousIndent) { // child of the previous row if (actualIndent - previousIndent > 1) { // jump more than one in deep is // error addGridError(gridErrorsMap, INCONSISTENT_HIERARCHY_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, hierarchyColumn); } if (!parentStack.isEmpty()) { // add previous row as parent Integer parentRow = parentStack.peek(); rowToParentRow.put(rowNum, parentRow); } } else { // new hierarchy: nothing to do with // the previous row int difference = previousIndent - actualIndent; for (int i = 0; i <= difference; i++) { // pop to find the parent if (!parentStack.isEmpty()) { parentStack.pop(); } } if (!parentStack.isEmpty()) { Integer parentRow = parentStack.peek(); rowToParentRow.put(rowNum, parentRow); } } } } else { // no hierarchy string: top level item while (!parentStack.isEmpty()) { // empty the stack parentStack.pop(); } } // add row to stack for possible children parentStack.push(rowNum); } } } } else { IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID); Object attributeValue = null; if (fieldTypeRT.isComposite() || fieldTypeRT.isMultipleValues()) { String compositeOrMultipleValue = getStringCellValue(cell); if (compositeOrMultipleValue == null || "".equals(compositeOrMultipleValue.trim())) { workItemBean.setAttribute(fieldID, null, null); continue; } // we suppose that all composite and multiple values are // lookup values // TODO refactor if that is not true String[] parts; if (fieldTypeRT.isMultipleValues()) { parts = compositeOrMultipleValue .split(CustomSelectBaseRT.OPTION_SPLITTER_VALUES_STRING); List<Integer> multipleValues = new ArrayList<Integer>(); for (int i = 0; i < parts.length; i++) { String part = parts[i]; Integer objectID = null; try { objectID = getLookupValue(part, fieldTypeRT, fieldID, systemLookups, projectSpecificLookups, serializableBeanAllowedContext, null, invalidValueHandlingMap, locale); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (objectID != null) { multipleValues.add(objectID); } } if (!multipleValues.isEmpty()) { attributeValue = multipleValues.toArray(); excelValueFound = true; } } else { int numberOfParts = ((CustomCompositeBaseRT) fieldTypeRT).getNumberOfParts(); parts = compositeOrMultipleValue .split("\\" + CustomCompositeBaseRT.PART_SPLITTER_VALUES_STRING); if (parts != null && parts.length > numberOfParts) { addGridError(gridErrorsMap, WRONG_COMPOSITE_SIZE, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, compositeOrMultipleValue); } Map<Integer, Integer> componentPartsMap = new HashMap<Integer, Integer>(); attributeValue = new HashMap<Integer, Object>(); if (parts != null) { for (int i = 0; i < parts.length; i++) { String part = parts[i]; Integer objectID = null; IFieldTypeRT componentFieldType = ((CustomCompositeBaseRT) fieldTypeRT) .getCustomFieldType(i + 1); if (componentFieldType != null) { try { objectID = getLookupValue(part, componentFieldType, fieldID, systemLookups, projectSpecificLookups, serializableBeanAllowedContext, componentPartsMap, invalidValueHandlingMap, locale); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (objectID == null) { // workItemBean.setAttribute(fieldID, // Integer.valueOf(i+1), null); ((Map<Integer, Object>) attributeValue).put(Integer.valueOf(i + 1), null); } else { componentPartsMap.put(Integer.valueOf(i + 1), objectID); // workItemBean.setAttribute(fieldID, // Integer.valueOf(i+1), new // Object[] {objectID}); ((Map<Integer, Object>) attributeValue).put(Integer.valueOf(i + 1), new Object[] { objectID }); excelValueFound = true; } } } } } } else { // simple field // Object attributeValue = null; try { attributeValue = getAttributeValue(cell, fieldID, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportInvalidCellValueException e) { addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (attributeValue != null) { excelValueFound = true; if (possibleBottomUpFields.contains(fieldID)) { TFieldConfigBean fieldConfigBean = FieldRuntimeBL .getFieldConfigForProjectIssueTypeField( projectsToIssueTypesToFieldConfigsMapForBottomUpFields, projectID, issueTypeID, fieldID); Object fieldSettings = FieldRuntimeBL.getFieldSettingsForProjectIssueTypeField( projectsIssueTypesFieldSettingsMapForBottomUpFields, projectID, issueTypeID, fieldID); if (fieldTypeRT.getHierarchicalBehavior(fieldID, fieldConfigBean, fieldSettings) == HIERARCHICAL_BEHAVIOR_OPTIONS.COMPUTE_BOTTOM_UP && ItemBL.hasChildren(workItemBean.getObjectID())) { Date trackPlusAttributeValue = (Date) workItemBean.getAttribute(fieldID); if (EqualUtils.notEqual(trackPlusAttributeValue, (Date) attributeValue)) { // add read only restrictions for start // and end date for non leaf workItems LOGGER.debug("Parent change restriction for bottom up date " + fieldID); addGridError(gridErrorsMap, NOT_EDITABLE_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, getStringCellValue(cell)); } } /* * if * (ApplicationBean.getInstance().getSiteBean * ().getSummaryItemsBehavior() && * ItemBL2.hasChildren * (workItemBean.getObjectID())) { Date * trackPlusAttributeValue = * (Date)workItemBean.getAttribute(fieldID); if * (EqualUtils.notEqual(trackPlusAttributeValue, * (Date)attributeValue)) { //add read only * restrictions for start and end date for non * leaf workItems LOGGER.debug( * "Summary parent change restriction for date " * + fieldID); addGridError(gridErrorsMap, * NOT_EDITABLE_ERRORS, rowNum, * ExcelFieldMatchBL * .colNumericToLetter(columnIndex), fieldID, * getStringCellValue(cell)); } } */ } } } attributeChanged = fieldTypeRT.valueModified(attributeValue, workItemBean.getAttribute(fieldID)); workItemBean.setAttribute(fieldID, null, attributeValue); } if (attributeChanged) { try { verifyFieldRestrictions(fieldForRestriction, restrictedFields, cell, locale); } catch (ExcelImportNotModifiableCellValueException e) { addGridError(gridErrorsMap, NOT_EDITABLE_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } } if (!excelValueFound) { // not a single excel value found in any cell from the row // simply neglect this row. // expanded row count can be greater than the number of real // workItem rows // for example when the content of some rows is deleted but the // rows are not deleted // and empty rows may remain in the excel LOGGER.info("The row number " + (rowNum + 1) + " contains only empty cells and will be neglected"); continue; } // add the default values for those fields which didn't have column // in // excel sheet or have column but the value is empty or not valid Iterator<Integer> itrDefaultValueFields = defaultValuesMap.keySet().iterator(); while (itrDefaultValueFields.hasNext()) { Integer fieldID = itrDefaultValueFields.next(); if (/*!fieldIDToColumnIndexMap.containsKey(fieldID) ||*/workItemBean.getAttribute(fieldID, null) == null) { if (invalidValueHandlingMap.containsKey(fieldID)) { if (DEFAULT_IF_NOT_EXIST_OR_EMPTY.equals(invalidValueHandlingMap.get(fieldID))) { IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID, null); ILookup lookup = (ILookup) fieldTypeRT; Integer defaultObjectID = defaultValuesMap.get(fieldID); if (defaultObjectID != null) { boolean allowed = lookup.lookupBeanAllowed(defaultObjectID, serializableBeanAllowedContext); if (allowed) { workItemBean.setAttribute(fieldID, null, defaultObjectID); } else { // for example when no default project // and/or issue type is specified the // default manager and responsible // lists contain the users which are manager // or responsible in any of the projects // (but maybe not in all) LOGGER.debug("The default value is not allowed for field " + fieldID + " on row " + rowNum); // cache the show values and localized // labels to spare additional database // accesses String showValue; if (defaultShowValuesMap.containsKey(fieldID)) { showValue = defaultShowValuesMap.get(fieldID); } else { showValue = fieldTypeRT.getShowValue(defaultObjectID, locale); defaultShowValuesMap.put(fieldID, showValue); } String localizedLabel; if (defaultLocalizedFieldLabels.containsKey(fieldID)) { localizedLabel = defaultLocalizedFieldLabels.get(fieldID); } else { localizedLabel = FieldRuntimeBL.getLocalizedDefaultFieldLabel(fieldID, locale); defaultLocalizedFieldLabels.put(fieldID, localizedLabel); } addGridError(gridErrorsMap, NOT_ALLOWED_DEFAULT_VALUES_ERRORS, rowNum, localizedLabel, fieldID, showValue); } } } } } } workItemBeansMap.put(rowNum, workItemBean); } return workItemBeansMap; }