Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getRowNum.

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:org.matonto.etl.rest.impl.DelimitedRestImpl.java

License:Open Source License

/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String./*w  w w .java 2  s.  co m*/
 *
 * @param input the Excel file to convert into JSON
 * @param numRows the number of rows from the Excel file to convert
 * @return a string with the JSON of the Excel rows
 * @throws IOException excel file could not be read
 * @throws InvalidFormatException file is not in a valid excel format
 */
private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException {
    Workbook wb = WorkbookFactory.create(input);
    // Only support single sheet files for now
    Sheet sheet = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    JSONArray rowList = new JSONArray();
    String[] columns;
    for (Row row : sheet) {
        if (row.getRowNum() <= numRows) {
            columns = new String[row.getPhysicalNumberOfCells()];
            int index = 0;
            for (Cell cell : row) {
                columns[index] = df.formatCellValue(cell);
                index++;
            }
            rowList.add(columns);
        }
    }

    return rowList.toString();
}

From source file:org.matonto.etl.service.delimited.DelimitedConverterImpl.java

License:Open Source License

@Override
public Model convert(ExcelConfig config) throws IOException, MatOntoException {
    String[] nextRow;//from w w  w.ja va 2  s.  co m
    Model convertedRDF = modelFactory.createModel();
    ArrayList<ClassMapping> classMappings = parseClassMappings(config.getMapping());

    try {
        Workbook wb = WorkbookFactory.create(config.getData());
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        boolean containsHeaders = config.getContainsHeaders();
        long offset = config.getOffset();
        Optional<Long> limit = config.getLimit();

        //Traverse each row and convert column into RDF
        for (Row row : sheet) {
            // If headers exist or the row is before the offset point, skip the row
            if ((containsHeaders && row.getRowNum() == 0)
                    || row.getRowNum() - (containsHeaders ? 1 : 0) < offset
                    || (limit.isPresent() && row.getRowNum() >= limit.get() + offset)) {
                continue;
            }
            nextRow = new String[row.getPhysicalNumberOfCells()];
            int cellIndex = 0;
            for (Cell cell : row) {
                nextRow[cellIndex] = df.formatCellValue(cell);
                cellIndex++;
            }
            writeClassMappingsToModel(convertedRDF, nextRow, classMappings);
        }
    } catch (InvalidFormatException e) {
        throw new MatOntoException(e);
    }

    return convertedRDF;
}

