java根据excel中的手机号查询归属地并添加到excel

首先是手机号查归属地的依赖

 <!--手机号归属地查询-->
        <dependency>
            <groupId>com.googlecode.libphonenumber</groupId>
            <artifactId>geocoder</artifactId>
            <version>LATEST</version>
        </dependency>

        <dependency>
            <groupId>com.googlecode.libphonenumber</groupId>
            <artifactId>libphonenumber</artifactId>
            <version>LATEST</version>
        </dependency>

        <dependency>
            <groupId>com.googlecode.libphonenumber</groupId>
            <artifactId>prefixmapper</artifactId>
            <version>LATEST</version>
        </dependency>

        <dependency>
            <groupId>com.googlecode.libphonenumber</groupId>
            <artifactId>carrier</artifactId>
            <version>LATEST</version>
        </dependency>

手机号查询工具类

package com.lkp.demo.Util;

import com.google.i18n.phonenumbers.PhoneNumberToCarrierMapper;
import com.google.i18n.phonenumbers.PhoneNumberUtil;
import com.google.i18n.phonenumbers.Phonenumber;
import com.google.i18n.phonenumbers.geocoding.PhoneNumberOfflineGeocoder;

import java.util.Locale;

/**
 * @program: demo
 * @description: 工具类
 * @author: Mr.JZQ
 * @create: 2022-01-18 15:30
 **/

public class PhoneUtil {
    private static PhoneNumberUtil phoneNumberUtil = PhoneNumberUtil.getInstance();
    private static PhoneNumberToCarrierMapper carrierMapper = PhoneNumberToCarrierMapper.getInstance();
    private static PhoneNumberOfflineGeocoder geocoder = PhoneNumberOfflineGeocoder.getInstance();

    /**
     * 根据国家代码和手机号  判断手机号是否有效
     * @param phoneNumber
     * @param countryCode
     * @return
     */
    public static boolean checkPhoneNumber(String phoneNumber, String countryCode){

        int ccode = Integer.valueOf(countryCode);
        long phone = Long.valueOf(phoneNumber);

        Phonenumber.PhoneNumber pn = new Phonenumber.PhoneNumber();
        pn.setCountryCode(ccode);
        pn.setNationalNumber(phone);

        return phoneNumberUtil.isValidNumber(pn);

    }

    /**
     * 根据国家代码和手机号  判断手机运营商
     * @param phoneNumber
     * @param countryCode
     * @return
     */
    public static String getCarrier(String phoneNumber, String countryCode){

        int ccode = Integer.valueOf(countryCode);
        long phone = Long.valueOf(phoneNumber);

        Phonenumber.PhoneNumber pn = new Phonenumber.PhoneNumber();
        pn.setCountryCode(ccode);
        pn.setNationalNumber(phone);
        //返回结果只有英文，自己转成成中文
        String carrierEn = carrierMapper.getNameForNumber(pn, Locale.ENGLISH);
        String carrierZh = "";
        carrierZh += geocoder.getDescriptionForNumber(pn, Locale.CHINESE);
        switch (carrierEn) {
            case "China Mobile":
                carrierZh += "移动";
                break;
            case "China Unicom":
                carrierZh += "联通";
                break;
            case "China Telecom":
                carrierZh += "电信";
                break;
            default:
                break;
        }
        return carrierZh;
    }


    /**
     *
     * @Description: 根据国家代码和手机号  手机归属地
     * @date 2015-7-13 上午11:33:18
     * @param @param phoneNumber
     * @param @param countryCode
     * @param @return    参数
     * @throws
     */
    public static String getAdd(String phoneNumber, String countryCode) {

        int ccode = Integer.valueOf(countryCode);
        long phone = Long.valueOf(phoneNumber);

        Phonenumber.PhoneNumber pn = new Phonenumber.PhoneNumber();
        pn.setCountryCode(ccode);
        pn.setNationalNumber(phone);
        String descriptionForNumber = geocoder.getDescriptionForNumber(pn, Locale.CHINA);
        return descriptionForNumber;
    }

}

调用方法

String carrier = PhoneUtil.getCarrier("15965451235", "+86");//获取手机号归宿的

操作execl依赖

        <!-- xls -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
            <!-- xlsx -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>

最后是从指定的execl中读取手机号 然后添加手机号归属地到指定位置

        //1、创建输入流,指定要读取的文件
        InputStream inputStream = new FileInputStream("D:\\1.xlsx");
        //2、创建workbook将输入流传过去
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(inputStream);
        //3、指定读取的sheet
        XSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 2; i < rowNum; i++) {
            //修改execl
            //4、获取指定行
            XSSFRow row = sheet.getRow(i);
            //5、获取指定cell
            XSSFCell cell = row.getCell(1);
            if (null == cell) {
                continue;
            }
            //6、从cell中也就是单元格中获取数据
            String cellValue = cell.getRawValue();
            String carrier = PhoneUtil.getCarrier(cellValue, "+86");
            if (carrier.length() < 2) {
                continue;
            }
            //先去判断如果包含省就截取到省如果不包含省就截取到市
            String cityName=null;
            if (carrier.contains("省")||carrier.contains("市")){
                if (carrier.contains("省")) {
                    cityName = carrier.substring(0, carrier.indexOf("省"));
                } else {
                    cityName = carrier.substring(0, carrier.indexOf("市"));
                }
                System.out.println(cityName);
            }

            //输出结果

            row.createCell(5).setCellValue(cityName);
        }
        FileOutputStream out = new FileOutputStream("D:\\1.xlsx");
        hssfWorkbook.write(out);
        out.flush();
        out.close();
        //关闭输入流
        inputStream.close();

整理了一上午 希望能够帮到你