Example usage for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum

Introduction

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

Prototype

@Override
    public int getLastRowNum() 

Source Link

Usage

From source file:com.adobe.acs.commons.mcp.util.Spreadsheet.java

License:Apache License

/**
 * Parse out the input file synchronously for easier unit test validation
 *
 * @return List of files that will be imported, including any renditions
 * @throws IOException if the file couldn't be read
 *///  w w  w. j a  v  a 2  s .  com
private void parseInputFile(InputStream file) throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook(file);

    final XSSFSheet sheet = workbook.getSheetAt(0);
    rowCount = sheet.getLastRowNum();
    final Iterator<Row> rows = sheet.rowIterator();

    headerRow = readRow(rows.next()).stream().map(this::convertHeaderName).collect(Collectors.toList());

    Iterable<Row> remainingRows = () -> rows;
    dataRows = StreamSupport.stream(remainingRows.spliterator(), false).map(this::buildRow)
            .filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
}

---

From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java

private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum,
        XSSFSheet destinationWorksheet, int destinationRowNum) {
    // Get the source / new row
    XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row

    if (newRow != null) {
        destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1);
    } else {//  w  w  w.  j  a  va  2  s .  c o m
        newRow = destinationWorksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        //newCell.setCellValue(oldCell.getRawValue());
        //newCell.setCellType(oldCell.getCellType());                        

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

    // If there are are any merged regions in the source row, copy to new row
    /*
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
    CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
    if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
        CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                (newRow.getRowNum() +
                        (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                )),
                cellRangeAddress.getFirstColumn(),
                cellRangeAddress.getLastColumn());
        worksheet.addMergedRegion(newCellRangeAddress);
    }            
    }
    */
}

---

From source file:com.atanas.kanchev.testframework.dataservices.dataprovider.excel.ExcelParser.java

License:Apache License

/**
 * Gets table data./*from  w  w  w .j a v  a2 s  . c  o m*/
 *
 * @param sheetName the sheet name
 * @return the table data
 */
public synchronized List<Map<String, Object>> getTableData(String sheetName) {

    XSSFSheet sheet = workbook.getSheet(sheetName);
    int totalColumns = sheet.getRow(0).getLastCellNum();
    logger.debug("Number of columns: " + totalColumns);
    logger.debug(
            "Number of data rows: ".concat(String.valueOf(sheet.getLastRowNum() - sheet.getFirstRowNum())));
    List<String> header = getHeaders(sheetName);
    List<Map<String, Object>> data = new LinkedList<>();
    LinkedHashMap<String, Object> xlData;

    for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
        xlData = new LinkedHashMap<>();
        XSSFRow dRow = sheet.getRow(i);

        if (null == dRow) {
            logger.warn("Empty row, exiting excel reader");
            break;
        }

        for (int j = dRow.getFirstCellNum(); j < totalColumns; j++) {

            switch (dRow.getCell(j).getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                xlData.put(header.get(j), dRow.getCell(j).getStringCellValue());
                logger.debug("Data in cell " + dRow.getCell(j).getAddress() + " : "
                        + dRow.getCell(j).getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                xlData.put(header.get(j), dRow.getCell(j).getNumericCellValue());
                logger.debug("Data in cell " + dRow.getCell(j).getAddress() + " : "
                        + dRow.getCell(j).getNumericCellValue());
                break;
            }
        }
        data.add(xlData);
    }
    return data;
}

---

From source file:com.centurylink.mdw.drools.Excel2007Parser.java

License:Apache License

@SuppressWarnings("deprecation")
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }//from w  w  w .j ava2s .  c  o  m

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode()
                                ? String.valueOf(topLeft.getNumericCellValue())
                                : topLeft.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

---

From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java

License:Apache License

private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }// ww w .j  av a 2 s.c o m

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        newCell(listeners, i, cellNum, topLeft.getStringCellValue(), topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

---

From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java

License:Apache License

private void readXmlBasedExcel(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data)
        throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Cell cell = null;/*from   w ww  .  j  av a  2 s  .  c o  m*/

        List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            Map<String, String> columnData = new HashMap<String, String>();

            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);

                columnData.put("column" + (cellIndex + 1), cell.toString());
            }

            sheetData.add(columnData);
        }

        data.put("sheet" + (sheetIndex + 1), sheetData);
    }

}

---

From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java

License:Apache License

@SuppressWarnings("rawtypes")
private void readXmlBasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int index = 0; index < sheetCount; index++) {
        XSSFSheet sheet = workbook.getSheetAt(index);

        Sheet s = new Sheet();

        sheets.add(s);/*w w  w  . j a v a2s.  co m*/

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);

            Record record = new Record();

            s.addRecord(record);

            Iterator it = row.cellIterator();

            while (it.hasNext()) {
                record.addCellValue(it.next());
            }
        }
    }

}

---

From source file:com.dao.DatabaseDao.java

