Example usage for org.apache.poi.xssf.usermodel XSSFRow getLastCellNum

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getLastCellNum

Introduction

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

Prototype

@Override
public short getLastCellNum()

Source Link

Document

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

Usage

From source file:org.finra.jtaf.core.utilities.ExcelFileParser.java

License:Apache License

public List<List<String>> parseExcelFile(boolean isXlsx) throws Exception {
    List<List<String>> parsedExcelFile = new ArrayList<List<String>>();
    if (isXlsx) {
        for (int i = 0, numberOfRows = workBookSheetXlsx.getPhysicalNumberOfRows(); i < numberOfRows + 1; i++) {
            XSSFRow row = workBookSheetXlsx.getRow(i);
            if (row != null) {
                List<String> parsedExcelRow = new ArrayList<String>();
                for (int j = 0, numberOfColumns = row.getLastCellNum(); j < numberOfColumns; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        try {
                            if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                parsedExcelRow.add(cell.getStringCellValue());
                            } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                                parsedExcelRow.add("");
                            } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                                parsedExcelRow.add(String.valueOf(cell.getBooleanCellValue()));
                            } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                parsedExcelRow.add(String.valueOf(cell.getNumericCellValue()));
                            } else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                                parsedExcelRow.add("");
                            } else {
                                parsedExcelRow.add(cell.getStringCellValue());
                            }//www  . j  a v a2s  .  c  o m
                        } catch (Exception e) {
                            logger.fatal("Oops! Can't read cell (row = " + i + ", column = " + j
                                    + ") in the excel file! Change cell format to 'Text', please!");
                            return null;
                        }
                    } else {
                        parsedExcelRow.add("");
                    }
                }
                parsedExcelFile.add(parsedExcelRow);
            }
        }
    } else {
        for (int i = 0, numberOfRows = workBookSheetXls.getPhysicalNumberOfRows(); i < numberOfRows + 1; i++) {
            HSSFRow row = workBookSheetXls.getRow(i);
            if (row != null) {
                List<String> parsedExcelRow = new ArrayList<String>();
                for (int j = 0, numberOfColumns = row.getLastCellNum(); j < numberOfColumns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        try {
                            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                parsedExcelRow.add(cell.getStringCellValue());
                            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                                parsedExcelRow.add("");
                            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                                parsedExcelRow.add(String.valueOf(cell.getBooleanCellValue()));
                            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                parsedExcelRow.add(String.valueOf(cell.getNumericCellValue()));
                            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                                parsedExcelRow.add(String.valueOf(""));
                            } else {
                                parsedExcelRow.add(cell.getStringCellValue());
                            }
                        } catch (Exception e) {
                            logger.fatal("Oops! Can't read cell (row = " + i + ", column = " + j
                                    + ") in the excel file! Change cell format to 'Text', please!");
                            return null;
                        }
                    } else {
                        parsedExcelRow.add("");
                    }
                }
                parsedExcelFile.add(parsedExcelRow);
            }
        }
    }

    return parsedExcelFile;
}

From source file:org.hlc.utility.excel.ExcelInputHandler.java

License:Apache License

/**
 * Import excel./*from  w  ww .ja va  2s  . c o m*/
 *
 * @param <T> the generic type
 * @param type the type
 * @param in the in
 * @return the list
 */
