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.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java

License:Open Source License

public String[][] getExcelStringArray() throws Exception {
    if (mHssfSheet != null) {
        mExcelStringArray = new String[getRowLenght()][getColumnLenght()];
        for (int rowIndex = 0; rowIndex < getRowLenght(); rowIndex++) {
            HSSFRow row = mHssfSheet.getRow(rowIndex);
            for (int colIndex = 0; colIndex < getColumnLenght(); colIndex++) {
                HSSFCell cell = row.getCell(colIndex);
                mExcelStringArray[rowIndex][colIndex] = convertCellToString(cell);
            }//  w ww.  j ava  2s.  c o m
        }
        try {
            mHssfWorkbook.close();
            mFileInputStream.close();
        } catch (IOException ioe) {
            System.out.println("Error in closing the filestream");
        }
        return mExcelStringArray;
    } else {
        throw new Exception("Worksheet with name " + mWorksheetName + " does not exist in " + mFilePath);
    }
}

From source file:com.jk.framework.util.ExcelUtil.java

License:Apache License

private static Record populateRecordByFieldsIndex(final HSSFRow row, final TableMeta tableMeta,
        final Hashtable<String, Integer> customHeaders) {
    final Record record = tableMeta.createEmptyRecord();
    int index = 0;
    for (final Field field : record.getFields()) {
        final HSSFCell cell = row.getCell(index++);
        field.setValue(getCellValue(cell));
    }/*from ww  w.j a  va 2 s . c o  m*/
    return record;
}

From source file:com.jk.framework.util.ExcelUtil.java

License:Apache License

private static Record populateRecordByHeaders(final HSSFRow row, final TableMeta tableMeta,
        final Hashtable<String, Integer> customHeaders) {
    final Record record = tableMeta.createEmptyRecord();
    final Set<String> keySet = customHeaders.keySet();
    for (final String key : keySet) {
        final Integer index = customHeaders.get(key);
        final HSSFCell cell = row.getCell(index);
        record.setFieldValue(key, getCellValue(cell));
    }/* w w  w  .  j  a va  2 s . c o m*/
    return record;
}

From source file:com.kahlon.guard.controller.DocumentManager.java

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

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);

    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.WHITE.index);
    cellStyle.setFont(font);
    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);
    }
}

From source file:com.kcs.action.FrfImportAction.java

public void excelToList() throws Exception {
    String filePath = getServletRequest().getSession().getServletContext().getRealPath("/");
    try {/*ww w.  j  a  va2 s  .co m*/
        InputStream input = new BufferedInputStream(new FileInputStream(this.toBeUploaded));

        POIFSFileSystem fs = new POIFSFileSystem(input);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator rows = sheet.rowIterator();

        setResultsFromExcel(new ArrayList<Datasetfrf>());

        int checkColName = 0;
        setCheckDatasetDate("true");
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            if (checkColName != 0) {

                Datasetfrf datasetfrf = new Datasetfrf();

                Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate());
                Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(),
                        DateUtil.DATE_FORMAT_YYYYMMDDX);
                if (dateFromJsp.compareTo(dateFromExcel) != 0) {
                    setCheckDatasetDate("false");
                    break;
                }
                int index = 0;
                datasetfrf.setOrgId(row.getCell(index++).toString());
                datasetfrf.setDataSetDate(convertDate(row.getCell(index++).toString()));
                datasetfrf.setLoanDepsitTrnTye(row.getCell(index++).toString());
                datasetfrf.setCurrCode(row.getCell(index++).toString());
                datasetfrf.setPaymentMethod(row.getCell(index++).toString());
                datasetfrf.setBrOrBcFlg(row.getCell(index++).toString());
                datasetfrf.setCommInLieuRate(row.getCell(index++).toString());
                datasetfrf.setMinCommInLieu(row.getCell(index++).toString());
                datasetfrf.setMaxCommInLieu(row.getCell(index++).toString());
                datasetfrf.setOthFeeDesc(row.getCell(index++).toString());
                datasetfrf.setEffectiveDate(convertDate(row.getCell(index++).toString()));
                datasetfrf.setEndDate(convertDate(row.getCell(index++).toString()));
                datasetfrf.setSeq(Long.valueOf(row.getCell(index++).toString()));
                datasetfrf.setUpdBy(getCurrentLoginId());
                datasetfrf.setUpdDate(DateUtil.getCurrentDateTime());
                index++;
                index++;
                datasetfrf.setSysCode(row.getCell(index++).toString());

                getResultsFromExcel().add(datasetfrf);

            }
            checkColName++;
        }

        setResultsFromExcel(getResultsFromExcel());
        session.put("EXCEL_TO_LIST_FRF", getResultsFromExcel());

    } catch (IOException ex) {
        ex.printStackTrace();
        logger.error("Error >>> " + ex.getMessage());
    }
}

