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

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private DataFrame readData(Sheet sheet, int startRow, int startCol, int nrows, int ncols, boolean header,
        ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat,
        boolean takeCached, int[] subset) {

    DataFrame data = new DataFrame();
    int[] colset;

    // Formula evaluator - only if we don't want to take cached values
    FormulaEvaluator evaluator = null;/*ww w  . j  av a2  s  .  co m*/
    if (!takeCached) {
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.clearAllCachedResultValues();
    }

    if (subset == null) {
        colset = new int[ncols];
        for (int i = 0; i < ncols; i++) {
            colset[i] = i;
        }
    } else {
        colset = subset;
    }

    ColumnBuilder cb;
    switch (readStrategy) {
    case DEFAULT:
        cb = new DefaultColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, missingValue,
                dateTimeFormat);
        break;
    case FAST:
        cb = new FastColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, dateTimeFormat);
        break;
    default:
        throw new IllegalArgumentException("Unknown read strategy!");
    }

    // Loop over columns
    for (int col : colset) {
        int colIndex = startCol + col;
        // Determine column header
        String columnHeader = null;
        if (header) {
            Cell cell = getCell(sheet, startRow, colIndex, false);
            // Check if there actually is a cell ...
            if (cell != null) {
                if (!takeCached) {
                    CellValue cv = evaluator.evaluate(cell);
                    if (cv != null)
                        columnHeader = cv.getStringValue();
                } else {
                    columnHeader = cell.getStringCellValue();
                }
            }
        }
        // If it was specified that there is a header but an empty(/non-existing)
        // cell or cell value is found, then use a default column name
        if (columnHeader == null)
            columnHeader = "Col" + (col + 1);

        // Prepare column builder for new set of rows
        cb.clear();

        // Loop over rows
        Row r;
        for (int row = header ? 1 : 0; row < nrows; row++) {
            int rowIndex = startRow + row;

            // Cell cell = getCell(sheet, rowIndex, colIndex, false);
            Cell cell = ((r = sheet.getRow(rowIndex)) == null) ? null : r.getCell(colIndex);
            cb.addCell(cell);
        }

        DataType columnType = ((colTypes != null) && (colTypes.length > 0)) ? colTypes[col % colTypes.length]
                : cb.determineColumnType();
        switch (columnType) {
        case Boolean:
            data.addColumn(columnHeader, cb.buildBooleanColumn());
            break;
        case DateTime:
            data.addColumn(columnHeader, cb.buildDateTimeColumn());
            break;
        case Numeric:
            data.addColumn(columnHeader, cb.buildNumericColumn());
            break;
        case String:
            data.addColumn(columnHeader, cb.buildStringColumn());
            break;
        default:
            throw new IllegalArgumentException("Unknown data type detected!");

        }
        // ArrayList columnValues = cb.build(columnType);
        // data.addColumn(columnHeader, columnType, columnValues);
        // Copy warnings
        for (String w : cb.retrieveWarnings())
            this.addWarning(w);
    }

    return data;
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private Cell getCell(Sheet sheet, int rowIndex, int colIndex, boolean create) {
    // Get or create row
    Row row = sheet.getRow(rowIndex);
    if (row == null) {
        if (create) {
            row = sheet.createRow(rowIndex);
        } else/*  ww w .  ja v  a  2s.  c  o  m*/
            return null;
    }
    // Get or create cell
    Cell cell = row.getCell(colIndex);
    if (cell == null) {
        if (create) {
            cell = row.createCell(colIndex);
        } else
            return null;
    }

    return cell;
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void clearRange(int sheetIndex, int[] coords) {
    Sheet sheet = getSheet(sheetIndex);/*from w  w w . j  a v  a  2  s  . c o m*/
    for (int i = coords[0]; i <= coords[2]; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            continue;
        for (int j = coords[1]; j <= coords[3]; j++) {
            Cell cell = row.getCell(j);
            if (cell != null)
                row.removeCell(cell);
        }
        if (row.getLastCellNum() < 0)
            sheet.removeRow(row);
    }
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public int[] getBoundingBox(int sheetIndex, int startRow, int startCol, int endRow, int endCol,
        boolean autofitRow, boolean autofitCol) {
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    final int mark = Integer.MAX_VALUE - 1;

    if (startRow < 0) {
        startRow = sheet.getFirstRowNum();
        if (sheet.getRow(startRow) == null) {
            // There is no row in this sheet
            startRow = -1;/*from  w ww  .j a v a  2  s  . c om*/
        }
    }

    if (endRow < 0) {
        endRow = sheet.getLastRowNum();
        if (sheet.getRow(endRow) == null) {
            // There is no row in this sheet
            endRow = -1;
        }
    }

    int minRow = startRow;
    int maxRow = endRow;
    int minCol = startCol;
    int maxCol = endCol < 0 ? mark : endCol;

    startCol = startCol < 0 ? mark : startCol;
    endCol = endCol < 0 ? -1 : endCol;
    Cell topLeft = null, bottomRight = null;
    boolean anyCell = false;
    for (int i = minRow; i > -1 && i <= maxRow; i++) {
        Row r = sheet.getRow(i);
        if (r != null) {
            // Determine column boundaries
            int start = Math.max(minCol, r.getFirstCellNum());
            int end = Math.min(maxCol + 1, r.getLastCellNum()); // NOTE: getLastCellNum is 1-based!
            boolean anyNonBlank = false;
            for (int j = start; j > -1 && j < end; j++) {
                Cell c = r.getCell(j);
                if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    anyCell = true;
                    anyNonBlank = true;
                    if ((autofitCol || minCol < 0) && (topLeft == null || j < startCol)) {
                        startCol = j;
                        topLeft = c;
                    }
                    if ((autofitCol || maxCol == mark) && (bottomRight == null || j > endCol)) {
                        endCol = j;
                        bottomRight = c;
                    }
                }
            }
            if (autofitRow && anyNonBlank) {
                endRow = i;
                if (sheet.getRow(startRow) == null) {
                    startRow = i;
                }
            }
        }
    }

    if ((autofitRow || startRow < 0) && !anyCell) {
        startRow = endRow = -1;
    }
    if ((autofitCol || startCol == mark) && !anyCell) {
        startCol = endCol = -1;
    }

    return new int[] { startRow, startCol, endRow, endCol };
}

From source file:com.mto.excel.model.MergeWorkbook.java

License:Open Source License

private void addRow(Row row, int cellOffset) {
    Row newRow = sheet.createRow(rowOffset);
    rowOffset++;//from   w ww  .  j a  va2 s.  c o m
    for (int i = cellOffset; i < row.getLastCellNum(); i++) {
        Cell c = row.getCell(i);
        if (c == null) {
            continue;
        }

        Cell newCell = newRow.createCell(i, c.getCellType());

        switch (c.getCellType()) {
        case CELL_TYPE_STRING:
            newCell.setCellValue(helper.createRichTextString(c.getStringCellValue()));
            break;
        case CELL_TYPE_BOOLEAN:
            newCell.setCellValue(c.getBooleanCellValue());
            break;
        case CELL_TYPE_NUMERIC:
            newCell.setCellValue(c.getNumericCellValue());
            break;
        default:
            return;
        }
    }
}

From source file:com.murilo.excel.ExcelHandler.java

public String getLine(int sheetIndex, int lineIndex, char separator) {
    Row linha = sheets[sheetIndex].getRow(lineIndex);
    String aux = "";
    for (int i = linha.getFirstCellNum(); i < linha.getLastCellNum(); i++) {
        Cell campo = linha.getCell(i);
        if ((i + 1) != linha.getLastCellNum()) {
            aux = aux + "\"" + stringrizeCell(campo) + "\"" + separator;
        } else {//  w  w w.  j  a va 2 s .c o  m
            aux = aux + "\"" + stringrizeCell(campo) + "\"\n";
        }
    }

    return aux;
}

From source file:com.mycompany.asyncreq.Main.java

@SuppressWarnings("empty-statement")
public static ArrayList<String> GenData() {

    ArrayList<String> rootobject = new ArrayList<String>();

    try {/* w ww. j  ava2s . co  m*/

        FileInputStream file = new FileInputStream(new File("src/main/resources/Eu3.xlsx"));
        FileOutputStream ffout = new FileOutputStream(new File("trade.json"));
        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows from first sheet
        Iterator rowIterator = sheet.iterator();
        Root r;
        String addr;
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();

            //For each row, iterate through each columns
            Iterator cellIterator = row.cellIterator();

            Cell cell = (Cell) row.getCell(0);

            switch (cell.getCellType()) {

            case Cell.CELL_TYPE_NUMERIC:
                addr = "http://comtrade.un.org/api/get?max=50000&type=C&freq=M&px=HS&ps=2014&r=804&p="
                        + (int) cell.getNumericCellValue() + "&rg=All&cc=All&fmt=json";

                rootobject.add(addr);
                break;

            }

        }

    } catch (IOException e) {
        e.printStackTrace();
    }

    return rootobject;
}

