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.pureinfo.studio.db.xls2srm.impl.XlsObjectsImpl.java

License:Open Source License

/**
 * @see com.pureinfo.dolphin.model.IObjects#next()
 *//*w w w.j a v a2  s. c  om*/
public DolphinObject next() throws PureException {
    HSSFRow row = m_sheet.getRow(m_nCurrent++);
    if (row == null)
        return null;

    //else
    DolphinObject obj = new DolphinObject();
    Object oValue;
    HSSFCell cell;

    int nCellNum = row.getLastCellNum();
    if (nCellNum > m_heads.length) {
        nCellNum = m_heads.length;
    }
    for (int i = 0; i < nCellNum; i++) {
        cell = row.getCell((short) i);
        if (cell == null) {
            oValue = null;
        } else {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                oValue = new Double(cell.getNumericCellValue());
                break;

            case HSSFCell.CELL_TYPE_STRING:
                oValue = cell.getStringCellValue();
                if (oValue != null)
                    oValue = ((String) oValue).trim();
                break;

            case HSSFCell.CELL_TYPE_FORMULA:
                oValue = new Double(cell.getNumericCellValue());
                break;

            case HSSFCell.CELL_TYPE_BOOLEAN:
                oValue = new Boolean(cell.getBooleanCellValue());
                break;

            case HSSFCell.CELL_TYPE_ERROR:
                throw new PureException(PureException.INVALID_VALUE, "error value in cell[" + i + "]-"
                        + m_heads[i] + ": " + String.valueOf(cell.getErrorCellValue()));
                //case HSSFCell.CELL_TYPE_BLANK:
            default:
                oValue = null;
            }//endcase

            if (oValue instanceof Number) {
                int nFormat = cell.getCellStyle().getDataFormat();
                if (nFormat >= 0xe && nFormat <= 0x16) {
                    oValue = cell.getDateCellValue();
                } else if (nFormat == 1) {
                    oValue = new Long(((Number) oValue).intValue());
                }
            }
        }

        obj.setProperty(m_heads[i], oValue);
    }
    return obj;
}

From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java

License:Open Source License

