Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

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

public void excelToList() throws Exception {
    String filePath = getServletRequest().getSession().getServletContext().getRealPath("/");
    try {//  w w  w.  j  a  v  a  2  s  .  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();

        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 w w . ja v  a2s .  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<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 {/*  w w  w  . ja  va2 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<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 ww .ja  v a  2  s  .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  w  ww. j  av  a 2 s  .  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<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

/**
   * <p>Parse a delimited text file</p>
   * Parsed values are stored at an internal array in this CSVParser.<br>
   * File is readed using the character set specifid at constructor
   * @param oFile CSV File//from w w  w  .j a va 2  s .  com
   * @param sFileDescriptor A list of column names separated by ',' ';' '|' '`' or '\t'.
   * Column names may be quoted. Lines are delimiter by '\n' characters<br>
   * Example 1) tx_mail,tx_name,tx_surname<br>
   * Example 2) "tx_name","tx_surname","tx_salutation"<br>
   * @throws IOException
   * @throws FileNotFoundException
   * @throws ArrayIndexOutOfBoundsException Delimited values for a file is greater
   * than columns specified at descriptor.
   * @throws RuntimeException If delimiter is not one of { ',' ';' '|' '`' or '\t' }
   * @throws NullPointerException if oFile or sFileDescriptor are <b>null</b>
   * @throws IllegalArgumentException if sFileDescriptor is ""
   * @throws UnsupportedEncodingException
   * @since 3.0
   */
  public void parseFile(File oFile, String sFileDescriptor)
          throws ArrayIndexOutOfBoundsException, IOException, FileNotFoundException, RuntimeException,
          NullPointerException, IllegalArgumentException, UnsupportedEncodingException {

      Reader oReader;

      if (oFile == null)
          throw new NullPointerException("CSVParser.parseFile() File parameter may not be null");

      if (DebugFile.trace) {
          DebugFile.writeln(
                  "Begin CSVParser.parseFile(\"" + oFile.getAbsolutePath() + "\",\"" + sFileDescriptor + "\")");
          DebugFile.incIdent();
      }

      if (sFileDescriptor == null && !oFile.getName().endsWith(".xls")) {
          if (DebugFile.trace)
              DebugFile.decIdent();
          throw new NullPointerException("CSVParser.parseFile() File Descriptor parameter may not be null");
      }

      if (sFileDescriptor.trim().length() == 0 && !oFile.getName().endsWith(".xls")) {
          if (DebugFile.trace)
              DebugFile.decIdent();
          throw new IllegalArgumentException("File Descriptor parameter may not be an empty string");
      }

      iErrLine = 0;

      iBuffer = new Long(oFile.length()).intValue();

      if (iBuffer == 0) {
          iRows = 0;
          if (DebugFile.trace) {
              DebugFile.decIdent();
              DebugFile.writeln("End CSVParser.parseFile() : zero length file");
          }
          return;
      }

      if (oFile.getName().endsWith(".xls")) {
          FileInputStream oFistrm = new FileInputStream(oFile);
          HSSFWorkbook oWrkb = new HSSFWorkbook(oFistrm);
          HSSFSheet oSheet = oWrkb.getSheetAt(0);
          oFistrm.close();
          parseSheet(oSheet, sFileDescriptor);
      } else {

          cBuffer = new char[iBuffer];

          if (null == sCharSet) {
              oReader = new FileReader(oFile);
          } else {
              oReader = new InputStreamReader(new FileInputStream(oFile), sCharSet);
          }
          oReader.read(cBuffer);
          oReader.close();
          oReader = null;

          // Skip Unicode characters prolog
          if (sCharSet == null) {
              parseData(cBuffer, sFileDescriptor);
          } else {
              if (sCharSet.startsWith("UTF") || sCharSet.startsWith("utf") || sCharSet.startsWith("Unicode")) {
                  int iSkip = 0;
                  if ((int) cBuffer[0] == 65279 || (int) cBuffer[0] == 65533 || (int) cBuffer[0] == 65534)
                      iSkip++;
                  if ((int) cBuffer[1] == 65279 || (int) cBuffer[1] == 65533 || (int) cBuffer[1] == 65534)
                      iSkip++;

                  if (0 == iSkip)
                      parseData(cBuffer, sFileDescriptor);
                  else
                      parseData(Arrays.copyOfRange(cBuffer, iSkip, iBuffer), sFileDescriptor);
              } else {
                  parseData(cBuffer, sFileDescriptor);
              }
          }
      }

      if (DebugFile.trace) {
          DebugFile.decIdent();
          DebugFile.writeln("End CSVParser.parseFile()");
      }
  }

From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java

License:Open Source License

public String extractText(String filepath) throws Exception {

    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    String resultText = "";
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return resultText;
    }/*from w w w.j  a  v  a2s .c om*/

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {
                        /*
                         * if(HSSFDateUtil.isCellDateFormatted(cell) ==
                         * true) { resultText +=
                         * cell.getDateCellValue().toString() + " "; } else
                         */
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            resultText += "           ";

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            resultText += cell.getRichStringCellValue().toString() + " ";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            resultText += d.toString() + " ";
                        }

                        /*
                         * else if(cell.getCellType() ==
                         * HSSFCell.CELL_TYPE_FORMULA){ resultText +=
                         * cell.getCellFormula() + " "; }
                         */
                    }
                } catch (Exception ex) {

                }
            }
            resultText += "\n";
        }
    }
    if (input != null) {
        input.close();
    }
    return resultText;
}

