设计通用导出Excel和Word方案
1.应用场景
项目遇到很多导出excel或者word,但表格设计是非常复杂,且格式不变不有任何不对,如自动生成公文内容等。
2.设计思路
a.将Excel或 word另存为xml 格式 ,然后保存为 flt/vm格式。
b.基于 freemarker或velocity对模板进行编辑,设置动态变量。
c.根据模板生成文件。
3.实现
3.1 velocity生成工具
public class VelocityTemplateEngine extends AbstractTemplateEngine {
private static final String ENCODING = "UTF-8";
private static final VelocityEngine velocityEngine = new VelocityEngine();
private Context innerContext;
public VelocityTemplateEngine() throws TemplateEngineException {
this("/", new ClasspathResourceLoader(), null);
}
public VelocityTemplateEngine(String resourceLoaderPath) throws TemplateEngineException {
this(resourceLoaderPath, new ClasspathResourceLoader(), null);
}
public VelocityTemplateEngine(String resourceLoaderPath, Properties velocityProperties)
throws TemplateEngineException {
this(resourceLoaderPath, new ClasspathResourceLoader(), velocityProperties);
}
public VelocityTemplateEngine(String resourceLoaderPath, ResourceLoader resourceLoader)
throws TemplateEngineException {
this(resourceLoaderPath, resourceLoader, null);
}
public VelocityTemplateEngine(String resourceLoaderPath, ResourceLoader resourceLoader,
Properties velocityProperties) throws TemplateEngineException {
Properties props = new Properties();
props.setProperty(Velocity.INPUT_ENCODING, ENCODING);
props.setProperty(Velocity.OUTPUT_ENCODING, ENCODING);
props.setProperty(Velocity.ENCODING_DEFAULT, ENCODING);
props.setProperty("file.resource.loader.class", resourceLoader.getClass().getName());
props.setProperty(Velocity.FILE_RESOURCE_LOADER_PATH, resourceLoaderPath);
if (velocityProperties != null) {
props.putAll(velocityProperties);
}
velocityEngine.init(props);
try {
this.innerContext = new VelocityContext();
//下面这些工具可以在vm模板中直接使用,如:$numberTool.format("#0.00", $i)
this.innerContext.put("dateTool", new org.apache.velocity.tools.generic.DateTool());
this.innerContext.put("numberTool", new org.apache.velocity.tools.generic.NumberTool());
// 显示工具,如对list数据逗号分隔显示、null替换显示、限制长度、大小写首字母
this.innerContext.put("displayTool", new org.apache.velocity.tools.generic.DisplayTool());
this.innerContext.put("escapeTool", new org.apache.velocity.tools.generic.EscapeTool());
this.innerContext.put("sortTool", new org.apache.velocity.tools.generic.SortTool());
this.innerContext.put("loopTool", new org.apache.velocity.tools.generic.LoopTool());
this.innerContext.put("mathTool", new org.apache.velocity.tools.generic.MathTool());
} catch (VelocityException e) {
throw new TemplateEngineException("velocity template error.", e);
}
}
@Override
public void process(String templateName, Writer writer, Map<String, Object> data, String encoding)
throws TemplateEngineException {
try {
velocityEngine.mergeTemplate(templateName, encoding, new VelocityContext(data, innerContext), writer);
writer.flush();
} catch (ResourceNotFoundException e) {
throw new TemplateEngineException("resource not be found error.", e);
} catch (ParseErrorException e) {
throw new TemplateEngineException("parse template error.", e);
} catch (MethodInvocationException e) {
throw new TemplateEngineException("method invoke error.", e);
} catch (Exception e) {
throw new TemplateEngineException(e);
}
}
/**
* 根据模板字符串生成
*
* @param templateContent
* @param data
* @return
* @throws TemplateEngineException
*/
public String evaluateByTemplate(String templateContent, Map<String, Object> data) throws TemplateEngineException {
try {
StringWriter writer = new StringWriter();
velocityEngine.evaluate(new VelocityContext(data, innerContext), writer, "", templateContent);
writer.flush();
return writer.toString();
} catch (ResourceNotFoundException e) {
throw new TemplateEngineException("resource not be found error.", e);
} catch (ParseErrorException e) {
throw new TemplateEngineException("parse template error.", e);
} catch (MethodInvocationException e) {
throw new TemplateEngineException("method invoke error.", e);
} catch (Exception e) {
throw new TemplateEngineException(e);
}
}
3.2freemarker模板生成工具
public class FreemarkerTemplateEngine extends AbstractTemplateEngine {
private static final String ENCODING = "UTF-8";
private Configuration configuration;
public FreemarkerTemplateEngine() {
this("/");
}
/**
* 默认从classpath中resourceLoaderPath加载,如果templateName有了包路径,
* 可以设置resourceLoaderPath="/"
*
* @param resourceLoaderPath
* 模板加载路径,多个路径用","隔开
*/
public FreemarkerTemplateEngine(String resourceLoaderPath) throws TemplateEngineException {
this(resourceLoaderPath, new DefaultResourceLoader());
}
@SuppressWarnings("deprecation")
public FreemarkerTemplateEngine(String resourceLoaderPath, ResourceLoader resourceLoader)
throws TemplateEngineException {
FreeMarkerConfigurationFactory fcf = new FreeMarkerConfigurationFactory();
fcf.setDefaultEncoding(ENCODING);
fcf.setPreferFileSystemAccess(false);
fcf.setResourceLoader(resourceLoader);
fcf.setTemplateLoaderPaths(StringUtils.commaDelimitedListToStringArray(resourceLoaderPath));
try {
this.configuration = fcf.createConfiguration();
this.configuration.setObjectWrapper(new DefaultObjectWrapper());
this.configuration.setDateFormat("yyyy-MM-dd");
this.configuration.setDateTimeFormat("yyyy-MM-dd hh:mm:ss");
this.configuration.setNumberFormat("0.######");
} catch (IOException e) {
throw new TemplateEngineException("IO error.", e);
} catch (TemplateException e) {
throw new TemplateEngineException("Template error.", e);
}
}
@Override
public void process(String templateName, Writer writer, Map<String, Object> data,
String encoding) throws TemplateEngineException {
try {
Template template = this.configuration.getTemplate(templateName, encoding);
template.process(data, writer);
writer.flush();
} catch (IOException e) {
throw new TemplateEngineException("IO error.", e);
} catch (TemplateException e) {
throw new TemplateEngineException("Template error.", e);
}
}
@Override
public String evaluateByTemplate(String templateContent, Map<String, Object> data) throws TemplateEngineException {
//@TODO
throw new UnsupportedOperationException();
}
}
3.3 另存为xml后再定制Excel或Word模板
3.4 根据模板生成xls
public static void main(String[] args) throws FileNotFoundException{
FreemarkerTemplateEngine t =new FreemarkerTemplateEngine();
Map<String, Object> beanMap = new HashMap<String, Object>();
beanMap.put("entityName", "User");// 实体类名
beanMap.put("interfaceName", "User");// 接口名
beanMap.put("name", "经三");//测试
List<Map<String, String>> paramsList = new ArrayList<Map<String, String>>();
for (int i = 0; i < 4; i++) {
Map<String, String> tmpParamMap = new HashMap<String, String>();
tmpParamMap.put("fieldNote", "fieldNote" + i);
tmpParamMap.put("fieldType", "String");
tmpParamMap.put("fieldName", "fieldName" + i);
paramsList.add(tmpParamMap);
}
beanMap.put("params", paramsList);
//System.out.println(t.process("templates/free.flt", beanMap));
FileOutputStream f = new FileOutputStream("d:/test111.xls");
t.process("templates/excel.flt", f, beanMap);
}
以上是 设计通用导出Excel和Word方案 的全部内容, 来源链接: utcz.com/z/516982.html