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

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

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

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java

License:Open Source License

public void addMarginsAndStylesForAssignmentToShift(final HSSFSheet sheet, final int rowNumber,
        final int numberOfDays) {
    int margin = 3;
    int firstColumn = 0;
    int lastColumn = (numberOfDays + 1) * margin;

    for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) {
        if (sheet.getRow(rowNumber).getCell(columnNumber) == null) {
            sheet.getRow(rowNumber).createCell(columnNumber);
        }/*w w w  . ja  va 2 s  .  c  o  m*/

        setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
    }

    for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) {
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2));
    }
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java

License:Open Source License

public void addMarginsAndStylesForSeries(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) {
    int margin = 3;
    int firstColumn = 0;
    int lastColumn = (numberOfDays + 1) * margin;

    for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) {
        if (sheet.getRow(rowNumber).getCell(columnNumber) == null) {
            sheet.getRow(rowNumber).createCell(columnNumber);
        }/*from  w  ww.  j av  a 2 s  .  c  o m*/

        if (columnNumber == firstColumn) {
            setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        } else {
            setWhiteDataStyleBorderBoxAlignLeft(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        }
    }

    for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) {
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2));
    }
}

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;//  ww w.j a v a 2 s  . 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;//w w w.  ja v a 2  s. 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

private void parseAttribute(HSSFSheet sheet, Map<String, MetaNode> nodeName2MetaNode) throws Exception {
    int idx = 1;//from  ww w . ja  va  2s  .  c o m
    while (true) {
        HSSFRow row = sheet.getRow(idx);
        if (row == null) {
            return;
        }
        String nodeOwner = getStringValue(row, 0, null);
        if (nodeOwner == null || nodeOwner.length() == 0) {
            return;
        }
        MetaNode node = getMetaNode(nodeOwner, nodeName2MetaNode);
        MetaAttribute att = new MetaAttribute();
        att.name = getStringValue(row, 1, null);
        if (att.name == null) {
            throw new Exception("Null attribute name on line " + (idx + 1));
        }
        att.name = att.name.trim();
        att.type = getStringValue(row, 2, null);
        att.rangeValue = getStringValue(row, 3, null);
        att.description = getStringValue(row, 4, null);
        node.attributes.add(att);
        idx++;
    }
}

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

License:Open Source License

private MetaNode parseNode(HSSFSheet sheet, Map<String, MetaNode> nodeName2MetaNode) {
    int idx = 1;/*from   w ww .  jav  a 2  s.  c  om*/
    MetaNode rootNode = null;
    while (true) {
        HSSFRow row = sheet.getRow(idx);
        if (row == null) {
            return rootNode;
        }
        String parentNodeName = getStringValue(row, 0, null);
        String childNodeName = getStringValue(row, 1, null);
        if (parentNodeName == null || parentNodeName.length() == 0) {
            return rootNode;
        }
        MetaNode parentNode = getMetaNode(parentNodeName, nodeName2MetaNode);
        if (rootNode == null) {
            rootNode = parentNode;
        }
        MetaNode childNode = getMetaNode(childNodeName, nodeName2MetaNode);
        parentNode.subnodes.add(childNode);
        idx++;
    }
}

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

License:Apache License

protected void processSheet(HSSFSheet sheet) {
    //processSheetHeader( htmlDocumentFacade.getBody(), sheet );
    sheet.setAutobreaks(true);//from   ww  w  .  j  av a 2 s  .  c o m

    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0)
        return;

    Element table = htmlDocumentFacade.createTable();
    htmlDocumentFacade.addStyleClass(table, cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;");

    Element tableBody = htmlDocumentFacade.createTableBody();

    final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet);

    final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows);
    int maxSheetColumns = 1;
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        HSSFRow row = sheet.getRow(r);

        if (row == null)
            continue;

        if (!isOutputHiddenRows() && row.getZeroHeight())
            continue;

        Element tableRowElement = htmlDocumentFacade.createTableRow();
        htmlDocumentFacade.addStyleClass(tableRowElement, cssClassPrefixRow,
                "height:" + (row.getHeight() / 20f) + "pt;");

        int maxRowColumnNumber = processRow(mergedRanges, row, tableRowElement);

        if (maxRowColumnNumber == 0) {
            emptyRowElements.add(tableRowElement);
        } else {
            if (!emptyRowElements.isEmpty()) {
                for (Element emptyRowElement : emptyRowElements) {
                    tableBody.appendChild(emptyRowElement);
                }
                emptyRowElements.clear();
            }

            tableBody.appendChild(tableRowElement);
        }
        maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber);
    }

    processColumnWidths(sheet, maxSheetColumns, table);

    if (isOutputColumnHeaders()) {
        //processColumnHeaders( sheet, maxSheetColumns, table );
    }

    table.appendChild(tableBody);

    htmlDocumentFacade.getBody().appendChild(table);
}

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

/**
 *
 * @param file//w  w w  . ja  v  a 2  s . c om
 * @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 {/*from  w ww . j  av  a  2s . 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;
        }/* w ww  .ja v  a 2s . c  o  m*/
        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;
}