在在多数的项目中都有对excel 文件进行数据导入，这里我们采用阿里开源框架进行excel 表格导入和导出
在使用easyexcel 时可以借鉴官方网站
https://alibaba-easyexcel.github.io/quickstart/read.html但是这个网址的说明文档不进行更新了。当然官方也给出了更新技术的最新地址：EasyExcel · 语雀EasyExcel是一个基于Java的简单、省内存的读...
在这里进行简单的文件导出和文件导入，只写后端的代码！
文件导入：
maven 坐标：
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<!-- 2003 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- 2007 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>写一个导入的工具类：
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<ImportBasMofDivDto> {
private static final Logger LOGGER = LoggerFactory.getLogger(BasMofDivListener.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List<ImportBasMofDivDto> list = new ArrayList<>();
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
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()+"列数据解析异常");
}
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@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();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
List<ImportBasMofDivDto> listProvince = list.stream().filter(e -> StringUtils.isBlank(e.getCounty()) && StringUtils.isBlank(e.getCity())).collect(Collectors.toList());
List<ImportBasMofDivDto> 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<ImportBasMofDivDto> 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<ImportBasMofDivDto> dtoList = new ArrayList<>();
dtoList.addAll(listProvince);
dtoList.addAll(listCity);
dtoList.addAll(listCounty);
List<BasMofDivEntity> 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<String, Boolean> 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<BaseMofDivHeaderEnum> getEnumListByCodes(List<String> codes) {
// 头数据
List<BaseMofDivHeaderEnum> head = new ArrayList<BaseMofDivHeaderEnum>();
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<String> text;
public String getCode() {
return code;
}
public List<String> getText() {
return text;
}
}
我创建了一个接口需要进行实现，可能麻烦点：
package gcloud.mof.util.inter;
import java.util.List;
public interface ExportExcelHeader {
/**
* code 对应枚举类中的code,返回枚举类中的name（头名称）
* @param code
* @return
*/
public List<String> getHeadText(String code);
/**
* 文件名字
* @return
*/
public String getFileName();
/**
* 导出数据对应类中的字段名
* @return
*/
public List<String> getFieldList();
/**
* 导出的数据
* @return
*/
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<List<Object>> getDataList(List<?> list, List<String> fieldList,Boolean isParent) {
List<String> 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<Object> 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<String> fieldList = excelHeader.getFieldList();
// 头数据
List<List<String>> head = new ArrayList<List<String>>();
for (int i = 0; i < fieldList.size(); i++) {
List<String> headText = excelHeader.getHeadText(fieldList.get(i));
if(head!=null){
head.add(headText);
}
}
String title = head.get(0).get(0);
List<String> head0 = new ArrayList<>();
head0.add(title);
head0.add("序号");
head.add(0,head0);
List<?> list = excelHeader.getData();
// 内容数据
List<List<Object>> 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<List<String>> headBaseMofDivBill() {
String[] heads = {"省","市","县区","财政区划编码","区划编码（国家编码）","区划编码（自编码）","归并级次", "是否体制管理型省直管县","是否资金管理型省直管县","是否深度贫困县","贫困县-是否国定贫困县","贫困县-是否省定贫困县", "集中连片特困地区-是否燕山-太行山区","集中连片特困地区-是否吕梁山区","是否两区县","是否有扶贫任务的非贫困县","是否享受西部大开发政策的县"};
List<String> headList = Arrays.asList(heads);
List<List<String>> list = new ArrayList<List<String>>();
for (int i = 0; i < heads.length; i++) {
List<String> head = new ArrayList<String>();
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<FileDownloadResp> responseObserver) {
try {
List<BasMofDivEntity> list = mofFactory.getBasMofDivRead().findListByReq(request.getReq());
List<BasMofDivEntity> finalList = new ArrayList<>();
// 最小的值
Integer level = list.stream().map(BasMofDivEntity::getLevel).min(Integer::compareTo).get();
List<BasMofDivEntity> pList = list.stream().filter(e -> e.getLevel() == level).collect(Collectors.toList());
for (BasMofDivEntity p:pList) {
finalList.add(p);
List<BasMofDivEntity> clist = list.stream().filter(e -> e.getParentId().equals(p.getMofDivId())).collect(Collectors.toList());
for (BasMofDivEntity c:clist) {
finalList.add(c);
List<BasMofDivEntity> xlist = list.stream().filter(e -> e.getParentId().equals(c.getMofDivId())).collect(Collectors.toList());
for (BasMofDivEntity x:xlist) {
finalList.add(x);
}
}
}
Map<Boolean, String> stateMap = new HashMap<>();
stateMap.put(false,"停用");
stateMap.put(true,"启用");
Map<Boolean, String> flagMap = new HashMap<>();
flagMap.put(false,"否");
flagMap.put(true,"是");
List<BasMofDivEntityDto> 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<String> 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<String> 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();
}
}
1675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
