Easyexcel reads the geographical location data in the excel table and sorts them according to Chinese pinyin

Use Easyexcel Read excel file

EasyExcel It's based on Java Simple 、 Save memory for reading and writing Excel Open source projects for .
Official website ：https://www.yuque.com/easyexcel/doc/easyexcel

1.maven Import

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.0.5</version>
</dependency>

2. Create entity class

The name in the note should be the same as excel The name of the header is consistent
excel surface

3. Create entity class

@Data
public class WeatherExcelData {
    
    @ExcelProperty("Location_ID")
    private String Location_ID;

    /** *  Region name  */
    @ExcelProperty("Location_Name_ZH")
    private String locationName;
    /** *  First class area name  */
    @ExcelProperty("Adm1_Name_ZH")
    private String primaryArea;
    /** *  Name of secondary area  */
    @ExcelProperty("Adm2_Name_ZH")
    private String SecondArea;
}

4. Use easyexcel Read excel After the document , Deposited in the cache . Use Collator class , rewrite compare Comparison method . Use stream The stream is named according to the first level region , Second level region name grouping sorting .
Use here easyexcel The simplest reading , For more usage, please check the official website

public class ResourceController {
    
    private static Map<String, WeatherExcelData> locationMap = new HashMap<>();
    private static List<WeatherExcelData> locationList = new ArrayList<>();
    private static TreeMap<String, Map<String, List<WeatherExcelData>>> treeMap = new TreeMap();


    public void getExcel(){
    
        try{
    
        	// excel The document is placed in resources Under the folder , adopt ClassPathResource Form read , Place after packaging linux Data can also be read normally in the environment 
            ClassPathResource resource = new ClassPathResource("China-City-List-latest.xlsx");
            //  here   You need to specify which to read class To read , Then read the first sheet  The file stream closes automatically 
            //  It reads every time 3000 Data   Then come back   Just call and use the data directly 
            EasyExcel.read(resource.getInputStream(), WeatherExcelData.class, new PageReadListener<WeatherExcelData>(dataList -> {
    
                for (WeatherExcelData data : dataList) {
    
                    locationMap.put(data.getLocationName() + "", data);
                }
                locationList.addAll(dataList);
            })).sheet().doRead();

            putLocationCache(locationList);
        }catch (Exception e){
    
            log.error(" Error reading region file ");
        }
    }

    /** *  Cache geographic location  */
    public void putLocationCache(List<WeatherExcelData> locationList) {
    
        if(locationList.size() == 0){
    
            return;
        }

        TreeMap<String, Map<String, List<WeatherExcelData>>> newLocation = new TreeMap<>(new Comparator<Object>() {
    
        	//  Set up Locale.CHINA Sort by Chinese 
            Collator collator = Collator.getInstance(Locale.CHINA);

            @Override
            public int compare(Object  object1, Object  object2) {
    
                if (object1 == null || object2 == null){
    
                    return 0;
                }
                CollationKey keyOne = collator.getCollationKey(String.valueOf(object1));
                CollationKey keyTwo = collator.getCollationKey(String.valueOf(object2));
                return keyOne.compareTo(keyTwo);
            }
        });

        //  Turn into TreeMap, First, press the first-class region name , Then sort by the name of the secondary region 
        Map<String, Map<String, List<WeatherExcelData>>> location = locationList.stream()
                .collect(Collectors.groupingBy(WeatherExcelData::getPrimaryArea,
                        Collectors.groupingBy(WeatherExcelData::getSecondArea, Collectors.toList())));
        for (Map.Entry<String, Map<String, List<WeatherExcelData>>> entry : location.entrySet()) {
    
            newLocation.put(entry.getKey(), entry.getValue());
        }

        treeMap = newLocation;
        System.out.println(treeMap);
    }
}