From source file:org.meveo.service.catalog.impl.PricePlanMatrixService.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void importExcelLine(Row row, User user, Provider provider) throws BusinessException {
    EntityManager em = getEntityManager();
    Object[] cellsObj = IteratorUtils.toArray(row.cellIterator());
    int rowIndex = row.getRowNum();
    int i = 0;/*from  w w  w.  j ava 2  s .com*/
    String pricePlanCode = getCellAsString((Cell) cellsObj[i++]);

    PricePlanMatrix pricePlan = null;
    QueryBuilder qb = new QueryBuilder(PricePlanMatrix.class, "p");
    qb.addCriterion("code", "=", pricePlanCode, false);
    qb.addCriterionEntity("provider", provider);
    @SuppressWarnings("unchecked")
    List<PricePlanMatrix> pricePlans = qb.getQuery(em).getResultList();

    if (pricePlans == null || pricePlans.size() == 0) {
        pricePlan = new PricePlanMatrix();
        pricePlan.setProvider(provider);
        pricePlan.setAuditable(new Auditable());
        pricePlan.getAuditable().setCreated(new Date());
        pricePlan.getAuditable().setCreator(user);
    } else if (pricePlans.size() == 1) {
        pricePlan = pricePlans.get(0);
    } else {
        throw new BusinessException(
                "More than one priceplan in line=" + rowIndex + "with code=" + pricePlanCode);
    }

    String pricePlanDescription = getCellAsString((Cell) cellsObj[i++]);
    String eventCode = getCellAsString((Cell) cellsObj[i++]);
    String sellerCode = getCellAsString((Cell) cellsObj[i++]);
    String countryCode = getCellAsString((Cell) cellsObj[i++]);
    String currencyCode = getCellAsString((Cell) cellsObj[i++]);
    try {
        pricePlan.setStartSubscriptionDate(getCellAsDate((Cell) cellsObj[i++]));
    } catch (Exception e) {
        throw new BusinessException("Invalid startAppli in line=" + rowIndex + " expected format:"
                + param.getProperty("excelImport.dateFormat", "dd/MM/yyyy")
                + ", you may change the property excelImport.dateFormat.");
    }
    try {
        pricePlan.setEndSubscriptionDate(getCellAsDate((Cell) cellsObj[i++]));
    } catch (Exception e) {
        throw new BusinessException("Invalid endAppli in line=" + rowIndex + " expected format:"
                + param.getProperty("excelImport.dateFormat", "dd/MM/yyyy")
                + ", you may change the property excelImport.dateFormat.");
    }
    String offerCode = getCellAsString((Cell) cellsObj[i++]);
    String priority = getCellAsString((Cell) cellsObj[i++]);
    String amountWOTax = getCellAsString((Cell) cellsObj[i++]);
    String amountWithTax = getCellAsString((Cell) cellsObj[i++]);
    String amountWOTaxEL = getCellAsString((Cell) cellsObj[i++]);
    String amountWithTaxEL = getCellAsString((Cell) cellsObj[i++]);
    String minQuantity = getCellAsString((Cell) cellsObj[i++]);
    String maxQuantity = getCellAsString((Cell) cellsObj[i++]);
    String criteria1 = getCellAsString((Cell) cellsObj[i++]);
    String criteria2 = getCellAsString((Cell) cellsObj[i++]);
    String criteria3 = getCellAsString((Cell) cellsObj[i++]);
    String criteriaEL = getCellAsString((Cell) cellsObj[i++]);
    try {
        pricePlan.setStartRatingDate(getCellAsDate((Cell) cellsObj[i++]));
    } catch (Exception e) {
        throw new BusinessException("Invalid startRating in line=" + rowIndex + " expected format:"
                + param.getProperty("excelImport.dateFormat", "dd/MM/yyyy")
                + ", you may change the property excelImport.dateFormat.");
    }
    try {
        pricePlan.setEndRatingDate(getCellAsDate((Cell) cellsObj[i++]));
    } catch (Exception e) {
        throw new BusinessException("Invalid endRating in line=" + rowIndex + " expected format:"
                + param.getProperty("excelImport.dateFormat", "dd/MM/yyyy")
                + ", you may change the property excelImport.dateFormat.");
    }
    String minSubAge = getCellAsString((Cell) cellsObj[i++]);
    String maxSubAge = getCellAsString((Cell) cellsObj[i++]);
    String validityCalendarCode = getCellAsString((Cell) cellsObj[i++]);
    log.debug(
            "priceplanCode={}, priceplanDescription= {}, chargeCode={} sellerCode={}, countryCode={}, currencyCode={},"
                    + " startSub={}, endSub={}, offerCode={}, priority={}, amountWOTax={}, amountWithTax={},amountWOTaxEL={}, amountWithTaxEL={},"
                    + " minQuantity={}, maxQuantity={}, criteria1={}, criteria2={}, criteria3={}, criteriaEL={},"
                    + " startRating={}, endRating={}, minSubAge={}, maxSubAge={}, validityCalendarCode={}",
            new Object[] { pricePlanCode, pricePlanDescription, eventCode, sellerCode, countryCode,
                    currencyCode, pricePlan.getStartSubscriptionDate(), pricePlan.getEndSubscriptionDate(),
                    offerCode, priority, amountWOTax, amountWithTax, amountWOTaxEL, amountWithTaxEL,
                    minQuantity, maxQuantity, criteria1, criteria2, criteria3, criteriaEL,
                    pricePlan.getStartRatingDate(), pricePlan.getEndRatingDate(), minSubAge, maxSubAge,
                    validityCalendarCode });

    if (!StringUtils.isBlank(eventCode)) {
        qb = new QueryBuilder(ChargeTemplate.class, "p");
        qb.addCriterion("code", "=", eventCode, false);
        qb.addCriterionEntity("provider", provider);
        @SuppressWarnings("unchecked")
        List<Seller> charges = qb.getQuery(em).getResultList();
        if (charges.size() == 0) {
            throw new BusinessException("cannot find charge in line=" + rowIndex + " with code=" + eventCode);
        } else if (charges.size() > 1) {
            throw new BusinessException("more than one charge in line=" + rowIndex + " with code=" + eventCode);
        }
        pricePlan.setEventCode(eventCode);
    } else {
        throw new BusinessException("Empty chargeCode in line=" + rowIndex + ", code=" + eventCode);
    }

    // Seller
    if (!StringUtils.isBlank(sellerCode)) {
        qb = new QueryBuilder(Seller.class, "p");
        qb.addCriterion("code", "=", sellerCode, false);
        qb.addCriterionEntity("provider", provider);
        @SuppressWarnings("unchecked")
        List<Seller> sellers = qb.getQuery(em).getResultList();
        Seller seller = null;

        if (sellers == null || sellers.size() == 0) {
            throw new BusinessException("Invalid seller in line=" + rowIndex + ", code=" + sellerCode);
        }

        seller = sellers.get(0);
        pricePlan.setSeller(seller);
    } else {
        pricePlan.setSeller(null);
    }

    // Country
    if (!StringUtils.isBlank(countryCode)) {
        qb = new QueryBuilder(TradingCountry.class, "p");
        qb.addCriterion("p.country.countryCode", "=", countryCode, false);
        qb.addCriterionEntity("provider", provider);
        @SuppressWarnings("unchecked")
        List<TradingCountry> countries = qb.getQuery(em).getResultList();
        TradingCountry tradingCountry = null;

        if (countries == null || countries.size() == 0) {
            throw new BusinessException("Invalid country in line=" + rowIndex + ", code=" + countryCode);
        }

        tradingCountry = countries.get(0);
        pricePlan.setTradingCountry(tradingCountry);
    } else {
        pricePlan.setTradingCountry(null);
    }

    // Currency
    if (!StringUtils.isBlank(currencyCode)) {
        qb = new QueryBuilder(TradingCurrency.class, "p");
        qb.addCriterion("p.currency.currencyCode", "=", currencyCode, false);
        qb.addCriterionEntity("provider", provider);
        @SuppressWarnings("unchecked")
        List<TradingCurrency> currencies = qb.getQuery(em).getResultList();
        TradingCurrency tradingCurrency = null;

        if (currencies == null || currencies.size() == 0) {
            throw new BusinessException("Invalid currency in line=" + rowIndex + ", code=" + countryCode);
        }

        tradingCurrency = currencies.get(0);
        pricePlan.setTradingCurrency(tradingCurrency);
    } else {
        pricePlan.setTradingCurrency(null);
    }

    if (!StringUtils.isBlank(pricePlanCode)) {
        pricePlan.setCode(pricePlanCode);
    } else {
        throw new BusinessException("Invalid priceplan code in line=" + rowIndex + ", code=" + offerCode);
    }

    if (!StringUtils.isBlank(pricePlanDescription)) {
        pricePlan.setDescription(pricePlanDescription);
    } else {
        pricePlan.setDescription(pricePlanCode);
    }

    // OfferCode
    if (!StringUtils.isBlank(offerCode)) {
        qb = new QueryBuilder(OfferTemplate.class, "p");
        qb.addCriterion("code", "=", offerCode, false);
        qb.addCriterionEntity("provider", provider);
        @SuppressWarnings("unchecked")
        List<OfferTemplate> offers = qb.getQuery(em).getResultList();
        OfferTemplate offer = null;

        if (offers == null || offers.size() == 0) {
            throw new BusinessException("Invalid offer code in line=" + rowIndex + ", code=" + offerCode);
        }

        offer = offers.get(0);
        pricePlan.setOfferTemplate(offer);
    } else {
        pricePlan.setOfferTemplate(null);
    }

    if (!StringUtils.isBlank(validityCalendarCode)) {
        qb = new QueryBuilder(Calendar.class, "p");
        qb.addCriterion("code", "=", validityCalendarCode, false);
        qb.addCriterionEntity("provider", provider);
        @SuppressWarnings("unchecked")
        List<Calendar> calendars = qb.getQuery(em).getResultList();
        Calendar calendar = null;

        if (calendars == null || calendars.size() == 0) {
            throw new BusinessException(
                    "Invalid calendars code in line=" + rowIndex + ", code=" + validityCalendarCode);
        }

        calendar = calendars.get(0);
        pricePlan.setValidityCalendar(calendar);
    } else {
        pricePlan.setValidityCalendar(null);
    }

    // Priority
    if (!StringUtils.isBlank(priority)) {
        try {
            pricePlan.setPriority(Integer.parseInt(priority));
        } catch (Exception e) {
            throw new BusinessException("Invalid priority in line=" + rowIndex + ", priority=" + priority);
        }
    } else {
        pricePlan.setPriority(1);
    }

    // AmountWOTax
    if (!StringUtils.isBlank(amountWOTax)) {
        try {
            pricePlan.setAmountWithoutTax(new BigDecimal(amountWOTax));
        } catch (Exception e) {
            throw new BusinessException(
                    "Invalid amount wo tax in line=" + rowIndex + ", amountWOTax=" + amountWOTax);
        }
    } else {
        throw new BusinessException("Amount wo tax in line=" + rowIndex + " should not be empty");
    }

    // AmountWithTax
    if (!StringUtils.isBlank(amountWithTax)) {
        try {
            pricePlan.setAmountWithTax(new BigDecimal(amountWithTax));
        } catch (Exception e) {
            throw new BusinessException(
                    "Invalid amount wo tax in line=" + rowIndex + ", amountWithTax=" + amountWithTax);
        }
    } else {
        pricePlan.setAmountWithTax(null);
    }

    if (!StringUtils.isBlank(amountWOTaxEL)) {
        pricePlan.setAmountWithoutTaxEL(amountWOTaxEL);
    } else {
        pricePlan.setAmountWithoutTaxEL(null);
    }

    if (!StringUtils.isBlank(amountWithTaxEL)) {
        pricePlan.setAmountWithTaxEL(amountWithTaxEL);
    } else {
        pricePlan.setAmountWithTaxEL(null);
    }
    // minQuantity
    if (!StringUtils.isBlank(minQuantity)) {
        try {
            pricePlan.setMinQuantity(new BigDecimal(minQuantity));
        } catch (Exception e) {
            throw new BusinessException(
                    "Invalid minQuantity in line=" + rowIndex + ", minQuantity=" + minQuantity);
        }
    } else {
        pricePlan.setMinQuantity(null);
    }

    // maxQuantity
    if (!StringUtils.isBlank(maxQuantity)) {
        try {
            pricePlan.setMaxQuantity(new BigDecimal(maxSubAge));
        } catch (Exception e) {
            throw new BusinessException(
                    "Invalid maxQuantity in line=" + rowIndex + ", maxQuantity=" + maxQuantity);
        }
    } else {
        pricePlan.setMaxQuantity(null);
    }

    // Criteria1
    if (!StringUtils.isBlank(criteria1)) {
        try {
            pricePlan.setCriteria1Value(criteria1);
        } catch (Exception e) {
            throw new BusinessException("Invalid criteria1 in line=" + rowIndex + ", criteria1=" + criteria1);
        }
    } else {
        pricePlan.setCriteria1Value(null);
    }

    // Criteria2
    if (!StringUtils.isBlank(criteria2)) {
        try {
            pricePlan.setCriteria2Value(criteria2);
        } catch (Exception e) {
            throw new BusinessException("Invalid criteria2 in line=" + rowIndex + ", criteria2=" + criteria2);
        }
    } else {
        pricePlan.setCriteria2Value(null);
    }

    // Criteria3
    if (!StringUtils.isBlank(criteria3)) {
        try {
            pricePlan.setCriteria3Value(criteria3);
        } catch (Exception e) {
            throw new BusinessException("Invalid criteria3 in line=" + rowIndex + ", criteria3=" + criteria3);
        }
    } else {
        pricePlan.setCriteria3Value(null);
    }

    // CriteriaEL
    if (!StringUtils.isBlank(criteriaEL)) {
        try {
            pricePlan.setCriteriaEL(criteriaEL);
            ;
        } catch (Exception e) {
            throw new BusinessException(
                    "Invalid criteriaEL in line=" + rowIndex + ", criteriaEL=" + criteriaEL);
        }
    } else {
        pricePlan.setCriteriaEL(null);
    }

    // minSubAge
    if (!StringUtils.isBlank(minSubAge)) {
        try {
            pricePlan.setMinSubscriptionAgeInMonth(Long.parseLong(minSubAge));
        } catch (Exception e) {
            throw new BusinessException("Invalid minSubAge in line=" + rowIndex + ", minSubAge=" + minSubAge);
        }
    } else {
        pricePlan.setMinSubscriptionAgeInMonth(0L);
    }

    // maxSubAge
    if (!StringUtils.isBlank(maxSubAge)) {
        try {
            pricePlan.setMaxSubscriptionAgeInMonth(Long.parseLong(maxSubAge));
        } catch (Exception e) {
            throw new BusinessException("Invalid maxSubAge in line=" + rowIndex + ", maxSubAge=" + maxSubAge);
        }
    } else {
        pricePlan.setMaxSubscriptionAgeInMonth(9999L);
    }

    if (pricePlan.getId() == null) {
        create(pricePlan, user);
    } else {
        pricePlan.updateAudit(user);
        updateNoCheck(pricePlan);
    }
}

