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

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

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

License:Open Source License

/**
 * <p>/*from   w ww . j a va 2s .  c  om*/
 * 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:util.DocumentFunction.java

public static Map<Boolean, List<ImportedStudent>> readStudentXlsxFile(String filename, int course_id) {
    //        StringBuilder text = new StringBuilder();
    Map<Boolean, List<ImportedStudent>> stMap = new TreeMap<>();
    List<ImportedStudent> exStudentList = new ArrayList<>();
    ImportedStudent st = null;/*from  www . j  a  v a2  s .c  om*/
    try {
        FileInputStream file = new FileInputStream(new File(filename));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        boolean breakPoint = true;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(i);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            String email = null, firstname = null, lastname = null;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                int rownum = row.getRowNum();

                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    if (cell.getColumnIndex() == 0) {
                        email = cell.getStringCellValue();
                    } else if (cell.getColumnIndex() == 1) {
                        firstname = cell.getStringCellValue();
                    } else if (cell.getColumnIndex() == 2) {
                        lastname = cell.getStringCellValue();
                    }
                }

                if (row.getRowNum() == 0) {
                    if (email.equalsIgnoreCase("Email") && firstname.equalsIgnoreCase("Firstname")
                            && lastname.equalsIgnoreCase("Lastname")) {
                        continue;
                    } else {
                        stMap.put(false, exStudentList);
                        breakPoint = false;
                        break;
                    }
                } else {
                    st = new ImportedStudent();
                    st.setCourse_id(course_id);
                    st.setEmail(email);
                    st.setFirstname(firstname);
                    st.setLastname(lastname);
                    exStudentList.add(st);
                }
                //                    System.out.println(email + "/" + firstname + "/" + lastname);
            }
        }
        if (breakPoint) {
            stMap.put(true, exStudentList);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return stMap;
}

---

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);//w  ww .j  av a2s.co m
}

---

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  av a2 s .  c  o  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.ja va 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();
        }/*from   w  ww  .ja  va  2 s.c  o m*/

        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;
}

---

From source file:writeintoexcel.ReadExcelFileExample.java

private static List getStudentsListFromExcel() {
    List studentList = new ArrayList();
    FileInputStream fis = null;//w  ww  .  j  av  a  2 s  .c  om
    try {
        fis = new FileInputStream(FILE_PATH);

        // Using XSSF for xlsx format, for xls use HSSF
        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        //looping over each workbook sheet
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            //iterating over each row
            while (rowIterator.hasNext()) {

                Student student = new Student();
                Row row = (Row) rowIterator.next();
                Iterator cellIterator = row.cellIterator();

                //Iterating over each cell (column wise)  in a particular row.
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();
                    //The Cell Containing String will is name.
                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                        student.setName(cell.getStringCellValue());

                        //The Cell Containing numeric value will contain marks
                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        //Cell with index 1 contains marks in Maths
                        if (cell.getColumnIndex() == 1) {
                            student.setMaths(String.valueOf(cell.getNumericCellValue()));
                        }
                        //Cell with index 2 contains marks in Science
                        else if (cell.getColumnIndex() == 2) {

                            student.setScience(String.valueOf(cell.getNumericCellValue()));
                        }
                        //Cell with index 3 contains marks in English
                        else if (cell.getColumnIndex() == 3) {
                            student.setEnglish(String.valueOf(cell.getNumericCellValue()));
                        }
                    }
                }
                //end iterating a row, add all the elements of a row in list
                studentList.add(student);
            }
        }

        fis.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return studentList;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »