List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java
License:Open Source License
public static String xmlTagAttributesFromSS(Sheet sheet, int increment) throws JSONException { // Return null, if sheet is null if (sheet == null) { return null; }/*w w w . j ava2s .co m*/ // Iterate through the rows. StringBuilder stringBuilderRows = new StringBuilder(); List<String> stack = new ArrayList<String>(); Boolean first = Boolean.TRUE; int splitRowNumber = 0; if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) { splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition(); } for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) { continue; } String tagAttributes = tagAttributesAsString(row); if (tagAttributes.isEmpty()) { continue; } String stringRow = xmlFromRowAsTreeAttributes(tagAttributes, row, stack, increment); if (first) { stringBuilderRows.append(IPLAST_LEFT_TAG).append(stringRow); first = Boolean.FALSE; } else { stringBuilderRows.append(stringRow); } System.out.println(stringRow); } // Close all opened tags from stack if (!stack.isEmpty()) { int n = stack.size(); for (int i = n - 1; i >= 0; --i) { stringBuilderRows = stringBuilderRows.append(rightTag(stack.get(i))); } } // Get the XML text. stringBuilderRows = stringBuilderRows.append(IPLAST_RIGHT_TAG); return stringBuilderRows.toString(); }
From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java
License:Open Source License
private static String xmlTagNodesFromSSheet(Sheet sheet, int increment, int dataRangeStart, int dataRangeLen) throws JSONException { // Return null, if sheet is null if (sheet == null) { return null; }/*from w ww . ja v a 2 s . c o m*/ // Iterate through the rows. StringBuilder stringBuilderRows = new StringBuilder(); List<String> stack = new ArrayList<String>(); // Boolean first = Boolean.TRUE; int splitRowNumber = 0; if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) { splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition(); } for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) { continue; } String tagNodes = tagNodesAsString(row, dataRangeStart, dataRangeLen); if (tagNodes.isEmpty()) { continue; } String stringRow = xmlFromRowAsTreeNodes(tagNodes, row, stack, increment); stringBuilderRows.append(stringRow); } // Close all opened tags from stack if (!stack.isEmpty()) { int n = stack.size(); for (int i = n - 1; i >= 0; --i) { stringBuilderRows = stringBuilderRows.append(rightTag(stack.get(i))); } } return stringBuilderRows.toString(); }
From source file:org.meveo.admin.parse.xls.XLSFile.java
License:Open Source License
public void parse() throws IOException { Workbook w;/*from ww w. j a v a 2 s. c o m*/ try { w = WorkbookFactory.create(new FileInputStream(file)); // Get the first sheet Sheet sheet = w.getSheetAt(0); // Loop over first 10 column and lines Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); String[] strs = new String[row.getPhysicalNumberOfCells()]; int cellCtr = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); strs[cellCtr++] = cell.getStringCellValue(); } contexts.add(strs); } } catch (InvalidFormatException e) { log.error("invalid file format ", e); } }
From source file:org.mousephenotype.cda.utilities.DataReaderXls.java
License:Apache License
/** * Opens the stream defined by the url used in the constructor. * @throws IOException/*w w w . jav a2s . c om*/ */ @Override protected void open() throws IOException { try (InputStream inputStream = url.openStream()) { workbook = WorkbookFactory.create(inputStream); } catch (Exception e) { System.out.println( "Error opening workbook. Will treat as empty workbook. Exception: " + e.getLocalizedMessage()); return; } Sheet sheet = workbook.getSheetAt(0); rowIterator = sheet.rowIterator(); }
From source file:org.mousephenotype.www.testing.model.DataReaderXls.java
License:Apache License
/** * Opens the stream defined by the url used in the constructor. * @throws IOException /* w w w.jav a 2 s . c om*/ */ @Override public void open() throws IOException { try { try (InputStream inputStream = url.openStream()) { workbook = WorkbookFactory.create(inputStream); } Sheet sheet = workbook.getSheetAt(0); rowIterator = sheet.rowIterator(); } catch (InvalidFormatException e) { System.out.println("InvalidFormatException: " + e.getLocalizedMessage()); throw new IOException(e); } }
From source file:org.opencrx.kernel.portal.wizard.ImportAccountsFromXlsController.java
License:BSD License
/** * Perform the import and generate report. * /*from w ww.ja v a 2 s. c o m*/ * @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 . jav a2s . c o m*/ * * @throws ServiceException */ public void doOK() throws ServiceException { PersistenceManager pm = this.getPm(); ApplicationContext app = this.getApp(); this.doRefresh(); if (!this.hasPermission) { this.errorMessage = "no permission to run this wizard"; } else { String location = app.getTempFileName(UPLOAD_FILE_FIELD_NAME, ""); try { if (new File(location + ".INFO").exists() && new File(location).exists() && (new File(location).length() > 0)) { String contentMimeType = null; String contentName = null; try { // mimeType and name BufferedReader r = new BufferedReader(new FileReader(location + ".INFO")); contentMimeType = r.readLine(); contentName = r.readLine(); r.close(); new File(location + ".INFO").delete(); } catch (Exception ignore) { } if ((contentName != null) && !contentName.isEmpty() && (contentMimeType != null) && !contentMimeType.isEmpty()) { // the calling object determines which columns are optional/required in the spreadsheet ProductConfigurationTypeSet productConfigurationTypeSet = null; ProductConfigurationType productConfigurationType = null; PropertySet propertySet = null; CrxObject crxObject = null; String callerName = null; String callerParentName = null; ImportTarget importTarget = ImportTarget.NA; // case 1: // required: Property_name // ProductConfigurationTypeSet_name // ProductConfigurationType_name if (this.getObject() instanceof org.opencrx.kernel.product1.jmi1.Segment) { importTarget = ImportTarget.ProductSegment; } // case 2: // required: Property_name // ProductConfigurationType_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) else if (this.getObject() instanceof ProductConfigurationTypeSet) { importTarget = ImportTarget.ProductConfigurationTypeSet; productConfigurationTypeSet = (ProductConfigurationTypeSet) this.getObject(); callerName = ((ProductConfigurationTypeSet) this.getObject()).getName(); } // case 3: // required: Property_name // optional: ProductConfigurationTypeSet_name (if provided, then only Properties of matching ProductConfigurationTypeSets are considered) // ProductConfigurationType_name (if provided, then only Properties of matching ProductConfigurationTypes are considered) else if (this.getObject() instanceof ProductConfigurationType) { importTarget = ImportTarget.ProductConfigurationType; productConfigurationType = (ProductConfigurationType) this.getObject(); callerName = ((ProductConfigurationType) this.getObject()).getName(); RefObject_1_0 parentObj = (RefObject_1_0) pm .getObjectById(this.getObject().refGetPath().getParent().getParent()); if (parentObj instanceof ProductConfigurationTypeSet) { callerParentName = ((ProductConfigurationTypeSet) parentObj).getName(); } } // case 4: // required: Property_name // optional: PropertySet_name (if provided, then only Properties of matching PropertySets are considered) else if (this.getObject() instanceof PropertySet) { importTarget = ImportTarget.PropertySet; propertySet = (PropertySet) this.getObject(); callerName = propertySet.getName(); } // case 5: // required: PropertySet_name // Property_name else if (this.getObject() instanceof CrxObject) { importTarget = ImportTarget.CrxObject; crxObject = (CrxObject) this.getObject(); } // Get product segment org.opencrx.kernel.product1.jmi1.Segment productSegment = Products.getInstance() .getProductSegment(pm, this.getProviderName(), this.getSegmentName()); int idxProperty_dtype = -1; int idxProperty_name = -1; int idxProperty_description = -1; int idxProperty_value = -1; int idxPropertySet_name = -1; int idxPropertySet_description = -1; int idxProductConfigurationTypeSet_name = -1; int idxProductConfigurationTypeSet_description = -1; int idxProductConfigurationType_name = -1; int idxProductConfigurationType_description = -1; // verify whether File exists // Read workbook Workbook wb = null; try { wb = WorkbookFactory.create(new FileInputStream(location)); } catch (Exception e) { this.errorMessage = e.getMessage(); } if (wb != null) { //for (int i = 0; i < workbook.getNumberOfSheets(); i++) { // read first sheet only!!! for (int i = 0; i < 1; i++) { Sheet sheet = wb.getSheetAt(i); int linesRead = 0; int propertiesUpdated = 0; this.importReport += ""; Iterator<Row> rows = sheet.rowIterator(); int nRow = 0; int maxCell = 0; HSSFRow row = null; Map<String, String> attributeMap = new TreeMap<String, String>(); if (rows.hasNext()) { nRow += 1; // read first row with attribute names this.importReport += "<tr class='gridTableHeaderFull'>"; this.importReport += "<td>#</td>"; row = (HSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); int nCell = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > maxCell) { maxCell = nCell; } try { if ((cell.getCellType() == HSSFCell.CELL_TYPE_STRING) && (cell.getStringCellValue() != null)) { boolean isSearchAttribute = false; String cellValue = (cell.getStringCellValue().trim()); attributeMap.put(DECIMAL_FORMAT_0000.format(nCell), cellValue); // get idx of select attributes if (ATTR_PROPERTY_DTYPE.compareToIgnoreCase(cellValue) == 0) { idxProperty_dtype = nCell; } else if (ATTR_PROPERTY_NAME.compareToIgnoreCase(cellValue) == 0) { idxProperty_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTY_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProperty_description = nCell; } else if (ATTR_PROPERTY_VALUE .compareToIgnoreCase(cellValue) == 0) { idxProperty_value = nCell; } else if (ATTR_PROPERTYSET_NAME .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_name = nCell; isSearchAttribute = true; } else if (ATTR_PROPERTYSET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxPropertySet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPESET_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationTypeSet_description = nCell; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_NAME .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_name = nCell; isSearchAttribute = true; } else if (ATTR_PRODUCTCONFIGURATIONTYPE_DESCRIPTION .compareToIgnoreCase(cellValue) == 0) { idxProductConfigurationType_description = nCell; } this.importReport += "<td " + (isSearchAttribute ? "class='searchAttr' title='attribute used for matching'" : "") + ">" + cellValue + "</td>"; } else { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + "[not a string cell]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class='err'>c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + ec.getMessage() + "</td>"; } } this.importReport += "</tr>"; } while (rows.hasNext()) { nRow += 1; linesRead += 1; row = (HSSFRow) rows.next(); String propertyType = null; String propertyName = null; String propertyDescription = null; HSSFCell propertyValue = null; String propertySetName = null; String propertySetDescription = null; String productConfigurationTypeSetName = null; String productConfigurationTypeSetDescription = null; String productConfigurationTypeName = null; String productConfigurationTypeDescription = null; String cellId = null; Map<String, Cell> valueMap = new TreeMap<String, Cell>( String.CASE_INSENSITIVE_ORDER); String appendErrorRow = null; this.importReport += "<tr class='gridTableRowFull'>"; this.importReport += "<td id='r" + nRow + "'><b>" + DECIMAL_FORMAT_0000.format(nRow) + "</b></td>"; String jsBuffer = ""; try { Iterator<Cell> cells = row.cellIterator(); int nCell = 0; int currentCell = 0; appendErrorRow = null; while (cells.hasNext()) { //HSSFCell cell = (HSSFCell)row.getCell((short)0); HSSFCell cell = (HSSFCell) cells.next(); nCell = cell.getColumnIndex(); if (nCell > currentCell) { this.importReport += "<td colspan=\"" + (nCell - currentCell) + "\" class=\"empty\"> </td>"; } currentCell = nCell + 1; try { cellId = "id='r" + nRow + (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString().toUpperCase() + "'"; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue().trim(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); if (nCell == idxProperty_dtype) { propertyType = cellValue; } else if (nCell == idxProperty_name) { propertyName = cellValue; } else if (nCell == idxProperty_description) { propertyDescription = cellValue; } else if (nCell == idxProperty_value) { propertyValue = cell; } else if (nCell == idxPropertySet_name) { propertySetName = cellValue; } else if (nCell == idxPropertySet_description) { propertySetDescription = cellValue; } else if (nCell == idxProductConfigurationTypeSet_name) { productConfigurationTypeSetName = cellValue; } else if (nCell == idxProductConfigurationTypeSet_description) { productConfigurationTypeSetDescription = cellValue; } else if (nCell == idxProductConfigurationType_name) { productConfigurationTypeName = cellValue; } else if (nCell == idxProductConfigurationType_description) { productConfigurationTypeDescription = cellValue; } this.importReport += "<td " + cellId + ">" + (cellValue != null ? (cellValue.replace("\r\n", EOL_HTML)).replace("\n", EOL_HTML) : "") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (nCell == idxProperty_value) { propertyValue = cell; } BigDecimal cellValue = new BigDecimal( cell.getNumericCellValue()); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + cellValue + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (nCell == idxProperty_value) { propertyValue = cell; } boolean cellValue = cell.getBooleanCellValue(); valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + ">" + (cellValue ? "TRUE" : "FALSE") + "</td>"; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { valueMap.put( (attributeMap.get(DECIMAL_FORMAT_0000.format(nCell))) .toString(), cell); this.importReport += "<td " + cellId + " class=\"empty\"> </td>"; } else { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + "[cell-type (" + cell.getCellType() + ") not supported]<br>" + cell.getCellFormula() + "</td>"; } } catch (Exception ec) { this.importReport += "<td class=\"err\">r" + DECIMAL_FORMAT_0000.format(nRow) + "-c" + DECIMAL_FORMAT_0000.format(nCell) + " [UNKNOWN ERROR]<br>" + cell.getCellFormula() + "</td>"; } } if (nCell < maxCell) { this.importReport += "<td colspan=\"" + (maxCell - nCell) + "\" class=\"empty\"></td>"; } } catch (Exception e) { this.importReport += "<td class='err' colspan=\"" + (maxCell + 2) + "\">ERROR in Attribute Row!</td>"; } // process row Property property = null; if (this.isSupportedDtypeValue(propertyType, propertyValue)) { /* case 1 */ if (importTarget == ImportTarget.ProductSegment && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeSetName != null && !productConfigurationTypeSetName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from Product Segment)';"; if (propertySetName == null || propertySetName.isEmpty()) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (org.opencrx.kernel.product1.jmi1.ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 2 */ } else if (importTarget == ImportTarget.ProductConfigurationTypeSet && propertyName != null && !propertyName.isEmpty() && productConfigurationTypeName != null && !productConfigurationTypeName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationTypeSet)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerName != null && productConfigurationTypeSetName != null && callerName.equals(productConfigurationTypeSetName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm .getObjectById(new Path(property.refMofId()) .getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 3 */ } else if (importTarget == ImportTarget.ProductConfigurationType && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of ProductConfigurationTypeSet (called from ProductConfigurationType)';"; if (((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) || (callerParentName != null && productConfigurationTypeSetName != null && callerParentName .equals(productConfigurationTypeSetName))) && ((productConfigurationTypeName == null || productConfigurationTypeName.isEmpty()) || (callerName != null && productConfigurationTypeName != null && callerName .equals(productConfigurationTypeName))) && (propertySetName == null || propertySetName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet( productConfigurationTypeSet, productConfigurationTypeSetName, productConfigurationTypeSetDescription, productConfigurationType, productConfigurationTypeName, productConfigurationTypeDescription, null, null, null, null, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); if (property != null) { this.updateProductConfigurationType( (ProductConfigurationType) pm.getObjectById( property.refGetPath().getParent().getParent()), valueMap); } } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 4 */ } else if (importTarget == ImportTarget.PropertySet && propertyName != null && !propertyName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from PropertySet)';"; if (((propertySetName == null || propertySetName.isEmpty()) || (callerName != null && propertySetName != null && callerName.equals(propertySetName))) && (productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, null, propertySet, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } /* case 5 */ } else if (importTarget == ImportTarget.CrxObject && propertyName != null && !propertyName.isEmpty() && propertySetName != null && !propertySetName.isEmpty()) { jsBuffer += "$('r" + nRow + "').title += 'Property Of PropertySet (called from CrxObject)';"; if ((productConfigurationTypeSetName == null || productConfigurationTypeSetName.isEmpty()) && (productConfigurationTypeName == null || productConfigurationTypeName.isEmpty())) { //createOrUpdatePropertyOfPropertySet property = this.createOrUpdatePropertyOfPropertySet(null, null, null, null, null, null, crxObject, null, propertySetName, propertySetDescription, propertyType, propertyName, propertyDescription, propertyValue, productSegment, app); } else { jsBuffer += "$('r" + nRow + "').title += ' - verify data row';"; } } else { // incomplete and/or inconsistent row --> disregard this row jsBuffer += "$('r" + nRow + "').title += 'incomplete and/or inconsistent row';"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>CELL VALUE TYPE NOT SUPPORTED</td></tr>"; } this.importReport += "</tr>"; if (property != null) { propertiesUpdated++; cellId = "r" + nRow + ATTR_PROPERTY_NAME.toUpperCase(); jsBuffer += "try{$('r" + nRow + "').className += ' ok';$('" + cellId + "').className=' ok';$('" + cellId + "').innerHTML = '<a href=\"" + this.getSelectObjectHref(property) + "\" target=\"_blank\"><b>' + " + "$('" + cellId + "').innerHTML +" + "'</b></a>'}catch(e){};"; cellId = "r" + nRow + ATTR_PROPERTY_VALUE.toUpperCase(); if (propertyValue != null) { jsBuffer += "try{$('" + cellId + "').className='ok';}catch(e){};"; } } else { appendErrorRow = "<tr class='gridTableRowFull'><td class='err' colspan='" + (maxCell + 2) + "'>VERIFY Property DTYPE/NAME/VALUE</td></tr>"; jsBuffer += "$('r" + nRow + "').className += 'nok';"; } if (appendErrorRow != null) { this.importReport += appendErrorRow; } valueMap = null; this.importReport += "<tr style=\"display:none;\">"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += jsBuffer.length() > 0 ? "<script language='javascript' type='text/javascript'>" + jsBuffer + "</script>" : ""; this.importReport += " </td>"; this.importReport += "</tr>"; } /* while */ // Spacer this.importReport += "<tr class='gridTableRowFull' style=\"background-color:white;\">"; this.importReport += " <td colspan='" + (maxCell + 2) + "'> </td>"; this.importReport += "</tr>"; // Summary this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td colspan=\"" + (maxCell + 2) + "\">"; this.importReport += " Sheet: <b>" + wb.getSheetName(i) + "</b> |"; this.importReport += " data lines <b>read: " + linesRead + "</b><br>"; this.importReport += "</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td> </td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">Created / Updated</td>"; this.importReport += "</tr>"; this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td>Properties</td>"; this.importReport += " <td colspan=\"" + (maxCell + 1) + "\">" + propertiesUpdated + "</td>"; this.importReport += "</tr>"; if (linesRead != propertiesUpdated) { this.importReport += "<tr class='sheetInfo gridTableRowFull'>"; this.importReport += " <td class=\"err\" colspan=\"" + (maxCell + 2) + "\">WARNING: some data lines were not processed due to data errors (e.g. multiple matches, missing name, etc.)</td>"; this.importReport += "</tr>"; } } } } } else { this.errorMessage = "No Excel workbook selected"; } } finally { new File(location).delete(); } } }
From source file:org.openmrs.module.reporting.common.ExcelUtilTest.java
License:Open Source License
protected Cell getCellToTheRightOf(Sheet sheet, Object contents) { for (Iterator<Row> ri = sheet.rowIterator(); ri.hasNext();) { Row row = ri.next();/*from ww w . j a v a 2s . c o m*/ for (Iterator<Cell> ci = row.cellIterator(); ci.hasNext();) { Cell cell = ci.next(); if (contents.equals(ExcelUtil.getCellContents(cell))) { return ci.next(); } } } return null; }
From source file:org.openmrs.module.reporting.report.renderer.ExcelTemplateRendererTest.java
License:Mozilla Public License
public void testLocalization(String prefix, String locale, String emrIdVal, String genderVal, String dobVal) throws Exception { ReportDefinition rd = new ReportDefinition(); SqlDataSetDefinition testDataSet = new SqlDataSetDefinition(); testDataSet.setSqlQuery(/* w w w . ja v a2 s . co m*/ "select p.patient_id as PID, n.gender as GENDER, n.birthdate as DOB from patient p, person n where p.patient_id = n.person_id and n.gender = 'M'"); rd.addDataSetDefinition("dataset", testDataSet, null); // Next, we set up the ReportDesign and ReportDesignResource files for the renderer final ReportDesign design = new ReportDesign(); design.setName("TestDesign"); design.setReportDefinition(rd); design.setRendererType(ExcelTemplateRenderer.class); ReportDesignResource resource = new ReportDesignResource(); resource.setName("template.xls"); InputStream is = OpenmrsClassLoader.getInstance().getResourceAsStream( "org/openmrs/module/reporting/report/renderer/ExcelTemplateLocalizeLabelsTest.xls"); resource.setContents(IOUtils.toByteArray(is)); IOUtils.closeQuietly(is); design.addResource(resource); Properties props = new Properties(); props.put("columnTranslationPrefix", prefix); props.put("columnTranslationLocale", locale); design.setProperties(props); // For now, we need this little magic to simulate what would happen if this were all stored in the database via the UI ExcelTemplateRenderer renderer = new ExcelTemplateRenderer() { public ReportDesign getDesign(String argument) { return design; } }; // We construct an EvaluationContext (in this case the parameters aren't used, but included here for reference) EvaluationContext context = new EvaluationContext(); ReportData data = Context.getService(ReportDefinitionService.class).evaluate(rd, context); MutableMessageSource messageSource = Context.getMessageSourceService().getActiveMessageSource(); messageSource.addPresentation(new PresentationMessage("reporting.test.PID", Locale.ENGLISH, "EMR ID", "")); messageSource.addPresentation( new PresentationMessage("reporting.test.dataset.DOB", Locale.ENGLISH, "Date of Birth", "")); messageSource .addPresentation(new PresentationMessage("reporting.test.PID", Locale.FRENCH, "ID DE EMR", "")); messageSource.addPresentation(new PresentationMessage("reporting.test.GENDER", Locale.FRENCH, "Sexe", "")); messageSource.addPresentation( new PresentationMessage("reporting.test.dataset.DOB", Locale.FRENCH, "Date de naissance", "")); ByteArrayOutputStream reportBaos = new ByteArrayOutputStream(1024); renderer.render(data, "xxx:xls", reportBaos); IOUtils.closeQuietly(reportBaos); Workbook wb = ExcelUtil.loadWorkbookFromInputStream(new ByteArrayInputStream(reportBaos.toByteArray())); Sheet sheet = wb.getSheet("TestLabels"); List<String> cellsFound = new ArrayList<String>(); for (Iterator<Row> ri = sheet.rowIterator(); ri.hasNext();) { Row row = ri.next(); for (Iterator<Cell> ci = row.cellIterator(); ci.hasNext();) { Cell cell = ci.next(); Object contents = ExcelUtil.getCellContents(cell); if (!ObjectUtil.isNull(contents)) { cellsFound.add(contents.toString()); } } } Assert.assertEquals(3, cellsFound.size()); Assert.assertEquals(emrIdVal, cellsFound.get(0)); Assert.assertEquals(genderVal, cellsFound.get(1)); Assert.assertEquals(dobVal, cellsFound.get(2)); }
From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java
License:Open Source License
private void importFromFile(final File file, final boolean firstRowIsHeader) { final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length())); try {/*www . ja v a 2 s . co m*/ final InputStream fin = new FileInputStream(file); try { IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout); } finally { fin.close(); } if (Thread.currentThread().isInterrupted()) { return; } final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray())); int sheetIndex = 0; if (workbook.getNumberOfSheets() > 1) { final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent); if (selectorDialog.performSelection()) { sheetIndex = selectorDialog.getSelectedIndex(); } else { return; } } final TypedTableModel tableModel = new TypedTableModel(); final Sheet sheet = workbook.getSheetAt(sheetIndex); final Iterator rowIterator = sheet.rowIterator(); if (firstRowIsHeader) { if (rowIterator.hasNext()) { final Row headerRow = (Row) rowIterator.next(); final short cellCount = headerRow.getLastCellNum(); for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = headerRow.getCell(colIdx); if (cell != null) { while (colIdx > tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } final RichTextString string = cell.getRichStringCellValue(); if (string != null) { tableModel.addColumn(string.getString(), Object.class); } else { tableModel.addColumn( Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)), Object.class); } } } } } Object[] rowData = null; while (rowIterator.hasNext()) { final Row row = (Row) rowIterator.next(); final short cellCount = row.getLastCellNum(); if (cellCount == -1) { continue; } if (rowData == null || rowData.length != cellCount) { rowData = new Object[cellCount]; } for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = row.getCell(colIdx); final Object value; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { final RichTextString string = cell.getRichStringCellValue(); if (string != null) { value = string.getString(); } else { value = null; } } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { final CellStyle hssfCellStyle = cell.getCellStyle(); final short dataFormat = hssfCellStyle.getDataFormat(); final String dataFormatString = hssfCellStyle.getDataFormatString(); if (isDateFormat(dataFormat, dataFormatString)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { value = cell.getBooleanCellValue(); } else { value = cell.getStringCellValue(); } } else { value = null; } if (value != null && "".equals(value) == false) { while (colIdx >= tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } } rowData[colIdx] = value; } if (Thread.currentThread().isInterrupted()) { return; } tableModel.addRow(rowData); } final int colCount = tableModel.getColumnCount(); final int rowCount = tableModel.getRowCount(); for (int col = 0; col < colCount; col++) { Class type = null; for (int row = 0; row < rowCount; row += 1) { final Object value = tableModel.getValueAt(row, col); if (value == null) { continue; } if (type == null) { type = value.getClass(); } else if (type != Object.class) { if (type.isInstance(value) == false) { type = Object.class; } } } if (Thread.currentThread().isInterrupted()) { return; } if (type != null) { tableModel.setColumnType(col, type); } } parent.importComplete(tableModel); } catch (Exception e) { parent.importFailed(e); logger.error("Failed to import spreadsheet", e); // NON-NLS } }