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

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

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

License:Apache License

/**
 * @param srcSheet//from   w w w  . ja  va  2s .c o 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.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadErrorResponse(InputStream is, List<String> errors)
        throws IOException {
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    HSSFFont font = wb.createFont();/*from w  w w. j  a v  a 2 s . c  o m*/
    font.setColor(Font.COLOR_RED);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    HSSFSheet sheet = wb.getSheetAt(0);

    Row row = sheet.getRow(0);
    int lastCell = row.getLastCellNum();
    Cell cell = createExcelCell(sheet, row, lastCell, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("ERRORS");

    for (String anError : errors) {
        String lineNoStr = StringUtils.substringBefore(anError, ":");
        lineNoStr = StringUtils.substringAfter(lineNoStr, "Line ");
        Integer lineNo = new Integer(lineNoStr) - 1;

        row = sheet.getRow(lineNo);
        cell = createExcelCell(sheet, row, lastCell, 256 * 100);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(anError);
    }

    return createOutputStream(wb);
}

---

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);
        }/* ww  w  .  j  a  va2 s . c om*/

        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 www.  j a v a  2  s.c  om*/
    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 w  w w  .  j  a  va 2 s.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 www . j  a v  a2 s .  c  om
 *
 * @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;/*from  w  w w.  j  av a  2 s .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.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ?(?,,?)// www  . j ava  2  s . c  o  m
 *
 * @param params
 * @param object
 * @param cell
 * @param excelParams
 * @param titleString
 * @param row
 * @throws Exception
 */
private void saveFieldValue(com.qihang.winter.poi.excel.entity.ImportParams params, Object object, Cell cell,
        Map<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity> excelParams,
        String titleString, Row row) throws Exception {
    Object value = cellValueServer.getValue(params.getDataHanlder(), object, cell, excelParams, titleString);
    if (object instanceof Map) {
        if (params.getDataHanlder() != null) {
            params.getDataHanlder().setMapValue((Map) object, titleString, value);
        } else {
            ((Map) object).put(titleString, value);
        }
    } else {
        com.qihang.winter.poi.excel.entity.result.ExcelVerifyHanlderResult verifyResult = verifyHandlerServer
                .verifyData(object, value, titleString, excelParams.get(titleString).getVerify(),
                        params.getVerifyHanlder());
        if (verifyResult.isSuccess()) {
            setValues(excelParams.get(titleString), object, value);
        } else {
            Cell errorCell = row.createCell(row.getLastCellNum());
            errorCell.setCellValue(verifyResult.getMsg());
            errorCell.setCellStyle(errorCellStyle);
            verfiyFail = true;
            throw new com.qihang.winter.poi.exception.excel.ExcelImportException(
                    com.qihang.winter.poi.exception.excel.enums.ExcelImportEnum.VERIFY_ERROR);
        }
    }
}

---

From source file:com.qihang.winter.poi.util.PoiSheetUtility.java

License:Apache License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * /* www .  j  a  va  2  s.  c o  m*/
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = 0; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}

---

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

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();//from   w  w w  . ja  v a 2 s . c  om

    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");
}

---

• «
• 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
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• »