public String processExcel(String fileName, ESupplyGlobalParameters loginBean) throws EJBException {
    long startTime = System.currentTimeMillis();
    String s1 = "";
    PreparedStatement pstmtMainHeader = null;
    PreparedStatement pstmtDetails = null;
    PreparedStatement pstmtDetailsData = null;
    PreparedStatement MainHeader = null;
    PreparedStatement Details = null;
    PreparedStatement DetailsData = null;
    Connection connection = null;
    OperationsImpl operationsImpl = null;
    String dateFormat = null;// w  w w . j a  v  a2s  . c o m
    String deleteDtldata = "DELETE FROM QMS_STG_BUYRATES_DETAILS_DATA";
    String deleteDtl = "DELETE FROM QMS_STG_BUYRATES_DETAILS";
    String deleteHeader = "DELETE FROM QMS_STG_BUYRATES_MAIN_HEADER";
    String selectMainHeader = "SELECT WEIGHT_BREAK FROM QMS_STG_BUYRATES_MAIN_HEADER";
    ResultSet rs = null;
    PreparedStatement pstmtHeader = null; //@@Added by Kameswari on 15/04/09
    String wtbreak = null;
    String returnstr = null;
    CallableStatement cstmt = null;
    double time = 0.0;
    String data = null;
    try {
        String s = "";
        String mainHeaderData[] = new String[8];
        int rows = 0;
        int cols = 0;
        int notesIndex = 0;
        int batchCount = 0;
        HSSFRow row = null;
        HSSFCell cell = null;

        InputStream myxls = new FileInputStream(fileName);
        HSSFWorkbook wb = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheetAt(0);
        rows = sheet.getPhysicalNumberOfRows();
        dateFormat = loginBean.getUserPreferences().getDateFormat();
        String queryMainHeader = "INSERT INTO QMS_STG_BUYRATES_MAIN_HEADER (SHIPMENT_MODE,CURRENCY,WEIGHT_BREAK,WEIGHT_CLASS,RATE_TYPE,UOM,CONSOLETYPE,DENSITY_RATIO,TERMINALID) VALUES (?,?,?,?,?,?,?,?,?)";
        //   String queryDetails      = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,?,?,?)";         
        String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,TO_DATE(?),TO_DATE(?),?)";
        String queryDetailsData = "INSERT INTO QMS_STG_BUYRATES_DETAILS_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINERS_VALUE,LOWER_BOUND,UPPER_BOUND,RATE_DESCRIPTION) VALUES (?,?,?,?,?,?,?)";
        String msgquery = "SELECT ERROR_MSG FROM QMS_STG_BUYRATES_DETAILS";
        //   connection=ConnectionUtil.getConnection();
        operationsImpl = new OperationsImpl();
        connection = operationsImpl.getConnection();
        DetailsData = connection.prepareStatement(deleteDtldata);
        Details = connection.prepareStatement(deleteDtl);
        MainHeader = connection.prepareStatement(deleteHeader);
        DetailsData.execute();
        Details.execute();
        MainHeader.execute();
        // long startTime1=System.currentTimeMillis(); 
        //For MainHeader
        row = sheet.getRow(1);
        for (int k = 0; k < 8; k++) {
            cell = row.getCell((short) k);
            mainHeaderData[k] = getCellData(cell, 0);
        }
        pstmtMainHeader = connection.prepareStatement(queryMainHeader);

        insertMainHeaderData(mainHeaderData, pstmtMainHeader, loginBean);
        //End
        pstmtHeader = connection.prepareStatement(selectMainHeader);
        rs = pstmtHeader.executeQuery();
        if (rs.next()) {
            wtbreak = rs.getString("WEIGHT_BREAK");
        }
        //To get columns count , column index for NOTES and containers ids
        row = sheet.getRow(3);

        cols = row.getPhysicalNumberOfCells();

        int l = cols - 1;

        for (; l >= 0; l--) {
            cell = row.getCell((short) l);
            if ("NOTES".equalsIgnoreCase(getCellData(cell, l)))
                break;

        }

        notesIndex = l + 1;

        String detailsData[] = new String[notesIndex];
        String containersData[] = new String[notesIndex - 9];
        for (l = 8; l < notesIndex - 1; l++) {
            cell = row.getCell((short) l);
            containersData[l - 8] = getCellData(cell, l);
        }
        //End

        //For Details    
        pstmtDetails = connection.prepareStatement(queryDetails);
        ;
        pstmtDetailsData = connection.prepareStatement(queryDetailsData);
        for (int i = 4; i <= rows; i++) {

            row = sheet.getRow(i);
            //cols=row.getPhysicalNumberOfCells();
            //s=row.getRowNum() + " : ";          
            for (int j = 0; j < notesIndex; j++) {
                if (row != null) {
                    cell = row.getCell((short) j);

                    detailsData[j] = getCellData(cell, j);
                }
                // s = s+ " -- " + detailsData[j];                

                if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType())
                        && (detailsData[j].indexOf(".") != -1)
                        && (Double.parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) {
                    time = 1 / Double.parseDouble(detailsData[j]);
                    Double t = new Double(time);
                    if (t.toString().indexOf(".") != -1) {
                        data = t.toString().substring(0, t.toString().indexOf("."));
                    }
                    if (data != null) {
                        detailsData[j] = data + ":" + "00";
                    }
                }
                if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType())
                        && (detailsData[j].indexOf(".") != -1) && (!(Double
                                .parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) {
                    if (detailsData[j].indexOf(".") != -1) {
                        detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf("."));
                    }
                }
                if (j == 5 && detailsData[j].indexOf(".") != -1) {
                    detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf("."));
                }
            }

            //long EndTime1=System.currentTimeMillis();
            //System.out.println("\n------->>>> M.SEC : "+(EndTime1-startTime1) +"  SEC : " + (EndTime1-startTime1)/(1000) + " MIN :  "+(EndTime1-startTime1)/(1000*60));

            batchCount++;

            insertDetails(detailsData, containersData, pstmtDetails, pstmtDetailsData, notesIndex, i,
                    dateFormat, wtbreak);
            if (batchCount == 150) {
                pstmtDetails.executeBatch();
                pstmtDetailsData.executeBatch();
                pstmtDetails.clearBatch();
                pstmtDetailsData.clearBatch();
                batchCount = 0;
            }
            // s1=s1+s + "\n";         
        }

        if (batchCount > 0) {
            pstmtDetails.executeBatch();
            pstmtDetailsData.executeBatch();
            pstmtDetails.clearBatch();
            pstmtDetailsData.clearBatch();
            batchCount = 0;
        }

        cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_PROC(?)}");

        cstmt.registerOutParameter(1, Types.VARCHAR);
        cstmt.setString(2, loginBean.getTerminalId());
        cstmt.execute();
        returnstr = (String) cstmt.getString(1);

        //End
        return returnstr;
    } catch (Exception e) {
        logger.error("Exception");
        e.printStackTrace();
    } finally {
        try {
            if (pstmtMainHeader != null)
                pstmtMainHeader.close();
            if (pstmtDetails != null)
                pstmtDetails.close();
            if (pstmtDetailsData != null)
                pstmtDetailsData.close();
            if (cstmt != null)
                cstmt.close();
            if (connection != null)
                connection.close();
            if (rs != null)//Added by govind on 16-02-2010 for Connection Leakages
                rs.close();
            if (pstmtHeader != null)//Added by govind on 16-02-2010 for Connection Leakages
                pstmtHeader.close();
            if (MainHeader != null)//Added by govind on 16-02-2010 for Connection Leakages
                MainHeader.close();
            if (DetailsData != null) //Added by govind on 16-02-2010 for Connection Leakages
                DetailsData.close();
            if (Details != null)//Added by govind on 16-02-2010 for Connection Leakages
                Details.close();
        } catch (Exception e) {
            logger.error("Error Occured  while closing Resources" + e);
        }
    }

    long EndTime = System.currentTimeMillis();
    s1 = " M.SEC : " + (EndTime - startTime) + "  SEC : " + (EndTime - startTime) / (1000) + " MIN :  "
            + (EndTime - startTime) / (1000 * 60);
    return s1;
}