From source file:org.natica.expense.ExpenseUtility.java

public List<Expense> parseExcel(File file) throws IOException, ExpenseExcelFormatException {
    List<Expense> expenses = new ArrayList<Expense>();
    FileInputStream fis;//from  ww w. j  a  v a 2 s. c  o m

    fis = new FileInputStream(file);
    XSSFWorkbook wb;

    wb = new XSSFWorkbook(fis);
    XSSFSheet sh = wb.getSheetAt(0);

    for (Row row : sh) {
        if (row.getRowNum() == 0) {
            if (!checkHeaderRow(sh.getRow(0)))
                throw new ExpenseExcelFormatException("Excel Balk simleri Hataldr.");
            else
                continue;
        }
        Expense e = new Expense();
        for (Cell cell : row) {
            if (cell.getColumnIndex() == 0) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    if (!HSSFDateUtil.isCellDateFormatted(cell)) {
                        throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum()
                                + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi tarih deil");
                    }
                } else {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi tarih deil");
                }
                e.setExpenseEntryDate(cell.getDateCellValue());
            } else if (cell.getColumnIndex() == 1) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setProjectName(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 2) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setExpenseName(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 3) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setPaymentMethod(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 4) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setCurrency(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 5) {
                if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi nmerik deil");
                }
                e.setNetAmount(BigDecimal.valueOf(cell.getNumericCellValue()));
            } else if (cell.getColumnIndex() == 6) {
                if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil");
                }
                e.setRestaurant(cell.getStringCellValue());
            } else if (cell.getColumnIndex() == 7) {
                if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                    throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:"
                            + cell.getColumnIndex() + " Hata Nedeni: Veri tipi nmerik deil");
                }
                e.setDocumentNumber(Integer.valueOf((int) cell.getNumericCellValue()));
            }
        }
        expenses.add(e);
    }

    if (wb != null)
        wb.close();

    if (fis != null)
        fis.close();

    return expenses;
}

