Example usage for org.apache.poi.ss.usermodel Cell getRowIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getRowIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getRowIndex

Introduction

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

Prototype

int getRowIndex();

Source Link

Document

Returns row index of a row in the sheet that contains this cell

Usage

From source file:th.co.aoe.makedev.missconsult.exam.service.impl.ReadWriteWorkbook_bk.java

License:Apache License

public static List<MissQuestion> setQuestion() {
    //???/*from  w w w  .j a  va  2s  .c  o  m*/
    FileInputStream fileIn = null;
    //  FileOutputStream fileOut = null;
    List<MissQuestion> missQuestions = new ArrayList<MissQuestion>();
    try {
        try {
            //fileIn = new FileInputStream("/usr/local/data/Work/PROJECT/MakeDev/Exam/Question_Test/Service_Attitude.xls"); // ok
            //fileIn = new FileInputStream("/usr/local/data/Work/PROJECT/MakeDev/Exam/Question_Test/EPT_PLUS_THAI.xls");// ok
            //fileIn = new FileInputStream("/usr/local/data/Work/PROJECT/MakeDev/Exam/Question_Test/The4Factors_1.xls"); // ok
            //   fileIn = new FileInputStream("/usr/local/data/Work/PROJECT/MakeDev/Exam/Question_Test/The4Factors_2.xls"); // ok
            //fileIn = new FileInputStream("/usr/local/data/Work/PROJECT/MakeDev/Exam/Question_Test/LeadershipAssessment_1.xls"); // ok
            fileIn = new FileInputStream(
                    "/usr/local/data/Work/PROJECT/MakeDev/Exam/Question_Test/LeadershipAssessment_2.xls"); // ok
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        POIFSFileSystem fs = null;
        try {
            fs = new POIFSFileSystem(fileIn);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                //System.out.println("row id="+row.getRowNum()+"");
                MissQuestion missQuestion = new MissQuestion();
                //int rowId=row.getRowNum();
                //if(rowId>0){
                //   ThaiCustomUser user =new ThaiCustomUser();
                for (Cell cell : row) {

                    int columnIndex = cell.getColumnIndex();
                    String value = "";
                    //System.out.println("  row id="+cell.getRowIndex()+",column id="+columnIndex+"");  

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        if (columnIndex == 0) {
                            value = cell.getStringCellValue();
                            //    System.out.println("      CELL_TYPE_STRING="+value);
                            missQuestion.setMqId(Long.parseLong((cell.getRowIndex() + 1) + ""));
                            missQuestion.setMqNameTh1(value);
                            missQuestions.add(missQuestion);
                        }
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // System.out.println("      CELL_TYPE_NUMERIC DATE="+cell.getDateCellValue());
                        } else {
                            double valuecel = cell.getNumericCellValue();
                            NumberFormat format = NumberFormat.getNumberInstance();
                            // format.setMaximumIntegerDigits(99);
                            format.setGroupingUsed(false);

                            // System.out.println("      CELL_TYPE_NUMERIC="+format.format(valuecel));
                            value = format.format(valuecel);
                            //System.out.println("      CELL_TYPE_NUMERIC="+Double.toString(cell.getNumericCellValue()));
                            // System.out.println("      CELL_TYPE_NUMERIC="+cell.getNumericCellValue());
                            /* Date    date    = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                                DateFormat  format2  = new     SimpleDateFormat("yyyyMMdd");
                               System.out.println("      CELL_TYPE_NUMERIC date="+format2.format(date));
                            */
                        }
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        // System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        // System.out.println("yy="+cell.getCellFormula());
                        break;
                    default:
                        //  System.out.println();
                    }
                }
            }
            // } 
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    } finally {

        if (fileIn != null)
            try {
                fileIn.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
    return missQuestions;
}

From source file:uk.co.spudsoft.birt.emitters.excel.handlers.PageHandler.java

License:Open Source License

/**
 * <p>/*w  ww  .j a v  a  2s  .c  o  m*/
 * Process a CellImage from the images list and place the image on the sheet.
 * </p><p>
 * This involves changing the row height as necesssary and determining the column spread of the image.
 * </p>
 * @param cellImage
 * The image to be placed on the sheet.
 */
private void processCellImage(HandlerState state, Drawing drawing, CellImage cellImage) {
    Coordinate location = cellImage.location;

    Cell cell = state.currentSheet.getRow(location.getRow()).getCell(location.getCol());

    IImageContent image = cellImage.image;

    StyleManagerUtils smu = state.getSmu();
    float ptHeight = cell.getRow().getHeightInPoints();
    if (image.getHeight() != null) {
        ptHeight = smu.fontSizeInPoints(image.getHeight().toString());
    }

    // Get image width
    int endCol = cell.getColumnIndex();
    double lastColWidth = ClientAnchorConversions
            .widthUnits2Millimetres((short) state.currentSheet.getColumnWidth(endCol)) + 2.0;
    int dx = smu.anchorDxFromMM(lastColWidth, lastColWidth);
    double mmWidth = 0.0;
    if (smu.isAbsolute(image.getWidth())) {
        mmWidth = image.getWidth().convertTo(DimensionType.UNITS_MM);
    } else if (smu.isPixels(image.getWidth())) {
        mmWidth = ClientAnchorConversions.pixels2Millimetres(image.getWidth().getMeasure());
    }
    // Allow image to span multiple columns
    CellRangeAddress mergedRegion = getMergedRegionBegunBy(state.currentSheet, location.getRow(),
            location.getCol());
    if ((cellImage.spanColumns) || (mergedRegion != null)) {
        log.debug("Image size: ", image.getWidth(), " translates as mmWidth = ", mmWidth);
        if (mmWidth > 0) {
            double mmAccumulatedWidth = 0;
            int endColLimit = cellImage.spanColumns ? 256 : mergedRegion.getLastColumn();
            for (endCol = cell.getColumnIndex(); mmAccumulatedWidth < mmWidth
                    && endCol < endColLimit; ++endCol) {
                lastColWidth = ClientAnchorConversions
                        .widthUnits2Millimetres((short) state.currentSheet.getColumnWidth(endCol)) + 2.0;
                mmAccumulatedWidth += lastColWidth;
                log.debug("lastColWidth = ", lastColWidth, "; mmAccumulatedWidth = ", mmAccumulatedWidth);
            }
            if (mmAccumulatedWidth > mmWidth) {
                mmAccumulatedWidth -= lastColWidth;
                --endCol;
                double mmShort = mmWidth - mmAccumulatedWidth;
                dx = smu.anchorDxFromMM(mmShort, lastColWidth);
            }
        }
    } else {
        float widthRatio = (float) (mmWidth / lastColWidth);
        ptHeight = ptHeight / widthRatio;
    }

    int rowsSpanned = state.findRowsSpanned(cell.getRowIndex(), cell.getColumnIndex());
    float neededRowHeightPoints = ptHeight;

    for (int i = 0; i < rowsSpanned; ++i) {
        int rowIndex = cell.getRowIndex() + 1 + i;
        neededRowHeightPoints -= state.currentSheet.getRow(rowIndex).getHeightInPoints();
    }

    if (neededRowHeightPoints > cell.getRow().getHeightInPoints()) {
        cell.getRow().setHeightInPoints(neededRowHeightPoints);
    }

    // ClientAnchor anchor = wb.getCreationHelper().createClientAnchor();
    ClientAnchor anchor = state.getWb().getCreationHelper().createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setRow1(cell.getRowIndex());
    anchor.setCol2(endCol);
    anchor.setRow2(cell.getRowIndex() + rowsSpanned);
    anchor.setDx2(dx);
    anchor.setDy2(smu.anchorDyFromPoints(ptHeight, cell.getRow().getHeightInPoints()));
    anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
    drawing.createPicture(anchor, cellImage.imageIdx);
}

From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerUtils.java

License:Open Source License

public void extendRows(HandlerState state, int startRow, int startCol, int endRow, int endCol) {
    for (int colNum = startCol; colNum < endCol; ++colNum) {
        Cell lastCell = null;
        for (int rowNum = startRow; rowNum < endRow; ++rowNum) {
            Row row = state.currentSheet.getRow(rowNum);
            if (row != null) {
                Cell cell = row.getCell(colNum);
                if (cell != null) {
                    lastCell = cell;//from w  w  w  . j  a va  2s  .  c o m
                }
            }
        }
        if ((lastCell != null) && (lastCell.getRowIndex() < endRow - 1)) {
            CellRangeAddress range = new CellRangeAddress(lastCell.getRowIndex(), endRow - 1,
                    lastCell.getColumnIndex(), lastCell.getColumnIndex());
            log.debug("Extend: merging from [", range.getFirstRow(), ",", range.getFirstColumn(), "] to [",
                    range.getLastRow(), ",", range.getLastColumn(), "]");
            state.currentSheet.addMergedRegion(range);
            for (int rowNum = lastCell.getRowIndex() + 1; rowNum < endRow; ++rowNum) {
                Row row = state.currentSheet.getRow(rowNum);
                if (row == null) {
                    log.error(0,
                            "Creating a row (for column " + colNum + "), this really shouldn't be necessary",
                            null);
                    row = state.currentSheet.createRow(rowNum);
                }
                Cell cell = row.createCell(colNum);
                cell.setCellStyle(lastCell.getCellStyle());
            }
        }
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.CellRangeTester.java

License:Open Source License

protected void validateCellRange(Matcher matcher, Cell cell) {
    int desiredR1 = Integer.parseInt(matcher.group(1));
    int desiredC1 = Integer.parseInt(matcher.group(2));
    int desiredR2 = Integer.parseInt(matcher.group(3));
    int desiredC2 = Integer.parseInt(matcher.group(4));

    int actualR1 = cell.getRowIndex() + 1;
    int actualC1 = cell.getColumnIndex() + 1;
    int actualR2 = actualR1;
    int actualC2 = actualC1;

    for (int i = 0; i < cell.getSheet().getNumMergedRegions(); ++i) {
        CellRangeAddress cra = cell.getSheet().getMergedRegion(i);
        if ((cra.getFirstRow() == cell.getRowIndex()) && (cra.getFirstColumn() == cell.getColumnIndex())) {
            assertEquals(actualR1, actualR2);
            assertEquals(actualC1, actualC2);
            actualR2 = cra.getLastRow() + 1;
            actualC2 = cra.getLastColumn() + 1;
        }/*ww w .  j  a va2 s .  co  m*/
    }
    assertEquals(desiredR1, actualR1);
    assertEquals(desiredC1, actualC1);
    assertEquals(desiredR2, actualR2);
    assertEquals(desiredC2, actualC2);
}

From source file:uk.co.spudsoft.birt.emitters.excel2.handlers.PageHandler.java

License:Open Source License

/**
 * <p>/*from w w w . j  a va  2s  .co  m*/
 * Process a CellImage from the images list and place the image on the sheet.
 * </p><p>
 * This involves changing the row height as necesssary and determining the column spread of the image.
 * </p>
 * @param cellImage
 * The image to be placed on the sheet.
 */
private void processCellImage(HandlerState state, Drawing drawing, CellImage cellImage) {
    Coordinate location = cellImage.location;

    Cell cell = state.currentSheet.getRow(location.getRow()).getCell(location.getCol());

    IImageContent image = cellImage.image;

    StyleManagerUtils smu = state.getSmu();
    float ptHeight = cell.getRow().getHeightInPoints();
    if (image.getHeight() != null) {
        ptHeight = smu.fontSizeInPoints(image.getHeight().toString());
    }

    // Get image width
    int endCol = cell.getColumnIndex();
    double lastColWidth = ClientAnchorConversions
            .widthUnits2Millimetres((short) state.currentSheet.getColumnWidth(endCol)) + 2.0;
    double mmWidth = 0.0;
    if (smu.isAbsolute(image.getWidth())) {
        mmWidth = image.getWidth().convertTo(DimensionType.UNITS_MM);
    } else if (smu.isPixels(image.getWidth())) {
        mmWidth = ClientAnchorConversions.pixels2Millimetres(image.getWidth().getMeasure());
    }
    int dx = smu.anchorDxFromMM(mmWidth, lastColWidth);
    // Allow image to span multiple columns
    CellRangeAddress mergedRegion = getMergedRegionBegunBy(state.currentSheet, location.getRow(),
            location.getCol());
    if ((cellImage.spanColumns) || (mergedRegion != null)) {
        log.debug("Image size: ", image.getWidth(), " translates as mmWidth = ", mmWidth);
        if (mmWidth > 0) {
            double mmAccumulatedWidth = 0;
            int endColLimit = cellImage.spanColumns ? 256 : mergedRegion.getLastColumn();
            for (endCol = cell.getColumnIndex(); mmAccumulatedWidth < mmWidth
                    && endCol < endColLimit; ++endCol) {
                lastColWidth = ClientAnchorConversions
                        .widthUnits2Millimetres((short) state.currentSheet.getColumnWidth(endCol)) + 2.0;
                mmAccumulatedWidth += lastColWidth;
                log.debug("lastColWidth = ", lastColWidth, "; mmAccumulatedWidth = ", mmAccumulatedWidth);
            }
            if (mmAccumulatedWidth > mmWidth) {
                mmAccumulatedWidth -= lastColWidth;
                --endCol;
                double mmShort = mmWidth - mmAccumulatedWidth;
                dx = smu.anchorDxFromMM(mmShort, lastColWidth);
            }
        }
        /*
              } else {
                 float widthRatio = (float)(mmWidth / lastColWidth);
                 ptHeight = ptHeight * widthRatio;
        */
    }

    int rowsSpanned = state.findRowsSpanned(cell.getRowIndex(), cell.getColumnIndex());
    float neededRowHeightPoints = ptHeight;

    for (int i = 0; i < rowsSpanned; ++i) {
        int rowIndex = cell.getRowIndex() + 1 + i;
        neededRowHeightPoints -= state.currentSheet.getRow(rowIndex).getHeightInPoints();
    }

    if (neededRowHeightPoints > cell.getRow().getHeightInPoints()) {
        cell.getRow().setHeightInPoints(neededRowHeightPoints);
    }

    // ClientAnchor anchor = wb.getCreationHelper().createClientAnchor();
    ClientAnchor anchor = state.getWb().getCreationHelper().createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setRow1(cell.getRowIndex());
    anchor.setCol2(endCol);
    anchor.setRow2(cell.getRowIndex() + rowsSpanned);
    anchor.setDx2(dx);
    anchor.setDy2(smu.anchorDyFromPoints(ptHeight, cell.getRow().getHeightInPoints()));
    anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
    drawing.createPicture(anchor, cellImage.imageIdx);
}

From source file:Utilities.ExcelTools.java

private static String missingData(Cell cell) {
    return "There is missing data in column " + (cell.getColumnIndex() + 1) + ", row "
            + (cell.getRowIndex() + 1);
}

From source file:Utilities.ExcelTools.java

private static String wrongFormat(Cell cell) {
    return "The cell at column " + (cell.getColumnIndex() + 1) + ", row " + (cell.getRowIndex() + 1)
            + " is not formated correctly";
}

From source file:workbench.db.exporter.XlsRowDataConverter.java

License:Apache License

private void setCellValueAndStyle(Cell cell, Object value, boolean isHead, boolean multiline, int column) {
    CellStyle cellStyle = null;/* ww  w. j  ava 2s . co m*/

    boolean useFormat = applyFormatting();

    if (value == null) {
        // this somewhat duplicates the following code, but the detection based
        // on the actual value class is a bit more accurate than just based
        // on the JDBC datatype, but if a NULL value is passed, then the detection
        // must be done based on the JDBC type
        cellStyle = getBaseStyleForColumn(column, isHead, multiline);
        int type = metaData.getColumnType(column);
        if (type == Types.TIMESTAMP) {
            useFormat = useFormat || applyTimestampFormat();
        } else if (type == Types.DATE) {
            useFormat = useFormat || applyDateFormat();
        }
    } else if (value instanceof BigDecimal) {
        BigDecimal bd = (BigDecimal) value;

        // this is a workaround for exports using Oracle and NUMBER columns
        // which are essentially integer values. But it shouldn't hurt for other DBMS
        // either in case the driver returns a BigDecimal for "real" integer column
        if (bd.scale() == 0 && isIntegerColumn(column)) {
            cellStyle = excelFormat.integerCellStyle;
        } else {
            cellStyle = excelFormat.decimalCellStyle;
            useFormat = useFormat || applyDecimalFormat();
        }
        cell.setCellValue(bd.doubleValue());
    } else if (value instanceof Double || value instanceof Float) {
        cellStyle = excelFormat.decimalCellStyle;
        cell.setCellValue(((Number) value).doubleValue());
        useFormat = useFormat || applyDecimalFormat();
    } else if (value instanceof Number) {
        cellStyle = excelFormat.integerCellStyle;
        cell.setCellValue(((Number) value).doubleValue());
        useFormat = useFormat || applyDecimalFormat();
    } else if (value instanceof java.sql.Timestamp) {
        cellStyle = excelFormat.tsCellStyle;
        cell.setCellValue((java.util.Date) value);
        useFormat = useFormat || applyTimestampFormat();
    } else if (value instanceof java.util.Date) {
        cellStyle = excelFormat.dateCellStyle;
        cell.setCellValue((java.util.Date) value);
        useFormat = useFormat || applyDateFormat();
    } else {
        RichTextString s = workbook.getCreationHelper().createRichTextString(value.toString());
        cell.setCellValue(s);
        if (multiline) {
            cellStyle = excelFormat.multilineCellStyle;
        } else {
            cellStyle = excelFormat.textCellStyle;
        }
    }

    if (isHead) {
        cellStyle = excelFormat.headerCellStyle;
    }

    // do not mess with the formatting if we are writing to an existing sheet
    if (useFormat) {
        try {
            CellStyle style = geCachedStyle(cellStyle, column, isHead);
            cell.setCellStyle(style);
        } catch (IllegalArgumentException iae) {
            LogMgr.logWarning("XlsRowDataConverter.setCellValueAndStyle()",
                    "Could not set style for column: " + metaData.getColumnName(column) + ", row: "
                            + cell.getRowIndex() + ", column: " + cell.getColumnIndex());
        }
    }
}

From source file:workbench.db.importer.ExcelReader.java

License:Apache License

private boolean isMerged(Cell cell) {
    if (cell == null)
        return false;
    for (CellRangeAddress range : mergedRegions) {
        if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex()))
            return true;
    }/*from  ww  w .  j  ava 2 s . c  o m*/
    return false;
}

From source file:workbench.db.importer.ExcelReader.java

License:Apache License

@Override
public List<Object> getRowValues(int rowIndex) {
    Row row = dataSheet.getRow(rowIndex);
    ArrayList<Object> values = new ArrayList<>();

    if (row == null)
        return values;

    int nullCount = 0;
    int colCount = row.getLastCellNum();

    for (int col = 0; col < colCount; col++) {
        Cell cell = row.getCell(col);

        // treat rows with merged cells as "empty"
        if (isMerged(cell)) {
            LogMgr.logDebug("ExcelReader.getRowValues()", dataSheet.getSheetName() + ": column:"
                    + cell.getColumnIndex() + ", row:" + cell.getRowIndex() + " is merged. Ignoring row!");
            return Collections.emptyList();
        }/* ww w .  j  a  v  a  2  s. c  om*/

        Object value = getCellValue(cell);

        if (value == null) {
            nullCount++;
        }
        values.add(value);
    }

    if (nullCount == values.size()) {
        // return an empty list if all columns are null
        values.clear();
    }

    return values;
}