依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
导入成功后,会出现下图的依赖,easyexcel底层用poi的包实现,如果你的项目里面已经引入poi,需要解决版本冲突的问题。
实体
@ExcelProperty(value = "权力编码",index = 0)
private String powerCode;
@ExcelProperty(value = "部门名称",index = 1)
private String orgName;
@ExcelProperty(value = "内设机构名称(必填)",index = 2)
private String innerOrgName;
@ExcelProperty(value = "内设机构职责(必填)",index = 3)
private String innerOrgDuty;
@ExcelProperty(value = "权力名称",index = 4)
private String powerName;
@ExcelProperty(value = "权力类别",index = 5)
private String powerType;
@ExcelProperty(value = "核心数据项(选填)",index = 6)
private String coreData;
ps:ExcelProperty注解中的value值要与excel表头的字段对应。
导入,excel文件转List
List<DutyBean> list = new ArrayList<>();
String path = "D:\\test\\test1.xlsx";
EasyExcel.read(path, DutyBean.class, new AnalysisEventListener<DutyBean>() {
@Override
public void invoke(DutyBean dutyBean, AnalysisContext analysisContext) {
list.add(dutyBean);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}).sheet().doRead();
System.out.println("集合的大小为: "+list.size());
ps:拿到List后,业务逻辑相关和数据库交互代码,自行补充。
导出,List转excel文件
String path = "D:\\test\\test.xlsx";
EasyExcel.write(path, DutyBean.class).sheet("sheet1").doWrite(list);
下面是一个案例
@PostMapping(value = "/importExcel")
public Map<String, Object> importExcel(String type, MultipartFile multipartFile) {
logger.info("----开始导入excel----");
logger.info("-------参数type的值为[" + type + "]------");
Map<String, Object> result = new HashMap<>();
result.put("code", fail); // 默认失败
String name = multipartFile.getName();
String excelPath = "";
File file;
try {
//excelPath = serverPath + name + ".xlsx";
excelPath = devPath + name + ".xlsx";
file = new File(excelPath);
multipartFile.transferTo(file);
} catch (IOException e) {
logger.info(e.getMessage());
result.put("message", "上传文件路径异常,请联系管理员!");
return result;
}
try {
if ("01".equals(type)) {
logger.info("开始导入职责目录");
List<DutyBean> list = new ArrayList<>();
if (!FileUtil.isExcelValid(excelPath, "01")) {
throw new MyException(msg);
}
EasyExcel.read(excelPath, DutyBean.class, new AnalysisEventListener<DutyBean>() {
int num = 2;
@Override
public void invoke(DutyBean dutyBean, AnalysisContext analysisContext) {
String powerCode = dutyBean.getPowerCode();
String orgName = dutyBean.getOrgName();
String powerType = dutyBean.getPowerType();
String innerOrgDuty = dutyBean.getInnerOrgDuty();
if (StringUtils.isEmpty(powerCode)) {
throw new MyException("第" + num + "行数据,职责编码不能为空,请修改!");
}
if (StringUtils.isEmpty(orgName)) {
throw new MyException("第" + num + "行数据,部门名称不能为空,请修改!");
}
if (StringUtils.isEmpty(powerType)) {
throw new MyException("第" + num + "行数据,职责类型不能为空,请修改");
}
num++;
if (StringUtils.isNotEmpty(innerOrgDuty)) {
String[] arr = innerOrgDuty.split("。");
if (arr.length > 1) {
// 从index==1 开始
for (int i = 1; i < arr.length; i++) {
DutyBean bean = new DutyBean(dutyBean);
bean.setPowerCode(dutyBean.getPowerCode() + ("00" + i));
bean.setInnerOrgDuty(arr[i].trim());
list.add(bean);
}
}
}
list.add(dutyBean);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
logger.info("集合的大小为: " + list.size());
if (!list.isEmpty()) {
DataObject[] arr = new DataObject[list.size()];
for (int i = 0; i < list.size(); i++) {
DutyBean bean = list.get(i);
DlDuty dutyOne = DlDuty.FACTORY.create();
String uuid = UUIDUtil.getUUID();
dutyOne.setDmId(uuid);
dutyOne.setPowerCode(bean.getPowerCode());
String orgName = bean.getOrgName();
String orgId = FileUtil.getOrgId(orgName);
dutyOne.setOrgName(orgName);
dutyOne.setOrgId(orgId);
dutyOne.setInnerOrgName(bean.getInnerOrgName() == null ? "" : bean.getInnerOrgName());
dutyOne.setInnerOrgDuty(bean.getInnerOrgDuty() == null ? "" : bean.getInnerOrgDuty());
dutyOne.setPowerName(bean.getPowerName() == null ? "" : bean.getPowerName());
dutyOne.setCoreData(bean.getCoreData() == null ? "" : bean.getCoreData());
String powerTypeNum = FileUtil.getPowerTypeValue(bean.getPowerType());
dutyOne.setPowerType(powerTypeNum);
dutyOne.setStatus("1");
dutyOne.setCreateTime(new Date());
dutyOne.setCreateUser(UserManager.getCurrentUser().getUserName());
arr[i] = dutyOne;
}
DatabaseUtil.insertEntityBatch(BaseConstants.DATASOURCE.DEFAULT_SOURCE, arr);
result.put("code", success);
result.put("message", msg3);
} else {
result.put("message", msg2);
}
}
}).sheet().doRead();
logger.info("职责目录导入结束");
} else if (type.startsWith("02")) {
if (type.equals("02A")) {
logger.info("开始导入信息系统A类");
List<InfoBean> list = new ArrayList<>();
if (!FileUtil.isExcelValid(excelPath, "02A")) {
throw new MyException(msg);
}
EasyExcel.read(excelPath, InfoBean.class, new AnalysisEventListener<InfoBean>() {
int num = 2;
@Override
public void invoke(InfoBean infoBean, AnalysisContext analysisContext) {
String orgName = infoBean.getOrgName();
String appName = infoBean.getAppName();
String appType = infoBean.getAppType();
String tecName = infoBean.getTecName();
String appStatus = infoBean.getAppStatus();
if (StringUtils.isEmpty(orgName)) {
throw new MyException("第" + num + "行数据,业务责任部门不能为空,请修改!");
}
if (StringUtils.isEmpty(appName)) {
throw new MyException("第" + num + "行数据,信息系统名称不能为空,请修改!");
}
if (StringUtils.isEmpty(appType)) {
throw new MyException("第" + num + "行数据,系统类型不能为空,请修改!");
}
if (!"A类".equals(appType)) {
throw new MyException("第" + num + "行数据,系统类型必须为A类,请修改!");
}
if (StringUtils.isEmpty(tecName)) {
throw new MyException("第" + num + "行数据,技术责任部门不能为空,请修改!");
}
if (StringUtils.isEmpty(appStatus)) {
throw new MyException("第" + num + "行数据,系统状态不能为空,请修改!");
}
num++;
list.add(infoBean);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
logger.info("集合的大小为: " + list.size());
if (!list.isEmpty()) {
DataObject[] arr = new DataObject[list.size()];
for (int i = 0; i < list.size(); i++) {
InfoBean bean = list.get(i);
DlInfo infoOne = DlInfo.FACTORY.create();
infoOne.setAppId(UUIDUtil.getUUID());
String appCode = "";
if (i == 0) {
appCode = FileUtil.getInfoSerialNo();
} else {
appCode = String.valueOf(Long.parseLong(arr[i - 1].getString("appCode")) + 1);
}
infoOne.setAppCode(appCode);
String orgName = bean.getOrgName();
String orgId = FileUtil.getOrgId(orgName);
infoOne.setOrgName(orgName);
infoOne.setOrgId(orgId);
infoOne.setAppName(bean.getAppName());
String appType = bean.getAppType();
infoOne.setAppType(FileUtil.getAppTypeValue(appType));
infoOne.setTecName(bean.getTecName());
infoOne.setAppOwner(bean.getAppOwner() == null ? "" : bean.getAppOwner());
infoOne.setAppTel(bean.getAppTel() == null ? "" : bean.getAppTel());
String appStatus = bean.getAppStatus();
infoOne.setAppStatus(FileUtil.getAppStatusValue(appStatus));
infoOne.setStatus("1");
infoOne.setStatusB("2");
infoOne.setAppKey("1");
infoOne.setCreateTime(new Date());
infoOne.setCreateUser(UserManager.getCurrentUser().getUserName());
arr[i] = infoOne;
}
DatabaseUtil.insertEntityBatch(BaseConstants.DATASOURCE.DEFAULT_SOURCE, arr);
result.put("code", success);
result.put("message", msg3);
} else {
result.put("message", msg2);
}
}
}).sheet().doRead();
logger.info("信息系统A类导入结束");
} else {
logger.info("开始导入信息系统B类");
List<InfoBeanB> list = new ArrayList<>();
if (!FileUtil.isExcelValid(excelPath, "02B")) {
throw new MyException(msg);
}
EasyExcel.read(excelPath, InfoBeanB.class, new AnalysisEventListener<InfoBeanB>() {
int num = 2;
@Override
public void invoke(InfoBeanB infoBean, AnalysisContext analysisContext) {
String orgName = infoBean.getOrgName();
String proName = infoBean.getProName();
String tecName = infoBean.getTecName();
String appStatus = infoBean.getAppStatus();
if (StringUtils.isEmpty(orgName)) {
throw new MyException("第" + num + "行数据,业务需求处室不能为空,请修改!");
}
if (StringUtils.isEmpty(proName)) {
throw new MyException("第" + num + "行数据,项目名称不能为空,请修改!");
}
if (StringUtils.isEmpty(tecName)) {
throw new MyException("第" + num + "行数据,技术责任部门不能为空,请修改!");
}
if (StringUtils.isEmpty(appStatus)) {
throw new MyException("第" + num + "行数据,系统状态不能为空,请修改!");
}
num++;
list.add(infoBean);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
logger.info("集合的大小为: " + list.size());
if (!list.isEmpty()) {
DataObject[] arr = new DataObject[list.size()];
for (int i = 0; i < list.size(); i++) {
InfoBeanB bean = list.get(i);
DlInfo infoOne = DlInfo.FACTORY.create();
String proName = bean.getProName();
Map<String, Object> params = new HashMap<>();
params.put("proName", proName);
if (!fileService.isProNameExist(params)) {
infoOne.setAppId(UUIDUtil.getUUID());
infoOne.setStatus("1");
infoOne.setStatusB("1");
infoOne.setAppKey("1");
infoOne.setCreateTime(new Date());
infoOne.setCreateUser(UserManager.getCurrentUser().getUserName());
String orgName = bean.getOrgName();
String orgId = FileUtil.getOrgId(orgName);
infoOne.setOrgName(orgName);
infoOne.setOrgId(orgId);
infoOne.setProName(bean.getProName());
infoOne.setAppName(bean.getAppName() == null ? "" : bean.getAppName());
infoOne.setAppType("2");
infoOne.setTecName(bean.getTecName());
infoOne.setAppOwner(bean.getAppOwner() == null ? "" : bean.getAppOwner());
infoOne.setAppTel(bean.getAppTel() == null ? "" : bean.getAppTel());
String appStatus = bean.getAppStatus();
infoOne.setAppStatus(FileUtil.getAppStatusValue(appStatus));
DatabaseUtil.insertEntity(BaseConstants.DATASOURCE.DEFAULT_SOURCE, infoOne);
} else {
DataObject dataObject = fileService.selectDlInfoByProName(params);
String appId = dataObject.getString("app_id");
infoOne.setAppId(appId);
int num = DatabaseUtil.expandEntity(BaseConstants.DATASOURCE.DEFAULT_SOURCE, infoOne);
if (num > 0) {
infoOne.setUpdateTime(new Date());
infoOne.setUpdateUser(UserManager.getCurrentUser().getUserName());
infoOne.setOrgName(bean.getOrgName());
infoOne.setProName(bean.getProName());
infoOne.setAppName(bean.getAppName() == null ? "" : bean.getAppName());
infoOne.setAppType("2");
infoOne.setTecName(bean.getTecName());
infoOne.setAppOwner(bean.getAppOwner() == null ? "" : bean.getAppOwner());
infoOne.setAppTel(bean.getAppTel() == null ? "" : bean.getAppTel());
String appStatus = bean.getAppStatus();
infoOne.setAppStatus(FileUtil.getAppStatusValue(appStatus));
DatabaseUtil.updateEntity(BaseConstants.DATASOURCE.DEFAULT_SOURCE, infoOne);
}
}
}
DatabaseUtil.insertEntityBatch(BaseConstants.DATASOURCE.DEFAULT_SOURCE, arr);
result.put("code", success);
result.put("message", msg3);
} else {
result.put("message", msg2);
}
}
}).sheet().doRead();
logger.info("信息系统B类导入结束");
}
} else if ("03".equals(type)) {
logger.info("开始导入数据目录");
List<CatalogBean> list = new ArrayList<>();
if (!FileUtil.isExcelValid(excelPath, "03")) {
throw new MyException(msg);
}
EasyExcel.read(excelPath, CatalogBean.class, new AnalysisEventListener<CatalogBean>() {
int num = 2;
@Override
public void invoke(CatalogBean catalogBean, AnalysisContext analysisContext) {
String cataCode = catalogBean.getCataCode();
String cataTitle = catalogBean.getCataTitle();
String orgName = catalogBean.getOrgName();
String cataType = catalogBean.getCataType();
String shareType = catalogBean.getShareType();
String openCondition = catalogBean.getOpenCondition();
String cataReg = catalogBean.getCataReg();
String createTime = catalogBean.getCreateTime();
String sourceTable = catalogBean.getSourceTable();
if (StringUtils.isEmpty(cataCode)) {
throw new MyException("第" + num + "行数据,目录代码不能为空,请修改!");
}
if (StringUtils.isEmpty(cataTitle)) {
throw new MyException("第" + num + "行数据,目录名称不能为空,请修改!");
}
if (StringUtils.isEmpty(orgName)) {
throw new MyException("第" + num + "行数据,部门名称不能为空,请修改!");
}
if (StringUtils.isEmpty(cataType)) {
throw new MyException("第" + num + "行数据,目录类型不能为空,请修改!");
}
if (StringUtils.isEmpty(shareType)) {
throw new MyException("第" + num + "行数据,共享类型不能为空,请修改!");
}
if (StringUtils.isEmpty(openCondition)) {
throw new MyException("第" + num + "行数据,开放类型不能为空,请修改!");
}
if (StringUtils.isEmpty(cataReg)) {
throw new MyException("第" + num + "行数据,在平台注册系统不能为空,请修改!");
}
if (StringUtils.isEmpty(createTime)) {
throw new MyException("第" + num + "行数据,创建时间不能为空,请修改!");
}
if (StringUtils.isEmpty(sourceTable)) {
throw new MyException("第" + num + "行数据,数据资源表名不能为空,请修改!");
}
num++;
list.add(catalogBean);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
logger.info("集合的大小为: " + list.size());
if (!list.isEmpty()) {
DataObject[] arr = new DataObject[list.size()];
for (int i = 0; i < list.size(); i++) {
CatalogBean bean = list.get(i);
DlCatalogNew catalogOne = DlCatalogNew.FACTORY.create();
catalogOne.setCataId(UUIDUtil.getUUID());
catalogOne.setCataCode(bean.getCataCode());
catalogOne.setCataTitle(bean.getCataTitle());
String orgName = bean.getOrgName();
String orgId = FileUtil.getOrgId(orgName);
catalogOne.setOrgName(orgName);
catalogOne.setOrgId(orgId);
String cataType = bean.getCataType();
catalogOne.setCataType(FileUtil.getCataTypeValue(cataType));
String shareType = bean.getShareType();
catalogOne.setShareType(FileUtil.getShareTypeValue(shareType));
String openCondition = bean.getOpenCondition();
catalogOne.setOpenCondition(FileUtil.getOpenConditionValue(openCondition));
catalogOne.setCataReg(bean.getCataReg());
catalogOne.setUpdateCycle(bean.getUpdateCycle() == null ? "" : bean.getUpdateCycle());
catalogOne.setUpdateCycle1(bean.getUpdateCycle1() == null ? "" : bean.getUpdateCycle1());
catalogOne.setCreateTime(DateUtils.dateTime("yyyy-MM-dd HH:mm:ss", bean.getCreateTime()));
catalogOne.setSourceTable(bean.getSourceTable());
catalogOne.setCreateUser(UserManager.getCurrentUser().getUserName());
catalogOne.setOperateStatus(bean.getOperateStatus() == null ? "" : bean.getOperateStatus());
catalogOne.setRemark(bean.getRemark() == null ? "" : bean.getRemark());
catalogOne.setStatus("1");
arr[i] = catalogOne;
}
DatabaseUtil.insertEntityBatch(BaseConstants.DATASOURCE.DEFAULT_SOURCE, arr);
result.put("code", success);
result.put("message", msg3);
} else {
result.put("message", msg2);
}
}
}).sheet().doRead();
logger.info("数据目录导入结束");
}
} catch (Exception e) {
logger.info(e.getMessage());
String message = e.getMessage();
if (message.contains(":")) {
int i = message.indexOf(':');
message = message.substring(i + 1).trim();
}
result.put("message", message);
return result;
}
logger.info("----导入excel结束----");
file.delete();
return result;
}
@GetMapping(value = "/exportExcel")
public void exportExcel(String appCode, String orgName, HttpServletResponse response) {
logger.info("----开始导出excel----");
Map<String, Object> params = new HashMap<>();
params.put("appCode", appCode);
params.put("orgName", orgName);
try {
Object[] objs = DatabaseExt.queryByNamedSql(BaseConstants.DS_NAME.DEFAULT_SOURCE, sqlName, params);
List<InfoBeanB> list = new ArrayList<>();
for (int i = 0; i < objs.length; i++) {
DataObject data = (DataObject) objs[i];
InfoBeanB bean = new InfoBeanB();
bean.setOrgName(data.getString("org_name"));
bean.setProName(data.get("pro_name") == null ? "" : data.get("pro_name").toString());
list.add(bean);
}
//String filePath = "D:\\B类项目清单.xlsx";
//String filePath = "/opt/primeton/eos8/datalink/temp/B类项目清单.xlsx";
String excelName = "B类项目清单";
//EasyExcel.write(filePath, InfoBeanB.class).sheet("sheet1").doWrite(list);
response.reset();
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName.concat(".xlsx"), "UTF-8"));
response.setContentType("application/vnd.ms-excel");
EasyExcel.write(response.getOutputStream(), InfoBeanB.class).sheet("B类项目清单").doWrite(list);
} catch (Exception e) {
logger.info(e + e.getMessage());
}
logger.info("----导出excel结束----");
}
@GetMapping(value = "/exportExcelAll")
public void exportExcelAll(String orgName, HttpServletResponse response) {
logger.info("----开始导出excel----");
Map<String, Object> params = new HashMap<>();
params.put("orgName", orgName);
try {
Object[] objs = DatabaseExt.queryByNamedSql(BaseConstants.DS_NAME.DEFAULT_SOURCE, sqlName, params);
List<InfoBeanB> list = new ArrayList<>();
for (int i = 0; i < objs.length; i++) {
DataObject data = (DataObject) objs[i];
InfoBeanB bean = new InfoBeanB();
bean.setOrgName(data.getString("org_name"));
bean.setProName(data.get("pro_name") == null ? "" : data.get("pro_name").toString());
bean.setAppName(data.get("app_name") == null ? "" : data.get("app_name").toString());
bean.setTecName(data.get("tec_name") == null ? "" : data.get("tec_name").toString());
bean.setAppOwner(data.get("app_owner") == null ? "" : data.get("app_owner").toString());
bean.setAppTel(data.get("app_tel") == null ? "" : data.get("app_tel").toString());
String appStatus = data.get("app_status") == null ? "" : data.get("app_status").toString();
if ("1".equals(appStatus)) {
appStatus = "新建";
} else {
appStatus = "运维";
}
bean.setAppStatus(appStatus);
list.add(bean);
}
//String filePath = "D:\\B类系统导出.xlsx";
// String filePath = "/opt/primeton/eos8/datalink/temp/B类信息系统导出.xlsx";
String excelName = "B类系统导出";
// EasyExcel.write(filePath, InfoBeanB.class).sheet("sheet1").doWrite(list);
response.reset();
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName.concat(".xlsx"), "UTF-8"));
response.setContentType("application/vnd.ms-excel");
EasyExcel.write(response.getOutputStream(), InfoBeanB.class).sheet("B类信息系统导出").doWrite(list);
} catch (Exception e) {
logger.info(e + e.getMessage());
}
logger.info("----导出excel结束----");
}