Example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum.

Prototype

@Override
public int getLastRowNum() 

Source Link

Document

Gets the number last row on the sheet.

Usage

From source file:org.opentaps.dataimport.ExcelImportServices.java

License:Open Source License

/**
 * Takes each row of an Excel sheet and put it into DataImportProduct.
 * @param sheet the Excel sheet/*  www . j  a v a 2  s . c o m*/
 * @return a <code>Collection</code> of DataImportProduct entities
 * @throws RepositoryException if an error occurs
 */
protected Collection<? extends EntityInterface> createDataImportProducts(HSSFSheet sheet)
        throws RepositoryException {
    int sheetLastRowNumber = sheet.getLastRowNum();
    List<DataImportProduct> products = FastList.newInstance();

    for (int j = 1; j <= sheetLastRowNumber; j++) {
        HSSFRow row = sheet.getRow(j);
        if (isNotEmpty(row)) {
            // row index starts at 0 here but is actually 1 in Excel
            int rowNum = row.getRowNum() + 1;
            // read productId from first column "sheet column index
            // starts from 0"
            String id = readStringCell(row, 0);

            if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("productId")) {
                Debug.logWarning("Row number " + rowNum + " not imported from Products tab: invalid ID value ["
                        + id + "].", MODULE);
                continue;
            }

            DataImportProduct product = new DataImportProduct();
            product.setProductId(id);
            product.setProductName(readStringCell(row, 1));
            product.setInternalName(readStringCell(row, 1));
            product.setProductTypeId(readStringCell(row, 2));
            product.setDescription(readStringCell(row, 3));
            product.setPrice(readBigDecimalCell(row, 4));
            product.setPriceCurrencyUomId(readStringCell(row, 5));
            product.setSupplierPartyId(readStringCell(row, 6));
            product.setPurchasePrice(readBigDecimalCell(row, 7));
            products.add(product);
        }
    }
    return products;
}

---

From source file:org.opentaps.dataimport.ExcelImportServices.java

License:Open Source License

/**
 * Take each row of an Excel sheet and put it into DataImportSupplier.
 * @param sheet the Excel sheet/*  ww w .  jav  a 2s  .  co  m*/
 * @return a <code>Collection</code> of DataImportSupplier entities
 * @throws RepositoryException if an error occurs
 */
protected Collection<? extends EntityInterface> createDataImportSuppliers(HSSFSheet sheet)
        throws RepositoryException {

    List<DataImportSupplier> suppliers = FastList.newInstance();
    int sheetLastRowNumber = sheet.getLastRowNum();
    for (int j = 1; j <= sheetLastRowNumber; j++) {
        HSSFRow row = sheet.getRow(j);
        if (isNotEmpty(row)) {
            // row index starts at 0 here but is actually 1 in Excel
            int rowNum = row.getRowNum() + 1;
            // read supplierId from first column "sheet column index
            // starts from 0"
            String id = readStringCell(row, 0);

            if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("supplierId")) {
                Debug.logWarning("Row number " + rowNum + " not imported from Suppliers tab: invalid ID value ["
                        + id + "].", MODULE);
                continue;
            }

            DataImportSupplier supplier = new DataImportSupplier();
            supplier.setSupplierId(id);
            supplier.setSupplierName(readStringCell(row, 1));
            supplier.setAddress1(readStringCell(row, 2));
            supplier.setAddress2(readStringCell(row, 3));
            supplier.setCity(readStringCell(row, 4));
            supplier.setStateProvinceGeoId(readStringCell(row, 5));
            supplier.setPostalCode(readStringCell(row, 6));
            supplier.setCountryGeoId(readStringCell(row, 7));
            supplier.setPrimaryPhoneCountryCode(readStringCell(row, 8));
            supplier.setPrimaryPhoneAreaCode(readStringCell(row, 9));
            supplier.setPrimaryPhoneNumber(readStringCell(row, 10));
            supplier.setNetPaymentDays(readLongCell(row, 11));
            supplier.setIsIncorporated(readStringCell(row, 12));
            supplier.setFederalTaxId(readStringCell(row, 13));
            supplier.setRequires1099(readStringCell(row, 14));
            suppliers.add(supplier);
        }
    }

    return suppliers;
}