From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java

License:Open Source License

public String processExcelDelete(String fileName) throws EJBException {
    String s1 = "";
    PreparedStatement pstmtDetails = null;
    // PreparedStatement pstmtDetails1      = null;Commented by govind on 16-02-2010 for connectionLeakages
    PreparedStatement Details = null;
    // PreparedStatement Details1           = null;Commented by govind on 16-02-2010 for connectionLeakages
    Connection connection = null;
    OperationsImpl operationsImpl = null;
    String deleteDtl = "TRUNCATE TABLE QMS_BUYRATES_DELETE_DETAILS";
    //   String            deleteDtl1         = "DELETE FROM QMS_BUYRATES_DELETE_DATA";
    String queryDetails = "INSERT INTO QMS_BUYRATES_DELETE_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICE_LEVEL,FREQUENCY,TRANSIT_TIME,CURRENCY,TERMINAL_ID,DENSITY_CODE) VALUES (?,?,?,?,?,?,?,?,?,?)";
    //   String            queryDetails1     = "INSERT INTO QMS_BUYRATES_DELETE_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINER_VALUE) VALUES (?,?,?,?)";         

    String msgquery = "SELECT ERROR_MSG FROM QMS_BUYRATES_DELETE_DETAILS";
    //   ResultSet         rs               = null;Commeneted by Govind on 16-02-2010 for Connection Leakages
    String returnstr = null;//from   w w w.j av  a  2s . co m
    CallableStatement cstmt = null;
    try {
        String s = "";
        String mainHeaderData[] = new String[8];
        int rows = 0;
        int cols = 0;
        int notesIndex = 0;
        int batchCount = 0;
        HSSFRow row = null;
        HSSFCell cell = null;
        InputStream myxls = new FileInputStream(fileName);
        HSSFWorkbook wb = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheetAt(0);
        rows = sheet.getPhysicalNumberOfRows();
        //   connection=ConnectionUtil.getConnection();
        operationsImpl = new OperationsImpl();
        connection = operationsImpl.getConnection();
        Details = connection.prepareStatement(deleteDtl);

        Details.execute();

        //  Details1 = connection.prepareStatement(deleteDtl1);
        //  Details1.execute();
        row = sheet.getRow(0);

        cols = row.getPhysicalNumberOfCells();

        int l = cols - 1;

        for (; l >= 0; l--) {
            cell = row.getCell((short) l);

            if ("TERMINALID:".equalsIgnoreCase(getCellData(cell, 0)))
                break;

        }

        notesIndex = l + 1;
        //  notesIndex=cols;

        String detailsData[] = new String[notesIndex];
        /*String containersData[] =new String[notesIndex-7];
        for(l=7;l<notesIndex-2;l++)
        {
           cell=row.getCell((short)l);
           containersData[l-7]=getCellData(cell);
        }*/

        pstmtDetails = connection.prepareStatement(queryDetails);

        //  pstmtDetails1= connection.prepareStatement(queryDetails1);

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            double time = 0.0;
            String data = null;
            for (int j = 0; j < notesIndex; j++) {
                if (row != null) {
                    cell = row.getCell((short) j);

                    /* {
                     cell.setCellType(cell.CELL_TYPE_STRING);
                            
                     }*/
                    detailsData[j] = getCellData(cell, 0);
                    if (j == 4) // Added by Gowtham.
                    {
                        if (detailsData[j].toString().indexOf(".") != -1)
                            detailsData[j] = detailsData[j].substring(0,
                                    detailsData[j].toString().indexOf("."));
                    }
                    if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType())
                            && (detailsData[j].indexOf(".") != -1) && (Double.parseDouble(
                                    detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) {
                        time = 1 / Double.parseDouble(detailsData[j]);
                        Double t = new Double(time);
                        if (t.toString().indexOf(".") != -1) {
                            data = t.toString().substring(0, t.toString().indexOf("."));
                        }
                        if (data != null) {
                            detailsData[j] = data + ":" + "00";
                        }
                    }
                    if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType())
                            && (detailsData[j].indexOf(".") != -1) && (!(Double.parseDouble(
                                    detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) {
                        if (detailsData[j].indexOf(".") != -1) {
                            detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf("."));
                        }

                    }
                    if (j == 5 && detailsData[j].indexOf(".") != -1) {
                        detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf("."));
                    }
                    /*if(j==8&&detailsData[j].indexOf(".")!=-1)
                    {
                       detailsData[j] =detailsData[j].substring(0, detailsData[j].indexOf("."));
                      }*/
                }
            }
            batchCount++;

            insertDeleteDetails(detailsData, pstmtDetails, notesIndex, i);

            if (batchCount == 150) {
                pstmtDetails.executeBatch();
                pstmtDetails.clearBatch();

                batchCount = 0;

            }
        }

        if (batchCount > 0) {
            pstmtDetails.executeBatch();
            pstmtDetails.clearBatch();

            batchCount = 0;
        }

        cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_DELETE_PROC}");

        cstmt.registerOutParameter(1, Types.VARCHAR);
        cstmt.execute();
        returnstr = (String) cstmt.getString(1);

        //End
        return returnstr;
    } catch (Exception e) {
        logger.error("Exception");
        e.printStackTrace();
    } finally {
        try {
            /*    if(rs!=null)
               rs.close();-*/
            //Commented by govind  on 16-02-2010 fro connection leakages.

            if (pstmtDetails != null)
                pstmtDetails.close();
            if (Details != null)
                Details.close();
            if (cstmt != null)
                cstmt.close();
            if (connection != null)
                connection.close();
        } catch (Exception e) {
            logger.error("Error Occured  while closing Resources" + e);
        }
    }

    return s1;
}