From source file:com.mycompany.mavenproject1.MainExecutor.java

static Map<String, Object[]> getNewWorkbookData(HSSFWorkbook inputExcel, Iterator<String> playersIterator) {
    Map<String, Object[]> data = new HashMap<String, Object[]>();
    data.put("1", new Object[] { "Player", "FSP.1", "FSW.1", "SSP.1", "SSW.1", "ACE.1", "DBF.1", "WNR.1",
            "UFE.1", "BPC.1", "BPW.1", "NPA.1", "NPW.1" });
    int key = 2;//w w  w .  ja va2  s.  com
    while (playersIterator.hasNext()) {
        String player = playersIterator.next();
        for (int i = 0; i < inputExcel.getNumberOfSheets(); i++) {
            Iterator<Row> rowIterator = inputExcel.getSheetAt(i).iterator();
            rowIterator.next();
            int row_counter = 0;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Object[] rowValues = new Object[13];

                String player_1_cell = row.getCell(0).getStringCellValue();
                String player_2_cell = row.getCell(1).getStringCellValue();
                int dataRangeLow = 0;
                int dataRangeHigh = 0;

                if (player.equals(player_1_cell)) {
                    dataRangeLow = 6;
                    dataRangeHigh = 17;
                    rowValues[0] = player;
                } else if (player.equals(player_2_cell)) {
                    dataRangeLow = 24;
                    dataRangeHigh = 35;
                    rowValues[0] = player;
                } else {
                    // Go to another row.
                }
                int cell_counter = 0;
                for (int j = dataRangeLow; j < dataRangeHigh; j++) {
                    Cell cell = row.getCell(j);
                    System.out.println(cell_counter);
                    if (cell != null)
                        rowValues[j - dataRangeLow + 1] = cell.getNumericCellValue();

                    if (cell_counter == 5) {
                        Object debug = new Object();
                    }
                    cell_counter++;
                    data.put("" + key, rowValues);
                    key++;
                }

                System.out.println(row_counter);
                row_counter++;
            }
        }
    }

    return data;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>//  w w  w  .j  ava 2s .  c o m
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? )
 * </pre>
 *
 * @method Name : readExcel
 * @param strFullFilePath, serviceType
 * @return List<Map<String, Object>>
 * @throws Exception
 * 
 */
