List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum
int getLastRowNum();
From source file:org.opencrx.kernel.portal.wizard.ImportAccountsFromXlsController.java
License:BSD License
/** * Perform the import and generate report. * //w w w . jav a 2 s.co 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.openpythia.utilities.SSUtilities.java
License:Apache License
public static void deleteRow(Sheet sheet, Row rowToDelete) { // if the row contains merged regions, delete them List<Integer> mergedRegionsToDelete = new ArrayList<>(); int numberMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numberMergedRegions; i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() == rowToDelete.getRowNum() && mergedRegion.getLastRow() == rowToDelete.getRowNum()) { // this region is within the row - so mark it for deletion mergedRegionsToDelete.add(i); }/*from w ww.j a va 2 s.c om*/ } // now that we know all regions to delete just do it for (Integer indexToDelete : mergedRegionsToDelete) { sheet.removeMergedRegion(indexToDelete); } int rowIndex = rowToDelete.getRowNum(); // this only removes the content of the row sheet.removeRow(rowToDelete); int lastRowNum = sheet.getLastRowNum(); // shift the rest of the sheet one index down if (rowIndex >= 0 && rowIndex < lastRowNum) { sheet.shiftRows(rowIndex + 1, lastRowNum, -1); } }
From source file:org.opentestsystem.delivery.testreg.upload.ExcelFileAppender.java
License:Open Source License
public InputStream insertAtTop(String text, InputStream inputStream) { try {// w w w. ja v a2 s .c o m 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(""); } }
From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java
License:Open Source License
/** * Iterate rows on a excel spreadsheet./* w w w . ja v a 2 s . c om*/ * * @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.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
public void prepareNextOutputFile() throws KettleException { try {// ww w .j a v a 2 s.c o m // sheet name shouldn't exceed 31 character if (data.realSheetname != null && data.realSheetname.length() > 31) { throw new KettleException( BaseMessages.getString(PKG, "ExcelWriterStep.Exception.MaxSheetName", data.realSheetname)); } // clear style cache int numOfFields = meta.getOutputFields() != null && meta.getOutputFields().length > 0 ? meta.getOutputFields().length : 0; if (numOfFields == 0) { numOfFields = data.inputRowMeta != null ? data.inputRowMeta.size() : 0; } data.clearStyleCache(numOfFields); // build new filename String buildFilename = buildFilename(data.splitnr); data.file = KettleVFS.getFileObject(buildFilename, getTransMeta()); if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.OpeningFile", buildFilename)); } // determine whether existing file must be deleted if (data.file.exists() && data.createNewFile) { if (!data.file.delete()) { if (log.isBasic()) { logBasic(BaseMessages.getString(PKG, "ExcelWriterStep.Log.CouldNotDeleteStaleFile", buildFilename)); } setErrors(1); throw new KettleException("Could not delete stale file " + buildFilename); } } // adding filename to result if (meta.isAddToResultFiles()) { // Add this to the result file names... ResultFile resultFile = new ResultFile(ResultFile.FILE_TYPE_GENERAL, data.file, getTransMeta().getName(), getStepname()); resultFile .setComment("This file was created with an Excel writer step by Pentaho Data Integration"); addResultFile(resultFile); } boolean appendingToSheet = true; // if now no file exists we must create it as indicated by user if (!data.file.exists()) { // if template file is enabled if (meta.isTemplateEnabled()) { // handle template case (must have same format) // ensure extensions match String templateExt = KettleVFS.getFileObject(data.realTemplateFileName).getName() .getExtension(); if (!meta.getExtension().equalsIgnoreCase(templateExt)) { throw new KettleException("Template Format Mismatch: Template has extension: " + templateExt + ", but output file has extension: " + meta.getExtension() + ". Template and output file must share the same format!"); } if (KettleVFS.getFileObject(data.realTemplateFileName).exists()) { // if the template exists just copy the template in place copyFile(KettleVFS.getFileObject(data.realTemplateFileName, getTransMeta()), data.file); } else { // template is missing, log it and get out if (log.isBasic()) { logBasic(BaseMessages.getString(PKG, "ExcelWriterStep.Log.TemplateMissing", data.realTemplateFileName)); } setErrors(1); throw new KettleException("Template file missing: " + data.realTemplateFileName); } } else { // handle fresh file case, just create a fresh workbook Workbook wb = meta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook(); BufferedOutputStreamWithCloseDetection out = new BufferedOutputStreamWithCloseDetection( KettleVFS.getOutputStream(data.file, false)); wb.createSheet(data.realSheetname); wb.write(out); out.close(); wb.close(); } appendingToSheet = false; } // file is guaranteed to be in place now if (meta.getExtension().equalsIgnoreCase("xlsx")) { XSSFWorkbook xssfWorkbook = new XSSFWorkbook(KettleVFS.getInputStream(data.file)); if (meta.isStreamingData() && !meta.isTemplateEnabled()) { data.wb = new SXSSFWorkbook(xssfWorkbook, 100); } else { //Initialize it later after writing header/template because SXSSFWorkbook can't read/rewrite existing data, // only append. data.wb = xssfWorkbook; } } else { data.wb = new HSSFWorkbook(KettleVFS.getInputStream(data.file)); } int existingActiveSheetIndex = data.wb.getActiveSheetIndex(); int replacingSheetAt = -1; if (data.wb.getSheet(data.realSheetname) != null) { // sheet exists, replace or reuse as indicated by user if (data.createNewSheet) { replacingSheetAt = data.wb.getSheetIndex(data.wb.getSheet(data.realSheetname)); data.wb.removeSheetAt(replacingSheetAt); } } // if sheet is now missing, we need to create a new one if (data.wb.getSheet(data.realSheetname) == null) { if (meta.isTemplateSheetEnabled()) { Sheet ts = data.wb.getSheet(data.realTemplateSheetName); // if template sheet is missing, break if (ts == null) { throw new KettleException(BaseMessages.getString(PKG, "ExcelWriterStep.Exception.TemplateNotFound", data.realTemplateSheetName)); } data.sheet = data.wb.cloneSheet(data.wb.getSheetIndex(ts)); data.wb.setSheetName(data.wb.getSheetIndex(data.sheet), data.realSheetname); // unhide sheet in case it was hidden data.wb.setSheetHidden(data.wb.getSheetIndex(data.sheet), false); if (meta.isTemplateSheetHidden()) { data.wb.setSheetHidden(data.wb.getSheetIndex(ts), true); } } else { // no template to use, simply create a new sheet data.sheet = data.wb.createSheet(data.realSheetname); } if (replacingSheetAt > -1) { data.wb.setSheetOrder(data.sheet.getSheetName(), replacingSheetAt); } // preserves active sheet selection in workbook data.wb.setActiveSheet(existingActiveSheetIndex); data.wb.setSelectedTab(existingActiveSheetIndex); appendingToSheet = false; } else { // sheet is there and should be reused data.sheet = data.wb.getSheet(data.realSheetname); } // if use chose to make the current sheet active, do so if (meta.isMakeSheetActive()) { int sheetIndex = data.wb.getSheetIndex(data.sheet); data.wb.setActiveSheet(sheetIndex); data.wb.setSelectedTab(sheetIndex); } // handle write protection if (meta.isSheetProtected()) { protectSheet(data.sheet, data.realPassword); } // starting cell support data.startingRow = 0; data.startingCol = 0; if (!Utils.isEmpty(data.realStartingCell)) { CellReference cellRef = new CellReference(data.realStartingCell); data.startingRow = cellRef.getRow(); data.startingCol = cellRef.getCol(); } data.posX = data.startingCol; data.posY = data.startingRow; // Find last row and append accordingly if (!data.createNewSheet && meta.isAppendLines() && appendingToSheet) { data.posY = 0; if (data.sheet.getPhysicalNumberOfRows() > 0) { data.posY = data.sheet.getLastRowNum(); data.posY++; } } // offset by configured value // Find last row and append accordingly if (!data.createNewSheet && meta.getAppendOffset() != 0 && appendingToSheet) { data.posY += meta.getAppendOffset(); } // may have to write a few empty lines if (!data.createNewSheet && meta.getAppendEmpty() > 0 && appendingToSheet) { for (int i = 0; i < meta.getAppendEmpty(); i++) { openLine(); if (!data.shiftExistingCells || meta.isAppendLines()) { data.posY++; } } } // may have to write a header here if (meta.isHeaderEnabled() && !(!data.createNewSheet && meta.isAppendOmitHeader() && appendingToSheet)) { writeHeader(); } if (meta.isStreamingData() && meta.isTemplateEnabled()) { Sheet templateSheet = ((XSSFWorkbook) data.wb).getSheet(data.realSheetname); int currentRowNum = templateSheet.getLastRowNum(); SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook((XSSFWorkbook) data.wb, 100); Sheet aNewSheet = sxssfWorkbook.getSheet(data.realSheetname); int aNewSheetRowCount = aNewSheet.getLastRowNum(); while (currentRowNum > aNewSheetRowCount) { templateSheet.removeRow(templateSheet.getRow(currentRowNum)); currentRowNum--; } data.wb = sxssfWorkbook; data.sheet = sxssfWorkbook.getSheet(data.realSheetname); } if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.FileOpened", buildFilename)); } // this is the number of the new output file data.splitnr++; } catch (Exception e) { logError("Error opening new file", e); setErrors(1); throw new KettleException(e); } }
From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5180IT.java
License:Open Source License
private void validateExcelSheet(final ByteArrayOutputStream boutSlow, final TableModel data) throws IOException, InvalidFormatException { Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutSlow.toByteArray())); Sheet sheet = workbook.getSheetAt(0); Assert.assertEquals(0, sheet.getFirstRowNum()); Assert.assertEquals(data.getRowCount() - 1, sheet.getLastRowNum()); for (int r = 0; r < data.getRowCount(); r += 1) { Row row = sheet.getRow(r);//from ww w .j av a 2 s.c o m for (int c = 0; c < data.getColumnCount(); c += 1) { Cell cell = row.getCell(c); Object valueAt = data.getValueAt(r, c); if (valueAt == null) { if (cell != null) { // excel cells never return null Assert.assertEquals("", cell.getStringCellValue()); } } else { Assert.assertEquals(valueAt, cell.getStringCellValue()); } } } }
From source file:org.pivot4j.ui.poi.ExcelExporterIT.java
License:Common Public License
/** * @param format/*from ww w .j av a 2 s . com*/ * @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.primefaces.component.export.ExcelExporter.java
License:Open Source License
@Override protected void exportCells(DataTable table, Object document) { Sheet sheet = (Sheet) document; int sheetRowIndex = sheet.getLastRowNum() + 1; Row row = sheet.createRow(sheetRowIndex); for (UIColumn col : table.getColumns()) { if (col instanceof DynamicColumn) { ((DynamicColumn) col).applyStatelessModel(); }//w w w .j a v a2 s . c om if (col.isRendered() && col.isExportable()) { addColumnValue(row, col.getChildren(), col); } } }
From source file:org.primefaces.component.export.ExcelExporter.java
License:Open Source License
protected void addColumnFacets(DataTable table, Sheet sheet, Exporter.ColumnType columnType) { int sheetRowIndex = columnType.equals(Exporter.ColumnType.HEADER) ? 0 : (sheet.getLastRowNum() + 1); Row rowHeader = sheet.createRow(sheetRowIndex); for (UIColumn col : table.getColumns()) { if (col instanceof DynamicColumn) { ((DynamicColumn) col).applyStatelessModel(); }// ww w . jav a 2 s . c om if (col.isRendered() && col.isExportable()) { UIComponent facet = col.getFacet(columnType.facet()); if (facet != null) { addColumnValue(rowHeader, facet); } else { String textValue; switch (columnType) { case HEADER: textValue = col.getHeaderText(); break; case FOOTER: textValue = col.getFooterText(); break; default: textValue = ""; break; } addColumnValue(rowHeader, textValue); } } } }
From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java
License:Apache License
@Override public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle, boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor, MethodExpression postProcessor, boolean subTable) throws IOException { wb = new XSSFWorkbook(); String safeName = WorkbookUtil.createSafeSheetName(filename); Sheet sheet = wb.createSheet(safeName); cellStyle = wb.createCellStyle();/*from ww w . j ava2 s. co m*/ facetStyle = wb.createCellStyle(); titleStyle = wb.createCellStyle(); facetStyleLeftAlign = wb.createCellStyle(); facetStyleCenterAlign = wb.createCellStyle(); facetStyleRightAlign = wb.createCellStyle(); cellStyleLeftAlign = wb.createCellStyle(); cellStyleCenterAlign = wb.createCellStyle(); cellStyleRightAlign = wb.createCellStyle(); createCustomFonts(); int maxColumns = 0; StringTokenizer st = new StringTokenizer(tableId, ","); while (st.hasMoreElements()) { String tableName = (String) st.nextElement(); UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(), tableName); if (component == null) { throw new FacesException("Cannot find component \"" + tableName + "\" in view."); } if (!(component instanceof DataTable || component instanceof DataList)) { throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName() + "\", exporter must target a PrimeFaces DataTable/DataList."); } DataList list = null; DataTable table = null; int cols = 0; if (preProcessor != null) { preProcessor.invoke(context.getELContext(), new Object[] { wb }); } if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) { Row titleRow = sheet.createRow(sheet.getLastRowNum()); int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum(); Cell cell = titleRow.createCell(cellIndex); cell.setCellValue(new XSSFRichTextString(tableTitle)); Font titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleStyle.setFont(titleFont); cell.setCellStyle(titleStyle); sheet.createRow(sheet.getLastRowNum() + 3); } if (component instanceof DataList) { list = (DataList) component; if (list.getHeader() != null) { tableFacet(context, sheet, list, "header"); } if (pageOnly) { exportPageOnly(context, list, sheet); } else { exportAll(context, list, sheet); } cols = list.getRowCount(); } else { table = (DataTable) component; int columnsCount = getColumnsCount(table); if (table.getHeader() != null && !subTable) { tableFacet(context, sheet, table, columnsCount, "header"); } if (!subTable) { tableColumnGroup(sheet, table, "header"); } addColumnFacets(table, sheet, ColumnType.HEADER); if (pageOnly) { exportPageOnly(context, table, sheet); } else if (selectionOnly) { exportSelectionOnly(context, table, sheet); } else { exportAll(context, table, sheet, subTable); } if (table.hasFooterColumn() && !subTable) { addColumnFacets(table, sheet, ColumnType.FOOTER); } if (!subTable) { tableColumnGroup(sheet, table, "footer"); } table.setRowIndex(-1); if (postProcessor != null) { postProcessor.invoke(context.getELContext(), new Object[] { wb }); } cols = table.getColumnsCount(); if (maxColumns < cols) { maxColumns = cols; } } sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding)); } if (!subTable) for (int i = 0; i < maxColumns; i++) { sheet.autoSizeColumn((short) i); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); sheet.setPrintGridlines(true); writeExcelToResponse(context.getExternalContext(), wb, filename); }