From source file:com.raisepartner.chartfusion.generator.XLSParser.java

License:Open Source License

public String getStringValue(HSSFRow row, int col, String defaultValue) {
    return getStringValue(row.getCell((short) col), defaultValue);
}

From source file:com.report.excel.ExcelToHtmlConverter.java

License:Apache License

/**
 * @return maximum 1-base index of column that were rendered, zero if none
 *///from ww w.  ja  v  a 2  s. c  o m
protected int processRow(CellRangeAddress[][] mergedRanges, HSSFRow row, Element tableRowElement) {
    final HSSFSheet sheet = row.getSheet();
    final short maxColIx = row.getLastCellNum();
    if (maxColIx <= 0)
        return 0;

    final List<Element> emptyCells = new ArrayList<Element>(maxColIx);

    if (isOutputRowNumbers()) {
        Element tableRowNumberCellElement = htmlDocumentFacade.createTableHeaderCell();
        //processRowNumber( row, tableRowNumberCellElement );
        emptyCells.add(tableRowNumberCellElement);
    }

    int maxRenderedColumn = 0;
    for (int colIx = 0; colIx < maxColIx; colIx++) {
        if (!isOutputHiddenColumns() && sheet.isColumnHidden(colIx))
            continue;

        CellRangeAddress range = ExcelToHtmlUtils.getMergedRange(mergedRanges, row.getRowNum(), colIx);

        if (range != null && (range.getFirstColumn() != colIx || range.getFirstRow() != row.getRowNum()))
            continue;

        HSSFCell cell = row.getCell(colIx);

        int divWidthPx = 0;
        if (isUseDivsToSpan()) {
            divWidthPx = getColumnWidth(sheet, colIx);

            boolean hasBreaks = false;
            for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++) {
                if (!isOutputHiddenColumns() && sheet.isColumnHidden(nextColumnIndex))
                    continue;

                if (row.getCell(nextColumnIndex) != null && !isTextEmpty(row.getCell(nextColumnIndex))) {
                    hasBreaks = true;
                    break;
                }

                divWidthPx += getColumnWidth(sheet, nextColumnIndex);
            }

            if (!hasBreaks)
                divWidthPx = Integer.MAX_VALUE;
        }

        Element tableCellElement = htmlDocumentFacade.createTableCell();

        if (range != null) {
            if (range.getFirstColumn() != range.getLastColumn())
                tableCellElement.setAttribute("colspan",
                        String.valueOf(range.getLastColumn() - range.getFirstColumn() + 1));
            if (range.getFirstRow() != range.getLastRow())
                tableCellElement.setAttribute("rowspan",
                        String.valueOf(range.getLastRow() - range.getFirstRow() + 1));
        }

        boolean emptyCell;
        if (cell != null) {
            emptyCell = processCell(cell, tableCellElement, getColumnWidth(sheet, colIx), divWidthPx,
                    row.getHeight() / 20f);
        } else {
            emptyCell = true;
        }

        if (emptyCell) {
            emptyCells.add(tableCellElement);
        } else {
            for (Element emptyCellElement : emptyCells) {
                tableRowElement.appendChild(emptyCellElement);
            }
            emptyCells.clear();

            tableRowElement.appendChild(tableCellElement);
            maxRenderedColumn = colIx;
        }
    }

    return maxRenderedColumn + 1;
}