@SuppressWarnings("deprecation")
public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    Map<String, Object> map = null;

    try {
        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */

        /* Sheet  ? ,  ?  ??  */
        /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */
        for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1  ? */

            Sheet sheet = wb.getSheetAt(sheetIdx);

            /*   ?? ?    ?,   */

            /* row  ?  */
            int cellCount = 0;
            for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

                Row row = sheet.getRow(rowIdx);
                cellCount = row.getLastCellNum();
                map = new HashMap<String, Object>();

                if (row != null) {
                    // cell  ? 
                    for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                        Cell cell = row.getCell(cellIdx);
                        if (cell != null) {

                            int cellType = cell.getCellType();
                            String value = "";

                            //  WBS  ? ? ??
                            if (serviceType.equals("order")) {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                        value = formatter.format(cell.getDateCellValue());
                                    } else {
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        value = cell.getStringCellValue();
                                    }
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                    ;
                                }
                            } else {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    value = cell.getNumericCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                }
                            }
                            map.put("colName" + cellIdx, value);
                        } else {
                            map.put("colName" + cellIdx, "");
                        }
                    }

                    list.add(map);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>/*from w w  w  .  j av  a2  s  .c om*/
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? ), sheet  ?
 * </pre>
 *
 * @method Name : readExcelMulti
 * @param strFullFilePath, serviceType, sheetNo
 * @return 
 * @throws
 * 
 */
@SuppressWarnings("deprecation")
public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType,
        int sheetNo) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
    LinkedHashMap<String, Object> map = null;

    try {
        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= file path : {} =", strFullFilePath);
            log.debug("= tmp file  : {} =", tmpFile);
            log.debug(
                    "==========================================================================================");
        }

        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */

        Sheet sheet = wb.getSheetAt(sheetNo);

        /* row  ?  */
        int cellCount = 0;
        for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

            Row row = sheet.getRow(rowIdx);
            cellCount = row.getLastCellNum();
            map = new LinkedHashMap<String, Object>();

            if (rowIdx == 0) {
                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= sheet no  : {} =", sheetNo);
                    log.debug("= row count : {} =", sheet.getLastRowNum());
                    log.debug("= col count : {} =", cellCount);
                    log.debug(
                            "==================================================================================");
                }
            }

            if (row != null) {
                // cell  ? 
                for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                    Cell cell = row.getCell(cellIdx);
                    if (cell != null) {

                        int cellType = cell.getCellType();
                        String value = "";

                        switch (cellType) {
                        case HSSFCell.CELL_TYPE_FORMULA: //??
                            value = cell.getStringCellValue();//cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC://?
                            value = cell.getNumericCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_STRING: //?
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: //
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: //BYTE
                            value = cell.getErrorCellValue() + "";
                            break;

                        default:
                        }

                        map.put("item" + String.valueOf(cellIdx), value);
                    } else {
                        map.put("item" + String.valueOf(cellIdx), "");
                    }
                }

                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= map : {} =", map);
                    log.debug(
                            "==================================================================================");
                }

                list.add(map);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}