Java - Execl自定义导入、导出

1.需求：问卷星答 下图框出区域，为用户自定义字段问题及答案

2.采用技术EasyExcel

模板所在位置如下

/**
 * 导出模板
 *
 * @param response
 */
@Override
public void exportTemplate(HttpServletResponse response) throws IOException {

	ClassPathResource classPathResource = new ClassPathResource("templates/会员满意度调研.xlsx");
	StreamUtils.copy(classPathResource.getInputStream(),response.getOutputStream());
}

3.监听Listener继承AnalysisEventListener

导入模板数据时，所用的监听器
package com.huatek.frame.modules.survey.service.impl;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.Map;

@Slf4j
public class SurveyImportMessageListener extends AnalysisEventListener<Object> {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    private ArrayList<Object> datas = new ArrayList();
    /**
     * 表头
     */
    private Map<Integer, String> headMap;
    public SurveyImportMessageListener() {
    }

public void invoke(Object data, AnalysisContext analysisContext) {
    this.datas.add(data);
}


public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
    this.headMap = headMap;
}

public void doAfterAllAnalysed(AnalysisContext context) {
}

public ArrayList<Object> getDatas() {
    return this.datas;
}
public Map<Integer, String> getHead() {
    return this.headMap;
}
}

4.导入问卷

/**
 * 导入问卷
 *
 * @param file
 * @param localUser
 * @return
 */
@Override
public List<String> importSurvey(MultipartFile file, String surveyMainId,UserAuthProfileInfo localUser) {
	List<String> errorList = new ArrayList<>();
	try {
		SurveyImportMessageListener listener = new SurveyImportMessageListener();
		EasyExcel.read(file.getInputStream(), listener).sheet().doRead();
		ArrayList datas = listener.getDatas();
		Map<Integer, String> head = listener.getHead();
		LinkedList<SurveyProblemAnswerVO> surveyProblemAnswerVOS = new LinkedList();
		LinkedList<SurveyProblemAnswerItemVO> surveyProblemAnswerItemVOS = new LinkedList();
		head.forEach((key,value)->{
			//存储问题
			if(key >= 6){
				SurveyProblemAnswerItemVO item = new SurveyProblemAnswerItemVO();
				item.setProblem(value);
				item.setSortBy(key);
				surveyProblemAnswerItemVOS.add(item);
			}
		});
		for (Object data : datas) {
			SurveyProblemAnswerVO survey = new SurveyProblemAnswerVO();
			List<SurveyProblemAnswerItemVO> items = BeanListUtils.copyListProperties(surveyProblemAnswerItemVOS, SurveyProblemAnswerItemVO::new);
			survey.setSurveyMainId(surveyMainId);
			((LinkedHashMap<Integer, String>) data).forEach((key,value)->{
				if(key == 1) survey.setSubmitTime(DateUtil.parse(value,"yyyy/MM/dd HH:mm:ss"));
				if(key == 2) survey.setUseTime(value);
				if(key == 3) survey.setSource(value);
				if(key == 4) survey.setSourceInfo(value);
				if(key == 5) survey.setSourceIp(value);
				//存储问题答案
				if(key >= 6){
					SurveyProblemAnswerItemVO item = items.get(key-6);
					item.setAnswer(value);
				}
			});
			survey.setItemVOList(items);
			surveyProblemAnswerVOS.add(survey);
		}
		log.info("导入问答信息:{}", JSONArray.toJSONString(surveyProblemAnswerVOS));
		for (SurveyProblemAnswerVO surveyProblemAnswerVO : surveyProblemAnswerVOS) {
			SurveyMain surveyMain = surveyMainMapper.selectById(surveyMainId);
			if(surveyMain.getImportTime() == null){
				surveyMain.setImportTime(new Date());
				HttpServletRequest request = RequestHolder.getHttpServletRequest();
				JSONObject jsonObject = securityUser.currentUser(securityUser.getToken(request));
				surveyMain.setImportBy(jsonObject.get("userName").toString());
				surveyMainMapper.updateById(surveyMain);
			}

			SurveyProblemAnswer surveyProblemAnswer = new SurveyProblemAnswer();
			BeanUtils.copyProperties(surveyProblemAnswerVO,surveyProblemAnswer);
			surveyProblemAnswerService.saveOrUpdate(surveyProblemAnswer);

			List<SurveyProblemAnswerItemVO> itemVOList = surveyProblemAnswerVO.getItemVOList();
			itemVOList.forEach(item->item.setSurveyId(surveyProblemAnswer.getId()));
			List<SurveyProblemAnswerItem> surveyProblemAnswerItems = BeanListUtils.copyListProperties(itemVOList, SurveyProblemAnswerItem::new);
			surveyProblemAnswerItemService.saveOrUpdateBatch(surveyProblemAnswerItems);
		}

	} catch(Exception e){
		log.error("导入问卷异常",e);
	}
	return errorList;
}

