poi excel下载工具类优化（利用LinkedHashMap和反射getDeclaredMethod）

待我称王封你为后i 2022-05-25 01:13 143阅读 0赞

目的：源代码重复代码过多，所以准备提取工具类。考虑用过数组等，但是Excel需要标题（中文）和对象属性对应关系，所以考虑LinkedHashMap方法按顺序提取，后利用getDeclaredMethod("show", null); 第一个参数时String方法名，第二个参数时输入参数，没有为空，后进行优化，根据属性得到get方法（getUsername 改为username 属性名）。

#### 一、未优化前代码 ####

public static HSSFWorkbook excelExport(HSSFWorkbook workbook,List<LenovoShopAuthenticateClassResultVo> resultlist){
            //新建sheet
            HSSFSheet sheet = workbook.createSheet("sheet001");
            //设置行宽，列高
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            //新建标题行
            HSSFRow row0 =sheet.createRow(0);
            Cell cell0 = row0.createCell(0);
            cell0.setCellValue("编号");
            Cell cell1 = row0.createCell(1);
            cell1.setCellValue("名称");
            Cell cell2 = row0.createCell(2);
            cell2.setCellValue("类型");
            Cell cell3 = row0.createCell(3);
            cell3.setCellValue("申请级别");
            Cell cell4 = row0.createCell(4);
            cell4.setCellValue("上季度认证级别");
            Cell cell5 = row0.createCell(5);
            cell5.setCellValue("创建时间");
            Cell cell6 = row0.createCell(6);
            cell6.setCellValue("申请时间");
            Cell cell7 = row0.createCell(7);
           
            //给数据列赋值
            for (int i = 0; i < resultlist.size(); i++) {
                Row row =sheet.createRow(i+1);
                LenovoShopAuthenticateClassResultVo resultVo = resultlist.get(i);
                Cell cell11 = row.createCell(0);
                cell11.setCellValue(resultVo.getShopid());
    
                Cell cell12 = row.createCell(1);
                cell12.setCellValue(resultVo.getShopname());
    
                Cell cell13 = row.createCell(2);
                cell13.setCellValue(resultVo.getShoptype());
    
                Cell cell14 = row.createCell(3);
                cell14.setCellValue(resultVo.getShoplevelname());
    
                Cell cell15 = row.createCell(4);
                cell15.setCellValue(resultVo.getAuthenticateclassname());
    
                Cell cell16 = row.createCell(5);
                cell16.setCellValue(resultVo.getShopcreatedate().substring(0, 19));
    
                Cell cell17 = row.createCell(6);
                cell17.setCellValue(resultVo.getCreatedate().substring(0, 19));
    
                Cell cell18 = row.createCell(7);
                cell18.setCellValue(resultVo.getAuthenticatedate().substring(0, 19));
    
                }
            return workbook;
        }

### 2.按put顺序的map       ###

hashMap、treeMap和linkedHashMap，但是，要达到我们的要求：按添加顺序保存元素的，就只有LinkedHashMap

@Test
        public  void main(String[] args) {
            Map<String, Integer> hashMap = Maps.newHashMap();
            Map<String, Integer> treeMap = Maps.newTreeMap();
            Map<String, Integer> linkedHashMap = Maps.newLinkedHashMap();
            System.out.println("--------------test hashMap");
            testMap(hashMap);
            System.out.println("--------------test treeMap");
            testMap(treeMap);
            System.out.println("--------------test linkedHashMap");
            testMap(linkedHashMap);
        }
    
        private  void testMap(Map<String, Integer> map) {
            map.put("asd", 1);
            map.put("2das", 2);
            map.put("3das", 3);
            map.put("4das", 4);
            for (Map.Entry<String, Integer> entry : map.entrySet()) {
                System.out.println(entry.getKey() + ":" + entry.getValue());
            }
    
    }

![70][]

### 3.利用反射获取方法执行 ###