From source file:org.netxilia.impexp.impl.ExcelImportService.java

License:Open Source License

@Override
public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName,
        InputStream is, IProcessingConsole console) throws ImportException {
    List<SheetFullName> sheetNames = new ArrayList<SheetFullName>();
    try {/*from   w  w w . java 2s. c o m*/
        log.info("Starting import:" + workbookName);
        Workbook poiWorkbook = new HSSFWorkbook(is);
        IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName);
        log.info("Read POI");

        NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver(
                styleService.getStyleDefinitions(workbookName));

        HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette();

        for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) {
            Sheet poiSheet = poiWorkbook.getSheetAt(s);

            SheetFullName sheetName = new SheetFullName(workbookName,
                    getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
            ISheet nxSheet = null;
            BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder();
            try {
                List<CellReference> refreshCells = new ArrayList<CellReference>();

                for (Row poiRow : poiSheet) {
                    if (poiRow.getRowNum() % 100 == 0) {
                        log.info("importing row #" + poiRow.getRowNum());
                    }
                    for (Cell poiCell : poiRow) {
                        if (nxSheet == null) {
                            // lazy creation
                            while (true) {
                                try {
                                    nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(),
                                            SheetType.normal);
                                    nxSheet.setRefreshEnabled(false);
                                    break;
                                } catch (AlreadyExistsException e) {
                                    // may happen is simultaneous imports take place
                                    sheetName = new SheetFullName(workbookName,
                                            getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName()));
                                }
                            }
                        }

                        CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(),
                                poiCell.getColumnIndex());
                        try {
                            ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver);
                            if (cmd != null) {
                                cellCommandBuilder.command(cmd);
                            }
                            if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                                refreshCells.add(ref);
                            }

                        } catch (Exception e) {
                            if (console != null) {
                                console.println("Could import cell " + ref + ":" + poiCell + ":" + e);
                            }
                            log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e);
                        }
                    }

                    if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) {
                        nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
                        cellCommandBuilder = new BlockCellCommandBuilder();
                    }
                }

                if (nxSheet == null) {
                    // empty sheet
                    continue;
                }
                if (!cellCommandBuilder.isEmpty()) {
                    nxSheet.sendCommandNoUndo(cellCommandBuilder.build());
                }
                // add the columns after as is not very clear how to get the number of cols in poi
                for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) {
                    int width = 50;
                    try {
                        width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c));
                        nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width));
                    } catch (NullPointerException ex) {
                        // ignore it
                        // NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998)
                        // defaultColumnWidth can be null !?
                    }

                    CellStyle poiStyle = poiSheet.getColumnStyle(c);
                    if (poiStyle == null) {
                        continue;
                    }
                    Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle,
                            poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver);
                    if (styles != null) {
                        nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles));
                    }
                }

                // merge
                List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions());
                for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) {
                    CellRangeAddress poiSpan = poiSheet.getMergedRegion(i);
                    spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(),
                            poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn()));
                }
                nxSheet.sendCommand(SheetCommands.spans(spans));

                // refresh all the cells now
                nxSheet.setRefreshEnabled(true);
                nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false));

            } finally {
                if (nxSheet != null) {
                    sheetNames.add(sheetName);
                }
            }
        }
    } catch (IOException e) {
        throw new ImportException(null, "Cannot open workbook:" + e, e);
    } catch (StorageException e) {
        throw new ImportException(null, "Error storing sheet:" + e, e);
    } catch (NotFoundException e) {
        throw new ImportException(null, "Cannot find workbook:" + e, e);
    } catch (NetxiliaResourceException e) {
        throw new ImportException(null, e.getMessage(), e);
    } catch (NetxiliaBusinessException e) {
        throw new ImportException(null, e.getMessage(), e);
    }

    return sheetNames;
}