5.根据模板导出数据

@Override
public void exportExcel(SurveyProblemAnswerVO vo, HttpServletResponse response) throws IOException {
	List<SurveyProblemAnswer> surveyProblemAnswers = surveyProblemAnswerMapper.selectList(
			new LambdaQueryWrapper<SurveyProblemAnswer>()
			.eq(SurveyProblemAnswer::getSurveyMainId, vo.getId()));

	List<String> surveyIds = surveyProblemAnswers.stream().map(item -> item.getId()).collect(Collectors.toList());
	List<SurveyProblemAnswerItem> surveyProblemAnswerItems = surveyProblemAnswerItemMapper.selectList(
			new LambdaQueryWrapper<SurveyProblemAnswerItem>()
					.in(SurveyProblemAnswerItem::getSurveyId, surveyIds));

	LinkedList<List<String>> head = new LinkedList<>();
	head.add(CollectionUtil.newArrayList("序号"));
	head.add(CollectionUtil.newArrayList( "提交答卷时间"));
	head.add(CollectionUtil.newArrayList( "所用时间"));
	head.add(CollectionUtil.newArrayList( "来源"));
	head.add(CollectionUtil.newArrayList( "来源详情"));
	head.add(CollectionUtil.newArrayList( "来源IP"));
	List<SurveyProblemAnswerItem>  problemItems = surveyProblemAnswerItems.stream()
			.filter(item -> item.getSurveyId().equals(surveyProblemAnswers.get(0).getId()))
			.sorted(Comparator.comparing(SurveyProblemAnswerItem::getSortBy))
			.collect(Collectors.toList());
	for (SurveyProblemAnswerItem item : problemItems) {
		head.add(CollectionUtil.newArrayList( item.getProblem()));
	}


	LinkedList<List<Object>> data = new LinkedList<>();
	int count = 0;
	for (SurveyProblemAnswer surveyProblemAnswer : surveyProblemAnswers) {
		List<Object> tmp = new ArrayList<>();
		tmp.add(++count);
		tmp.add(surveyProblemAnswer.getSubmitTime() == null ? "" : surveyProblemAnswer.getSubmitTime());
		tmp.add(surveyProblemAnswer.getUseTime() == null ? "":surveyProblemAnswer.getUseTime());
		tmp.add(surveyProblemAnswer.getSource() == null ? "":surveyProblemAnswer.getSource());
		tmp.add(surveyProblemAnswer.getSourceInfo() == null ? "":surveyProblemAnswer.getSourceInfo());
		tmp.add(surveyProblemAnswer.getSourceIp() == null ? "":surveyProblemAnswer.getSourceIp());
		List<SurveyProblemAnswerItem> items = surveyProblemAnswerItems.stream()
				.filter(item -> item.getSurveyId().equals(surveyProblemAnswer.getId()))
				.sorted(Comparator.comparing(SurveyProblemAnswerItem::getSortBy))
				.collect(Collectors.toList());
		for (SurveyProblemAnswerItem item : items) {
			if(StringUtils.isNotEmpty(item.getAnswer())){
				tmp.add(item.getAnswer());
			}else{
				tmp.add("");
			}
		}
		data.add(tmp);
	}
	EasyExcel.write(response.getOutputStream(),null)
			.head(head)
			.autoCloseStream(false)
			.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("会员满意度调研模板")
			.doWrite(data);
}

以上均为实现类，具体接口可参照定义

结果如下：