Example usage for org.apache.poi.ss.usermodel Cell 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.ifeng.vdn.ip.repository.service.impl.AliBatchIPAddressCheckerTest.java

License:Apache License

@Test
public void wrap() {
    Workbook wb = null;//from   ww  w.j  av a 2s . c  om
    try {
        BatchIPAddressChecker<AliIPBean> checker = new AliBatchIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_18.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        Cell locCell = null;

        List<String> ips = new ArrayList<String>();

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(1);

            ips.add(ipCell.getStringCellValue());
        }

        List<AliIPBean> locations = checker.check(ips);
        String location = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            locCell = row.getCell(3);

            try {
                location = locations.get(i).getIpString();
            } catch (Exception e) {
                location = "ERROR";
            }

            locCell.setCellValue(location);
        }

        /*if(location.length() == (totalRows - 1)){
                   
        }else {
           log.error("Batch executed error");
           throw new RuntimeException("Batch executed error. Some one IP location not be checked.");
        }*/

        wb.write(new FileOutputStream("src/test/resources/data/ip_18_Alibaba.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliDataFactoryTest.java

License:Apache License

@Test
public void importDataFromExcel() {
    Workbook wb = null;//from  w ww .j a  va  2 s.  c o  m
    String input = "src/test/resources/data/ip_18_Alibaba.xlsx";

    try {

        List<IPModel> ipList = new ArrayList<IPModel>();

        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell totalCell = null;
        Cell locCell = null;

        String location = "";
        int total;
        String ipString = "";

        String[] items = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            totalCell = row.getCell(2);
            locCell = row.getCell(3);

            try {
                String country = "";
                String area = "";
                String region = "";
                String city = "";
                String isp = "";
                IPModel model = new IPModel();

                ipString = ipCell.getStringCellValue();
                total = (int) totalCell.getNumericCellValue();
                location = locCell.getStringCellValue();

                items = location.split(" ");
                if (items != null) {
                    if (items[0] != null) {
                        country = items[0];
                    }
                    if (items[1] != null) {
                        area = items[1];
                    }
                    if (items[2] != null) {
                        region = items[2];
                    }
                    if (items[3] != null) {
                        city = items[3];
                    }
                    if (items[4] != null) {
                        isp = items[4];
                    }

                    model.setIp(ipString);
                    model.setCountry(country.trim());
                    model.setArea(area.trim());
                    model.setRegion(region.trim());
                    model.setCity(city.trim());
                    model.setIsp(isp.trim());
                    model.setTotal(total);

                    ipList.add(model);
                }

                log.info("IP: {}, Total{}, location: {}", ipString, total, location);

                if ((i % 1000) == 0) {
                    aliDataFactory.importData(ipList);
                    ipList = new ArrayList<IPModel>();
                }

            } catch (Exception e) {
                e.getMessage();
                //log.error(e.getMessage(), e);

            }

        }

        if (ipList.size() > 0) {
            aliDataFactory.importData(ipList);
        }

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

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliIPAddressCheckerTest.java

License:Apache License

public void wrap(String input, String output) {
    Workbook wb = null;//from  w ww .  j av  a 2  s .  com
    PrintWriter pw = null;
    try {
        pw = new PrintWriter(output, "UTF-8");

        IPAddressChecker checker = new AliIPAddressChecker();
        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        String location = "";
        String ipString = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            try {
                ipString = ipCell.getStringCellValue();
                AliIPBean ip = (AliIPBean) checker.ipcheck(ipString);
                location = ip.getIpString();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                location = "ERROR";
            } finally {
                //locCell.setCellValue(location);

                pw.append(ipString + " " + location);
                pw.println();
            }
        }

        wb.write(new FileOutputStream(output));

    } catch (InvalidFormatException | IOException e) {
        log.error(e.getMessage(), e);
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        pw.flush();
        pw.close();
    }
}

From source file:com.ifeng.vdn.ip.repository.service.impl.IPAddressBaiduCheckerTest.java

License:Apache License

@Test
public void wrap() {

    Workbook wb = null;/*from  w ww  . j av a  2  s  .com*/

    try {
        IPAddressBaiduChecker checker = new IPAddressBaiduChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_16.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            BaiduIPBean ip = (BaiduIPBean) checker.ipcheck(ipCell.getStringCellValue());
            List<IPLocation> locations = ip.getData();

            if (locations != null && locations.size() > 0) {

                try {
                    locCell.setCellValue(locations.get(0).getLocation());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        wb.write(new FileOutputStream("src/test/resources/data/ip_16_loc.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}

From source file:com.impetus.kvapps.runner.UserBroker.java

License:Apache License

private String extractCellData(Row row, int iCurrent) throws Exception {
    Cell cell = (Cell) row.getCell(iCurrent);
    if (cell == null) {
        return "";
    } else {/*from   w  w  w.ja va  2 s  .c o m*/
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            double value = cell.getNumericCellValue();
            if (HSSFDateUtil.isCellDateFormatted(cell))

            {
                if (HSSFDateUtil.isValidExcelDate(value)) {
                    Date date = HSSFDateUtil.getJavaDate(value);
                    SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
                    return dateFormat.format(date);
                } else {
                    throw new Exception("Invalid Date value found at row number " + row.getRowNum()
                            + " and column number " + cell.getColumnIndex());
                }
            } else {
                return value + "";
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case HSSFCell.CELL_TYPE_BLANK:
            return null;
        default:
            return null;
        }
    }
}

From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java

public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
    ArrayList<String[]> data = new ArrayList<String[]>();
    File selectedFile = null;/*from w  ww.j a  v a2  s  .  co m*/
    FileInputStream fis = null;
    ;
    XSSFWorkbook workbook = null;
    //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
    boolean sheetFound = false;
    XSSFSheet sheet = null;
    try {

        int sheetIndex = -1;
        fis = new FileInputStream(inputFile);
        workbook = new XSSFWorkbook(fis);

        int noOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < noOfSheets; i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
        XSSFWorkbook myWorkBook;

        try {
            myWorkBook = new XSSFWorkbook(selectedFile);
            // Return first sheet from the XLSX workbook
            // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();
            String query;
            String[] values = null;
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (rowHasData(row) && (row.getRowNum() >= 9)) {
                    int endColumn = row.getLastCellNum();
                    int startColumn = row.getFirstCellNum();
                    // For each row, iterate through each columns
                    values = new String[endColumn + 2];
                    for (int i = startColumn; i < endColumn; i++) {
                        String cellValue;
                        Cell cell = row.getCell(i);
                        values[0] = Integer.toString(row.getRowNum() + 1);
                        if (cell != null) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                    && DateUtil.isCellDateFormatted(cell)) {
                                DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                Date cellDateValue = cell.getDateCellValue();
                                cellValue = df.format(cellDateValue);
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue().replaceAll("'", "");
                            }
                            if (!"".equals(cellValue) && cellValue != null) {
                                values[i + 1] = cellValue;
                            } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                values[i + 1] = "";
                            }
                        } else {
                            values[i + 1] = "";
                        }
                    }
                    data.add(values);
                }

            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    } catch (IOException ex) {
        Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
    } finally {
        try {
            fis.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    }

    return data;
}

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

private String getCellAsString(Cell cell) {
    if (cell == null)
        return "";

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        int intValue = (int) Math.ceil(cell.getNumericCellValue());
        return String.valueOf(intValue);
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default:// w w w. j a  v a 2  s.c o  m
        return "";
    }
}

From source file:com.jaredrummler.android.devices.Main.java

License:Apache License

private static String getStringCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "true" : "false";
    case Cell.CELL_TYPE_NUMERIC:
        return Double.toString(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
    default:/*from   www .  j  a v a 2s. c  om*/
        return cell.getStringCellValue();
    }
}

From source file:com.javabean.excel.ExcelDocumentReader.java

public List<String> GetColumnName(int sheetIndex) {
    List<String> columnNameList;
    Sheet sheet;/*from w w  w  .ja v a  2  s.c  om*/
    Cell cell;
    int columnCount;
    String stringTemp;
    sheet = GetSheet(sheetIndex);
    if (sheet == null) {
        return null;
    }
    Row row = sheet.getRow(0);
    columnCount = row.getLastCellNum();
    columnNameList = new ArrayList();
    for (int i = 0; i < columnCount; i++) {
        cell = row.getCell(i);
        stringTemp = Convert.NullToString(cell.getStringCellValue(), ReturnStringValue.NULL);
        columnNameList.add(stringTemp);
    }
    return columnNameList;
}

From source file:com.javabean.excel.ExcelDocumentReader.java

private List<String> GetOneDataRow(Sheet sheet, int rowIndex, int columnCount) {
    List<String> dataRow;
    Cell cell;
    Row row;//from www. j  a v  a 2 s. com
    String stringTemp;
    dataRow = new ArrayList<>();
    try {
        row = sheet.getRow(rowIndex);
        for (int i = 0; i < columnCount; i++) {
            cell = row.getCell(i);
            cell.setCellType(CELL_TYPE_STRING);
            if (cell == null) {
                stringTemp = "NULL";
            } else {
                stringTemp = cell.getStringCellValue();
            }
            dataRow.add(stringTemp);
        }
    } catch (Exception ex) {
        dataRow = null;
    }
    return dataRow;
}