package com.code.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.util.Date;
/**
* @author Herat
*/
@Data
@TableName("t_user")
public class User {
@TableId(value = "id", type = IdType.AUTO)
@ExcelProperty(value = "ID")
@ColumnWidth(value = 10)
private Integer id;
@TableField(value = "name", insertStrategy = FieldStrategy.NOT_NULL)
@ExcelProperty(value = "用户名")
@ColumnWidth(value = 25)
private String name;
@TableField(value = "age")
@ExcelProperty(value = "年龄")
@ColumnWidth(value = 10)
private Integer age;
@TableField(value = "create_time", updateStrategy = FieldStrategy.NEVER)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
@ExcelProperty(value = "创建时间")
@ColumnWidth(value = 40)
private Date createTime;
}
package com.code.controller;
import com.code.service.ExcelService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* @author Herat
*/
@RestController
@RequestMapping("/excel")
public class ExcelHuToolController {
@Resource
private ExcelService excelService;
@GetMapping("export")
public void export(HttpServletResponse response) throws IOException {
// excelService.export(response);
excelService.exportExcel(response);
}
@PostMapping("upload")
public String upload(MultipartFile file) throws IOException {
System.out.println(file.getName());
excelService.imports(file);
return "SUCCESS";
}
}
package com.code.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.code.entity.User;
import com.code.service.UserService;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* 不能由spring进行管理
* @author Herat
*/
@Slf4j
public class DataListener extends AnalysisEventListener<User> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<User> cachedDataList = new ArrayList<>(BATCH_COUNT);
private UserService userService;
public DataListener(UserService userService) {
this.userService = userService;
}
@Override
public void invoke(User data, AnalysisContext context) {
cachedDataList.add(data);
if(cachedDataList.size() > BATCH_COUNT) {
userService.saveBatch(cachedDataList);
log.info("保存数据");
cachedDataList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
userService.saveBatch(cachedDataList);
log.info("import success");
}
}
package com.code.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.code.entity.User;
import com.code.listener.DataListener;
import com.code.listener.UserDataListener;
import com.code.mapper.db1.UserMapper;
import org.apache.commons.collections.MapUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
/**
* @author Herat
*/
@Service
public class ExcelService {
@Resource
private UserMapper userMapper;
@Resource
private UserService userService;
public void exportExcel(HttpServletResponse response) throws IOException {
List<User> users = userMapper.selectList(new QueryWrapper<User>());
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("中奖名单", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), User.class).autoCloseStream(Boolean.FALSE)
.sheet(1)
.doWrite(users);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
public void imports(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new DataListener(userService)).sheet().doRead();
}
}
package com.code.mapper.db1;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.code.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
public interface UserMapper extends BaseMapper<User> {
}
package com.code.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.code.entity.User;
public interface UserService extends IService<User> {
}
package com.code.service.imp;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.code.entity.User;
import com.code.mapper.db1.UserMapper;
import com.code.service.UserService;
import org.springframework.stereotype.Service;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
@Service
public class UserServiceImp extends ServiceImpl<UserMapper, User> implements UserService {
}