先画好excle或word,另存为xml, 然后后缀改为.ftl,用freemaker取值。
如果是List,需要将List放入map
1、调用
public String export() throws SysException {
DocUtils doc = new DocUtils();
if (StringUtils.isNotBlank(id)) {
try {
pageInfo = payFreeDelegate.queryPage(model, getPageInfo());
List<PayFreeMVO> payFreeList = pageInfo.getRows();
List<Map<String, Object>> payMapList = new ArrayList<Map<String, Object>>();
for (PayFreeMVO payFree : payFreeList) {
Map<String, Object> dataMap = DataMapUtil.setObjToMap(payFree);
payMapList.add(dataMap);
}
Map<String ,Object> objMap = new HashMap<String, Object>();
objMap.put("list", payMapList);
doc.exportExcel(objMap,"payfree","渠道清缴费用" + DateTimeUtil.nowString("yyyy-MM-dd"),response);
} catch (AppException e) {
e.printStackTrace();
}
}
return null;
}
2、实现
package com.bjdv.cm.util;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts2.ServletActionContext;
import freemarker.template.Configuration;
import freemarker.template.DefaultObjectWrapper;
import freemarker.template.Template;
import freemarker.template.TemplateExceptionHandler;
public class DocUtils {
private Configuration configure = null;
public DocUtils() {
configure= new Configuration();
configure.setDefaultEncoding("utf-8");
}
/**
* 根据Doc模板生成word文件
* @param dataMap Map 需要填入模板的数据
* @param fileName 文件名称
* @param savePath 保存路径
*/
public void createDoc(Map<String, Object> dataMap, String downloadType, String savePath)
{
try
{
//加载需要装填的模板
Template template = null;
//加载模板文件
configure.setClassForTemplateLoading(this.getClass(),"temp");
//设置对象包装器
configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象,注意模板类型名字与downloadType要一致
template= configure.getTemplate(downloadType + ".xml");
//输出文档
File outFile = new File(savePath);
Writer out = null;
out= new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile),"utf-8"));
template.process(dataMap,out);
outFile.delete();
}
catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据Doc模板生成word文件
* @param dataMap Map 需要填入模板的数据
* @param fileName 文件名称
* @param savePath 保存路径
*/
public void createDoc(Map<String, Object> dataMap, String downloadType, String srcPath, String savePath)
{
try
{
//加载需要装填的模板
Template template = null;
//加载模板文件
//configure.setClassForTemplateLoading(this.getClass(),srcPath);
configure.setDirectoryForTemplateLoading(new File(srcPath));
//设置对象包装器
configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象,注意模板类型名字与downloadType要一致
template= configure.getTemplate(downloadType);
//输出文档
File outFile = new File(savePath);
Writer out = null;
out= new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile),"utf-8"));
template.process(dataMap,out);
outFile.delete();
}
catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据Doc模板生成word文件
* @param dataMap Map 需要填入模板的数据
* @param fileName 文件名称
* @param savePath 保存路径
*/
public void downloadeDoc(Map<String, Object> dataMap, String templateName,String fileName,HttpServletResponse response)
{
try
{
//加载需要装填的模板
Template template = null;
//加载模板文件
//configure.setClassForTemplateLoading(this.getClass(),srcPath);
String basePath = ServletActionContext.getServletContext().getRealPath(File.separator);
configure.setDirectoryForTemplateLoading(new File(basePath+"/template/simple"));
//设置对象包装器
configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象,注意模板类型名字与downloadType要一致
template= configure.getTemplate(templateName);
response.setCharacterEncoding("utf-8");
response.setContentType("application/msword");
response.addHeader("Content-Disposition", "attachment; filename="+fileName+new Date().getTime()+".doc");
OutputStream out = response.getOutputStream();
template.process(dataMap,new OutputStreamWriter(out,"utf-8"));
out.flush();
out.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据Doc模板生成word文件
* @param dataMap Map 需要填入模板的数据
* @param fileName 文件名称
* @param savePath 保存路径
*/
public void exportExcel(Map<String, Object> dataMap, String templateName, String fileName,HttpServletResponse response)
{
OutputStream out = null;
try
{
//加载需要装填的模板
Template template = null;
//加载模板文件
String basePath = ServletActionContext.getServletContext().getRealPath(File.separator);
configure.setDirectoryForTemplateLoading(new File(basePath+"/template/simple"));
//设置对象包装器
configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象,注意模板类型名字与downloadType要一致
template= configure.getTemplate(templateName+".ftl");
response.setCharacterEncoding("utf-8");
response.setContentType("application/msexcel");
response.addHeader("Content-Disposition", "attachment; filename=\""+ new String(fileName.getBytes("gb2312"),"iso8859-1")+".xls\"");
out = response.getOutputStream();
template.process(dataMap,new OutputStreamWriter(out,"utf-8"));
out.flush();
out.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据Doc模板生成word文件
* @param dataMap Map 需要填入模板的数据
* @param fileName 文件名称
* @param savePath 保存路径
*/
public void exportDoc(Map<String, Object> dataMap, String templateName, String fileName,HttpServletResponse response)
{
OutputStream out = null;
try
{
//加载需要装填的模板
Template template = null;
//加载模板文件
String basePath = ServletActionContext.getServletContext().getRealPath(File.separator);
configure.setDirectoryForTemplateLoading(new File(basePath+"/template/simple"));
//设置对象包装器
configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象,注意模板类型名字与downloadType要一致
template= configure.getTemplate(templateName+".ftl");
response.setCharacterEncoding("utf-8");
response.setContentType("application/msword");
response.addHeader("Content-Disposition", "attachment; filename=\""+ new String(fileName.getBytes("gb2312"),"iso8859-1")+".doc\"");
out = response.getOutputStream();
template.process(dataMap,new OutputStreamWriter(out,"utf-8"));
out.flush();
out.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据Doc模板生成word文件
* @param dataMap Map 需要填入模板的数据
* @param fileName 文件名称
* @param savePath 保存路径
*/
public void getsDoc(Map<String, Object> dataMap, String downloadType, String savePath)
{
try
{
//加载需要装填的模板
Template template = null;
//加载模板文件 //相对路径 本class 同包下的temp文件夹下加载模版
configure.setClassForTemplateLoading(this.getClass(),"/temp");
//设置对象包装器
configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象,注意模板类型名字与downloadType要一致
template= configure.getTemplate(downloadType);
//输出文档
File outFile = new File(savePath);
Writer out = null;
out= new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile),"utf-8"));
template.process(dataMap,out);
outFile.delete();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
3、ftl文件
<#list list as payFreeRec>
<Row>
<Cell>
<Data ss:Type="String">${payFreeRec.channelCode}</Data>
</Cell>
<Cell>
<Data ss:Type="String">${payFreeRec.channelName}</Data>
</Cell>
<Cell>
<Data ss:Type="String">${payFreeRec.areaId}</Data>
</Cell>
</Row>
</#list>
///-------------------拼写----------------------------
/**
* 导出工时 PUT: /moa/attend/attendancetemp/{id}/exportGongShi
*/
public String exportGongShi() throws SysException {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb;
try {
wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("学生表一");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("部门编号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("姓名");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("实际工作日数");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("备注");
cell.setCellStyle(style);
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
if (StringUtils.isNotEmpty(model.getYearMon())
&& model.getYearMon().contains("-")) {
model.setYearMon(model.getYearMon().replace("-", ""));
} else {
SimpleDateFormat format1 = new SimpleDateFormat("yyyMM");
String currentDate1 = format1.format(new Date());
model.setYearMon(currentDate1);
}
pageInfo = attendanceTempDelegate.queryPageReprot(model,
getPageInfo());
List<AttendanceTempMVO> tempList = pageInfo.getRows();
for (int i = 0; i < tempList.size(); i++) {
row = sheet.createRow((int) i + 1);
AttendanceTempMVO at = (AttendanceTempMVO) tempList.get(i);
// 第四步,创建单元格,并设置值
row.createCell((short) 0).setCellValue(at.getAcronym());
row.createCell((short) 1).setCellValue(at.getXingMing());
row.createCell((short) 2).setCellValue(at.getProjectName());
row.createCell((short) 3).setCellValue(at.getDescription());
row.createCell((short) 4).setCellValue(at.getChuQin());
row.createCell((short) 5).setCellValue("");
}
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename="
+ "工时统计" + DateTimeUtil.nowString("yyyy-MM-dd") + ".xls");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;