阿里 easyexcel导入

pom依赖

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
        </dependency>

监听对象, 用于接收读取excel 中读取数据

package com.exx.dzj.util.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author yangyun
 * @create 2019-03-19-10:15
 */
public class ExcelListener extends AnalysisEventListener {

    /**
     * @description 用于保存数据
     * @author yangyun
     * @date 2019/3/27 0027
     * @param null
     * @return
     */
    private List<Object> datas = Collections.synchronizedList(new ArrayList<>());

    /**
     * @description
     * @author yangyun
     * @date 2019/3/19 0019
     * @param o 读取的每行数据
     * @param analysisContext
     * @return void
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);
    }

    /**
     * @description excel 读取完之后的操作
     * @author yangyun
     * @date 2019/3/27 0027
     * @param analysisContext
     * @return void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//        analysisContext.
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }
}



package com.exx.dzj.util.excel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.exx.dzj.constant.CommonConstant;
import com.exx.dzj.excepte.ErpException;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @description alibaba easyexcel 解析 excel 解析单个excel
 * @author yangyun
 * @date 2019/3/19 0019
 * @return
 */
public class ExcelUtil {

    /**
     * @description
     * @author yangyun
     * @date 2019/3/19 0019
     * @param excel 上传excel 文件
     * @param rowModel 对应实体对象
     * @param sheetNo 解析开始行, 有表头的情况从第一行开始
     * @return java.util.List<java.lang.Object>
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
        return readExcel(excel, rowModel, sheetNo, CommonConstant.DEFAULT_VALUE_ONE);
    }

    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    public static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException{
        String fileName = excel.getOriginalFilename();
        if (fileName == null || !(fileName.toLowerCase().endsWith(".xls") || fileName.toLowerCase().endsWith(".xlsx") || fileName.toLowerCase().endsWith(".xlsm"))){
            throw new ErpException(CommonConstant.FAIL_CODE, "文件格式错误");
        }

        InputStream is = new BufferedInputStream(excel.getInputStream());

        return new ExcelReader(is, null, excelListener, false);
    }
}

上面两个类就是主要解析和接收数据的类, 还需要一个model 与excel 的没列表格映射, 获取每行数据, 该映射对象需要继承 BaseRowModel

package com.exx.dzj.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.math.BigDecimal;

/**
 * @author yangyun
 * @create 2019-03-27-15:38
 */
@Data
public class StockModel extends BaseRowModel {
    @ExcelProperty(value = "存货编号", index = 0)
    private String stockCode;

    @ExcelProperty(value = "存货性质", index = 1) //
    private String nature;

    @ExcelProperty(value = "存货名称", index = 2)
    private String stockName;

    @ExcelProperty(value = "规格型号", index = 3)
    private String specificateType;

    @ExcelProperty(value = "存货类别", index = 4)
    private String stockClass;

    @ExcelProperty(value = "默认存货地点", index = 5)
    private String stockAddress;

    @ExcelProperty(value = "计量单位", index = 6)
    private String meterUnit;

    @ExcelProperty(value = "条形码", index = 7)
    private String barCode;

    @ExcelProperty(value = "标准买价", index = 8)
    private BigDecimal standardBuyPrice;

    @ExcelProperty(value = "标准卖价", index = 9)
    private BigDecimal standardSellPrice;

    @ExcelProperty(value = "最高采购限价", index = 10)
    private BigDecimal maxPurchasePrice;

    @ExcelProperty(value = "最低销售限价", index = 11)
    private BigDecimal minSellPrice;

    @ExcelProperty(value = "最低存量", index = 12)
    private Integer minInventory;

    @ExcelProperty(value = "状态", index = 12) // 1 使用中 0 禁用
    private String status;
}

代码已完, 那么问题来了

由于下面列的格式导致还有 4 个 byte 没有被读取

将文件另存为启用宏的 excel 工作簿,


将后缀名一改就ok了, 或者在代码中允许这种格式的excel上传
后面又遇到这个问题
SQL state [HY000]; error code [1366]; Incorrect string value: ‘\xF0\xA3\x87\x88\xE5\xA8…’ for column ‘cust_name’ at row 13078; nested exception is java.sql.SQLException: Incorrect string value: ‘\xF0\xA3\x87\x88\xE5\xA8…’ for column 意思就是说从 excel 中导入数据, 但是由于 mysql
中编码设置不能兼容, 所以出错了.

server 中字符编码为 utf8 , 虽然开始查看表字段编码设置的时候为 utf8mb4, 但还是不能正常导入数据, 然后就是修改 mysql 的my.ini 文件中默认编码设置了

记得修改完要重启mysql

然后就可以正常导入了, 还要注意一点的就是, 因为我是从别的系统导入数据进自己家数据库, 所以字段长度设计的时候除了小小的问题, 一般出这个错一眼就能看出来的;
如果有同学遇到其他问题欢迎一起进步!!!