基于FreeMarker导出模板

今天，来简单介绍一下基于freemarker来导出Excel

引入maven依赖

我这里是用的Spring-Boot项目，首先引入依赖

 <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-freemarker</artifactId>
  </dependency>

构造freemarker模板

创建目标Excel

首先，我们先手工创建一个我们需要的Excel文件

然后选择文件-另存为，文件类型选择xml的格式。

处理模板数据

首先，把刚才另存为的模板数据，格式改为.ftl,然后对模板数据进行编辑，这里我只贴关键代码(如果要完整的，去看附件)

<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="20" x:FullColumns="1" x:FullRows="1"
               ss:DefaultColumnWidth="51.2" ss:DefaultRowHeight="17.6">
            <Row>
                <Cell>
                    <Data ss:Type="String">姓名</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">年龄</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">地址</Data>
                </Cell>
            </Row>


            <#list userList as user>
                <Row>
                    <Cell>
                        <Data ss:Type="String">${user.name}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="Number">${user.age}</Data>
                    </Cell>
                    <Cell>
                        <Data ss:Type="String">${user.address}</Data>
                    </Cell>
                </Row>
            </#list>

        </Table>

构建数据实体

我这里是用Map来封装的数据，当然也可以用对象实体

private static List<Map<String, Object>> getUserList(){
		List<Map<String, Object>> returnList = new ArrayList<Map<String, Object>>();
		Map<String,Object> map1 = new HashMap<String, Object>();
		Map<String,Object> map2 = new HashMap<String, Object>();
		map1.put("name", "张三");
		map1.put("age", "18");
		map1.put("address", "广东");

		map2.put("name", "王五");
		map2.put("age", "22");
		map2.put("address", "北京");

		returnList.add(map1);
		returnList.add(map2);
		return returnList;
	}

4、导出操作

@Autowired
private Configuration freemarkerConfiguration;
	

	@RequestMapping("/export")
	public void export(HttpServletResponse response, HttpServletRequest request) {
		try {
			List<Map<String, Object>> userList= getUserList();
			//获取模板
			Template template = freemarkerConfiguration.getTemplate("userlist.ftl");
			Map<String,Object> root = new HashMap<String,Object>();
			root.put("userList", userList);
			root.put("userListSize", String.valueOf(userList.size()+2));

			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
			Date today = new Date();
			String fileName = "人员导出列表" + sdf.format(today);
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setHeader("Content-disposition","attachment;filename=\""+new String((fileName+".xls").getBytes("GBK"),"ISO8859-1")+"\"");
			//response字符流转换成字节流，template需要字节流作为输出
			OutputStream outputStream = response.getOutputStream();
			OutputStreamWriter outputWriter = new OutputStreamWriter(outputStream,"UTF-8");
			Writer writer = new BufferedWriter(outputWriter);
			template.process(root, writer);
			writer.flush();
			writer.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

总结

我在这次导出Excel的过程中，遇到一些问题，整理如下：

1.后台找不到Excel模板数据，需要在pom里执行ftl模板的位置

 <resources>  
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.ftl</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>

2、导出数据是金额的时候，默认是按照3位一个逗号，如果想取消，可通过xx?c来取消数据的3位一逗号。

<#if data.eleLoanBalance??>
    <Data ss:Type="String">${data.eleLoanBalance?c}元</Data>
    <#else>
        <Data ss:Type="String">0元</Data>
</#if>

如果有写的不对或者不好的地方，欢迎大佬们指出!