投稿
  1. 首页
  2. 随笔

EasyExcel 进行文件导入和文件导出

尾行3攻略 • 2022-12-11 • 随笔 • 阅读 20

EasyExcel 进行文件导入和文件导出,第1张

EasyExcel 进行文件导入和文件导出

在在多数的项目中都有对excel 文件进行数据导入，这里我们采用阿里开源框架进行excel 表格导入和导出

在使用easyexcel 时可以借鉴官方网站

前官网：Alibaba Easy Excel - 简单、省内存的Java解析Excel工具 | 读Excel 简单、省内存的Java解析Excel工具https://alibaba-easyexcel.github.io/quickstart/read.html

但是这个网址的说明文档不进行更新了。当然官方也给出了更新技术的最新地址：EasyExcel · 语雀EasyExcel是一个基于Java的简单、省内存的读...https://www.yuque.com/easyexcel/doc/easyexcel

在这里进行简单的文件导出和文件导入，只写后端的代码！

文件导入：

maven 坐标：

		
		
			com.alibaba
			easyexcel
			2.2.6
		
		
		
			org.apache.poi
			poi
			3.17
		
		
		
			org.apache.poi
			poi-ooxml
			3.17

写一个导入的工具类：

package gcloud.mof.util;

import com.alibaba.excel.EasyExcel;
import gcloud.core.FileProps;
import gcloud.mof.dto.ExcelResultDto;
import gcloud.mof.dto.importBasMofDivDto;
import gcloud.mof.protobuf.importExcelFile;
import gcloud.mof.util.listener.BasMofDivListener;

import java.io.File;

public class Excelimport {
    private Excelimport() {
    }

    public static String getFilePath(importExcelFile request, FileProps fileProps) {
        String filePropsPath = fileProps.getPath();
        if (filePropsPath == null) {
            return null;
        }
        String filePath = filePropsPath + File.separator + request.getFilePath();
        String extString = filePath.substring(filePath.lastIndexOf("."));
        if (".xls".equals(extString) || ".xlsx".equals(extString)) {
            return filePath;
        }
        return null;
    }
    public static ExcelResultDto importBasMofDiv(importExcelFile request, FileProps fileProps) {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        //文件路径
        String fileName = getFilePath(request,fileProps);
        ExcelResultDto dto  = new ExcelResultDto();
        try {
            // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
            EasyExcel.read(fileName, importBasMofDivDto.class, new BasMofDivListener()).sheet().headRowNumber(2).doRead();
            dto.setCode("200");
        } catch (Exception e) {
            e.printStackTrace();
            dto.setCode("500");
            dto.setMsg(e.getMessage());
        }
        return dto;
    }
}

导入工具中会补充监听：

 

