1、maven需要的jar包
<!-- exl导出 -->
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
2、工具类 ExportZsjh.java
package com.yufei.core.util.excel;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import com.yufei.core.util.Const;
import com.yufei.core.util.DateUtil;
import com.yufei.core.util.PathUtil;
import com.yufei.erp.basis.model.my.MySysDeptPerson;
public class ExportZsjh {
/**
* 导出excel表
* 1、response
* 2、userinfo session对象
* 3、list 填充到excel模板中的数据
* 4、temp 模板表名(如:zsyjh)
* 5、downloadName 下载后文件名称(如:月度计划汇总表_)
*/
public static Map<String, Object> exportExcel(HttpServletResponse response,MySysDeptPerson userinfo,List<?> list,String temp,String downloadName) throws Exception{
Map<String, Object> map = new HashMap<String,Object>();
//模板地址
String templetPath = PathUtil.getClassResources() + "excel/" + temp + ".xls";
// 文件写入地址
String tempOut = Const.ZSYJH_ATTACH_ROOTPATH; //这里采用配置的地址,根据实际情况修改
File filexls = new File(tempOut);
if (!filexls.exists() && !filexls.isDirectory()) {
//不存在则创建
filexls.mkdirs();
}
String exportPath = filexls.getPath() + "/" + temp + "_" + DateUtil.getDays() + "_" + userinfo.getRealName() + ".xls";
File filetemp=new File(exportPath);
if(!filetemp.exists()) {
//不存在则创建
filetemp.createNewFile();
}
// 渲染模板文件
Map<String, Object> beanParams = new HashMap<String, Object>();
beanParams.put("zsjhList", list);
ExportUtils.writeExcel(templetPath, exportPath, beanParams);
BufferedInputStream in = null;
BufferedOutputStream out = null;
try{
String name = downloadName + DateUtil.getDays() + "_" + userinfo.getRealName() + ".xls";
String fileName = new String(name.getBytes("UTF-8"), "ISO8859-1");
response.setContentType("application/x-excel");
// response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "inline; filename=" + fileName);
response.setHeader("Content-Length", String.valueOf(filetemp.length()));
in = new BufferedInputStream(new FileInputStream(filetemp));
out = new BufferedOutputStream(response.getOutputStream());
byte[] data = new byte[1024];
int len = 0;
while (-1 != (len = in.read(data, 0, data.length))) {
out.write(data, 0, len);
}
map.put("msg", "success");
} catch(Exception e){
e.printStackTrace();
} finally {
if (in != null) {
in.close();
}
if (out != null) {
out.close();
}
}
return map;
}
/**
* 导出word表
* 1、response
* 2、userinfo session对象
* 3、list 填充到excel模板中的数据
* 4、temp 模板表名(如:zsyjh)
* 5、downloadName 下载后文件名称(如:月度计划汇总表_)
*/
public static Map<String, Object> exportWord(HttpServletResponse response,MySysDeptPerson userinfo,List<?> list,String temp,String downloadName) throws Exception{
Map<String, Object> map = new HashMap<String,Object>();
File file = null;
InputStream fin = null;
ServletOutputStream out = null;
String templetPath = PathUtil.getClassResources() + "excel/" ; //word模板存放地址
String templateName = temp + ".ftl";
// 文件写入地址
String tempOut = Const.ZSYJH_ATTACH_ROOTPATH;
File filexls = new File(tempOut);
if (!filexls.exists() && !filexls.isDirectory()) {
//不存在则创建
filexls.mkdirs();
}
String exportPath = filexls.getPath() + "/" + temp + "_" + DateUtil.getDays() + "_" + userinfo.getRealName() + ".doc"; //导出的word文件存放地址
File filetemp=new File(exportPath);
if(!filetemp.exists()) {
//不存在则创建
filetemp.createNewFile();
}
// 渲染模板文件
Map<String, Object> beanParams = new HashMap<String, Object>();
beanParams.put("zsjhList", list);
try{
String name = downloadName + DateUtil.getDays() + "_" + userinfo.getRealName() + ".doc";
file = ExportUtils.writeWord(beanParams,templetPath,templateName,exportPath,name);
fin = new FileInputStream(file);
response.setCharacterEncoding("utf-8");
response.setContentType("application/msword");
response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(name, "UTF-8"))));
out = response.getOutputStream();
byte[] buffer = new byte[512]; // 缓冲区
int bytesToRead = -1;
// 通过循环将读入的Word文件的内容输出到浏览器中
while((bytesToRead = fin.read(buffer)) != -1) {
out.write(buffer, 0, bytesToRead);
}
map.put("msg", "success");
} catch(Exception e){
e.printStackTrace();
} finally {
if(fin != null) fin.close();
if(out != null) out.close();
//if(file != null) file.delete(); // 删除临时文件
}
return map;
}
}
3、工具类 ExportUtils.java
package com.yufei.core.util.excel;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.List;
import java.util.Map;
import freemarker.template.Configuration;
import freemarker.template.Template;
/**
* excel文件模板方法导出
*
* @author hedh
*/
public class ExportUtils {
/**
* 渲染模板,然后生成新的excel文件
*
* @param templetPath
* excel模板存放地址
* @param exportPath
* 导出的excel文件存放地址
* @param beanParams
* 填充到excel模板中的数据
*/
public static void writeExcel(String templetPath, String exportPath, Map<String, Object> beanParams) {
try {
InputStream is = new FileInputStream(templetPath);
// 关联模板
XLSTransformer transformer = new XLSTransformer();
//这里导出.xls文件,需要导出.xlsx的文件需要换成HSSFWorkbook
XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(is, beanParams);
OutputStream os = new FileOutputStream(exportPath);
workBook.write(os);
is.close();
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 渲染模板,然后生成新的word文件
*
* @param dataMap
* 目标数据
* @param templetPath
* word模板存放地址
* @param templateName
* word模板名称
* @param filePath
* 导出的word文件存放地址
* @param fileName
* 文件名称
*/
public static File writeWord(Map dataMap, String templetPath, String templateName, String filePath, String fileName) {
try {
// 创建配置实例
Configuration configuration = new Configuration();
// 设置编码
configuration.setDefaultEncoding("UTF-8");
// ftl模板文件
configuration.setDirectoryForTemplateLoading(new File(templetPath));
// 获取模板
Template template = configuration.getTemplate(templateName);
// 输出文件
File outFile = new File(filePath);
// 将模板和数据模型合并生成文件
Writer out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8"));
// 生成文件
template.process(dataMap, out);
// 关闭流
out.flush();
out.close();
return outFile;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}
4、测试类 (根据实际数据情况修改)
/**
* 导出正式月计划
*/
@RequestMapping(value = "/exportYjh", method = RequestMethod.GET)
@ResponseBody
public Map<String, Object> exportYjh(HttpServletResponse response) throws Exception{
Map<String, Object> map = new HashMap<String,Object>();
PageData pd = new PageData();
//参数
pd = this.getPageData();
MySysDeptPerson userinfo = (MySysDeptPerson) pd.getSession("userInfo");// 获取session的值
//获取正式月计划集合
List<LjyyxYdjdjhb> list = ljyyxydjdjhbService.selectZsYdjdjhbList(pd);
//分组后的数据
Map<String, List<LjyyxYdjdjhb>> resultMap = new HashMap<String, List<LjyyxYdjdjhb>>();
//最后需要的数据
List<MyLjyyxYdjdjhbExport> exportList = new ArrayList<MyLjyyxYdjdjhbExport>();
String temp = "zsyjh"; //模板名称,最好使用英文
String downloadName = "月度计划汇总表_"; //导出文件名称
try{
String group = pd.getString("group");
//对数据进行分组
if(list != null && list.size() >0){
if("bh".equals(group)){
resultMap.put("", list);
}else {
for (LjyyxYdjdjhb ljyyxYdjdjhb : list) {
if("jldw".equals(group)){
//监理单位可以为空
if(resultMap.containsKey(null != ljyyxYdjdjhb.getJldwfzr() && ljyyxYdjdjhb.getJldwfzr().length() > 0 ? ljyyxYdjdjhb.getJldwfzr().split(",")[0] : "")){//map中异常批次已存在,将该数据存放到同一个key(key存放的是异常批次)的map中
resultMap.get(null != ljyyxYdjdjhb.getJldwfzr() && ljyyxYdjdjhb.getJldwfzr().length() > 0 ? ljyyxYdjdjhb.getJldwfzr().split(",")[0] : "").add(ljyyxYdjdjhb);
} else{//map中不存在,新建key,用来存放数据
List<LjyyxYdjdjhb> tmpList = new ArrayList<LjyyxYdjdjhb>();
tmpList.add(ljyyxYdjdjhb);
resultMap.put(null != ljyyxYdjdjhb.getJldwfzr() && ljyyxYdjdjhb.getJldwfzr().length() > 0 ? ljyyxYdjdjhb.getJldwfzr().split(",")[0] : "", tmpList);
}
}else if("jsdw".equals(group)){
if(resultMap.containsKey(ljyyxYdjdjhb.getJsdwmc())){//map中异常批次已存在,将该数据存放到同一个key(key存放的是异常批次)的map中
resultMap.get(ljyyxYdjdjhb.getJsdwmc()).add(ljyyxYdjdjhb);
} else{//map中不存在,新建key,用来存放数据
List<LjyyxYdjdjhb> tmpList = new ArrayList<LjyyxYdjdjhb>();
tmpList.add(ljyyxYdjdjhb);
resultMap.put(ljyyxYdjdjhb.getJsdwmc(), tmpList);
}
}else if("spcs".equals(group)){
if(resultMap.containsKey(ljyyxYdjdjhb.getSpcsmc())){//map中异常批次已存在,将该数据存放到同一个key(key存放的是异常批次)的map中
resultMap.get(ljyyxYdjdjhb.getSpcsmc()).add(ljyyxYdjdjhb);
} else{//map中不存在,新建key,用来存放数据
List<LjyyxYdjdjhb> tmpList = new ArrayList<LjyyxYdjdjhb>();
tmpList.add(ljyyxYdjdjhb);
resultMap.put(ljyyxYdjdjhb.getSpcsmc(), tmpList);
}
}
}
}
Set<String> keySet = resultMap.keySet(); //Map中的key
for (String groupName : keySet) {
MyLjyyxYdjdjhbExport mye = new MyLjyyxYdjdjhbExport();
mye.setName(groupName);
if("1".equals(pd.get("searchXdLx"))){
mye.setType("月计划");
}else{
mye.setType("增补月计划");
}
mye.setList(resultMap.get(groupName));
exportList.add(mye);
}
}
String type = pd.getString("type");
if("excel".equals(type)){
map = ExportZsjh.exportExcel(response,userinfo,exportList,temp,downloadName);
} else {
map = ExportZsjh.exportWord(response,userinfo,exportList,temp,downloadName);
}
} catch(Exception e){
logger.error(e.toString(), e);
// 记录到错误日志表
this.saveErrorLog("月计划导出表", "月计划导出表", e);
map.put("msg", this.getCommonErrorMessage(e)); // 返回提示信息
}
return map;
}
5、模板数据类 MyLjyyxYdjdjhbExport.java
package com.yufei.erp.business.model.yjhmanagement.ljyyxydjdjhb.my;
import java.util.List;
import com.yufei.erp.business.model.rjhmanagement.ljyyxrjdjhb.my.MyLjyyxRjdjhb;
import com.yufei.erp.business.model.yjhmanagement.ljyyxydjdjhb.LjyyxYdjdjhb;
import com.yufei.erp.business.model.zjhmanagement.ljyyxzjdjhb.LjyyxZjdjhb;
public class MyLjyyxYdjdjhbExport {
private String name;
private List<LjyyxYdjdjhb> list; //月计划
private List<LjyyxZjdjhb> zList; //周计划
private List<MyLjyyxRjdjhb> rjhList; //日计划
private String type;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<LjyyxYdjdjhb> getList() {
return list;
}
public void setList(List<LjyyxYdjdjhb> list) {
this.list = list;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public List<LjyyxZjdjhb> getzList() {
return zList;
}
public void setzList(List<LjyyxZjdjhb> zList) {
this.zList = zList;
}
public List<MyLjyyxRjdjhb> getRjhList() {
return rjhList;
}
public void setRjhList(List<MyLjyyxRjdjhb> rjhList) {
this.rjhList = rjhList;
}
}
6、模板
Excel(这里使用到两层List)
Word(比较复杂,需要先转换成ftl文件)
(1)建一个word文件,根据数据把字段填入模板中
(2)将word另存为xml
(3)将xml扩展名改为ftl
(4)利用在线格式化工具把ftl里面的内容格式化一下(转换成的ftl文件内容都挤到一坨了,不利于修改)
(5)搜索 w:tr 可以找到行的起点与结束点(注意第一对w:tr 是表头,应找第二对 w:tr)
(6)用<#list userList as user> </#list>标签将第二对 w:tr 标签包围起来(userList是集合的key, user是集合中的每个元素, 类似<c:forEach items=‘userList’ var=‘user’>) (网上教程应该很多,不懂的可以百度一下,这里就不多做解释了)