使用easyExcel导入excel数据

最新推荐文章于 2024-09-16 16:05:38 发布

长路漫漫，与键作伴

最新推荐文章于 2024-09-16 16:05:38 发布

阅读量1.2k

点赞数

分类专栏： easyExcel 文章标签： excel

本文链接：https://blog.csdn.net/qq_40974235/article/details/109161560

版权

easyExcel 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.导入依赖包

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>1.1.2-beta5</version>
		</dependency>

2.导入的excel数据对应的model模型

package com.iflytek.ssgl.dto;

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

/**
 * @version 1.0
 * @description
 * @create 2020/10/16 10:52
 */
@Data
public class ExcelStayMode extends BaseRowModel {
    @ExcelProperty(value = "学生姓名", index = 0)
    private String stuName;
    @ExcelProperty(value = "性别", index = 1)
    private String sexName;
    @ExcelProperty(value = "年级", index = 2)
    private String gradeName;
    @ExcelProperty(value = "班级", index = 3)
    private String className;
    @ExcelProperty(value = "平台账号", index = 4)
    private String platformNum;
    @ExcelProperty(value = "住宿状态", index = 5)
    private String stayStatusName;
    @ExcelProperty(value = "所属楼栋", index = 6)
    private String buildingName;
    @ExcelProperty(value = "所属楼层", index = 7)
    private String floorName;
    @ExcelProperty(value = "所属宿舍", index = 8)
    private String dormitoryName;
    @ExcelProperty(value = "所属床位", index = 9)
    private String bedName;
}

3.导入工具类

package com.iflytek.ssgl.utils;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.iflytek.ssgl.config.ExcelListener;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * @version 1.0
 * @description
 * @create 2020/10/15 16:45
 */
public class EasyExcelUtil {
    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
        return readExcel(excel, rowModel, 1, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static Map<String,Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
        Map<String,Object> result = new HashMap<>();
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
        //校验表头
        Boolean flag = false;
        if(excelListener.getImportHeads().equals(excelListener.getModelHeads())){
            flag = true;
        }
        result.put("flag", flag);
        result.put("datas", excelListener.getDatas());
        return result;
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    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();
    }

    /**
     * 读取指定sheetName的Excel(多个 sheet)
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            //读取指定名称的sheet
            if(sheet.getSheetName().contains(sheetName)){
                reader.read(sheet);
                break;
            }
        }
        return excelListener.getDatas();
    }

    /**
     * 返回 ExcelReader
     * @param excel 需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel,ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if(filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null, excelListener, false);
        }else{
            return null;
        }
    }

}

4.导入监听类

package com.iflytek.ssgl.config;

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

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @version 1.0
 * @description
 * @create 2020/10/15 16:47
 */
public class ExcelListener extends AnalysisEventListener {
    //自定义用于暂时存储data
    private List<Object> datas = new ArrayList<>();
    //导入表头
    private String importHeads = "";
    //模版表头
    private String modelHeads = "";

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        Integer currentRowNum = analysisContext.getCurrentRowNum();
        //获取导入表头，默认第一行为表头
        if(currentRowNum == 0){
            try {
                Map<String,Object> m = objToMap(o);
                for (Object v : m.values()) {
                    importHeads += String.valueOf(v).trim() + ",";
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else{
            datas.add(o);
        }
    }

    /**
     * 读取完之后的操作
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //获取模版表头
        ExcelHeadProperty ehp = analysisContext.getExcelHeadProperty();
        for(List<String> s : ehp.getHead()){
            modelHeads += s.get(0) + ",";
        }
    }

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

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

    public String getImportHeads() {
        return importHeads;
    }

    public void setImportHeads(String importHeads) {
        this.importHeads = importHeads;
    }

    public String getModelHeads() {
        return modelHeads;
    }

    public void setModelHeads(String modelHeads) {
        this.modelHeads = modelHeads;
    }

    //Object转换为Map
    public  Map<String,Object> objToMap(Object obj) throws Exception{
        Map<String,Object> map = new LinkedHashMap<String, Object>();
        Field[] fields = obj.getClass().getDeclaredFields();
        for(Field field : fields){
            field.setAccessible(true);
            map.put(field.getName(), field.get(obj));
        }
        return map;
    }

}

5.控制层代码

@RequestMapping("/importStuStayInfo")
	@ResponseBody
	public ResultVO importStuStayInfo(@RequestParam("file") MultipartFile file) throws Exception {
		if (file == null) {
			return new ResultVO(ResponseCodeEnum.ERROR.getCode(), "获取excel失败！");
		}
		if (file != null) {
			Map<String, Object> result = EasyExcelUtil.readExcel(file, new ExcelStayMode(), 1);
			Boolean flag = (Boolean) result.get("flag");
			if (flag) {
				List<ExcelStayMode> stuStayList = (List<ExcelStayMode>) result.get("datas");
				//下面是执行批量插入的数据就不粘贴代码了
				return studentService.batchAddStuStayInfo(stuStayList);
			} else {
				System.out.println("表头格式错误");
			}
		}
		return new ResultVO(ResponseCodeEnum.ERROR.getCode(),"导入失败！");
	}