前面我发过一篇Excel的导入导出,不过我觉得这次的excel导出更好,更简洁,是easy poi别人封装好的的,不对BB,下面上代码。
第一步,pom.xml
<!-- easy poi -->
<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>
<!-- easy poi -->
第二步,工具类
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 cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import lombok.extern.slf4j.Slf4j;
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.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* easy poi Excel 导入导出整理
* @Author: w
* @Date: 2019/8/15 15:18
* @Version 1.0
*/
@Slf4j
public class MyExcelUtils {
/**
* 功能描述:复杂导出Excel,包括文件名以及表名。创建表头
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param isCreateHeader 是否创建表头
* @param fileName
* @param response
* @return
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 功能描述:复杂导出Excel,包括文件名以及表名,不创建表头
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param fileName
* @param response
* @return
*/
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));
}
/**
* 功能描述:Map 集合导出
* @param list 实体集合
* @param fileName 导出的文件名称
* @param response
* @return
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 功能描述:默认导出方法
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param pojoClass pojo实体
* @param exportParams ExportParams封装实体
* @param response
* @return
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
/**
* 功能描述:Excel导出
* @param fileName 文件名称
* @param response
* @param workbook Excel对象
* @return
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setHeader("Content-disposition", fileName);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("Pragma", "No-cache");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 功能描述:默认导出方法
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param response
* @return
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导入
* @param file excel文件
* @param pojoClass 导入实体类
* @param verfiy 是否验证
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file,Class<T> pojoClass,boolean verfiy){
/*文件为空,直接返回空*/
if (file == null){
return null;
}
/*是否验证*/
ImportParams importParams = new ImportParams();
importParams.setNeedVerfiy(verfiy);
/*定义获取成功的集合*/
List<T> successList;
try{
/*excel获取结果*/
ExcelImportResult<T> result = ExcelImportUtil.importExcelMore(file.getInputStream(),pojoClass,importParams);
/*获取成功的集合*/
successList = result.getList();
/*获取失败的集合*/
List<T> failList = result.getFailList();
log.info("是否存在验证未通过的数据:" + result.isVerfiyFail());
log.info("验证通过的数量:" + successList.size());
log.info("验证未通过的数量:" + failList.size());
}catch (NoSuchElementException e){
log.error("excel文件不能为空");
throw new RuntimeException("excel文件不能为空");
}catch (Exception e){
log.error(e.getMessage(), e);
throw new RuntimeException("excel导入未知异常");
}
return successList;
}
/**
* excel 导入
* @param filePath 文件路径
* @param pojoClass 导入实体类
* @param verfiy 是否验证
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath,Class<T> pojoClass,boolean verfiy){
/*路径为空,直接返回空*/
if (StringUtils.isBlank(filePath)){
return null;
}
/*是否验证*/
ImportParams importParams = new ImportParams();
importParams.setNeedVerfiy(verfiy);
/*定义获取成功的集合*/
List<T> successList;
try{
/*excel获取结果*/
ExcelImportResult<T> result = ExcelImportUtil.importExcelMore(new File(filePath),pojoClass,importParams);
/*获取成功的集合*/
successList = result.getList();
/*获取失败的集合*/
List<T> failList = result.getFailList();
log.info("是否存在验证未通过的数据:" + result.isVerfiyFail());
log.info("验证通过的数量:" + successList.size());
log.info("验证未通过的数量:" + failList.size());
}catch (NoSuchElementException e){
log.error("excel文件不能为空");
throw new RuntimeException("excel文件不能为空");
}catch (Exception e){
log.error(e.getMessage(), e);
throw new RuntimeException("excel导入未知异常");
}
return successList;
}
}
第三步,定义接收实体,这个实体请自定义哈
@Data
@NoArgsConstructor
public class CustomerDTO {
@Excel(name = "姓名")
private String name;
@Excel(name = "手机")
private String mobile;
@Excel(name = "所在地")
private String zoneName;
}
附使用示例
/*导入*/
@PostMapping("/demo1")
public String demo1(@RequestParam("file") MultipartFile file){
List<DemoDTO> list = MyExcelUtils.importExcel(file,DemoDTO.class,true);
list.forEach(x->log.info(x.getName()));
return "Ok";
}
/*导出*/
@GetMapping("/demo2")
public String demo2(HttpServletResponse response){
List<CustomerDTO> list = new ArrayList<>();
CustomerDTO dto1 = new CustomerDTO("1",2,"3");
CustomerDTO dto2 = new CustomerDTO("2",2,"30");
CustomerDTO dto3 = new CustomerDTO("3",22,"300");
list.add(dto1);
list.add(dto2);
list.add(dto3);
MyExcelUtils.exportExcel(list,"标题","sheet名称",CustomerDTO.class,"文件名称.xls",response);
return "Ok";
}
看完觉得有用点个赞呗