@SuppressWarnings("rawtypes")
public <T> List<T> importExcel2007(Class<T> type, InputStream in) {

    Excel excelAnn = type.getAnnotation(Excel.class);
    if (excelAnn == null) {
        throw new ExcelException("The Class <" + type + "> did not Excel");
    }

    List<T> list = new ArrayList<T>();

    Map<String, Method> mapping = new LinkedHashMap<String, Method>();
    Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>();

    try {
        // Step1 ??
        Field fileds[] = type.getDeclaredFields();
        for (int i = 0; i < fileds.length; i++) {
            Field field = fileds[i];
            ExcelColumn column = field.getAnnotation(ExcelColumn.class);
            if (column != null) {
                Method setMethod = ReflectionUtils.setValueMethod(field, type);
                mapping.put(column.value(), setMethod);
                if (column.converter() != TypeHandler.class) {
                    converters.put(setMethod.getName().toString(), column.converter().newInstance());
                } else {
                    converters.put(setMethod.getName().toString(),
                            TypeHandlerFactory.getHandler(field.getType()));
                }
            }
        }

        T temp = null;
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(in);
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            // ?Sheet
            List<Method> methods = new ArrayList<Method>();
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

                XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }

                // ?
                if (rowNum == 0) {
                    for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) {
                        String title = hssfRow.getCell(cellNum).getStringCellValue();
                        Method me = mapping.get(title);
                        if (me == null) {
                            continue;
                        }
                        methods.add(me);
                    }
                    continue;
                }

                temp = type.newInstance();
                for (int cellNum = 0; cellNum < methods.size(); cellNum++) {
                    XSSFCell xh = hssfRow.getCell(cellNum);
                    if (xh == null) {
                        continue;
                    }
                    Method m = methods.get(cellNum);
                    TypeHandler handler = converters.get(m.getName());
                    if (handler == null) {
                        continue;
                    }
                    xh.setCellType(Cell.CELL_TYPE_STRING);
                    String value = xh.getStringCellValue();
                    if (StringUtils.isEmpty(value)) {
                        continue;
                    }
                    Object val = null;
                    try {
                        val = handler.stringToType(value);
                    } catch (Exception e) {
                        throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1)
                                + "" + (cellNum + 1) + "" + value + "??");
                    }
                    methods.get(cellNum).invoke(temp, val);
                }
                list.add(temp);
            }
        }
    } catch (Exception e) {
        throw new ExcelException("Excel processing error?", e);
    }
    return list;
}

From source file:org.kitodo.production.plugin.importer.massimport.PicaMassImport.java

License:Open Source License

private List<Record> getRecordsForXLSX(InputStream xls) throws IOException {
    List<Record> records = new ArrayList<>();

    XSSFWorkbook wb = new XSSFWorkbook(xls);
    XSSFSheet sheet = wb.getSheetAt(0); // first sheet
    // loop over all rows
    for (int j = 0; j <= sheet.getLastRowNum(); j++) {
        // loop over all cells
        XSSFRow row = sheet.getRow(j);
        if (Objects.nonNull(row)) {
            for (int i = 0; i < row.getLastCellNum(); i++) {
                XSSFCell cell = row.getCell(i);
                // changing all cell types to String
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                Record record = changeCellTypeToString(cell, i, j);
                if (Objects.nonNull(record)) {
                    records.add(record);
                }//from  w w  w .j a va  2s  .  c  o  m
            }
        }
    }

    return records;
}

From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java

License:Open Source License

@Override
public int getNumberOfLastColumn(int row) {
    XSSFRow r = sheet.getRow(row);
    if (r != null) {
        return r.getLastCellNum() - 1;
    }//from   w  ww  .  ja  v a  2 s  . com
    return 0;
}

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>();

    int mergedReqionsCount = sheet.getNumMergedRegions();

    for (int i = 0; i < mergedReqionsCount; ++i) {
        newSheet.addMergedRegion(sheet.getMergedRegion(i));
    }//from  ww w. ja v  a  2  s  .  c  o m

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        XSSFRow srcRow = sheet.getRow(i);
        XSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
}

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

private void copyRow(XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
    destRow.setHeight(srcRow.getHeight());
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        XSSFCell oldCell = srcRow.getCell(j);
        XSSFCell newCell = destRow.getCell(j);
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }//  w w  w .  ja  v a2  s .com
            copyCell(oldCell, newCell, styleMap);
        }
    }
}

From source file:org.ramadda.util.XlsUtil.java

License:Apache License

/**
 * _more_//from w w  w. j  a v a  2 s . c o m
 *
 * @param filename _more_
 *
 * @return _more_
 */
