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.util.tools.FeraExporter.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*from w w  w . ja va 2s.c  o  m*/
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }
}

From source file:com.verticon.treatment.poi.handlers.NamingImportHandler.java

License:Open Source License

static String getStringValue(HSSFRow row, int index) throws Exception {
    String result = null;/*from  w w w.  j av a2  s . c  o m*/

    if (index != -1) {

        HSSFCell cellContents = row.getCell(index);
        if (cellContents != null) {
            switch (cellContents.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                result = cellContents.getStringCellValue();
                break;

            default:
                throw new Exception(
                        "The string value in a critical spreadsheet cell has the wrong data type. Please make sure your spreadsheet column number "
                                + index + " is set to the string datatype. Row: " + row.getRowNum()
                                + " Column Index: " + index);

            }

        }

    }
    return result;
}

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

static String getStringValue(HSSFRow row, EStructuralFeature feature, int index)
        throws MissingCriticalDataException {
    String result = null;/*from ww  w.j  a v a  2 s.  c o m*/

    if (index != -1) {
        try {
            HSSFCell cellContents = row.getCell(index);
            if (cellContents != null) {
                switch (cellContents.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cellContents.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    result = Double.toString(cellContents.getNumericCellValue());
                    result = result.replace(".0", "");
                    break;
                default:
                    throw new MissingCriticalDataException(
                            "The string value in a critical spreadsheet cell has the wrong data type (id: "
                                    + cellContents.getCellType()
                                    + "). Please make sure your spreadsheet column number " + index
                                    + " is set to the string datatype.",
                            index, feature, row.getRowNum());

                }

            }
        } catch (RuntimeException e) {
            // just fall through and return a null
        }
    }
    return result;
}

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

static String getValue(HSSFRow row, EStructuralFeature feature, int index) {
    String result = null;/*from ww  w .j a va  2s.  c om*/

    if (index != -1) {
        try {
            HSSFCell cellContents = row.getCell(index);
            if (cellContents != null) {
                switch (cellContents.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cellContents.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    double num = cellContents.getNumericCellValue();
                    long l = (long) num;
                    result = Long.toString(l);
                    break;
                default:
                    break;
                }

            }
        } catch (RuntimeException e) {
            //just fall through and return a null
        }
    }
    return result;
}

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

private static BigDecimal getDecimalValue(HSSFRow row, EAttribute feature, int index) {
    BigDecimal result = null;//from www  .j ava 2s .  c  om

    if (index != -1) {
        try {
            HSSFCell cellContents = row.getCell(index);
            if (cellContents != null) {
                switch (cellContents.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    result = new BigDecimal(cellContents.getNumericCellValue());
                    break;
                default:
                    System.err.printf("Wrong type for decimal %s%n", cellContents.getCellType());
                    break;
                }

            }
        } catch (RuntimeException e) {
            // just fall through and return a null
        }
    }
    return result;
}

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

private static Date getDateValue(HSSFRow row, EAttribute feature, int index) {
    Date result = null;//from www  . j a  va2s .  c om

    if (index != -1) {
        try {
            HSSFCell cellContents = row.getCell(index);
            if (cellContents != null) {
                switch (cellContents.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    result = cellContents.getDateCellValue();
                    break;
                default:
                    System.err.printf("Wrong type for date %s%n", cellContents.getCellType());
                    break;
                }

            }
        } catch (RuntimeException e) {
            // just fall through and return a null
        }
    }
    return result;
}

From source file:com.viettel.vsaadmin.database.DAO.UsersDAO.java

License:Open Source License

