利用apache 的PropertyUtilsBean 实现map和pojo相互转换

因为公司需要利用poi 进行自定义的导出，乘此了解一下poi的一些常用操作

client 端

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
import com.alibaba.excel.metadata.BaseRowModel;
import com.hiberate.huijpa.pojo.EmpExcelModel;
import com.hiberate.huijpa.util.ReflectUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
 
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
 
/**
 * @author liyhu
 * @date 2019年09月28日
 */
public class PoiModelClient {
    public static void main(String[] args) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, NoSuchFieldException {
        String sheetName="one";
        List<BaseRowModel> data=new ArrayList<>();
        for (int i = 0; i < 9; i++) {
            EmpExcelModel model=new EmpExcelModel();
            model.setMobile("mobile"+i);
            model.setCardPassword("pwd"+i);
            model.setCardSn("sn"+i);
            model.setCardNo("no"+i);
            model.setFreezeStatus("正常");
            data.add(model);
        }
        Workbook wb = ExcelUtil.createExcel(sheetName, data);
        try (FileOutputStream os = new FileOutputStream("D:\\a.xlsx")){
            wb.write(os);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("ok");
 
    }
}

工具类


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.hiberate.huijpa.util.ReflectUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author liyhu
 * @date 2019年09月28日
 */
public class ExcelUtil {

    private static short text;
    private static Font blodFont ;// 粗字
    private static Font redFont; // 红字
    private static Workbook globalWorkBook =new XSSFWorkbook();
//  匹配这种格式  *状态[正常/立即冻结/售完冻结]
    private static Pattern pullDownPattern = Pattern.compile("[\\u4e00-\\u9fa5]+\\[([\\u4e00-\\u9fa5]+/[\\u4e00-\\u9fa5|/]+)]$");
    static {
        DataFormat dataFormat = globalWorkBook.createDataFormat();//创建格式化对象
        text=dataFormat.getFormat("TEXT");

        blodFont= globalWorkBook.createFont();
        blodFont.setBold(true);// 加粗
        blodFont.setFontName("宋体");
        blodFont.setFontHeightInPoints((short) 14);// 14号字体

        redFont = globalWorkBook.createFont();
        redFont.setBold(true);
        redFont.setFontName("宋体");
        redFont.setColor(Font.COLOR_RED);
    }

    /**
     * 这里的 workbook 不能用全局的 workbook
     * @param workbook
     * @return
     */
    private static CellStyle crateTitleCellStyle(Workbook workbook){
        CellStyle titleStyle = workbook.createCellStyle();        //标题样式
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return titleStyle;
    }



    /**
     * 创建标题
     * @param workbook
     * @param sheet
     * @param colWidthMap
     */
    private static void setHeader(Workbook workbook, Sheet sheet, Map<String, String> headerMap, Map<Integer, Integer> colWidthMap, int startRow){
        Row titleRow = sheet.createRow(startRow);
        CellStyle textStyle = workbook.createCellStyle();        //标题样式
        textStyle.setDataFormat(text);

        //这里不能和类里的font公用
        Font blodFont = workbook.createFont();
        blodFont.setBold(true);
        blodFont.setFontName("宋体");
        blodFont.setFontHeightInPoints((short) 14);

        CellStyle titleStyle= crateTitleCellStyle(workbook);
        titleStyle.setFont(blodFont);

        int index = 0;
        for (Map.Entry<String, String> header : headerMap.entrySet()) {
            Cell cell = titleRow.createCell(index);
            cell.setCellStyle(titleStyle);
            String excelPropertyVal = header.getValue();

            Matcher matcher = pullDownPattern.matcher(excelPropertyVal);
            if(matcher.find()){// 创建该列的下拉
                String[] subjects  = matcher.group(1).split("/");
                DataValidationHelper helper = sheet.getDataValidationHelper();
                DataValidationConstraint constraint = helper.createExplicitListConstraint(subjects);
                CellRangeAddressList  addressList = new CellRangeAddressList(startRow + 1, startRow+50, index, index);
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                sheet.addValidationData(dataValidation);
            }

            setColWidth(colWidthMap,excelPropertyVal,index,true);
            RichTextString richTextString = new XSSFRichTextString(excelPropertyVal);
            richTextString.applyFont(blodFont);
            if (richTextString.getString().startsWith("*")) {
                richTextString.applyFont(0, 1, redFont);
            }
            cell.setCellValue(richTextString);
            sheet.setDefaultColumnStyle(index, textStyle);
            index++;
        }
    }

    private static void setColWidth(Map<Integer, Integer> colWidthMap, String val, int index, boolean isHeader){
        int length = val.length();
        Integer defaultColWidth = colWidthMap.get(index);
        if (length > defaultColWidth) {
            if(isHeader){// 标题则是字数的两倍
                length *= 2;
            }
            colWidthMap.put(index, length );
        }
    }


    /**
     * 这里的 workbook 不能用全局的 workbook <br/>
     * 获取数据单元格样式
     * @return
     * @param workbook
     */
    private static CellStyle createDataCellStyle(Workbook workbook){
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setDataFormat(text);
        return dataStyle;
    }

