疫情严重，隔离在家，不出门既是爱国，抽出一个项目中使用的easypoi导出代码，请大家指教。
测试项目：使用easypoi导出excel文件
1、创建springboot项目，使用如下POM
代码语言：javascript复制<properties>
<java.version>1.8</java.version>
<mapper.starter.version>2.1.5</mapper.starter.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>${mapper.starter.version}</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring</artifactId>
<version>1.1.5</version>
</dependency>
</dependencies>2、application.properties配置
代码语言：javascript复制server.port=8080
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/hake?useUnicode=true&characterEncoding=utf8&serverTimezone=CTT&allowMultiQueries=true&autoReconnect=true&rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=longfor
mybatis.mapper-locations=classpath*:mapper/*.xml
mybatis.type-aliases-package=com.liu.exportdemo.model3、HkUserDosimetersMapper.xml配置
代码语言：javascript复制<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liu.exportdemo.mapper.HkUserDosimetersMapper">
<resultMap id="BaseResultMap" type="com.liu.exportdemo.model.HkUserDosimeters">
<id column="ID" jdbcType="INTEGER" property="id"/>
<result column="UUID" jdbcType="VARCHAR"
property="uuid"/>
<result column="DOSEYEAR" jdbcType="VARCHAR"
property="doseyear"/>
<result column="DOSESEASON" jdbcType="INTEGER"
property="doseseason"/>
<result column="USERNO" jdbcType="VARCHAR"
property="userno"/>
<result column="USERRADIATIONNO" jdbcType="VARCHAR"
property="userradiationno"/>
<result column="USERNAME" jdbcType="VARCHAR"
property="username"/>
<result column="USERSEX" jdbcType="INTEGER"
property="usersex"/>
<result column="HOSPITALCODE" jdbcType="VARCHAR"
property="hospitalcode"/>
<result column="HOSPITALNAME" jdbcType="VARCHAR"
property="hospitalname"/>
<result column="DEPARTMENTCODE" jdbcType="VARCHAR"
property="departmentcode"/>
<result column="DEPARTMENTNAME" jdbcType="VARCHAR"
property="departmentname"/>
<result column="DOSIMETERNO" jdbcType="VARCHAR"
property="dosimeterno"/>
<result column="NEWDOSIMETERNO" jdbcType="VARCHAR"
property="newdosimeterno"/>
<result column="CHANGFLAG" jdbcType="INTEGER"
property="changflag"/>
<result column="CHANGTIME" jdbcType="TIMESTAMP"
property="changtime"/>
<result column="FLAG" jdbcType="INTEGER"
property="flag"/>
<result column="DELMARK" jdbcType="INTEGER"
property="delmark"/>
<result column="REMARKS" jdbcType="VARCHAR"
property="remarks"/>
<result column="CREATETIME" jdbcType="TIMESTAMP"
property="createtime"/>
<result column="CREATEUSER" jdbcType="VARCHAR"
property="createuser"/>
<result column="MODIFYTIME" jdbcType="TIMESTAMP"
property="modifytime"/>
<result column="MODIFYUSER" jdbcType="VARCHAR"
property="modifyuser"/>
</resultMap>
<select id="selectListExport" resultMap="BaseResultMap" parameterType="com.liu.exportdemo.model.HkUserDosimeters">
set @n = 0;
select
`id`,
(@n := @n 1) orderid,
`UUID`,`doseyear`,`doseseason`,`userno`,`userRadiationNo`,`userName`,`userSex`,`hospitalCode`,`hospitalName`,
`departmentCode`,`departmentName`,`dosimeterNo`,`newdosimeterNo`,`changflag`,
`changtime`,`flag`,`delMark`,`remarks`,
IF(`userSex`=1,'男','女') sexStr,
IF(changflag =0,'未换领','已领取') changflagStr,
IF(
changtime IS NOT NULL,
DATE_FORMAT(changtime, '%Y-%m-%d %H:%i:%s'),
''
) changtimeStr
from hk_user_dosimeters
order by id asc
</select>
</mapper>4、HkUserDosimetersMapper.java文件
代码语言：javascript复制public interface HkUserDosimetersMapper {
List<HkUserDosimeters> selectListExport(HkUserDosimeters hkUserDosimeters);
}5、service层
代码语言：javascript复制public interface IHkUserDosimetersService {
List<HkUserDosimeters> selectListExport(HkUserDosimeters hkUserDosimeters);
}
/**
*
*/
@Slf4j
@Service
public class HkUserDosimetersServiceImpl implements IHkUserDosimetersService {
@Resource
private HkUserDosimetersMapper hkUserDosimetersMapper;
@Override
public List<HkUserDosimeters> selectListExport(HkUserDosimeters hkUserDosimeters) {
return hkUserDosimetersMapper.selectListExport(hkUserDosimeters);
}
}6、controller层
代码语言：javascript复制@Slf4j
@RestController
@RequestMapping("/hkUserDosimeters")
public class HkUserDosimetersController {
@Resource
private IHkUserDosimetersService ihkUserDosimetersService;
/**
* 导出
* @param hkUserDosimeters
* @return
*/
@PostMapping("/export")
public void export(@RequestBody HkUserDosimeters hkUserDosimeters,HttpServletResponse response) {
try {
List<HkUserDosimeters> hkUserDosimeterses =
ihkUserDosimetersService.selectListExport(hkUserDosimeters);
ExportWordUtils.exportExcel(hkUserDosimeterses,"计量笔统计","计量笔统计",
HkUserDosimeters.class,"计量笔统计",response);
} catch (Exception e) {
log.error("HkUserDosimeters导出异常——》", e);
}
}
}7、启动类
代码语言：javascript复制@SpringBootApplication
@MapperScan("com.liu.exportdemo.mapper")
public class ExportdemoApplication {
public static void main(String[] args) {
SpringApplication.run(ExportdemoApplication.class, args);
}
}8、导出公共类方法
代码语言：javascript复制/**
* @Author: Liu Yue
* @Descripition: 导出excel和word的通用方法
* @Date; Create in 2019/12/17 10:00
**/
public class ExportWordUtils {
/**
* 模板路径
*/
private static final String TEMPLATE_PATH = "word/";
/**
* 导出word
* <p>第一步生成替换后的word文件，只支持docx</p>
* <p>第二步下载生成的文件</p>
* <p>第三步删除生成的临时文件</p>
* 模版变量中变量格式：{{foo}}
*
* @param templatePath word模板地址
* @param temDir 生成临时文件存放地址
* @param fileName 文件名
* @param params 替换的参数
* @param request HttpServletRequest
* @param response HttpServletResponse
*/
public static void exportWord(String templatePath, String temDir, String fileName,
Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) {
Assert.notNull(templatePath, "模板路径不能为空");
Assert.notNull(temDir, "临时文件路径不能为空");
Assert.notNull(fileName, "导出文件名不能为空");
Assert.isTrue(fileName.endsWith(".docx"), "word导出请使用docx格式");
if (!temDir.endsWith("/")) {
temDir = temDir File.separator;
}
File dir = new File(temDir);
if (!dir.exists()) {
dir.mkdirs();
}
try {
String userAgent = request.getHeader("user-agent").toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
fileName = URLEncoder.encode(fileName, "UTF-8");
} else {
fileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
}
XWPFDocument doc = WordExportUtil.exportWord07(templatePath, params);
String tmpPath = temDir fileName;
FileOutputStream fos = new FileOutputStream(tmpPath);
doc.write(fos);
// 设置强制下载不打开
response.setContentType("application/force-download");
// 设置文件名
response.addHeader("Content-Disposition", "attachment;fileName=" fileName);
OutputStream out = response.getOutputStream();
doc.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
//delFileWord(temDir,fileName);//这一步看具体需求，要不要删
}
}
/**
* 生成excel对象
*
* @param params 模板导出参数设置
* @param data 模板导出数据
* @param templateName 模板名称
* @return workBook对象
* @throws Exception 异常抛出
*/
public static Workbook getWorkbook(TemplateExportParams params, Map<String, Object> data, String templateName) throws Exception {
String templatePath = TEMPLATE_PATH templateName;
File file = getTemplateFile(templatePath);
params.setTemplateUrl(file.getAbsolutePath());
Workbook book = ExcelExportUtil.exportExcel(params, data);
if (file.exists()) {
file.delete();
}
return book;
}
/**
* 导出excel对象
*
* @param response httpResponse对象
* @param workbook workBook对象
* @param fileName 导出文件名
* @throws Exception 异常抛出
*/
public static void export(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
response.setContentType("application/x-msdownload");
fileName = fileName new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
response.setHeader("Content-disposition", "attachment; filename=" new String(fileName.getBytes("gb2312"), "ISO-8859-1") ".xls");
ServletOutputStream outStream = null;
//OutputStream outStream = null;
try {
outStream = response.getOutputStream();
workbook.write(outStream);
} finally {
outStream.close();
}
}
/**
* 获取模板文件--获取到的文件为临时文件，用完后需要手动删除
* <p>由于springboot打包成jar之后，不能以绝对路径的形式读取模板文件，故此处将模板文件以临时文件的形式写到磁盘中，用完请手动删除</p>
*
* @param templatePath 模板路径
* @return 模板文件
* @throws Exception 异常抛出
*/
public static File getTemplateFile(String templatePath) throws Exception {
File file = File.createTempFile("temp", null);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource[] resources = resolver.getResources(templatePath);
if (resources.length == 1) {
InputStream inputStream = resources[0].getInputStream();
inputStreamToFile(inputStream, file);
} else {
System.out.println("请检查模板文件是否存在");
}
return file;
}
/**
* InputStream 转file
*
* @param ins 输入流
* @param file 目标文件
*/
public static void inputStreamToFile(InputStream ins, File file) {
try {
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//以下是excel的通用方法类
/**
* 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 fileName 文件名称
* @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 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 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 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 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(), titleRows, headerRows, needVerfiy, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerfiy 是否检验excel内容
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, 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);
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;
}
}
}代码地址： https://gitee.com/maojindaogg/easypoidemo
补充DDL语句：
代码语言：javascript复制/*DDL 信息*/------------
CREATE TABLE `hk_user_dosimeters` (
`id` int(21) NOT NULL AUTO_INCREMENT COMMENT '主键',
`UUID` varchar(64) DEFAULT NULL COMMENT 'uuid',
`doseyear` varchar(10) DEFAULT NULL COMMENT '监测年份',
`doseseason` int(1) DEFAULT NULL COMMENT '监测季节',
`userno` varchar(100) NOT NULL COMMENT '用户工号',
`userRadiationNo` varchar(100) DEFAULT NULL COMMENT '放射人员编号',
`userName` varchar(100) DEFAULT NULL COMMENT '人员姓名',
`userSex` int(1) DEFAULT NULL COMMENT '性别：1 男，2 女',
`hospitalCode` varchar(64) DEFAULT NULL COMMENT '医院uuid',
`hospitalName` varchar(100) DEFAULT NULL COMMENT '医院名称',
`departmentCode` varchar(64) DEFAULT NULL COMMENT '科室uuid',
`departmentName` varchar(100) DEFAULT NULL COMMENT '科室名称',
`dosimeterNo` varchar(100) DEFAULT NULL COMMENT '原来计量笔编号',
`newdosimeterNo` varchar(100) DEFAULT NULL COMMENT '新计量笔编号',
`changflag` int(1) NOT NULL DEFAULT '0' COMMENT '换领标识0是未换领，1已经领取',
`changtime` datetime DEFAULT NULL COMMENT '换领时间',
`flag` int(1) NOT NULL DEFAULT '1' COMMENT '有效标记1:有效；0无效',
`delMark` int(1) DEFAULT '1' COMMENT '删除标记1:没删除；0删除',
`remarks` varchar(100) DEFAULT NULL COMMENT '描述、备注',
`createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`createUser` varchar(100) DEFAULT NULL COMMENT '创建人',
`modifyTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`modifyUser` varchar(100) DEFAULT NULL COMMENT '更新人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3081 DEFAULT CHARSET=utf8