public static void insertFromFile(MainForm mf, String sect, String destpath, String fname, Statement st,
        Connection c, long maxRecID, ArrayList<Long> arl) throws Exception {

    FileInputStream fin = new FileInputStream(destpath);
    XSSFWorkbook wb = new XSSFWorkbook(fin);
    XSSFSheet sheet = wb.getSheet("EmployeeInfo");
    long lastrowno = sheet.getLastRowNum();
    XSSFRow row;/* w w  w  .  j  a v a 2 s .  c o  m*/

    long i = 1;
    while (i <= lastrowno) {
        row = sheet.getRow((int) i);

        long id = (long) row.getCell(0).getNumericCellValue();//ID
        String name = row.getCell(1).getStringCellValue();//NAME
        long recp_no = (long) row.getCell(2).getNumericCellValue();//RECEIPT_NO
        Date edate = (Date) row.getCell(3).getDateCellValue();//ENTRY_DATE
        int subrate = (int) row.getCell(4).getNumericCellValue();//SUB_RATE
        int jan = (int) row.getCell(5).getNumericCellValue();//JAN
        int feb = (int) row.getCell(6).getNumericCellValue();//FEB
        int mar = (int) row.getCell(7).getNumericCellValue();//MAR
        int apr = (int) row.getCell(8).getNumericCellValue();//APR
        int may = (int) row.getCell(9).getNumericCellValue();//MAY
        int jun = (int) row.getCell(10).getNumericCellValue();//JUN
        int jul = (int) row.getCell(11).getNumericCellValue();//JUL
        int aug = (int) row.getCell(12).getNumericCellValue();//AUG
        int sep = (int) row.getCell(13).getNumericCellValue();//SEP
        int oct = (int) row.getCell(14).getNumericCellValue();//OCT
        int nov = (int) row.getCell(15).getNumericCellValue();//NOV
        int dec = (int) row.getCell(16).getNumericCellValue();//DECB
        long tot = (long) row.getCell(17).getNumericCellValue();//TOTAL
        String remark = row.getCell(18).getStringCellValue();//REMARK
        String sector = row.getCell(19).getStringCellValue();//SECTOR
        String sub_frm = row.getCell(20).getStringCellValue();//SUB_FROM
        String sub_to = row.getCell(21).getStringCellValue();//SUB_TO
        String place = row.getCell(22).getStringCellValue();//PLACE
        boolean isAlready = arl.contains(recp_no);

        Employee emp = new Employee();
        emp.setName(name);
        emp.setEntry_date(edate);
        emp.setSub_rate(subrate);
        emp.setJan(jan);
        emp.setFeb(feb);
        emp.setMar(mar);
        emp.setApr(apr);
        emp.setMay(may);
        emp.setJun(jun);
        emp.setJul(jul);
        emp.setAug(aug);
        emp.setSep(sep);
        emp.setOct(oct);
        emp.setNov(nov);
        emp.setDecb(dec);
        emp.setTotal(tot);
        emp.setRemark(remark);
        emp.setSector(sector);
        emp.setSub_from(sub_frm);
        emp.setSub_to(sub_to);
        emp.setPlace(place);
        if (isAlready) {
            emp.setReceipt_no(maxRecID);
            maxRecID++;
        } else {
            emp.setReceipt_no(recp_no);
        }
        EmployeeDao.save(emp);

        i++;
    }

    st = c.createStatement();
    st.execute("INSERT INTO IMPORTFILE_INFO(NAME) VALUES('" + fname + "')");

    mf.initData(sect);
    JOptionPane.showMessageDialog(null, "Database Import Successfully...!!");
}

---

From source file:com.denimgroup.threadfix.csv2ssl.parser.FormatParser.java

License:Mozilla Public License

public static Option<String[]> getHeadersExcel(File file) {
    try {/*w ww .  j av a  2s  .c o m*/
        FileInputStream fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);

        XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
        int totalRows = ws.getLastRowNum();

        if (totalRows == 0) {
            return Option.failure("No lines found in file " + file.getName());
        }

        XSSFRow row = ws.getRow(0);

        String[] headers = new String[row.getLastCellNum()];

        for (int index = 0; index < row.getLastCellNum(); index++) {
            XSSFCell cell = row.getCell(index);

            assert cell != null : "Got null cell at index " + index;

            headers[index] = cell.toString();
        }

        return Option.success(headers);

    } catch (IOException e) {
        e.printStackTrace();
        return Option.failure("Encountered IOException.");
    }
}

---

From source file:com.denimgroup.threadfix.csv2ssl.serializer.RecordToXMLSerializer.java

License:Mozilla Public License

public static String getFromExcel(XSSFWorkbook wb, String... format) {
    StringBuilder builder = getStart();

    int line = Configuration.CONFIG.shouldSkipFirstLine ? 1 : 0;

    XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
    int totalColumns = ws.getRow(0).getLastCellNum();
    int totalRows = ws.getLastRowNum();
    Map<String, String> rowMap = map();

    for (; line <= totalRows; line++) { // we want <= because the index returned from ws.getLastRowNum() is valid
        XSSFRow row = ws.getRow(line);/*from   w  w w  .j a  v a 2s .com*/

        for (int column = 0; column < totalColumns; column++) {
            XSSFCell cell = row.getCell(column);

            if (cell == null) {
                // cells are null if there's no data in them; this is fine.
                continue;
            }

            String value = cell.toString();

            if (format.length > column) {
                rowMap.put(format[column], value);
            } else {
                System.err.println("format wasn't long enough for column. Column length = " + totalColumns
                        + ", format was " + format.length);
            }
        }

        addRecord(builder, line, rowMap);
        rowMap.clear();
    }

    return writeEnd(builder);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• »