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

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getFirstRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getFirstRowNum

Introduction

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

Prototype

@Override
public int getFirstRowNum()

Source Link

Document

Gets the first row on the sheet

Usage

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

License:Apache License

/**
 * Gets table data./*from w w w .  jav  a2s  .c  om*/
 *
 * @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.cn.util.ExcelImport.java

/**
* ?2007excel/*from w  w w . j  ava 2s .  c o m*/
* 
* @param file
* @return
*/
private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

From source file:com.excel.javafx.frames.MainFrame.java

public boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
    sourceRow = new ArrayList<String>();
    destRow = new ArrayList<String>();
    int firstRow1 = sheet1.getFirstRowNum() + 1;
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    for (int i = firstRow1; i <= lastRow1; i++) {

        System.out.println("\n\nComparing Row " + i);

        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);
        if (!compareTwoRows(row1, row2)) {
            equalSheets = false;/*ww w  .j av  a2s. co  m*/
            System.out.println("Row " + i + " - Not Equal");
            sourceRow.add("Row" + i);
            destRow.add("Row" + i);
        } else {
            System.out.println("Row " + i + " - Equal");
        }
    }
    return equalSheets;
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

private void updateExpandedRegion(XSSFSheet sheet, int columnIndex, int expandLevel) {
    if (expandLevel < 0) {
        return;/*ww  w. j a  v  a 2  s. c  om*/
    }
    int endIndex = -1;
    for (GroupingData data : getState().colGroupingData) {
        if (data.level == expandLevel) {
            endIndex = data.endIndex;
            break;
        }
    }
    if (endIndex < 0) {
        return;
    }
    // update the style for the region cells, effects region + 1 row&col
    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();
    for (int r = firstRowNum; r <= lastRowNum; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = columnIndex; c <= endIndex; c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    valueManager.markCellForUpdate(cell);
                }
            }
        }
    }
}

From source file:FormatConvert.exceloperation.Excel2csv.java

public static void copySheets2CSV(XSSFSheet sheet, String csvfile) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap = null;

    try {/*  w  w w  .  j  a v  a  2 s.c  o  m*/
        FileWriter fw = new FileWriter(csvfile);

        String str = "";
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow srcRow = sheet.getRow(i);
            if (srcRow != null) {
                System.out.println(srcRow.getLastCellNum());
                System.out.println(srcRow.getFirstCellNum());
                //                    System.out.println(srcRow.getCell(srcRow.getLastCellNum()).toString());
                for (int j = srcRow.getFirstCellNum(); j < srcRow.getLastCellNum(); j++) {

                    if (srcRow.getCell(j) != null && j != srcRow.getLastCellNum() - 1) {
                        srcRow.getCell(j).setCellType(1);

                        str = str + srcRow.getCell(j).getReference() + ",";
                    } else if (srcRow.getCell(j) != null) {
                        srcRow.getCell(j).setCellType(1);

                        str = str + srcRow.getCell(j).getStringCellValue() + "\r\n";
                    }
                    //
                }
                fw.append(str);
            }
            str = "";
        }

        fw.flush();
        fw.close();
    } catch (IOException ex) {

    } //Util.copyPictures(newSheet,sheet) ;
}

From source file:io.unravellingtechnologies.excalibur.Sheet.java

License:Open Source License

/**
 * Initializes the sheet header structure.
 * //from  ww  w  .j  a v  a2s  .  c o m
 * @param sheet Sheet POI object used to initialize the header of this sheet.
 */
private void setSheetHeader(XSSFSheet sheet) {
    if (logger.isDebugEnabled()) {
        logger.debug("Setting sheet header...");
    }

    org.apache.poi.ss.usermodel.Row firstRow = sheet.getRow(sheet.getFirstRowNum());

    if (firstRow.getPhysicalNumberOfCells() == 0) {
        return;
    }

    for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) {
        Cell cell = it.next();

        sheetHeader.put(cell.getColumnIndex(), cell.getStringCellValue());
    }

    if (logger.isDebugEnabled()) {
        logger.debug("Finished setting the sheet header.");
    }
}

From source file:io.unravellingtechnologies.excalibur.Sheet.java

License:Open Source License

/**
 * Loads all the rows that have content into the Sheet structure.
 *//*  www  .j av a 2 s  .co m*/
