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.使用

1. .在项目中编写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();
	}
}

2. 在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;
		}
	}

3. 编写模板jzgImportTemplate.xlsx
   
   我此处是循环的批注写法jx:area(lastCell=“A1”) jx:each(items=“XB” var=“item” lastCell=“A1” )
   如果不循环，就更简单了。直接在所对应单元格中直接写入${XB}此种样式即可。