EasyPoi导入导出
<h5>依赖：</h5>
<pre><code><!--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>
<!--easypoi-->
<!---解决验证报错-->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency></code></pre>
<h5>工具类：</h5>
<pre><code>package com.example.demo.util;
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.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* excel工具类
*
* @author jxd
*/
public class ExcelUtils {
/**
* excel 导出
*
* @param list 数据
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param isCreateHeader 是否创建表头
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
*
* @param list 数据
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* excel 导出
*
* @param list 数据
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param response
* @param exportParams 导出参数
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 默认的 excel 导出
*
* @param list 数据
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param response
* @param exportParams 导出参数
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* 默认的 excel 导出
*
* @param list 数据
* @param fileName 文件名称
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
/**
* 下载
*
* @param fileName 文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param filePath excel文件路径
* @param headerRows 表头行,即从多少行开始读取
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file excel文件
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
return importExcel(file, 0, 1, pojoClass);
}
public static <T> List<T> importExcel(FileInputStream fileInputStream, Class<T> pojoClass) throws Exception {
ImportParams params = new ImportParams();
params.setHeadRows(1);//从第二行开始读
params.setNeedVerfiy(true);
return ExcelImportUtil.importExcel(fileInputStream,
pojoClass, params);
}
/**
* excel 导入
*
* @param file excel文件
* @param titleRows 标题行
* @param headerRows 表头行,即从多少行开始读取
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
return importExcel(file, titleRows, headerRows, false, pojoClass);
}
/**
* excel 导入
*
* @param file 上传的文件
* @param titleRows 标题行
* @param headerRows 表头行,即从多少行开始读取
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), headerRows, needVerfiy, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param headerRows 表头行,即从多少行开始读取
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
params.setNeedVerfiy(needVerfiy);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* Excel 类型枚举
*/
enum ExcelTypeEnum {
XLS("xls"), XLSX("xlsx");
private String value;
ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
}</code></pre>
<h5>使用举例：</h5>
<h6>：数据模型：</h6>
<pre><code>package com.example.demo.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.*;
import javax.validation.constraints.NotBlank;
import java.io.Serializable;
@Builder
@AllArgsConstructor()
@NoArgsConstructor
@Data
public class Student implements Serializable {
private static final long serialVersionUID = 5465872778586910807L;
//@NotBlank(message = "姓名不能为空")
@Excel(name = "姓名", orderNum = "1",width = 20)
private String name;
@Excel(name = "地址", orderNum = "2",width = 20)
private String address;
}
</code></pre>
<h5>导入：</h5>
<pre><code> String string = "F:\\ceshi\\student.xlsx";
File file = new File(string);
FileInputStream fileInputStream = new FileInputStream(file);
List<Student> result = ExcelUtils.importExcel(fileInputStream, 1, true, Student.class);
Optional.ofNullable(result).ifPresent(x -> {
System.out.println("导入Excel打印：");
x.forEach(j -> {
System.out.println("姓名:" + j.getName() + " 地址:" + j.getAddress());
});
});</code></pre>
<p>#######excle示例：</p>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/e1e73ad52d03f06b6443de30a76909c8?showdoc=.jpg" alt="" /></p>
<h6>:导出：</h6>
<pre><code>@GetMapping("/abc")
public void abc(HttpServletResponse httpServletResponse) throws Exception {
ExcelUtils.exportExcel(list(), "学生信息", "学生信息", Student.class, "导出学生", httpServletResponse);
}</code></pre>
<p>#######导出示例：</p>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/1144598729208c4ab4355a9ee003b803?showdoc=.jpg" alt="" /></p>