From source file:org.newcashel.meta.model.NCClass.java

License:Apache License

public static void load(HSSFWorkbook wb, LaunchParms launchParm) throws Exception {

    // load the sheet
    Sheet sheet = wb.getSheet("ClassAttributes");
    if (sheet == null) {
        throw new Exception("The ClassAttributes sheet was not found in the MetaBook, terminate load process");
    }//from   w  ww .  j  av  a  2 s . co m

    //String[] fieldNames = POIUtil.getFirstRowVals(sheet);

    Class cls = Class.forName("org.newcashel.meta.model.NCClass");
    Class[] parmString = new Class[1];

    Row row = null;

    try {

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            // skip blank rows between class attributes
            row = sheet.getRow(i);

            if (row != null && (POIUtil.getCellValue(row, ATTRIBUTE_OFFSET) == null
                    || POIUtil.getCellValue(row, TYPE_OFFSET).length() < 1))
                continue;

            // get the size of the cell, the length will be the number of atbs in the class
            // determine if the next Cell to the left is a separate Cell or part of a CellRangeAddress
            Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                continue;
            }

            CellRangeAddress cra = getCellRangeAddress(sheet, row.getRowNum(), 0);
            if (cra == null) {
                return;
            }

            // instantiate the NCClass instance
            NCClass ncClass = new NCClass();
            ncClass.setClassName(POIUtil.getCellValue(row, CLASSNAME_OFFSET));
            //logger.info("loading NCClass " + ncClass.getClassName());

            ncClass.setSuperClassName(POIUtil.getCellValue(row, SUPERCLASS_NAME_OFFSET));
            ncClass.setClassParent(POIUtil.getCellValue(row, PARENTCLASS_OFFSET));
            ncClass.setPrimaryKey(POIUtil.getCellValue(row, PRIMARYKEY_OFFSET));
            ncClass.setPercolate(new Boolean(POIUtil.getCellValue(row, PERCOLATE_OFFSET)));
            ncClass.setGroupConstrain(new Boolean(POIUtil.getCellValue(row, GROUP_CONSTRAIN_OFFSET)));

            // not throwing java class errors, may not be significant to load context

            // TODO, if NO_VERIFY_JAVA_CLASS is true, skip validation
            // TODO, if NO_VERF true and blank 

            String javaClassName = POIUtil.getCellValue(row, JAVA_CLASS_OFFSET);
            if (javaClassName.endsWith("Person.class")) {
                System.out.println("OKK");
            }
            Class<?> javaCls = null;
            if (javaClassName != null && javaClassName.length() > 0) {
                ncClass.setJavaClassName(javaClassName);
                try {
                    javaCls = Class.forName(javaClassName);
                } catch (Exception e) {
                    logger.error("Java class specified but cannot be loaded for " + ncClass.getClassName()
                            + ", " + javaClassName);
                }
            } else {
                logger.info("no java class specified for class " + ncClass.getClassName());
            }

            classes.put(ncClass.getClassName(), ncClass);
            logger.info("Adding class " + ncClass.getClassName());

            // loop for all the rows in the cell range
            for (i = cra.getFirstRow(); i <= cra.getLastRow(); i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    return; // range iteration complete
                }
                cell = row.getCell(ATTRIBUTE_OFFSET, Row.RETURN_BLANK_AS_NULL);
                if (cell == null)
                    continue;

                String atbName = POIUtil.getCellValue(row, ATTRIBUTE_OFFSET);
                String version = POIUtil.getCellValue(row, VERSION_OFFSET);

                // if  no version id and the atb has not been set, then set it
                // if a version and it matches the build version, set/overwrite the value 
                Attribute atb = null;

                // if version id is set and matches the launchParm setting, use it else skip 
                // a non-versioned atb may be encountered first, reuse it if received a versioned one
                if (version != null && version.length() > 0) {
                    if (!(launchParm.getVersion().equals(version))) {
                        continue;
                    }
                    logger.debug("add version specific atb " + ncClass.getClassName() + ", " + atbName + ", "
                            + version);
                    // if a default version has already been established, use it else create one
                    atb = ncClass.getAttribute(atbName);
                    if (atb == null) {
                        atb = new Attribute();
                    }
                } else { // no version, use existing if already set to the current version
                    atb = ncClass.getAttribute(atbName);
                    if (atb == null) {
                        atb = new Attribute();
                    } else
                        continue; // already established a version specific atb, ignore non-versioned entry
                }

                // create the Attributes and add to the class instance
                // TODO, verify not null on these required values, user may override Excel edits
                atb.setName(POIUtil.getCellValue(row, ATTRIBUTE_OFFSET));
                atb.setType(POIUtil.getCellValue(row, TYPE_OFFSET));
                atb.setLabel(POIUtil.getCellValue(row, LABEL_OFFSET));
                atb.setIndexName(POIUtil.getPopulatedCellValue(row, INDEXNAME_OFFSET));

                //logger.info("added NCClass atb " + ncClass.getClassName() + ", " + atb.getName());

                // defaults to false
                atb.setStore(UTIL.convertBoolean(POIUtil.getCellValue(row, STORE_OFFSET)));
                /*
                String storeVal = POIUtil.getPopulatedCellValue(row, STORE_OFFSET);
                if (storeVal != null) {
                   atb.setStore(new Boolean(storeVal));
                }
                */

                // analyzed is default value, will tokenize field
                String indexVal = POIUtil.getPopulatedCellValue(row, INDEX_OFFSET);
                if (indexVal != null) {
                    atb.setIndex(indexVal);
                }

                // default is true, don't set unless value is not
                String includeInAll = POIUtil.getPopulatedCellValue(row, INCLUDEINALL_OFFSET);
                if (includeInAll != null && includeInAll.equalsIgnoreCase("no")) {
                    atb.setIncludeInAll(false);
                }

                // default varies, based on the numeric type
                // TODO, verify numeric field
                String precision = POIUtil.getPopulatedCellValue(row, PRECISIONSTEP_OFFSET);
                if (precision != null) {
                    atb.setPrecision(new Integer(precision));
                }

                String dateFormat = POIUtil.getPopulatedCellValue(row, DATEFORMAT_OFFSET);
                if (dateFormat != null) {
                    atb.setDateFormat(dateFormat);
                }

                String fieldDataFormat = POIUtil.getPopulatedCellValue(row, FIELDDATAFORMAT_OFFSET);
                if (fieldDataFormat != null) {
                    atb.setFieldDataFormat(fieldDataFormat);
                }

                atb.setDocValues(UTIL.convertBoolean(POIUtil.getCellValue(row, DOCVALUES_OFFSET)));

                String boost = POIUtil.getPopulatedCellValue(row, BOOST_OFFSET);
                if (boost != null) {
                    atb.setBoost(new Double(boost));
                }

                // defaults to not adding the field to the JSON string
                String nullVal = POIUtil.getPopulatedCellValue(row, NULLVALUE_OFFSET);
                if (nullVal != null) {
                    atb.setNullValue(nullVal);
                }

                String termVector = POIUtil.getPopulatedCellValue(row, TERMVECTOR_OFFSET);
                if (termVector != null) {
                    atb.setTermVector(termVector);
                }

                String analyzer = POIUtil.getPopulatedCellValue(row, ANALYZER_OFFSET);
                if (analyzer != null) {
                    atb.setAnalyzer(analyzer);
                }

                String indexAnalyzer = POIUtil.getPopulatedCellValue(row, INDEX_ANALYZER_OFFSET);
                if (indexAnalyzer != null) {
                    atb.setIndexAnalyzer(indexAnalyzer);
                }

                String searchAnalyzer = POIUtil.getPopulatedCellValue(row, SEARCH_ANALYZER_OFFSET);
                if (searchAnalyzer != null) {
                    atb.setSearchAnalyzer(searchAnalyzer);
                }

                atb.setIgnoreAbove(UTIL.convertAnyNumberToInt(POIUtil.getCellValue(row, IGNOREABOVE_OFFSET)));
                atb.setPositionOffset(
                        UTIL.convertAnyNumberToInt(POIUtil.getCellValue(row, POSITIONGAP_OFFSET)));
                atb.setIgnoreMalformed(UTIL.convertBoolean(POIUtil.getCellValue(row, IGNOREMALFORMED_OFFSET)));
                atb.setCoerceNumber(UTIL.convertBoolean(POIUtil.getCellValue(row, COERCENUMBER_OFFSET)));
                atb.setBinaryCompress(UTIL.convertBoolean(POIUtil.getCellValue(row, BINARYCOMPRESS_OFFSET)));
                atb.setCompressThreshold(
                        UTIL.convertAnyNumberToInt(POIUtil.getCellValue(row, COMPRESSTHRESHOLD_OFFSET)));

                // TODO, all all the others

                //atb.setStore(UTIL.convertBoolean(POIUtil.getCellValue(row, STORE_OFFSET)));

                if (atb.getType().equalsIgnoreCase("SubType")) {
                    subTypes.put(atb.getName(), atb.getLabel());
                } else {
                    // save the attribute
                    ncClass.attributes.put(atb.getName(), atb);
                    ncClass.labels.put(atb.getLabel(), atb);

                    // if java class, verify the field accessibility
                    if (javaCls != null) {
                        Field field = null;
                        Class<?> current = javaCls;
                        while (!(current.getName().equals("java.lang.Object"))) {
                            try {
                                field = current.getDeclaredField(atb.getName());
                                atb.setField(field);
                                //atb.setField(current.getDeclaredField(atb.getName()));
                                break;
                            } catch (Exception e) {
                                //System.out.println("java reflection warning, class/field not found, checking super class " + cls.getName() + ", " + atb.getName());
                                current = current.getSuperclass();
                                continue;
                            }
                        }

                        if (field != null) {
                            field.setAccessible(true);
                        }
                    }
                }
            }
            i--; // continue the loop on the prior row
        }
    } catch (Exception e) {
        String msg = "exception in NCClass load " + e.toString();
        logger.error(msg);
        throw new Exception(msg);
    }
}

