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

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

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

Introduction

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

Prototype

@Override
public short getFirstCellNum()

Source Link

Document

Get the 0-based number of the first cell contained in this row.

Usage

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

License:Apache License

/**
 * Gets table data./*  ww w.j  ava2 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.cn.util.ExcelImport.java

/**
* ?2007excel/*from  w ww  .  j  av a2 s  .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:FormatConvert.exceloperation.Excel2csv.java

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

    try {//  w w w. ja  va2 s  .  co  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: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);
            }//from w  ww .  j a  va2  s  .c o  m
            copyCell(oldCell, newCell, styleMap);
        }
    }
}

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

License:Apache License

/**
 * _more_//from w w  w.j  av a 2s.co  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.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  w  w .j a va2s . co  m

    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:steffen.haertlein.file.FileObject.java

License:Apache License

private void readExcelDocument() {
    try {//from ww  w . j  a  v  a2 s .c om
        FileInputStream fs = new FileInputStream(f);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sh;
        String text = "";
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            sh = wb.getSheetAt(i);
            for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) {
                XSSFRow currRow = sh.getRow(j);
                if (currRow == null || currRow.getFirstCellNum() == -1) {
                    continue;
                } else {
                    for (int k = currRow.getFirstCellNum(); k < currRow.getLastCellNum(); k++) {
                        if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) {
                            continue;
                        } else {
                            text += currRow.getCell(k) + "; ";
                        }
                    }
                    text += System.lineSeparator();
                }
            }
        }
        fs.close();
        wb.close();
        String[] xlsxLines = text.split(System.lineSeparator());
        for (String line : xlsxLines) {
            lines.add(line);
        }
    } catch (IOException e) {
        JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument", "Fehler", JOptionPane.ERROR_MESSAGE);
        e.printStackTrace();
    }
}