1.新建excel表格 保存为xml格式 用例如<#list userlist as user>…</#list>包含所需导出的表格数据,并对每个数据单元格cell做如下操作 user.id为javabean类字段
2.javabean类
public class TestModel implements Serializable{
private static final long serialVersionUID = 1L;
private String id;
private String realname;
private String jobnum;
private String ktwl;
private String kswl;
private String sywl;
private String sjwl;
private String sxwl;
private String kcwl;
private String bswl;
private String sxiwl;
private String state;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getRealname() {
return realname;
}
public void setRealname(String realname) {
this.realname = realname;
}
public String getJobnum() {
return jobnum;
}
public void setJobnum(String jobnum) {
this.jobnum = jobnum;
}
public String getKtwl() {
return ktwl;
}
public void setKtwl(String ktwl) {
this.ktwl = ktwl;
}
public String getKswl() {
return kswl;
}
public void setKswl(String kswl) {
this.kswl = kswl;
}
public String getSywl() {
return sywl;
}
public void setSywl(String sywl) {
this.sywl = sywl;
}
public String getSjwl() {
return sjwl;
}
public void setSjwl(String sjwl) {
this.sjwl = sjwl;
}
public String getSxwl() {
return sxwl;
}
public void setSxwl(String sxwl) {
this.sxwl = sxwl;
}
public String getKcwl() {
return kcwl;
}
public void setKcwl(String kcwl) {
this.kcwl = kcwl;
}
public String getBswl() {
return bswl;
}
public void setBswl(String bswl) {
this.bswl = bswl;
}
public String getSxiwl() {
return sxiwl;
}
public void setSxiwl(String sxiwl) {
this.sxiwl = sxiwl;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
}
4.util类
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.util.Map;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
public class DocumentHandler {
private Configuration configuration = null;
@SuppressWarnings("deprecation")
public DocumentHandler() {
configuration = new Configuration();
configuration.setDefaultEncoding("utf-8");
}
// 输出word文档
public boolean createDoc(Map<String, Object> dataMap, String fileName) throws UnsupportedEncodingException {
// dataMap 要填入模本的数据文件
// 设置模本装置方法和路径,FreeMarker支持多种模板装载方法。可以重servlet,classpath,数据库装载,
// 这里我们的模板是放在template包下面
configuration.setClassForTemplateLoading(this.getClass(), "/template");
Template t = null;
try {
// test.ftl为要装载的模板
t = configuration.getTemplate("template.ftl");
} catch (IOException e) {
e.printStackTrace();
return false;
}
// 输出文档路径及名称
File outFile = new File(fileName);
Writer out = null;
FileOutputStream fos = null;
try {
fos = new FileOutputStream(outFile);
OutputStreamWriter oWriter = new OutputStreamWriter(fos, "UTF-8");
// 这个地方对流的编码不可或缺,使用main()单独调用时,应该可以,但是如果是web请求导出时导出后word文档就会打不开,并且包XML文件错误。主要是编码格式不正确,无法解析。
// out = new BufferedWriter(new OutputStreamWriter(new
// FileOutputStream(outFile)));
out = new BufferedWriter(oWriter);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
return false;
}
// 装载数据
try {
t.process(dataMap, out);
out.close();
fos.close();
return true;
} catch (TemplateException e) {
e.printStackTrace();
return false;
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
// 输出excel文档
public void createXls(Map<String, Object> data,String fileName) throws IOException, TemplateException {
// 全局数字格式
configuration.setNumberFormat("0.00");
// 设置模板文件位置
configuration.setClassForTemplateLoading(this.getClass(), "/template");
// 加载模板
Template template = configuration.getTemplate("excel_tamplate.ftl", "utf-8");
OutputStreamWriter writer = null;
try {
// 填充数据至Excel
writer = new OutputStreamWriter(new FileOutputStream(fileName), "UTF-8");
template.process(data, writer);
writer.flush();
} finally {
writer.close();
}
}
}
5.测试类
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import freemarker.template.TemplateException;
import model.TestModel;
public class Test {
/**
* @param args
* @throws UnsupportedEncodingException
*/
public static void main(String[] args) throws UnsupportedEncodingException {
Map<String, Object> dataMap = new HashMap<String, Object>();
List<TestModel> testList = new ArrayList<TestModel>();
for (int i = 0; i < 100; i++) {
TestModel textmodel = new TestModel();
textmodel.setId("0");
textmodel.setRealname("testname"+i);
textmodel.setJobnum("123");
textmodel.setKtwl("0");
textmodel.setKswl("0");
textmodel.setSywl("0");
textmodel.setSjwl("0");
textmodel.setSxwl("0");
textmodel.setKcwl("0");
textmodel.setBswl("0");
textmodel.setSxiwl("0");
textmodel.setState("pass");
testList.add(textmodel);
}
dataMap.put("userlist", testList);
DocumentHandler dochandler = new DocumentHandler();
try {
dochandler.createXls(dataMap, "/Users/lingyumin/Desktop/workload.xls");
} catch (IOException | TemplateException e) {
e.printStackTrace();
}
}
}
导出的xls文件使用office无法打开,wps正常打开,问题未解决
需要使用freemaker 2.2.8以上版本 否则会出现javabean在模板中无法解析的问题。