---

From source file:org.opentaps.dataimport.ExcelImportServices.java

License:Open Source License

/**
 * Take each row of an Excel sheet and put it into DataImportCustomer.
 * @param sheet the Excel sheet//from  w  ww. j av a  2  s. c om
 * @return a <code>Collection</code> of DataImportCustomer entities
 * @throws RepositoryException if an error occurs
 */
protected Collection<? extends EntityInterface> createDataImportCustomers(HSSFSheet sheet)
        throws RepositoryException {

    List<DataImportCustomer> customers = FastList.newInstance();
    int sheetLastRowNumber = sheet.getLastRowNum();
    for (int j = 1; j <= sheetLastRowNumber; j++) {
        HSSFRow row = sheet.getRow(j);
        if (isNotEmpty(row)) {
            // row index starts at 0 here but is actually 1 in Excel
            int rowNum = row.getRowNum() + 1;
            // read customerId from first column "sheet column index
            // starts from 0"
            String id = readStringCell(row, 0);

            if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("customerId")) {
                Debug.logWarning("Row number " + rowNum + " not imported from Customers tab: invalid ID value ["
                        + id + "].", MODULE);
                continue;
            }

            DataImportCustomer customer = new DataImportCustomer();
            customer.setCustomerId(id);
            int rowCount = 1; // keep track of the row 
            customer.setCompanyName(this.readStringCell(row, rowCount++));
            customer.setFirstName(this.readStringCell(row, rowCount++));
            customer.setLastName(this.readStringCell(row, rowCount++));
            customer.setAttnName(this.readStringCell(row, rowCount++));
            customer.setAddress1(this.readStringCell(row, rowCount++));
            customer.setAddress2(this.readStringCell(row, rowCount++));
            customer.setCity(this.readStringCell(row, rowCount++));
            customer.setStateProvinceGeoId(this.readStringCell(row, rowCount++));
            customer.setPostalCode(this.readStringCell(row, rowCount++));
            customer.setPostalCodeExt(this.readStringCell(row, rowCount++));
            customer.setCountryGeoId(this.readStringCell(row, rowCount++));
            customer.setPrimaryPhoneCountryCode(this.readStringCell(row, rowCount++));
            customer.setPrimaryPhoneAreaCode(this.readStringCell(row, rowCount++));
            customer.setPrimaryPhoneNumber(this.readStringCell(row, rowCount++));
            customer.setPrimaryPhoneExtension(this.readStringCell(row, rowCount++));
            customer.setSecondaryPhoneCountryCode(this.readStringCell(row, rowCount++));
            customer.setSecondaryPhoneAreaCode(this.readStringCell(row, rowCount++));
            customer.setSecondaryPhoneNumber(this.readStringCell(row, rowCount++));
            customer.setSecondaryPhoneExtension(this.readStringCell(row, rowCount++));
            customer.setFaxCountryCode(this.readStringCell(row, rowCount++));
            customer.setFaxAreaCode(this.readStringCell(row, rowCount++));
            customer.setFaxNumber(this.readStringCell(row, rowCount++));
            customer.setDidCountryCode(this.readStringCell(row, rowCount++));
            customer.setDidAreaCode(this.readStringCell(row, rowCount++));
            customer.setDidNumber(this.readStringCell(row, rowCount++));
            customer.setDidExtension(this.readStringCell(row, rowCount++));
            customer.setEmailAddress(this.readStringCell(row, rowCount++));
            customer.setWebAddress(this.readStringCell(row, rowCount++));
            customer.setDiscount(this.readBigDecimalCell(row, rowCount++));
            customer.setPartyClassificationTypeId(this.readStringCell(row, rowCount++));
            customer.setCreditCardNumber(this.readStringCell(row, rowCount++));
            customer.setCreditCardExpDate(this.readStringCell(row, rowCount++));
            customer.setOutstandingBalance(this.readBigDecimalCell(row, rowCount++));
            customer.setCreditLimit(this.readBigDecimalCell(row, rowCount++));
            customer.setCurrencyUomId(this.readStringCell(row, rowCount++));
            customer.setDisableShipping(this.readStringCell(row, rowCount++));
            customer.setNetPaymentDays(this.readLongCell(row, rowCount++));
            customer.setShipToCompanyName(this.readStringCell(row, rowCount++));
            customer.setShipToFirstName(this.readStringCell(row, rowCount++));
            customer.setShipToLastName(this.readStringCell(row, rowCount++));
            customer.setShipToAttnName(this.readStringCell(row, rowCount++));
            customer.setShipToAddress1(this.readStringCell(row, rowCount++));
            customer.setShipToAddress2(this.readStringCell(row, rowCount++));
            customer.setShipToCity(this.readStringCell(row, rowCount++));
            customer.setShipToStateProvinceGeoId(this.readStringCell(row, rowCount++));
            customer.setShipToPostalCode(this.readStringCell(row, rowCount++));
            customer.setShipToPostalCodeExt(this.readStringCell(row, rowCount++));
            customer.setShipToStateProvGeoName(this.readStringCell(row, rowCount++));
            customer.setShipToCountryGeoId(this.readStringCell(row, rowCount++));
            customer.setNote(this.readStringCell(row, rowCount++));
            customers.add(customer);
        }
    }

    return customers;
}