    /**
     * 设置单元格数据
     * @param sheet
     * @param dataMapList
     * @param headerMap
     * @param colWidthMap
     * @param startRow
     */
    private static void setData(Workbook workbook, Sheet sheet, List<Map<String, Object>> dataMapList, Map<String, String> headerMap, Map<Integer, Integer> colWidthMap, int startRow){
        CellStyle dataStyle= createDataCellStyle(workbook);
        int rowIndex = 0;
        for (Map<String, Object> beanMap : dataMapList) {
            Row row = sheet.createRow(rowIndex + startRow);
            int colIndex = 0;
            for (Map.Entry<String, String> entry : headerMap.entrySet()) {// 遍历标题
                Cell cell = row.createCell(colIndex);
                String key = entry.getKey();
                Object val = beanMap.get(key);// 根据标题找到对应的值
                String valString = "";
                if(val != null){
                    valString = beanMap.get(key).toString();
                }
                cell.setCellValue(valString);
                cell.setCellStyle(dataStyle);

                setColWidth(colWidthMap,valString,colIndex, false);
                colIndex++;
            }
            rowIndex++;
        }
    }


    public static Workbook createExcel(String sheetName, List<BaseRowModel> data,Class clazz) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, NoSuchFieldException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultRowHeight((short) (2 * 256));//设置默认行高
        Map<String, String> headerMap = ReflectUtil.getBeanExcelProperty(clazz);
        int colNum = headerMap.size();// 列的数量
        int startRow = 0;

        Map<Integer, Integer> colWidthMap = new HashMap<>();
        for (int i = 0; i < colNum; i++) {// 设置默认列宽
            colWidthMap.put(i, 14);
        }
        setHeader(workbook,sheet,headerMap,colWidthMap,startRow);
        if(!CollectionUtils.isEmpty(data)){
            List<Map<String, Object>> dataMapList = new ArrayList<>();
            for (BaseRowModel model : data) {
                Map<String, Object> map = ReflectUtil.beanToMap(model);
                dataMapList.add(map);
            }
            setData(workbook,sheet,dataMapList,headerMap,colWidthMap,startRow + 1);
        }

        for (Map.Entry<Integer, Integer> entry : colWidthMap.entrySet()) {
            sheet.setColumnWidth(entry.getKey(), entry.getValue() * 256);//设置每列宽度
        }
        return workbook;
    }


    public static TreeMap<Integer,String>  getSortMap(Class<?> clazz){
        TreeMap<Integer,String> treeMap=new TreeMap<>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if(excelProperty == null){
                continue;
            }
            treeMap.put(excelProperty.index(),field.getName());
        }
        return treeMap;
    }

}

　实体转换工具类

　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
import com.alibaba.excel.annotation.ExcelProperty;
import org.apache.commons.beanutils.PropertyUtilsBean;
 
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.TreeMap;
 
/**
 * @author liyhu
 * @date 2019年08月27日
 */
public class ReflectUtil {
    public static <T>T mapToProperties(Map<String,Object> map,Class<T> tClass) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException {
        T t = tClass.newInstance();
        PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
        PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(t);
        for (PropertyDescriptor descriptor : descriptors) {
            String name = descriptor.getName();
            if("class".equals(name)){
                continue;
            }
            Object val = map.get(name);
            if(val != null){
                propertyUtilsBean.setProperty(t,name,val);
            }
        }
        return t;
    }
    public static Map<String,Object> commonBeanToMap(Object obj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
        PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
        Map<String,Object> resultMap=new HashMap<>(descriptors.length);
        for (PropertyDescriptor descriptor : descriptors) {
            String name = descriptor.getName();
            if("class".equals(name)){
                continue;
            }
            Object val = propertyUtilsBean.getNestedProperty(obj, name);
            if(val != null){
                resultMap.put(name,val);
            }
        }
        return resultMap;
    }
 
    public static Map<String,Object> beanToMap(Object obj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, NoSuchFieldException {
        TreeMap<Integer,String> treeMap=new TreeMap<>();
        Class<?> aClass = obj.getClass();
        PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
        PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
        Map<String,Object> resultMap=new HashMap<>(descriptors.length);
        for (PropertyDescriptor descriptor : descriptors) {
            String name = descriptor.getName();
            if("class".equals(name)){
                continue;
            }
            Object val = propertyUtilsBean.getNestedProperty(obj, name);
            if(val != null){
                resultMap.put(name,val);
                if("cellStyleMap".equals(name)){
                    continue;
                }
                Field field = aClass.getDeclaredField(name);
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                if(excelProperty != null){
                    treeMap.put(excelProperty.index(),name);
                }
            }
        }
 
        Map<String,Object> result=new LinkedHashMap<>();
        for (Map.Entry<Integer, String> entry : treeMap.entrySet()) {
            Object val = resultMap.get(entry.getValue());
            result.put(entry.getValue(),val);
        }
 
        return result;
    }
 
 
 
}

　　

posted @   冬马党  阅读(1156)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示