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

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

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

Introduction

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

Prototype

short getFirstCellNum();

Source Link

Document

Get the number of the first cell contained in this row.

Usage

From source file:com.plugin.excel.util.ExcelUtil.java

License:Apache License

/**
 * @param srcSheet/*w  w w.  j  av a  2  s. co m*/
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *            -
 */
public static void copyRow(SXSSFSheet srcSheet, SXSSFSheet destSheet, Row srcRow, Row destRow,
        Map<Integer, CellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a
    // merged zone
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        Cell oldCell = srcRow.getCell(j); // ancienne cell
        Cell newCell = destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell
            copyCell(oldCell, newCell, styleMap);
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                // System.out.println("Selected merged region: " +
                // mergedRegion.toString());
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
                        mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                // System.out.println("New merged region: " +
                // newMergedRegion.toString());
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }

}

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

private LinkedHashMap<String, Integer> getOrderedColumnIndexes(Row titleRow,
        LinkedHashMap<String, String> vendorSpecificColumns) {
    LinkedHashMap<String, Integer> orderedColumnIndexesMap = new LinkedHashMap<String, Integer>();

    int startCellNumber = titleRow.getFirstCellNum();

    Set<Entry<String, String>> keySet = vendorSpecificColumns.entrySet();
    Iterator<Entry<String, String>> iterator = keySet.iterator();
    while (iterator.hasNext()) {
        Entry<String, String> entry = iterator.next();

        if (StringUtils.isEmpty(entry.getValue())) {
            orderedColumnIndexesMap.put(entry.getKey(), -1);
        }// www . ja v a2  s  .com

        boolean foundExpectedColumn = false;
        for (int i = startCellNumber; i < titleRow.getLastCellNum(); i++) {
            String columnHeader = (String) getCellValue(((HSSFCell) titleRow.getCell(i)));
            if (StringUtils.isEmpty(columnHeader.trim()) && StringUtils.isEmpty(entry.getValue().trim())) {
                continue;
            }

            if (columnHeader.trim().equalsIgnoreCase(entry.getValue().trim())) {
                // match found
                foundExpectedColumn = true;
                // orderedColumnIndexes.add(i);
                orderedColumnIndexesMap.put(entry.getKey(), i);

                System.out.println("Column Index Mapping for " + entry.getKey() + " = " + columnHeader
                        + " @ index = " + i);
                break;
            }
        }
        if (!foundExpectedColumn) {
            // throw error??
            System.out.println("Could not find expected column " + entry.getValue());
            orderedColumnIndexesMap.put(entry.getKey(), -1);
        }
    }

    System.out.println("Ordered Column Indexes Map = " + orderedColumnIndexesMap);
    return orderedColumnIndexesMap;
}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map) throws Exception {
    deleteCell(sheet, map);//from   w w  w. j av  a2 s  .  c  o m
    Row row = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }
}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??/*from  ww w  .  j a v  a 2s  . c  o  m*/
 * @param sheet
 * @param map
 * @throws Exception 
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(PoiElUtil.IF_DELETE)) {
                    if (Boolean.valueOf(PoiElUtil.eval(text
                            .substring(text.indexOf(PoiElUtil.START_STR) + 2, text.indexOf(PoiElUtil.END_STR))
                            .trim(), map).toString())) {
                        com.qihang.winter.poi.util.PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/***
 * ?List?/*from   w w  w.ja v  a2s  .  c o  m*/
 *
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object,
        com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param, Row row,
        Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures,
        com.qihang.winter.poi.excel.entity.ImportParams params) throws Exception {
    Collection collection = (Collection) com.qihang.winter.poi.util.PoiPublicUtil
            .getMethod(param.getName(), object.getClass()).invoke(object, new Object[] {});
    Object entity = com.qihang.winter.poi.util.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:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,
        com.qihang.winter.poi.excel.entity.ImportParams params, Map<String, PictureData> pictures)
        throws Exception {
    List collection = new ArrayList();
    Map<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity> excelParams = new HashMap<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity>();
    List<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams> excelCollection = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams>();
    String targetId = null;// ww  w . j a v  a2s  .  c  o m
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass);
        com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass
                .getAnnotation(com.qihang.winter.poi.excel.annotation.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;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        row = rows.next();
        // ???,?,?
        if ((row.getCell(params.getKeyIndex()) == null
                || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
            for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) {
                addListContinue(object, param, row, titlemap, targetId, pictures, params);
            }
        } else {
            object = com.qihang.winter.poi.util.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 {
                            saveFieldValue(params, object, cell, excelParams, titleString, row);
                        }
                    }
                }

                for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                }
                collection.add(object);
            } catch (com.qihang.winter.poi.exception.excel.ExcelImportException e) {
                if (!e.getType()
                        .equals(com.qihang.winter.poi.exception.excel.enums.ExcelImportEnum.VERIFY_ERROR)) {
                    throw new com.qihang.winter.poi.exception.excel.ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:com.radaee.excel.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();//from   ww w .j a  v a2s  . co m

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    //                        CellFormat cf = CellFormat.getInstance(
                    //                                style.getDataFormatString());
                    //                        CellFormatResult result = cf.apply(cell);
                    //                        content = result.text;
                    content = getText(cell);

                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:com.runwaysdk.dataaccess.io.excel.ErrorSheet.java

License:Open Source License

public void autoSize() {
    Row row = this.errorSheet.getRow(HEADER_COLUMN);
    if (row != null) {
        for (short s = row.getFirstCellNum(); s < row.getLastCellNum(); s++) {
            this.errorSheet.autoSizeColumn(s);
        }//from   ww w.  ja v  a  2s .c  o  m
    }
}

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }//ww w. j a  v a2  s  . c om

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

private boolean rowIsBlank(Row row) {
    // returns true if a row has cells but all cells are 'BLANK' type.
    boolean isBlank = true;
    for (int columnNum = row.getFirstCellNum(); columnNum < row.getLastCellNum(); columnNum++) {
        Cell c = row.getCell(columnNum);
        isBlank = isBlank && (c == null || c.getCellTypeEnum() == CellType.BLANK);
        if (!isBlank)
            break;
    }/*w  w  w.ja  va2s. c  o m*/
    return isBlank;
}