java Excel转换为POJO对象 公共组件

前言：

很多软件都有使用EXCEL导入数据的场景。一般做法如下：

1.根据EXCEL模版编辑数据

2.点击文件上传到服务端

3.解析文件并进行数据校验

4.获取校验成功的数据

5.匹配成功数据到POJO对象

6.返回失败的EXCEL并标记提示

7.客户拿到无法导入的excel并根据提示进行更新，更新后重复2操作

本工具核心解决的是3-6问题

原理：

1.excel对象转化pojo，首先需要把excel列和pojo的属性的关系对应起来，然后设置pojo属性的校验规则，核心处理对象根据对应pojo属性与excel列的关系以及pojo属性校验规则进行处理，对于正确的数据我们进行转换成pojo对象，对于错误的数据我们保存为错误的excel对象

2.excel对象使用workbook操作

具体实现：

1.规则注解

目前支持不许为空required、主键primarykey

其中主键意思为如果excel多行存在相同的主键数据，只要其中一行校验不合法则主键对应的所有行都不允许转换pojo，并输出到错误的excle对象里，注解如下：

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Column {
    boolean required() default false;
    String code();
    boolean primarykey() default false;
}

2.核心处理对象

import com.alibaba.dubbo.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * author:wuliang
 */
public class ExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    private static Map<String, Integer> getCellIndexs (Row rowheader) {
        int totalCells = rowheader.getPhysicalNumberOfCells();//列数
        Map<String, Integer> filedIndexMap = new HashMap<>();
        for (int c = 0; c < totalCells; c++) {
            org.apache.poi.ss.usermodel.Cell cell = rowheader.getCell(c);
            HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
            String cellValue = hSSFDataFormatter.formatCellValue(cell);
            if (!StringUtils.isEmpty(cellValue)) {
                filedIndexMap.put(cellValue, c);
            }
        }
        return filedIndexMap;
    }

    private static <T> List<T> getObjList (Map<String, List<KeyValue<Row, T>>> successRows) {
        List<T> objList = new ArrayList<>();
        for (Map.Entry<String, List<KeyValue<Row, T>>> entry : successRows.entrySet()) {
            for (KeyValue<Row, T> obj : entry.getValue()) {
                objList.add(obj.getV());
            }
        }
        return objList;
    }

    private static <T> boolean isErrorOrder (List<KeyValue<Row, T>> list) {
        boolean iserr = false;
        for (KeyValue<Row, T> obj : list) {
            if (obj.getV() == null) {
                iserr = true;
                break;
            }
        }
        return iserr;
    }

    private static void delNullRows (Sheet sheet) {
        int i = sheet.getLastRowNum();
        Row tempRow;
        while (i > 0) {
            i--;
            tempRow = sheet.getRow(i);
            if (tempRow == null) {
                sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            }
        }
    }

    private static <T> int getErrWorkbook (Map<String, List<KeyValue<Row, T>>> successRows) {
        int errcount=0;
        List<T> objList = new ArrayList<>();
        for (Iterator<Map.Entry<String, List<KeyValue<Row, T>>>> it = successRows.entrySet().iterator(); it.hasNext(); ) {
            Map.Entry<String, List<KeyValue<Row, T>>> item = it.next();
            boolean iserr = isErrorOrder(item.getValue());
            if (iserr) {
                //移除错误的记录
                it.remove();
                errcount=errcount+item.getValue().size();
            } else {
                //在excel中删除正确的记录,只保留错误的记录
                for (int j = 0; j < item.getValue().size(); j++) {
                    Row row = item.getValue().get(j).getK();
                    row.getSheet().removeRow(row);
                }
            }
        }
        return errcount;
    }

    public static <T> Result<T> adapter(Class<T> t, Workbook wb) throws IllegalAccessException, InstantiationException {
        Result<T> result = new Result<>();
        //region 设置错误提示样式
        Sheet sheet = wb.getSheetAt(0);
        Font font = wb.createFont();
        font.setColor(Font.COLOR_RED);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);
        //endregion 样式设置结束
        int totalRows = sheet.getLastRowNum();
        boolean sethint = false;
        if (totalRows >= 2) {
            Row rowheader = sheet.getRow(0);//第一行必须是列名行
            int lastCellNum = rowheader.getLastCellNum();//列数
            Map<String, Integer> filedIndexMap = getCellIndexs(rowheader);
            Field[] fields = t.getDeclaredFields();
            Map<String, List<KeyValue<Row, T>>> successRows = new HashMap<>();
            for (int r = 1; r <= totalRows; r++) {
                Row row = sheet.getRow(r);
                T obj = t.newInstance();
                String primarykey = "";
                String errorMsg = "";
                boolean error = false;
                if (row == null)
                    continue;
                for (Field field : fields) {
                    Column cell = field.getAnnotation(Column.class);
                    if (cell != null) {
                        String cellValue = null;
                        Object indexObj = filedIndexMap.get(cell.code());
                        if (indexObj != null) {
                            int index = (int) indexObj;
                            org.apache.poi.ss.usermodel.Cell cell1 = row.getCell(index);
                            HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                            cellValue = hSSFDataFormatter.formatCellValue(cell1);
                        }
                        if (cell.primarykey()) {
                            primarykey = primarykey + cellValue;
                        }
                        if ((cell.primarykey() || cell.required()) &&
                                StringUtils.isEmpty(cellValue)
                                ) {
                            errorMsg = errorMsg + cell.code() + ",不能为空;";
                            error = true;
                        } else if (cellValue != null) {
                            field.setAccessible(true);
                            field.set(obj, cellValue);
                        }
                    }
                }
                KeyValue<Row, T> keyValue = new KeyValue<>();
                keyValue.setK(row);
                if (!error)
                    keyValue.setV(obj);
                else {
                    sethint = true;
                    org.apache.poi.ss.usermodel.Cell errorCell = row.createCell(lastCellNum);
                    errorCell.setCellValue(errorMsg);
                    errorCell.setCellStyle(cellStyle);
                }
                List<KeyValue<Row, T>> list = successRows.get(primarykey);
                if (list == null) {
                    list = new ArrayList<>();
                    list.add(keyValue);
                    successRows.put(primarykey, list);
                } else {
                    list.add(keyValue);
                }
            }
            if (sethint) {
                //如果存在失败的记录执行
                org.apache.poi.ss.usermodel.Cell errorMsgCell = rowheader.createCell(lastCellNum);
                sheet.setColumnWidth(lastCellNum, 30 * 256);
                errorMsgCell.setCellValue("提示");
                int errcount=getErrWorkbook(successRows);//wb中只保留错误数据
                delNullRows(sheet);//删除空行
                result.setErrWorkbook(wb);//保存错误的wb
                result.setFail(errcount);
 /*
               //默认写入数据
                FileOutputStream os = null;
                try {
                    os = new FileOutputStream("d://test.xlsx");
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                }
                try {
                    wb.write(os);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
*/
            }
            List<T> objList = getObjList(successRows);
            result.setList(objList);
            result.setSuccess(objList.size());
        }
        return result;
    }
    public static <T> Result<T> adapter(Class<T> t, InputStream is) throws IOException, InvalidFormatException, InstantiationException, IllegalAccessException {
        Workbook wb = WorkbookFactory.create(is);
        return adapter(t,wb);
    }
}