From source file:com.ro.ssc.app.client.utils.ExcelReader.java

/**
 *
 * @param file/*from  w  w w . j a v  a 2s.  c  o m*/
 * @return
 */
public static Map<String, User> readExcel(File file) {
    Map<String, User> result = new HashMap<>();
    List<Event> events;
    try {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols) {
                    cols = tmp;
                }
            }
        }

        DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss EEEE");
        for (int r = 1; r < rows; r++) {
            row = sheet.getRow(r);

            if (row != null) {
                try {
                    String user = WordUtils
                            .capitalizeFully(row.getCell(ExcelEnum.USER_NAME.getAsInteger()).toString().trim());
                    if (row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim().equals("1.0")) {
                        if (result.containsKey(user)) {
                            events = result.get(user).getEvents();

                            events.add(new Event(
                                    DateTime.parse(row.getCell(ExcelEnum.TIMESTAMP.getAsInteger()).toString(),
                                            dtf),
                                    row.getCell(ExcelEnum.DESCRIPTION.getAsInteger()).toString(),
                                    row.getCell(ExcelEnum.ADDRESS.getAsInteger()).toString().trim(),
                                    row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim()
                                            .equals("1.0")));
                            result.get(user).setEvents(events);

                        } else {
                            events = new ArrayList();
                            events.add(new Event(
                                    DateTime.parse(row.getCell(ExcelEnum.TIMESTAMP.getAsInteger()).toString(),
                                            dtf),
                                    row.getCell(ExcelEnum.DESCRIPTION.getAsInteger()).toString().trim(),
                                    row.getCell(ExcelEnum.ADDRESS.getAsInteger()).toString().trim(),
                                    row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim()
                                            .equals("1.0")));
                            String id = row.getCell(ExcelEnum.USER_ID.getAsInteger()).toString().trim();
                            result.put(user, new User(user, id.contains(".") ? id.split("\\.")[0] : id,
                                    row.getCell(ExcelEnum.CARD_NO.getAsInteger()).toString().trim(),
                                    WordUtils.capitalizeFully(
                                            row.getCell(ExcelEnum.DEPARTMENT.getAsInteger()).toString().trim()),
                                    events));
                        }
                    }
                } catch (Exception e) {
                    log.error("Exception" + e.getMessage());
                }
            }
        }
    } catch (Exception ioe) {
        log.error("Exception" + ioe.getMessage());
    }
    return result;
}