package gcloud.mof.util.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.fastjson.JSON;
import gcloud.core.IocFactory;
import gcloud.mof.MofFactory;
import gcloud.mof.dto.importBasMofDivDto;
import gcloud.mof.entity.BasMofDivEntity;
import gcloud.mof.service.BasMofDivRead;
import gcloud.mof.service.BasMofDivWrite;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class BasMofDivListener extends AnalysisEventListener {
    private static final Logger LOGGER = LoggerFactory.getLogger(BasMofDivListener.class);
    
    private static final int BATCH_COUNT = 3000;
    List list = new ArrayList<>();
    
    private BasMofDivRead mofDivRead;
    private BasMofDivWrite mofDivWrite;
    public BasMofDivListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        mofDivWrite = IocFactory.getIoc().getBean(MofFactory.class).getBasMofDivWrite();
        mofDivRead = IocFactory.getIoc().getBean(MofFactory.class).getBasMofDivRead();
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        LOGGER.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            LOGGER.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex());
            throw new Exception("第"+excelDataConvertException.getRowIndex()+"行，第"+excelDataConvertException.getColumnIndex()+"列数据解析异常");
        }
    }

    
    @Override
    public void invoke(importBasMofDivDto data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSonString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        List listProvince = list.stream().filter(e -> StringUtils.isBlank(e.getCounty()) && StringUtils.isBlank(e.getCity())).collect(Collectors.toList());
        List listCity = list.stream().filter(e -> StringUtils.isBlank(e.getCounty()) && StringUtils.isNotBlank(e.getCity())).map(e->{
            for (importBasMofDivDto dto:listProvince) {
                if(StringUtils.equals(e.getProvince(),dto.getProvince())){
                    e.setParentId(dto.getMofDivId());
                }
            }
            return e;
        }).collect(Collectors.toList());
        List listCounty = list.stream().filter(e -> StringUtils.isNotBlank(e.getCounty()) && StringUtils.isNotBlank(e.getCity())).map(e->{
            for (importBasMofDivDto dto:listCity) {
                if(StringUtils.equals(e.getProvince(),dto.getProvince())&&StringUtils.equals(e.getCity(),dto.getCity())){
                    e.setParentId(dto.getMofDivId());
                }
            }
            return e;
        }).collect(Collectors.toList());
        List dtoList = new ArrayList<>();
        dtoList.addAll(listProvince);
        dtoList.addAll(listCity);
        dtoList.addAll(listCounty);
        List collect = dtoList.stream().map(e -> {
            BasMofDivEntity entity = new BasMofDivEntity();
            String county = e.getCounty();
            String city = e.getCity();
            String province = e.getProvince();
            int level = 0;
            String parentId = e.getParentId();
            if(StringUtils.isNotBlank(county)){
                if(parentId==null){
                    BasMofDivEntity mofDiv = mofDivRead.findByMofDivName(city);
                    parentId = mofDiv.getMofDivId();
                }
                level = 3;
                entity.setMofDivName(county);
            }else{
                level = 1;
                entity.setMofDivName(province);
                if(StringUtils.isNotBlank(city)){
                    entity.setMofDivName(city);
                    if(parentId==null){
                        BasMofDivEntity mofDiv = mofDivRead.findByMofDivName(province);
                        parentId = mofDiv.getMofDivId();
                    }
                    level = 2;
                }
            }
            entity.setMofDivId(e.getMofDivId());
            entity.setParentId(parentId);
            entity.setMofDivCode(e.getMofDivCode());
            Map flagMap = new HashMap<>();
            flagMap.put("否",false);
            flagMap.put("是",true);
            entity.setLevel(level);
            entity.setAdmDivCode(e.getAdmDivCode());
            entity.setAdmDivCustom(e.getAdmDivCustom());
            entity.setMergeMofDivCode(e.getMergeMofDivCode());
            entity.setDirUnderCountyFlag(flagMap.get(e.getDirUnderCountyFlag()));
            entity.setFundUnderCountyFlag(flagMap.get(e.getFundUnderCountyFlag()));
            entity.setDeepPoorCountyFlag(flagMap.get(e.getDeepPoorCountyFlag()));
            entity.setNationalPoorCountyFlag(flagMap.get(e.getNationalPoorCountyFlag()));
            entity.setProvincialPoorCountyFlag(flagMap.get(e.getProvincialPoorCountyFlag()));
            entity.setPoorTaihangFlag(flagMap.get(e.getPoorTaihangFlag()));
            entity.setPoorLuliangFlag(flagMap.get(e.getPoorLuliangFlag()));
            entity.setTwoCountyFlag(flagMap.get(e.getTwoCountyFlag()));
            entity.setHelpNonPoorCountyFlag(flagMap.get(e.getHelpNonPoorCountyFlag()));
            entity.setEnjoyWesternPolicyFlag(flagMap.get(e.getEnjoyWesternPolicyFlag()));
            entity.setIsEnabled(e.getIsEnabled());
            entity.setUpdateTime(e.getUpdateTime());
            entity.setStartDate(e.getStartDate());
            entity.setEndDate(e.getEndDate());

            return entity;
        }).collect(Collectors.toList());
        mofDivWrite.saveArray(collect);
        LOGGER.info("存储数据库成功！");
    }
}

当然这里也可以采用注解的方式进行动态获取，需要和表格的头信息一一对应。

导出数据：

这里数据导出要求自定义选择列，所以我采用枚举类和反射机制进行属性对比。

枚举类：

package gcloud.mof.enums;

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

public enum baseMofDivHeaderEnum {

