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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

private Team processRow(Row currentRow, CourtScheduleInfo info) {
    short columnCount = currentRow.getLastCellNum();
    int columnCounter = 0;

    currentRowNum = currentRow.getRowNum();
    currentColumnNum = 0;//from w w w .j  a v  a2  s.c  om

    Integer teamId = null;
    String teamName = "";
    Integer conference = null;
    String year = "";
    String gender = "";
    String grade = "";
    String level = "";
    String requests = "";
    String notSameTimeAs = "";
    Team team = new Team();

    while (columnCounter < columnCount) {

        Cell cell = currentRow.getCell(columnCounter);

        if (cell == null) {
            if (teamId == null) {
                System.out.println(
                        "================================================================================");
                break;
            } else {
                columnCounter++;
                continue; // if the cell is null just jump to the next iteration
            }
        }

        currentColumnNum = cell.getColumnIndex();
        if (columnCounter == 0) {
            int index = cell.toString().indexOf(".");
            String teamString = cell.toString().substring(0, index);
            try {
                teamId = Integer.parseInt(teamString);
                team.setTeamId(teamId);
                team.getDontPlay().addSharedTeam(teamId);
            } catch (NumberFormatException e) {
                //not sure what we should do here, this means a team's id is not being captured
                String niceMessage = String.format("Could not determine the team id from '%s'", teamString);
                niceMessage = niceMessage + "\tFound in " + currentCell();
                Main.error(niceMessage, e.toString());
            }
        } else if (columnCounter == 1) {
            team.setConference(getStringValueOfInt(cell.toString()));
        } else if (columnCounter == 2) {
            teamName = cell.toString();
            team.setTeamName(teamName);
        } else if (columnCounter == 3) {
            year = cell.toString();
            team.setYear(year);
        } else if (columnCounter == 4) {
            gender = cell.toString();
            team.setGender(gender);
        } else if (columnCounter == 5) {
            team.setGrade(getStringValueOfInt(cell.toString()));
            if (team.getGrade().trim().equals("")) {
                warning("Team \"" + teamId + "\" has no grade!" + "\tFound in " + currentCell());
            }
        } else if (columnCounter == 6) {
            level = cell.toString();
            team.setLevel(level);
        } else if (columnCounter == 7) {
            requests = cell.toString();
            //debug(team.getTeamId().toString()+":"+requests);
            System.out.println(team.getTeamId() + ": " + requests);
            processRequestConstraints(team, requests, info);
        } else if (columnCounter == 8) {
            notSameTimeAs = cell.toString();
            String[] tempSplit = notSameTimeAs.split(",");

            for (String teamIdStr : tempSplit) {
                try {
                    int index = teamIdStr.indexOf(".");
                    if (index > -1) {
                        teamId = Integer.parseInt(teamIdStr.substring(0, index));
                        team.getAvailability().getNotSameTimeAs().addSharedTeam(teamId);
                        team.getDontPlay().addSharedTeam(teamId);
                    }
                } catch (NumberFormatException nfe) {
                    warning("Unable to add team \"" + teamIdStr
                            + "\" to shared team list because it is not a number" + "\tFound in "
                            + currentCell());
                } catch (NullPointerException npe) {
                    warning("team.availability or team.availability.notSameTimeAs is null for " + teamIdStr
                            + "\tFound in " + currentCell());
                }
            }
        }

        columnCounter += 1;
    }
    return team;
}

From source file:coverageqc.data.DoNotCall.java

