首先添加依赖
<!-- web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- lombok主要是来简化实体类方法 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- io常用工具类 -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
<!-- 文件上传工具类 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<!-- easypoi 依赖 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
新建 EasyPoiUtils 工具类
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 cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.pigs.springbooteasypoipigs.entity.UserEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
/**
* @author PIGS
* @version 1.0
* @date 2020/4/25 14:16
* @effect :
* 表格数据工具类
*/
public final class EasyPoiUtils {
private EasyPoiUtils() {
}
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) {
throw new RuntimeException(e);
}
}
private static <T> void defaultExport(List<T> dataList, Class<?> clz, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clz, dataList);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(dataList, clz, fileName, response, exportParams);
}
public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, HttpServletResponse response) {
defaultExport(dataList, clz, fileName, response, new ExportParams(title, sheetName));
}
private static void defaultExport(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
public static void exportExcel(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
defaultExport(dataList, fileName, response);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clz) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
try {
return ExcelImportUtil.importExcel(new File(filePath), clz, params);
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clz) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
try {
return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 导入数据
* userEnity 你自己新建的实体类 实体类代码在下面
* @param file
* @param clz
* @return
*/
public static List<UserEntity> importExcel(MultipartFile file, Class<UserEntity> clz) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(0);
params.setHeadRows(1);
try {
return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
新建一个实体类接收参数
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author PIGS
* @version 1.0
* @date 2020/4/25 14:16
* @effect :
* 用户实体类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserEntity {
@Excel(name = "用户ID", width = 15)
private Integer userId;
@Excel(name = "用户名", width = 15)
private String userName;
@Excel(name = "用户性别", width = 15)
private String userSex;
@Excel(name = "用户年龄", width = 15)
private Integer userAge;
}
新建一个前端控制器
import com.pigs.springbooteasypoipigs.entity.UserEntity;
import com.pigs.springbooteasypoipigs.utils.EasyPoiUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author PIGS
* @version 1.0
* @date 2020/4/25 14:22
* @effect :
* Excel 前端控制器
*/
@RestController
public class ExcelController {
/**
* 从表格插入数据
* 接收并返回前台
*
* @param file
* @return
* @throws IOException
*/
@RequestMapping("/uploadExcel")
public Map<String,Object> uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
List<UserEntity> checkingIns = EasyPoiUtils.importExcel(file, UserEntity.class);
Map<String,Object> map = new HashMap<>();
map.put("code",200);
map.put("msg","ok");
map.put("data",checkingIns);
return map;
}
}
新建一个html页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上传excel</title>
</head>
<body>
<h1>上传excel文件</h1>
<form action="/uploadExcel" method="post" enctype="multipart/form-data">
<p>文件上传</p>
<input type="file" name="file">
<p><input type="submit" value="提交"></p>
</form>
</body>
</html>
自制几条数据
这个字段名跟实体类 @Excel(name = "用户ID", width = 15) 需要一致不然会接收不到参数的
最后提交就完事了
demo 已经上传了 gitee了需要就去看吧