---

From source file:org.opentaps.dataimport.ExcelImportServices.java

License:Open Source License

/**
 * Take each row of an Excel sheet and put it into DataImportInventory.
 * @param sheet the Excel sheet//from   ww w  .j av a 2  s . co  m
 * @return a <code>Collection</code> of DataImportInventory entities
 * @throws RepositoryException if an error occurs
 */
protected Collection<? extends EntityInterface> createDataImportInventory(HSSFSheet sheet)
        throws RepositoryException {

    List<DataImportInventory> inventory = FastList.newInstance();
    int sheetLastRowNumber = sheet.getLastRowNum();
    for (int j = 1; j <= sheetLastRowNumber; j++) {
        HSSFRow row = sheet.getRow(j);
        if (isNotEmpty(row)) {
            // row index starts at 0 here but is actually 1 in Excel
            int rowNum = row.getRowNum() + 1;
            // read itemId from first column "sheet column index
            // starts from 0"
            String id = readStringCell(row, 0);

            if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("itemId")) {
                Debug.logWarning("Row number " + rowNum + " not imported from Inventory tab: invalid ID value ["
                        + id + "].", MODULE);
                continue;
            }

            DataImportInventory inventoryItem = new DataImportInventory();
            inventoryItem.setItemId(id);
            inventoryItem.setProductId(this.readStringCell(row, 1));
            inventoryItem.setFacilityId(this.readStringCell(row, 2));
            inventoryItem.setAvailableToPromise(this.readBigDecimalCell(row, 3));
            inventoryItem.setOnHand(this.readBigDecimalCell(row, 4));
            inventoryItem.setMinimumStock(this.readBigDecimalCell(row, 5));
            inventoryItem.setReorderQuantity(this.readBigDecimalCell(row, 6));
            inventoryItem.setDaysToShip(this.readBigDecimalCell(row, 7));
            inventoryItem.setInventoryValue(this.readBigDecimalCell(row, 8));
            inventory.add(inventoryItem);
        }
    }

    return inventory;
}

---

From source file:org.opentaps.dataimport.ExcelImportServices.java

License:Open Source License

