设计通用导出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

回到顶部