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();
      	}
     	@Override
     	public 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")
     	@Override
     	public Object put(Object key, Object value) {
     		return map.put(key, value);
      	}
     	@Override
     	public Object remove(Object key) {
     		return map.remove(key);
      	}
     	public void clear() {
      		map.clear();
      	}
     	public boolean containsKey(Object key) {
     		// TODO Auto-generated method stub
     		return map.containsKey(key);
      	}
     	public boolean containsValue(Object value) {
     		// TODO Auto-generated method stub
     		return map.containsValue(value);
      	}
     	public Set entrySet() {
     		// TODO Auto-generated method stub
     		return map.entrySet();
      	}
     	public boolean isEmpty() {
     		// TODO Auto-generated method stub
     		return map.isEmpty();
      	}
     	public Set keySet() {
     		// TODO Auto-generated method stub
     		return map.keySet();
      	}
     	@SuppressWarnings("unchecked")
     	public void putAll(Map t) {
     		// TODO Auto-generated method stub
      		map.putAll(t);
      	}
     	public int size() {
     		// TODO Auto-generated method stub
     		return map.size();
      	}
     	public Collection values() {
     		// TODO Auto-generated method stub
     		return 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{
     	@Override
     	protected void buildExcelDocument(Map<String, Object> model,
       HSSFWorkbook workbook, HttpServletRequest request,
       HttpServletResponse response) throws Exception {
     		// TODO Auto-generated method stub
      		Date 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;
      	}
  
 

文章来源: smilenicky.blog.csdn.net，作者：smileNicky，版权归原作者所有，如需转载，请联系作者。

原文链接：smilenicky.blog.csdn.net/article/details/52526551