Example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getLastRowNum

Introduction

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

Prototype

@Override
public int getLastRowNum()

Source Link

Document

Gets the number last row on the sheet.

Usage

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;
    }/*  w w  w  . j  a  va  2s . 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.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 w  w .j a  va  2s.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.servlets.XLSDataExtractor.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();
    }/*from  w w w . j a v a  2  s. c  om*/

    JSONArray jArr = new JSONArray();
    for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
        Row row = sheet.getRow(i);
        JSONObject obj = new JSONObject();
        JSONObject jtemp1 = new JSONObject();
        if (row == null) {
            jArr.put(obj);
            continue;
        }
        if (maxCol < row.getLastCellNum())
            maxCol = row.getLastCellNum();
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell == null)
                continue;
            String colHeader = new CellReference(i, j).getCellRefParts()[2];
            String val = null;
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                val = Double.toString(cell.getNumericCellValue());
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                    String df_full = "yyyy-MM-dd";
                    DateFormat sdf = new SimpleDateFormat(df_full);
                    val = sdf.format(df);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                val = cell.getRichStringCellValue().getString();
                break;
            }
            if (i == 0) { // List of Headers (Consider first row as Headers)
                jtemp1 = new JSONObject();
                jtemp1.put("header", val);
                jtemp1.put("index", j);
                jobj.append("Header", jtemp1);
            }
            obj.put(colHeader, val);
        }
        jArr.put(obj);
    }
    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", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.esp.servlets.XLSDataExtractor.java

License:Open Source License

public JSONObject parseXLS1(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);
    ArrayList<String> arr = new ArrayList<String>();
    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;

    JSONArray jArr = new JSONArray();
    try {//from   w  ww .  j  av  a 2 s .c o  m
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                jArr.put(obj);
                continue;
            }
            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell 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 Cell.CELL_TYPE_NUMERIC:
                    val = Double.toString(cell.getNumericCellValue());
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        String df_full = "yyyy-MM-dd";
                        DateFormat sdf = new SimpleDateFormat(df_full);
                        val = sdf.format(df);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    val = cell.getRichStringCellValue().getString();
                    break;
                }
                if (i == 0) { // List of Headers (Consider first row as Headers)
                    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);
                }

            }
            jArr.put(obj);
        }
    } catch (Exception ex) {
        Logger.getLogger(XLSDataExtractor.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", "Image 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 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();
    }/*ww  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 w  w w .  j a v  a  2 s . co  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   www  .  j av  a2 s.  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//from ww w  .ja va 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//from w ww  . j a va 2s.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);
    }
}