    MOF_DIV_CODE("mofDivCode","一体化系统市县财政区划信息","财政区划编码"),
    MOF_DIV_NAME("mofDivName","一体化系统市县财政区划信息","财政区划名称"),
    LEVEL("level","一体化系统市县财政区划信息","级别"),
    ADM_DIV_CODE("admDivCode","一体化系统市县财政区划信息","行政区划编码"),
    ADM_DIV_CUSTOM("admDivCustom","一体化系统市县财政区划信息","行政区划自编码"),
    MERGE_MOF_DIV_CODE("mergeMofDivCode","一体化系统市县财政区划信息","归并级次"),
    DIR_UNDER_COUNTY_FLAG("dirUnderCountyFlag","一体化系统市县财政区划信息","是否体制管理型省直管县"),
    FUND_UNDER_COUNTY_FLAG("fundUnderCountyFlag","一体化系统市县财政区划信息","是否资金管理型省直管县"),
    DEEP_POOR_COUNTY_FLAG("deepPoorCountyFlag","一体化系统市县财政区划信息","是否深度贫困县"),
    NATIONAL_POOR_COUNTY_FLAG("nationalPoorCountyFlag","一体化系统市县财政区划信息","贫困县-是否国定贫困县"),
    PROVINCIAL_POOR_COUNTY_FLAG("provincialPoorCountyFlag","一体化系统市县财政区划信息","贫困县-是否省定贫困县"),
    POOR_TAIHANG_FLAG("poorTaihangFlag","一体化系统市县财政区划信息","集中连片特困地区-是否燕山-太行山区"),
    POOR_LULIANG_FLAG("poorLuliangFlag","一体化系统市县财政区划信息","集中连片特困地区-是否吕梁山区"),
    TWO_COUNTY_FLAG("twoCountyFlag","一体化系统市县财政区划信息","是否两区县"),
    HELP_NON_POOR_COUNTY_FLAG("helpNonPoorCountyFlag","一体化系统市县财政区划信息","是否有扶贫任务的非贫困县"),
    ENJOY_WESTERN_POLICY_FLAG("enjoyWesternPolicyFlag","一体化系统市县财政区划信息","是否享受西部大开发政策的县"),
    IS_ENABLED("isEnabled","一体化系统市县财政区划信息","状态"),
    UPDATE_TIME("updateTime","一体化系统市县财政区划信息","更新时间"),
    START_DATE("startDate","一体化系统市县财政区划信息","启用时间"),
    END_DATE("endDate","一体化系统市县财政区划信息","停用时间");


    baseMofDivHeaderEnum(String code, String... text) {
        this.code = code;
        this.text = new ArrayList<>();
        for (String s:text) {
            this.text.add(s);
        }
    }

    public static baseMofDivHeaderEnum getEnumByCode(String code) {
        baseMofDivHeaderEnum[] values = baseMofDivHeaderEnum.values();
        for (baseMofDivHeaderEnum value : values) {
            if (code.equals(value.getCode())) {
                return value;
            }
        }
        return null;
    }

    public static List getEnumListByCodes(List codes) {
        // 头数据
        List head = new ArrayList();
        for (int i = 0; i < codes.size(); i++) {
            baseMofDivHeaderEnum headEnum = baseMofDivHeaderEnum.getEnumByCode(codes.get(i));
            if (head != null) {
                head.add(headEnum);
            }
        }
        return head;
    }

    private String code;
    private List text;

    public String getCode() {
        return code;
    }

    public List getText() {
        return text;
    }
    
}

我创建了一个接口需要进行实现，可能麻烦点：

package gcloud.mof.util.inter;

import java.util.List;

public interface ExportExcelHeader {
    
    public List getHeadText(String code);

    
    public  String  getFileName();

    
    public  List  getFieldList();

    
    public  List  getData();
}

因为导出时我们会很多地方用到

导出的工具类：

package gcloud.mof.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import gcloud.core.FileProps;
import gcloud.core.util.FilePathUtil;
import gcloud.mof.util.handler.CustomSheetWriteHandler;
import gcloud.mof.util.inter.ExportExcelHeader;
import org.apache.commons.logging.LogFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

public class ExcelExport {
    private static final Logger log = LoggerFactory.getLogger(ExcelExport.class);

    private ExcelExport() {
    }
    private static String outFileSrc(String fileName) {
        try {
            String fileSrc = FilePathUtil.createNewTempFile(fileName+".xls","excel");
            return FilePathUtil.realPath(fileSrc);
        } catch (Exception e) {
            LogFactory.getLog(ExcelExport.class).error("",e);
        }
        return null;
    }

