Spring-Boot快速集成jxls-poi (自定义模板，支持本地文件导出，在线文件导出)
在项目持续集成的过程中，有时候需要实现报表导出和文档导出，类似于excel中这种文档的导出，在要求不高的情况下，有人可能会考虑直接导出csv文件来简化导出过程。但是导出xlsx文件，其实过程相对更复杂。解决方案就是使用poi的jar包。使用源生的poi来操作表格，代码冗余，处理复杂，同时poi的相关联的依赖还会存在版本兼容问题。所以直接使用poi来实现表格导出，维护成本大，不易于拓展。
我们需要学会站在巨人的肩膀上解决问题，jxls-poi这个就很好解决这个excel表格导出的多样化的问题。类似jsp和thymealf的模板定义，使得表格导出变得简单可控。
不多BB上代码
1.引入关键依赖包
<!-- jxls-api依赖 -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.15</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.6</version>
</dependency>
这里只需要两个依赖便操作excel表格了。
2.定义模板文件
新建一个excel文件，后缀名为.xlsx,在resources目录下新增一个jxls的文件夹，把模板文件放在这个文件夹下，便于后续的spring-boot的集成。
3.导出工具类
public class ExcelUtils {
public static void exportExcel(String fileName, InputStream templateFile, Map<String, Object> params,
HttpServletResponse response) throws IOException {
response.reset();
response.setHeader("Accept-Ranges", "bytes");
OutputStream os = null;
response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName));
response.setContentType("application/octet-stream;charset=UTF-8");
try {
os = response.getOutputStream();
exportExcel(templateFile, params, os);
} catch (IOException e) {
throw e;
}
}
public static void exportExcel(InputStream templateFile, Map<String, Object> params, OutputStream os) throws IOException {
try {
Context context = new Context();
Set<String> keySet = params.keySet();
for (String key : keySet) {
//设置参数变量
context.putVar(key, params.get(key));
}
Map<String, Object> myFunction = new HashMap<>();
myFunction.put("fun", new ExcelUtils());
// 启动新的jxls-api 加载自定义方法
Transformer trans = TransformerFactory.createTransformer(templateFile, os);
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) trans.getTransformationConfig().getExpressionEvaluator();
evaluator.getJexlEngine().setFunctions(myFunction);
// 载入模板、处理导出
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(trans);
List<Area> areaList = areaBuilder.build();
areaList.get(0).applyAt(new CellRef("sheet1!A1"), context);
trans.write();
} catch (IOException e) {
throw e;
} finally {
try {
if (os != null) {
os.flush();
os.close();
}
if (templateFile != null) {
templateFile.close();
}
} catch (IOException e) {
throw e;
}
}
}
public Object formatDate(Date date) {
if (date != null) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateStr = sdf.format(date);
return dateStr;
}
return "--";
}
public WritableCellValue getLink(String address, String title) {
return new WritableHyperlink(address, title);
}
}
这个工具类中我定义两个导出用的方法
一个是直接是HttpServletResponse 导出在线下载文件
一个使用输入流导出
同时模板中还支持方法传入。
4.定义导出服务类
全局配置jxls模板的基础路径
#jxls模板的基础路径
jxls.template.path: classpath:jxls/
定义服务接口
public interface ExcelService {
boolean getExcel(String templateFile,Map<String,Object> params, OutputStream os);
boolean getExcel(String templateFile,String fileName, Map<String,Object> params, HttpServletResponse response);
boolean getExcel(String templateFile, Map<String,Object> params, File outputFile);
}
excel导出用服务实现类
@Service
public class ExcelServiceImppl implements ExcelService {
private static final Logger logger = LoggerFactory.getLogger(ExcelServiceImppl.class);
@Value("${jxls.template.path}")
private String templatePath;
@Override
public boolean getExcel(String templateFile, Map<String, Object> params, OutputStream os) {
FileInputStream inputStream = null;
try {
//获取模板文件的输入流
inputStream = new FileInputStream(ResourceUtils.getFile(templatePath + templateFile));
//导出文件到输出流
ExcelUtils.exportExcel(inputStream, params, os);
} catch (IOException e) {
logger.error("excel export has error" + e);
return false;
}
return true;
}
@Override
public boolean getExcel(String templateFile, String fileName, Map<String, Object> params, HttpServletResponse response) {
FileInputStream inputStream = null;
try {
//获取模板文件的输入流
inputStream = new FileInputStream(ResourceUtils.getFile(templatePath + templateFile));
//导出文件到response
ExcelUtils.exportExcel(fileName,inputStream,params,response);
} catch (IOException e) {
logger.error("excel export has error" + e);
return false;
}
return true;
}
@Override
public boolean getExcel(String templateFile, Map<String, Object> params, File outputFile) {
FileInputStream inputStream = null;
try {
//获取模板文件的输入流
inputStream = new FileInputStream(ResourceUtils.getFile(templatePath + templateFile));
File dFile = outputFile.getParentFile();
//文件夹不存在时创建文件夹
if(dFile.isDirectory()){
if(!dFile.exists()){
dFile.mkdir();
}
}
//文件不存在时创建文件
if(!outputFile.exists()){
outputFile.createNewFile();
}
//导出excel文件
ExcelUtils.exportExcel(inputStream, params, new FileOutputStream(outputFile));
} catch (IOException e) {
logger.error("excel export has error" + e);
return false;
}
return true;
}
}
如上，服务类提供了，三种导出的实现方法
- 导导出excel写入response中
- 导出excel写入输出流中
- 导出excel写入文件中
这三种方法足以覆盖所有的业务需求
5.编辑jxls模板
这里为方便导出最好定义与模板匹配的实体类，便于数据的装载和导出
public class UserModel {
private Integer id;
private String name;
private String sex;
private Integer age;
private String remark;
private Date date;
private String link;
}
插入标注定义模板,定义迭代对象jx:each
jx:each(items="list" var="item" lastCell="G3")
上面G3 模板中迭代的结束位置，然后用类似EL表达式的方式填充到模板当中
填写范围jx:area
jx:area(lastCell="G3")
模板编辑完成后保存即可，
6.代码测试使用
- 导出为本地文件
@SpringBootTest
class BlogJxlsApplicationTests {
@Autowired
ExcelService excelService;
@Test
void contextLoads() {
Map<String, Object> params = new HashMap();
List<UserModel> list = new ArrayList<>();
list.add(new UserModel(1, "test01", "男", 25, "tttttttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(2, "test02", "男", 20, "tttttttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(3, "test04", "女", 25, "ttttddddasdadatttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(4, "test08", "男", 20, "ttttttdasdatttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(5, "test021", "女", 25, "ttttdatttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(7, "test041", "男", 25, "ttdadatttttttt",new Date(),"htpp://wwww.baidu.com"));
params.put("list", list);
excelService.getExcel("t1.xlsx", params, new File("D:\\test05.xlsx"));
}
}
导出成功
- 在线导出文件
@RestController
public class TestController {
@Autowired
ExcelService excelService;
@RequestMapping("test")
public void testFile(HttpServletResponse response){
Map<String, Object> params = new HashMap();
List<UserModel> list = new ArrayList<>();
list.add(new UserModel(1, "test01", "男", 25, "tttttttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(2, "test02", "男", 20, "tttttttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(3, "test04", "女", 25, "ttttddddasdadatttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(4, "test08", "男", 20, "ttttttdasdatttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(5, "test021", "女", 25, "ttttdatttttt",new Date(),"htpp://wwww.baidu.com"));
list.add(new UserModel(7, "test041", "男", 25, "ttdadatttttttt",new Date(),"htpp://wwww.baidu.com"));
params.put("list", list);
excelService.getExcel("t1.xlsx",System.currentTimeMillis()+".xlsx", params,response);
}
}
导出成功
源码地址
https://github.com/DavidLei08/BlogJxls.git
