"easyexcel"
共 142 条结果
Springboot整合easyExcel
Springboot整合easyExcel介绍Entity实体类下载模板上传Excel文件上传Excel前端页面测试介绍平时工作中，我们经常都会用到excel文件上传或者下载的问题，比如将表数据导出为excel表格或者进行数据导入数据库等，本篇我们就介绍一下easyExcel进行操作Excel进行web的上传或者下载。快速开始<!-- 引入easyexcel依赖--><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.1</version></dependency>Entity实体类上传Excel的每个表格对应一行的一个cell，使用注解@ExcelProperty进行标注标题/** * 列信息实体类 */@Data@TableName("ColumnEntity")public class ColumnEntity { @TableId(value = "column_id", type = IdType.AUTO) @ExcelProperty(value="序号") private String columnId; //列名 @ExcelProperty(value="列名") @TableField("column_name") private String columnName; //列注释 @ExcelProperty(value="列注释") @TableField("column_desc") private String ColumnDesc; //类型 @ExcelProperty(value="数据类型") @TableField("column_type") private String columnType; //长度 @ExcelProperty(value="长度") @TableField("column_len") private String columnLen; //精度 @ExcelProperty(value="精度") @TableField("precision") private String precision; //是否主键 Y true 都默认是主键 @ExcelProperty(value="是否主键") @TableField("isPrimaryKey") private String isPrimaryKey; //是否允许为空 Y true 不允许为空 @ExcelProperty(value="是否允许为空") @TableField("isNotNull") private String isNotNull; //是否索引列，方便进行生成建表语句的时候进行生成索引 @ExcelProperty(value="是否索引列") @TableField("isIdxCol") private String isIdxCol;}下载模板 /** * 下载excel模板 */ @GetMapping("/downLoadExcelTemplate") public void downLoadExcelTemplate(HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman 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"); //空数据 List<ColumnEntity> columnEntityList = null; EasyExcel.write(response.getOutputStream(), ColumnEntity.class).sheet("创建实体类模板").doWrite(columnEntityList); }上传Excel文件上传Excel我们需要监听读取sheet，EasyExcel会自动解析文件，我们只需要对监听的行进行处理就ok。监听类/*** * easyexcel监听类 */@Slf4jpublic class ColumnListener implements ReadListener { /** * 这个每一条数据解析都会来调用 * * @param o * @param analysisContext */ @Override public void invoke(Object o, AnalysisContext analysisContext) { System.out.println(o.toString()); } /** * 所有数据解析完成了 都会来调用 * @param analysisContext */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { // log.info("所有数据解析完成！"); }}上传Excel /** * 文件上传 * <p>1. 创建excel对应的实体对象 参照{@link } * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link } * <p>3. 直接读即可 */ @PostMapping("/uploadExcel") public String importData(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), ColumnEntity.class, new ColumnListener()).sheet().doRead(); return "success"; }前端页面前端主要使用elementui的上传文件组件进行上传。代码如下：<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <title>excel上传下载管理</title></head><!--script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script--><!--引入静态的路径--><script type="text/javascript" src="js/vue.min.js"></script><!--script src="https://unpkg.com/element-ui/lib/index.js"></script--><script type="text/javascript" src="js/index.js"></script><!-- 引入样式 --><!--link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css"--><link rel="stylesheet" href="css/index.css"><!-- 引入组件库 --><!--引入通信框架--><!--script src="https://unpkg.com/axios/dist/axios.min.js"></script--><script type="text/javascript" src="js/axios.min.js"></script><body><div id="app"> <h1 style="background-color: #409EFF;text-align: center;">EasyExcel上传下载管理</h1> <div> <el-form :inline="true" class="demo-form-inline" label-position="center" > <el-form-item> <el-button type="primary" icon="el-icon-download" @click="downloadTemplate">下载excel模板</el-button> <el-button type="primary" icon="el-icon-upload2" @click="importData">上传excel</el-button> </el-form-item> </el-form> <!---导入弹框--> <el-dialog title="上传Excel:上传前先下载模板进行格式调整" :visible.sync="dialogImportVisible" width="480px"> <!--选择框--> <el-form label-position="center" label-width="170px"> <el-form-item label="文件"> <el-upload :multiple="false" :on-success="onUploadSuccess" :action="'http://localhost:8017/uploadExcel'" class="upload-demo"> <el-button size="small" type="primary">点击上传</el-button> <div slot="tip" class="el-upload__tip">只能上传xls文件，且不超过500kb</div> </el-upload> </el-form-item> </el-form> <div slot="footer" class="dialog-footer" style="text-align: center"> <el-button type="info" @click="dialogImportVisible = false">取消</el-button> </div> </el-dialog> </div></div></body></html><script> new Vue({ el: "#app", //数据 data() { return { dialogImportVisible:false //导入上传文本框 } }, //创建之前 created() { }, //初始化加载数据 mounted() { }, methods: { //导入数据 importData(){ this.dialogImportVisible = true }, //下载模板 downloadTemplate(){ window.open("http://localhost:8017/downLoadExcelTemplate") }, //加载成功 onUploadSuccess(response, file) { this.$message.info('上传成功') this.dialogImportVisible = false } } });</script><style scoped></style>测试后台监听数据：
SpringBoot项目：RedisTemplate实现轻量级消息队列
背景 公司项目有个需求, 前端上传excel文件, 后端读取数据、处理数据、返回错误数据, 最简单的方式同步处理, 客户端上传文件后一直阻塞等待响应, 但用户体验无疑很差, 处理数据可能十分耗时, 没人愿意傻等, 由于项目暂未使用ActiveMQ等消息队列中间件, 而redis的lpush和rpop很适合作为一种轻量级的消息队列实现, 所以用它完成此次功能开发一、本文涉及知识点excel文件读写--阿里easyexcel sdk文件上传、下载--腾讯云对象存储远程服务调用--restTemplate生产者、消费者--redisTemplate leftPush和rightPop操作异步处理数据--Executors线程池读取网络文件流--HttpClient自定义注解实现用户身份认证--JWT token认证, 拦截器拦截标注有@LoginRequired注解的请求入口当然, Java实现咯 涉及的知识点比较多, 每一个知识点都可以作为专题进行学习分析, 本文将完整实现呈现出来, 后期拆分与小伙伴分享学习二、项目目录结构项目结构说明: 数据库DAO层放到另一个模块了, 不是本文重点三、主要maven依赖easyexcelJWTredis腾讯cos四、流程用户上传文件将文件存储到腾讯cos将上传后的文件id及上传记录保存到数据库redis生产一条导入消息, 即保存文件id到redis请求结束, 返回"处理中"状态redis消费消息读取cos文件, 异步处理数据将错误数据以excel形式上传至cos, 以供用户下载, 并更新处理状态为"处理完成"客户端轮询查询处理状态, 并可以下载错误文件结束五、实现效果上传文件上传文件数据库导入记录数据库导入记录导入的数据导入的数据下载错误文件下载错误文件错误数据提示错误数据提示查询导入记录查询导入记录六、代码实现1、导入excel控制层@LoginRequired
@RequestMapping(value = "doImport", method = RequestMethod.POST)
public JsonResponse doImport(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
PLUser user = getUser(request);
return orderImportService.doImport(file, user.getId());
}2、service层@Override
public JsonResponse doImport(MultipartFile file, Integer userId) {
if (null == file || file.isEmpty()) {
throw new ServiceException("文件不能为空");
}
String filename = file.getOriginalFilename();
if (!checkFileSuffix(filename)) {
throw new ServiceException("当前仅支持xlsx格式的excel");
}
// 存储文件
String fileId = saveToOss(file);
if (StringUtils.isBlank(fileId)) {
throw new ServiceException("文件上传失败, 请稍后重试");
}
// 保存记录到数据库
saveRecordToDB(userId, fileId, filename);
// 生产一条订单导入消息
redisProducer.produce(RedisKey.orderImportKey, fileId);
return JsonResponse.ok("导入成功, 处理中...");
}
/**
* 校验文件格式
* @param fileName
* @return
*/
private static boolean checkFileSuffix(String fileName) {
if (StringUtils.isBlank(fileName) || fileName.lastIndexOf(".") <= 0) {
return false;
}
int pointIndex = fileName.lastIndexOf(".");
String suffix = fileName.substring(pointIndex, fileName.length()).toLowerCase();
if (".xlsx".equals(suffix)) {
return true;
}
return false;
}
/**
* 将文件存储到腾讯OSS
* @param file
* @return
*/
private String saveToOss(MultipartFile file) {
InputStream ins = null;
try {
ins = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
String fileId;
try {
String originalFilename = file.getOriginalFilename();
File f = new File(originalFilename);
inputStreamToFile(ins, f);
FileSystemResource resource = new FileSystemResource(f);
MultiValueMap<String, Object> param = new LinkedMultiValueMap<>();
param.add("file", resource);
ResponseResult responseResult = restTemplate.postForObject(txOssUploadUrl, param, ResponseResult.class);
fileId = (String) responseResult.getData();
} catch (Exception e) {
fileId = null;
}
return fileId;
}3、redis生产者4、redis消费者@Service
public class RedisConsumer {
@Autowired
public RedisTemplate redisTemplate;
@Value("${txOssFileUrl}")
private String txOssFileUrl;
@Value("${txOssUploadUrl}")
private String txOssUploadUrl;
@PostConstruct
public void init() {
processOrderImport();
}
/**
* 处理订单导入
*/
private void processOrderImport() {
ExecutorService executorService = Executors.newCachedThreadPool();
executorService.execute(() -> {
while (true) {
Object object = redisTemplate.opsForList().rightPop(RedisKey.orderImportKey, 1, TimeUnit.SECONDS);
if (null == object) {
continue;
}
String msg = JSON.toJSONString(object);
executorService.execute(new OrderImportTask(msg, txOssFileUrl, txOssUploadUrl));
}
});
}
}5、处理任务线程类public class OrderImportTask implements Runnable {
public OrderImportTask(String msg, String txOssFileUrl, String txOssUploadUrl) {
this.msg = msg;
this.txOssFileUrl = txOssFileUrl;
this.txOssUploadUrl = txOssUploadUrl;
}
}
/**
* 注入bean
*/
private void autowireBean() {
this.restTemplate = BeanContext.getApplicationContext().getBean(RestTemplate.class);
this.transactionTemplate = BeanContext.getApplicationContext().getBean(TransactionTemplate.class);
this.orderImportService = BeanContext.getApplicationContext().getBean(OrderImportService.class);
}
@Override
public void run() {
// 注入bean
autowireBean();
JSONObject jsonObject = JSON.parseObject(msg);
String fileId = jsonObject.getString("fileId");
MultiValueMap<String, Object> param = new LinkedMultiValueMap<>();
param.add("id", fileId);
ResponseResult responseResult = restTemplate.postForObject(txOssFileUrl, param, ResponseResult.class);
String fileUrl = (String) responseResult.getData();
if (StringUtils.isBlank(fileUrl)) {
return;
}
InputStream inputStream = HttpClientUtil.readFileFromURL(fileUrl);
List<Object> list = ExcelUtil.read(inputStream);
process(list, fileId);
}
/**
* 将文件上传至oss
* @param file
* @return
*/
private String saveToOss(File file) {
String fileId;
try {
FileSystemResource resource = new FileSystemResource(file);
MultiValueMap<String, Object> param = new LinkedMultiValueMap<>();
param.add("file", resource);
ResponseResult responseResult = restTemplate.postForObject(txOssUploadUrl, param, ResponseResult.class);
fileId = (String) responseResult.getData();
} catch (Exception e) {
fileId = null;
}
return fileId;
}说明: 处理数据的业务逻辑代码就不用贴了6、上传文件到cos@RequestMapping("/txOssUpload")
@ResponseBody
public ResponseResult txOssUpload(@RequestParam("file") MultipartFile file) throws UnsupportedEncodingException {
if (null == file || file.isEmpty()) {
return ResponseResult.fail("文件不能为空");
}
String originalFilename = file.getOriginalFilename();
originalFilename = MimeUtility.decodeText(originalFilename);// 解决中文乱码问题
String contentType = getContentType(originalFilename);
String key;
InputStream ins = null;
File f = null;
try {
ins = file.getInputStream();
f = new File(originalFilename);
inputStreamToFile(ins, f);
key = iFileStorageClient.txOssUpload(new FileInputStream(f), originalFilename, contentType);
} catch (Exception e) {
return ResponseResult.fail(e.getMessage());
} finally {
if (null != ins) {
try {
ins.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (f.exists()) {// 删除临时文件
f.delete();
}
}
return ResponseResult.ok(key);
}
public static void inputStreamToFile(InputStream ins,File file) {
try {
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public String txOssUpload(FileInputStream inputStream, String key, String contentType) {
key = Uuid.getUuid() + "-" + key;
OSSUtil.txOssUpload(inputStream, key, contentType);
try {
if (null != inputStream) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
return key;
}
public static void txOssUpload(FileInputStream inputStream, String key, String contentType) {
ObjectMetadata objectMetadata = new ObjectMetadata();
try{
int length = inputStream.available();
objectMetadata.setContentLength(length);
}catch (Exception e){
logger.info(e.getMessage());
}
objectMetadata.setContentType(contentType);
cosclient.putObject(txbucketName, key, inputStream, objectMetadata);
}7、下载文件/**
* 腾讯云文件下载
* @param response
* @param id
* @return
*/
@RequestMapping("/txOssDownload")
public Object txOssDownload(HttpServletResponse response, String id) {
COSObjectInputStream cosObjectInputStream = iFileStorageClient.txOssDownload(id, response);
String contentType = getContentType(id);
FileUtil.txOssDownload(response, contentType, cosObjectInputStream, id);
return null;
}
public static void txOssDownload(HttpServletResponse response, String contentType, InputStream fileStream, String fileName) {
FileOutputStream fos = null;
response.reset();
OutputStream os = null;
try {
response.setContentType(contentType + "; charset=utf-8");
if(!contentType.equals(PlConstans.FileContentType.image)){
try {
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
} catch (UnsupportedEncodingException e) {
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
logger.error("encoding file name failed", e);
}
}
os = response.getOutputStream();
byte[] b = new byte[1024 * 1024];
int len;
while ((len = fileStream.read(b)) > 0) {
os.write(b, 0, len);
os.flush();
try {
if(fos != null) {
fos.write(b, 0, len);
fos.flush();
}
} catch (Exception e) {
logger.error(e.getMessage());
}
}
} catch (IOException e) {
IOUtils.closeQuietly(fos);
fos = null;
} finally {
IOUtils.closeQuietly(os);
IOUtils.closeQuietly(fileStream);
if(fos != null) {
IOUtils.closeQuietly(fos);
}
}
}8、读取网络文件流/**
* 读取网络文件流
* @param url
* @return
*/
public static InputStream readFileFromURL(String url) {
if (StringUtils.isBlank(url)) {
return null;
}
HttpClient httpClient = new DefaultHttpClient();
HttpGet methodGet = new HttpGet(url);
try {
HttpResponse response = httpClient.execute(methodGet);
if (response.getStatusLine().getStatusCode() == 200) {
HttpEntity entity = response.getEntity();
return entity.getContent();
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}9、ExcelUtil/**
* 读excel
* @param inputStream 文件输入流
* @return list集合
*/
public static List<Object> read(InputStream inputStream) {
return EasyExcelFactory.read(inputStream, new Sheet(1, 1));
}
/**
* 写excel
* @param data list数据
* @param clazz
* @param saveFilePath 文件保存路径
* @throws IOException
*/
public static void write(List<? extends BaseRowModel> data, Class<? extends BaseRowModel> clazz, String saveFilePath) throws IOException {
File tempFile = new File(saveFilePath);
OutputStream out = new FileOutputStream(tempFile);
ExcelWriter writer = EasyExcelFactory.getWriter(out);
Sheet sheet = new Sheet(1, 3, clazz, "Sheet1", null);
writer.write(data, sheet);
writer.finish();
out.close();
}说明: 至此, 整个流程算是完整了, 下面将其他知识点代码也贴出来参考七、其他1、@LoginRequired注解/**
* 在需要登录验证的Controller的方法上使用此注解
*/
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface LoginRequired {
}2、MyControllerAdvice@ControllerAdvice
public class MyControllerAdvice {
@ResponseBody
@ExceptionHandler(TokenValidationException.class)
public JsonResponse tokenValidationExceptionHandler() {
return JsonResponse.loginInvalid();
}
@ResponseBody
@ExceptionHandler(ServiceException.class)
public JsonResponse serviceExceptionHandler(ServiceException se) {
return JsonResponse.fail(se.getMsg());
}
@ResponseBody
@ExceptionHandler(Exception.class)
public JsonResponse exceptionHandler(Exception e) {
e.printStackTrace();
return JsonResponse.fail(e.getMessage());
}
}3、AuthenticationInterceptorpublic class AuthenticationInterceptor implements HandlerInterceptor {
private static final String CURRENT_USER = "user";
@Autowired
private UserService userService;
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
// 如果不是映射到方法直接通过
if (!(handler instanceof HandlerMethod)) {
return true;
}
HandlerMethod handlerMethod = (HandlerMethod) handler;
Method method = handlerMethod.getMethod();
// 判断接口是否有@LoginRequired注解, 有则需要登录
LoginRequired methodAnnotation = method.getAnnotation(LoginRequired.class);
if (methodAnnotation != null) {
// 验证token
Integer userId = JwtUtil.verifyToken(request);
PLUser plUser = userService.selectByPrimaryKey(userId);
if (null == plUser) {
throw new RuntimeException("用户不存在，请重新登录");
}
request.setAttribute(CURRENT_USER, plUser);
return true;
}
return true;
}
@Override
public void postHandle(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse, Object o, ModelAndView modelAndView) throws Exception {
}
@Override
public void afterCompletion(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse, Object o, Exception e) throws Exception {
}
}4、JwtUtilpublic static final long EXPIRATION_TIME = 2592_000_000L; // 有效期30天
public static final String SECRET = "pl_token_secret";
public static final String HEADER = "token";
public static final String USER_ID = "userId";
/**
* 根据userId生成token
* @param userId
* @return
*/
public static String generateToken(String userId) {
HashMap<String, Object> map = new HashMap<>();
map.put(USER_ID, userId);
String jwt = Jwts.builder()
.setClaims(map)
.setExpiration(new Date(System.currentTimeMillis() + EXPIRATION_TIME))
.signWith(SignatureAlgorithm.HS512, SECRET)
.compact();
return jwt;
}
/**
* 验证token
* @param request
* @return 验证通过返回userId
*/
public static Integer verifyToken(HttpServletRequest request) {
String token = request.getHeader(HEADER);
if (token != null) {
try {
Map<String, Object> body = Jwts.parser()
.setSigningKey(SECRET)
.parseClaimsJws(token)
.getBody();
for (Map.Entry entry : body.entrySet()) {
Object key = entry.getKey();
Object value = entry.getValue();
if (key.toString().equals(USER_ID)) {
return Integer.valueOf(value.toString());// userId
}
}
return null;
} catch (Exception e) {
logger.error(e.getMessage());
throw new TokenValidationException("unauthorized");
}
} else {
throw new TokenValidationException("missing token");
}
}本文就是愿天堂没有BUG给大家分享的内容，大家有收获的话可以分享下，想学习更多的话可以到微信公众号里找我，我等你哦。
SpringBoot集成EasyExcel
[toc]
EasyExcel介绍
一个插件或者说是工具，用来导入导出excel/csv
快、相对省内存
阿里开源
重写 Apache POI
官网：https://easyexcel.opensource.alibaba.com/
github ：https://github.com/alibaba/easyexcel
优缺点
速度快，对于大批量数据导入可忽略性能问题。实测百万级别的表，一次性导入20W数据仅需20s，而解析excel数据到javalist仅用了4s。
自带逐行导入功能，能够实现类似分页导入的功能
使用方便，例如封装了EasyExcel.write，传一个HttpServletResponse 对象进去可以直接给调用者响应一个excel
不适合用来处理花里胡哨的、复杂的excel模板，可拓展性相对ApachePoi有阉割
示例代码
功能较为简单，主要是演示实际开发中如何编写代码。
excel示例
业务流程
客户端请求服务端，传递一个文件（excel或者csv），服务的读取文件中的数据并保存到数据库，对于文件中的错误数据或者库里已经存在重复id的数据将不会导入，错误数据会写入一个新的excel文件，并在请求响应的时候反馈给客户端。
pom
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
全量读取excel
顾名思义就是一次性将excel中的所有数据读取到java list中
case1-controller
需要全局增加异常捕获和统一响应
@Slf4j
@AllArgsConstructor
@RestController
@RequestMapping("/import")
public class ImportController {
private ImportService importService;
/**
* 方式一：
* 1：采用名称的方式映射表格列和实体
*/
@PostMapping("/case1")
public void importCase1(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws IOException {
FileTypeEnum fileType = FileTypeEnum.getFileType(file.getOriginalFilename());
List<DataImportCase1Dto> data = new ArrayList<>();
try {
if (FileTypeEnum.CSV.equals(fileType)) {
//CSV格式需要显示指定ExcelType
data = EasyExcel.read(file.getInputStream()).excelType(ExcelTypeEnum.CSV).head(DataImportCase1Dto.class).sheet().doReadSync();
} else if (FileTypeEnum.EXCEL.equals(fileType)) {
data = EasyExcel.read(file.getInputStream()).head(DataImportCase1Dto.class).sheet().doReadSync();
}
} catch (Exception e) {
log.error("importCase1-error,e={},stackTrace={}", e.getMessage(), e.getStackTrace());
throw new RuntimeException("导入失败");
}
importService.importCase1(data, response);
}
}
case1-service
@Slf4j
@Service
public class ImportService {
/**
*
* @param data
* @param response
* @throws IOException
*/
public void importCase1(List<DataImportCase1Dto> data, HttpServletResponse response) throws IOException {
Assert.notEmpty(data, "参数不可为空");
//todo:
//1:校验data中的数据是否重复，例如编号是否重复
//2：校验data中的编号是否与库里数据重复
//3：校验业务数据是否符合要求
//4：执行入库
//5:收集不符合要求的数据，反馈给调用者一个excel
List<DataImportResultDto> errorList = new ArrayList<>();
errorList = data.stream().map(x -> {
DataImportResultDto error = new DataImportResultDto();
BeanUtils.copyProperties(x, error);
error.setExplain("这里是错误描述");
return error;
}).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(errorList)) {
EasyExcel.write(response.getOutputStream())
//.head(DataImportResultDto.head())
.head(DataImportResultDto.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("错误数据")
.doWrite(errorList);
}
}
}
case1-导入dto
通过 com.alibaba.excel.annotation.ExcelProperty 注解将excel列映射到dto属性
建议所有的字段都使用string类型，这种做法增强了功能的可用性。例如：如果用户将double 类型的数据填写了中文，如果dto直接定义double，那么将发生异常，并且其他数据也无法进行导入操作。虽然数据不符合业务要求，但起码在excel转list的时候程序不应报异常，对于业务数据的校验可以放到service中去做。
dto不可加链式访问注解 @Accessors(chain = true)，加了之后读取到的数据都是null，读写反射对象用到了Cglib，加了此注解后无法被Cglib读取
若excel中的列名和dto中的属性名一样，无需指定ExcelProperty，如本示例中的address属性
@Data
public class DataImportCase1Dto {
@ExcelProperty("编号")
private String id;
@ExcelProperty("名称")
private String name;
private String address;
@ExcelProperty("类型")
private String type;
@ExcelProperty("宽")
private String width;
@ExcelProperty("高")
private String height;
@ExcelProperty("来源")
private String source;
@ExcelProperty("备注")
private String remark;
}
case1-错误数据dto
在导入完毕之后，将错误数据收集起来，放在一个新的excel中，响应给调用方
@Data
public class DataImportResultDto {
@ExcelProperty("编号")
private String id;
@ExcelProperty("名称")
private String name;
private String address;
@ExcelProperty("类型")
private String type;
@ExcelProperty("宽")
private String width;
@ExcelProperty("高")
private String height;
@ExcelProperty("来源")
private String source;
@ExcelProperty("备注")
private String remark;
@ExcelProperty("错误描述")
private String explain;
public static List<List<String>> head() {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("编号");
List<String> head1 = new ArrayList<String>();
head1.add("名称");
List<String> head2 = new ArrayList<String>();
head2.add("地址");
List<String> head3 = new ArrayList<String>();
head3.add("类型");
List<String> head4 = new ArrayList<String>();
head4.add("宽");
List<String> head5 = new ArrayList<String>();
head5.add("高");
List<String> head6 = new ArrayList<String>();
head6.add("来源");
List<String> head7 = new ArrayList<String>();
head7.add("备注");
List<String> head8 = new ArrayList<String>();
head8.add("错误描述");
list.add(head0);
list.add(head1);
list.add(head2);
list.add(head3);
list.add(head4);
list.add(head5);
list.add(head6);
list.add(head7);
list.add(head8);
return list;
}
}
FileTypeEnum
@AllArgsConstructor
@Slf4j
@Getter
public enum FileTypeEnum {
/**
* csv
*/
CSV("csv", "csv"),
/**
* excel
*/
EXCEL("xlsx", "excel"),
;
private final String type;
private final String describe;
public static FileTypeEnum getFileType(String fileName) {
if (StringUtils.hasText(fileName)) {
String[] array = fileName.split("\\.");
if (array.length > 0) {
for (FileTypeEnum item : FileTypeEnum.values()) {
if (item.getType().equals(array[array.length - 1])) {
return item;
}
}
}
}
return null;
}
}
逐行读取excel
一行一行的读取excel，官方给出的示例中引入了一个缓存的概念，每读一行将结果缓存起来，当缓存数据量达到指定阀值的时候批量操作一次入库
逐行读取的优点有：
尽可能避免了OOM问题
分批之后对数据库更加友好，要知道实际生产中往往不是简单的导入，还有各种数据校验，而数据的校验会涉及很多db的读写操作
能够更加精细化的监控和管理整个导入过程
case2-controller
@PostMapping("/case2")
public void importCase2(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws IOException {
FileTypeEnum fileType = FileTypeEnum.getFileType(file.getOriginalFilename());
if (FileTypeEnum.CSV.equals(fileType)) {
//CSV格式需要显示指定ExcelType
EasyExcel.read(file.getInputStream(), new DemoDataListener(importService, response)).excelType(ExcelTypeEnum.CSV).head(DataImportCase2Dto.class).sheet().doReadSync();
} else if (FileTypeEnum.EXCEL.equals(fileType)) {
EasyExcel.read(file.getInputStream(), new DemoDataListener(importService, response)).head(DataImportCase2Dto.class).sheet().doReadSync();
}
}
case2-listener
有个很重要的点 DemoDataListener 不能被spring管理，也就是说不能添加 @Service 类似的注解。要每次读取excel都要new,然后里面用到spring容器中的组件可以通过构造方法传进去。如service等可以先在controller中注入，然后在 new listener 的时候通过构造函数传递进去。
@Slf4j
public class DemoDataListener implements ReadListener<DataImportCase2Dto> {
/**
* 每隔 2 条存储数据库，实际使用中可以1000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 2;
/**
* 缓存的数据
*/
private List<DataImportCase2Dto> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private ImportService importService;
private HttpServletResponse response;
public DemoDataListener(ImportService service, HttpServletResponse res) {
this.importService = service;
this.response = res;
}
/**
* 每解析一条数据都会回调到此方法
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context analysis context
*/
@Override
public void invoke(DataImportCase2Dto data, AnalysisContext context) {
log.debug("DemoDataListener-invoke-data={}", JSON.toJSONString(data));
//数据校验
Boolean verifyResult = importService.verifyImportData(data);
if (verifyResult) {
cachedDataList.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
importService.importCase2(cachedDataList);
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
} else {
log.debug("数据校验不通过，本行数据将放到错误数据excel中响应给调用者，即将开始解析下一行");
}
}
/**
* 所有数据解析完成会回调此方法
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
importService.importCase2(cachedDataList);
//响应错误数据excel
importService.responseErrorData(response);
log.debug("DemoDataListener-所有数据解析完成");
}
@Override
public void onException(Exception exception, AnalysisContext context) {
log.error("DemoDataListener-error,本行解析发生异常，但是可以继续解析下一行,msg={},stackTrace={}", exception.getMessage(), exception.getStackTrace());
//如果是某一个单元格的转换异常 能获取到具体行号
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
}
}
}
case2-service
@Slf4j
@AllArgsConstructor
@Service
public class ImportService {
private UserService userService;
/**
* 错误数据集合
*/
private List<DataImportResultDto> errorList = new CopyOnWriteArrayList<>();
/**
* @param data
* @param response
* @throws IOException
*/
public void importCase1(List<DataImportCase1Dto> data, HttpServletResponse response) throws IOException {
Assert.notEmpty(data, "参数不可为空");
//todo:
//1:校验data中的数据是否重复，例如编号是否重复
//2：校验data中的编号是否与库里数据重复
//3：校验业务数据是否符合要求
//4：执行入库
//5:收集不符合要求的数据，反馈给调用者一个excel
List<DataImportResultDto> errorList = new ArrayList<>();
errorList = data.stream().map(x -> {
DataImportResultDto error = new DataImportResultDto();
BeanUtils.copyProperties(x, error);
error.setExplain("这里是错误描述");
return error;
}).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(errorList)) {
EasyExcel.write(response.getOutputStream())
//.head(DataImportResultDto.head())
.head(DataImportResultDto.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("错误数据")
.doWrite(errorList);
}
}
public void importCase2(List<DataImportCase2Dto> data) {
log.debug("importCase2-分批导入，本次导入数据量={}", data.size());
}
public Boolean verifyImportData(DataImportCase2Dto data) {
//1：校验data中的编号是否与库里数据重复
//2：校验业务数据是否符合要求
DataImportResultDto error = new DataImportResultDto();
BeanUtils.copyProperties(data, error);
error.setExplain("这里是错误描述");
errorList.add(error);
return false;
}
/**
* 响应错误数据
*/
public void responseErrorData(HttpServletResponse response) {
if (errorList.size() > 0) {
//生成一个新的excel，返回给调用者
try {
EasyExcel.write(response.getOutputStream())
//.head(DataImportResultDto.head())
.head(DataImportResultDto.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("错误数据")
.doWrite(errorList);
} catch (IOException e) {
log.error("responseErrorData-error,msg={},stackTrace={}", e.getMessage(), e.getStackTrace());
}
}
}
}
case2导入dto
@Data
public class DataImportCase2Dto {
@ExcelProperty("编号")
private String id;
@ExcelProperty("名称")
private String name;
private String address;
@ExcelProperty("类型")
private String type;
@ExcelProperty("宽")
private String width;
@ExcelProperty("高")
private String height;
@ExcelProperty("来源")
private String source;
@ExcelProperty("备注")
private String remark;
}
工作流程
解析Excel文件：EasyExcel 首先需要解析Excel文件，获取文件中的所有工作表和单元格。可以使用 POI 提供的 API 来解析 Excel 文件。
格式化数据：一旦文件被解析，EasyExcel 可以将数据格式化为所需的格式。这可以通过使用 POI 提供的各种方法来完成，如设置单元格格式、填充数据等。
生成 Java 对象：一旦数据被格式化，EasyExcel 可以将其转换为 Java 对象。这些对象可以包含数据、公式、图表等。
写入 Excel 文件：最后，EasyExcel 可以将生成的 Java 对象写入到 Excel 文件中。这可以通过使用 POI 提供的 API 来完成，如创建工作簿、创建工作表、写入数据等。
代码
https://gitee.com/naylor_personal/ramble-spring-boot/tree/master/easyexcel
前后端分离项目知识汇总（整合课程分类）
整合课程分类实现思路1、图解树形控件考到前端页面，按需求更改2、实现Excel表格数据导入数据库功能前端找一个上传的组件后端使用前面学的easyexcel功能来实现3、树状图显示数据功能前端找一个树状图组件后端返回上传的表格数据课程分类集合给前端，传递给组件自动遍历表格数据导入数据库这个功能用前面学的easyExcel来实现1、添加依赖<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>2、创建excel对应实体类对象3、通过代码生成器生成课程表代码==4、创建回调监听器==5、自定义接口方法==实现类完成读取功能==6、控制类完成调用7、swagger完成测试树状图显示数据树状图由element-ui获取1、树状图前端代码结构说明==2、设计思路==给el-tree返回一个集合对象就可以实现遍历这个集合对象格式是，{一级分类对象，二级分类对象数组[],一级分类对象，二级分类对象数组[].......}对应到实体类中就是这个形式==3、返回集合对象==在数据库中查询通过表格上传的数据，返回为集合对象详细的注释通过图片的形式展现出来了，仔细观察理解即可源码也放下来，供大家理解@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
//添加课程分类
@Override
public void saveSubject(MultipartFile file, EduSubjectService subjectService) {
try {
//文件输入流
InputStream in = file.getInputStream();
//调用方法进行读取
EasyExcel.read(in, SubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public List<OneSubject> getAllOneTwoSubject() {
//查询所有一级分类 parentid=0
QueryWrapper<EduSubject> wrapperOne = new QueryWrapper<>();
wrapperOne.eq("parent_id", "0");
List<EduSubject> oneSubjectList = baseMapper.selectList(wrapperOne);
// this.list()也可以通过这种方式调用查询。
//查询所有二级分类 parentid!=0
QueryWrapper<EduSubject> wrapperTwo = new QueryWrapper<>();
wrapperOne.ne("parent_id", "0");
List<EduSubject> twoSubjectList = baseMapper.selectList(wrapperTwo);
//创建list集合，用于存放最终封装的数据
List<OneSubject> finalSubjectList = new ArrayList<>();
//封装一级分类
//查询出来所有的一级分类list集合遍历，得到每一级分类对象，获得每个一级分类对象值
//封装到要求的list集合里面
for (int i = 0; i < oneSubjectList.size(); i++) {
EduSubject eduSubject = oneSubjectList.get(i);
OneSubject oneSubject = new OneSubject();
// oneSubject.setId(eduSubject.getId());
// oneSubject.setTitle(eduSubject.getTitle());
//把eduSubject值复制到对应的oneSubject对象里面，两个对象里面的属性相同对应的的自动赋值
BeanUtils.copyProperties(eduSubject, oneSubject);
//在一级分类循环遍历查询所有的二级分类
//创建list集合封装每个一级分类的二级分类
List<TwoSubject> twoFinalSubjectList = new ArrayList<>();
//遍历二级分类list集合
for (int j = 0; j < twoSubjectList.size(); j++) {
EduSubject tSubject = twoSubjectList.get(j);
//如果二级分类的parentid和一级分类的id一样，就把它加入到一级分类
if (tSubject.getParentId().equals(eduSubject.getId())) {
TwoSubject twoSubject = new TwoSubject();
BeanUtils.copyProperties(tSubject, twoSubject);
twoFinalSubjectList.add(twoSubject);
}
}
//把一级下面所有的二级分类放到一级分类里面
oneSubject.setChildren(twoFinalSubjectList);
finalSubjectList.add(oneSubject);
}
return finalSubjectList;
}
}4、控制类调用接口返回集合5、Swagger测试==加黑的小标题就是前后端整合的过程==1、定义路由、跳转页面2、定义API==定义的API就是后端写好的接口==3、添加上传组件element-ui找组件参数说明template部分数据部分4、测试树形数据与懒加载
前后端分离项目知识汇总（阿里云OSS，EasyExcel）
整合阿里云OSS我们想实现在添加讲师信息的时候加上头像上传功能，怎么办呢？用阿里云的OSS对象存储即可环境部署首先我们打开阿里云注册个OSS对象存储Java操作OSS详细操作可查官方文档，下面只写关键代码[SDK示例 (aliyun.com)](help.aliyun.com/document_de…)1、定义工具类读取配置文件通过继承InitializingBean当项目已启动，spring加载之后，执行接口一个方法。就是afterPropertiesSet读取配置文件内容后，在通过执行接口里的一个方法，从而让外面能使用2、编写上传文件接口MultipartFile类是org.springframework.web.multipart包下面的一个类，如果想使用MultipartFile类来进行文件操作，那么一定要引入Spring框架。MultipartFile主要是用表单的形式进行文件上传，在接收到文件时，可以获取文件的相关属性，比如文件名、文件大小、文件类型等等。我们对着官网实例进行修改3、controller调用接口4、前端部分引入上传图片框也在save页面，所以5、测试上传成功nginx反向代理Nginx快速入门_小蜗牛耶的博客-CSDN博客_nginx 快速入门首先知道nginx的配置文件是nginx.config其次是nginx的配置文件是可以看成一个http请求处理的然后是nginx的server服务。可以理解为每个服务监听不同的端口，分发不同的连接服务。如果是自己的可以直接删掉初始server ，直接新建自己的server。配置文件如下：worker_processes 1;
events {
worker_connections 1024;
}
http {
include mime.types;
default_type application/octet-stream;
client_max_body_size 1024m;
sendfile on;
keepalive_timeout 65;
server {
listen 81;
server_name localhost;
location / {
root html;
index index.html index.htm;
}
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root html;
}
}
server {
listen 9001; #监听端口号
server_name localhost; #主机名称
location ~ /eduService/ { #匹配路径
proxy_pass http://localhost:8001;
}
location ~ /eduService1/ { #匹配路径
proxy_pass http://localhost:6001;
}
location ~ /eduUser/ {
proxy_pass http://localhost:8001;
}
location ~ /eduOss/ {
proxy_pass http://localhost:8002;
}
location ~ /eduVod/ {
proxy_pass http://localhost:8003;
}
location ~ /eduCms/ {
proxy_pass http://localhost:8004;
}
location ~ /ucenterService/ {
proxy_pass http://localhost:8006;
}
location ~ /eduMsm/ {
proxy_pass http://localhost:8005;
}
location ~ /orderService/ {
proxy_pass http://localhost:8007;
}
location ~ /staService/ {
proxy_pass http://localhost:8008;
}
location ~ /admin/ {
proxy_pass http://localhost:8009;
}
}
}
启动nginx修改项目访问路径为nginx的ipEasyExcel简介alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具 (github.com)1.Java领域解析、生成Excel的一款工具写入测试引入依赖 com.alibaba easyexcel 2.1.1 读取测试1、创建实体类和excel对应详细的注释都写到代码里了，这里为了整洁性，不在粘贴代码2、创建回调监听器回调监听器起着很重要的作用，就是由它来进行读取excel文档中的文件的3、直接读使用easyexcel里的read方法进行读取 设置写入文件地址和excel文件名称测试如下：
使用EasyPoi轻松导入导出Excel文档
提到Excel的导入导出，大家肯定都知道alibaba开源的EasyExcel，该项目的github地址为：https://github.com/alibaba/easyexcel。 这个项目非常活跃，项目诞生的目的就是为了简化开发、降低内存消耗。我项目中也用过，但还是有一些槽点的，比如文档很简陋，功能做的不完善，不支持图片的读取等。所以，今天给大家推荐另外一款Excel处理的工具：EasyPoi。一、EasyPoi简介官网：JueYue文档：http://easypoi.mydoc.io/EasyPoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法 ，不如poi那么自定义,不如jxl那么多标签,但是我们就是写的少,写的少。EasyPoi的主要特点 设计精巧,使用简单 接口丰富,扩展简单 默认值多,write less do more spring mvc支持,web导出可以简单明了 二、EasyPoi的功能介绍Excel自适应xls和xlsx两种格式,word只支持docx模式，功能还是挺丰富的。1.Excel导入 注解导入 Map导入 大数据量导入sax模式 导入文件保存 文件校验 字段校验 2.Excel导出 注解导出 模板导出 html导出 3.Excel转html4.word导出5.pdf导出三、重点功能介绍1.注解EasyPoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model--row,filed--col 这样利用注解我们可以和容易做到excel到导入导出 经过一段时间发展,现在注解有5个类分别是 @Excel 作用到filed上面,是对Excel一列的一个描述 @ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示 @ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段 @ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导导出 @ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理 2.Excel 模板模板是处理复杂Excel的简单方法，复杂的Excel样式，可以用Excel直接编辑，完美的避开了代码编写样式的雷区，同时指令的支持，也提了模板的有效性。就像下面这样使用，具体用法请看文档。编辑3.Excel<->Html的互转4.Word模板导出5.图片的导入导出@Test//图片导入public void test() { try { ImportParams params = new ImportParams(); params.setNeedSave(true); List<CompanyHasImgModel> result = ExcelImportUtil.importExcel( new File(PoiPublicUtil.getWebRootPath("import/imgexcel.xls")), CompanyHasImgModel.class, params); for (int i = 0; i < result.size(); i++) { System.out.println(ReflectionToStringBuilder.toString(result.get(i))); } Assert.assertTrue(result.size() == 4); } catch (Exception e) { e.printStackTrace(); } }} //导出 @Excel(name = "公司LOGO", type = 2 ,width = 40 , height = 20,imageType = 1) private String companyLogo;6.Excel大数据导出大数据导出是当我们的导出数量在几万,到上百万的数据时,一次从数据库查询这么多数据加载到内存然后写入会对我们的内存和CPU都产生压力,这个时候需要我们像分页一样处理导出分段写入Excel缓解Excel的压力 EasyPoi提供的是两个方法 强制使用 xssf版本的Excel 。具体代码看文档，我们看下资源占用即可：多次测试用时统计，速度还是可以接受的。数据量用时文件大小列数100W16.4s24.3MB5100W15.9s24.3MB5200W29.5s48.5MB5100W30.8s37.8MB10200W58.7s76.1MB107.大数据导出View的用法Easypoi view 项目是为了更简单的方便搭建在导出时候的操作，利用spring mvc 的view 封装，更加符合spring mvc的风格。EasypoiBigExcelExportView 是针对大数据量导出特定的View,在跳转到这个View的时候不需要查询数据,而且这个View自己去查询数据,用户只要实现IExcelExportServer接口就可以了 。总结，Easypoi的功能强大，文档和示例代码丰富，大家在开发中如果有类似的需求，不妨一试。
榨干服务器：一次惨无人道的性能优化
背景做过2B类系统的同学都知道，2B系统最恶心的操作就是什么都喜欢批量，这不，我最近就遇到了一个恶心的需求——50个用户同时每人导入1万条单据，每个单据七八十个字段，请给我优化。Excel导入技术选型说起Excel导入的需求，很多同学都做过，也很熟悉，这里面用到的技术就是POI系列了。但是，原生的POI很难用，需要自己去调用POI的API解析Excel，每换一个模板，你都要写一堆重复而又无意义的代码。所以，后面出现了EasyPOI，它基于原生POI做了一层封装，使用注解即可帮助你自动解析Excel到你的Java对象。EasyPOI虽然好用，但是数据量特别大之后呢，会时不时地来个内存溢出，甚是烦恼。所以，后面某里又做了一些封装，搞出来个EasyExcel，它可以配置成不会内存溢出，但是解析速度会有所下降。如果要扣技术细节的话，就是DOM解析和SAX解析的区别，DOM解析是把整个Excel加载到内存一次性解析出所有数据，针对大Excel内存不够用就OOM了，而SAX解析可以支持逐行解析，所以SAX解析操作得当的话是不会出现内存溢出的。因此，经过评估，我们系统的目标是每天500万单量，这里面导入的需求非常大，为了稳定性考虑，我们最后选择使用EasyExcel来作为Excel导入的技术选型。导入设计我们以前也做过一些系统，它们都是把导入的需求跟正常的业务需求耦合在一起的，这样就会出现一个非常严重的问题：一损俱损，当大导入来临的时候，往往系统特别卡。导入请求同其它的请求一样只能打到一台机器上处理，这个导入请求打到哪台机器哪台机器倒霉，其它同样打到这台机器的请求就会受到影响，因为导入占用了大量的资源，不管是CPU还是内存，通常是内存。还有一个很操蛋的问题，一旦业务受到影响，往往只能通过加内存来解决，4G不行上8G，8G不行上16G，而且，是所有的机器都要同步调大内存，而实际上导入请求可能也就几个请求，导致浪费了大量的资源，大量的机器成本。另外，我们导入的每条数据有七八十个字段，且在处理的过程中需要写数据库、写ES、写日志等多项操作，所以每条数据的处理速度是比较慢的，我们按50ms算（实际比50ms还长），那1万条数据光处理耗时就需要 10000 * 50 / 1000 = 500秒，接近10分钟的样子，这个速度是无论如何都接受不了的。所以，我一直在思考，有没有什么方法既可以缩减成本，又可以加快导入请求的处理速度，同时，还能营造良好的用户体验？经过苦思冥想，还真被我想出来一种方案：独立出来一个导入服务，把它做成通用服务。导入服务只负责接收请求，接收完请求直接告诉前端收到了请求，结果后面再通知。然后，解析Excel，解析完一条不做其它处理直接就把它扔到Kafka中，下游的服务去消费，消费完了，再发一条消息给Kafka告诉导入服务这条数据的处理结果，导入服务检测到所有行数都收到了反馈，再通知前端这次导入完成了。（前端轮询）如上图所示，我们以导入XXX为例描述下整个流程：前端发起导入XXX的请求；后端导入服务接收到请求之后立即返回，告诉前端收到了请求；导入服务每解析一条数据就写入一行数据到数据库，同时发送该数据到Kafka的XXX_IMPORT分区；处理服务的多个实例从XXX_IMPORT的不同分区拉取数据并处理，这里的处理可能涉及数据合规性检查，调用其他服务补齐数据，写数据库，写ES，写日志等；待一条数据处理完成后给Kafka的IMPORT_RESULT发送消息说这条数据处理完了，或成功或失败，失败需要有失败原因；导入服务的多个实例从IMPORT_RESULT中拉取数据，更新数据库中每条数据的处理结果；前端轮询的接口在某一次请求的时候发现这次导入全部完成了，告诉用户导入成功；用户可以在页面上查看导入失败的记录并下载；这就是整个导入的过程，下面就开始了踩坑之旅，你准备好了吗？聪明的同学会发现，（关注公号彤哥读源码一起学习一起浪）其实大批量导入跟电商中的秒杀是有些类似的，所以，整个过程引入Kafka来在削峰和异步。初步测试经过上面的设计，我们测试导入1万条数据只需要20秒，比之前预估的10分钟快了不止一星半点。但是，我们发现一个很严重的问题，当我们导入数据的时候，查询界面卡到爆，需要等待10秒的样子查询界面才能刷出来，从表象来看，是导入影响了查询。初步怀疑因为我们查询只走了ES，所以，初步怀疑是ES的资源不够。但是，当我们查看ES的监控时发现，ES的CPU和内存都还很充足，并没有什么问题。然后，我们又仔细检查了代码，也没有发现明显的问题，而且服务本身的CPU、内存、带宽也没有发现明显的问题。真的神奇了，完全没有了任何思路。而且，我们的日志也是写ES的，日志的量比导入的量还更大，查日志的时候也没有发现卡过。所以，我想，直接通过Kibana查询数据试试。说干就干，在导入的同时，在Kibana上查询数据，并没有发现卡，结果显示只需要几毫秒数据就查出来了，更多的耗时是在网络传输上，但是整体也就1秒左右数据就刷出来了。因此，可以排除是ES本身的问题，肯定还是我们的代码问题。此时，我做了个简单的测试，我把查询和导入的处理服务分开，发现也不卡，秒级返回。答案已经快要浮出水面了，一定是导入处理的时候把ES的连接池资源占用完了，导致查询的时候拿不到连接，所以，需要等待。通过查看源码，最终发现ES的连接数是在RestClientBuilder类中写死的，DEFAULT_MAX_CONN_PER_ROUTE=10，DEFAULT_MAX_CONN_TOTAL=30，每个路由最大10，总连接数最大30，而且更操蛋的是，这两个配置是写死在代码里面的，没有参数可以配置，只能通过修改代码来实现了。这里也可以做个简单的估算，我们的处理服务部署了4台机器，每台机器一共可以建立30条连接，4台机器就是120条连接，导入一万单如果平均分配，每条连接需要处理 10000 / 120 = 83条数据，每条数据处理100ms（上面用的50ms，都是估值）就是8.3秒，所以，查询的时候需要等待10秒左右，比较合理。直接把这两个参数调大10倍到100和300，（关注公号彤哥读源码一起学习一起浪）再部署服务，测试发现导入的同时，查询也正常了。接下来，我们又测试了50个用户同时导入1万单，也就是并发导入50万单，按1万单20秒来算，总共耗时应该在 50*20=1000秒/60=16分钟，但是，测试发现需要耗时30分钟以上，这次瓶颈又在哪里呢？再次怀疑我们之前的压测都是基于单用户1万单来测试的，当时的服务器配置是导入服务4台机器，处理服务4台机器，根据上面我们的架构图，按理说导入服务和处理服务都是可以无限扩展的，只要加机器，性能就能上去。所以，首先，我们把处理服务的机器加到了25台（我们基于k8s，扩容非常方便，改个数字的事），跑一下50万单，发现没有任何效果，还是30分钟以上。然后，我们把导入服务的机器也加到25台，跑了一下50万单，同样地，发现也没有任何效果，此时，有点怀疑人生了。通过查看各组件的监控，发现，此时导入服务的数据库有个指标叫做IOPS，已经达到了5000，并且持续地在5000左右，IOPS是什么呢？它表示一秒读写IO多少次，跟TPS/QPS差不多，说明MySQL一秒与磁盘的交互次数，一般来说，5000已经是非常高的了。目前来看，瓶颈可能在这里，再次查看这个MySQL实例的配置，发现它使用的是超高IO，实际上还是普通的硬盘，想着如果换成SSD会不会好点呢。说干就干，联系运维重新购买一个磁盘是SSD的MySQL实例。切换配置，重新跑50万单，这次的时间果然降下来了，只需要16分钟了，接近降了一半。所以，SSD还是要快不少的，查看监控，当我们导入50万单的时候，SSD的MySQL的IOPS能够达到12000左右，快了一倍多。后面，我们把处理服务的MySQL磁盘也换成SSD，时间再次下降到了8分钟左右。你以为到这里就结束了嘛（关注公号彤哥读源码一起学习一起浪）？思考上面我们说了，根据之前的架构图，导入服务和处理服务是可以无限扩展的，而且我们已经分别加到了25台机器，但是性能并没有达到理想的情况，让我们来计算一下。假设瓶颈全部在MySQL，对于导入服务，我们一条数据大概要跟MySQL交互4次，整个Excel分成头表和行表，第一条数据是插入头表，后面的数据是更新头表、插入行表，等处理完了会更新头表、更新行表，所以按12000的IOPS来算的话，MySQL会消耗我们 500000 * 4 / 12000 / 60= 2.7分钟，同样地，处理服务也差不多，处理服务还会去写ES，但处理服务没有头表，所以时间也按2.7分钟算，但是这两个服务本质上是并行的，没有任何关系，所以总的时间应该可以控制在4分钟以内，因此，我们还有4分钟的优化空间。再优化经过一系列排查，我们发现Kafka有个参数叫做kafka.listener.concurrency，处理服务设置的是20，而这个Topic的分区是50，也就是说实际上我们25台机器只使用了2.5台机器来处理Kafka中的消息（猜测）。找到了问题点，就很好办了，先把这个参数调整成2，保持分区数不变，再次测试，果然时间降下来了，到5分钟了，后面经过一系列调整测试，发现分区数是100，concurrency是4的时候效率是最高的，最快可以达到4分半的样子。至此，整个优化过程告一段落。总结现在我们来总结一下一共优化了哪些地方：导入Excel技术选型为EasyExcel，确实非常不错，从来没出现过OOM；导入架构设计修改为异步方式处理，参考秒杀架构；Elasticsearch连接数调整为每个路由100，最大连接数300；MySQL磁盘更换为SSD；Kafka优化分区数和kafka.listener.concurrency参数；另外，还有很多其它小问题，限于篇幅和记忆，无法一一讲出来。后期规划通过这次优化，我们也发现了当数据量足够大的时候，瓶颈还是在存储这块，所以，是不是优化存储这块，性能还可以进一步提升呢？答案是肯定的，比如，有以下的一些思路：导入服务和处理服务都修改为分库分表，不同的Excel落入不同的库中，减轻单库压力；写MySQL修改为批量操作，减少IO次数；导入服务使用Redis来记录，而不是MySQL；但是，这次要不要把这些都试一遍呢，其实没有必要，通过这次压测，我们至少能做到心里有数就可以了，真的等到量达到了那个级别，再去优化也不迟。好了，今天的文章就到这里了。
阿里出品Excel工具EasyExcel使用小结
前提笔者做小数据和零号提数工具人已经有一段时间，服务的对象是运营和商务的大佬，一般要求导出的数据是Excel文件，考虑到初创团队机器资源十分有限的前提下，选用了阿里出品的Excel工具EasyExcel。这里简单分享一下EasyExcel的使用心得。EasyExcel从其依赖树来看是对apache-poi的封装，笔者从开始接触Excel处理就选用了EasyExcel，避免了广泛流传的apache-poi导致的内存泄漏问题。引入EasyExcel依赖引入EasyExcel的Maven如下：<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
复制代码当前（2020-09-08）的最新版本为2.2.6。API简介Excel文件主要围绕读和写操作进行处理，EasyExcel的API也是围绕这两个方面进行设计。先看读操作的相关API：// 新建一个ExcelReaderBuilder实例
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 读取的文件对象，可以是File、路径（字符串）或者InputStream实例
readerBuilder.file("");
// 文件的密码
readerBuilder.password("");
// 指定sheet，可以是数字序号sheetNo或者字符串sheetName，若不指定则会读取所有的sheet
readerBuilder.sheet("");
// 是否自动关闭输入流
readerBuilder.autoCloseStream(true);
// Excel文件格式，包括ExcelTypeEnum.XLSX和ExcelTypeEnum.XLS
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 指定文件的标题行，可以是Class对象（结合@ExcelProperty注解使用），或者List<List<String>>实例
readerBuilder.head(Collections.singletonList(Collections.singletonList("head")));
// 注册读取事件的监听器，默认的数据类型为Map<Integer,String>，第一列的元素的下标从0开始
readerBuilder.registerReadListener(new AnalysisEventListener() {
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
// 这里会回调标题行，文件内容的首行会认为是标题行
}
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
// 这里会回调每行的数据
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
});
// 构建读取器
ExcelReader excelReader = readerBuilder.build();
// 读取数据
excelReader.readAll();
excelReader.finish();
复制代码可以看到，读操作主要使用Builder模式和事件监听（或者可以理解为观察者模式）的设计。一般情况下，上面的代码可以简化如下：Map<Integer, String> head = new HashMap<>();
List<Map<Integer, String>> data = new LinkedList<>();
EasyExcel.read("文件的绝对路径").sheet()
.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
head.putAll(headMap);
}
@Override
public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
data.add(row);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里可以打印日志告知所有行读取完毕
}
}).doRead();
复制代码如果需要读取数据并且转换为对应的对象列表，则需要指定标题行的Class，结合注解@ExcelProperty使用：文件内容：
|订单编号|手机号|
|ORDER_ID_1|112222|
|ORDER_ID_2|334455|
@Data
private static class OrderDTO {
@ExcelProperty(value = "订单编号")
private String orderId;
@ExcelProperty(value = "手机号")
private String phone;
}
Map<Integer, String> head = new HashMap<>();
List<OrderDTO> data = new LinkedList<>();
EasyExcel.read("文件的绝对路径").head(OrderDTO.class).sheet()
.registerReadListener(new AnalysisEventListener<OrderDTO>() {
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
head.putAll(headMap);
}
@Override
public void invoke(OrderDTO row, AnalysisContext analysisContext) {
data.add(row);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里可以打印日志告知所有行读取完毕
}
}).doRead();
复制代码如果数据量巨大，建议使用Map<Integer, String>类型读取和操作数据对象，否则大量的反射操作会使读取数据的耗时大大增加，极端情况下，例如属性多的时候反射操作的耗时有可能比读取和遍历的时间长。接着看写操作的API：// 新建一个ExcelWriterBuilder实例
ExcelWriterBuilder writerBuilder = EasyExcel.write();
// 输出的文件对象，可以是File、路径（字符串）或者OutputStream实例
writerBuilder.file("");
// 指定sheet，可以是数字序号sheetNo或者字符串sheetName，可以不设置，由下面提到的WriteSheet覆盖
writerBuilder.sheet("");
// 文件的密码
writerBuilder.password("");
// Excel文件格式，包括ExcelTypeEnum.XLSX和ExcelTypeEnum.XLS
writerBuilder.excelType(ExcelTypeEnum.XLSX);
// 是否自动关闭输出流
writerBuilder.autoCloseStream(true);
// 指定文件的标题行，可以是Class对象（结合@ExcelProperty注解使用），或者List<List<String>>实例
writerBuilder.head(Collections.singletonList(Collections.singletonList("head")));
// 构建ExcelWriter实例
ExcelWriter excelWriter = writerBuilder.build();
List<List<String>> data = new ArrayList<>();
// 构建输出的sheet
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName("target");
excelWriter.write(data, writeSheet);
// 这一步一定要调用，否则输出的文件有可能不完整
excelWriter.finish();
复制代码ExcelWriterBuilder中还有很多样式、行处理器、转换器设置等方法，笔者觉得不常用，这里不做举例，内容的样式通常在输出文件之后再次加工会更加容易操作。写操作一般可以简化如下：List<List<String>> head = new ArrayList<>();
List<List<String>> data = new LinkedList<>();
EasyExcel.write("输出文件绝对路径")
.head(head)
.excelType(ExcelTypeEnum.XLSX)
.sheet("target")
.doWrite(data);
复制代码实用技巧下面简单介绍一下生产中用到的实用技巧。多线程读使用EasyExcel多线程读建议在限定的前提条件下使用：源文件已经被分割成多个小文件，并且每个小文件的标题行和列数一致。机器内存要充足，因为并发读取的结果最后需要合并成一个大的结果集，全部数据存放在内存中。经常遇到外部反馈的多份文件需要紧急进行数据分析或者交叉校对，为了加快文件读取，笔者通常使用这种方式批量读取格式一致的Excel文件一个简单的例子如下：@Slf4j
public class EasyExcelConcurrentRead {
static final int N_CPU = Runtime.getRuntime().availableProcessors();
public static void main(String[] args) throws Exception {
// 假设I盘的temp目录下有一堆同格式的Excel文件
String dir = "I:\\temp";
List<Map<Integer, String>> mergeResult = Lists.newLinkedList();
ThreadPoolExecutor executor = new ThreadPoolExecutor(N_CPU, N_CPU * 2, 0, TimeUnit.SECONDS,
new LinkedBlockingQueue<>(), new ThreadFactory() {
private final AtomicInteger counter = new AtomicInteger();
@Override
public Thread newThread(@NotNull Runnable r) {
Thread thread = new Thread(r);
thread.setDaemon(true);
thread.setName("ExcelReadWorker-" + counter.getAndIncrement());
return thread;
}
});
Path dirPath = Paths.get(dir);
if (Files.isDirectory(dirPath)) {
List<Future<List<Map<Integer, String>>>> futures = Files.list(dirPath)
.map(path -> path.toAbsolutePath().toString())
.filter(absolutePath -> absolutePath.endsWith(".xls") || absolutePath.endsWith(".xlsx"))
.map(absolutePath -> executor.submit(new ReadTask(absolutePath)))
.collect(Collectors.toList());
for (Future<List<Map<Integer, String>>> future : futures) {
mergeResult.addAll(future.get());
}
}
log.info("读取[{}]目录下的文件成功,一共加载:{}行数据", dir, mergeResult.size());
// 其他业务逻辑.....
}
@RequiredArgsConstructor
private static class ReadTask implements Callable<List<Map<Integer, String>>> {
private final String location;
@Override
public List<Map<Integer, String>> call() throws Exception {
List<Map<Integer, String>> data = Lists.newLinkedList();
EasyExcel.read(location).sheet()
.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
data.add(row);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("读取路径[{}]文件成功,一共[{}]行", location, data.size());
}
}).doRead();
return data;
}
}
}
复制代码这里采用ThreadPoolExecutor#submit()提交并发读的任务，然后使用Future#get()等待所有任务完成之后再合并最终的读取结果。注意，一般文件的写操作不能并发执行，否则很大的概率会导致数据错乱多Sheet写多Sheet写，其实就是使用同一个ExcelWriter实例，写入多个WriteSheet实例中，每个Sheet的标题行可以通过WriteSheet实例中的配置属性进行覆盖，代码如下：public class EasyExcelMultiSheetWrite {
public static void main(String[] args) throws Exception {
ExcelWriterBuilder writerBuilder = EasyExcel.write();
writerBuilder.excelType(ExcelTypeEnum.XLSX);
writerBuilder.autoCloseStream(true);
writerBuilder.file("I:\\temp\\temp.xlsx");
ExcelWriter excelWriter = writerBuilder.build();
WriteSheet firstSheet = new WriteSheet();
firstSheet.setSheetName("first");
firstSheet.setHead(Collections.singletonList(Collections.singletonList("第一个Sheet的Head")));
// 写入第一个命名为first的Sheet
excelWriter.write(Collections.singletonList(Collections.singletonList("第一个Sheet的数据")), firstSheet);
WriteSheet secondSheet = new WriteSheet();
secondSheet.setSheetName("second");
secondSheet.setHead(Collections.singletonList(Collections.singletonList("第二个Sheet的Head")));
// 写入第二个命名为second的Sheet
excelWriter.write(Collections.singletonList(Collections.singletonList("第二个Sheet的数据")), secondSheet);
excelWriter.finish();
}
}
复制代码效果如下：分页查询和批量写在一些数据量比较大的场景下，可以考虑分页查询和批量写，其实就是分页查询原始数据 -> 数据聚合或者转换 -> 写目标数据 -> 下一页查询....。其实数据量少的情况下，一次性全量查询和全量写也只是分页查询和批量写的一个特例，因此可以把查询、转换和写操作抽象成一个可复用的模板方法：int batchSize = 定义每篇查询的条数;
OutputStream outputStream = 定义写到何处;
ExcelWriter writer = new ExcelWriterBuilder()
.autoCloseStream(true)
.file(outputStream)
.excelType(ExcelTypeEnum.XLSX)
.head(ExcelModel.class);
for (;;){
List<OriginModel> list = originModelRepository.分页查询();
if (list.isEmpty()){
writer.finish();
break;
}else {
list 转换-> List<ExcelModel> excelModelList;
writer.write(excelModelList);
}
}
复制代码参看笔者前面写过的一篇非标题党生产应用文章《百万级别数据Excel导出优化》，适用于大数据量导出的场景，代码如下：Excel上传与下载下面的例子适用于Servlet容器，常见的如Tomcat，应用于spring-boot-starter-webExcel文件上传跟普通文件上传的操作差不多，然后使用EasyExcel的ExcelReader读取请求对象MultipartHttpServletRequest中文件部分抽象的InputStream实例即可：@PostMapping(path = "/upload")
public ResponseEntity<?> upload(MultipartHttpServletRequest request) throws Exception {
Map<String, MultipartFile> fileMap = request.getFileMap();
for (Map.Entry<String, MultipartFile> part : fileMap.entrySet()) {
InputStream inputStream = part.getValue().getInputStream();
Map<Integer, String> head = new HashMap<>();
List<Map<Integer, String>> data = new LinkedList<>();
EasyExcel.read(inputStream).sheet()
.registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
head.putAll(headMap);
}
@Override
public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
data.add(row);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("读取文件[{}]成功,一共:{}行......", part.getKey(), data.size());
}
}).doRead();
// 其他业务逻辑
}
return ResponseEntity.ok("success");
}
复制代码使用Postman请求如下：使用EasyExcel进行Excel文件导出也比较简单，只需要把响应对象HttpServletResponse中携带的OutputStream对象附着到EasyExcel的ExcelWriter实例即可：@GetMapping(path = "/download")
public void download(HttpServletResponse response) throws Exception {
// 这里文件名如果涉及中文一定要使用URL编码,否则会乱码
String fileName = URLEncoder.encode("文件名.xlsx", StandardCharsets.UTF_8.toString());
// 封装标题行
List<List<String>> head = new ArrayList<>();
// 封装数据
List<List<String>> data = new LinkedList<>();
response.setContentType("application/force-download");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
EasyExcel.write(response.getOutputStream())
.head(head)
.autoCloseStream(true)
.excelType(ExcelTypeEnum.XLSX)
.sheet("Sheet名字")
.doWrite(data);
}
复制代码这里需要注意一下：文件名如果包含中文，需要进行URL编码，否则一定会乱码。无论导入或者导出，如果数据量大比较耗时，使用了Nginx的话记得调整Nginx中的连接、读写超时时间的上限配置。使用SpringBoot需要调整spring.servlet.multipart.max-request-size和spring.servlet.multipart.max-file-size的配置值，避免上传的文件过大出现异常。小结EasyExcel的API设计简单易用，可以使用他快速开发有Excel数据导入或者导出的场景，实属提数工具人的喜爱的工具之一。（本文完 c-3-d e-a-20200909）
Excel 高效解析工具 EasyExcel 实践
简介EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。maven 依赖如下：<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>读 Execl 实践数据读取代码如下：先获取文件流，比如 test-data.xls;SkuModel是一个解析类；SkuModelReadListener中进行读取到的数据处理。InputStream excelStream = ExcelToolMain.class.getClassLoader().getResourceAsStream("test-data.xls");
EasyExcel.read(excelStream, SkuModel.class, new SkuModelReadListener()).sheet().doRead();写 Execl 实践写数据代码如下：首先我们需要设定写入文件的文件名然后设置写入的数据类 SkuModel.data() 是用来获取数据的，其实就是返回，构建一个 List<SkuModel> 数据集合。String fileName = ExcelToolMain.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName, SkuModel.class).sheet().doWrite(data());模板填充为了解决 excel 模板样式的支持，我们可以使用模板方式来进行数据填充：模板样式如下：填充的效果如下：代码如下：// 文件名
String fileName = ExcelToolMain.getPath() + "fillOps" + System.currentTimeMillis() + ".xlsx";
// 数据对象
FillData fillData = new FillData();
fillData.setName("张三");
fillData.setNumber(5.2);
// 写入
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);
文件追加文件追加，感觉在 EasyExcel 中其实是一个流的多写，最后写完后关闭。代码如下：ExcelWriter excelWriter = null;
try {
// 这里 需要指定写用哪个class去写
excelWriter = EasyExcel.write(fileName, SkuModel.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet(sheet).build();
for (int i = 0; i < 5; i++) {
// 分页多次写
excelWriter.write(data(), writeSheet);
}
} finally {
// 一定要 finish，表示文件写完毕，会关闭流
excelWriter.finish();
}总结EasyExcel 是基于 POI 的 excel 解析工具，给我们提供了非常多的高效 API，解决了 POI 一些常见的问题。
EasyExcel的简单使用
1.基本介绍EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 依赖<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>2.简单的导出一个excel对象import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
@Data
public class DemoExcel {
@ExcelProperty(value = {"数字"}, index = 0)
@ColumnWidth(20)
private Double doubleData;
@ExcelProperty(value = {"字符"}, index = 1)
@ColumnWidth(20)
private String string;
/**
* 自定义的时间格式
*/
@DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
@ExcelProperty(value = {"时间"}, index = 2)
@ColumnWidth(30)
private Date date;
}Controllerimport com.alibaba.excel.EasyExcel;
@GetMapping("/download")
public void download(HttpServletResponse response) {
// 模拟从数据库查询数据，不一定要转成demoExcel对象，只要字段能对应上就行
List<DemoExcel> list = getList();
try {
// 导出数据
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DemoExcel.class).sheet("sheet1")
.doWrite(list);
} catch (Exception e) {
log.debug("导出文件失败:{}", e.getMessage());
throw new RuntimeException("导出文件失败");
}
}使用postman调试点击send and download就能下载到excel如果excel打不开，报错，多半是抛异常了，可以右键用记事本打开看到返回的json，也可以直接点一下send再调一次接口直接看到报错信息3.简单的导入一个excel对象import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
@Data
public class DemoExcel {
@ExcelProperty(value = {"数字"}, index = 0)
@ColumnWidth(20)
private Double doubleData;
@ExcelProperty(value = {"字符"}, index = 1)
@ColumnWidth(20)
private String string;
/**
* 自定义的时间格式
*/
@DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
@ExcelProperty(value = {"时间"}, index = 2)
@ColumnWidth(30)
private Date date;
}监听import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
@Data
@Slf4j
public class DemoDataListener extends AnalysisEventListener<DemoExcel> {
private List<DemoExcel> list = new ArrayList();
@Override
public void invoke(DemoExcel data, AnalysisContext context) {
// 这里取到的data就是单独的一行数据，也可以在这个方法里对数据进行一些简单的处理
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这个方法是在excel解析完成后能对数据进行操作，也能在这里对数据进行各种处理
log.info("获取数据量：" + list.size());
}
}
Controllerimport com.alibaba.excel.EasyExcel;
@PostMapping("/upload")
public void upload(@RequestParam("file") MultipartFile file) throws IOException {
DemoDataListener listener = new DemoDataListener();
//headRowNumber(1)从第二行读数据
//sheet()默认读第一个sheet页,当然想读第二页就往里填个1
EasyExcel.read(file.getInputStream(), DemoExcel.class, listener).sheet().headRowNumber(1).doRead();
List<DemoExcel> list = listener.getList();
// 我们取到了excel中的数据后就能用来进行想要的操作了
list.forEach(s -> {
log.info("读取到数据------" + s);
});
}使用postman调试我还是用刚才导出来的那个文件进行一次导入postman向上面这样设置就能进行导入文件了控制台的日志告诉我取到了这个数组4.导出的excel要自带下拉框,以及多个sheet页的导出Controllerimport com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
@GetMapping("/download/default")
public void downloadDefault(HttpServletResponse response) {
try {
// 导出数据
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();
// 预设值
List<DefaultData> defaultData = getDefault();
// 设置第1个sheet为我们的模版 同时设置一个下拉框
// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
WriteSheet writeSheet = EasyExcel.writerSheet(0,"模板").needHead(false)
.registerWriteHandler(new CustomSheetWriteHandler(defaultData))
.build();
// 填写表单的表头
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(true).build();
writeTable0.setClazz(DemoExcel.class);
writer.write(Lists.newArrayList(), writeSheet, writeTable0);
// 根据预设值的pid整理一下
List<DemoDefaultExcel> defaultExcelList = getFormDataList(defaultData);
// 设置第2个sheet为字段可选范围 ,把可以选择的范围列出来，没有也没事
WriteSheet writeDefaultDataSheet = EasyExcel.writerSheet(1,"字段选填范围")
.head(DemoDefaultExcel.class).build();
writer.write(defaultExcelList, writeDefaultDataSheet);
writer.finish();
} catch (Exception e) {
log.debug("导出文件失败:{}", e.getMessage());
throw new RuntimeException("导出文件失败");
}
}Handlerimport com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
public class CustomSheetWriteHandler implements SheetWriteHandler {
private List<DefaultData> defaultDataList;
public CustomSheetWriteHandler(List<DefaultData> defaultDataList){
this.defaultDataList = defaultDataList;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 筛选出pid为1的值，添加到第2列的下拉框中
writeSheetHolder.getSheet().addValidationData(getValidationDataByPid(1L, 1, writeSheetHolder));
writeSheetHolder.getSheet().addValidationData(getValidationDataByPid(2L, 2, writeSheetHolder));
}
private DataValidation getValidationDataByPid(Long pid, int col, WriteSheetHolder writeSheetHolder){
List<String> dataValues = defaultDataList.stream().filter(item -> null != item.getPid() && item.getPid().equals(pid))
.map(DefaultData::getNodeName).collect(Collectors.toList());
return getValidationDataBySelects(writeSheetHolder, col, dataValues.toArray(new String[dataValues.size()]));
}
private DataValidation getValidationDataBySelects(WriteSheetHolder writeSheetHolder, int col, String[] valus){
// 这里4个参数代表从第二行到第65536行，从第几列到第几列。可以根据需求增加
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, col, col);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(valus);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
return dataValidation;
}
}DefaultData.java@Data
public class DefaultData {
private Long pid;
private String nodeName;
}预设值的结构是这样的导出的文件第一页，第二列和第三列有了下拉框选项第二页有可选范围的示范5.导出的excel要有示例DemoExampleExcel.javaimport com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
@Data
// 以注解形式设置样式
// 头背景设置成黄色 IndexedColors.YELLOW.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13)
// 内容的背景设置成黄色 IndexedColors.YELLOW.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 13)
public class DemoExampleExcel {
// 相同的表头会自动合并
@ExcelProperty(value = {"示例", "示例", "数字"}, index = 0)
@ColumnWidth(20)
private Double doubleData;
@ExcelProperty(value = {"示例", "示例", "字符"}, index = 1)
@ColumnWidth(20)
private String string;
@ExcelProperty(value = {"示例", "示例", "字符2"}, index = 2)
@ColumnWidth(20)
private String string2;
@DateTimeFormat("yyyy年MM月dd日 HH:mm:ss")
@ExcelProperty(value = {"示例", "示例", "时间"}, index = 3)
@ColumnWidth(30)
private Date date;
}Controllerimport com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
@GetMapping("/download/example")
public void downloadExample(HttpServletResponse response) {
try {
// 导出数据
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();
// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
WriteSheet writeSheet = EasyExcel.writerSheet(0,"模板").needHead(false).build();
// 获取示例list 填写示例
List<DemoExampleExcel> exampleExcels = getExampleList();
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(true).build();
writeTable0.setClazz(DemoExampleExcel.class);
writer.write(exampleExcels, writeSheet, writeTable0);
// 示例和表头间增加一栏空行
writer.write(Lists.newArrayList(""), writeSheet);
// 填写表单的表头
WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(true).build();
writeTable1.setClazz(DemoExcel.class);
writer.write(Lists.newArrayList(), writeSheet, writeTable1);
writer.finish();
} catch (Exception e) {
log.debug("导出文件失败:{}", e.getMessage());
throw new RuntimeException("导出文件失败");
}
}这里只列举了几种实用并且可能用的上的小功能。更多的进阶技巧可以查看官方语雀。也欢迎大家继续补充这篇文章。6.参考资料github地址:https://github.com/alibaba/easyexcel官方语雀:https://www.yuque.com/easyexcel/doc/easyexcel