public class AuthenticateClassServiceImpl {
    @Test
        public void AuthenticateClass() throws Exception {
            // 创建一个Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook();
    
            List<Object> userModels = new ArrayList<>();
            UserModel zhangsan = new UserModel("zhangsan", "123");
    
        UserModel lishi = new UserModel("lisi", "456");
        UserModel o = new UserModel("uiooo", "8");
        UserModel ee = new UserModel("eee", "123");
        UserModel ff = new UserModel("ff", "123");
            userModels.add(zhangsan);
            userModels.add(lishi);
        userModels.add(o);
        userModels.add(ee);
        userModels.add(ff);
    
             Map<String,String> linkedHashMap = Maps.newLinkedHashMap();
             linkedHashMap.put("姓名","getUsername");
             linkedHashMap.put("密码","getPassword");
    
                for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
                    System.out.println(entry.getKey() + ":" + entry.getValue());
                }
    
    
    
    
            //取数据
            workbook = excelExport(workbook, userModels, linkedHashMap);
    
        }
    
        public static HSSFWorkbook excelExport(HSSFWorkbook workbook, List<Object> resultlist, Map<String,String> linkedHashMap) throws Exception {
    
            //新建sheet
            HSSFSheet sheet = workbook.createSheet("sheet001");
            //设置行宽，列高
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            //新建标题行
    
    
    
            for (int k= 0; k <= resultlist.size(); k++) {
                HSSFRow row = sheet.createRow(k);
                if (k == 0) {
                    int i = 0;
                    for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
    
                        row.createCell(i).setCellValue(entry.getKey());
    
                        System.out.print(i + entry.getKey());
                        i++;
                        System.out.println("========");
                    }
                } else {
    
                    //给数据列赋值
                    int j = 0;
                    Object resultVo = resultlist.get(k-1);
                    for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
                        //获取方法
                        Method m = resultVo.getClass().getDeclaredMethod(entry.getValue(),null);
                        //调用方法
                        String result = (String) m.invoke(resultVo);
    
                        row.createCell(j).setCellValue(result);
    
                        System.out.print(j + "--------"+result);
                        j++;
                        System.out.println("========");
                    }
    
                }
            }
    
            return workbook;
        }
    }

### 4.改进     ###

getUsername 改为username 属性名

Map<String,String> linkedHashMap = Maps.newLinkedHashMap();
             linkedHashMap.put("姓名","username");
             linkedHashMap.put("密码","password");

import com.google.common.collect.Maps;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.junit.Test;
    
    import java.lang.reflect.Method;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    public class AuthenticateClassServiceImpl {
    @Test
        public void ShopAuthenticateClass() throws Exception {
            // 创建一个Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook();
    
            List<Object> userModels = new ArrayList<>();
            UserModel zhangsan = new UserModel("zhangsan", "123");
    
        UserModel lishi = new UserModel("lisi", "456");
        UserModel o = new UserModel("uiooo", "8");
        UserModel ee = new UserModel("eee", "123");
        UserModel ff = new UserModel("ff", "123");
            userModels.add(zhangsan);
            userModels.add(lishi);
        userModels.add(o);
        userModels.add(ee);
        userModels.add(ff);
    
             Map<String,String> linkedHashMap = Maps.newLinkedHashMap();
             linkedHashMap.put("姓名","username");
             linkedHashMap.put("密码","password");
    
                for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
                    System.out.println(entry.getKey() + ":" + entry.getValue());
                }
    
    
    
    
            //取数据
            workbook = excelExport(workbook, userModels, linkedHashMap);
    
        }
    
        public static HSSFWorkbook excelExport(HSSFWorkbook workbook, List<Object> resultlist, Map<String,String> linkedHashMap) throws Exception {
    
            //新建sheet
            HSSFSheet sheet = workbook.createSheet("sheet001");
            //设置行宽，列高
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            //新建标题行
    
    
    
            for (int k= 0; k <= resultlist.size(); k++) {
                HSSFRow row = sheet.createRow(k);
                if (k == 0) {
                    int i = 0;
                    for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
    
                        row.createCell(i).setCellValue(entry.getKey());
    
                        System.out.print(i + entry.getKey());
                        i++;
                        System.out.println("========");
                    }
                } else {
    
                    //给数据列赋值
                    int j = 0;
                    Object resultVo = resultlist.get(k-1);
                    for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
                        String result = null;
                        Method[] m = resultVo.getClass().getMethods();
                        for(int i = 0;i < m.length;i++){
                            if(("get"+entry.getValue()).toLowerCase().equals(m[i].getName().toLowerCase())){
                                result=(String)m[i].invoke(resultVo);
                            }
                        }
    
    
                        row.createCell(j).setCellValue(result);
    
                        System.out.print(j + "--------"+result);
                        j++;
                        System.out.println("========");
                    }
    
                }
            }
    
            return workbook;
        }
    }

[70]: /images/20220525/307541f01d5a439dac7ae8e63289bb60.png