根据模板导出excel适用于表格结构复杂的数据导出，需要提前定义好excel模板，设置好变量，然后导出数据到excel模板中。
20210420180130385.png
打开创建好的文件province.xls，开始定义模板，设置变量用${}形式，此处的变量要和后边java代码中的model类的字段要一致，这样才能把具体数据输出到excel模板对应的位置
20210420180241900.png
1.依赖
<!-- jxls poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.15</version>
</dependency>
2.model类
import lombok.Data;
@Data
public class AreaModel {
private Integer no;
private Integer areaCode;
private String areaName;
private Integer level;
}
3.util类
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Map;
public class TemplateExcelUtils {
/**
* 根据模板导出数据
* @param fileName
* @param sourcePath resource/template文件夹下路径
* @param beanParams
* @param response
* @throws Exception
*/
public static void downLoadExcel(String fileName,String sourcePath, Map<String, Object> beanParams, HttpServletResponse response)
throws Exception {
try{
OutputStream os = getOutputStream(fileName,response);
//读取模板
InputStream is = TemplateExcelUtils.class.getClassLoader().getResourceAsStream("template/"+sourcePath);
XLSTransformer transformer = new XLSTransformer();
//向模板中写入内容
Workbook workbook = transformer.transformXLS(is, beanParams);
//写入成功后转化为输出流
workbook.write(os);
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
/**
* 导出文件时为Writer生成OutputStream.
* @param fileName 文件名
* @param response response
* @return ""
*/
private static OutputStream getOutputStream(String fileName,
HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
}
4.业务类
@Override
public void exportTemplateProvinceAreaData(HttpServletResponse response){
List<Area> areas = areaMapper.queryProvinceArea(); // 此处是我本地项目的mapper，是为了获取省份数据
List<AreaModel> result = new ArrayList<>();
areas.stream().forEach(area -> {
AreaModel areaModel = new AreaModel();
BeanUtils.copyProperties(area,areaModel);
result.add(areaModel);
});
IntStream.range(0, result.size()).forEach(i -> {
result.get(i).setNo(i+1);
});
try {
Map<String,Object> param = new HashMap<>();
param.put("title","省份数据");
param.put("list",result);
TemplateExcelUtils.downLoadExcel("省份数据","province.xls",param,response);
} catch (Exception e) {
throw new ServiceException(ResultCode.EXPORT_ERROR);
}
}
这是我业务层areaMapper.queryProvinceArea();所返回的省份数据
2021042018212275.png
5.controller层
@GetMapping("/exportTemplateData")
public void exportTemplateData(HttpServletResponse response){
areaService.exportTemplateProvinceAreaData(response);
}
返回的excel
20210420182436178.png
7.到这里根据模板导出就做好了。上面是一个比较简单的模板，也可以定义复杂结构的模板。
20210421112759665.png
导出的数据。
20210421112950969.png
8.如果用easypoi导出的话
8.1依赖
<!--easy poi excel文件导出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
8.2修改实现
private void excelExport(List<TencentMeetingParticipantResultVO> list, MeetingLive meetingLive,
HttpServletResponse response) {
String fileName = "参会成员信息表";
TemplateExportParams param = new TemplateExportParams("templates/canhuixinxi.xlsx", true);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Map<String, Object> data = new HashMap<>();
data.put("title", meetingLive.getName());
data.put("id", meetingLive.getLiveId());
String userName = "";
if(!StringUtils.isEmpty(meetingLive.getCreateUser())){
User user = userMapper.selectById(meetingLive.getCreateUser());
if(user!=null){
userName = user.getFirstName() + user.getLastName();
}
}
data.put("user", userName);
data.put("beginTime", format.format(meetingLive.getBeginTime()));
data.put("endTime", format.format(meetingLive.getEndTime()));
data.put("list", list);//导出list集合
try {
Workbook book = ExcelExportUtil.exportExcel(param, data);
//下载方法
export(response, book, fileName);
} catch (Exception e) {
System.out.println("导出模板Excel，失败:" + e);
}
}
/**
* export导出请求头设置
*
* @param response
* @param workbook
* @param fileName
* @throws Exception
*/
private static void export(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
response.reset();
response.setContentType("application/x-msdownload");
response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName+".xlsx", "UTF-8"));
ServletOutputStream outStream = null;
try {
outStream = response.getOutputStream();
workbook.write(outStream);
} finally {
outStream.close();
}
}
8.3修改模版
A548844C-6D61-4760-8A35-F3263588047D.png
9.easypoi和poi依赖会有冲突，请根据需求合理选择适用的方法
网友评论