public static DoNotCall populate(Row xslxHeadingRow, Row xslxDataRow, Integer calltype) {
    DoNotCall donotcall = new DoNotCall();
    int columnNumber;
    int cellIndex;
    String[] headerArray;/*from  w  w  w .  j  a va2 s. c  om*/
    HashMap<String, Integer> headings = new HashMap<String, Integer>();

    columnNumber = xslxHeadingRow.getLastCellNum();
    headerArray = new String[columnNumber];

    Iterator<Cell> cellIterator = xslxHeadingRow.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        cellIndex = cell.getColumnIndex();
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            headerArray[cellIndex] = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            headerArray[cellIndex] = Double.toString(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            headerArray[cellIndex] = cell.getStringCellValue();
            break;
        default:
            headerArray[cellIndex] = "";
        }

    } //end while celliterator

    for (int x = 0; x < headerArray.length; x++) {
        headings.put(headerArray[x].substring(0, headerArray[x].indexOf("_")), x);
    }

    //String[] dataArray = xslxDataLine.split("\t");
    if (xslxDataRow.getCell(headings.get("HGVSc")) != null) {
        donotcall.hgvsc = xslxDataRow.getCell(headings.get("HGVSc").intValue()).getStringCellValue();

    }
    //donotcall.hgvsp = xslxDataRow.getCell(headings.get("HGVSp").intValue()).getStringCellValue();
    if (xslxDataRow.getCell(headings.get("ENSP")) != null) {
        donotcall.ensp = xslxDataRow.getCell(headings.get("ENSP").intValue()).getStringCellValue();
    }
    if (xslxDataRow.getCell(headings.get("Transcript")) != null) {
        donotcall.transcript = xslxDataRow.getCell(headings.get("Transcript").intValue()).getStringCellValue();
    } else {
        System.out.println(
                "Transcript_27 column entry is negative!  This is essential to do not call! Do not call list needs to be fixed!  Crashing to prevent abnormal behavior!");
        System.exit(1);
    }
    donotcall.coordinate = (long) xslxDataRow.getCell(headings.get("Coordinate").intValue())
            .getNumericCellValue();

    // CallType is the page of the xlsx :
    // 1 => Always_Not_Real
    // 2 => Not_Real_When_Percentage_Low
    // 3 => Undetermined_Importance
    if (calltype == 1) {
        donotcall.callType = "Don't call, always";
    } else if (calltype == 2) {
        donotcall.callType = "If percentage low, don't call";

    } else {
        donotcall.callType = "On lab list, Unknown significance";
    }

    return donotcall;
}

From source file:csv.impl.DefaultExcelFormatter.java

License:Open Source License

/**
 * Sets the cell style./*  ww w.  j  av a  2s .c o  m*/
 * This implementations calls various other methods to define
 * the style of the cell.
 * @param writer writer that requires the information
 * @param cell cell to be formatted
 * @param value value in cell
 * @see #getFormat(ExcelWriter, int, int, Object)
 * @see #getBackgroundColor(ExcelWriter, int, int, Object)
 * @see #getFillPattern(ExcelWriter, int, int, Object)
 * @see #getForegroundColor(ExcelWriter, int, int, Object)
 * @see #getFont(ExcelWriter, int, int, Object)
 * @see #getAlign(ExcelWriter, int, int, Object)
 * @see #getHyperlink(ExcelWriter, int, int, Object)
 */
@Override
public void setStyle(ExcelWriter writer, Cell cell, Object value) {
    int row = cell.getRowIndex();
    int column = cell.getColumnIndex();

    StyleDescription desc = new StyleDescription();

    // Collect cell style and check if we already had it before

    // data format
    desc.setFormat(getFormat(writer, row, column, value));
    desc.setFgColor(getForegroundColor(writer, row, column, value));
    desc.setFillPattern(getFillPattern(writer, row, column, value));
    desc.setBgColor(getBackgroundColor(writer, row, column, value));

    // Font
    desc.setFont(getFont(writer, row, column, value));

    // Borders
    desc.setTopBorderColor(getTopBorderColor(writer, row, column, value));
    desc.setLeftBorderColor(getLeftBorderColor(writer, row, column, value));
    desc.setRightBorderColor(getRightBorderColor(writer, row, column, value));
    desc.setBottomBorderColor(getBottomBorderColor(writer, row, column, value));
    desc.setTopBorderThickness(getTopBorderThickness(writer, row, column, value));
    desc.setLeftBorderThickness(getLeftBorderThickness(writer, row, column, value));
    desc.setRightBorderThickness(getRightBorderThickness(writer, row, column, value));
    desc.setBottomBorderThickness(getBottomBorderThickness(writer, row, column, value));
    desc.setTextWrap(isTextWrap(writer, row, column, value));

    // Alignment
    desc.setAlignment(getAlign(writer, row, column, value));

    if (!desc.isDefault()) {
        CellStyle style = styles.get(desc);
        if (style == null) {
            style = writer.getWorkbook().createCellStyle();
            styles.put(desc, style);
            desc.applyStyle(style);
        }

        desc.applyStyle(style);

        // set style
        cell.setCellStyle(style);
    }

    // Set a hyperlink
    Hyperlink link = getHyperlink(writer, row, column, value);
    if (link != null)
        cell.setHyperlink(link);

}

From source file:csv.impl.ExcelWriter.java

License:Open Source License

/**
 * Sets the value at the specified cell.
 * This method automatically selects the correct type for the cell
 * and notifies the {@link ExcelFormatter} to set the correct style
 * on this cell./*  ww w.  j a va 2 s. c  o m*/
 * @param cell cell object
 * @param value value to be set
 */
