Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

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

Usage

From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java

private void saveCauseCode(HSSFWorkbook wb, String remoteUser, List<State> steps) {
    HSSFSheet sheet = wb.getSheetAt(0);//from   ww  w .  j  a  va2  s.  c  o m
    Iterator<Row> rowIter = sheet.rowIterator();

    State state = State.findStateByLable(steps, STEP3_LABEL);
    if (state == null) {
        state = new State();
        state.setDescription("Persist changes");
        state.setLabel(STEP3_LABEL);
        state.setStatus(EStatus.IN_PROGRESS);
        steps.add(state);
    }

    int rowCounter = -1;
    int totalRows = sheet.getLastRowNum();
    while (rowIter.hasNext()) {
        HSSFRow row = (HSSFRow) rowIter.next();
        rowCounter++;

        int progress = (int) ((float) rowCounter / totalRows * 100);
        state.setProgress(progress);
        if (progress == 100) {
            state.setStatus(EStatus.FINISHED);
        }

        if (rowCounter <= ROW_TABLE_HEAD) {
            continue;
        }

        HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId());

        long causeCodeId = -1;
        if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            causeCodeId = Long.valueOf(causeCodeIdCell.getStringCellValue());
        } else if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            causeCodeId = Math.round(causeCodeIdCell.getNumericCellValue());
        }

        CauseCode causeCode = (CauseCode) getEntityManager().createNamedQuery("getCauseCodeById")
                .setParameter("id", causeCodeId).getSingleResult();

        String causeCodeName = causeCode.getAlertCause().getName();

        HSSFCell causeCodeCell = row.getCell(colIndexes.getColCauseCode());
        String colCauseCode = null;
        if (causeCodeCell != null) {
            colCauseCode = causeCodeCell.getStringCellValue().trim();
        }

        Date targetDate = causeCode.getTargetDate();
        HSSFCell targetDateCell = row.getCell(colIndexes.getColTargetDate());
        Date colTargetDate = null;
        if (targetDateCell != null) {
            if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC
                    && HSSFDateUtil.isCellDateFormatted(targetDateCell)) {
                colTargetDate = targetDateCell.getDateCellValue();
            } else if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                colTargetDate = convertTextToDate(targetDateCell);//Convert Date Text to Date Object
            }
        }
        String owner = causeCode.getOwner();

        HSSFCell ownerCell = row.getCell(colIndexes.getColOwner());
        String colOwner = null;
        if (ownerCell != null) {
            colOwner = ownerCell.getStringCellValue().trim();
        }

        //Assignee Comments Function Start
        if (colIndexes.getColAssigneeComments() != -1) {
            HSSFCell assigneeCommentsCell = row.getCell(colIndexes.getColAssigneeComments());

            String assigneeComments = "";
            if (assigneeCommentsCell != null
                    && assigneeCommentsCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                assigneeComments = assigneeCommentsCell.getStringCellValue();
            }

            if (assigneeComments != null && !"".equals(assigneeComments.trim())) {
                updateAssigneeComments(causeCode.getAlertId(), assigneeComments.trim(),
                        colIndexes.getReportName().trim(), remoteUser);
            }
        }
        //Assignee Comments Function End 

        boolean changed = false;

        if (!strCompare(causeCodeName, colCauseCode) || !dateCompare(targetDate, colTargetDate)
                || !strCompare(owner, colOwner)) {
            changed = true;
        }

        if (!changed) {
            continue;
        }

        CauseCodeHistory history = new CauseCodeHistory();
        history.setCauseCode(causeCode);
        history.setAlertType(causeCode.getAlertType());
        history.setAlertId(causeCode.getAlertId());
        history.setAlertCause(causeCode.getAlertCause());
        history.setTargetDate(causeCode.getTargetDate());
        history.setOwner(causeCode.getOwner());
        history.setRecordTime(causeCode.getRecordTime());
        history.setRemoteUser(causeCode.getRemoteUser());

        if (!strCompare(causeCodeName, colCauseCode)) {
            try {
                AlertCause alertCause = null;

                if ("UNDEFINED".equals(colCauseCode.trim().toUpperCase())) {
                    alertCause = (AlertCause) getEntityManager()
                            .createNamedQuery("findAlertCauseByNameWithoutShowInGui")
                            .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult();
                } else {
                    alertCause = (AlertCause) getEntityManager().createNamedQuery("findAlertCauseByName")
                            .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult();
                }

                if (alertCause != null) {
                    causeCode.setAlertCause(alertCause);
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }

        if (!dateCompare(targetDate, colTargetDate)) {
            causeCode.setTargetDate(colTargetDate);
        }

        if (!strCompare(owner, colOwner)) {
            causeCode.setOwner(colOwner);
        }

        causeCode.setRemoteUser(remoteUser);
        causeCode.setRecordTime(new Date());

        try {
            getEntityManager().persist(history);
            getEntityManager().persist(causeCode);
            getEntityManager().flush();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

}

From source file:com.ibm.ioes.actions.NewOrderAction.java

/**
   * @param templateStream /*from w ww.j a v a2  s  .c  o  m*/
   * @method saveUploadedFileInfo
   * @purpose save uploaded file data in staging table in database
   * @param FormFile,
   *            filepath, userName
   * @param excel_uploadPath,
   *            uploadedFilePath
   * @return
   * @throws NpdException
   */
public int saveUploadedFileInfo(FormFile uploadedFile, int productID, String templateFilePath)
        throws IOESException {
    //   AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() started");
    int sheetCol, sheetRow;
    ArrayList<Object[][]> excelDataList = new ArrayList<Object[][]>();
    int thisSaveCode = 0;
    int saveStatusCode = 0;
    try {
        String fileName = null;

        if (uploadedFile != null) {
            fileName = uploadedFile.getFileName();
        }

        if (fileName != null) {
            HSSFWorkbook workbook = null;
            HSSFSheet sheet = null;
            HSSFRow rowInSheet = null;
            HSSFCell cellInSheet = null;

            workbook = new HSSFWorkbook(uploadedFile.getInputStream());
            for (int count = 0; count < workbook.getNumberOfSheets() - 1; count++) {
                sheet = workbook.getSheetAt(count);
                sheetRow = sheet.getLastRowNum();
                sheetCol = sheet.getRow(0).getLastCellNum();
                Object excelData[][] = new Object[sheetRow][sheetCol];
                for (int r = 1; r <= sheetRow; r++) {
                    rowInSheet = sheet.getRow(r);
                    int columIndex = 0;
                    for (int c = 1; c < sheetCol + 1; c++) {
                        if (rowInSheet != null) {
                            cellInSheet = rowInSheet.getCell(c - 1);
                            if (cellInSheet != null) {
                                if (cellInSheet.getCellType() == 0) {
                                    excelData[r - 1][columIndex++] = Utility.convertWithOutE_WithOutDotZero(
                                            String.valueOf(cellInSheet.getNumericCellValue()));
                                    /*NumberFormat formatter = new DecimalFormat("0");
                                    excelData[r - 1][columIndex++] = formatter
                                     .format(cellInSheet.getNumericCellValue());*/
                                } else {
                                    excelData[r - 1][columIndex++] = cellInSheet.toString().trim();
                                }

                            } else {
                                excelData[r - 1][columIndex++] = "";
                            }
                        } else {
                            excelData[r - 1][columIndex++] = "";
                        }
                    }
                }
                excelDataList.add(excelData);
            }

        }
        //if (checkCode == 1) {
        NewOrderModel model = new NewOrderModel();
        saveStatusCode = model.saveUploadedFileToTemporaryTable(excelDataList, productID, fileName);
        if (saveStatusCode > 0) {
            thisSaveCode = 1;
        } else {
            thisSaveCode = 0;
        }

        /*}
        else
        {
          thisSaveCode= 0;
        }*/

        //         AppConstants.IOES_LOGGER.info("Completed..");
        return thisSaveCode;
    } catch (Exception ed) {
        ed.printStackTrace();
        AppConstants.IOES_LOGGER.error("Error while getting saveUploadedFileInfo " + ed.getMessage());
        throw new IOESException(ed);
    }

    finally {
        AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() completed");
    }

}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

@SuppressWarnings("deprecation")
public String getErrorExcel(String filePath, int fileID) throws IOESException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    int colCount, ctr = 0;
    String fileName;// w  w w.j  a va2s  . com
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList errVal = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ErrorLogDto dtoObj;
    BillingTriggerValidation validateDto = null;

    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.errors") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));

        errVal = objDao.getErrorLog(fileID);
        System.out.println(errVal.toString());
        System.out.println(wb.getNumberOfSheets());
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {

            ws = wb.getSheetAt(s);
            wr = ws.getRow(1);
            colCount = wr.getLastCellNum();
            wc = wr.createCell(colCount);
            wc.setCellValue("ERROR LOG");

            for (int r = 2; r <= ws.getLastRowNum(); r++) {
                if ((ctr < errVal.size())) {
                    dtoObj = (ErrorLogDto) errVal.get(ctr);
                    wr = ws.getRow(r);
                    if (wr != null) {
                        int chk = 0;
                        for (int col = 0; col < colCount; col++) {
                            wc = wr.getCell(col);
                            if (wc != null) {
                                if (!(wc.toString().trim().equals(""))) {
                                    chk = 1;
                                }
                            }
                        }
                        if (chk == 1) {
                            wc = wr.createCell(colCount);
                            System.out.println(dtoObj.getErrorLogValue());
                            if (dtoObj.getErrorLogValue() == null) {
                                wc.setCellValue("No Errors");
                            } else {
                                wc.setCellValue(dtoObj.getErrorLogValue().toString());
                            }

                            ctr++;
                        }
                    }
                }
            }
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

public String getResultExcel(String filePath, int fileID) throws IOESException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    int colCount, ctr = 0;
    String fileName;//from  ww w  .  j  a va2  s.c om
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList errVal = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ErrorLogDto dtoObj;

    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.success") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));

        errVal = objDao.getResultLog(fileID);
        System.out.println(errVal.toString());
        System.out.println(wb.getNumberOfSheets());
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {

            ws = wb.getSheetAt(s);
            wr = ws.getRow(1);
            colCount = wr.getLastCellNum();
            wc = wr.createCell(colCount);
            wc.setCellValue("RESULT LOG" + "_" + "ORDERNO");

            for (int r = 2; r <= ws.getLastRowNum(); r++) {
                if ((ctr < errVal.size())) {
                    dtoObj = (ErrorLogDto) errVal.get(ctr);
                    wr = ws.getRow(r);
                    if (wr != null) {
                        int chk = 0;
                        for (int col = 0; col < colCount; col++) {
                            wc = wr.getCell(col);
                            if (wc != null) {
                                if (!(wc.toString().trim().equals(""))) {
                                    chk = 1;
                                }
                            }
                        }
                        if (chk == 1) {
                            wc = wr.createCell(colCount);
                            System.out.println(dtoObj.getResultLogValue());
                            if (dtoObj.getResultLogValue() == null) {
                                wc.setCellValue("SUCCESS");
                            } else {
                                if (dtoObj.getOrderNo() != null)
                                    wc.setCellValue(dtoObj.getResultLogValue().toString() + "_("
                                            + dtoObj.getOrderNo().toString() + ")");
                            }

                            ctr++;
                        }
                    }
                }
            }
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getResultExcel method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

public String getResultErrorMixLog(String filePath, int fileID) throws IOESException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    int colCount, ctr = 0;
    String fileName;/*ww w .j  a va 2  s  .  c om*/
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList errVal = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ErrorLogDto dtoObj;

    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.success") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));

        errVal = objDao.getResultErrorMixLog(fileID);
        System.out.println(errVal.toString());
        System.out.println(wb.getNumberOfSheets());

        HSSFCellStyle styleErr = wb.createCellStyle();
        HSSFCellStyle styleSuccess = wb.createCellStyle();
        HSSFFont fontSuccess = wb.createFont();
        HSSFFont fontErr = wb.createFont();

        for (int s = 0; s < wb.getNumberOfSheets(); s++) {

            ws = wb.getSheetAt(s);
            wr = ws.getRow(1);
            colCount = wr.getLastCellNum();
            wc = wr.createCell(colCount);
            wc.setCellValue("RESULT LOG" + "_" + "ORDERNO");

            for (int r = 2; r <= ws.getLastRowNum(); r++) {
                if ((ctr < errVal.size())) {
                    dtoObj = (ErrorLogDto) errVal.get(ctr);
                    wr = ws.getRow(r);
                    if (wr != null) {
                        int chk = 0;
                        for (int col = 0; col < colCount; col++) {
                            wc = wr.getCell(col);
                            if (wc != null) {
                                if (!(wc.toString().trim().equals(""))) {
                                    chk = 1;
                                }
                            }
                        }
                        if (chk == 1) {
                            wc = wr.createCell(colCount);
                            if ((dtoObj.getResultLogValue() == null || dtoObj.getResultLogValue().length() == 0)
                                    && dtoObj.getErrorLogValue() != null) {
                                fontErr.setColor(HSSFColor.RED.index);
                                styleErr.setFont(fontErr);
                                wc.setCellStyle(styleErr);
                                wc.setCellValue(dtoObj.getErrorLogValue().toString());
                            } else {
                                fontSuccess.setColor(HSSFColor.BLACK.index);
                                styleSuccess.setFont(fontSuccess);
                                wc.setCellStyle(styleSuccess);
                                wc.setCellValue(dtoObj.getResultLogValue().toString());
                            }

                            ctr++;
                        }
                    }
                }
            }
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + " Exception occured in getResultExcel method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        return filePath = "NOTFOUND";
    }
    return filePath;
}

From source file:com.inet.web.service.mail.utils.ImportUtil.java

License:Open Source License

/**
 * get String//from  w w w .j  a v a 2s  . c om
 * @param rowData
 * @param index
 * @return
 */
private static String getString(HSSFRow rowData, int index) {
    HSSFCell cell = rowData.getCell(index);
    if (cell == null) {
        return StringService.EMPTY_STRING;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return String.valueOf((new Double(cell.getNumericCellValue())).longValue());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        return StringService.EMPTY_STRING;
    } else {
        return StringService.EMPTY_STRING;
    }
}

From source file:com.inet.web.service.mail.utils.ImportUtil.java

License:Open Source License

/**
 * get double/*www  .j  av  a 2s  .  c o  m*/
 * @param rowData
 * @param index
 * @param def
 * @return
 */
@SuppressWarnings("unused")
private static double getDouble(HSSFRow rowData, int index, int def) {
    HSSFCell cell = rowData.getCell(index);

    if (cell == null) {
        return def;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return cell.getNumericCellValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return Double.valueOf(cell.getNumericCellValue());
    } else {
        return 0;
    }
}

From source file:com.inet.web.service.mail.utils.ImportUtil.java

License:Open Source License

/**
 * get Long//from  ww  w. ja v  a2s  . c om
 * @param rowData
 * @param index
 * @return
 */
private static long getLong(HSSFRow rowData, int index) {
    HSSFCell cell = rowData.getCell(index);

    if (cell == null) {
        return 0L;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return (new Double(cell.getNumericCellValue())).longValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return (new Double(cell.getStringCellValue())).longValue();
    } else {
        return 0L;
    }
}

From source file:com.itn.excelDao.ExcelView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest requesr,
        HttpServletResponse response) throws Exception {

    // get data model which is passed by the Spring container
    List<Users> allUsers = (List<Users>) model.get("allUsers");

    //Create new excel sheet
    HSSFSheet sheet = workbook.createSheet("Java Books");
    sheet.setDefaultColumnWidth(30);//from w  w w.j  a v a 2s  . c  o  m

    //create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);

    // create header row
    HSSFRow header = sheet.createRow(0);

    header.createCell(0).setCellValue("id");
    header.getCell(0).setCellStyle(style);

    header.createCell(1).setCellValue("First Name");
    header.getCell(1).setCellStyle(style);

    header.createCell(2).setCellValue("Last Name");
    header.getCell(2).setCellStyle(style);

    header.createCell(3).setCellValue("Email Address");
    header.getCell(3).setCellStyle(style);

    // create data rows
    int rowCount = 1;

    for (Users aUsers : allUsers) {
        HSSFRow aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(aUsers.getId());
        aRow.createCell(1).setCellValue(aUsers.getFirstName());
        aRow.createCell(2).setCellValue(aUsers.getLastName());
        aRow.createCell(3).setCellValue(aUsers.getEmail());

    }

}

From source file:com.ivstars.astrology.util.LocationProvider.java

License:Open Source License

private Location createLocation(HSSFRow row, String province) {
    return new Location(row.getCell((short) 5).getNumericCellValue(),
            row.getCell((short) 4).getNumericCellValue(), row.getCell((short) 0).getStringCellValue(),
            province);/*from w  w w .j a  v a 2  s . c  o  m*/
}