From source file:com.sadakhata.spamsmsblocker.SKSpamBlocker.java

License:Mozilla Public License

private void readExcelFile(String excelFileName, double x[][], double t[][], int numRows) {

    try {/*  www  .  jav a2  s.co  m*/
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFileName));
        HSSFSheet worksheet = workbook.getSheetAt(0);
        for (int rowIdx = 0; rowIdx < numRows; rowIdx++) {
            HSSFRow row = worksheet.getRow(rowIdx);

            String msgCellVal = row.getCell(4).getStringCellValue();
            int spamCellVal = (int) row.getCell(5).getNumericCellValue();

            hashFullString(x[rowIdx], msgCellVal);
            t[rowIdx][spamCellVal] = 1;

            //System.out.println(rowIdx);
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.sammyun.service.impl.ExcelServiceImpl.java

License:Open Source License

@Override
public List<Member> getMembersByExcel(InputStream is) throws Exception {
    // TODO Auto-generated method stub
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    List<Member> members = new ArrayList<Member>();
    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
        if (hssfSheet == null) {
            continue;
        }//from  ww w  .  j  a  va 2  s  .  c  om
        int infoSize = hssfSheet.getRow(0).getLastCellNum();// ?
        int rowSize = hssfSheet.getLastRowNum();// ???
        // Row
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow memberInfo = hssfSheet.getRow(rowNum);
            Member member = new Member();
            String username = null;
            String mobile = null;
            String realName = null;
            for (int i = 0; i < memberInfo.getLastCellNum(); i++) {
                HSSFCell memberCell = memberInfo.getCell(i);
                if (i == 0) {
                    // ??
                    username = memberCell.toString();
                }
                if (i == 1) {
                    // 
                    mobile = memberCell.toString();
                }
                if (i == 2) {
                    // ??
                    realName = memberCell.toString();
                }
            }
            // ?
            if (username != null) {
                member.setUsername(username);
            } else {
                String errorString = "" + rowNum + "????";
                return null;
            }
            if (mobile != null) {
                member.setMobile(mobile);
            } else {
                String errorString = "" + rowNum + "???";
                return null;
            }
            if (realName != null) {
                member.setRealName(realName);
            } else {
                String errorString = "" + rowNum + "????";
                return null;
            }
            members.add(member);

        }
    }
    return members;
}

From source file:com.sammyun.service.impl.ExcelServiceImpl.java

License:Open Source License

@Override
public List<Member> getMembers(InputStream inputStream, DictSchool dictSchool, MemberType memberType,
        HttpServletRequest request) throws Exception {
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
    List<Member> members = new ArrayList<Member>();
    // start/*ww  w . jav  a  2s .  c  o m*/
    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
        if (hssfSheet == null) {
            continue;
        }
        // start Row
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow memberInfo = hssfSheet.getRow(rowNum);
            if (memberInfo == null) {
                continue;
            }
            Member member = new Member();
            member.setMemberType(memberType);
            member.setCreateDate(new Date());
            member.setModifyDate(new Date());
            member.setIsEnabled(true);
            member.setIsLocked(false);
            member.setLoginFailureCount(0);
            Setting setting = SettingUtils.get();
            member.setPassword(DigestUtils.md5Hex(setting.getInitPassword()));
            member.setPoint(0L);
            member.setRegisterIp(EduUtil.getAddr(request));
            member.setSignature("");
            member.setValidateCodeNumber(0);
            member.setDictSchool(dictSchool);
            // member.setIsUpdate(true);
            member.setIsAcceptLeaveInfo(true);
            String mobile = null;
            String realName = null;
            // start??
            for (int i = 0; i < memberInfo.getLastCellNum(); i++) {
                HSSFCell memberCell = memberInfo.getCell(i);
                if (memberCell == null) {
                    continue;
                }
                // if (i == 0)
                // {
                // // ??
                // username = memberCell.toString();
                // username = username.trim();
                // member.setUsername(username);
                // }
                if (i == 0) {
                    // 
                    mobile = memberCell.toString();
                    member.setMobile(mobile);
                    member.setUsername(mobile);
                }
                if (i == 1) {
                    // ??
                    realName = memberCell.toString();
                    realName = realName.trim();
                    member.setRealName(realName);
                }

            }
            // end??
            members.add(member);
        }
        // endrow
    }
    // end

    return members;
}

From source file:com.sammyun.service.impl.ExcelServiceImpl.java

