excel导入导出
product.java 实体类
依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
实体类 注意:lombok插件和easyexcel会有冲突 导致插入数据为空
value: 表头名称
index: 列的号, 0表示第一列
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Product extends BaseRowModel implements Serializable {
@ExcelProperty(value = {"订单号"},index = 0)
private Integer id;
@ExcelProperty(value = {"商品名"},index = 1)
private String name;
@ExcelProperty(value = {"商品号"},index = 2)
private Integer parentId;
@ExcelProperty(value = {"类型"},index = 3)
private Integer type;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getParentId() {
return parentId;
}
public void setParentId(Integer parentId) {
this.parentId = parentId;
}
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
}
ServiceImpl业务方法
/**
* 通过http请求响应得到的
* @param response
* @throws IOException
*/
@Override
public void excelExport(HttpServletResponse response) throws IOException {
List<Product> list = productMapper.products();
//文件名称
String fileName = "商品名单";
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
Sheet sheet = new Sheet(1,0,Product.class);
//设置自适应宽度
sheet.setAutoWidth(Boolean.TRUE);
sheet.setSheetName("商品名单");
writer.write(list,sheet);
writer.finish();
out.flush();
response.getOutputStream().close();
out.close();
}
controller类中的方法 直接访问路径即可
/**
* 导出
* @param response
* @throws IOException
*/
@GetMapping("/excel")
public void excelExport(HttpServletResponse response) throws IOException {
productService.excelExport(response);
}
导入
业务逻辑serviceImpl
/**
* 导入
* @param file
* @throws IOException
*/
@Override
public void excelImport(MultipartFile file) throws IOException {
if(!file.getOriginalFilename().equals("商品名单.xls") && !file.getOriginalFilename().equals("商品名单.xlsx") ){
return;
}
InputStream inputStream = new BufferedInputStream(file.getInputStream());
//实例化实现了AnalysisEventListener接口的类
ExcelListener excelListener = new ExcelListener(productMapper);
ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
//读取信息
//第一个1代表sheet1, 第二个1代表从第几行开始读取数据,行号最小值为0
reader.read(new Sheet(1,1,Product.class));
}
监听类 对mapper进行监听
public class ExcelListener extends AnalysisEventListener<Product> {
private List<Product> datas = new ArrayList<>();
private static final int BATCH_COUNT = 3000;
private ProductMapper productMapper;
public ExcelListener(ProductMapper productMapper){
this.productMapper = productMapper;
}
@Override
public void invoke(Product product, AnalysisContext analysisContext) {
//数据存储到datas,供批量处理,或后续自己业务逻辑处理。
datas.add(product);
//达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if(datas.size() >= BATCH_COUNT){
saveData();
// 存储完成清理datas
datas.clear();
}
}
private void saveData() {
System.out.println("save"+datas);
for(Product product : datas){
//dao层添加的数据
productMapper.add(product);
}
}
public List<Product> getDatas() {
return datas;
}
public void setDatas(List<Product> datas) {
this.datas = datas;
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();//确保所有数据都能入库
}
}
controller控制类
/**
* 导入
* @param file
* @return
* @throws IOException
*/
@PostMapping("/import")
public String excelImport(@RequestParam("file")MultipartFile file) throws IOException {
productService.excelImport(file);
return "success";
}