springboot+easypoi实现Excel导入导出
1.导入easypoi依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
2.创建工具类:
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.NoSuchElementException;
public class ExcelUtil {
/**
* Excel导入
* @param file Excel文件
* @param titleRows 标题行数
* @param headerRows 表头行数
* @param pojoClass 实体类
* @param <T> 泛型
* @return 数据集合
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
System.out.println("excel文件不能为空");
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* Excel导出
* @param list 数据集合
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass 实体类
* @param fileName 文件名
* @param response 请求信息
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.创建实体类:
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
@Data
public class Student {
//注解内的name对应的是Excel文件的表头
@Excel(name = "名字")
private String name;
@Excel(name = "年龄")
private Integer age;
@Excel(name = "住址")
private String address;
}
4.创建Controller接口文件:
/**
* Excel导入
* @param file 文件
* @return 返回信息
*/
@PostMapping("/importExcel")
public JSONObject importExcel(@RequestParam("file") MultipartFile file) {
List<Student> list = ExcelUtil.importExcel(file, 1, 1, Student.class);
if (list == null) {
//Excel文件是空或格式不正确
}
for (Student student : list)
{
//插入数据库操作
}
return CommonUtil.successJson("导入成功");
}
/**
* Excel导出
* @param response 请求信息
* @param params 数据库查询时参数
* @return 返回信息
*/
@PostMapping("/exportExcel")
public JSONObject importExcel(HttpServletResponse response, @RequestBody Student params) {
//list为数据库中查出的数据
ExcelUtil.exportExcel(list, "学生档案", "sheet1", Student.class, "学生档案.xls",response);
return CommonUtil.successJson("导出成功");
}