一、导入
1、依赖
<!--excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2、excle相关实体类
package com.example.licong.demo.entity.excle;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@HeadRowHeight(value = 40)
public class UserExcel {
/**
* 用户名
*/
@ExcelProperty(value = "用户名", index = 0)
@ColumnWidth(value = 15)
private String username;
/**
* 显示名称
*/
@ExcelProperty(value = "昵称", index = 1)
@ColumnWidth(value = 15)
private String nickname;
/**
* 密码
*/
@ExcelProperty(value = "密码", index = 2)
@ColumnWidth(value = 20)
private String password;
/**
* 邮箱
*/
@ExcelProperty(value = "邮箱", index = 3)
@ColumnWidth(value = 20)
private String email;
/**
* 头像
*/
@ExcelProperty(value = "头像", index = 4)
@ColumnWidth(value = 20)
private String avatar;
/**
* 0 正常
* 1 禁用
*/
@ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
@ColumnWidth(value = 20)
private String status;
/**
* 注册时间 yyyy-MM-dd HH:mm:ss格式
*/
@ExcelProperty(value = "注册时间", index = 6)
@ColumnWidth(value = 20)
private String createdTime;
}
3、监听器
package com.example.licong.demo.controller.excle;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.licong.demo.entity.excle.UserExcel;
import org.testng.collections.Lists;
import java.util.List;
public class UserEasyExcelListener extends AnalysisEventListener<UserExcel> {
List datas = Lists.newArrayList();
/**
* 每解析一行,回调该方法
*
* @param goodsInfo
* @param analysisContext
*/
@Override
public void invoke(UserExcel goodsInfo, AnalysisContext analysisContext) {
//这里可以做数据处理
datas.add(goodsInfo);
}
/**
* 解析完全部回调
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List getDatas() {
return datas;
}
public void setDatas(List datas) {
this.datas = datas;
}
}
4、controller
@PostMapping("/insert")
public String insert(@RequestBody MultipartFile file) throws IOException {
UserEasyExcelListener goodsInfoEasyExcelListener = new UserEasyExcelListener();
EasyExcel.read(file.getInputStream(), ExhibitsInfoExcel.class, goodsInfoEasyExcelListener).sheet().doRead();
List <UserExcel> datas = goodsInfoEasyExcelListener.getDatas();
if (org.apache.commons.collections4.CollectionUtils.isEmpty(datas)){
return "数据为空";
}
// goodsInforService.addGoodsInfoDtos(datas);//这里为数据库新增操作
return "SUCCESS";
}
二、导出模板或导出数据
1、依赖
<!--excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2、excle相关实体类
package com.example.licong.demo.entity.excle;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@HeadRowHeight(value = 40)
public class UserExcel {
/**
* 用户名
*/
@ExcelProperty(value = "用户名", index = 0)
@ColumnWidth(value = 15)
private String username;
/**
* 显示名称
*/
@ExcelProperty(value = "昵称", index = 1)
@ColumnWidth(value = 15)
private String nickname;
/**
* 密码
*/
@ExcelProperty(value = "密码", index = 2)
@ColumnWidth(value = 20)
private String password;
/**
* 邮箱
*/
@ExcelProperty(value = "邮箱", index = 3)
@ColumnWidth(value = 20)
private String email;
/**
* 头像
*/
@ExcelProperty(value = "头像", index = 4)
@ColumnWidth(value = 20)
private String avatar;
/**
* 0 正常
* 1 禁用
*/
@ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
@ColumnWidth(value = 20)
private String status;
/**
* 注册时间 yyyy-MM-dd HH:mm:ss格式
*/
@ExcelProperty(value = "注册时间", index = 6)
@ColumnWidth(value = 20)
private String createdTime;
}
3、导出工具类
package com.example.licong.demo.controller.excle;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {
/**
* 导出
* @param response
* @param data
* @param fileName
* @param sheetName
* @param clazz
* @throws Exception
*/
public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
return response.getOutputStream();
}
}
4、controller
@GetMapping("/excel/template")
public void downloadTemplate(HttpServletResponse response) {
String fileName = "导入用户模板";
String sheetName = "导入用户模板";
List<UserExcel> userList = new ArrayList<>();
//这里是随便给的数据,导出时从数据库查询数据就行了
userList.add(new UserExcel("saysky", "言曌", "123456", "847064370@qq.com", "http://xxx.com/xx.jpg", "0", "2017-12-31 12:13:14"));
userList.add(new UserExcel("qiqi", "琪琪", "123456", "666666@qq.com", "http://xxx.com/xx.jpg", "0", "2018-5-20 13:14:00"));
try {
ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
} catch (Exception e) {
e.printStackTrace();
}
}
完事。
==============================================================分割线====================================================================
导出模板也可以 用这个方式,只是现在遇到一个问题,由于项目中有poi也有easyexcle导致(线上)导出模板报错(本地是可以的),所以暂时用上面那种导出方法,后面改了的话更新文章
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
String fileName = "模板名称";
//通知浏览器以附件的形式下载处理,设置返回头要注意文件名有中文
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
ServletOutputStream out = response.getOutputStream();
List<UserExcel> areaInfoExcels = new ArrayList <>();
EasyExcelFactory.write(out,UserExcel.class).sheet("模板名称").doWrite(areaInfoExcels);
out.flush();
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)