public void setValue(Cell cell, Object value) {
    if (value != null) {
        if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }

        if (cell.getColumnIndex() > maxColumns)
            maxColumns = cell.getColumnIndex();
    }
    setStyle(cell, value);
}

From source file:Dao.XlsBillDao.java

public ArrayList<WorkItemBean> ReadXLS(File f) {
    WorkDao wdao = new WorkDao();
    FileInputStream fis = null;//w  ww.  jav a 2  s . com
    ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>();
    try {
        String pono = null;
        XSSFRow row = null;
        //fis = new FileInputStream(new File("D:\\WO-2015-2008.xlsx"));
        fis = new FileInputStream(f);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = spreadsheet.iterator();
        int i = 0;
        while (rowIterator.hasNext()) {
            i++;
            row = (XSSFRow) rowIterator.next();
            if (i == 1) {
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() == 0) {
                        pono = cell.getStringCellValue();
                        pono = pono.substring((pono.indexOf(":") + 1));
                    }
                }
            }
            if (i > 2) {
                WorkItemBean bean = new WorkItemBean();
                //                    System.out.println("ROW" + i);
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() >= 0) {
                        //System.out.print("COLUMN");
                        if (cell.getColumnIndex() == 1) {
                            //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setITEM_ID(String.valueOf(cell.getNumericCellValue()));
                            } else {
                                bean.setITEM_ID(cell.getStringCellValue());
                            }
                        } else if (cell.getColumnIndex() == 2) {
                            //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            bean.setITEM_DESC(cell.getStringCellValue());
                        } else if (cell.getColumnIndex() == 3) {
                            //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            bean.setUOM(cell.getStringCellValue());
                        } else if (cell.getColumnIndex() == 4) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setQTY((float) cell.getNumericCellValue());
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                            } else {
                                bean.setQTY(Float.parseFloat(cell.getStringCellValue()));
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            }
                        } else if (cell.getColumnIndex() == 5) {
                            //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setRATE((float) cell.getNumericCellValue());
                            } else {
                                bean.setRATE(Float.parseFloat(cell.getStringCellValue()));
                            }

                        } else if (cell.getColumnIndex() == 7) {

                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setPLANT(Integer.toString((int) cell.getNumericCellValue()));
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                            } else {
                                bean.setPLANT(cell.getStringCellValue());
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            }
                        } else if (cell.getColumnIndex() == 8) {

                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setCC(Integer.toString((int) cell.getNumericCellValue()));
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                            } else {
                                bean.setCC(cell.getStringCellValue());
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            }
                        } else if (cell.getColumnIndex() == 9) {
                            if (wdao.isProjWO(pono)) {
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    bean.setPROJ(Integer.toString((int) cell.getNumericCellValue()));
                                    //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                                } else {
                                    bean.setPROJ(cell.getStringCellValue());
                                    //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                                }
                            } else {
                                bean.setPROJ("-");

                            }
                        } else if (cell.getColumnIndex() == 10) {
                            if (wdao.isProjWO(pono)) {
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    bean.setTASK(Integer.toString((int) cell.getNumericCellValue()));
                                    //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                                } else {
                                    bean.setTASK(cell.getStringCellValue());
                                    //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                                }
                            } else {
                                bean.setTASK("-");

                            }
                        } else if (cell.getColumnIndex() == 11) {

                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setCMT(Integer.toString((int) cell.getNumericCellValue()));
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getNumericCellValue() + " \t ");
                            } else {
                                bean.setCMT(cell.getStringCellValue());
                                //System.out.print(cell.getColumnIndex() + ":" + cell.getStringCellValue() + " \t ");
                            }
                        } else {

                        }
                    }
                }
                //System.out.println();
                itm.add(bean);
            }
        }
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "READ DONE !!");
        fis.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } finally {
        try {
            fis.close();
        } catch (IOException ex) {
            Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
        }

    }
    for (WorkItemBean i : itm) {
        if (i.getQTY() != 0) {
            Logger.getLogger(XlsBillDao.class.getName()).log(Level.INFO,
                    "ITEM_ID : {0} QTY:{1} PLANT:{2} CC:{3}",
                    new Object[] { i.getITEM_ID(), i.getQTY(), i.getPLANT(), i.getCC() });
        }
    }
    return itm;
}

From source file:Dao.XlsWoDao.java