import org.apache.poi.ss.usermodel.Workbook;

import java.util.List;

public class Result<T> {
    List<T> list;//成功列表
    int success;//成功条数
    int fail;//失败条数
    Workbook errWorkbook;//失败表单
    public List<T> getList () {
        return list;
    }
    public void setList (List<T> list) {
        this.list = list;
    }
    public int getSuccess () {
        return success;
    }

    public void setSuccess (int success) {
        this.success = success;
    }

    public int getFail () {
        return fail;
    }

    public void setFail (int fail) {
        this.fail = fail;
    }

    public Workbook getErrWorkbook () {
        return errWorkbook;
    }

    public void setErrWorkbook (Workbook errWorkbook) {
        this.errWorkbook = errWorkbook;
    }
}

/**
 * 键值对象
 * @param <K>
 * @param <V>
 */
public class KeyValue<K,V> {
    private K k;
    private V v;
    public K getK () {
        return k;
    }
    public void setK (K k) {
        this.k = k;
    }

    public V getV () {
        return v;
    }

    public void setV (V v) {
        this.v = v;
    }
}

3.调用

public class OrderImport {
    @Column(code = "订单编号", primarykey = true)
    private String ordernumber;//订单编号
    @Column(code = "店铺编号",required = true)
    private String shopID;//店铺编号
    @Column(code = "订单状态")
    private String status; // 订单状态
    @Column(code = "退款状态")
    private String refundStatus; // 退款状态

public static void main (String[] args) throws ParseException {

    InputStream is;
    try {

        is = new FileInputStream("D:\\导入订单模板--测试专用.xlsx");
        Result<OrderImport> result = ExcelUtils.adapter(OrderImport.class, is);//OrderImport 为pojo对象
        if (result.getList().size() > 0) {
           // FunOrderImportBizImpl ss=new FunOrderImportBizImpl();
           // TradeCollection tradeCollection = ss.getTradeCollection(1, "2", result.getList());
           // System.out.println(1);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}
}