/**
 * Take each row of an Excel sheet and put it into DataImportGlAccount.
 * @param sheet the Excel sheet/*  w  w  w.  jav a  2s  .com*/
 * @return a <code>Collection</code> of DataImportGlAccount entities
 * @throws RepositoryException if an error occurs
 */
protected Collection<? extends EntityInterface> createDataImportGlAccounts(HSSFSheet sheet)
        throws RepositoryException {

    List<DataImportGlAccount> glAccounts = FastList.newInstance();
    int sheetLastRowNumber = sheet.getLastRowNum();
    for (int j = 1; j <= sheetLastRowNumber; j++) {
        HSSFRow row = sheet.getRow(j);
        if (isNotEmpty(row)) {
            // row index starts at 0 here but is actually 1 in Excel
            int rowNum = row.getRowNum() + 1;
            // read glAccountrId from first column "sheet column index
            // starts from 0"
            String id = readStringCell(row, 0);

            if (UtilValidate.isEmpty(id) || id.indexOf(" ") > -1 || id.equalsIgnoreCase("glAccountId")) {
                Debug.logWarning("Row number " + rowNum
                        + " not imported from GL Accounts tab: invalid ID value [" + id + "].", MODULE);
                continue;
            }

            DataImportGlAccount glAccount = new DataImportGlAccount();
            glAccount.setGlAccountId(id);
            glAccount.setParentGlAccountId(this.readStringCell(row, 1));
            glAccount.setClassification(this.readStringCell(row, 2));
            glAccount.setAccountName(this.readStringCell(row, 3));
            glAccounts.add(glAccount);
        }
    }

    return glAccounts;
}

---

From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java

License:Open Source License

protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config,
        OutputStream outputStream) {
    try {//  ww  w  .  jav a2 s.  co m
        Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA);
        final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null,
                XPathCache.getGlobalConfiguration());

        Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG);

        // Read template sheet
        String templateName = configDocument.getRootElement().attributeValue("template");
        //String fileName = configDocument.getRootElement().attributeValue("filename");
        InputStream templateInputStream = URLFactory.createURL(templateName).openStream();
        final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream));
        final HSSFDataFormat dataFormat = workbook.createDataFormat();
        templateInputStream.close();

        int sheetIndex = 0;

        PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper,
                "/workbook/sheet", getLocationData());
        List<Object> nodes = expr.evaluateToJavaReturnToPool();

        for (Iterator i = nodes.iterator(); i.hasNext();) {

            final Element sheetElement = (Element) i.next();
            HSSFSheet sheet = workbook.cloneSheet(0);
            workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name"));

            // Duplicate rows if we find a "repeat-row" in the config
            for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) {

                // Get info about row to repeat
                Element repeatRowElement = (Element) j.next();
                final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num"));
                final String forEach = repeatRowElement.attributeValue("for-each");
                HSSFRow templateRow = sheet.getRow(rowNum);
                int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue();

                // Move existing rows lower
                int lastRowNum = sheet.getLastRowNum();
                for (int k = lastRowNum; k > rowNum; k--) {
                    HSSFRow sourceRow = sheet.getRow(k);
                    HSSFRow newRow = sheet.createRow(k + repeatCount - 1);
                    XLSUtils.copyRow(workbook, newRow, sourceRow);
                }

                // Create rows, copying the template row
                for (int k = rowNum + 1; k < rowNum + repeatCount; k++) {
                    HSSFRow newRow = sheet.createRow(k);
                    XLSUtils.copyRow(workbook, newRow, templateRow);
                }

                // Modify the XPath expression on each row
                for (int k = rowNum; k < rowNum + repeatCount; k++) {
                    HSSFRow newRow = sheet.getRow(k);
                    for (short m = 0; m <= newRow.getLastCellNum(); m++) {
                        HSSFCell cell = newRow.getCell(m);
                        if (cell != null) {
                            String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat());
                            final Matcher matcher = FORMAT_XPATH.matcher(currentFormat);
                            if (matcher.find()) {
                                String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1)
                                        + "]/" + matcher.group(2) + "\"";
                                cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat));
                            }
                        }
                    }
                }
            }

            // Set values in cells with an XPath expression
            XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() {
                public void cell(HSSFCell cell, String sourceXPath, String targetXPath) {
                    if (sourceXPath.charAt(0) == '/')
                        sourceXPath = sourceXPath.substring(1);

                    // Set cell value
                    PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(),
                            wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData());
                    String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull();

                    if (newValue == null) {
                        throw new OXFException("Nothing matches the XPath expression '" + sourceXPath
                                + "' in the input document");
                    }
                    try {
                        cell.setCellValue(Double.parseDouble(newValue));
                    } catch (NumberFormatException e) {
                        cell.setCellValue(newValue);
                    }

                    // Set cell format
                    Object element = sheetElement.selectObject(sourceXPath);
                    if (element instanceof Element) {
                        // NOTE: We might want to support other properties here
                        String bold = ((Element) element).attributeValue("bold");
                        if (bold != null) {
                            HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex());
                            HSSFFont newFont = workbook.createFont();
                            XLSUtils.copyFont(newFont, originalFont);
                            if ("true".equals(bold))
                                newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                            cell.getCellStyle().setFont(newFont);
                        }
                    }
                }
            });
            sheetIndex++;
        }

        workbook.removeSheetAt(0);

        // Write out the workbook
        workbook.write(outputStream);
    } catch (IOException e) {
        throw new OXFException(e);
    }
}