public ArrayList<WorkItemBean> ReadXLS(File f) {
    Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "READ XLS CALLED");
    FileInputStream fis = null;//from w ww . ja v  a  2s . co  m
    ArrayList<WorkItemBean> itm = new ArrayList<WorkItemBean>();
    try {
        XSSFRow row = null;
        //  fis = new FileInputStream(new File("D:\\CreateWO_Tmp.xlsx"));
        fis = new FileInputStream(f);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = spreadsheet.iterator();
        int i = 0;
        while (rowIterator.hasNext()) {
            //                System.out.println("NEW ROW");
            i++;
            row = (XSSFRow) rowIterator.next();
            if (i == 1) {
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() == 0) {
                    }
                }
            }
            if (i > 3) {
                WorkItemBean bean = new WorkItemBean();
                // System.out.println("ROW" + i);
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    //                        System.out.println("NEW COLUMN");
                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() >= 0) {
                        //   System.out.print("COLUMN");
                        if (cell.getColumnIndex() == 0) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setITEM_ID(String.valueOf(t));
                            } else {
                                bean.setITEM_ID(cell.getStringCellValue());
                            }

                        } else if (cell.getColumnIndex() == 1) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                bean.setRATE((float) cell.getNumericCellValue());
                            } else {
                                bean.setRATE(Float.parseFloat(cell.getStringCellValue()));
                            }
                        } else if (cell.getColumnIndex() == 2) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setPLANT(String.valueOf(t));
                            } else {
                                bean.setPLANT(cell.getStringCellValue());
                            }
                        } else if (cell.getColumnIndex() == 3) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setPROJ(String.valueOf(t));
                            } else {
                                bean.setPROJ(cell.getStringCellValue());
                            }

                        } else if (cell.getColumnIndex() == 4) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setTASK(String.valueOf(t));
                            } else {
                                bean.setTASK(cell.getStringCellValue());
                            }
                        } else if (cell.getColumnIndex() == 5) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                int t = (int) cell.getNumericCellValue();
                                bean.setCMT(String.valueOf(t));
                            } else {
                                bean.setCMT(cell.getStringCellValue());
                            }
                        }
                    }
                }
                // if (bean.getITEM_ID() != null || !"".equals(bean.getITEM_ID())) {
                itm.add(bean);
                // }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } finally {
        try {
            fis.close();
        } catch (IOException ex) {
            Logger.getLogger(XlsWoDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
        }
    }
    return itm;
}

From source file:data.control.dataSheet.java

public ArrayList<Patient> getPatients() {
    ArrayList<XSSFRow> theRows;
    ArrayList<Patient> thePatients = new ArrayList();
    boolean firstRowSkipped = false;

    connect();/*from  ww w  . j a  va  2s.  com*/
    theRows = fetchRows();

    // looping through the rows
    Iterator<XSSFRow> rowIterator = theRows.iterator();

    while (rowIterator.hasNext()) {

        // reading the row
        Row aRow = rowIterator.next();

        if (!firstRowSkipped) {
            firstRowSkipped = true;
            continue;
        }

        Patient aPatient = new Patient();

        // loading the cells
        Iterator<Cell> cellIterator = aRow.cellIterator();

        // looping through the cells
        while (cellIterator.hasNext()) {
            // reading the cell
            Cell cell = cellIterator.next();

            if (cell != null) {
                switch (cell.getColumnIndex()) {

                case 0:
                    // ID
                    aPatient.setID((int) cell.getNumericCellValue());
                    break;
                case 1:
                    // Name
                    aPatient.setName(cell.getStringCellValue());
                    break;
                case 2: // heart rate
                case 3: // heart rate
                case 4: // heart rate
                case 5: // heart rate
                case 6: // heart rate
                    //aPatient.addHeartRate(cell.getNumericCellValue());
                    break;
                case 7: // tempreature
                case 8: // tempreature
                case 9: // tempreature
                case 10:// tempreature
                case 11:// tempreature
                    //aPatient.addTempreature(cell.getNumericCellValue());
                    break;
                case 12:
                    // blood_type
                    aPatient.setBloodType(cell.getStringCellValue());

                    break;
                case 13:
                    // sex
                    aPatient.setSex(cell.getStringCellValue());
                    break;
                case 14:
                    // age
                    aPatient.setAge((int) cell.getNumericCellValue());
                    break;
                case 15:
                    // date_added
                    aPatient.setDateAdded(cell.getDateCellValue());
                    break;
                case 16:
                    // last_updated
                    aPatient.setLastUpdated(cell.getDateCellValue());
                    break;
                case 17:
                    // last_alarmed
                    aPatient.setLastAlarm(cell.getDateCellValue());
                default:
                    break;
                }
            }
        }
        // adding patient to the collection
        if (aPatient.getName() != null) {
            thePatients.add(aPatient);
        }
        //aPatient.printAll();
    }

    //closeConnection();
    return thePatients;
}

From source file:de.enerko.reports2.engine.CellDefinition.java

