Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getStringCellValue

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getStringCellValue.

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:com.compassplus.gui.MainForm.java

private void openFile(File file) { //xlsFileChooser.getSelectedFile()
    try {//  ww w .j  a va2  s. c o m
        Proposal proposal = new Proposal(config);
        FileInputStream inp = new FileInputStream(file);
        final Workbook wb = WorkbookFactory.create(inp);
        inp.close();
        Sheet s = wb.getSheet("PCTSettings");
        String proposalString = null;
        if (s != null) {
            Row r = s.getRow(0);
            if (r != null) {
                Cell c = r.getCell(0);
                if (c != null) {
                    proposalString = c.getStringCellValue();
                }
            }
        }
        if (proposalString == null) {
            throw new PCTDataFormatException("Proposal not found");
        }
        proposal.init(CommonUtils.getInstance().getDocumentFromString(proposalString));
        //ProposalForm tmpForm = new ProposalForm(proposal, getFrame());
        addProposalForm(proposal, getFrame(), true);
        //tmpForm.setChanged(false);
        if (proposal.containsDeprecated()) {
            JOptionPane.showMessageDialog(getRoot(),
                    "Selected proposal contains deprecated module(s) or capacity(ies).", "Warning",
                    JOptionPane.INFORMATION_MESSAGE);
        }
    } catch (Exception exception) {
        //exception.printStackTrace();
        if (exception instanceof PCTDataFormatException) {
            Logger.getInstance().error(exception);
        }
        JOptionPane.showMessageDialog(getRoot(), "Can't read proposal from specified file", "Error",
                JOptionPane.ERROR_MESSAGE);
    }
}

From source file:com.compassplus.gui.MainForm.java