    public static List> getDataList(List list, List fieldList,Boolean isParent) {
        List  headList = fieldList.stream().filter(string -> !string.trim().isEmpty()).collect(Collectors.toList());
        return list.stream().map(e -> {
            Class cls = e.getClass();
            if(isParent){
                cls = e.getClass().getSuperclass();
            }
            List valueList = new ArrayList<>();
            for (String name : headList) {
                try {
                    Field field = cls.getDeclaredField(name);
                    field.setAccessible(true);
                    Object text = null;
                    if(field.get(e)!=null) {
                         text = field.get(e);
                    }
                    if(text instanceof Date){
                        text = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(text);
                    }
                    valueList.add(text);
                } catch (NoSuchFieldException | IllegalAccessException noSuchFieldException) {
                    noSuchFieldException.printStackTrace();
                }
            }
            return valueList;
        }).collect(Collectors.toList());
    }

    public static String exportExcel(ExportExcelHeader excelHeader, Boolean isParent) {
        // 文件名字
        String fileName = excelHeader.getFileName();
        // 指定文件地址
        String filePath = outFileSrc(fileName);
        // 导出数据对应类中的字段名
        List fieldList = excelHeader.getFieldList();
        // 头数据
        List> head = new ArrayList>();
        for (int i = 0; i < fieldList.size(); i++) {
            List headText = excelHeader.getHeadText(fieldList.get(i));
            if(head!=null){
                head.add(headText);
            }
        }
        String title = head.get(0).get(0);
        List head0  = new ArrayList<>();
        head0.add(title);
        head0.add("序号");
        head.add(0,head0);
        List list = excelHeader.getData();
        // 内容数据
        List> data = getDataList(list, fieldList,isParent);
        AtomicInteger i = new AtomicInteger(1);
        data = data.stream().map(e -> {
            e.add(0, i.getAndIncrement());
            return e;
        }).collect(Collectors.toList());

        EasyExcel.write(filePath).head(head)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet(fileName)
                .doWrite(data);
        return filePath;
    }
    private static List> headbaseMofDivBill() {
        String[] heads = {"省","市","县区","财政区划编码","区划编码（国家编码）","区划编码（自编码）","归并级次", "是否体制管理型省直管县","是否资金管理型省直管县","是否深度贫困县","贫困县-是否国定贫困县","贫困县-是否省定贫困县", "集中连片特困地区-是否燕山-太行山区","集中连片特困地区-是否吕梁山区","是否两区县","是否有扶贫任务的非贫困县","是否享受西部大开发政策的县"};
        List headList = Arrays.asList(heads);
        List> list = new ArrayList>();
        for (int i = 0; i < heads.length; i++) {
            List head = new ArrayList();
            head.add("一体化系统市县财政区划信息");
            head.add(headList.get(i));
            list.add(head);
        }
        return list;
    }
    public static String downloadBasMofDivTemplate(FileProps fileProps) {
        String filePropsPath = fileProps.getPath();
        if (filePropsPath == null) {
            return null;
        }
        String fileName = filePropsPath + File.separator + "财政区划信息导入模板.xlsx";

        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new CustomSheetWriteHandler())
                .head(headbaseMofDivBill()).sheet("财政区划信息导入模板").doWrite(new ArrayList());
        return fileName;
    }

} 