From source file:com.kcs.action.FrwImportAction.java

public void excelToList() throws Exception {
    String filePath = getServletRequest().getSession().getServletContext().getRealPath("/");
    try {// w ww. j  av a  2  s  .c  om
        InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded()));

        POIFSFileSystem fs = new POIFSFileSystem(input);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator rows = sheet.rowIterator();

        setResultsFromExcel(new ArrayList<Datasetfrw>());

        int checkColName = 0;
        setCheckDatasetDate("true");
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            if (checkColName != 0) {

                Datasetfrw datasetfrw = new Datasetfrw();

                Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate());
                Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(),
                        DateUtil.DATE_FORMAT_YYYYMMDDX);
                if (dateFromJsp.compareTo(dateFromExcel) != 0) {
                    setCheckDatasetDate("false");
                    break;
                }
                int index = 0;
                datasetfrw.setOrgId(row.getCell(index++).toString());
                datasetfrw.setDataSetDate(convertDate(row.getCell(index++).toString()));
                datasetfrw.setCurrCode(row.getCell(index++).toString());
                datasetfrw.setCommInLieuRateForDepsit(row.getCell(index++).toString());
                datasetfrw.setMinCommInLieuForDepsit(row.getCell(index++).toString());
                datasetfrw.setMaxCommInLieuForDepsit(row.getCell(index++).toString());
                datasetfrw.setInwTransfFeeForDepsit(row.getCell(index++).toString());
                datasetfrw.setMinInwTransfFeeForDepsit(row.getCell(index++).toString());
                datasetfrw.setMaxInwTransfFeeForDepsit(row.getCell(index++).toString());
                datasetfrw.setOthFeeDescForDepsit(row.getCell(index++).toString());
                datasetfrw.setCommInLieuRateForWithdw(row.getCell(index++).toString());
                datasetfrw.setMinCommInLieuRateFWithdw(row.getCell(index++).toString());
                datasetfrw.setMaxCommInLieuRateFWithdw(row.getCell(index++).toString());
                datasetfrw.setWithdwFeeForOthTransf(row.getCell(index++).toString());
                datasetfrw.setEffectiveDate(convertDate(row.getCell(index++).toString()));
                datasetfrw.setEndDate(convertDate(row.getCell(index++).toString()));
                datasetfrw.setSeq(Long.valueOf(row.getCell(index++).toString()));
                datasetfrw.setUpdBy(getCurrentLoginId());
                datasetfrw.setUpdDate(DateUtil.getCurrentDateTime());
                index++;
                index++;
                datasetfrw.setSysCode(row.getCell(index++).toString());

                getResultsFromExcel().add(datasetfrw);

            }
            checkColName++;
        }

        setResultsFromExcel(getResultsFromExcel());
        session.put("EXCEL_TO_LIST_FRW", getResultsFromExcel());

    } catch (IOException ex) {
        ex.printStackTrace();
        logger.error("Error >>> " + ex.getMessage());
    }
}

From source file:com.kcs.action.ImportExcelDsDipAction.java

