1,先引入依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.18</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2,controller类
package com.green.testlocalhost.controller;
import com.green.testlocalhost.common.Exception.BaseException;
import com.green.testlocalhost.service.ExcelService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* @Author zdj
* @Date 2022/11/25 16:49
* @Version 1.0
*/
@Api(tags = "excel管理")
@RestController
@RequestMapping("/excelMgmt")
public class ExcelController {
/**
* excel服务类
**/
@Autowired
private ExcelService excelService;
/**
* 处理用户成绩方法
* @param file
* @return
*/
@RequestMapping(value = "/detalUserGradeData", method = RequestMethod.POST)
@ApiOperation(value = "处理用户成绩方法", notes = "处理用户成绩方法", httpMethod = "POST")
public void detalUserGradeData(HttpServletResponse response, @RequestParam(value = "file", required = false) @RequestPart("file") MultipartFile file) throws IOException {
if (file.getBytes() == null) {
throw new BaseException("处理用户信息", "文件不能为空!", null);
}
excelService.detalUserGradeData(file, response);
}
}
3,server类
package com.green.testlocalhost.service;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.green.testlocalhost.vo.User;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* @Author zdj
* @Date 2022/10/28 17:22
* @Version 1.0
*/
@Service
public class ExcelService {
/**
* 处理用户成绩方法
* @param file
* @return
*/
public void detalUserGradeData(MultipartFile file, HttpServletResponse response) throws IOException {
/********************* 处理excel并封装未集合 *********************/
// 通过文件获取输入流
InputStream is = file.getInputStream();
// 借助hutool读取
ExcelReader reader = ExcelUtil.getReader(is);
List<User> userListTemp = reader.read(0, 1, User.class);
// 创建一个List集合
List<User> userListResult = CollUtil.newArrayList();
for (User row : userListTemp) {
User user = new User();
user.setName(row.getName());
user.setAge(row.getAge());
user.setChineseGrade(row.getChineseGrade());
user.setMathGrade(row.getMathGrade());
userListResult.add(user);
}
// 求总成绩
double chineseSumGrade = userListResult.stream().mapToDouble(User::getChineseGrade).sum();
double mathSumGrade = userListResult.stream().mapToDouble(User::getMathGrade).sum();
User user = new User();
user.setName("总成绩");
user.setChineseGrade(chineseSumGrade);
user.setMathGrade(mathSumGrade);
userListResult.add(user);
// 到这里,导入就结束了哈,你可以将这个userListResult进行入库处理
/********************* 将数据导出 *********************/
String excelName = "处理后的用户信息";
ExcelWriter writer = null;
ServletOutputStream out = null;
try {
// 设置数据到输入流
writer = ExcelUtil.getWriter(true);
writer.write(userListResult, true);
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml,sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xlsx");
out = response.getOutputStream();
writer.flush(out, true);
} catch (Exception e) {
e.printStackTrace();
System.out.println("导出出现异常:" + e.getMessage());
} finally {
// 最后关闭输出流和写入流
if(!ObjectUtils.isEmpty(writer)) writer.close();
if(!ObjectUtils.isEmpty(out)) out.close();
}
}
}
4,po类
package com.green.testlocalhost.vo;
import cn.hutool.core.annotation.Alias;
import lombok.Data;
/**
* @Author zdj
* @Date 2022/11/25 17:02
* @Version 1.0
*/
@Data
public class User {
@Alias("姓名")
private String name;
@Alias("年龄")
private String age;
@Alias("语文成绩")
private Double chineseGrade;
@Alias("数学成绩")
private Double mathGrade;
}
5,测试
简单粗暴,一步到位(这里偷懒,将导入,导出写在同一个方法中,只是记录下,使用方式,需要的伙伴们,自己进行参照封装下,即可)