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.jberet.support.io.ExcelUserModelItemReader.java

License:Open Source License

@Override
public Object readItem() throws Exception {
    if (currentRowNum == this.end) {
        return null;
    }//from w ww.j  a v a 2 s  .c o  m
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        currentRowNum = row.getRowNum();
        final short lastCellNum = row.getLastCellNum();
        if (lastCellNum == -1) { // no cell in the current row
            continue;
        }
        final int lastColumn = Math.max(lastCellNum, minColumnCount);
        if (java.util.List.class.isAssignableFrom(beanType)) {
            final List<Object> resultList = new ArrayList<Object>();
            for (int cn = 0; cn < lastColumn; cn++) {
                final Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                if (c == null) { // The spreadsheet is empty in this cell
                    resultList.add(null);
                } else {
                    resultList.add(getCellValue(c, c.getCellType()));
                }
            }
            return resultList;
        } else {
            final Map<String, Object> resultMap = new HashMap<String, Object>();
            for (int cn = 0; cn < header.length; cn++) {
                final Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                if (c != null) {
                    resultMap.put(header[cn], getCellValue(c, c.getCellType()));
                }
            }
            if (java.util.Map.class.isAssignableFrom(beanType)) {
                return resultMap;
            } else {
                if (objectMapper == null) {
                    initJsonFactoryAndObjectMapper();
                }
                final Object readValue = objectMapper.convertValue(resultMap, beanType);
                if (!skipBeanValidation) {
                    ItemReaderWriterBase.validate(readValue);
                }
                return readValue;
            }
        }
    }

    return null;
}

From source file:org.jberet.support.io.ExcelUserModelItemReader.java

License:Open Source License

protected void initWorkbookAndSheet(int startRowNumber) throws Exception {
    workbook = WorkbookFactory.create(inputStream);
    if (sheetName != null) {
        sheet = workbook.getSheet(sheetName);
    }/*from   www.  j a  v a2 s .  c  o  m*/
    if (sheet == null) {
        sheet = workbook.getSheetAt(sheetIndex);
    }
    startRowNumber = Math.max(startRowNumber, sheet.getFirstRowNum());
    rowIterator = sheet.rowIterator();

    if (startRowNumber > 0) {
        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            currentRowNum = row.getRowNum();
            if (header == null && headerRow == currentRowNum) {
                header = getCellStringValues(row);
            }
            if (currentRowNum >= startRowNumber - 1) {
                break;
            }
        }
    }
}

From source file:org.jberet.support.io.ExcelUserModelItemWriter.java

License:Open Source License

@Override
public void writeItems(final List<Object> items) throws Exception {
    int nextRowNum = currentRowNum + 1;
    Row row = null;
    if (List.class.isAssignableFrom(beanType)) {
        for (int i = 0, j = items.size(); i < j; ++i, ++nextRowNum) {
            @SuppressWarnings("unchecked")
            final List<Object> item = (List<Object>) items.get(i);
            row = sheet.createRow(nextRowNum);
            for (int x = 0, y = item.size(); x < y; ++x) {
                createCell(row, x, item.get(x));
            }// ww w .j av a2s  .  c  o  m
        }
    } else if (Map.class.isAssignableFrom(beanType)) {
        for (int i = 0, j = items.size(); i < j; ++i, ++nextRowNum) {
            @SuppressWarnings("unchecked")
            final Map<String, Object> item = (Map<String, Object>) items.get(i);
            row = sheet.createRow(nextRowNum);
            for (int x = 0, y = header.length; x < y; ++x) {
                createCell(row, x, item.get(header[x]));
            }
        }
    } else {
        if (objectMapper == null) {
            initJsonFactoryAndObjectMapper();
        }
        for (int i = 0, j = items.size(); i < j; ++i, ++nextRowNum) {
            final Object item = items.get(i);

            @SuppressWarnings("unchecked")
            final Map<String, Object> itemAsMap = objectMapper.convertValue(item, Map.class);

            row = sheet.createRow(nextRowNum);
            for (int x = 0, y = header.length; x < y; ++x) {
                createCell(row, x, itemAsMap.get(header[x]));
            }
        }
    }
    currentRowNum = row.getRowNum();
    if (sheet instanceof SXSSFSheet) {
        ((SXSSFSheet) sheet).flushRows();
    }
}

From source file:org.jeecgframework.poi.excel.imports.ExcelImportServer.java

License:Apache License

/***
 * ?List?/*from ww  w  .  j a v  a  2 s  . c  om*/
 * 
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap,
        String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception {
    Collection collection = (Collection) PoiPublicUtil.getMethod(param.getName(), object.getClass())
            .invoke(object, new Object[] {});
    Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
    String picId;
    boolean isUsed = false;// ??
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        String titleString = (String) titlemap.get(i);
        if (param.getExcelParams().containsKey(titleString)) {
            if (param.getExcelParams().get(titleString).getType() == 2) {
                picId = row.getRowNum() + "_" + i;
                saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
            } else {
                saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
            }
            isUsed = true;
        }
    }
    if (isUsed) {
        collection.add(entity);
    }
}

