Read the EXCEL file data, and then call the third-party interface to re write the third-party data to the EXCEL file

Read the EXCEL file data, and then call the third-party interface to re write the third-party data to the EXCEL file

Many documents and data are provided in the work, ranging from a few to tens of thousands. You can edit one by yourself and define it by static final. Once there are too many, you have to use the read access method, which is efficient and fast. However, the premise is that the encoding of the document is in string format (experts can not change it), that is, the text format in excel, otherwise reading exceptions will occur.

First, change all text / numeric data in EXCEL to text style


The obvious distinction between text and numbers is whether there is a green triangle. If there is a green triangle, it is a normal text format. If there is no green triangle at the bottom, there will be an error in the reading process.

In the change method, 1 select the column with all numbers, 2 select the data, and 3 select the column by column,
4 select fixed width, next, click next, 5 select text, and then click finish.

In this way, when only reading the column, there will be no reading number exception. Of course, if a master can skip this step and use different value methods, it should be possible.

The document is ready, and then the top priority,
The code part is relatively simple, but for multi column data, the logic needs to be deducted by itself

------------------------------------Code split line-----------------------------------------

Part I overall call

logger.info("Start reading meter");
        List<String> strings = readExcel();
        logger.info("End reading table" + JSON.toJSONString(strings));
        logger.info("Start call API");
        HashMap<String, String> stringStringHashMap = ampApi(strings);
        logger.info("End call API" + JSON.toJSONString(stringStringHashMap));
        logger.info("Start output Excel");
        loadExcel(stringStringHashMap);
        logger.info("output Excel complete");

Notes: 1 in readexcel, because I only need one column of data, I only return one column of set
2. Calling the interface inversely means calling the interface according to the result in 1, and then inversing the result, that is, the key of map is the result of 1, and the value of map is the result of calling the interface
3. Outputting data will increase the output of a column of text

Part II Reading EXCEL

 /**
     * .
     *
     * @Description: Function: read a column address in Excel
     * @Author: LXT
     * @Date: 2021/11/15 14:00
     */
    public static List<String> readExcel() {
        List<String> columnList = new ArrayList<String>();
        File file = new File("C:\\Desktop\\XXX.xls");
        try {
            FileInputStream in = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(in);
            //Get which sheet page
            Sheet sheet = wb.getSheetAt(0);
            //Take the first line
            int firstRowNum = sheet.getFirstRowNum();
            //Take the last line
            int lastRowNum = sheet.getLastRowNum();
            Row row = null;
            Cell cell_a = null;
            Cell cell_b = null;
            for (int i = firstRowNum; i <= lastRowNum; i++) {
                row = sheet.getRow(i);          //Get line i
                cell_a = row.getCell(2);        //Gets the column of row i
                //If it is a number format here, it will be abnormal, so the front needs to be converted to text format
                String cellValue = cell_a.getStringCellValue().trim();
                columnList.add(cellValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return columnList;
    }

The code is relatively simple. It is to read excel, then read all the rows, and then put the selected column data in no row back into the set. If you need multiple columns or other data, remember to modify the inverse parameter and customize the inverse parameter data.

Part 3 calling API

 /**
     * .
     *
     * @return com.common.EzgServiceResult
     * @Description: Role: Gaode map api
     * @Author: LXT
     * @Date: 2021/11/15 13:54
     */
    public static HashMap<String, String> ampApi(List<String> list) {
        logger.info("Gaude map api");
        HashMap<String, String> map = new HashMap<>();
        for (String address : list) {
            logger.info("Gaude map api---" + address);
            // Return the longitude and latitude information of the input address address. The format is longitude and latitude
            String queryUrl = "https://restapi.amap.com/v3/geocode/geo?address=" + address + "&output=JSON&key=" + ampKey;
            String queryResult = getResponse(queryUrl);
            logger.info("Gaode map inverse reference---" + address);
            JSONObject job = JSONObject.parseObject(queryResult);
            if (job.get("status").toString().equals("0")){
                logger.error("Gaode map inverse reference---" + address);
            }else {
                if (!job.get("count").toString().equals("0")) {
                    JSONObject jobJSON = JSONObject
                            .parseObject(job.get("geocodes").toString().substring(1, job.get("geocodes").toString().length() - 1));
                    String DZ = jobJSON.get("location").toString();
                    map.put(address, DZ);
                }
            }
        }
        return map;
    }

In fact, it's simpler to traverse the excel data, and then call other interfaces. Here, I need the longitude and latitude of a location, and the document data is the names of tens of thousands of locations. I raise the api, and then return the longitude and latitude string of the location. Of course, I use the link to get the string together,
Here you can play at will, such as data falling into the database, data caching, etc.

Part III data output

 /**
     * .
     *
     * @Description: Function: read the address in a column of Excel and store it in two columns of longitude and latitude
     * @Author: LXT
     * @Date: 2021/11/15 14:00
     */
    public static void loadExcel(HashMap<String, String> stringStringHashMap) {
        //Read document
        File file = new File("C:\\Desktop\\XXX.xls");
        List<String> isNull = new ArrayList<>();
        try {
            FileInputStream in = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(in);
            Sheet sheet = wb.getSheetAt(0);
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            Row row = null;
            Cell cell_a = null;
            Cell cell_b = null;
            for (int i = firstRowNum; i <= lastRowNum; i++) {
                row = sheet.getRow(i);          //Get line i
                Cell cell = row.getCell(2);
                String cellValue = cell.getStringCellValue().trim();
                logger.info("Value excel" + cellValue);
                //The previous step is the same as the second step, reading excel
                //Here is to use the obtained value to get the matching in the inverse parameter map
                String s = stringStringHashMap.get(cellValue);
                logger.info("Value map" + s);
                if (null != s && s.contains(",")) {
                	//If so, use it for segmentation. Third, add the longitude column
                    cell_a = row.getCell(3);        //Longitude column
                    cell_a.setCellValue(s.split(",")[0]);
                    //If so, use it for segmentation. Fourth, add the longitude column
                    cell_b = row.getCell(4);        //Latitude column
                    cell_b.setCellValue(s.split(",")[1]);
                }else {
                    isNull.add(s);
                }
            }
            logger.info("by null address" + JSON.toJSONString(isNull));
            //After the data is placed, use a new document to get new document data through line output
            FileOutputStream fileOutputStream = new FileOutputStream("C:Desktop\\XXX-2.xls");
            wb.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

This is equivalent to the data integration of the first and second steps. There is no technical content, that is, traversal and comparison. In fact, the three steps can be put into one, read the data, adjust the interface and place new data. One can be executed without three steps.

The general steps are like this. Because it is not used online, it just needs some data conversion. There is no optimization, that is, traversal. 20000 data is about half an hour. Remember to optimize it if necessary. It's a little too much to do so.

OK, that's it, remember to praise!!!

Tags: Java Excel JSON Spring post

Posted on Mon, 22 Nov 2021 02:47:34 -0500 by hussain