easyPOI基本用法
许沛
1.Excel文件的简单导入和导出
1.1准备工作
1）首先新建一个SpringBoot的项目，搭建基本的环境访问数据，详见源码。
2）导入easypoi依赖
<!--easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.4.0.Final</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
3）创建Excel操作的工具类ExcelUtils
package com.deludynamics.domain.site.util;
import cn.afterturn.easypoi.cache.manager.POICacheManager;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
import cn.afterturn.easypoi.excel.entity.ExcelToHtmlParams;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.word.WordExportUtil;
import cn.afterturn.easypoi.word.parse.ParseWord07;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
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.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* Excel导入导出工具类
*/
public class ExcelUtils {
/**
* excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
* @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
* @param response
*/
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 title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @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 templatePath 模板路径
* @param map 数据集合
* @param fileName 文件名
* @param response
* @throws IOException
*/
public static void exportExcel(TemplateExportParams templatePath, Map<String, Object> map, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(templatePath, map);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @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 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 + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* word下载
*
* @param fileName 下载时的文件名称
* @param response
* @param doc
*/
private static void downLoadWord(String fileName, HttpServletResponse response, XWPFDocument doc) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/msword");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".docx", "UTF-8"));
doc.write(response.getOutputStream());
} 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, 1, 1, pojoClass);
}
/**
* excel 导入
*
* @param filePath excel文件路径
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
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 上传的文件
* @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 {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel转html预览
*
* @param filePath 文件路径
* @param response
* @throws Exception
*/
public static void excelToHtml(String filePath, HttpServletResponse response) throws Exception {
ExcelToHtmlParams params = new ExcelToHtmlParams(WorkbookFactory.create(POICacheManager.getFile(filePath)), true);
response.getOutputStream().write(ExcelXorHtmlUtil.excelToHtml(params).getBytes());
}
/**
* word模板导出
*
* @param map
* @param templatePath
* @param fileName
* @param response
* @throws Exception
*/
public static void WordTemplateExport(Map<String, Object> map, String templatePath, String fileName, HttpServletResponse response) throws Exception {
XWPFDocument doc = WordExportUtil.exportWord07(templatePath, map);
downLoadWord(fileName, response, doc);
}
/**
* word模板导出多页
*
* @param list
* @param templatePath
* @param fileName
* @param response
* @throws Exception
*/
public static void WordTemplateExportMorePage(List<Map<String, Object>> list, String templatePath, String fileName, HttpServletResponse response) throws Exception {
XWPFDocument doc = new ParseWord07().parseWord(templatePath, list);
downLoadWord(fileName, response, doc);
}
/**
* excel 导入，有错误信息
*
* @param file 上传的文件
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcelMore(file.getInputStream(), pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param pojoClass pojo类型
* @param <T>
* @return
*/
private static <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(1);//表格内数据标题行
params.setHeadRows(1);//表头行
params.setSaveUrl("/excel/");
params.setNeedSave(true);
params.setNeedVerify(true);
try {
return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
4）application.yml文件配置
#easypoi启用覆盖
spring
main:
allow-bean-definition-overriding: true
1.2导入
1）编写controller导入接口
/**
* 导入数据
* @param file
* @return
* @throws IOException
*/
@RequestMapping(value = "/import", method = RequestMethod.POST)
public CommonResult importExcel(@RequestParam("file") MultipartFile file) throws IOException {
List<User> list = ExcelUtils.importExcel(file, User.class);
int i = userService.insertByBatch(list);
if (i != 0) {
return new CommonResult(200, "导入成功");
} else {
return new CommonResult(444, "导入失败");
}
}
2）新建User实体类，给属性添加@Excel注解
package com.example.easypoidemoadmin.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* @author zhongyushi
* @date 2020/6/24 0024
* @dec 用户实体
*/
@Data
@TableName(value = "User")
public class User {
/**
* 用户名
*/
@TableId(value = "username")
@Excel(name = "用户名",)
private String username;
/**
* 姓名
*/
@TableField(value = "name")
@Excel(name = "姓名")
private String name;
/**
* 年龄
*/
@TableField(value = "age")
@Excel(name = "年龄")
private Integer age;
/**
* 性别,0表示男，1表示女
*/
@TableField(value = "sex")
@Excel(name = "性别",replace = {"男_0", "女_1"})
private String sex;
/**
* 籍贯
*/
@TableField(value = "address")
@Excel(name = "籍贯")
private String address;
}
需要注意的是，上述的导入的excel内容必须包含表头和标题，否则读取不到内容。在性别这里，分别使用数字代替文字，存储方便。
3）注意事项
A：excel表格的表头必须和@Excel的name属性一样，否则读取不到数据。
B：若导入的字段包含日期类型，那么需要指定导入时的日期的格式并标明是必导入字段，如下所示，excel的内容的日期也需要是这种格式
@Excel(name = "日期",isImportField = "true", importFormat = "yyyy-MM-dd" ,databaseFormat = "yyyy-MM-dd")
C：若导出的字段包含日期类型，那么需要指定导出的格式
@Excel(name = "日期",exportFormat = "yyyy-MM-dd", databaseFormat = "yyyy-MM-dd")
二者综合的代码如下，下一小节的导出日期就不再说明。
@Excel(name = "日期",isImportField = "true",exportFormat = "yyyy-MM-dd", importFormat = "yyyy-MM-dd" ,databaseFormat = "yyyy-MM-dd")
1.3导出
导入就是根据查询的条件把查询结果先写到excel表格中，然后下载这个excel即可。
/**
* 导出数据，使用map接收
*
* @param map
* @param response
* @throws IOException
*/
@PostMapping("/exportExcel")
public void exportExcel(@RequestBody Map<String, Object> map, HttpServletResponse response) throws IOException {
IPage<User> iPage = userService.getList((String) map.get("name"), (Integer) map.get("page"), (Integer) map.get("limit"));
ExcelUtils.exportExcel(iPage.getRecords(), (String) map.get("title"), (String) map.get("sheetName"), User.class, (String) map.get("fileName"), response);
}
2）给实体类@Excel注解添加其他属性
package com.example.easypoidemoadmin.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* @author zhongyushi
* @date 2020/6/24 0024
* @dec 用户实体
*/
@Data
@TableName(value = "User")
public class User {
/**
* 用户名
*/
@TableId(value = "username")
@Excel(name = "用户名", orderNum = "0", width = 30)
private String username;
/**
* 姓名
*/
@TableField(value = "name")
@Excel(name = "姓名", orderNum = "1", width = 30)
private String name;
/**
* 年龄
*/
@TableField(value = "age")
@Excel(name = "年龄", orderNum = "2", width = 30)
private Integer age;
/**
* 性别,0表示男，1表示女
*/
@TableField(value = "sex")
@Excel(name = "性别", orderNum = "3", width = 30,replace = {"男_0", "女_1"})
private String sex;
/**
* 籍贯
*/
@TableField(value = "address")
@Excel(name = "籍贯", orderNum = "4", width = 30)
private String address;
}
1.4图片的导出
有了上面的导出基础，图片的导出就很简单了。
1）新建一个实体类，用于和上面的实体类区分
package com.example.easypoidemoadmin.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
/**
* @author zhongyushi
* @date 2020/6/26 0026
* @dec 描述
*/
@Data
public class Company {
@Excel(name = "公司名称",width =20)
private String name;
/**
* type为 2 表示字段类型为图片
* imageType为 1 表示从file读取
*/
@Excel(name = "公司logo",width =20,type = 2,imageType = 1)
private String logo;
@Excel(name = "公司介绍",width =100)
private String dec;
public Company(String name,String logo,String dec){
this.name=name;
this.logo=logo;
this.dec=dec;
}
}
2）创建接口，图片请自行下载。
/**
* 图片的导出
*
* @param response
* @throws IOException
*/
@PostMapping("/imgexport")
public void imgExport(HttpServletResponse response,@RequestBody Map<String, Object> map) throws IOException {
List<Company> list = new ArrayList<>();
//图片的路径自定义，但必须要正确
list.add(new Company("百度", "E:/img/1.jpg", "百度一下你就知道"));
list.add(new Company("腾讯", "E:/img/3.jpg", "腾讯qq,交流的世界"));
list.add(new Company("阿里巴巴", "E:/img/2.jpg", "阿里巴巴，马云的骄傲"));
String fileName = map.get("fileName").toString();
ExcelUtils.exportExcel(list, fileName, fileName, Company.class, fileName, response);
}
3）在页面添加导出的按钮，点击按钮即可进行下载，下载的文件如图
1.5图片的导入
/**
* 导入图片
* @param file
* @return
* @throws IOException
*/
@PostMapping("/imgimport")
public CommonResult imgImport(@RequestParam("file") MultipartFile file) throws IOException {
List<Company> list = ExcelUtils.importExcel(file, Company.class);
return new CommonResult(200,"导入成功",list);
}
参考
https://www.cnblogs.com/zys2019/p/13194068.html
类似资料：