List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:org.ojbc.adapters.analyticsstaging.custody.service.DescriptionCodeLookupFromExcelService.java
License:RPL License
private void loadMapOfCodeMaps(String codeTableExcelFilePath) throws FileNotFoundException, IOException { log.info("Recache code table maps."); mapOfCodeMaps = new HashMap<String, Map<String, Integer>>(); FileInputStream inputStream = new FileInputStream(new File(codeTableExcelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Map<String, Integer> codePkMap = new HashMap<String, Integer>(); for (int j = 1; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j);/*from w w w. ja v a2 s. c o m*/ if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) { row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING); } String codeOrDescription = StringUtils .upperCase(row.getCell(row.getLastCellNum() - 1).getStringCellValue()); Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue(); codePkMap.put(codeOrDescription, pkId); } mapOfCodeMaps.put(sheet.getSheetName(), codePkMap); } workbook.close(); inputStream.close(); }
From source file:org.ojbc.adapters.analyticsstaging.custody.service.SimpleExcelReaderExample.java
License:RPL License
public static void main(String[] args) throws IOException { Map<String, Map<String, Integer>> mapOfCodeMaps = new HashMap<String, Map<String, Integer>>(); String excelFilePath = "src/test/resources/codeSpreadSheets/PimaCountyAnalyticsCodeTables.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); System.out.println("Sheet Name: " + sheet.getSheetName()); Map<String, Integer> codePkMap = new HashMap<String, Integer>(); for (int j = 1; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j);//from w w w . j a v a 2s .c o m if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) { row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING); } String codeOrDescription = row.getCell(row.getLastCellNum() - 1).getStringCellValue(); Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue(); codePkMap.put(codeOrDescription, pkId); } mapOfCodeMaps.put(sheet.getSheetName(), codePkMap); } workbook.close(); inputStream.close(); }
From source file:org.ojbc.adapters.analyticsstaging.custody.service.SqlScriptFromExcelGenerator.java
License:RPL License
private static void generatePolulateCodeTableScript(String sqlScriptPath, String excelFilePath, boolean isSqlServerInsert) throws FileNotFoundException, IOException { Path adamsSqlPath = Paths.get(sqlScriptPath); FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); StringBuilder sb = new StringBuilder(); sb.append("/*\n "//from w w w .j ava2 s . c o m + "* Unless explicitly acquired and licensed from Licensor under another license, the contents of\n " + "* this file are subject to the Reciprocal Public License (\"RPL\") Version 1.5, or subsequent\n " + "* versions as allowed by the RPL, and You may not copy or use this file in either source code\n " + "* or executable form, except in compliance with the terms and conditions of the RPL\n " + "* \n " + "* All software distributed under the RPL is provided strictly on an \"AS IS\" basis, WITHOUT\n " + "* WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, AND LICENSOR HEREBY DISCLAIMS ALL SUCH\n " + "* WARRANTIES, INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY, FITNESS FOR A\n " + "* PARTICULAR PURPOSE, QUIET ENJOYMENT, OR NON-INFRINGEMENT. See the RPL for specific language\n " + "* governing rights and limitations under the RPL.\n " + "*\n " + "* http://opensource.org/licenses/RPL-1.5\n " + "*\n " + "* Copyright 2012-2015 Open Justice Broker Consortium\n " + "*/\n"); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (isSqlServerInsert) { sb.append("SET IDENTITY_INSERT dbo." + sheet.getSheetName() + " ON;\n"); } String idColumnName = sheet.getRow(0).getCell(0).getStringCellValue(); String descriptionColumnName = sheet.getRow(0).getCell(1).getStringCellValue(); System.out.println("descriptionColumnName: " + descriptionColumnName); String baseString = "insert into " + sheet.getSheetName() + " (" + idColumnName + ", " + descriptionColumnName + ") values ("; for (int j = 1; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); String description = row.getCell(1).getStringCellValue(); Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue(); String insertString = baseString + "'" + pkId + "', " + "'" + description.replace("'", "''") + "');\n"; sb.append(insertString); } if (isSqlServerInsert) { sb.append("SET IDENTITY_INSERT dbo." + sheet.getSheetName() + " OFF;\n"); } } workbook.close(); inputStream.close(); try (BufferedWriter writer = Files.newBufferedWriter(adamsSqlPath)) { writer.write(sb.toString()); } System.out.println("Sql script " + sqlScriptPath + " generated. "); }
From source file:org.opencrx.kernel.portal.wizard.ImportAccountsFromXlsController.java
License:BSD License
/** * Perform the import and generate report. * /*from w ww.java 2s. 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.opentestsystem.delivery.testreg.upload.ExcelUtils.java
License:Open Source License
/** * Iterate rows on a excel spreadsheet./*from w w w. j av a2s.co m*/ * * @param sheet * Sheet to iterate over * @param rowMapper * A mapper to the rows on the sheet. * @param skipHeader * If the sheet's header needed to be skipped. */ public void iterateRows(final Sheet sheet, final ExcelRowMapper rowMapper, final boolean skipHeader, final boolean isPreview) { // Using for loop here instead of Iterator because iterator is skipping blank rows for (int i = 0; i <= sheet.getLastRowNum(); i++) { if (!skipHeader) { final Row row = sheet.getRow(i); if (row == null && i < 2 && !isPreview) { throw new LocalizedException("file.row.empty", new String[] { String.valueOf(i + 1), sheet.getSheetName() }); } // do not process if empty row is the last row final String[] records = getRecordsWithNullRowsAsBlank(row); if (i == sheet.getLastRowNum() && this.ignoreEmptyRows && isEmptyRecord(records)) { continue; } else { final boolean continueMapping = rowMapper.mapRow(row); if (!continueMapping) { break; } } } } }
From source file:org.paxml.bean.excel.ReadExcelTag.java
License:Open Source License
private Iterator doBasic(Context context) throws Exception { return new Iterator() { private Iterator<Row> it; private int index; private Map<Integer, String> headers = new HashMap<Integer, String>(); private void start() { boolean ok = false; try { Sheet s = getExcelSheet(false); it = s.iterator();//from w w w. ja v a2 s .c o m // find the start row if (log.isDebugEnabled()) { log.debug("Start reading from row " + Math.max(1, firstRow) + " of sheet: " + s.getSheetName()); } for (int i = 1; i < firstRow && it.hasNext(); i++) { it.next(); index++; } ok = true; } finally { if (!ok) { end(); } } } private void end() { it = null; file.close(); } @Override public boolean hasNext() { if (it == null) { start(); } if (lastRow > 0 && index > lastRow - 1) { end(); return false; } try { boolean has = it.hasNext(); if (!has) { end(); } return has; } catch (Exception e) { end(); throw new PaxmlRuntimeException(e); } } @Override public Object next() { try { Row row = it.next(); Object r = readRow(row); index++; return r; } catch (Exception e) { end(); throw new PaxmlRuntimeException(e); } } @Override public void remove() { throw new UnsupportedOperationException(); } private Map<Object, Object> readRow(Row row) { final int firstCell = Math.max(row.getFirstCellNum(), _firstColumn); final int lastCell = _lastColumn < 0 ? row.getLastCellNum() - 1 : Math.min(row.getLastCellNum() - 1, _lastColumn); if (log.isDebugEnabled()) { log.debug("Reading cells: " + new CellReference(index, firstCell).formatAsString() + ":" + new CellReference(index, lastCell).formatAsString()); } Map<Object, Object> result = new LinkedHashMap<Object, Object>(); for (int i = firstCell; i <= lastCell; i++) { Cell cell = row.getCell(i); if (cell != null) { Object value = file.getCellValue(cell); // dual keys for the same value result.put(i, value); String key = headers.get(i); if (key == null) { key = new CellReference(-1, i).formatAsString(); headers.put(i, key); } result.put(key, value); } } return result; } }; }
From source file:org.pentaho.di.trans.steps.excelinput.poi.PoiWorkbook.java
License:Apache License
public String getSheetName(int sheetNr) { Sheet sheet = (Sheet) getSheet(sheetNr); if (sheet == null) { return null; }//from w ww. j ava2 s. c o m return sheet.getSheetName(); }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_FormulaRecalculationTest.java
License:Apache License
@Test public void forcesToRecalculate_Hssf() throws Exception { data.wb = new HSSFWorkbook(); data.wb.createSheet("sheet1"); data.wb.createSheet("sheet2"); step.recalculateAllWorkbookFormulas(); if (!data.wb.getForceFormulaRecalculation()) { int sheets = data.wb.getNumberOfSheets(); for (int i = 0; i < sheets; i++) { Sheet sheet = data.wb.getSheetAt(i); assertTrue("Sheet #" + i + ": " + sheet.getSheetName(), sheet.getForceFormulaRecalculation()); }/* ww w . jav a 2 s .c o m*/ } }
From source file:org.pivot4j.ui.poi.ExcelExporterIT.java
License:Common Public License
/** * @param format/*from www . j a v a 2s . c om*/ * @param showParentMember * @param showDimensionTitle * @param hideSpans * @param rows * @param mergedRegions * @throws IOException * @throws InvalidFormatException */ protected void testExport(Format format, boolean showParentMember, boolean showDimensionTitle, boolean hideSpans, int rows, int mergedRegions) throws IOException, InvalidFormatException { OutputStream out = null; File file = File.createTempFile("pivot4j-", "." + format.getExtension()); if (deleteTestFile) { file.deleteOnExit(); } try { out = new FileOutputStream(file); TableRenderer renderer = new TableRenderer(); renderer.setShowParentMembers(showParentMember); renderer.setShowDimensionTitle(showDimensionTitle); renderer.setHideSpans(hideSpans); ExcelExporter exporter = new ExcelExporter(out); exporter.setFormat(format); renderer.render(getPivotModel(), exporter); } finally { out.flush(); IOUtils.closeQuietly(out); } Workbook workbook = WorkbookFactory.create(file); assertThat("Workbook cannot be null.", workbook, is(notNullValue())); Sheet sheet = workbook.getSheetAt(0); assertThat("Worksheet cannot be null.", sheet, is(notNullValue())); assertThat("Invalid worksheet name.", sheet.getSheetName(), is(equalTo("Sales"))); assertThat("Wrong number of rows.", sheet.getLastRowNum(), is(equalTo(rows))); assertThat("Wrong number of merged regions.", sheet.getNumMergedRegions(), is(equalTo(mergedRegions))); }
From source file:org.projectforge.excel.ExportWorkbook.java
License:Open Source License
/** * Clones the current sheet.//from w ww. j av a 2 s. com * * @see Workbook#cloneSheet(int) */ public ExportSheet cloneSheet(final int sheetNum, final String name) { final ExportSheet originSheet = getSheet(sheetNum); final Sheet poiSheet = this.poiWorkbook.cloneSheet(sheetNum); this.poiWorkbook.setSheetName(sheets.size(), name); ContentProvider cp = getContentProvider(); if (contentProvider != null) { cp = contentProvider; } else { cp = ExportConfig.getInstance().createNewContentProvider(this); } final ExportSheet sheet = new ExportSheet(cp, poiSheet.getSheetName(), poiSheet); sheet.setImported(originSheet.isImported()); sheets.add(sheet); return sheet; }