easyPoi的使用
EasyPOI技术
一.简介
1.什么是POI
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法
二.实现步骤
1.POM文件[两种导入方式]
1.1启动器方式
<!--easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
注意：此种方式需要添加配置
spring:
main:
allow-bean-definition-overriding: true
否则会出现以下错误:
有两个beanNameViewResolver类
The bean 'beanNameViewResolver', defined in class path resource [cn/afterturn/easypoi/configuration/EasyPoiAutoConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [org/springframework/boot/autoconfigure/web/servlet/error/ErrorMvcAutoConfiguration$WhitelabelErrorViewConfiguration.class] and overriding is disabled.
1.2jar包依赖
<!--<dependency>-->
<!--<groupId>cn.afterturn</groupId>-->
<!--<artifactId>easypoi-base</artifactId>-->
<!--<version>4.0.0</version>-->
<!--</dependency>-->
<!--<dependency>-->
<!--<groupId>cn.afterturn</groupId>-->
<!--<artifactId>easypoi-web</artifactId>-->
<!--<version>4.0.0</version> </dependency>-->
<!--<dependency>-->
<!--<groupId>cn.afterturn</groupId>-->
<!--<artifactId>easypoi-annotation</artifactId>-->
<!--<version>4.0.0</version>-->
<!--</dependency>-->
2.在util包下导入
ExcelUtiles.java文件
3.如何使用注解
代码如下 Person类
@Data
@ToString
public class Person {
@Excel(name = "姓名",orderNum = "0")
private String name;
@ExcelEntity(name = "狗")
private Dog dog;
public Person(String name,Dog dog) {
this.name = name;
this.dog = dog;
}
public Person() {
}
}
Dog类
@Data
@ToString
public class Dog {
@Excel(name = "狗名",orderNum = "2")
private String name;
@Excel(name = "狗的性别",orderNum = "3")
private String sex;
public Dog() {
}
public Dog(String name, String sex) {
this.name = name;
this.sex = sex;
}
}
测试类
@RestController
public class ExcelController {
@GetMapping("getExcel")
public void test(HttpServletResponse response) {
List<Person> list = new ArrayList<>();
Person person = new Person("小明",new Dog("小黑","公"));
list.add(person);
ExcelUtiles.exportExcel(list, "测试", "测试", Person.class,"ddd",response );
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G8USxt2u-1573136335579)(E:\JavaData\常用工具类\EasyPOI技术\img\导出数据的图片.jpg)]
三.注意
1.使用ajax
ajax的返回值类型是json,text,html,xml类型，或者可以说ajax的发送，接受都只能是string字符串，不能流类型，所以无法实现文件下载，强用会出现response冲突。
解决办法：模拟表单来提交请求即可
ExcelUtiles
package com.zhiyou100.hospital.test;
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.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;
import java.util.NoSuchElementException;
public class ExcelUtiles {
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);
}
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));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
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);
}
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 NormalException(e.getMessage());
}
}
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);
}
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
//throw new NormalException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
//throw new NormalException(e.getMessage());
} return list;
}
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){
// throw new NormalException("excel文件不能为空");
} catch (Exception e) {
//throw new NormalException(e.getMessage());
System.out.println(e.getMessage());
}
return list;
}
}