【easypoi导出实例】

编程

   测试项目:使用easypoi导出excel文件

1、创建springboot项目,使用如下POM

<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-annotation</artifactId>

<version>3.0.1</version>

</dependency>

<dependency>

<groupId>cn.afterturn</groupId>

<artifactId>easypoi-base</artifactId>

<version>3.0.1</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配置

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.model

3、HkUserDosimetersMapper.xml配置

<?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文件

public interface HkUserDosimetersMapper {

List<HkUserDosimeters> selectListExport(HkUserDosimeters hkUserDosimeters);

}

5、service层

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层

@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、启动类

@SpringBootApplication

@MapperScan("com.liu.exportdemo.mapper")

public class ExportdemoApplication {

public static void main(String[] args) {

SpringApplication.run(ExportdemoApplication.class, args);

}

}

8、导出公共类方法

/**

* @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语句:

/*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

 

以上是 【easypoi导出实例】 的全部内容, 来源链接: utcz.com/z/513582.html

回到顶部