SSM框架使用POI技术导出Excel表

POI框架是Apache开源的可以导出导入Excel表的，本博客介绍在SSM(Spring+SpringMVC+Mybatis)项目里，如何使用POI框架，导出Excel表

这里我们先要去Apache官网下载jar

然后，就可以先编程了

先提供一个封装的httpservlet请求和添加数据的类

public class PageData extends HashMap implements Map{private static final long serialVersionUID = 1L;Map map = null;HttpServletRequest request;public PageData(HttpServletRequest request){this.request = request;Map properties = request.getParameterMap();Map returnMap = new HashMap(); Iterator entries = properties.entrySet().iterator(); Map.Entry entry; String name = "";  String value = "";  while (entries.hasNext()) {entry = (Map.Entry) entries.next(); name = (String) entry.getKey(); Object valueObj = entry.getValue(); if(null == valueObj){ value = ""; }else if(valueObj instanceof String[]){ String[] values = (String[])valueObj;for(int i=0;i<values.length;i++){  value = values[i] + ",";}value = value.substring(0, value.length()-1); }else{value = valueObj.toString(); }returnMap.put(name, value); }map = returnMap;}public PageData() {map = new HashMap();}@Overridepublic Object get(Object key) {Object obj = null;if(map.get(key) instanceof Object[]) {Object[] arr = (Object[])map.get(key);obj = request == null ? arr:(request.getParameter((String)key) == null ? arr:arr[0]);} else {obj = map.get(key);}return obj;}public String getString(Object key) {return (String)get(key);}@SuppressWarnings("unchecked")@Overridepublic Object put(Object key, Object value) {return map.put(key, value);}@Overridepublic Object remove(Object key) {return map.remove(key);}public void clear() {map.clear();}public boolean containsKey(Object key) {// TODO Auto-generated method stubreturn map.containsKey(key);}public boolean containsValue(Object value) {// TODO Auto-generated method stubreturn map.containsValue(value);}public Set entrySet() {// TODO Auto-generated method stubreturn map.entrySet();}public boolean isEmpty() {// TODO Auto-generated method stubreturn map.isEmpty();}public Set keySet() {// TODO Auto-generated method stubreturn map.keySet();}@SuppressWarnings("unchecked")public void putAll(Map t) {// TODO Auto-generated method stubmap.putAll(t);}public int size() {// TODO Auto-generated method stubreturn map.size();}public Collection values() {// TODO Auto-generated method stubreturn map.values();}}

写个实体类：

会员类

public class Member {/** * 会员账号 */private String memberID;/** * 会员密码 */private String password;/** * 会员级别 */private String rank;/** * 会员积分 */private int credit;/** * 会员手机号 */private String phone;/** * 会员皮肤 */private String imgPath;private List<GroupPost> postes;public List<GroupPost> getPostes() {return postes;}public void setPostes(List<GroupPost> postes) {this.postes = postes;}public String getMemberID() {return memberID;}public void setMemberID(String memberID) {this.memberID = memberID;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getRank() {return rank;}public void setRank(String rank) {this.rank = rank;}public int getCredit() {return credit;}public void setCredit(int credit) {this.credit = credit;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getImgPath() {return imgPath;}public void setImgPath(String imgPath) {this.imgPath = imgPath;}}

DAO.java

package com.appweb.core.dao;public interface DAO {/** * 保存对象 * @param str * @param obj * @return * @throws Exception */public Object save(String str, Object obj) throws Exception;/** * 修改对象 * @param str * @param obj * @return * @throws Exception */public Object update(String str, Object obj) throws Exception;/** * 删除对象  * @param str * @param obj * @return * @throws Exception */public Object delete(String str, Object obj) throws Exception;/** * 查找对象 * @param str * @param obj * @return * @throws Exception */public Object findForObject(String str, Object obj) throws Exception;/** * 查找对象 * @param str * @param obj * @return * @throws Exception */public Object findForList(String str, Object obj) throws Exception;/** * 查找对象封装成Map * @param s * @param obj * @return * @throws Exception */public Object findForMap(String sql, Object obj, String key , String value) throws Exception;}

DAOSupport类：

package com.appweb.core.dao;import java.util.List;import javax.annotation.Resource;import org.apache.ibatis.session.ExecutorType;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionTemplate;import org.springframework.stereotype.Repository;@Repository("daoSupport")public class DaoSupport implements DAO {@Resource(name = "sqlSessionTemplate")private SqlSessionTemplate sqlSessionTemplate;/** * 保存对象 * @param str * @param obj * @return * @throws Exception */public Object save(String str, Object obj) throws Exception {return sqlSessionTemplate.insert(str, obj);}/** * 批量更新 * @param str * @param obj * @return * @throws Exception */public Object batchSave(String str, List objs )throws Exception{return sqlSessionTemplate.insert(str, objs);}/** * 修改对象 * @param str * @param obj * @return * @throws Exception */public Object update(String str, Object obj) throws Exception {return sqlSessionTemplate.update(str, obj);}/** * 批量更新 * @param str * @param obj * @return * @throws Exception */public void batchUpdate(String str, List objs )throws Exception{SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();//批量执行器SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);try{if(objs!=null){for(int i=0,size=objs.size();i<size;i++){sqlSession.update(str, objs.get(i));}sqlSession.flushStatements();sqlSession.commit();sqlSession.clearCache();}}finally{sqlSession.close();}}/** * 批量更新 * @param str * @param obj * @return * @throws Exception */public Object batchDelete(String str, List objs )throws Exception{return sqlSessionTemplate.delete(str, objs);}/** * 删除对象  * @param str * @param obj * @return * @throws Exception */public Object delete(String str, Object obj) throws Exception {return sqlSessionTemplate.delete(str, obj);} /** * 查找对象 * @param str * @param obj * @return * @throws Exception */public Object findForObject(String str, Object obj) throws Exception {return sqlSessionTemplate.selectOne(str, obj);}/** * 查找对象 * @param str * @param obj * @return * @throws Exception */public Object findForList(String str, Object obj) throws Exception {return sqlSessionTemplate.selectList(str, obj);}public Object findForMap(String str, Object obj, String key, String value) throws Exception {return sqlSessionTemplate.selectMap(str, obj, key);}}

写个Service类:

/** * 会员信息列表 * @param pd * @return * @throws Exception */public List<PageData> listM(PageData pd)throws Exception{return (List<PageData>)dao.findForList("MemberMapper.memberList", pd);}

ObjectExcelView.java:

package com.appweb.core.view;import java.util.Date;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.springframework.web.servlet.view.document.AbstractExcelView;import com.appweb.core.entity.PageData;import com.appweb.core.utils.Tools;/*** 导入到EXCEL* 类名称：ObjectExcelView.java* 类描述：  */public class ObjectExcelView extends AbstractExcelView{@Overrideprotected void buildExcelDocument(Map<String, Object> model,HSSFWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {// TODO Auto-generated method stubDate date = new Date();String filename = Tools.date2Str(date, "yyyyMMddHHmmss");HSSFSheet sheet;HSSFCell cell;response.setContentType("application/octet-stream");response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");sheet = workbook.createSheet("sheet1");List<String> titles = (List<String>) model.get("titles");int len = titles.size();HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);HSSFFont headerFont = workbook.createFont();//标题字体headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);headerFont.setFontHeightInPoints((short)11);headerStyle.setFont(headerFont);short width = 20,height=25*20;sheet.setDefaultColumnWidth(width);for(int i=0; i<len; i++){ //设置标题String title = titles.get(i);cell = getCell(sheet, 0, i);cell.setCellStyle(headerStyle);setText(cell,title);}sheet.getRow(0).setHeight(height);HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);List<PageData> varList = (List<PageData>) model.get("varList");int varCount = varList.size();for(int i=0; i<varCount; i++){PageData vpd = varList.get(i);for(int j=0;j<len;j++){String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";cell = getCell(sheet, i+1, j);cell.setCellStyle(contentStyle);setText(cell,varstr);}}}}

控制类导出Excel表：

/** * 导出Excel * @return * @throws Exception */@RequestMapping("/exportExcel")public ModelAndView exportExcel()throws Exception{ModelAndView mv = this.getModelAndView();PageData pd = new PageData();pd = this.getPageData();//检索条件Map<String,Object> dataMap = new HashMap<String,Object>();List<String> titles = new ArrayList<String>();titles.add("用户名");titles.add("密码");titles.add("级别");titles.add("积分");titles.add("手机号");dataMap.put("titles", titles);List<PageData> memberList = memberService.listM(pd);List<PageData> varList = new ArrayList<PageData>();for(int i=0;i<memberList.size();i++){PageData vpd = new PageData();vpd.put("var1", memberList.get(i).getString("memberID"));vpd.put("var2", memberList.get(i).getString("password"));vpd.put("var3", memberList.get(i).getString("rank"));vpd.put("var4", memberList.get(i).get("credit").toString());vpd.put("var5", memberList.get(i).getString("phone"));varList.add(vpd);}dataMap.put("varList", varList);ObjectExcelView erv = new ObjectExcelView();mv = new ModelAndView(erv,dataMap);return mv;}