public String importStaffFromExcel() throws Exception {
    List customInfo = new ArrayList();//lst users
    Long attachId = Long.parseLong(getRequest().getParameter("attachId"));//get attactId
    VoAttachs att = (VoAttachs) getSession().get("com.viettel.voffice.database.BO.VoAttachs", attachId);//Attachs BO
    if (att == null) {// if att null return error users
        customInfo.add("error");
    } else {//from   w w  w. j  a va 2  s.  c  o m

        ResourceBundle rb = ResourceBundle.getBundle("config");//get link tuong doi
        String dir = rb.getString("directory");
        String linkFile = att.getAttachPath();
        linkFile = dir + linkFile;
        InputStream myxls = new FileInputStream(linkFile);//get file excel
        //Get the workbook instance for XLS file
        HSSFWorkbook wb = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        //            HSSFRow firstRow = sheet.getRow(1);
        int rowNums = sheet.getLastRowNum();
        //            UsersDAOHE sdhe = new UsersDAOHE();
        SimpleDateFormat formatter = new SimpleDateFormat("dd/mm/yyyy");
        String userError = "";

        for (int i = 1; i <= rowNums; i++) {
            try {
                row = sheet.getRow(i);
                if (row != null) {
                    Users entity = new Users();
                    HSSFCell cellUserName = row.getCell((short) 0);
                    HSSFCell cellFullName = row.getCell((short) 1);
                    HSSFCell cellEmail = row.getCell((short) 2);
                    HSSFCell cellCellPhone = row.getCell((short) 3);
                    HSSFCell cellDeptName = row.getCell((short) 4);
                    HSSFCell cellBusiness = row.getCell((short) 5);
                    HSSFCell cellPosition = row.getCell((short) 6);
                    HSSFCell cellGender = row.getCell((short) 7);
                    HSSFCell cellTelephone = row.getCell((short) 8);
                    HSSFCell cellFax = row.getCell((short) 9);
                    HSSFCell cellDateOfBirth = row.getCell((short) 10);
                    HSSFCell cellBirthPlace = row.getCell((short) 11);
                    HSSFCell cellStaffCode = row.getCell((short) 12);
                    HSSFCell cellIdentityCard = row.getCell((short) 13);
                    HSSFCell cellIssueDateIdent = row.getCell((short) 14);
                    HSSFCell cellIssuePlaceIdent = row.getCell((short) 15);
                    HSSFCell cellDescription = row.getCell((short) 16);
                    //validate input
                    if (cellUserName != null) {
                        entity.setUserName(cellUserName.toString());
                    } else {
                        userError += i + " li Ti khon,";
                        customInfo.add(userError);
                    }

                    if (cellFullName != null) {
                        entity.setFullName(cellFullName.toString());
                    } else {
                        userError += i + " li Tn y ,";
                        customInfo.add(userError);
                    }

                    if (cellEmail.toString() != null && cellEmail.toString().length() > 0) {
                        entity.setEmail(cellEmail.toString());
                    }

                    if (cellCellPhone.toString() != null && cellCellPhone.toString().length() > 0) {
                        entity.setCellphone(cellCellPhone.toString());
                    }
                    //get dept
                    DepartmentDAOHE deptdhe = new DepartmentDAOHE();
                    Department deptBo = deptdhe.findByName(cellDeptName.toString());
                    if (deptBo != null) {
                        entity.setDeptName(deptBo.getDeptName());
                        entity.setDeptId(deptBo.getDeptId());
                    }
                    //get business
                    BusinessDAOHE busdhe = new BusinessDAOHE();
                    Business busbo = busdhe.findByName(cellBusiness.toString());
                    if (busbo != null) {
                        entity.setBusinessId(busbo.getBusinessId());
                        entity.setBusinessName(busbo.getBusinessName());
                    }
                    //get posId
                    PositionDAOHE posdhe = new PositionDAOHE();
                    Position pos = posdhe.findByName(cellPosition.toString());
                    if (pos != null) {
                        entity.setPosId(pos.getPosId());
                    } else {
                        userError += i + " li Chc v,";
                        customInfo.add(userError);
                    }
                    if (cellTelephone != null) {
                        entity.setTelephone(cellTelephone.toString());
                    }
                    if (cellFax != null) {
                        entity.setFax(cellFax.toString());
                    }
                    if (cellBirthPlace != null) {
                        entity.setBirthPlace(cellBirthPlace.toString());
                    }
                    if (cellStaffCode != null) {
                        entity.setStaffCode(cellStaffCode.toString());
                    }
                    if (cellIdentityCard != null) {
                        entity.setIdentityCard(cellIdentityCard.toString());
                    }
                    if (cellIssuePlaceIdent != null) {
                        entity.setIssuePlaceIdent(cellIssuePlaceIdent.toString());
                    }
                    if (cellIssueDateIdent != null && cellIssueDateIdent.toString().length() > 0) {
                        entity.setIssueDateIdent(formatter.parse(cellIssueDateIdent.toString()));
                    }
                    if (cellDateOfBirth != null) {
                        entity.setDateOfBirth(formatter.parse(cellDateOfBirth.toString()));
                    }
                    if (cellDescription != null) {
                        entity.setDescription(cellDescription.toString());
                    }
                    // end validate input
                    String passwordEncrypt = PasswordService.getInstance().encrypt("Attp@123");
                    entity.setPassword(passwordEncrypt);
                    entity.setPasswordchanged(0L);
                    entity.setStatus(1L);
                    //                        entity.setDeptId(Long.parseLong(cellDeptId.toString()));
                    //                        entity.setPosId(Long.parseLong(cellPosId.toString()));
                    //                        entity.setStatus(Long.parseLong(cellStatus.toString()));
                    //                        entity.setGender(Long.parseLong(cellGender.toString()));
                    String gender;
                    if (cellGender == null) {
                        userError += i + " li Gii tnh,";
                        customInfo.add(userError);
                    } else {
                        gender = cellGender.toString().trim().toLowerCase();
                        if (gender.contains("Nam") || gender.contains("man") || gender.contains("male")) {
                            entity.setGender(0L);
                        } else {
                            entity.setGender(1L);
                        }
                    }
                    if (entity != null) {
                        getSession().saveOrUpdate(entity);
                        RoleUser roleUser = new RoleUser();
                        roleUser.setIsActive(1L);
                        roleUser.setIsAdmin(0L);
                        roleUser.setUserId(entity.getUserId());
                        roleUser.setRoleId(323L);
                        roleUser.setRoleUserPK(new RoleUserPK(322, entity.getUserId()));
                        getSession().saveOrUpdate(roleUser);
                        customInfo.add("success");
                    } else {
                        userError += i + ",";
                        customInfo.add(userError);
                    }

                } // end if row != null
            } // end if att != null
            catch (Exception ex) {
                LogUtil.addLog(ex);//binhnt sonar a160901
                userError += i + ",";
                customInfo.add(userError);
                //                    jsonDataGrid.setCustomInfo(customInfo);
                //                    return "gridData";
            }
        } // end loop

    }
    this.jsonDataGrid.setCustomInfo(customInfo);
    return "gridData";
}

From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java

License:Open Source License

public static void createCellNoBorder(HSSFRow row, short column, String value, HSSFWorkbook wb) {

    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    HSSFCell cell = row.getCell(column);
    if (cell == null)
        cell = row.createCell(column);/*from   w  ww .  j  a  v a  2s.  c  o m*/
    //cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(value);
    cell.setCellStyle(style);
}

From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java

License:Open Source License

public static void createCell(HSSFRow row, short column, String value, HSSFWorkbook wb) {

    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFCell cell = row.getCell(column);
    if (cell == null)
        cell = row.createCell(column);//w  w  w .j  a  va  2s  . c  om
    //cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(value);
    cell.setCellStyle(style);
}

From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java

License:Open Source License

public static void createCellAlignLeft(HSSFRow row, short column, String value, HSSFWorkbook wb) {

    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFCell cell = row.getCell(column);
    if (cell == null)
        cell = row.createCell(column);//from  w ww.  jav a  2s . co m
    //cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(value);
    cell.setCellStyle(style);
}