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

浅浅的花香味﹌ 2021-09-11 03:40 284阅读 0赞

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

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

![Center][]

然后，就可以先编程了

先提供一个封装的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;
    	}

![Center 1][]

![Center 2][]

[Center]: /images/20210813/cc67d02b343e401489b3a5b28d0a1815.png
[Center 1]: /images/20210813/37ae39a8118a4e23acf84242d4c9d30b.png
[Center 2]: /images/20210813/86e3fdea18334cda995d870e2a9fba5b.png