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

目的：源代码重复代码过多，所以准备提取工具类。考虑用过数组等，但是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());
        }

}

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;
    }
}