Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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

public <T> Workbook batchImportFailList(String modelPath, List<T> objectList, Class<T> modelClass,
        Map<String, String> columnMap) throws Exception {
    //??excel//from   w w  w  . ja va  2 s  . c om
    File localfile = new File("D:\\lw7068\\Desktop\\ (4)\\??.xlsx");
    InputStream in = new FileInputStream(localfile);
    // ?
    Workbook book = new XSSFWorkbook(in);
    // ?sheet
    Sheet sheet = book.getSheetAt(0);
    //
    Row titleRow = sheet.getRow(0);
    //???index
    Map<String, Integer> rowIndex = getTitleRowIndex(titleRow);
    //???method
    Map<Integer, Method> methodsIndex = getRowIndexAndGetMethod(titleRow, rowIndex, modelClass, columnMap);
    for (int i = 0; i < objectList.size(); i++) {
        //excelindex   ??? 
        Map<Integer, String> dataMap = getRowIndexAndData(titleRow, objectList.get(i), methodsIndex);
        //
        Row row = sheet.createRow(i + 1);
        //
        for (Map.Entry<Integer, String> entry : dataMap.entrySet()) {
            Cell cell = row.createCell(entry.getKey());
            cell.setCellValue(entry.getValue());
        }
    }
    return book;
}

From source file:com.mimp.controllers.reporte.java

private static void copyRowStyle(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {//  w ww . j  av a2s  .  c  om
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            //            switch (oldCell.getCellType()) {
            //                case Cell.CELL_TYPE_BLANK:
            //                    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;
            //            }
        }
    }

From source file:com.mimp.controllers.reporte.java

private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {//from   w  w  w . ja va2s  . co  m
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                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;
            }
        }
    }

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;//from w w w  .j  a  v a 2  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

public void setRowHeight(int sheetIndex, int rowIndex, float height) {
    Sheet sheet = getSheet(sheetIndex);
    Row r = sheet.getRow(rowIndex);
    if (r == null)
        r = getSheet(sheetIndex).createRow(rowIndex);

    if (height >= 0)
        r.setHeightInPoints(height);//w  w  w . j  ava2  s  .c  om
    else
        r.setHeightInPoints(sheet.getDefaultRowHeightInPoints());
}

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/*from   w  w  w.  java2s.  com*/
            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 int getLastColumn(Sheet sheet) {
    int lastRow = sheet.getLastRowNum();
    int lastColumn = 1;
    for (int i = 0; i < lastRow; ++i) {
        Row row = sheet.getRow(i);
        if (row != null) {
            int col = row.getLastCellNum();
            if (col > lastColumn) {
                lastColumn = col;// w  w  w .j a  v  a2 s.  com
            }
        }
    }
    return lastColumn - 1;
}

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

License:Open Source License

public void appendWorksheet(DataFrame data, int worksheetIndex, boolean header) {
    Sheet sheet = getSheet(worksheetIndex);
    int lastRow = getLastRow(worksheetIndex);
    int firstCol = Integer.MAX_VALUE;
    for (int i = 0; i < lastRow && firstCol > 0; i++) {
        Row row = sheet.getRow(i);
        if (row != null && row.getFirstCellNum() < firstCol)
            firstCol = row.getFirstCellNum();
    }/*from  w w w.j  av a  2s .  c  o  m*/
    if (firstCol == Integer.MAX_VALUE)
        firstCol = 0;

    writeWorksheet(data, worksheetIndex, getLastRow(worksheetIndex) + 1, firstCol, header);
}

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

License:Open Source License

public void clearSheet(int sheetIndex) {
    Sheet sheet = getSheet(sheetIndex);
    int firstRow = sheet.getFirstRowNum();
    int lastRow = sheet.getLastRowNum();
    for (int i = lastRow; i >= firstRow; i--) {
        Row r = sheet.getRow(i);
        if (r != null)
            sheet.removeRow(r);//  w w w  . j  av a  2  s.c  o m
    }
}

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

License:Open Source License

public void clearRange(int sheetIndex, int[] coords) {
    Sheet sheet = getSheet(sheetIndex);
    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);//from ww  w. java  2s. c  om
            if (cell != null)
                row.removeCell(cell);
        }
        if (row.getLastCellNum() < 0)
            sheet.removeRow(row);
    }
}