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.apache.ofbiz.product.spreadsheetimport.ImportProductServices.java

License:Apache License

/**
 * This method is responsible to import spreadsheet data into "Product" and
 * "InventoryItem" entities into database. The method uses the
 * ImportProductHelper class to perform its operation. The method uses "Apache
 * POI" api for importing spreadsheet (xls files) data.
 *
 * Note : Create the spreadsheet directory in the ofbiz home folder and keep
 * your xls files in this folder only./*from   w  w w  . jav  a  2 s  . c  o m*/
 *
 * @param dctx the dispatch context
 * @param context the context
 * @return the result of the service execution
 * @throws IOException 
 */
public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx,
        Map<String, ? extends Object> context) throws IOException {
    Delegator delegator = dctx.getDelegator();
    Locale locale = (Locale) context.get("locale");
    // System.getProperty("user.dir") returns the path upto ofbiz home
    // directory
    String path = System.getProperty("user.dir") + "/spreadsheet";
    List<File> fileItems = new LinkedList<File>();

    if (UtilValidate.isNotEmpty(path)) {
        File importDir = new File(path);
        if (importDir.isDirectory() && importDir.canRead()) {
            File[] files = importDir.listFiles();
            // loop for all the containing xls file in the spreadsheet
            // directory
            for (int i = 0; i < files.length; i++) {
                if (files[i].getName().toUpperCase().endsWith("XLS")) {
                    fileItems.add(files[i]);
                }
            }
        } else {
            return ServiceUtil.returnError(
                    UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale));
        }
    } else {
        return ServiceUtil.returnError(
                UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale));
    }

    if (fileItems.size() < 1) {
        return ServiceUtil.returnError(
                UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale)
                        + path);
    }

    for (File item : fileItems) {
        // read all xls file and create workbook one by one.
        List<Map<String, Object>> products = new LinkedList<Map<String, Object>>();
        List<Map<String, Object>> inventoryItems = new LinkedList<Map<String, Object>>();
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        try {
            fs = new POIFSFileSystem(new FileInputStream(item));
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            Debug.logError("Unable to read or create workbook from file", module);
            return ServiceUtil.returnError(UtilProperties.getMessage(resource,
                    "ProductProductImportCannotCreateWorkbookFromFile", locale));
        }

        // get first sheet
        HSSFSheet sheet = wb.getSheetAt(0);
        wb.close();
        int sheetLastRowNumber = sheet.getLastRowNum();
        for (int j = 1; j <= sheetLastRowNumber; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row != null) {
                // read productId from first column "sheet column index
                // starts from 0"
                HSSFCell cell2 = row.getCell(2);
                cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
                String productId = cell2.getRichStringCellValue().toString();
                // read QOH from ninth column
                HSSFCell cell5 = row.getCell(5);
                BigDecimal quantityOnHand = BigDecimal.ZERO;
                if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                    quantityOnHand = new BigDecimal(cell5.getNumericCellValue());

                // check productId if null then skip creating inventory item
                // too.
                boolean productExists = ImportProductHelper.checkProductExists(productId, delegator);

                if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) {
                    products.add(ImportProductHelper.prepareProduct(productId));
                    if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0)
                        inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand,
                                delegator.getNextSeqId("InventoryItem")));
                    else
                        inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO,
                                delegator.getNextSeqId("InventoryItem")));
                }
                int rowNum = row.getRowNum() + 1;
                if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) {
                    Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module);
                }
            }
        }
        // create and store values in "Product" and "InventoryItem" entity
        // in database
        for (int j = 0; j < products.size(); j++) {
            GenericValue productGV = delegator.makeValue("Product", products.get(j));
            GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j));
            if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) {
                try {
                    delegator.create(productGV);
                    delegator.create(inventoryItemGV);
                } catch (GenericEntityException e) {
                    Debug.logError("Cannot store product", module);
                    return ServiceUtil.returnError(UtilProperties.getMessage(resource,
                            "ProductProductImportCannotStoreProduct", locale));
                }
            }
        }
        int uploadedProducts = products.size() + 1;
        if (products.size() > 0)
            Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module);
    }
    return ServiceUtil.returnSuccess();
}

---

From source file:org.beanfuse.transfer.importer.reader.ExcelItemReader.java

License:Open Source License

public Object read() {
    HSSFSheet sheet = workbook.getSheetAt(sheetNum);
    if (indexInSheet > sheet.getLastRowNum()) {
        return null;
    }//from ww w .  j  ava 2s.  co  m
    HSSFRow row = sheet.getRow(indexInSheet);
    indexInSheet++;
    // ,
    if (row == null) {
        return new Object[attrCount];
    } else {
        Object[] values = new Object[((attrCount != 0) ? attrCount : row.getLastCellNum())];
        for (short k = 0; k < values.length; k++) {
            String celValue = getCelValue(row.getCell(k));
            if (null != celValue) {
                celValue = celValue.trim();
            }
            values[k] = celValue;
        }
        return values;
    }
}

---

From source file:org.beangle.commons.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * <p>/*w w w  . j  a v  a  2  s. c om*/
 * read.
 * </p>
 * 
 * @return a {@link java.lang.Object} object.
 */
