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.knowgate.misc.CSVParser.java

License:Open Source License

public void parseSheet(HSSFSheet oSheet, String sFileDescriptor) {
      HSSFCell oCel;/*  ww w .  j a v a2s.  c om*/
      HSSFRow oRow = oSheet.getRow(0);
      int iRow;
      char cDelim;
      SimpleDateFormat oFmt4 = new SimpleDateFormat("yyyy-MM-dd");
      String[] aFileDescriptor;
      int iFileDescLen;

      if (isVoid(sFileDescriptor)) {
          iRow = 1;
          cDelim = '\t';
          sFileDescriptor = "";
          short iCel = (short) 0;
          oCel = oRow.getCell(iCel);
          while (oCel != null) {
              if (isVoid(oCel.getStringCellValue()))
                  break;
              sFileDescriptor += (sFileDescriptor.length() == 0 ? "" : "\t") + oCel.getStringCellValue();
              oCel = oRow.getCell(++iCel);
          } // wend
          aFileDescriptor = Gadgets.split(sFileDescriptor, cDelim);
          iFileDescLen = aFileDescriptor.length;
      } else {
          iRow = 1;
          cDelim = extractDelimiter(sFileDescriptor);
          aFileDescriptor = Gadgets.split(sFileDescriptor, cDelim);
          iFileDescLen = aFileDescriptor.length;
          for (int c = 0; c < iFileDescLen; c++) {
              oCel = oRow.getCell(c);
              if (null == oCel) {
                  iRow = 0;
                  break;
              } else if (!aFileDescriptor[c].equalsIgnoreCase(oCel.getStringCellValue())) {
                  iRow = 0;
                  break;
              }
          } //next
      } // fi

      StringBuffer oData = new StringBuffer(1024 * 1024);
      while (!isEmptyRow(oSheet.getRow(iRow), iFileDescLen) && iRow <= 65535) {
          oRow = oSheet.getRow(iRow);
          if (oRow.getCell(0) != null)
              oData.append(oRow.getCell(0).getStringCellValue());
          for (int c = 1; c < iFileDescLen; c++) {
              oData.append(cDelim);
              if (oRow.getCell(c) != null) {
                  int iCelType = oRow.getCell(c).getCellType();
                  switch (iCelType) {
                  case HSSFCell.CELL_TYPE_BLANK:
                      break;
                  case HSSFCell.CELL_TYPE_STRING:
                      oData.append(oRow.getCell(c).getStringCellValue().replace(cDelim, ' ').replace('\n', ' '));
                      break;
                  case HSSFCell.CELL_TYPE_NUMERIC:
                      switch (oRow.getCell(c).getCellStyle().getDataFormat()) {
                      case (short) 15: // m/d/yy
                      case (short) 16: // d-mmm-yy
                          oData.append(oFmt4.format(oRow.getCell(c).getDateCellValue()));
                          break;
                      default:
                          oData.append(String.valueOf(oRow.getCell(c).getNumericCellValue()));
                      }
                      break;
                  } // end switch                  
              } // fi 
          } // next
          oData.append('\n');
          iRow++;
      } // wend
      parseData(oData.toString().toCharArray(), sFileDescriptor);
  }

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;
    }/*w  w w .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();

            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\":[]");
    }//from   w  w  w . j a  va 2  s . co  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();
}

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;
    }//from w  w w. ja  v  a 2  s  .co  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\":[]");
    }/* www  .ja v  a2  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();
}

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }/* w w  w. j  a  va 2  s . c om*/

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                HSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = Double.toString(cell.getNumericCellValue());
                            java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                            DateFormat sdf = new SimpleDateFormat(df);
                            val = sdf.format(date1);
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        val = cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public JSONObject parseXLS1(String filename, int sheetNo, int startindex)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    ArrayList<String> arr = new ArrayList<String>();
    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;

    JSONArray jArr = new JSONArray();
    try {//from  www .j  a  va2  s. c  o  m
        for (int i = startindex; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum();
            }
            for (int j = 0; j < maxCol; j++) {
                HSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    arr.add(val);
                    continue;
                }
                ;
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = Double.toString(cell.getNumericCellValue());
                        java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        DateFormat sdf = new SimpleDateFormat(df);
                        val = sdf.format(date1);
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    val = cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                if (i == startindex) { // List of Headers (consider startindex row as a headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val);
                        jtemp1.put("index", j);
                        jobj.append("Header", jtemp1);
                        obj.put(colHeader, val);
                    }
                    arr.add(val);
                } else {
                    if (arr.get(j) != null)
                        obj.put(arr.get(j), val);
                }

            }
            if (obj.length() > 0) {
                jArr.put(obj);
            }

        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public int dumpXLSFileData(String filename, int sheetNo, int startindex) throws ServiceException {
    int dumpedRows = 0;
    try {//from   w  w  w . j av  a 2s.  c  o m
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        for (int i = startindex; i <= maxRow; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                HSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = Double.toString(cell.getNumericCellValue());
                        java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        DateFormat sdf = new SimpleDateFormat(df);//(df_full); //BUG:16085
                        val = sdf.format(date1);
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    val = cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                dumpedRows += importDao.dumpFileRow(tableName, dataArray.toArray());
            }
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    }
    return dumpedRows;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * Generate the preview of the xls grid/*w  w w .  j a  v  a 2  s.  c  o  m*/
 * @param filename
 * @param sheetNo
 * @return
 * @throws FileNotFoundException
 * @throws IOException
 * @throws JSONException
 */
public static JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    //DateFormat sdf = new SimpleDateFormat(df);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int maxSheetColCnt = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }
    int firstValidRec = 0;
    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header
                firstValidRec = i;

            //                    if(i==0) {
            maxCol = row.getLastCellNum();
            if (maxSheetColCnt < maxCol) // get max column count
                maxSheetColCnt = maxCol;
            //                    }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                HSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;

                if (cell != null) {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = cell.toString();//Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == firstValidRec) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxSheetColCnt);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename//  w ww  .ja va  2  s  . c o  m
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                HSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex);
    }
}