From source file:org.jeecgframework.poi.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
        Map<String, PictureData> pictures) throws Exception {
    List collection = new ArrayList();
    Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    String targetId = null;/*from ww w .j  a  va  2 s .c o  m*/
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    // 
    int count = 4;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        count++;
        row = rows.next();
        // ???,?,?
        if ((row.getCell(params.getKeyIndex()) == null
                || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
            for (ExcelCollectionParams param : excelCollection) {
                try {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                } catch (Exception e) {
                    // TODO: handle exception
                    Exception f = new Exception("" + count + "" + e.getMessage());
                    throw f;
                }
            }
        } else {
            object = PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = row.getLastCellNum(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            try {
                                saveFieldValue(params, object, cell, excelParams, titleString, row);
                            } catch (Exception e) {
                                // TODO: handle exception
                                Exception f = new Exception("" + count + "" + e.getMessage());
                                throw f;
                            }
                        }
                    }
                }

                for (ExcelCollectionParams param : excelCollection) {
                    try {
                        addListContinue(object, param, row, titlemap, targetId, pictures, params);
                    } catch (Exception e) {
                        // TODO: handle exception
                        Exception f = new Exception("" + count + "" + e.getMessage());
                        throw f;
                    }
                }
                collection.add(object);
            } catch (ExcelImportException e) {
                if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:org.lazulite.boot.autoconfigure.core.utils.excel.ExportExcel.java

License:Apache License

/**
 * ?annotation.ExportField?/*www.j  a  v a  2  s.com*/
 *
 * @return list ?
 */
public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = ReflectUtils.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = ReflectUtils.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = ReflectUtils.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }
                }

            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

From source file:org.lisapark.octopus.util.json.ExcelSardineUtils.java

License:Open Source License

public static void main(String[] args) {

    Map<String, Integer> prodCellIndexMap = Maps.newHashMap();
    prodCellIndexMap.put(SHOP, 0);/*from www  .ja  va 2 s .  co m*/
    prodCellIndexMap.put(SHIFT, 0);
    prodCellIndexMap.put(MACHINE, 0);
    prodCellIndexMap.put(PRODUCT, 0);
    prodCellIndexMap.put(PRODUCT_TYPE, 0);
    prodCellIndexMap.put(MATERIAL_TYPE, 0);
    prodCellIndexMap.put(RAW_MATERIAL, 4);
    prodCellIndexMap.put(TOTAL_MATERIALS, 5);
    prodCellIndexMap.put(TOTAL_PRODUCTS, 6);

    Map<String, Integer> wrhCellIndexMap = Maps.newHashMap();
    wrhCellIndexMap.put(WAREHOUSE, 1);
    wrhCellIndexMap.put(ITEM, 1);
    wrhCellIndexMap.put(ITEM_TYPE, 1);
    wrhCellIndexMap.put(BEGINING, 0);
    wrhCellIndexMap.put(INCOMING, 1);
    wrhCellIndexMap.put(OUTGOING, 2);
    wrhCellIndexMap.put(ENDING, 3);

    try {
        String excelFile = "http://173.72.110.131:8080/WebDavServer/iPlast/Warehouse/";

        // Get all xml files
        Sardine sardine = SardineFactory.begin("", "");
        List<DavResource> resources = sardine.getResources(excelFile);

        for (DavResource res : resources) {
            String url = res.getPath();
            //getAbsoluteUrl();
            if (res.isDirectory()) {
                continue;
            } else {
                Map<String, String> props = res.getCustomProps();
                if (props.get(PROCESSED) == null) {
                    InputStream isData = sardine.get(url);
                    HSSFWorkbook book = new HSSFWorkbook(isData);

                    int index = 0;
                    int increament = 1;
                    if (book.getNumberOfSheets() > index) {
                        if (increament == 0) {
                            //                                increament = PROD_OUTLINE_INCREAMENT;
                            increament = WRH_OUTLINE_INCREAMENT;
                        }
                        Sheet sheet = book.getSheetAt(index);
                        if (sheet == null) {
                            continue;
                        }

                        // Iterate through the rows.
                        int splitRowNumber = 0;

                        if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
                            splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
                        }

                        Map<String, Object> rowMap = Maps.newHashMap();

                        int start = 2;
                        Row dateRow = sheet.getRow(8);
                        int end = dateRow.getLastCellNum();

                        for (int dateShift = start; dateShift < end - 4; dateShift = dateShift + 4) {

                            rowMap.put(DATE, formatDate(dateRow.getCell(dateShift).getStringCellValue()));
                            System.out.println(dateRow.getCell(dateShift).getStringCellValue());

                            Sheet _sheet = book.getSheetAt(index);

                            for (Iterator<Row> rowsIt = _sheet.rowIterator(); rowsIt.hasNext();) {
                                Row row = rowsIt.next();
                                if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
                                    continue;
                                }

                                Cell cell = row.getCell(1);
                                int indent = cell.getCellStyle().getIndention();
                                int absIndent = indent / increament;
                                //                                
                                if (processRowWrhSs(rowMap, row, wrhCellIndexMap, absIndent, dateShift)) {
                                    System.out.println(rowMap);
                                }
                            }
                        }
                    }
                    props.put(PROCESSED, TRUE);
                    sardine.setCustomProps(url, props, null);
                } else {
                    System.out.println("Property PROCESSED: " + props.get(PROCESSED));
                    List<String> removeProps = new ArrayList<String>(1);
                    removeProps.add(PROCESSED);

                    sardine.setCustomProps(url, null, removeProps);
                }
                break;
            }
        }
    } catch (FileNotFoundException ex) {
        Exceptions.printStackTrace(ex);
    } catch (IOException ex) {
        Exceptions.printStackTrace(ex);
    }
}

