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

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

client 端

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

}

　实体转换工具类

　

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



}