From source file:org.nuclos.client.nuclet.generator.content.EntityFieldGroupNucletContentGenerator.java

License:Open Source License

@Override
public void generateEntityObjects() {
    final XSSFSheet sheet = generator.getWorkbook().getSheet(SHEET);
    for (Row row : sheet) {
        if (row.getRowNum() <= 1)
            continue; // header row

        newEntityObject();//from  ww w  . j  a v a 2  s. c o  m

        boolean emptyRow = true;
        for (int i = 0; i < COLUMN_COUNT; i++) {
            final Cell cell = row.getCell(i); // could be null!
            try {
                switch (i) {
                case COL_NAME:
                    storeField(FIELD_NAME, getStringValue(cell));
                    if (!StringUtils.looksEmpty(getStringValue(cell))) {
                        emptyRow = false;
                    }
                    break;
                }
            } catch (Exception ex) {
                error(cell, ex);
            }
        }

        if (!emptyRow)
            finishEntityObject();
    }
}

From source file:org.nuclos.client.nuclet.generator.content.EntityFieldNucletContentGenerator.java

License:Open Source License

@Override
public void generateEntityObjects() {
    final XSSFSheet sheet = generator.getWorkbook().getSheet(SHEET);
    for (Row row : sheet) {
        if (row.getRowNum() <= 1)
            continue; // header row

        newEntityObject();/*from   w w w .  jav  a 2 s .co  m*/
        storeField("order", row.getRowNum());

        boolean emptyRow = true;
        for (int i = 0; i < COLUMN_COUNT; i++) {
            final Cell cell = row.getCell(i); // could be null!
            try {
                switch (i) {
                case COL_ENTITY_NAME:
                    storeField("entity", getStringValue(cell));
                    storeFieldId("entity", entityGenerator.getIdByName(getStringValue(cell)));
                    if (!StringUtils.looksEmpty(getStringValue(cell))) {
                        emptyRow = false;
                    }
                    break;
                case COL_NAME:
                    storeField("field", getStringValue(cell));
                    break;
                case COL_COLUMN:
                    storeField("dbfield", getStringValue(cell));
                    break;
                case COL_LABEL:
                    storeLocaleResource("localeresourcel", getStringValue(cell));
                    storeLocaleResource("localeresourced", getStringValue(cell));
                    break;
                case COL_ATTRBIUTE_GROUP:
                    if (!StringUtils.looksEmpty(getStringValue(cell))) {
                        storeField("entityfieldgroup", getStringValue(cell));
                        storeFieldId("entityfieldgroup",
                                entityFieldGroupGenerator.getIdByName(getStringValue(cell)));
                    }
                    break;
                case COL_DATATYPE:
                    storeField("datatype", getStringValue(cell));
                    break;
                case COL_DATASCALE:
                    storeField("datascale", getIntegerValue(cell));
                    break;
                case COL_DATAPRECISION:
                    storeField("dataprecision", getIntegerValue(cell));
                    break;
                case COL_READONLY:
                    storeField("readonly", getBooleanValue(cell));
                    break;
                case COL_NULLABLE:
                    storeField("nullable", getBooleanValue(cell));
                    break;
                case COL_UNIQUE:
                    storeField("unique", getBooleanValue(cell));
                    break;
                case COL_LOGBOOK:
                    storeField("logbooktracking", getBooleanValue(cell));
                    break;
                case COL_MODIFIABLE:
                    storeField("modifiable", getBooleanValue(cell));
                    break;
                case COL_FOREIGN_ENTITY:
                    storeField("foreignentity", getStringValue(cell));
                    break;
                case COL_FOREIGN_ENTITY_FIELD:
                    storeField("foreignentityfield", getStringValue(cell));
                    break;
                case COL_SEARCHABLE:
                    storeField("searchable", getBooleanValue(cell));
                    break;
                case COL_DELETE_ON_CASCADE:
                    storeField("ondeletecascade", getBooleanValue(cell));
                    break;
                case COL_INDEXED:
                    storeField("indexed", getBooleanValue(cell));
                    break;
                case COL_LOOKUP_ENTITY:
                    storeField("lookupentity", getStringValue(cell));
                    break;
                case COL_LOOKUP_ENTITY_FIELD:
                    storeField("lookupentityfield", getStringValue(cell));
                    break;
                case COL_FORMAT_INPUT:
                    storeField("formatinput", getStringValue(cell));
                    break;
                case COL_FORMAT_OUTPUT:
                    storeField("formatoutput", getStringValue(cell));
                    break;
                case COL_DEFAULT_VALUE:
                    storeField("valuedefault", getStringValue(cell));
                    break;
                case COL_CALC_FUNCTION:
                    storeField("calcfunction", getStringValue(cell));
                    break;
                }
            } catch (Exception ex) {
                error(cell, ex);
            }
        }

        if (!emptyRow)
            finishEntityObject();
    }
}