From source file:org.lisapark.octopus.util.json.JsonUtils.java

License:Open Source License

/**
 * /*from   w ww .  j  a v  a 2  s.  c om*/
 * @param sheet
 * @param ontology
 * @return
 * @throws JSONException 
 */
private String jsonFromSS(Sheet sheet, int increment) throws JSONException {
    // Return null, if sheet is null
    if (sheet == null)
        return null;

    String sheetName = sheet.getSheetName();
    if (sheetName.isEmpty()) {
        sheetName = SPREAD_SHEET_ROWS;
    }

    // Iterate through the rows.
    StringBuilder stringBuilderRows = new StringBuilder();
    List<String> stack = new ArrayList<String>();
    Boolean first = Boolean.TRUE;
    int splitRowNumber = 0;
    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
    }
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        if (row.getPhysicalNumberOfCells() > 0 && row.getRowNum() >= splitRowNumber) {
            continue;
        }
        String stringCells = jsonFromRowAsString(row);
        if (stringCells.isEmpty()) {
            continue;
        }

        String stringRow = jsonFromRowAsTreeNode(row, stringCells, stack, increment);

        if (first) {
            stringBuilderRows.append("[").append(stringRow);
            first = Boolean.FALSE;
        } else {
            stringBuilderRows.append(",").append(stringRow);
        }

    }
    // Get the JSON text.
    stringBuilderRows = stringBuilderRows.append("]"
    //                + "}"
    );

    return //"{" + "\"" + sheetName + "\"" + " : " + 
    stringBuilderRows.toString();
}

From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java

License:Open Source License

public static String xmlTagAttributesFromSS(Sheet sheet, int increment) throws JSONException {
    // Return null, if sheet is null
    if (sheet == null) {
        return null;
    }/*from ww w  .  j a v  a  2s .  c om*/

    // Iterate through the rows.
    StringBuilder stringBuilderRows = new StringBuilder();
    List<String> stack = new ArrayList<String>();
    Boolean first = Boolean.TRUE;
    int splitRowNumber = 0;
    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
    }
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
            continue;
        }
        String tagAttributes = tagAttributesAsString(row);
        if (tagAttributes.isEmpty()) {
            continue;
        }
        String stringRow = xmlFromRowAsTreeAttributes(tagAttributes, row, stack, increment);
        if (first) {
            stringBuilderRows.append(IPLAST_LEFT_TAG).append(stringRow);
            first = Boolean.FALSE;
        } else {
            stringBuilderRows.append(stringRow);
        }
        System.out.println(stringRow);
    }

    // Close all opened tags from stack
    if (!stack.isEmpty()) {
        int n = stack.size();
        for (int i = n - 1; i >= 0; --i) {
            stringBuilderRows = stringBuilderRows.append(rightTag(stack.get(i)));
        }
    }
    // Get the XML text.
    stringBuilderRows = stringBuilderRows.append(IPLAST_RIGHT_TAG);

    return stringBuilderRows.toString();
}

From source file:org.lisapark.octopus.util.xml.XmlConverterUtils.java

License:Open Source License

private static String xmlTagNodesFromSSheet(Sheet sheet, int increment, int dataRangeStart, int dataRangeLen)
        throws JSONException {
    // Return null, if sheet is null
    if (sheet == null) {
        return null;
    }/*from  ww  w .  j a  va2s  .c  o  m*/

    // Iterate through the rows.
    StringBuilder stringBuilderRows = new StringBuilder();
    List<String> stack = new ArrayList<String>();
    //        Boolean first = Boolean.TRUE;        
    int splitRowNumber = 0;
    if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
        splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
    }
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
            continue;
        }
        String tagNodes = tagNodesAsString(row, dataRangeStart, dataRangeLen);
        if (tagNodes.isEmpty()) {
            continue;
        }
        String stringRow = xmlFromRowAsTreeNodes(tagNodes, row, stack, increment);
        stringBuilderRows.append(stringRow);
    }
    // Close all opened tags from stack
    if (!stack.isEmpty()) {
        int n = stack.size();
        for (int i = n - 1; i >= 0; --i) {
            stringBuilderRows = stringBuilderRows.append(rightTag(stack.get(i)));
        }
    }

    return stringBuilderRows.toString();
}