private void loadRows(XSSFSheet sheet) {
    if (logger.isDebugEnabled()) {
        logger.debug("Loading sheet rows...");
    }

    if (sheet.getPhysicalNumberOfRows() < 2) {
        return;
    }

    for (Iterator<org.apache.poi.ss.usermodel.Row> rowIt = sheet.rowIterator(); rowIt.hasNext();) {
        org.apache.poi.ss.usermodel.Row tableRow = rowIt.next();

        if (tableRow.getRowNum() != sheet.getFirstRowNum()) {
            Row row = new Row(new HashMap<String, String>());

            for (Iterator<Cell> cellIt = tableRow.cellIterator(); cellIt.hasNext();) {
                Cell cell = cellIt.next();

                row.addCell(getColumnName(cell.getColumnIndex()), cell.getStringCellValue());
            }

            rows.add(row);
        }
    }

    if (logger.isDebugEnabled()) {
        logger.debug("Completed loading " + rows.size() + " rows.");
    }
}

From source file:Model.Picture.java

private static ArrayList<String> enterUploadedData(String fileName) {
    ArrayList<String> errors = new ArrayList<String>();

    try {/*  ww w.  j  ava  2s .  com*/
        FileInputStream file = new FileInputStream(Constants.TEMP_DIR + fileName);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

        int rowStart = sheet.getFirstRowNum();
        int rowEnd = sheet.getLastRowNum() + 1;
        int colStart = sheet.getRow(rowStart).getFirstCellNum();
        int colEnd = sheet.getRow(rowStart).getLastCellNum();

        int[] indices = ExcelTools.getColumnIndices(colStart, colEnd, sheet.getRow(rowStart));
        if (Tools.arrayContains(indices, -1)) {
            errors.add(Constants.IMPROPER_EXCEL_FORMAT);
            return errors;
        }

        errors.addAll(ExcelTools.readFile(indices, sheet, rowStart + 1, rowEnd));

    } catch (IOException e) {
        e.printStackTrace(System.out);
    }
    return errors;
}

From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java

License:Open Source License

/**
 * Returns only a text from .xlsx file content.
 * /*from  ww  w . j  av a 2s  . c o m*/
 * @param is an input stream with .xls file content.
 * @return The string only with text from file content.
 */
public String getContentAsText(final InputStream is) throws IOException, DocumentReadException {
    if (is == null) {
        throw new IllegalArgumentException("InputStream is null.");
    }

    StringBuilder builder = new StringBuilder("");
    SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT);

    try {
        if (is.available() == 0) {
            return "";
        }

        XSSFWorkbook wb;
        try {
            wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() {
                public XSSFWorkbook run() throws Exception {
                    return new XSSFWorkbook(is);
                }
            });
        } catch (IOException e) {
            throw new DocumentReadException("Can't open spreadsheet.", e);
        } catch (OpenXML4JRuntimeException e) {
            return builder.toString();
        }
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            XSSFSheet sheet = wb.getSheetAt(sheetNum);
            if (sheet != null) {
                for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
                    XSSFRow row = sheet.getRow(rowNum);

                    if (row != null) {
                        int lastcell = row.getLastCellNum();
                        for (int k = 0; k < lastcell; k++) {
                            XSSFCell cell = row.getCell(k);
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                case XSSFCell.CELL_TYPE_NUMERIC: {
                                    double d = cell.getNumericCellValue();
                                    if (isCellDateFormatted(cell)) {
                                        Date date = HSSFDateUtil.getJavaDate(d);
                                        String cellText = dateFormat.format(date);
                                        builder.append(cellText).append(" ");
                                    } else {
                                        builder.append(d).append(" ");
                                    }
                                    break;
                                }
                                case XSSFCell.CELL_TYPE_FORMULA:
                                    builder.append(cell.getCellFormula().toString()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_BOOLEAN:
                                    builder.append(cell.getBooleanCellValue()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_ERROR:
                                    builder.append(cell.getErrorCellValue()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_STRING:
                                    builder.append(cell.getStringCellValue().toString()).append(" ");
                                    break;
                                default:
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (IOException e) {
                if (LOG.isTraceEnabled()) {
                    LOG.trace("An exception occurred: " + e.getMessage());
                }
            }
        }
    }
    return builder.toString();
}

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));
    }// w  w w. j  a  v  a2s .co 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));
    }
}