ssh整合web导出excel案例.doc

基于Spring,Struts2,Hibernate整合，jsp页面导出（下载）excel文件的简单应用1）本例不采用Java任何导出excel文件常用的poi或者jxl等第三方jar包，仅仅基于I/O，做一个最简单的ssh整合excel文件导出（下载）。2）2-3为功能展示a.主页展示导出当前页到excel文件导出全部到excel文件b.详细信息展示(下属子地名)导出当前页到excel文件3）4-7为ssh整合相关xml配置a. Spring相关xml配置b. Hibernate相关xml配置c. Struts2相关xml配置d. web.xml配置(Spring容器的实例化，struts过滤器的配置)3）8-14为源代码a.action代码b.service代码(导出excel文件具体实现，包括下载文件名中文乱码问题，详见代码注释)c.dao代码4）15-17为jsp页面a.index.jsp(主页)b.list.jsp(列表显示)c.detail.jsp(详细信息-子地址列表显示)1.导入相关jar包(ssh+dbcp)2.本例导出全国地址详细信息，数据库中数据内容如下：3.功能展示a.主页 b.导出当前页(文件名为当前页起始id到结束id)c.导出当前页的excel文件内容d.导出全部e.下属地名详细信息f.导出下属地名4.spring配置 org.hibernate.dialect.MySQLDialect true true com/luo/ssh/entity/Address.hbm.xml 5.hibernate映射配置(基本) 6.struts配置36/list.jsp/detail.jsp7.web.xml配置contextConfigLocationclasspath:ssh.xmlorg.springframework.web.context.ContextLoaderListenerstruts2org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilterstruts2/* index.jsp 8.实体类Addresspackage com.luo.ssh.entity;import java.io.Serializable;/* * 地名类 * author 罗立明 * */public class Address implements Serializable /*地名的id，唯一*/private int id;/*地名*/private String name;/*地名的下属级别，直辖市，省，自治区为1，其他没下一级级别+1*/private int level;/*地名上一级的id，直辖市，省，自治区的上级id为0*/private int upid;/*保留字段，地名备注及描述*/private String info;public int getId() return id;public void setId(int id) this.id = id;public String getName() return name;public void setName(String name) = name;public int getLevel() return level;public void setLevel(int level) this.level = level;public int getUpid() return upid;public void setUpid(int upid) this.upid = upid;public String getInfo() return info;public void setInfo(String info) = info;Overridepublic int hashCode() return id16;Overridepublic boolean equals(Object obj) if (this = obj)return true;if (obj = null)return false;if (getClass() != obj.getClass()return false;Address other = (Address) obj;if (id != other.id)return false;return true;Overridepublic String toString() return id：+id+n地名：+name+n级别：+level+n上一级id：+upid;9.dao接口定义package com.luo.ssh.dao;import java.util.List;import com.luo.ssh.entity.Address;public interface AddressDao /* * 查询地址总数 * return */public int count();/* * 根据id查找地址 * param id * return */public Address findById(int id);/* * 根据id查找所有下级地址 * param address * return */public List findChildren(Address address);/* * 根据id查找该地址的详细信息，追溯到最高上级 * param id * return 结果形如：湖北省-荆州市-公安县-狮子口镇 */public String findDetailById(int id);/* * 根据地名模糊查询所有满足条件的地址 * param name * return */public List findLikeName(String name);/* * 分页查询地址信息 * param page * param pageSize * return */public List findPage(int page,int pageSize);/* * 查询全部 * return */public List findAll();10.dao的Hibernate实现package com.luo.ssh.dao.impl;import java.sql.SQLException;import java.util.List;import org.hibernate.HibernateException;import org.hibernate.Query;import org.hibernate.Session;import org.springframework.orm.hibernate3.HibernateCallback;import org.springframework.orm.hibernate3.HibernateTemplate;import org.springframework.orm.hibernate3.support.HibernateDaoSupport;import com.luo.ssh.dao.AddressDao;import com.luo.ssh.entity.Address;public class AddressDaoHibernateImpl extends HibernateDaoSupport implements AddressDao public Address findById(int id) HibernateTemplate template=getHibernateTemplate();Address address=(Address)template.get(Address.class, id);return address;public List findChildren(Address address) String hql=from Address where upid=?;return getHibernateTemplate().find(hql, address.getId();public String findDetailById(int id) Address add=findById(id);String detail=add.getName();while(add.getLevel()!=1)add=findById(add.getUpid();detail=add.getName()+-+detail;return detail;public List findLikeName(String name) String hql=from Address where name like ?;return getHibernateTemplate().find(hql, %+name+%);public int count() String hql=select count(*) from Address;return Integer.parseInt(getHibernateTemplate().find(hql).get(0).toString();public List findPage(final int page, final int pageSize) HibernateTemplate template=getHibernateTemplate();List list=(List)template.execute(new HibernateCallback() public Object doInHibernate(Session session) throws HibernateException,SQLException String hql=from Address;Query query=session.createQuery(hql);query.setFirstResult(page-1)*pageSize);query.setMaxResults(pageSize);return query.list(););return list;public List findAll() String hql=from Address;return getHibernateTemplate().find(hql);11.service接口定义package com.luo.ssh.service;import java.util.List;import javax.servlet.http.HttpServletResponse;import com.luo.ssh.entity.Address;public interface AddressService /* * 分页查找 * param page * param pageSize * return */List findPage(int page, int pageSize);/* * 统计总数 * return */int count();/* * 根据ID查找地址 * param id * return */Address findById(int id);/* * 查找当前地址的下属地址 * param address * return */List findChildred(Address address);/* * 导出当前页到excel文件 * param page * param pageSize * param response */void pageToExcel(int page, int pageSize,HttpServletResponse response);/* * 导出数据库全部信息到excel文件 * param response */void allToExcel(HttpServletResponse response);/* * 导出指定id的地址子地址到excel文件 * param id * param response */void childrenToExcel(int id, HttpServletResponse response);/* * 导出功能实现 * param list 要导出的数据 * param fileName 下载显示的文件名 * param response */void toExcel(List list,String fileName, HttpServletResponse response);/* * 根据id查找完整地址 * param id * return */String findDetailById(int id);12.service实现package com.luo.ssh.service.impl;import java.io.IOException;import java.io.OutputStream;import java.util.List;import javax.servlet.http.HttpServletResponse;import com.luo.ssh.dao.AddressDao;import com.luo.ssh.entity.Address;import com.luo.ssh.service.AddressService;public class AdressServiceImpl implements AddressService private AddressDao addressDao;public void setAddressDao(AddressDao addressDao) this.addressDao = addressDao;public AddressDao getAddressDao() return addressDao;public List findPage(int page, int pageSize) /page和pageSize的简单验证int total=count();if(page=0)page=1;if(pageSize100)page=100;int maxPage=total%pageSize=0?total/pageSize:total/pageSize+1;if(pagemaxPage)page=maxPage;return addressDao.findPage(page, pageSize);public int count() return addressDao.count();public Address findById(int id) return addressDao.findById(id);public List findChildred(Address address) return addressDao.findChildren(address);/导出当前页到excel文件public void pageToExcel(int page, int pageSize, HttpServletResponse response) /需要导出的数据List list=findPage(page,pageSize);/下载是显示给用户的文件名（如果是中文需要作一定处理，否则会有乱码）String fileName=(page-1)*pageSize+1+-+page*pageSize+.xls;/调用导出的具体实现方法toExcel(list,fileName,response);/导出数据库全部信息到excel文件public void allToExcel(HttpServletResponse response) /需要导出的数据List list=addressDao.findAll();/下载是显示给用户的文件名（如果是中文需要作一定处理，否则会有乱码）String fileName=all.xls;/调用导出的具体实现方法toExcel(list,fileName,response);public void childrenToExcel(int id, HttpServletResponse response) Address parent=new Address();parent.setId(id);List list=addressDao.findChildren(parent);/下载是显示给用户的文件名（如果是中文需要作一定处理，否则会有乱码）String fileName=addressDao.findDetailById(id)+_下属地址.xls;/调用导出的具体实现方法toExcel(list,fileName,response);/导出的具体实现方法public void toExcel(List list, String fileName, HttpServletResponse response) try /对文件名作处理，避免中文乱码问题String name = new String(fileName.getBytes(gbk),iso8859-1);/设置response相应属性，设置为下载response.setContentType(application/x-msdownload);response.setHeader(Content-Disposition, attachment;filename=+name);/获得response中的输出流OutputStream out=response.getOutputStream();/excel表格第一行标题，本例中不采用poi或jxl，只对每个单元属性后面添/加t，每行结束加n。这是excel文件的基本属性。String head=idtnametupidtleveln;/编码应该根据本地编码来确定。如果是台湾同胞，这里需要改为big5out.write(head.getBytes(gbk);for(Address a:list)/加上t,n等分隔符，写出文件，当文件以.xls命名时，打开即为普通excel文件 String s=a.getId()+t+a.getName()+t+a.getUpid()+t+a.getLevel()+n;out.write(s.getBytes(gbk);out.flush();out.close(); catch (IOException e) e.printStackTrace();public String findDetailById(int id) return addressDao.findDetailById(id);13.action代码package com.luo.ssh.action;import java.util.List;import com.luo.ssh.entity.Address;import com.luo.ssh.service.AddressService;public class AddressAction extends BaseAction /inputprivate int page=1;/页数private int pageSize;/每页显示数量private Address address;/用来接收对象参数/outputprivate int maxPage;/最大页数private List addresses;/数据集合private String detailStr;/详细地址:xx省-xx市-xx县-xx镇/属性private AddressService addressService;/方法/* * 列表显示 */public String list()int total=addressService.count();maxPage=total%pageSize=0?total/pageSize:total/pageSize+1;addresses=addressService.findPage(page,pageSize);return success;/* * 详细信息(下属子地址列表显示) */public String detail()address=addressService.findById(address.getId();addresses=addressService.findChildred(address);detailStr=addressService.findDetailById(address.getId();return success;/* * 导出当前页 */public void pageToExcel()addressService.pageToExcel(page, pageSize,response);/* * 导出数据库全部信息 */public void allToExcel()addressService.allToExcel(response);/* * 导出当前地址的子地址 */public void childrenToExcel()addressService.childrenToExcel(address.getId(),response);public void setPage(int page) this.page = page;public int getPage() return page;public void setPageSize(int pageSize) this.pageSize = pageSize;public int getPageSize() return pageSize;public void setAddressService(AddressService addressService) this.addressService = addressService;public AddressService getAddressService() return addressService;public void setMaxPage(int maxPage) this.maxPage = maxPage;public int getMaxPage() return maxPage;public void setAddresses(List addresses) this.addresses = addresses;public List getAddresses() return addresses;public void setAddress(Address address) this.address = address;public Address getAddress() return address;public void setDetailStr(String detailStr) this.detailStr = detailStr;public String getDetailStr() return detailStr;14.BaseAction类(只为方便使用response对象)package com.luo.ssh.action;import java.util.Map;import javax.servlet.ServletContext;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import erceptor.ApplicationAware;import erceptor.ParameterAware;import erceptor.RequestAware;import erceptor.ServletRequestAware;import erceptor.ServletResponseAware;import erceptor.SessionAware;import org.apache.struts2.util.ServletContextAware;import com.opensymphony.xwork2.ActionSupport;public class BaseAction extends ActionSupport implements RequestAware,SessionAware,ApplicationAware,ParameterAware,ServletContextAware,ServletRequestAware,ServletResp