导出实现方法：
 

  @Override
    public void exportBasMofDiv(ExportBasMofDivReq request, StreamObserver responseObserver) {
        try {
            List list = mofFactory.getBasMofDivRead().findListByReq(request.getReq());
            List finalList = new ArrayList<>();
            // 最小的值
            Integer level = list.stream().map(BasMofDivEntity::getLevel).min(Integer::compareTo).get();
            List pList = list.stream().filter(e -> e.getLevel() == level).collect(Collectors.toList());

            for (BasMofDivEntity p:pList) {
                finalList.add(p);
                List clist = list.stream().filter(e -> e.getParentId().equals(p.getMofDivId())).collect(Collectors.toList());
                for (BasMofDivEntity c:clist) {
                    finalList.add(c);
                    List xlist = list.stream().filter(e -> e.getParentId().equals(c.getMofDivId())).collect(Collectors.toList());
                    for (BasMofDivEntity x:xlist) {
                        finalList.add(x);
                    }
                }
            }

            Map  stateMap = new HashMap<>();
            stateMap.put(false,"停用");
            stateMap.put(true,"启用");

            Map  flagMap = new HashMap<>();
            flagMap.put(false,"否");
            flagMap.put(true,"是");

            List resultList = finalList.stream().map(e->{
                BasMofDivEntityDto dto = new BasMofDivEntityDto();
                StringBuffer empty = new StringBuffer(">");
                for (int i = 0; i < e.getLevel(); i++) {
                    empty.append("  ");
                }
                dto.setMofDivCode(e.getMofDivCode());
                dto.setMofDivName(empty+e.getMofDivName());
                dto.setLevel(e.getLevel());
                dto.setAdmDivCode(e.getAdmDivCode());
                dto.setAdmDivCustom(e.getAdmDivCustom());
                dto.setMergeMofDivCode(e.getMergeMofDivCode());
                dto.setDirUnderCountyFlag(flagMap.get(e.getDirUnderCountyFlag()));
                dto.setFundUnderCountyFlag(flagMap.get(e.getFundUnderCountyFlag()));
                dto.setDeepPoorCountyFlag(flagMap.get(e.getDeepPoorCountyFlag()));
                dto.setNationalPoorCountyFlag(flagMap.get(e.getNationalPoorCountyFlag()));
                dto.setProvincialPoorCountyFlag(flagMap.get(e.getProvincialPoorCountyFlag()));
                dto.setPoorTaihangFlag(flagMap.get(e.getPoorTaihangFlag()));
                dto.setPoorLuliangFlag(flagMap.get(e.getPoorLuliangFlag()));
                dto.setTwoCountyFlag(flagMap.get(e.getTwoCountyFlag()));
                dto.setHelpNonPoorCountyFlag(flagMap.get(e.getHelpNonPoorCountyFlag()));
                dto.setEnjoyWesternPolicyFlag(flagMap.get(e.getEnjoyWesternPolicyFlag()));
                dto.setIsEnabled(stateMap.get(e.getIsEnabled()));
                dto.setUpdateTime(e.getUpdateTime());
                dto.setStartDate(e.getStartDate());
                dto.setEndDate(e.getEndDate());
                return dto;
            }).collect(Collectors.toList());
            ExportExcelHeader exportExcelHeader = new ExportExcelHeader() {
                @Override
                public List getHeadText(String code) {
                    baseMofDivHeaderEnum[] values = baseMofDivHeaderEnum.values();
                    for (baseMofDivHeaderEnum value:values) {
                        if(code.equals(value.getCode())){
                            return value.getText();
                        }
                    }
                    return null;
                }
                @Override
                public String getFileName() {
                    return request.getFileName();
                }

                @Override
                public List getFieldList() {
                    return request.getFieldList();
                }

                @Override
                public List getData() {
                    return resultList;
                }
            };
            String distFile = ExcelExport.exportExcel(exportExcelHeader,false);
            GcloudFileUtil.download(responseObserver,distFile);
        } catch (Exception e) {
            log.error("历史数据导出失败："+e);
            e.printStackTrace();
        }
    }
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5437910.html

					

				
								

										
										
										
										
										
									

								

					
						
							
						 赞
						(0)
					
					

						
							 打赏
						
							
								微信扫一扫 微信扫一扫
							
							
								支付宝扫一扫 支付宝扫一扫
							
						
					

				

				

					

						

							
								尾行3攻略
								尾行3攻略
								一级用户组
							
						

						

							

								
									
										
									
									0
								
								
									
										
									
									0
								
							

							
							

								
									
										
									
								
							

						

					

				

			
			
            

            	            

            
			

								

					
					

         				
							
								
							 上一篇
						
						2022-12-11
					

				

												

					
					

         				
							下一篇 
           						
							
						
						2022-12-11
					

				

							


			
			

				

					

						发表评论
					

										

						
						

							

								登录后才能评论
							

							
						

					

									

								
 评论列表（0条）

				
    
									

				
											

		

	

	





	


	

		

			

			

				
				

					

						Copyright © 2022 内存溢出 版权所有
						
							湘ICP备2022025235号						
						Powered by
						
							outofmemory.cn
						
					
					

				

			

			

				

					
					
						
						
					
					
						
					
				

			

		

	




























    
    

    

        
            保存