easyExcel是阿里巴巴开源的快速、简单避免OOM的java处理Excel工具,话不多说直接上代码。
1 在POM中引入EasyExcel的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2 根据Excel中的数据,写一个对应的VO类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.util.Date;
/**
* @author mazhen
* @className Student
* @Description TODO
* @date 2020/11/17 15:30
*/
@Data
public class Student {
/*
* @ExcelProperty(value = "ID") 将列与Excel中的列名进行对应
* 也可不加此注解,但就要求Excel中列名与类的属性名要一致
*/
@ExcelProperty(value = "ID")
private Integer id;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "性别")
private String gender;
@ColumnWidth(20)
@ExcelProperty(value = "生日")
private Date birthday;
@ExcelProperty(value = "成绩")
private Integer score;
}
3 返回值封装类
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author mazhen
* @className ResultResponse
* @Description TODO
* @date 2020/11/17 15:35
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ResultResponse {
private Integer status;
private String msg;
private Object data;
public static ResultResponse fail(){
return new ResultResponse(201,"业务调用失败",null);
}
public static ResultResponse fail(String msg){
return new ResultResponse(201,msg,null);
}
public static ResultResponse success(){
return new ResultResponse(200,"业务调用成功",null);
}
public static ResultResponse success(Object data){
return new ResultResponse(200,"业务调用成功",data);
}
public static ResultResponse success(String msg,Object data){
return new ResultResponse(200,msg,data);
}
}
4 上传和下载excel的controller
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
/**
* @author mazhen
* @className ExcelController
* @Description TODO
* @date 2020/11/17 15:48
*/
@Log4j2
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private ExcelService excelService;
private CopyOnWriteArrayList<Student> studentList;
public CopyOnWriteArrayList<Student> getStudentList() {
return studentList;
}
public void setStudentList(CopyOnWriteArrayList<Student> studentList) {
this.studentList = studentList;
}
//上传excel
@RequestMapping("/upload")
public ResultResponse excelUpload(MultipartFile file){
ResultResponse response = checkParam(file);
if (!(200 == response.getStatus())) {
return response;
}
//调用service中的uploadExcel()获取excel中的数据
List<Student> students = excelService.uploadExcel(file,Student.class,new StudentListener());
//将得到的excel数据封装后进行返回
return ResultResponse.success(students);
}
private ResultResponse checkParam(MultipartFile file) {
if (null == file) {
return ResultResponse.fail("上传的文件为空");
}
String filename = file.getOriginalFilename();
if (StringUtils.isEmpty(filename)) {
return ResultResponse.fail("文件格式异常");
}
if (!filename.contains(".xlsx") && !filename.contains(".XLSX") ) {
return ResultResponse.fail("文件格式错误");
}
return ResultResponse.success();
}
//先从前端获取studentList
@RequestMapping("/getStudent")
public void getStudent(@RequestBody CopyOnWriteArrayList<Student> students) {
setStudentList(students);
}
//实现excel下载功能
@RequestMapping("/download")
public void downloadExcel(HttpServletResponse response) {
try {
ExcelUtils.writeExcel("学生信息" ,Student.class ,response,getStudentList());
} catch (Exception e) {
log.error("导出excel表格失败:", e);
}
}
}
5 上传excel的service及StudentListener
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
public interface ExcelService {
List<Student> uploadExcel(MultipartFile file,Class head, StudentListener listener);
}
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.List;
/**
* @author mazhen
* @className ExcelServiceImpl
* @Description TODO
* @date 2020/11/17 15:43
*/
@Service
public class ExcelServiceImpl implements ExcelService {
//读取上传的excel
@Override
public List<Student> uploadExcel(MultipartFile file,Class head, StudentListener listener) {
try {
//1.获取工作簿
ExcelReaderBuilder readBook = EasyExcel.read(file.getInputStream(), head, listener);
//2.获取sheet
ExcelReaderSheetBuilder sheet = readBook.sheet();
//3.获取Excel中的数据
List<Student> students = sheet.doReadSync();
//4.返回数据
return students;
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
package com.cloudpath.iam.gatewayservice.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
/**
* @author mazhen
* @className StudentListener
* @Description TODO
* @date 2020/11/17 15:39
*/
@Component
@Scope("prototype")
public class StudentListener extends AnalysisEventListener<Student> {
@Override
public void invoke(Student student, AnalysisContext analysisContext) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
6 下载Excel的工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import lombok.extern.log4j.Log4j2;
import org.springframework.http.MediaType;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.concurrent.CopyOnWriteArrayList;
/**
* @author mazhen
* @className ExcelUtils
* @Description TODO
* @date 2020/11/17 20:41
*/
@Log4j2
public class ExcelUtils {
public static void writeExcel(String fileName , Class head,
HttpServletResponse response, CopyOnWriteArrayList list) {
try {
ServletOutputStream outputStream = getOutputStream(fileName,response);
ExcelWriterBuilder writeBook = EasyExcel.write(outputStream, head);
ExcelWriterSheetBuilder sheet = writeBook.sheet(fileName);
sheet.doWrite(list);
} catch (Exception e) {
log.error("导出excel表格失败:", e);
}
}
/**
* 导出文件时为Writer生成OutputStream.
*
* @param fileName 文件名
* @param response response
* @return ""
*/
private static ServletOutputStream getOutputStream(String fileName,
HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
//设置响应的类型
response.setContentType(MediaType.MULTIPART_FORM_DATA_VALUE);
//设置响应的编码格式
response.setCharacterEncoding("utf8");
//设置响应头
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
log.error("导出excel表格失败:", e);
throw new Exception("导出excel表格失败!", e);
}
}
}
7 测试
7.1 上传Excel测试
7.2 下载excel测试
先在postman中执行http://localhost:8099/excel/getStudent获取要写入excel的数据:
然后在浏览器中执行http://localhost:8099/excel/download
参考:使用easyexcel完成复杂表头及标题的导出功能
EasyExcel实现上传和下载Excel数据
easyExcel实现excel文件上传和下载