License:Apache License

public CellDefinition(final String sheetname, final Cell cell) {
    final int ct = cell.getCellType();

    Method m = null;/*w  w w .ja  v  a 2  s .  co m*/
    try {
        m = this.getClass().getDeclaredMethod("parse_" + Report.IMPORTABLE_CELL_TYPES.get(new Integer(ct)),
                new Class[] { Cell.class });
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (m == null)
            throw new RuntimeException("Invalid type " + ct);
    }

    try {
        final CellValue cellValue = (CellValue) m.invoke(this, new Object[] { cell });
        this.sheetname = sheetname;
        this.column = cell.getColumnIndex();
        this.row = cell.getRowIndex();
        this.name = CellReferenceHelper.getCellReference(cell.getColumnIndex(), cell.getRowIndex());
        this.type = cellValue.type;
        this.value = cellValue.representation;
        if (cell.getCellComment() == null || cell.getCellComment().getString() == null)
            this.comment = null;
        else
            this.comment = new CommentDefinition(cell.getCellComment());
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

public List<CellDefinition> evaluateWorkbook() {
    final List<CellDefinition> rv = new ArrayList<CellDefinition>();

    boolean reevaluate = false;
    if (workbook instanceof HSSFWorkbook) {
        try {/* w ww.ja v  a 2 s  . c  o m*/
            workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
        } catch (Exception e) {
            reevaluate = true;
        }
    }

    final FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook,
            IStabilityClassifier.TOTALLY_IMMUTABLE);
    formulaEvaluator.clearAllCachedResultValues();

    for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
        final Sheet sheet = workbook.getSheetAt(i);
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (reevaluate && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    try {
                        formulaEvaluator.evaluateFormulaCell(cell);
                    } catch (Exception e) {
                        ReportEngine.logger.log(Level.WARNING,
                                String.format("Could not evaluate formula '%s' in cell %s on sheet '%s': %s",
                                        cell.getCellFormula(), CellReferenceHelper
                                                .getCellReference(cell.getColumnIndex(), row.getRowNum()),
                                        sheet.getSheetName(), e.getMessage()));
                    }
                }

                final CellDefinition cellDefinition = IMPORTABLE_CELL_TYPES.containsKey(
                        new Integer(cell.getCellType())) ? new CellDefinition(sheet.getSheetName(), cell)
                                : null;
                if (cellDefinition != null)
                    rv.add(cellDefinition);
            }
        }
    }

    return rv;
}

From source file:de.enerko.reports2.engine.Report.java

License:Apache License

/**
 * This method adds a new cell to the sheet of a workbook. It could 
 * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to
 * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is
 * tied to a specific Excel API. Having those methods here allows the Report to become
 * an interface if a second engine (i.e. JXL) should be added in the future.
 * @param workbook//from www  . jav a2  s.  c  o m
 * @param sheet
 * @param cellDefinition
 */
private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) {
    final int columnNum = cellDefinition.column, rowNum = cellDefinition.row;

    Row row = sheet.getRow(rowNum);
    if (row == null)
        row = sheet.createRow(rowNum);

    Cell cell = row.getCell(columnNum);
    // If the cell already exists and is no blank cell
    // it will be used including all formating
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell = fill(workbook, cell, cellDefinition, false);
    }
    // Otherwise a new cell will be created, the datatype set and 
    // optionally a format will be created
    else {
        cell = fill(workbook, row.createCell(columnNum), cellDefinition, true);

        final Sheet referenceSheet;
        if (cellDefinition.getReferenceCell() != null
                && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) {
            final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row);
            final Cell referenceCell = referenceRow == null ? null
                    : referenceRow.getCell(cellDefinition.getReferenceCell().column);
            if (referenceCell != null && referenceCell.getCellStyle() != null)
                cell.setCellStyle(referenceCell.getCellStyle());
        }
    }

    // Add an optional comment      
    if (cellDefinition.hasComment()) {
        final CreationHelper factory = workbook.getCreationHelper();

        final Drawing drawing = sheet.createDrawingPatriarch();
        final ClientAnchor commentAnchor = factory.createClientAnchor();

        final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1
                : cellDefinition.comment.column;
        final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row;

        commentAnchor.setCol1(col1);
        commentAnchor.setRow1(row1);
        commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width));
        commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height));

        final Comment comment = drawing.createCellComment(commentAnchor);
        comment.setString(factory.createRichTextString(cellDefinition.comment.text));
        comment.setAuthor(cellDefinition.comment.author);
        comment.setVisible(cellDefinition.comment.visible);

        cell.setCellComment(comment);
    }
}