public void excelToList() throws Exception {
    String filePath = getServletRequest().getSession().getServletContext().getRealPath("/");
    try {/*from   ww w .j  a  va2s.  co  m*/
        InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded()));

        POIFSFileSystem fs = new POIFSFileSystem(input);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator rows = sheet.rowIterator();

        setResultsFromExcel(new ArrayList<Datasetdip>());

        int checkColName = 0;
        setCheckDatasetDate("true");
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            if (checkColName != 0) {

                Datasetdip datasetdip = new Datasetdip();

                Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate());
                Date dateFromExcel = DateUtil.getDateFromString(row.getCell(3).toString(),
                        DateUtil.DATE_FORMAT_YYYYMMDDX);
                logger.debug("dateFromJsp >>> " + dateFromJsp);
                logger.debug("dateFromExcel >>> " + dateFromExcel);

                if (dateFromJsp.compareTo(dateFromExcel) != 0) {
                    setCheckDatasetDate("false");
                    break;
                }
                int index = 0;
                datasetdip.setOrgId(row.getCell(index++).toString());
                datasetdip.setDataPvdrBrcNo(row.getCell(index++).toString());
                datasetdip.setFiRptGrp(row.getCell(index++).toString());
                datasetdip.setDataSetDate(convertDate(row.getCell(index++).toString()));
                datasetdip.setItemType(row.getCell(index++).toString());
                datasetdip.setItemDesc(row.getCell(index++).toString());
                datasetdip.setCtryId(row.getCell(index++).toString());
                datasetdip.setCurr(row.getCell(index++).toString());
                datasetdip.setAmt(new BigDecimal(row.getCell(index++).toString()));
                datasetdip.setUpdDate(DateUtil.getCurrentDateTime());
                datasetdip.setUpdBy(getCurrentLoginId());

                index++;
                index++;
                datasetdip.setSysCode(row.getCell(index++).toString());
                datasetdip.setSeq(Long.valueOf(row.getCell(index++).toString()));
                datasetdip.setCustCode(row.getCell(index++).toString());

                getResultsFromExcel().add(datasetdip);

            }
            checkColName++;
        }

        setResultsFromExcel(getResultsFromExcel());
        session.put("EXCEL_TO_LIST_DIP", getResultsFromExcel());

    } catch (IOException ex) {
        ex.printStackTrace();
        logger.error("Error >>> " + ex.getMessage());
    }
}

From source file:com.kcs.action.IrfImportAction.java

public void excelToList() throws Exception {
    String filePath = servletRequest.getSession().getServletContext().getRealPath("/");
    try {/*from   w w  w.j  a  v a 2s  . c o  m*/
        InputStream input = new BufferedInputStream(new FileInputStream(this.toBeUploaded));

        POIFSFileSystem fs = new POIFSFileSystem(input);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator rows = sheet.rowIterator();

        resultsFromExcel = new ArrayList<Datasetirf>();

        int checkColName = 0;
        setCheckDatasetDate("true");
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            if (checkColName != 0) {

                Datasetirf datasetirf = new Datasetirf();

                Date dateFromJsp = DateUtil.convertDateFromJsp(dataSetDate);
                Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(),
                        DateUtil.DATE_FORMAT_YYYYMMDDX);
                if (dateFromJsp.compareTo(dateFromExcel) != 0) {
                    setCheckDatasetDate("false");
                    break;
                }

                datasetirf.setOrgId(row.getCell(0).toString());
                datasetirf.setDataSetDate(
                        DateUtil.parse(row.getCell(1).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX));
                datasetirf.setArrgmentTye(row.getCell(2).toString());
                datasetirf.setInvPartyTye(row.getCell(3).toString());
                datasetirf.setCurrCode(row.getCell(4).toString());
                datasetirf.setDepsitTerm(Long.valueOf(row.getCell(5).toString()));
                datasetirf.setDepsitTermUnt(row.getCell(6).toString());
                datasetirf.setBalTierAmt(row.getCell(7).toString());
                datasetirf
                        .setInterestRate(NumberUtil.convertToBigDecimal(row.getCell(8).toString()).setScale(2));
                datasetirf.setEffectiveDate(
                        DateUtil.parse(row.getCell(9).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX));

                if (!row.getCell(10).toString().equals("null") || row.getCell(10).toString().equals(null)) {
                    datasetirf.setEndDate(
                            DateUtil.parse(row.getCell(10).toString(), DateUtil.DATE_FORMAT_YYYYMMDDX));
                } else {
                    datasetirf.setEndDate(null);
                }

                datasetirf.setSeq(Long.valueOf(row.getCell(11).toString()));
                datasetirf.setUpdBy(getCurrentLoginId());
                datasetirf.setUpdDate(DateUtil.getCurrentDateTime());
                datasetirf.setSysCode(row.getCell(14).toString());

                resultsFromExcel.add(datasetirf);

            }
            checkColName++;
        }

        setResultsFromExcel(resultsFromExcel);
        session.put("EXCEL_TO_LIST", resultsFromExcel);

    } catch (IOException ex) {
        ex.printStackTrace();
        logger.error("Error >>> " + ex.getMessage());
    }
}