private boolean analyzeCell(Workbook wb, Sheet sheet, Row row, Cell cell, ScriptEngine engine,
        Bindings bindings) {/* w ww  .  ja  v  a2 s  .  c  om*/
    try {
        String formula = cell.getCellFormula();
        cell.setCellFormula(formula);
    } catch (Exception e) {
    }
    try {
        String expr = cell.getStringCellValue();
        short type = 0;
        if (expr.contains("__REMOVE")) {
            type = __REMOVE;
        } else if (expr.contains("__INSERT")) {
            type = __INSERT;
        }
        if (type > 0) {
            try {
                expr = expr.substring(expr.indexOf("(") + 1);
                expr = expr.substring(0, expr.lastIndexOf(")"));
                expr = expr.replaceAll("\\s", "");

                if (type == __REMOVE) {
                    cell.setCellValue("");
                    Object val = null;
                    val = engine.eval(expr, bindings);
                    if (val instanceof Boolean) {
                        return (Boolean) val;
                    } else {
                        throw new Exception("result is not boolean");
                    }
                } else if (type == __INSERT) {
                    cell.setCellValue("");
                    Object val = null;
                    val = engine.eval(expr, bindings);

                    if (!(val instanceof String && ((String) val).equals(""))) {
                        CellStyle cs = wb.createCellStyle();
                        CellStyle csT = cell.getCellStyle();
                        cs.cloneStyleFrom(csT);
                        String format = (getCurrentProposalForm().getProposal().getCurrency()
                                .getSymbol() != null ? "\""
                                        + getCurrentProposalForm().getProposal().getCurrency().getSymbol()
                                        + "\" " : "")
                                + "#,##0"
                                + (getCurrentProposalForm().getProposal().getCurrency().getSymbol() == null
                                        ? " \"" + getCurrentProposalForm().getProposal().getCurrency().getName()
                                                + "\""
                                        : "");
                        if (expr.contains("$PRICE") || expr.contains("$MAN-DAY-RATE")) {
                            cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat(format));
                        } else if (expr.contains("$SUPPORT_RATE")) {
                            cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("0%;-0%"));
                        } else if (expr.contains("$VALUE")) {
                            cs.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("#,##0"));
                        }
                        cell.setCellStyle(cs);
                    }
                    if (val instanceof Boolean) {
                        cell.setCellValue((Boolean) val);
                    } else if (val instanceof Number) {
                        cell.setCellValue(((Number) val).doubleValue());
                    } else if (val instanceof String) {
                        cell.setCellValue((String) val);
                    } else {
                        throw new Exception("result type is unknown");
                    }
                }

            } catch (Exception e) {
                log.error("Bad" + (type == __REMOVE ? " __REMOVE" : (type == __INSERT ? " __INSERT" : ""))
                        + " expression: " + expr);
                cell.setCellValue("");
            }
        }
    } catch (Exception e) {
    }
    return false;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetSimType(FileNames fileName, SheetName sheetName) throws IOException {
    String filePath = FilePath(fileName);
    String simType = null;/*from ww w. ja  v a2  s .  c  o  m*/
    try {
        String sheet = sheetName.toString();
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            sheet = Name[1];
        }
        SetStatusForSimTypSheets(sheet, fileName);
        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        XSSFSheet workSheet = workBook.getSheet(sheet);
        for (int i = 0; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Row currentRow = workSheet.getRow(i);
            int type = 1;
            Cell cell = currentRow.getCell(1);
            try {
                type = cell.getCellType();
                System.out.println(type);
                System.out.println(workSheet.getRow(i).getCell(1).toString());
                System.out.println(cell.getStringCellValue().toLowerCase());
                if (type == 1 && cell.getStringCellValue().toLowerCase()
                        .equals(Status.UNUSED.toString().toLowerCase())) {
                    cell.setCellValue(Status.INUSE.toString());
                    simType = currentRow.getCell(0).getStringCellValue();
                    break;
                }
            }

            catch (Exception e) {
                if (type != 1) {
                    cell = currentRow.createCell(1);
                    cell.setCellValue(Status.UNUSED.toString());
                }
            }
        }
    } catch (Exception ex) {
        Log.error(ex.toString());
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
    return simType;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public void SetCellValueToUsed(FileNames fileName, SheetName sheetName, String searchString)
        throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    String sheet = sheetName.toString();
    int rowIndex = -1;

    try {/*  w ww  .ja  va  2  s  . c o  m*/
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            if (Name[0].contains("IMEI")) {
                sheet = Name[1] + " " + Name[0];
            } else
                sheet = Name[1];
        }
        worksheet = workBook.getSheet(sheet);
        System.out.println(worksheet.getSheetName());
        Iterator<Row> rowIterator = worksheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && String
                        .valueOf(Double.valueOf(cell.getNumericCellValue()).longValue()).equals(searchString)) {
                    rowIndex = row.getRowNum();
                    break;
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equals(searchString))
                        rowIndex = row.getRowNum();
                    break;
                }
            }
        }
        if (rowIndex != -1) {
            boolean flag = false;
            Row searchStringRow = worksheet.getRow(rowIndex);
            System.out.println(searchStringRow.getPhysicalNumberOfCells());
            Iterator<Cell> statusChangeCell = searchStringRow.cellIterator();
            while (statusChangeCell.hasNext()) {
                Cell statusCell = statusChangeCell.next();
                if (statusCell.getStringCellValue().toLowerCase()
                        .equals(Status.INUSE.toString().toLowerCase())) {
                    statusCell.setCellValue(Status.USED.toString());
                    flag = true;
                }
            }
            if (flag == false)
                Log.error("FAILED TO FIND INUSE FIELD for " + searchString + " @FileName: " + filePath
                        + ", Sheet: " + sheet);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetIMEINumber(FileNames fileName, SheetName workSheet) throws IOException {
    String filePath = FilePath(fileName);
    String sheet = null;// w w  w.j  a  va2 s  .  c o  m
    String tempSheet = null;
    String imei = "";
    int statusColumn = -1;
    try {
        int numOfWorkSheets = 0;

        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        numOfWorkSheets = workBook.getNumberOfSheets();
        for (int count = 0; count < numOfWorkSheets; count++) {
            if (workSheet.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) {
                sheet = workBook.getSheetName(count);
                break;
            }
        }
        worksheet = workBook.getSheet(sheet);
        if (sheet.equals("iPhone 5C")) {
            tempSheet = sheet;
            sheet = "iPhone 4S";
        }

        switch (sheet) {
        case "4G IMEI":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(2);
                if (row.getCell(5).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf(cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(5).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
            return imei;

        case "3G":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                if (row.getCell(4).toString().equals(Status.UNUSED.toString())) {
                    imei = String.valueOf(row.getCell(2).getNumericCellValue());
                    worksheet.getRow(i).getCell(4).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "Jetpack":
            statusColumn = CreateStatusColumn(filePath, sheet);
            if (statusColumn == -1) {
                statusColumn = 3;
            }

            for (int i = 2; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(1);
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "iPhone 4S":
            if (tempSheet.contains("5C"))
                statusColumn = CreateStatusColumn(filePath, tempSheet);
            if (statusColumn == -1) {
                Cell cell = worksheet.getRow(0).createCell(4);
                cell.setCellValue(Status.STATUS.toString());
                statusColumn = 4;
            }
            for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(0);
                if (row.getCell(statusColumn).getCellType() == Cell.CELL_TYPE_BLANK) {
                    row.createCell(statusColumn).setCellValue(Status.UNUSED.toString());
                }
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    if (imei != "" || imei != null)
                        row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    return imei;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public AccountDetails GetAccountDetails(FileNames fileName, SheetName worksheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, worksheetName.toString());
    AccountDetails accountDetails = new AccountDetails();

    try {/*w  ww . j a  v a  2  s  .co  m*/
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("MTN")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Password")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("SSN")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                accountDetails.MTN = String
                        .valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                accountDetails.Password = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                accountDetails.SSN = String
                        .valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (accountDetails == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return accountDetails;
}

From source file:com.consensus.qa.framework.ExcelOperations.java

@SuppressWarnings("unused")
public NPANXX GetNumberPortData(FileNames fileName, SheetName workSheetName) throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    XSSFSheet workSheet = GetSheetFromWorkBook(workBook, workSheetName.toString());
    NPANXX npaNXX = new NPANXX();

    try {/*w ww  .  j  a  va  2s.  c  o  m*/
        int mtnIndex = -1;
        int passwordIndex = -1;
        int ssnIndex = -1;
        int statusCol = -1;
        Row row = workSheet.getRow(0);
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (String.valueOf(cell.getStringCellValue()).contains("NGP")) {
                mtnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("Location")) {
                passwordIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains("NPANXX")) {
                ssnIndex = cell.getColumnIndex();
            } else if (String.valueOf(cell.getStringCellValue()).contains(Status.STATUS.toString())) {
                statusCol = cell.getColumnIndex();
            }
            if (mtnIndex != -1 && passwordIndex != -1 && ssnIndex != -1 && statusCol != -1) {
                break;
            }
        }
        if (statusCol == -1) {
            statusCol = (workSheet.getRow(0).getPhysicalNumberOfCells());
            Cell cell = workSheet.getRow(0).createCell(statusCol);
            cell.setCellValue(Status.STATUS.toString());
        }
        for (int i = 1; i < workSheet.getPhysicalNumberOfRows(); i++) {
            Cell cell = null;
            if (workSheet.getRow(i).getCell(statusCol) == null
                    || (workSheet.getRow(i).getCell(statusCol).getCellType() == Cell.CELL_TYPE_BLANK)) {
                cell = workSheet.getRow(i).createCell(statusCol);
                cell.setCellValue(Status.UNUSED.toString());
            }
            cell = workSheet.getRow(i).getCell(statusCol);
            if (cell.getStringCellValue().toString().equals(Status.UNUSED.toString())) {
                npaNXX.NGP = String.valueOf(workSheet.getRow(i).getCell(mtnIndex).getNumericCellValue());
                npaNXX.Location = workSheet.getRow(i).getCell(passwordIndex).getStringCellValue();
                npaNXX.NPANXX = String.valueOf(workSheet.getRow(i).getCell(ssnIndex).getNumericCellValue());

                cell.setCellValue(Status.INUSE.toString());
                break;
            }
        }
    }

    catch (Exception ex) {
        ex.printStackTrace();
    }

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    if (npaNXX == null)
        Log.error("FAILED To get account details; one among MTN/Password/SSN is blank");
    return npaNXX;
}

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Read records from Excel file//from   w  w  w  .j a  v a  2s . c o m
 *
 * @param vcConfig The validator configuration object.
 * @param bUseTupleOld
 * @param filename Name of the Excel file.
 * @param doc Document conatins the request.
 * @param iResponsenode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param startcolumn column index from which data to be read.
 * @param endcolumn column index upto which data to be read.
 */
public static void readall(ValidatorConfig vcConfig, Boolean bUseTupleOld, String filename, Document doc,
        int iResponsenode, int sheetno, int startrow, int endrow, int startcolumn, int endcolumn)
        throws FileException {

    Workbook book = null;
    Sheet sheet;
    Cell cell;
    Row row;
    FileInputStream fileinp = null;
    String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
    try {
        int iRow, iCol, sheetindex, noofsheets;
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                book = (Workbook) new HSSFWorkbook(fileinp);
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                book = new XSSFWorkbook(fileinp);
            } else {
                //ERROR
                fileinp.close();
            }
        } else {
            //ERROR
            fileinp.close();
        }

        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        for (; sheetindex < noofsheets; sheetindex++) {
            sheet = book.getSheetAt(sheetindex);

            if (endrow == -1) {
                endrow = sheet.getLastRowNum();
                if (startrow == -1) {
                    startrow = 0;
                }
            } else {
                endrow = startrow + endrow - 1;
                if (endrow > sheet.getLastRowNum()) {
                    endrow = sheet.getLastRowNum();
                }
            }

            if (endcolumn == -1) {
                endcolumn = 30;
                if (startcolumn == -1) {
                    startcolumn = 0;
                }
            }
            for (int i = startrow; i <= endrow; i++) {

                row = sheet.getRow(i);

                if (row == null) {
                    int iTup = doc.createElement("tuple", iResponsenode);

                    if (bUseTupleOld) {
                        iTup = doc.createElement("old", iTup);
                    }
                    iRow = doc.createElement(sRecordName, iTup);
                    //Node.setAttribute(iRow, "id", "" + i);
                    ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                            .listIterator();
                    while (fieldslist.hasNext()) {
                        FieldType excelfields = (FieldType) fieldslist.next();
                        String sColumnName = excelfields.sFieldName;

                        iCol = doc.createTextElement(sColumnName, "", iRow);
                    }
                    continue;
                }
                int iTup = doc.createElement("tuple", iResponsenode);
                if (bUseTupleOld) {
                    iTup = doc.createElement("old", iTup);
                }
                iRow = doc.createElement(sRecordName, iTup);
                ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                        .listIterator();
                while (fieldslist.hasNext()) {
                    FieldType excelfields = (FieldType) fieldslist.next();
                    int iColumnIndex = Integer.parseInt(excelfields.sColumnIndex);
                    cell = row.getCell(iColumnIndex);
                    String sColumnName = excelfields.sFieldName;
                    if (cell == null) {
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        continue;
                    }
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getBooleanCellValue(), iRow);

                        break;
                    case Cell.CELL_TYPE_ERROR:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getCellFormula(), iRow);

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat simpledateformat = new SimpleDateFormat(
                                    "yyyy-MM-dd 'T' HH:mm:ss.S");
                            iCol = doc.createTextElement(sColumnName,
                                    "" + simpledateformat.format(cell.getDateCellValue()), iRow);

                        } else {
                            iCol = doc.createTextElement(sColumnName, "" + cell.getNumericCellValue(), iRow);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getStringCellValue(), iRow);
                        break;
                    default:
                        System.out.println("default");
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        throw new FileException(e, LogMessages.FILE_NOT_FOUND);
    } catch (IOException e) {
        throw new FileException(e, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename);
    } finally {
        try {
            fileinp.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.creditcloud.carinsurance.CarInsuranceFeeServiceBean.java

/**
 * ??/*from  w  w w.  java  2s  .  co m*/
 *
 * @param feeFileExcel
 */
public void bacthUpdateCarInsuranceFeeSatatus(File feeFileExcel) {
    try {
        FileInputStream file = new FileInputStream(feeFileExcel);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        String insuranceNum = "";
        int currentPeriod = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    currentPeriod = (int) cell.getNumericCellValue();
                    System.out.print((int) cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    insuranceNum = cell.getStringCellValue().trim();
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }
            }
            CarInsuranceFee fee = carInsuranceFeeDAO.findByInSuranceNumAndCurrentPeriod(insuranceNum,
                    currentPeriod);
            //?
            if (fee != null) {
                updateCarInsuranceFeeSatatus(fee.getId(), CarInsuranceStatus.CLEARED);
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.cseur.utils.ExcelReader.java

public ArrayList<ArrayList<String>> readToMap() throws IOException {

    FileInputStream file = new FileInputStream(xlsFile);
    //Get the workbook instance for XLS file 
    HSSFWorkbook workbook = new HSSFWorkbook(file);

    //Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);

    //get pol pod via1 via2 via3 via4 index in column and saved to array
    ArrayList<Integer> portIndexs = new ArrayList(Arrays.asList(null, null, null, null, null, null, null));
    boolean isTableHeader = true;
    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        //read file, find the columnindex for required order, and then insert the value to valueArray.
        Row row = rowIterator.next();// w  w w  .java 2 s.com
        //            //escape empty lines
        Iterator<Cell> cellIterator = row.cellIterator();
        if (isTableHeader) {
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //set absolute columnIndex in the order of POL, POD, VIA1.2.l.3.4
                switch (cell.getStringCellValue().trim().toUpperCase()) {
                case "POL":
                    portIndexs.set(0, cell.getColumnIndex());
                    break;
                case "POD":
                    portIndexs.set(1, cell.getColumnIndex());
                    break;
                case "VIA1":
                    portIndexs.set(2, cell.getColumnIndex());
                    break;
                case "VIA2":
                    portIndexs.set(3, cell.getColumnIndex());
                    break;
                case "VIA3":
                    portIndexs.set(4, cell.getColumnIndex());
                    break;
                case "VIA4":
                    portIndexs.set(5, cell.getColumnIndex());
                    break;
                //                    case "VIA5":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA6":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA7":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA8":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA9":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                //                    case "VIA0":
                //                            portIndexs.add(cell.getColumnIndex());
                //                        break;
                }
                //                    portIndexs.trimToSize();
            }
        }
        if (!isTableHeader) {
            ArrayList<String> valueArrayList = new ArrayList<>();
            for (int i = 0; portIndexs.get(i) != null; i++) {
                if (row.getCell(portIndexs.get(i)) != null
                        && !row.getCell(portIndexs.get(i)).getStringCellValue().isEmpty()) {
                    valueArrayList
                            .add(row.getCell(portIndexs.get(i)).getStringCellValue().trim().toUpperCase());
                }
            }
            System.out.println(valueArrayList.size());
            valueArrayList_X.add(valueArrayList);
            //reference added to arraylist. clear the valueArrayList remove the values in valueArrayList_X Too!!
            //valueArrayList.clear();
        }
        isTableHeader = false;//add contents to vector.
    }
    file.close();
    return valueArrayList_X;
}