JAVA中模板导出excel之JXLS
Java中实现excel根据模板导出数据的方法有很多,一般简单的可以通过操作POI进行。还可以使用一些工具很轻松的实现模板导出。这些工具现在还在维护,而且做得比较好的国内的有easyPOI,国外的就是这个JXLS了。个人觉得easyPOI做动态列导出(不固定列)比较好;JXLS做固定模板导出比较方便。
1. 首先加入JXLS依赖包。
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.6</version>
<exclusions>
<exclusion>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.15</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.7</version>
</dependency>
2.使用
- .在项目中编写util工具类
public class JxlsExcelView extends AbstractView{
private static final String CONTENT_TYPE = "application/vnd.ms-excel";
private String templatePath;
private String exportFileName;
/**
* @param templatePath
* 模版相对于当前classpath路径
* @param exportFileName
* 导出文件名
*/
public JxlsExcelView(String templatePath, String exportFileName)
{
this.templatePath = templatePath;
this.exportFileName = exportFileName;
setContentType(CONTENT_TYPE);
}
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception
{
Context context = PoiTransformer.createInitialContext();
if (model != null)
{
for (String key : model.keySet())
{
context.putVar(key, model.get(key));
}
}
String userAgent = request.getHeader("user-agent").toLowerCase();
response.setContentType(getContentType());
// 防止乱码
if (userAgent.contains("msie") || userAgent.contains("like gecko"))
{
// win10 ie edge 浏览器 和其他系统的ie
exportFileName = URLEncoder.encode(exportFileName, "UTF-8");
} else
{
exportFileName = new String(exportFileName.getBytes("UTF-8"), "ISO-8859-1");
}
response.setCharacterEncoding("UTF-8");
response.setHeader("content-disposition", "attachment;filename=" + exportFileName + ".xlsx");
ServletOutputStream os = response.getOutputStream();
Resource resource = new ClassPathResource("/static/xlstemplate/" + templatePath);
InputStream is = resource.getInputStream();
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
// 获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
//设置静默模式,不报警告
evaluator.getJexlEngine().setSilent(true);
//必须要这个,否者表格函数统计会错乱
jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
is.close();
}
}
- 在controller中调用方法
@RequestMapping("downloadTeacherTemplate")
public JxlsExcelView downModel(Model model, HttpServletRequest request)
{
try
{
model.addAttribute("XB", dictService.queryDict("STD_GB_XB"));
model.addAttribute("MZ", dictService.queryDict("STD_GB_MZ"));
model.addAttribute("ZJLX", dictService.queryDict("STD_JB_SFZJLX"));
model.addAttribute("DQZT", dictService.queryDict("STD_JB_JZGDQZT"));
// 模版相对于当前classpath路径;导出文件名;
return new JxlsExcelView("jzgImportTemplate.xlsx", "教职工导入模板");
} catch (Exception e)
{
e.printStackTrace();
return null;
}
}
- 编写模板jzgImportTemplate.xlsx
我此处是循环的批注写法jx:area(lastCell=“A1”) jx:each(items=“XB” var=“item” lastCell=“A1” )
如果不循环,就更简单了。直接在所对应单元格中直接写入${XB}此种样式即可。