From source file:org.nuclos.client.nuclet.generator.content.EntityNucletContentGenerator.java

License:Open Source License

@Override
public void generateEntityObjects() {
    final XSSFSheet sheet = generator.getWorkbook().getSheet(SHEET);
    for (Row row : sheet) {
        if (row.getRowNum() <= 1)
            continue; // header row

        newEntityObject();//from  w ww  .  j a va 2s  .c o  m

        boolean emptyRow = true;
        for (int i = 0; i < COLUMN_COUNT; i++) {
            final Cell cell = row.getCell(i); // could be null!
            try {
                switch (i) {
                case COL_NAME:
                    storeField(FIELD_ENTITY, getStringValue(cell));
                    if (!StringUtils.looksEmpty(getStringValue(cell))) {
                        emptyRow = false;
                    }
                    break;
                case COL_TABLE_NAME:
                    storeField("dbentity", getStringValue(cell));
                    break;
                case COL_LABEL:
                    storeLocaleResource("localeresourcel", getStringValue(cell));
                    storeLocaleResource("localeresourced", getStringValue(cell));
                    break;
                case COL_MENU:
                    storeLocaleResource("localeresourcem", getStringValue(cell));
                    break;
                case COL_MENUSHORTCUT:
                    storeField("menushortcut", getStringValue(cell));
                    break;
                case COL_ACCELERATOR:
                    storeField("accelerator", getStringValue(cell));
                    break;
                case COL_ACCELERATOR_MODIFIER:
                    storeField("acceleratormodifier", getIntegerValue(cell));
                    break;
                case COL_SEARCHABLE:
                    storeField("searchable", getBooleanValue(cell));
                    break;
                case COL_CACHEABLE:
                    storeField("cacheable", getBooleanValue(cell));
                    break;
                case COL_LOGBOOK:
                    storeField("logbooktracking", getBooleanValue(cell));
                    break;
                case COL_EDITABLE:
                    storeField("editable", getBooleanValue(cell));
                    break;
                case COL_STATEMODEL:
                    storeField("usessatemodel", getBooleanValue(cell));
                    break;
                case COL_SYSTEM_ID_PREFIX:
                    storeField("systemidprefix", getStringValue(cell));
                    break;
                }
            } catch (Exception ex) {
                error(cell, ex);
            }
        }

        if (!emptyRow)
            finishEntityObject();
    }
}

From source file:org.nuclos.client.nuclet.generator.NucletGenerator.java

License:Open Source License

private void checkFileVersion() throws NuclosBusinessException {
    final XSSFSheet sheet = workbook.getSheet(XLSX_FILE_SHEET_VERSION);
    for (Row row : sheet) {
        switch (row.getRowNum()) {
        case 0:/*from w w  w  .  java 2  s.co  m*/
            continue; // header row
        case 1:
            String value = row.getCell(0).getStringCellValue();
            if (!StringUtils.equalsIgnoreCase(value, XLSX_FILE_VERSION)) {
                throw new NuclosBusinessException(String.format(
                        "Wrong File Version \"%s\")! Current Nuclos Generation Version is \"%s\".", value,
                        XLSX_FILE_VERSION));
            }
        default:
            return;
        }
    }
}