public Object read() {
    HSSFSheet sheet = workbook.getSheetAt(sheetNum);
    if (indexInSheet > sheet.getLastRowNum()) {
        return null;
    }
    HSSFRow row = sheet.getRow(indexInSheet);
    indexInSheet++;
    // ,
    if (row == null) {
        return new Object[attrCount];
    } else {
        Object[] values = new Object[((attrCount != 0) ? attrCount : row.getLastCellNum())];
        for (int k = 0; k < values.length; k++) {
            values[k] = getCellValue(row.getCell(k));
        }
        return values;
    }
}

---

From source file:org.beangle.model.transfer.excel.ExcelItemReader.java

License:Open Source License

public Object read() {
    HSSFSheet sheet = workbook.getSheetAt(sheetNum);
    if (indexInSheet > sheet.getLastRowNum()) {
        return null;
    }// w w w.ja v  a  2s .c  o  m
    HSSFRow row = sheet.getRow(indexInSheet);
    indexInSheet++;
    // ,
    if (row == null) {
        return new Object[attrCount];
    } else {
        Object[] values = new Object[((attrCount != 0) ? attrCount : row.getLastCellNum())];
        for (int k = 0; k < values.length; k++) {
            values[k] = getCellValue(row.getCell(k));
        }
        return values;
    }
}

---

From source file:org.databene.formats.xls.BeanXLSWriter.java

License:Open Source License

public void save(E bean) {
    HSSFSheet sheet = getOrCreateSheet(bean, sheetName);
    HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
    for (int i = 0; i < beanProperties.size(); i++) {
        PropFormat prop = beanProperties.get(i);
        Object propValue = PropertyGraphAccessor.getPropertyGraph(prop.getName(), bean);
        render(propValue, row, i);//  ww w .j  a v  a 2s.c o  m
    }
}

---

From source file:org.databene.platform.xls.XLSEntityExporter.java

License:Open Source License

@Override
public void startProductConsumption(Object object) {
    logger.debug("exporting {}", object);
    if (!(object instanceof Entity))
        throw new IllegalArgumentException("Expecting Entity");
    Entity entity = (Entity) object;
    HSSFSheet sheet = getOrCreateSheet(entity);
    HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
    int i = 0;/*from   w w  w  .  ja v  a 2  s .  c o m*/
    for (Map.Entry<String, Object> component : getComponents(entity))
        render(row, i++, component.getValue());
}

---

From source file:org.dbunit.dataset.excel.XlsTable.java

License:Open Source License

public XlsTable(String sheetName, HSSFSheet sheet) throws DataSetException {
    int rowCount = sheet.getLastRowNum();
    if (rowCount >= 0 && sheet.getRow(0) != null) {
        _metaData = createMetaData(sheetName, sheet.getRow(0));
    } else {//from   w w w  .j av  a  2s.  c o m
        _metaData = new DefaultTableMetaData(sheetName, new Column[0]);
    }

    _sheet = sheet;

    // Needed for later "BigDecimal"/"Number" conversion
    symbols.setDecimalSeparator('.');
}

---

From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

@Override
public int getNewId(String fileName) {
    HSSFWorkbook workBook = new HSSFWorkbook();
    try {/*w  w  w  .  jav  a  2  s.  co  m*/
        FileInputStream in = new FileInputStream(fileName);
        workBook = (HSSFWorkbook) WorkbookFactory.create(in);
        in.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    }

    HSSFSheet sheet = workBook.getSheet(DEFAULT_SHEET_NAME);

    return sheet.getLastRowNum() + 1;
}

---

From source file:org.eclipse.titanium.markers.export.ExportedProblemMerger.java

License:Open Source License

/**
 * Collect the smell names contained in a sheet.
 * // w  ww .j a  va  2  s.c o  m
 * @param sheet
 *            The sheet to process
 */
private void collectSmellNames(final HSSFSheet sheet) {
    final int rows = sheet.getLastRowNum();
    int row = 2;
    while (row <= rows) {
        final HSSFRow actualRow = sheet.getRow(row);
        if (actualRow != null) {
            final Cell cell = actualRow.getCell(0);
            final String name = cell.getStringCellValue();
            // new smell found
            if (!smellrow.containsKey(name) && !name.isEmpty()) {
                smellrow.put(name, smellindex);
                smellindex += 1;
            }
        }
        row += 1;
    }
}

---

From source file:org.eclipse.titanium.markers.export.ExportedProblemMerger.java

License:Open Source License

/**
 * Write the smell data.// w  ww .ja  va  2 s . co  m
 * 
 * @param sheet
 *            The sheet from which to read the data
 * @param date
 *            The date which we write now
 * @param col
 *            The next column where the data will be written
 * @throws RowsExceededException
 * @throws WriteException
 */
private void writeSmellData(final HSSFSheet sheet, final Date date, final int col) {
    final int rows = sheet.getLastRowNum();
    final int colinfile = datecol.get(date);
    for (int row = 2; row <= rows; ++row) {
        final HSSFRow actualRow = sheet.getRow(row);
        if (actualRow == null) {
            continue;
        }
        Cell cell = actualRow.getCell(0);
        final String name = cell.getStringCellValue();
        // the number of smells
        cell = actualRow.getCell(colinfile);
        if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
            final double value = cell.getNumericCellValue();
            final Row r = summarysheet.getRow(smellrow.get(name));
            final Cell number = r.createCell(col);
            number.setCellValue(value);
        }
    }
}

---

• «
• 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
• »