根基excel表查询表中第二列名称，去百度回去经纬度重新输出excel

向右看齐 2021-09-28 15:16 186阅读 0赞

package com.sms.tenantexamineserver.sys.controller;
    
    import com.alibaba.druid.support.json.JSONUtils;
    import com.baomidou.mybatisplus.core.toolkit.IdWorker;
    import com.sms.tenantexamineserver.sys.entity.Aaaa;
    import com.sms.tenantexamineserver.sys.entity.NewBaseInfo;
    import io.swagger.annotations.Api;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.*;
    import java.net.HttpURLConnection;
    import java.net.URL;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @Author chengpunan
     * @Description //TODO $
     * @Date $ $
     * @Param $
     * @return $
     **/
    @Api(description = "百度api查询金纬度")
    @RestController
    @RequestMapping("/DITU")
    public class lnglatController {
    
        private static final String URL = "http://api.map.baidu.com/geocoder/v2/?output=json&ak=您的apiAK";
    
        @PostMapping("/A")
        public List inputExcel(MultipartFile file) {
            //        //1.  使用HSSFWorkbook 打开或者创建 “Excel对象”
            //2.  用HSSFWorkbook返回对象或者创建sheet对象
            //3.  用sheet返回行对象，用行对象得到Cell对象
            //4.  对Cell对象进行读写
    
            Workbook workbook = null;
            try {
                workbook = new HSSFWorkbook(file.getInputStream());
                Sheet sheet = workbook.getSheetAt(0);
                int rows = sheet.getLastRowNum();
                if (rows == 0) {
                    System.out.println("数据为空");// 数据为空 请填写数据
                }
                long startTime = System.currentTimeMillis();
                StringBuffer sb = new StringBuffer();
                List list = new ArrayList();
                for (int i = 1; i <= rows + 1; i++) {
                    Row row = sheet.getRow(i);
                    if (row != null) {
                        Aaaa aaaa = Aaaa.builder()
                                .area(getCellValue(row.getCell(0)))
                                .name(getCellValue(row.getCell(1)))
                                .price(getCellValue(row.getCell(2)))
                                .price2(getCellValue(row.getCell(3)))
                                .A(getCellValue(row.getCell(4)))
                                .phone(getCellValue(row.getCell(5)))
                                .address(getCellValue(row.getCell(6)))
                                .email(getCellValue(row.getCell(7)))
                                .build();
    
    
                        String result = getResult(URL+"&address="+aaaa.getName().trim(),"","UTF-8","GET");
                        Map<String,Object> map = (Map) JSONUtils.parse(result);
                        String lng = "";
                        String lat = "";
                        if (map!=null){
                            Map remap = (Map) map.get("result");
                            if (remap!=null){
                                Map loMap = (Map) remap.get("location");
                                if (loMap!=null){
                                     lng = String.valueOf(loMap.get("lng"));
                                     lat = String.valueOf(loMap.get("lat"));
                                }
                            }
                        }
                        sb.append(aaaa.getName());
                        aaaa.setLng(lng);
                        aaaa.setLat(lat);
                        list.add(aaaa);
                    }
                }
                //组成带有金纬度的类
                System.out.println(sb.toString());
                test(list);
                return list;
            } catch (IOException e) {
                e.printStackTrace();
                return null;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    
        private static String getCellValue(Cell cell){
            String result="";
            if(null != cell) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        result = String.valueOf(cell.getNumericCellValue());
                        if(result.endsWith(".0")){
                            result = result.split("\\.0")[0] ;
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        result = cell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        result = "";
                        break;
                    default:
                        break;
                }
            }
            return result ;
        }
    
        public static String getResult(String urlStr, String content, String encoding,String method) {
            java.net.URL url = null;
            HttpURLConnection connection = null;
            try {
                url = new URL(urlStr);
                connection = (HttpURLConnection) url.openConnection();// 新建连接实例
                connection.setConnectTimeout(10000);// 设置连接超时时间，单位毫秒
                connection.setReadTimeout(10000);// 设置读取数据超时时间，单位毫秒
                connection.setDoOutput(true);// 是否打开输出流 true|false
                connection.setDoInput(true);// 是否打开输入流true|false
                connection.setRequestMethod(method);// 提交方法POST|GET
                connection.setUseCaches(false);// 是否缓存true|false
                connection.connect();// 打开连接端口
                DataOutputStream out = new DataOutputStream(connection.getOutputStream());// 打开输出流往对端服务器写数据
                out.writeBytes(content);// 写数据,也就是提交你的表单 name=xxx&pwd=xxx
                out.flush();// 刷新
                out.close();// 关闭输出流
                BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream(), encoding));
                StringBuffer buffer = new StringBuffer();
                String line = "";
                while ((line = reader.readLine()) != null) {
                    buffer.append(line);
                }
                reader.close();
                return buffer.toString();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    connection.disconnect();// 关闭连接
                }
            }
            return null;
        }
    
        public static void test(List list) throws Exception  {
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("学生表一");
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow((int) 0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
    //        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
    
    //        HSSFCell cell = row.createCell((short) 0);
    //        cell.setCellValue("学号");
    //        cell.setCellStyle(style);
    //        cell = row.createCell((short) 1);
    //        cell.setCellValue("姓名");
    //        cell.setCellStyle(style);
    //        cell = row.createCell((short) 2);
    //        cell.setCellValue("年龄");
    //        cell.setCellStyle(style);
    //        cell = row.createCell((short) 3);
    //        cell.setCellValue("生日");
    //        cell.setCellStyle(style);
    
            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
    
            for (int i = 0; i < list.size(); i++){
                row = sheet.createRow((int) i + 1);
                Aaaa stu = (Aaaa) list.get(i);
                // 第四步，创建单元格，并设置值
                row.createCell((short) 0).setCellValue(stu.getArea());
                row.createCell((short) 1).setCellValue(stu.getName());
                row.createCell((short) 2).setCellValue(stu.getPrice());
                row.createCell((short) 3).setCellValue(stu.getPrice2());
                row.createCell((short) 4).setCellValue(stu.getA());
                row.createCell((short) 5).setCellValue(stu.getPhone());
                row.createCell((short) 6).setCellValue(stu.getAddress());
                row.createCell((short) 7).setCellValue(stu.getEmail());
                row.createCell((short) 8).setCellValue(stu.getLng());
                row.createCell((short) 9).setCellValue(stu.getLat());
            }
            // 第六步，将文件存到指定位置
            try{
                FileOutputStream fout = new FileOutputStream("H:/students.xls");
                wb.write(fout);
                fout.close();
            }catch (Exception e)  {
                e.printStackTrace();
            }
        }
    }