直接上代码
pom
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
结果类:
import java.io.Serializable;
public class Result implements Serializable {
private Integer errno;
private String[] data;
public Integer getErrno() {
return errno;
}
public void setErrno(Integer errno) {
this.errno = errno;
}
public String[] getData() {
return data;
}
public void setData(String[] data) {
this.data = data;
}
}
工具类
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 org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
public class ExcelUtil {
//下载带有表头的空白Excel模板文件
public static void exportExcel(Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams();
exportParams.setCreateHeadRows(true);
defaultExport(new ArrayList<>(), clazz, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> clazz, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, clazz, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, clazz, fileName, response, new ExportParams());
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, clazz, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> clazz, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, list);
if (workbook != null) downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
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());
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clazz) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcel(new File(filePath), clazz, params);
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clazz) throws Exception {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
}
//将excel文件内容转换成User集合
public static List<sysUser> getUserList(MultipartFile file) throws IOException {
String fileName = file.getOriginalFilename();
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
sysUser user;
List<sysUser> list = new LinkedList<>();
int sheetNumber = wb.getNumberOfSheets();
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = wb.getSheetAt(i);
int rowNumber = sheet.getLastRowNum();
for (int r = 1; r <= rowNumber; r++) {
Row row = sheet.getRow(r);
if (row != null) {
String username = getCellValue(row.getCell(0));
String password = getCellValue(row.getCell(1));
String nickname = getCellValue(row.getCell(2));
String headImgUrl = getCellValue(row.getCell(3));
String truename = getCellValue(row.getCell(4));
// String email = getCellValue(row.getCell(5));
Integer status = (int) row.getCell(5).getNumericCellValue();
String intro = getCellValue(row.getCell(6));
String inTime = getCellValue(row.getCell(7));
user = new sysUser(username, password, nickname, headImgUrl, truename,status,intro,inTime);
list.add(user);
}
}
}
return list;
}
@SuppressWarnings("deprecation")
private static String getCellValue(Cell cell) {
if (cell == null) return null;
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
}
}
服务层:服务层加入方法
Result downloadExcelTemplate(HttpServletResponse response);
Result importExcel(MultipartFile file);
Result exportExcel(HttpServletResponse response);
实现类:
/**
* 批量导入下载excel
*/
//下载空白的Excel模板文件
public Result downloadExcelTemplate(HttpServletResponse response) {
try {
ExcelUtil.exportExcel(sysUser.class, "用户表.xls", response);
return Result.getFailedResult("下载成功!");
} catch (Exception e) {
logger.log(Level.SEVERE, e.getMessage());
return Result.getFailedResult("下载失败!"); } }
// 导入用户
public Result importExcel(MultipartFile file) {
String fileName = file.getOriginalFilename();
SysRole role = roleMapper.selectByName("ROLE_USER");
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return Result.getFailedResult("上传文件格式不正确!"); }
try {
List<sysUser> users = ExcelUtil.getUserList(file);
// System.out.println(users);
for (sysUser user : users) {
sysUser user0 =userMapper.getUserById(user.getId());
if (user0 == null) {
//密码加密
user.setPassword(new BCryptPasswordEncoder().encode(user.getPassword()));
userMapper.save(user);
// UserInfo userInfo1= new UserInfo();
// userInfo1.setTruename(user.getTruename());
// userInfo1.setInTime(user.getInTime());
// userInfo1.setUserid(user.getId());
// userInfoService.insert(userInfo1);
}
if (role !=null){
userRoleMapper.add(role.getId(), user.getId());
}
}
return Result.getSuccessResult("导入成功!");
} catch (Exception e) {
logger.log(Level.SEVERE, e.getMessage());
return Result.getFailedResult("导入失败!"); } }
// 导出用户
public Result exportExcel(HttpServletResponse response) {
try {
List<sysUser> users = userMapper.queryAll();
ExcelUtil.exportExcel(users, sysUser.class, "用户.xls", response);
return Result.getFailedResult("导出成功!");
} catch (Exception e) {
logger.log(Level.SEVERE, e.getMessage());
return Result.getFailedResult("导出失败!"); }
}
控制层:
/**
* 下载空白表头
* @param response
* @return
*/
@PreAuthorize("hasPermission('/admin','c')")
@ResponseBody
@RequestMapping("/downloadExcelTemplate")
public Result downloadExcelTemplate(HttpServletResponse response) {
return userService.downloadExcelTemplate(response); }
//导入,即上传
@ResponseBody
@RequestMapping("/import")
public Result importExcel(@RequestParam(value = "filename") MultipartFile file) {
return userService.importExcel(file); }
@PreAuthorize("hasPermission('/admin','c')")
//导出
@ResponseBody
@RequestMapping("/export")
public Result exportExcel(HttpServletResponse response) {
return userService.exportExcel(response); }