List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create
public static Workbook create(File file) throws IOException, EncryptedDocumentException
From source file:org.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder.java
License:Open Source License
/** {@inheritDoc} */ @Override/*from w w w . ja v a 2 s. co m*/ public Workbook load(File file) throws InvalidFormatException, IOException { InputStream inp = new FileInputStream(file); return WorkbookFactory.create(inp); }
From source file:org.opencrx.kernel.portal.wizard.ImportAccountsFromXlsController.java
License:BSD License
/** * Perform the import and generate report. * // w ww . ja va 2 s. c om * @return the this.importReport */ public void performImport(Writer importReport, ProgressMeter progressMeter) throws ServiceException, IOException { PersistenceManager pm = this.getPm(); ApplicationContext app = this.getApp(); Codes codes = this.getCodes(); if (this.importFileLocation != null) { try { boolean isImportMembershipMode = this.getObject() instanceof Group; Group parentGroup = null; Member groupMember = null; int parentGroupMemberSize = 0; if (isImportMembershipMode) { parentGroup = (Group) this.getObject(); parentGroupMemberSize = parentGroup.getMember().size(); } // Get account segment org.opencrx.kernel.account1.jmi1.Segment accountSegment = (org.opencrx.kernel.account1.jmi1.Segment) pm .getObjectById(new Path("xri://@openmdx*org.opencrx.kernel.account1").getDescendant( "provider", this.getProviderName(), "segment", this.getSegmentName())); // Read workbook Workbook wb = null; try { wb = WorkbookFactory.create(new FileInputStream(this.importFileLocation)); } catch (Exception e) { this.errorMessage = e.getMessage(); } if (wb != null) { for (int i = 0; i < 1; i++) { Sheet sheet = wb.getSheetAt(i); int nLinesRead = 0; int nContactsUpdated = 0; int nContactsCreated = 0; int nGroupsUpdated = 0; int nGroupsCreated = 0; int nLegalEntitiesUpdated = 0; int nLegalEntitiesCreated = 0; int nUnspecifiedAccountsUpdated = 0; int nUnspecifiedAccountsCreated = 0; progressMeter.setTotal(sheet.getLastRowNum()); Iterator<Row> rows = sheet.rowIterator(); int nRow = 0; int maxCell = 0; Row row = null; AccountRecordDefinition recordDefinition = new AccountRecordDefinition(); // Read first row with attribute names if (rows.hasNext()) { nRow += 1; row = rows.next(); maxCell = this.readImportDefinition(row, nRow, recordDefinition, importReport); } // Read data while (rows.hasNext()) { nRow += 1; progressMeter.setCurrent(nRow); nLinesRead += 1; Account account = null; String multiMatchList = ""; boolean isCreation = false; boolean isUpdate = false; String appendErrorRow = null; AccountRecord accountRecord = new AccountRecord(recordDefinition); accountRecord.setTypeExplicitlySet(false); accountRecord.setXriExplicitlySet(false); accountRecord.setAccountType(AccountType.Contact); row = (Row) rows.next(); List<String> errors = new ArrayList<String>(); this.readAccountRecord(row, nRow, maxCell, accountRecord, importReport, errors); String accountHref = ""; // Import Members if (isImportMembershipMode) { groupMember = null; List<Account> matchingAccounts = null; // try to locate contacts with firstName and lastName only List<Contact> matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), null, accountRecord.getEmailAddress(), null, accountSegment); if (matchingContacts != null) { matchingAccounts = new ArrayList<Account>(); for (Iterator<Contact> c = matchingContacts.iterator(); c.hasNext();) { try { matchingAccounts.add((Account) c.next()); } catch (Exception e) { } } } // try to locate legal entities with name only List<AbstractGroup> matchingAbstractGroups = this.findAbstractGroup( accountRecord.getCompany(), null, null, null, false, /* Group allowed: no */ true, /* LegalEntity allowed: yes */ false, /* UnspecifiedAccount allowed: no */ accountSegment); if (matchingAbstractGroups != null) { if (matchingAccounts == null) { matchingAccounts = new ArrayList<Account>(); } for (Iterator<AbstractGroup> l = matchingAbstractGroups.iterator(); l .hasNext();) { try { matchingAccounts.add(l.next()); } catch (Exception e) { } } } if (matchingAccounts != null) { for (Iterator<Account> a = matchingAccounts.iterator(); a.hasNext();) { try { pm.currentTransaction().begin(); Account acct = (Account) a.next(); // create or update membership groupMember = this.createOrUpdateMember(parentGroup, acct, null, /* no particular membership role */ null, // userStrings null, // userCodes null, // userNumbers FEATURE_MEMBERROLE, codes, accountSegment); pm.currentTransaction().commit(); } catch (Exception e) { ServiceException e0 = new ServiceException(e); e0.log(); errors.add(e0.getMessage()); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } } } // Import Accounts else { boolean updateExisting = false; List<Contact> matchingContacts = null; List<AbstractGroup> matchingAbstractGroups = null; if (accountRecord.isXriExplicitlySet()) { // try to find existing account with provided xri try { account = (Account) pm.getObjectById(new Path(accountRecord.getXri())); } catch (Exception e) { new ServiceException(e).log(); } if (account != null) { accountRecord.setTypeExplicitlySet(true); accountRecord.setAccountType(AccountType.NA); if (account instanceof Contact) { accountRecord.setAccountType(AccountType.Contact); matchingContacts = new ArrayList<Contact>(); matchingContacts.add((Contact) account); } else if (account instanceof Group) { accountRecord.setAccountType(AccountType.Group); matchingAbstractGroups = new ArrayList<AbstractGroup>(); matchingAbstractGroups.add((AbstractGroup) account); } else if (account instanceof LegalEntity) { accountRecord.setAccountType(AccountType.LegalEntity); matchingAbstractGroups = new ArrayList<AbstractGroup>(); matchingAbstractGroups.add((AbstractGroup) account); } else if (account instanceof UnspecifiedAccount) { accountRecord.setAccountType(AccountType.UnspecifiedAccount); matchingAbstractGroups = new ArrayList<AbstractGroup>(); matchingAbstractGroups.add((AbstractGroup) account); } } } if (!accountRecord.isTypeExplicitlySet()) { // try to find existing account to determine dtype matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), accountRecord.getAliasName(), accountRecord.getEmailAddress(), accountRecord.getExtString0(), accountSegment); if (matchingContacts == null && accountRecord.getExtString0() == null) { // try again without aliasName matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), null, accountRecord.getEmailAddress(), accountRecord.getExtString0(), accountSegment); } if (matchingContacts == null && accountRecord.getExtString0() == null) { // try again without aliasName and without emailAddress matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), null, null, accountRecord.getExtString0(), accountSegment); } if (matchingContacts != null) { accountRecord.setTypeExplicitlySet(true); } else { matchingAbstractGroups = this.findAbstractGroup(accountRecord.getCompany(), accountRecord.getAliasName(), accountRecord.getEmailAddress(), accountRecord.getExtString0(), true, true, true, accountSegment); if (matchingAbstractGroups == null && accountRecord.getExtString0() == null) { // try again without emailaddress matchingAbstractGroups = this.findAbstractGroup( accountRecord.getCompany(), accountRecord.getAliasName(), null, accountRecord.getExtString0(), true, true, true, accountSegment); } if (matchingAbstractGroups != null) { AbstractGroup matchingAbstractGroup = (AbstractGroup) (matchingAbstractGroups .iterator().next()); if (matchingAbstractGroup instanceof Group) { accountRecord.setTypeExplicitlySet(true); accountRecord.setAccountType(AccountType.Group); } else if (matchingAbstractGroup instanceof LegalEntity) { accountRecord.setTypeExplicitlySet(true); accountRecord.setAccountType(AccountType.LegalEntity); } else if (matchingAbstractGroup instanceof UnspecifiedAccount) { accountRecord.setTypeExplicitlySet(true); accountRecord.setAccountType(AccountType.UnspecifiedAccount); } } } } if (accountRecord.getAccountType() == AccountType.Contact) { if (matchingContacts == null) { matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), accountRecord.getAliasName(), accountRecord.getEmailAddress(), accountRecord.getExtString0(), accountSegment); } if (matchingContacts == null && accountRecord.getExtString0() == null) { // try again without aliasName matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), null, accountRecord.getEmailAddress(), accountRecord.getExtString0(), accountSegment); } if (matchingContacts == null && accountRecord.getExtString0() == null) { // try again without aliasName and without emailaddress matchingContacts = this.findContact(accountRecord.getFirstName(), accountRecord.getLastName(), null, null, accountRecord.getExtString0(), accountSegment); } if (matchingContacts != null) { // at least 1 match with existing contacts updateExisting = true; for (Iterator<Contact> c = matchingContacts.iterator(); c.hasNext();) { Contact matchingContact = (Contact) c.next(); if (c.hasNext()) { // more than 1 match updateExisting = false; ; accountHref = this.getSelectObjectHref(matchingContact); multiMatchList += "<br><a href='" + accountHref + " target='_blank'><b>" + (new ObjectReference(matchingContact, app)).getTitle() + "</b> [" + matchingContact.refMofId() + "]</a>"; } else if (updateExisting) { nContactsUpdated += 1; isUpdate = true; account = matchingContact; } } } else { // no match with existing contacts if ( // minimum requirements to create contact ((accountRecord.getFirstName() != null) || (accountRecord.getLastName() != null))) { try { pm.currentTransaction().begin(); Contact contact = pm.newInstance(Contact.class); contact.setFirstName(accountRecord.getFirstName()); contact.setLastName(accountRecord.getLastName()); contact.setExtString0(accountRecord.getExtString0()); accountSegment.addAccount(Base.getInstance().getUidAsString(), contact); pm.currentTransaction().commit(); account = contact; } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } if (account != null) { nContactsCreated += 1; isCreation = true; } else { // creation failed appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CREATION FAILED [<b>" + accountRecord.getAccountType() + "</b>]</td></tr>"; } } if (account != null) { // update new or existing contact accountHref = this.getSelectObjectHref(account); try { Contact contact = (Contact) account; pm.currentTransaction().begin(); this.updateAccount(accountRecord, contact, nRow, codes, app, errors); pm.currentTransaction().commit(); } catch (Exception e) { ServiceException e0 = new ServiceException(e); e0.log(); errors.add(e0.getMessage()); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } } else if (accountRecord.getAccountType() == AccountType.Group || accountRecord.getAccountType() == AccountType.LegalEntity || accountRecord.getAccountType() == AccountType.UnspecifiedAccount) { if (matchingAbstractGroups == null) { matchingAbstractGroups = this.findAbstractGroup(accountRecord.getCompany(), accountRecord.getAliasName(), accountRecord.getEmailAddress(), accountRecord.getExtString0(), accountRecord.getAccountType() == AccountType.Group, accountRecord.getAccountType() == AccountType.LegalEntity, accountRecord.getAccountType() == AccountType.UnspecifiedAccount, accountSegment); if (matchingAbstractGroups == null && accountRecord.getExtString0() == null) { // try again without emailaddress matchingAbstractGroups = this.findAbstractGroup( accountRecord.getCompany(), accountRecord.getAliasName(), null, accountRecord.getExtString0(), accountRecord.getAccountType() == AccountType.Group, accountRecord.getAccountType() == AccountType.LegalEntity, accountRecord .getAccountType() == AccountType.UnspecifiedAccount, accountSegment); } } if (matchingAbstractGroups != null) { // at least 1 match with existing AbstractGroups updateExisting = true; for (Iterator<AbstractGroup> c = matchingAbstractGroups.iterator(); c .hasNext();) { AbstractGroup matchingAbstractGroup = (AbstractGroup) c.next(); if (c.hasNext()) { // more than 1 match updateExisting = false; ; accountHref = this.getSelectObjectHref(matchingAbstractGroup); multiMatchList += "<br><a href='" + accountHref + " target='_blank'><b>" + (new ObjectReference(matchingAbstractGroup, app)) .getTitle() + "</b> [" + matchingAbstractGroup.refMofId() + "]</a>"; } else if (updateExisting) { isUpdate = true; if (accountRecord.getAccountType() == AccountType.Group) { nGroupsUpdated += 1; account = (Group) matchingAbstractGroup; } else if (accountRecord .getAccountType() == AccountType.LegalEntity) { nLegalEntitiesUpdated += 1; account = (LegalEntity) matchingAbstractGroup; } else if (accountRecord .getAccountType() == AccountType.UnspecifiedAccount) { nUnspecifiedAccountsUpdated += 1; account = (UnspecifiedAccount) matchingAbstractGroup; } } } } else { // no match with existing AbstractGroups if ( // minimum requirements to create AbstractGroup (accountRecord.getCompany() != null)) { try { pm.currentTransaction().begin(); if (accountRecord.getAccountType() == AccountType.Group) { Group group = pm.newInstance(Group.class); group.setName(accountRecord.getCompany()); group.setExtString0(accountRecord.getExtString0()); accountSegment.addAccount(Base.getInstance().getUidAsString(), group); account = group; } else if (accountRecord .getAccountType() == AccountType.LegalEntity) { LegalEntity legalEntity = pm.newInstance(LegalEntity.class); legalEntity.setName(accountRecord.getCompany()); legalEntity.setExtString0(accountRecord.getExtString0()); accountSegment.addAccount(Base.getInstance().getUidAsString(), legalEntity); account = legalEntity; } else if (accountRecord .getAccountType() == AccountType.UnspecifiedAccount) { UnspecifiedAccount unspecifiedAccount = pm .newInstance(UnspecifiedAccount.class); unspecifiedAccount.setName(accountRecord.getCompany()); unspecifiedAccount.setExtString0(accountRecord.getExtString0()); accountSegment.addAccount(Base.getInstance().getUidAsString(), unspecifiedAccount); account = unspecifiedAccount; } pm.currentTransaction().commit(); } catch (Exception e) { new ServiceException(e).log(); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } if (account instanceof Group) { nGroupsCreated += 1; isCreation = true; } else if (account instanceof LegalEntity) { nLegalEntitiesCreated += 1; isCreation = true; } else if (account instanceof UnspecifiedAccount) { nUnspecifiedAccountsCreated += 1; isCreation = true; } else { // creation failed appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CREATION FAILED [<b>" + accountRecord.getAccountType() + "</b>]</td></tr>"; } } if (account != null) { accountHref = this.getSelectObjectHref(account); try { pm.currentTransaction().begin(); this.updateAccount(accountRecord, account, nRow, codes, app, errors); pm.currentTransaction().commit(); } catch (Exception e) { ServiceException e0 = new ServiceException(e).log(); errors.add(e0.getMessage()); try { pm.currentTransaction().rollback(); } catch (Exception e1) { } } } } } accountRecord.printImportStatus(importReport); importReport.append("</tr>"); if (appendErrorRow != null) { importReport.append(appendErrorRow); } if (isImportMembershipMode) { importReport.append("<tr class='gridTableRowFull'>"); importReport.append("<td class=\"" + (!errors.isEmpty() ? "err" : "match") + "\" colspan=\"" + (maxCell + 2) + "\">"); importReport.append("MEMBER " + (!errors.isEmpty() ? "FAILED [Reason: " + errors.toString() + "]" : "OK") + ":"); if (groupMember != null) { String memberHref = this.getSelectObjectHref(groupMember); importReport.append("<a href=\"" + memberHref + "\" target=\"_blank\"><b>" + (new ObjectReference(groupMember, app)).getTitle() + "</b> [" + groupMember.refMofId() + "]</a>"); } else { importReport.append(this.ATTR_FIRSTNAME + "=" + accountRecord.getFirstName() + "/" + this.ATTR_LASTNAME + "=" + accountRecord.getLastName() + "/" + this.ATTR_ALIASNAME + "=" + accountRecord.getAliasName() + "/" + this.ATTR_EMAILADDRESS + "=" + accountRecord.getEmailAddress() + "/" + this.ATTR_COMPANY + "=" + accountRecord.getCompany()); } importReport.append("</td>"); importReport.append("</tr>"); } else { if (isCreation) { importReport.append("<tr class='gridTableRowFull'>"); importReport.append("<td class=\"" + (!errors.isEmpty() ? "err" : "match") + "\" colspan=\"" + (maxCell + 2) + "\">"); importReport.append("CREATE " + (!errors.isEmpty() ? "FAILED [Reason: " + errors.toString() + "]" : "OK") + "[<b>" + accountRecord.getAccountType() + "</b>]: <a href=\"" + accountHref + "\" target=\"_blank\"><b>" + (new ObjectReference(account, app)).getTitle() + "</b> [" + account.refMofId() + "]</a>"); importReport.append("</td>"); importReport.append("</tr>"); } if (isUpdate) { if (!multiMatchList.isEmpty()) { importReport.append("<tr class='gridTableRowFull'>"); importReport.append("<td class=\"err\" colspan=\"" + (maxCell + 2) + "\">"); importReport.append("NO UPDATE [<b>" + accountRecord.getAccountType() + "</b>] - Multiple Matches:" + multiMatchList); importReport.append("</td>"); importReport.append("</tr>"); } else { importReport.append("<tr class='gridTableRowFull'>"); importReport.append("<td class=\"" + (!errors.isEmpty() ? "err" : "match") + "\" colspan=\"" + (maxCell + 2) + "\">"); importReport.append("UPDATE " + (!errors.isEmpty() ? "FAILED [Reason is:" + errors.toString() + "]" : "OK") + " [<b>" + accountRecord.getAccountType() + "</b>]: <a href=\"" + accountHref + "\" target=\"_blank\"><b>" + (new ObjectReference(account, app)).getTitle() + "</b> [" + account.refMofId() + "]</a>"); importReport.append("</td>"); importReport.append("</tr>"); } } } } /* while */ // Spacer importReport.append("<tr class='gridTableRowFull' style=\"background-color:white;\">"); importReport.append(" <td colspan='" + (maxCell + 2) + "'> </td>"); importReport.append("</tr>"); // Summary importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append("<td colspan=\"" + (maxCell + 2) + "\">"); importReport.append("Sheet: <b>" + sheet.getSheetName() + "</b> |"); importReport.append("data lines <b>read: " + nLinesRead + "</b><br>"); importReport.append("</td>"); importReport.append("</tr>"); importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append(" <td> </td>"); importReport.append(" <td><b>Created</b></td>"); importReport.append("<td colspan=\"" + maxCell + "\"><b>Updated</b></td>"); importReport.append("</tr>"); importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append("<td><b>" + AccountType.Contact.name() + "</b></td>"); importReport.append(" <td>" + nContactsCreated + "</td>"); importReport.append(" <td colspan=\"" + maxCell + "\">" + nContactsUpdated + "</td>"); importReport.append("</tr>"); importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append(" <td><b>" + AccountType.Group.name() + "</b></td>"); importReport.append(" <td>" + nGroupsCreated + "</td>"); importReport.append(" <td colspan=\"" + maxCell + "\">" + nGroupsUpdated + "</td>"); importReport.append("</tr>"); importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append(" <td><b>" + AccountType.LegalEntity.name() + "</b></td>"); importReport.append(" <td>" + nLegalEntitiesCreated + "</td>"); importReport .append(" <td colspan=\"" + maxCell + "\">" + nLegalEntitiesUpdated + "</td>"); importReport.append("</tr>"); importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append(" <td><b>" + AccountType.UnspecifiedAccount.name() + "</b></td>"); importReport.append(" <td>" + nUnspecifiedAccountsCreated + "</td>"); importReport.append( " <td colspan=\"" + maxCell + "\">" + nUnspecifiedAccountsUpdated + "</td>"); importReport.append("</tr>"); if (isImportMembershipMode) { importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append(" <td colspan=\"" + (maxCell + 2) + "\">" + parentGroup.getFullName() + " has now " + parentGroup.getMember().size() + " Members (before import: " + parentGroupMemberSize + " Members)</td>"); importReport.append("</tr>"); } else { if (nLinesRead != nContactsCreated + nContactsUpdated + nGroupsCreated + nGroupsUpdated + nLegalEntitiesCreated + nLegalEntitiesUpdated + nUnspecifiedAccountsCreated + nUnspecifiedAccountsUpdated) { importReport.append("<tr class='sheetInfo gridTableRowFull'>"); importReport.append(" <td class=\"err\" colspan=\"" + (maxCell + 2) + "\">WARNING: some data lines were not processed due to data errors (e.g. multiple matches, missing first/last name, etc.)</td>"); importReport.append("</tr>"); } } } } } finally { new File(this.importFileLocation).delete(); } } }
From source file:org.opencrx.kernel.portal.wizard.ImportPropertiesFromXlsController.java
License:BSD License
/** * OK action.// w w w.j a va2 s . c om * * @throws ServiceException */ public void doOK() throws ServiceException { PersistenceManager pm = this.getPm(); ApplicationContext app = this.getApp(); this.doRefresh(); if (!this.hasPermission) { this.errorMessage = "no permission to run this wizard"; } else { String location = app.getTempFileName(UPLOAD_FILE_FIELD_NAME, ""); try { if (new File(location + ".INFO").exists() && new File(location).exists() && (new File(location).length() > 0)) { String contentMimeType = null; String contentName = null; try { // mimeType and name BufferedReader r = new BufferedReader(new FileReader(location + ".INFO")); contentMimeType = r.readLine(); contentName = r.readLine(); r.close(); new File(location + ".INFO").delete(); } catch (Exception ignore) { } if ((contentName != null) && !contentName.isEmpty() && (contentMimeType != null) && !contentMimeType.isEmpty()) { // the calling object determines which columns are optional/required in the spreadsheet ProductConfigurationTypeSet productConfigurationTypeSet = null; ProductConfigurationType productConfigurationType = null; PropertySet propertySet = null; CrxObject crxObject = null; String callerName = null; String callerParentName = null; ImportTarget importTarget = ImportTarget.NA; // case 1: // required: Property_name // ProductConfigurationTypeSet_name // ProductConfigurationType_name if (this.getObject() instanceof org.opencrx.kernel.product1.jmi1.Segment) { importTarget = ImportTarget.ProductSegment; } // case 2: // required: Property_name // ProductConfigurationType_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) else if (this.getObject() instanceof ProductConfigurationTypeSet) { importTarget = ImportTarget.ProductConfigurationTypeSet; productConfigurationTypeSet = (ProductConfigurationTypeSet) this.getObject(); callerName = ((ProductConfigurationTypeSet) this.getObject()).getName(); } // case 3: // required: Property_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) // ProductConfigurationType_name (if provided, then only Properties of matching ProductConfigurationTypes are considered) else if (this.getObject() instanceof ProductConfigurationType) { importTarget = ImportTarget.ProductConfigurationType; productConfigurationType = (ProductConfigurationType) this.getObject(); callerName = ((ProductConfigurationType) this.getObject()).getName(); RefObject_1_0 parentObj = (RefObject_1_0) pm .getObjectById(this.getObject().refGetPath().getParent().getParent()); if (parentObj instanceof ProductConfigurationTypeSet) { callerParentName = ((ProductConfigurationTypeSet) parentObj).getName(); } } // case 4: // required: Property_name // optional: PropertySet_name (if provided, then only Properties of matching PropertySets are considered) else if (this.getObject() instanceof PropertySet) { importTarget = ImportTarget.PropertySet; propertySet = (PropertySet) this.getObject(); callerName = propertySet.getName(); } // case 5: // required: PropertySet_name // Property_name else if (this.getObject() instanceof CrxObject) { importTarget = ImportTarget.CrxObject; crxObject = (CrxObject) this.getObject(); } // Get product segment org.opencrx.kernel.product1.jmi1.Segment productSegment = Products.getInstance() .getProductSegment(pm, this.getProviderName(), this.getSegmentName()); int idxProperty_dtype = -1; int idxProperty_name = -1; int idxProperty_description = -1; int idxProperty_value = -1; int idxPropertySet_name = -1; int idxPropertySet_description = -1; int idxProductConfigurationTypeSet_name = -1; int idxProductConfigurationTypeSet_description = -1; int idxProductConfigurationType_name = -1; int idxProductConfigurationType_description = -1; // verify whether File exists // Read workbook Workbook wb = null; try { wb = WorkbookFactory.create(new FileInputStream(location)); } catch (Exception e) { this.errorMessage = e.getMessage(); } if (wb != null) { //for (int i = 0; i < workbook.getNumberOfSheets(); i++) { // read first sheet only!!! for (int i = 0; i < 1; i++) { Sheet sheet = wb.getSheetAt(i); int linesRead = 0; int propertiesUpdated = 0; this.importReport += ""; Iterator<Row> rows = sheet.rowIterator(); int nRow = 0; int maxCell = 0; HSSFRow row = null; Map<String, String> attributeMap = new TreeMap<String, String>(); if (rows.hasNext()) { nRow += 1; // read first row with attribute names this.importReport += "<tr class='gridTableHeaderFull'>"; this.importReport += "<td>#</td>"; row = (HSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); int nCell = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > maxCell) { maxCell = nCell; } try { if ((cell.getCellType() == HSSFCell.CELL_TYPE_STRING) && (cell.getStringCellValue() != null)) { boolean isSearchAttribute = false; String cellValue = (cell.getStringCellValue().trim()); attributeMap.put(DECIMAL_FORMAT_0000.format(nCell), cellValue); // get idx of select attributes if (ATTR_PROPERTY_DTYPE.compareToIgnoreCase(cellValue) == 0) { idxProperty_dtype = nCell; } else if (ATTR_PROPERTY_NAME.compareToIgnoreCase(cellValue) == 0) { idxProperty_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTY_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProperty_description = nCell; } else if (ATTR_PROPERTY_VALUE .compareToIgnoreCase(cellValue) == 0) { idxProperty_value = nCell; } else if (ATTR_PROPERTYSET_NAME .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTYSET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_description = nCell; } this.importReport += "<td " + (isSearchAttribute ? "class='searchAttr' title='attribute used for matching'" : "") + ">" + cellValue + "</td>"; } else { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + "[not a string cell]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + ec.getMessage() + "</td>"; } } this.importReport += "</tr>"; } while (rows.hasNext()) { nRow += 1; linesRead += 1; row = (HSSFRow) rows.next(); String propertyType = null; String propertyName = null; String propertyDescription = null; HSSFCell propertyValue = null; String propertySetName = null; String propertySetDescription = null; String productConfigurationTypeSetName = null; String productConfigurationTypeSetDescription = null; String productConfigurationTypeName = null; String productConfigurationTypeDescription = null; String cellId = null; Map<String, Cell> valueMap = new TreeMap<String, Cell>( String.CASE_INSENSITIVE_ORDER); String appendErrorRow = null; this.importReport += "<tr class='gridTableRowFull'>"; this.importReport += "<td id='r" + nRow + "'><b>" + DECIMAL_FORMAT_0000.format(nRow) + "</b></td>"; String jsBuffer = ""; try { Iterator<Cell> cells = row.cellIterator(); int nCell = 0; int currentCell = 0; appendErrorRow = null; while (cells.hasNext()) { //HSSFCell cell = (HSSFCell)row.getCell((short)0); HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > currentCell) { this.importReport += "<td colspan=\"" + (nCell - currentCell) + "\" class=\"empty\"> </td>"; } currentCell = nCell + 1; try { cellId = "id='r" + nRow + (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString().toUpperCase() + "'"; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue().trim(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); if (nCell == idxProperty_dtype) { propertyType = cellValue; } else if (nCell == idxProperty_name) { propertyName = cellValue; } else if (nCell == idxProperty_description) { propertyDescription = cellValue; } else if (nCell == idxProperty_value) { propertyValue = cell; } else if (nCell == idxPropertySet_name) { propertySetName = cellValue; } else if (nCell == idxPropertySet_description) { propertySetDescription = cellValue; } else if (nCell == idxProductConfigurationTypeSet_name) { productConfigurationTypeSetName = cellValue; } else if (nCell == idxProductConfigurationTypeSet_description) { productConfigurationTypeSetDescription = cellValue; } else if (nCell == idxProductConfigurationType_name) { productConfigurationTypeName = cellValue; } else if (nCell == idxProductConfigurationType_description) { productConfigurationTypeDescription = cellValue; } this.importReport += "<td " + cellId + ">" + (cellValue != null ? (cellValue.replace("\r\n", EOL_HTML)).replace("\n", EOL_HTML) : "") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (nCell == idxProperty_value) { propertyValue = cell; } BigDecimal cellValue = new BigDecimal( cell.getNumericCellValue()); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + cellValue + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (nCell == idxProperty_value) { propertyValue = cell; } boolean cellValue = cell.getBooleanCellValue(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + (cellValue ? "TRUE" : "FALSE") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + " class=\"empty\"> </td>"; } else { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + "[cell-type (" + cell.getCellType() + ") not supported]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + cell.getCellFormula() + "</td>"; } } if (nCell < maxCell) { this.importReport += "<td colspan=\"" + (maxCell - nCell) + "\" class=\"empty\"></td>"; } } catch (Exception e) { this.importReport += "<td class='err' colspan=\"" + (maxCell + 2) + "\">ERROR in Attribute Row!</td>"; } // process row Property property = null; if (this.isSupportedDtypeValue(propertyType, propertyValue)) { /* case 1 */ if (importTarget == ImportTarget.ProductSegment && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from Product Segment)';"; if (propertySetName == null || propertySetName.isEmpty()) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (org.opencrx.kernel.product1.jmi1.ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 2 */ } else if (importTarget == ImportTarget.ProductConfigurationTypeSet && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationTypeSet)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerName != null && productConfigurationTypeSetName != null && callerName.equals(productConfigurationTypeSetName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 3 */ } else if (importTarget == ImportTarget.ProductConfigurationType && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationType)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerParentName != null && productConfigurationTypeSetName != null && callerParentName .equals(productConfigurationTypeSetName))) && ((productConfigurationTypeName == null || productConfigurationTypeName.isEmpty()) || (callerName != null && productConfigurationTypeName != null && callerName .equals(productConfigurationTypeName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm.getObjectById( property.refGetPath().getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 4 */ } else if (importTarget == ImportTarget.PropertySet && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from PropertySet)';"; if (((propertySetName == null || propertySetName.isEmpty()) || (callerName != null && propertySetName != null && callerName.equals(propertySetName))) && (productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, null, propertySet, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 5 */ } else if (importTarget == ImportTarget.CrxObject && propertyName != null && !propertyName.isEmpty() && propertySetName != null && !propertySetName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from CrxObject)';"; if ((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { //createOrUpdatePropertyOfPropertySet property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, crxObject, null, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } } else { // incomplete and/or inconsistent row --> disregard this row jsBuffer += "$('r" + nRow + "').title += 'incomplete and/or inconsistent row';"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CELL VALUE TYPE NOT SUPPORTED</td></tr>"; } this.importReport += "</tr>"; if (property != null) { propertiesUpdated++; cellId = "r" + nRow + ATTR_PROPERTY_NAME.toUpperCase(); jsBuffer += "try{$('r" + nRow + "').className += ' ok';$('" + cellId + "').className=' ok';$('" + cellId + "').innerHTML = '<a href=\"" + this.getSelectObjectHref(property) + "\" target=\"_blank\"><b>' + " + "$('" + cellId + "').innerHTML +" + "'</b></a>'}catch(e){};"; cellId = "r" + nRow + ATTR_PROPERTY_VALUE.toUpperCase(); if (propertyValue != null) { jsBuffer += "try{$('" + cellId + "').className='ok';}catch(e){};"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>VERIFY Property DTYPE/NAME/VALUE</td></tr>"; jsBuffer += "$('r" + nRow + "').className += 'nok';"; } if (appendErrorRow != null) { this.importReport += appendErrorRow; } valueMap = null; this.importReport += "<tr style=\"display:none;\">"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += jsBuffer.length() > 0 ? "<script language='javascript' type='text/javascript'>" + jsBuffer + "</script>" : ""; this.importReport += " </td>"; this.importReport += "</tr>"; } /* while */ // Spacer this.importReport += "<tr class='gridTableRowFull' style=\"background-color:white;\">"; this.importReport += " <td colspan='" + (maxCell + 2) + "'> </td>"; this.importReport += "</tr>"; // Summary this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += " Sheet: <b>" + wb.getSheetName(i) + "</b> |"; this.importReport += " data lines <b>read: " + linesRead + "</b><br>"; this.importReport += "</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td> </td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">Created / Updated</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td>Properties</td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">" + propertiesUpdated + "</td>"; this.importReport += "</tr>"; if (linesRead != propertiesUpdated) { this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td class=\"err\" colspan=\"" + (maxCell + 2) + "\">WARNING: some data lines were not processed due to data errors (e.g. multiple matches, missing name, etc.)</td>"; this.importReport += "</tr>"; } } } } } else { this.errorMessage = "No Excel workbook selected"; } } finally { new File(location).delete(); } } }
From source file:org.openepics.conf.dl.DataStreamExcel.java
License:Open Source License
@Override public void open(String fileName, String sheetName) throws Exception { xlfile = new FileInputStream(fileName); workbook = WorkbookFactory.create(xlfile); if (workbook == null) { throw new CDLException(CDLExceptionCode.OSTREAM, "Invalid file (workbook) name " + xlfile); }/*ww w. j a v a 2 s .c om*/ sheet = workbook.getSheet(sheetName); if (sheet == null) { throw new CDLException(CDLExceptionCode.OSTREAM, "Invalid sheet name " + sheetName); } evaluator = workbook.getCreationHelper().createFormulaEvaluator(); currentRow = null; nextRowNum = 0; lastRowNumber = sheet.getLastRowNum(); }
From source file:org.openflexo.technologyadapter.excel.rm.ExcelWorkbookResourceImpl.java
License:Open Source License
private ExcelWorkbook createExcelWorkbook(FileFlexoIODelegate delegate) { Workbook wb = null;// w ww .j a va 2 s.c o m ExcelWorkbook newWorkbook = null; try { if (!delegate.exists() && delegate.getFile().getAbsolutePath().endsWith(".xls")) { wb = new HSSFWorkbook(); } else if (!delegate.exists() && delegate.getFile().getAbsolutePath().endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else { wb = WorkbookFactory.create(new FileInputStream(delegate.getFile())); } BasicExcelModelConverter converter = new BasicExcelModelConverter(); newWorkbook = converter.convertExcelWorkbook(wb, getTechnologyAdapter()); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (InvalidFormatException e) { // TODO Auto-generated catch block e.printStackTrace(); } return newWorkbook; }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * @return an Excel Workbook for the given argument *///from w w w. j a v a 2 s . co m protected Workbook getExcelTemplate(ReportDesign design) throws IOException { Workbook wb = null; InputStream is = null; try { ReportDesignResource r = getTemplate(design); is = new ByteArrayInputStream(r.getContents()); POIFSFileSystem fs = new POIFSFileSystem(is); wb = WorkbookFactory.create(fs); } catch (Exception e) { log.warn("No template file found, will use default Excel output"); } finally { IOUtils.closeQuietly(is); } return wb; }
From source file:org.openmrs.module.spreadsheetimport.SpreadsheetImportUtil.java
License:Open Source License
public static File importTemplate(SpreadsheetImportTemplate template, MultipartFile file, String sheetName, List<String> messages, boolean rollbackTransaction) throws Exception { if (file.isEmpty()) { messages.add("file must not be empty"); return null; }/*from ww w .jav a 2 s . co m*/ // Open file Workbook wb = WorkbookFactory.create(file.getInputStream()); Sheet sheet; if (!StringUtils.hasText(sheetName)) { sheet = wb.getSheetAt(0); } else { sheet = wb.getSheet(sheetName); } // Header row Row firstRow = sheet.getRow(0); if (firstRow == null) { messages.add("Spreadsheet header row must not be null"); return null; } List<String> columnNames = new Vector<String>(); for (Cell cell : firstRow) { columnNames.add(cell.getStringCellValue()); } if (log.isDebugEnabled()) { log.debug("Column names: " + columnNames.toString()); } // Required column names List<String> columnNamesOnlyInTemplate = new Vector<String>(); columnNamesOnlyInTemplate.addAll(template.getColumnNamesAsList()); columnNamesOnlyInTemplate.removeAll(columnNames); if (columnNamesOnlyInTemplate.isEmpty() == false) { messages.add("required column names not present: " + toString(columnNamesOnlyInTemplate)); return null; } // Extra column names? List<String> columnNamesOnlyInSheet = new Vector<String>(); columnNamesOnlyInSheet.addAll(columnNames); columnNamesOnlyInSheet.removeAll(template.getColumnNamesAsList()); if (columnNamesOnlyInSheet.isEmpty() == false) { messages.add( "Extra column names present, these will not be processed: " + toString(columnNamesOnlyInSheet)); } // Process rows boolean skipThisRow = true; for (Row row : sheet) { if (skipThisRow == true) { skipThisRow = false; } else { boolean rowHasData = false; Map<UniqueImport, Set<SpreadsheetImportTemplateColumn>> rowData = template .getMapOfUniqueImportToColumnSetSortedByImportIdx(); for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { int idx = columnNames.indexOf(column.getName()); Cell cell = row.getCell(idx); Object value = null; // check for empty cell (new Encounter) if (cell == null) { rowHasData = true; column.setValue(""); continue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = new Boolean(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: value = new Byte(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { java.util.Date date = cell.getDateCellValue(); value = "'" + new java.sql.Timestamp(date.getTime()).toString() + "'"; } else { value = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: // Escape for SQL value = "'" + cell.getRichStringCellValue() + "'"; break; } if (value != null) { rowHasData = true; column.setValue(value); } else column.setValue(""); } } for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); boolean isFirst = true; for (SpreadsheetImportTemplateColumn column : columnSet) { if (isFirst) { // Should be same for all columns in unique import // System.out.println("SpreadsheetImportUtil.importTemplate: column.getColumnPrespecifiedValues(): " + column.getColumnPrespecifiedValues().size()); if (column.getColumnPrespecifiedValues().size() > 0) { Set<SpreadsheetImportTemplateColumnPrespecifiedValue> columnPrespecifiedValueSet = column .getColumnPrespecifiedValues(); for (SpreadsheetImportTemplateColumnPrespecifiedValue columnPrespecifiedValue : columnPrespecifiedValueSet) { // System.out.println(columnPrespecifiedValue.getPrespecifiedValue().getValue()); } } } } } if (rowHasData) { Exception exception = null; try { DatabaseBackend.validateData(rowData); String encounterId = DatabaseBackend.importData(rowData, rollbackTransaction); if (encounterId != null) { for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { if ("encounter".equals(column.getTableName())) { int idx = columnNames.indexOf(column.getName()); Cell cell = row.getCell(idx); if (cell == null) cell = row.createCell(idx); cell.setCellValue(encounterId); } } } } } catch (SpreadsheetImportTemplateValidationException e) { messages.add("Validation failed: " + e.getMessage()); return null; } catch (SpreadsheetImportDuplicateValueException e) { messages.add("found duplicate value for column " + e.getColumn().getName() + " with value " + e.getColumn().getValue()); return null; } catch (SpreadsheetImportSQLSyntaxException e) { messages.add("SQL syntax error: \"" + e.getSqlErrorMessage() + "\".<br/>Attempted SQL Statement: \"" + e.getSqlStatement() + "\""); return null; } catch (Exception e) { exception = e; } if (exception != null) { throw exception; } } } } // write back Excel file to a temp location File returnFile = File.createTempFile("sim", ".xls"); FileOutputStream fos = new FileOutputStream(returnFile); wb.write(fos); fos.close(); return returnFile; }
From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java
License:Apache License
private boolean saveDeltaSnapshot() { try {//from w w w .j av a2 s . co m Workbook workbook = WorkbookFactory .create(this.getClass().getResourceAsStream(TEMPLATE_DELTA_V_SQL_AREA_XLSX)); statementsSheet = workbook.getSheet("Delta V$SQLAREA"); executionPlansSheet = workbook.getSheet("Execution Plans"); waitEventsForStatementSheet = workbook.getSheet("Wait Events per SQL Statement"); waitEventsForTimeSpanSheet = workbook.getSheet("All Wait Events"); hyperlinkStyle = createHyperlinkStyle(workbook); writeDeltaSnapshotStatements(); List<DeltaSQLStatementSnapshot> worstStatements = getWorstSQLStatements(); try { writeExecutionPlansForWorstStatements(worstStatements); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, e); } try { writeWaitEventsForWorstStatements(worstStatements); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, e); } try { writeWaitEventsForTimeSpan(); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, e); } if (listener != null) { listener.informFinished(); } OutputStream outputStream = new FileOutputStream(destination); workbook.write(outputStream); outputStream.close(); return true; } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, e); return false; } }
From source file:org.opentestsystem.delivery.testreg.integration.DownloadTemplateIntegrationTest.java
License:Open Source License
private void assertExcelTemplate(InputStream inpustream) throws Exception { Workbook workbook = WorkbookFactory.create(inpustream); assertThat(workbook.getNumberOfSheets(), is(1)); String sheetName = new String(); if (formatType.name().length() > 32) { sheetName = formatType.name().substring(0, 31); } else {/*w ww. j a v a2s . co m*/ sheetName = formatType.name(); } Sheet sheet = workbook.getSheet(sheetName); assertThat(sheet, is(notNullValue())); String[] columns = StringUtils.splitByWholeSeparatorPreserveAllTokens(columnString, "|"); assertThat(sheet.getRow(HEADER_ROW).getLastCellNum(), is((short) columns.length)); int cellNo = 0; for (String columnName : columns) { assertThat(sheet.getRow(HEADER_ROW).getCell(cellNo++).getStringCellValue(), is(columnName)); } }
From source file:org.opentestsystem.delivery.testreg.upload.ExcelFileAppender.java
License:Open Source License
public InputStream insertAtTop(String text, InputStream inputStream) { try {/* www . j a v a 2 s .com*/ final Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); sheet.shiftRows(0, sheet.getLastRowNum(), 1); Row formatTypeRow = sheet.createRow(0); formatTypeRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(text); return new ByteArrayInputStream(new ByteArrayOutputStream() { { workbook.write(this); } }.toByteArray()); } catch (InvalidFormatException | IOException e) { throw new RuntimeException(""); } }