From source file:com.kcs.action.OffImportAction.java

public void excelToList() throws Exception {
    String filePath = getServletRequest().getSession().getServletContext().getRealPath("/");
    try {/*from www .j a v a 2 s  . c  o m*/
        InputStream input = new BufferedInputStream(new FileInputStream(this.getToBeUploaded()));

        POIFSFileSystem fs = new POIFSFileSystem(input);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator rows = sheet.rowIterator();

        setResultsFromExcel(new ArrayList<Datasetoff>());

        int checkColName = 0;
        setCheckDatasetDate("true");
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            if (checkColName != 0) {

                Datasetoff datasetoff = new Datasetoff();

                Date dateFromJsp = DateUtil.convertDateFromJsp(getDataSetDate());
                Date dateFromExcel = DateUtil.getDateFromString(row.getCell(1).toString(),
                        DateUtil.DATE_FORMAT_YYYYMMDDX);
                if (dateFromJsp.compareTo(dateFromExcel) != 0) {
                    setCheckDatasetDate("false");
                    break;
                }
                int index = 0;
                datasetoff.setOrgId(row.getCell(index++).toString());
                datasetoff.setDataSetDate(convertDate(row.getCell(index++).toString()));
                datasetoff.setArrgmentTye(row.getCell(index++).toString());
                datasetoff.setCurrCode(row.getCell(index++).toString());
                datasetoff.setMinimOpenAcctAmt(row.getCell(index++).toString());
                datasetoff.setMinimBalForMaintenanceFee(row.getCell(index++).toString());
                datasetoff.setMaintenanceFee(row.getCell(index++).toString());
                datasetoff.setInactiveTerm(convertBigDecimal(row.getCell(index++).toString()));
                datasetoff.setInactiveTermUnt(row.getCell(index++).toString());
                datasetoff.setInactiveFee(row.getCell(index++).toString());
                datasetoff.setEffectiveDate(convertDate(row.getCell(index++).toString()));
                datasetoff.setEndDate(convertDate(row.getCell(index++).toString()));
                datasetoff.setSeq(Long.valueOf(row.getCell(index++).toString()));
                datasetoff.setUpdBy(getCurrentLoginId());
                datasetoff.setUpdDate(DateUtil.getCurrentDateTime());
                index++;
                index++;
                datasetoff.setSysCode(row.getCell(index++).toString());

                getResultsFromExcel().add(datasetoff);

            }
            checkColName++;
        }

        setResultsFromExcel(getResultsFromExcel());
        session.put("EXCEL_TO_LIST_FRF", getResultsFromExcel());

    } catch (IOException ex) {
        ex.printStackTrace();
        logger.error("Error >>> " + ex.getMessage());
    }
}

From source file:com.knowgate.misc.CSVParser.java

License:Open Source License

private boolean isEmptyRow(HSSFRow oRow, int nCols) {
      if (null == oRow)
          return true;
      for (int c = 0; c < nCols; c++) {
          if (oRow.getCell(c) != null) {
              if (!isVoid(oRow.getCell(c).getStringCellValue()))
                  return false;
          }/*from   w  w w.j  av a2 s .c o m*/
      }
      return true;
  }