public static String xlsxToCsv(String filename) {
    try {

        StringBuffer sb = new StringBuffer();
        InputStream myxls = IOUtil.getInputStream(filename, XlsUtil.class);
        XSSFWorkbook wb = new XSSFWorkbook(myxls);
        XSSFSheet sheet = wb.getSheetAt(0);
        boolean seenNumber = false;
        for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            XSSFRow row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }

            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; col < row.getLastCellNum(); col++) {
                XSSFCell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                String value = cell.toString();
                if (col > firstCol) {
                    sb.append(",");
                }
                sb.append(clean(value));
            }
            sb.append("\n");
        }

        return sb.toString();
    } catch (Exception exc) {
        throw new RuntimeException(exc);

    }
}

From source file:org.sakaiproject.tool.gradebook.ui.SpreadsheetUploadBean.java

License:Educational Community License

private String fromXSSFRowtoCSV(XSSFRow row) {
    StringBuffer csvRow = new StringBuffer();
    int l = row.getLastCellNum();
    for (int i = 0; i < l; i++) {
        XSSFCell cell = row.getCell((short) i);
        String cellValue = "";
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            cellValue = "";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            cellValue = "\"" + cell.getStringCellValue() + "\"";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            double value = cell.getNumericCellValue();
            cellValue = getNumberFormat().format(value);
            cellValue = "\"" + cellValue + "\"";
        }/*from w  ww  . j  a  va2  s .  c om*/

        csvRow.append(cellValue);

        if (i < l) {
            csvRow.append(getCsvDelimiter().toCharArray()[0]);
        }
    }
    return csvRow.toString();
}

From source file:org.talend.dataprep.qa.util.ExcelComparator.java

License:Open Source License

public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {
    if ((row1 == null) && (row2 == null)) {
        return true;
    } else if ((row1 == null) || (row2 == null)) {
        return false;
    }// w ww . ja  v a 2 s  . c  om

    int firstCell1 = row1.getFirstCellNum();
    int lastCell1 = row1.getLastCellNum();
    boolean equalRows = true;

    // Compare all cells in a row
    for (int i = firstCell1; i <= lastCell1; i++) {
        XSSFCell cell1 = row1.getCell(i);
        XSSFCell cell2 = row2.getCell(i);
        if (!compareTwoCells(cell1, cell2)) {
            equalRows = false;
            break;
        }
    }
    return equalRows;
}

From source file:org.xframium.page.data.provider.ExcelPageDataProvider.java

License:Open Source License

/**
 * Read elements./*w ww . j  a v  a  2  s .c  o m*/
 *
 * @param inputStream the input stream
 */
private void readElements(InputStream inputStream) {

    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);

        String[] tabs = tabNames.split(",");

        for (String tabName : tabs) {
            XSSFSheet sheet = workbook.getSheet(tabName);

            if (sheet == null)
                continue;

            addRecordType(tabName, false);

            XSSFRow firstRow = sheet.getRow(0);

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow currentRow = sheet.getRow(i);

                if (getCellValue(currentRow.getCell(0)) == null
                        || getCellValue(currentRow.getCell(0)).isEmpty())
                    break;

                DefaultPageData currentRecord = new DefaultPageData(tabName, tabName + "-" + i, true);
                for (int x = 0; x < firstRow.getLastCellNum(); x++) {

                    String currentName = getCellValue(firstRow.getCell(x));
                    String currentValue = getCellValue(currentRow.getCell(x));

                    if (currentValue == null)
                        currentValue = "";

                    if (currentValue.startsWith(PageData.TREE_MARKER)
                            && currentValue.endsWith(PageData.TREE_MARKER)) {
                        //
                        // This is a reference to another page data table
                        //
                        currentRecord.addPageData(currentName);
                        currentRecord.addValue(currentName + PageData.DEF, currentValue);
                        currentRecord.setContainsChildren(true);
                    } else
                        currentRecord.addValue(currentName, currentValue);
                }

                addRecord(currentRecord);
            }
        }

    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}