License:Open Source License

/**
 * ?week <??>//from   w  w  w . ja v a  2s  .c  om
 * 
 * @param in
 * @return
 * @throws Exception
 * @see [?#?#?]
 */
@Override
public ExcelMessage validateCourseWeekLessions(InputStream is) throws Exception {

    courseWorkbook = new HSSFWorkbook(is);
    ExcelMessage excelMessage = new ExcelMessage();
    excelMessage.setStatus(Status.success);
    // start
    for (int numSheet = 0; numSheet < courseWorkbook.getNumberOfSheets(); numSheet++) {
        HSSFSheet hssfSheet = courseWorkbook.getSheetAt(numSheet);
        if (hssfSheet == null) {
            continue;
        }
        // ?
        HSSFRow titleInfo = hssfSheet.getRow(0);
        ExcelMessage titleValidate = new ExcelMessage();
        titleValidate.setStatus(Status.fail);
        titleValidate.setError("???");
        if ((titleInfo.getCell(0) == null) || (!titleInfo.getCell(0).toString().equals(""))) {
            return titleValidate;
        }
        if ((titleInfo.getCell(1) == null) || (!titleInfo.getCell(1).toString().equals("?"))) {
            return titleValidate;
        }

        // start Row
        System.out.println(hssfSheet.getLastRowNum());
        if (hssfSheet.getLastRowNum() == 0) {
            excelMessage.setStatus(Status.fail);
            excelMessage.setError("?");
            is.close();
            return excelMessage;
        }
        // week
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow courseInfo = hssfSheet.getRow(rowNum);
            if (courseInfo == null) {
                continue;
            }
            if (courseInfo.getCell(0) == null) {
                excelMessage.setStatus(Status.fail);
                excelMessage.setError("" + rowNum + "?");
                is.close();
                return excelMessage;
            }

            String weekString = courseInfo.getCell(0).toString();
            try {
                int week = Integer.parseInt(weekString);
                if (week == 1) {
                    continue;
                } else if (week == 2) {
                    continue;
                } else if (week == 3) {
                    continue;
                } else if (week == 4) {
                    continue;
                } else if (week == 5) {
                    continue;
                } else if (week == 6) {
                    continue;
                } else if (week == 7) {
                    continue;
                } else {
                    excelMessage.setStatus(Status.fail);
                    excelMessage.setError(
                            "" + rowNum + "?1-7");
                    return excelMessage;
                }

            } catch (Exception e) {
                logger.error("" + rowNum + "?" + e.getMessage());
                excelMessage.setStatus(Status.fail);
                excelMessage
                        .setError("" + rowNum + "?1-7");
                is.close();
                return excelMessage;
            }
        }
        // lessons
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow courseInfo = hssfSheet.getRow(rowNum);
            if (courseInfo == null) {
                continue;
            }
            if (courseInfo.getCell(3) == null) {
                excelMessage.setStatus(Status.fail);
                excelMessage.setError("" + rowNum + "?");
                is.close();
                return excelMessage;
            }
            String lessonString = courseInfo.getCell(3).toString();
            try {
                int lesson = Integer.parseInt(lessonString);
                if (lesson == 1) {
                    continue;
                } else if (lesson == 2) {
                    continue;
                } else if (lesson == 3) {
                    continue;
                } else if (lesson == 4) {
                    continue;
                } else if (lesson == 5) {
                    continue;
                } else if (lesson == 6) {
                    continue;
                } else if (lesson == 7) {
                    continue;
                } else if (lesson == 8) {
                    continue;
                } else {
                    excelMessage.setStatus(Status.fail);
                    excelMessage.setError(
                            "" + rowNum + "?1-8");
                    is.close();
                    return excelMessage;
                }

            } catch (Exception e) {
                logger.error("" + rowNum + "?" + e.getMessage());
                excelMessage.setStatus(Status.fail);
                excelMessage
                        .setError("" + rowNum + "?1-8");
                is.close();
                return excelMessage;
            }
        }
        // start ???
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow courseInfo = hssfSheet.getRow(rowNum);
            if (courseInfo == null) {
                continue;
            }
            if (courseInfo.getCell(2) == null) {
                excelMessage.setStatus(Status.fail);
                excelMessage.setError("" + rowNum + "???");
                is.close();
                return excelMessage;
            }

        }
        // end ???

    }
    return excelMessage;
}