import java.io.IOException; import java.io.PrintWriter; import java.sql.SQLException; import java.util.Iterator; import java.util.List; import javax.faces.context.FacesContext; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import org.hibernate.HibernateException; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.springframework.context.ApplicationContext; import org.springframework.orm.hibernate3.HibernateCallback; import org.springframework.orm.hibernate3.HibernateTemplate; import org.springframework.web.context.support.WebApplicationContextUtils; public class ExcelReport { //参数sheetName为excel sheet的名字, //session通过type参数获取sql,title为excel 每一列的标题 @SuppressWarnings("unchecked") public void listToExcelHtml(String sheetName, String type, Object[] title) { List list = getExportList(type); try { HttpServletResponse httpServletResponse = (HttpServletResponse) General .getExternalContext().getResponse(); httpServletResponse .setContentType("application/vnd.ms-excel; charset=utf-8"); httpServletResponse.setCharacterEncoding("utf-8"); httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + sheetName + ".xls"); PrintWriter out = httpServletResponse.getWriter(); out.write("<html><head><meta http-equiv='Content-Type' content='application/vnd.ms-excel; charset=UTF-8'>"); out.write("<title>" + sheetName + "</title>"); out.write("</head><body>"); out.write("<table border=1>"); list.add(0, title); Iterator it = list.iterator(); while (it.hasNext()) { out.write("<tr>"); Object[] obj = (Object[]) it.next(); for (int i = 0; i < obj.length; i++) { out.write("<td>" + ((obj[i] == null) ? "" : obj[i]) + "</td>"); } out.write("</tr>"); } out.write("</table></body></html>"); out.flush(); out.close(); } catch (IOException ex) { ex.printStackTrace(); } finally { FacesContext.getCurrentInstance().responseComplete(); } } //session通过type参数的值得到sql查询语句 //获取list 的值,list 为从数据库中查询得到, @SuppressWarnings("unchecked") public List getExportList(String type) { final String queryString = (String) General.getSession(type); return getHibernateTemplate().executeFind(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { // TODO Auto-generated method stub SQLQuery sqlQuery = session.createSQLQuery(queryString); return sqlQuery.list(); } }); } // 根据xml 的bean id 为hibernateTemplate 获取HibernateTemplate对象 public HibernateTemplate getHibernateTemplate() { // TODO Auto-generated method stub ApplicationContext ctx = WebApplicationContextUtils .getWebApplicationContext(((HttpSession) General .getExternalContext().getSession(true)) .getServletContext()); return (HibernateTemplate) ctx.getBean("hibernateTemplate"); } }