---

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

public static void walk(HSSFDataFormat dataFormat, HSSFSheet sheet, Handler handler) {

    // Go through cells
    boolean[][] merged = getMergedCells(sheet);
    for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
        HSSFRow row = sheet.getRow(rowNum);
        walk(merged, dataFormat, row, handler);
    }//from  ww w  .java2 s.  co  m
}

---

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

public static boolean[][] getMergedCells(HSSFSheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    short maxCellNum = getMaxCellNum(sheet);

    // Compute merged regions
    boolean[][] merged = new boolean[lastRowNum + 1][maxCellNum + 1];
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        Region region = sheet.getMergedRegionAt(i);
        for (int rowNum = region.getRowFrom(); rowNum <= region.getRowTo(); rowNum++) {
            for (int columnNum = region.getColumnFrom(); columnNum <= region.getColumnTo(); columnNum++) {
                if (rowNum != region.getRowFrom() || columnNum != region.getColumnFrom())
                    merged[rowNum][columnNum] = true;
            }//from   w ww  .  j  a va2s .  c o  m
        }
    }

    return merged;
}

---

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

/**
 * Compute max number of columns/* ww w. ja v  a2  s.c o  m*/
 */
public static short getMaxCellNum(HSSFSheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    short maxCellNum = 0;
    for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
        HSSFRow hssfRow = sheet.getRow(rowNum);
        if (hssfRow != null) {
            short lastCellNum = hssfRow.getLastCellNum();
            if (lastCellNum > maxCellNum)
                maxCellNum = lastCellNum;
        }
    }
    return maxCellNum;
}

---

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

public static void copySheet(HSSFWorkbook workbook, HSSFSheet destination, HSSFSheet source) {

    // Copy column width
    short maxCellNum = getMaxCellNum(source);
    for (short i = 0; i <= maxCellNum; i++) {
        destination.setColumnWidth(i, source.getColumnWidth(i));
    }/* www.j  a va  2 s .c o m*/

    // Copy merged cells
    for (int i = 0; i < source.getNumMergedRegions(); i++) {
        Region region = source.getMergedRegionAt(i);
        destination.addMergedRegion(region);
    }

    // Copy rows
    for (int i = 0; i <= source.getLastRowNum(); i++) {
        HSSFRow sourceRow = source.getRow(i);
        HSSFRow destinationRow = destination.createRow(i);
        copyRow(workbook, destinationRow, sourceRow);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• »