From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java

License:Open Source License

public String getFormatedJSON(String filepath) throws Exception {
    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    JSONObject jobj = new JSONObject();
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return ("\"data\":[]");
    }// w w w .  j  av  a2 s. c  om
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();
            JSONObject temp = new JSONObject();
            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {

                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            temp.put("cell" + cell.getCellNum(), "");

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString());
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            temp.put("cell" + cell.getCellNum(), d.toString());
                        }
                    }

                } catch (Exception ex) {

                }
            }
            jobj.append("data", temp);
            temp = null;
        }
    }
    if (input != null) {
        input.close();
    }
    return jobj.toString();
}

From source file:com.krawler.esp.fileparser.excelparser.MsExcelParser.java

License:Open Source License

public String extractText(String filepath) throws Exception {

    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    String resultText = "";
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return resultText;
    }// ww  w.  ja va 2s . c o m

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {
                        /*
                         * if(HSSFDateUtil.isCellDateFormatted(cell) ==
                         * true) { resultText +=
                         * cell.getDateCellValue().toString() + " "; } else
                         */
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            resultText += "           ";

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            resultText += cell.getRichStringCellValue().toString() + " ";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            resultText += d.toString() + " ";
                        }

                        /*
                         * else if(cell.getCellType() ==
                         * HSSFCell.CELL_TYPE_FORMULA){ resultText +=
                         * cell.getCellFormula() + " "; }
                         */
                    }
                } catch (Exception ex) {
                }
            }
            resultText += "\n";
        }
    }
    if (input != null) {
        input.close();
    }
    return resultText;
}

From source file:com.krawler.esp.fileparser.excelparser.MsExcelParser.java

License:Open Source License

public String getFormatedJSON(String filepath) throws Exception {
    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    JSONObject jobj = new JSONObject();
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return ("\"data\":[]");
    }//from  w ww. j a  v  a  2 s . c  o m
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();
            JSONObject temp = new JSONObject();
            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {

                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            temp.put("cell" + cell.getCellNum(), "");

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString());
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            temp.put("cell" + cell.getCellNum(), d.toString());
                        }
                    }

                } catch (Exception ex) {
                }
            }
            jobj.append("data", temp);
            temp = null;
        }
    }
    if (input != null) {
        input.close();
    }
    return jobj.toString();
}