当今，J2EE企业级开发中SpringMVC无疑占据着举足轻重的地位，而POI又很好的支持了Excel操作，这两者的结合很好的支持了企业级报表系统的开发，废话不多说了，直接进入主题；
1、首先，在项目的lib目录下导入SpringMVC相关的Jar包，同时，导入
2、创建entity对象：
package com.entity;
import java.io.Serializable;
public class PersonEntity implements Serializable {
private static final long serialVersionUID = 6916427667499861702L;
private Integer id;
private String name;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
3、创建dao接口和对应的实现类：
package com.dao;
import java.util.List;
import com.entity.PersonEntity;
public interface PersonEntityDao {
List<PersonEntity> getList();
}
dao接口的实现类
package com.dao.impl;
import com.dao.PersonEntityService;
import java.util.List;
import com.entity.PersonEntity;
@Repository
public class PersonEntityDaoImpl implements PersonEntityDao {
@Override
public List<PersonEntity> getList() {
//操作数据库
return null;
}
}
4、建立service接口和对应的实现类
package com.service;
import java.util.List;
import com.entity.PersonEntity;
public interface PersonEntityService {
List<PersonEntity> getList();
}
service实现类
package com.service.impl;
import java.util.List;
import com.dao.PersonEntityDao;
import com.entity.PersonEntity;
import com.service.PersonEntityService;
@Service
public class PersonEntityServiceImpl implements PersonEntityService {
@Resource
private PersonEntityDao personDao;
@Override
public List<PersonEntity> getList() {
// TODO Auto-generated method stub
return personDao.getList();
}
}
5、创建处理下载中文名称Excel的工具类（防止生成的Excel名称中有中文而出现乱码的现象）
package com.poi;
import java.net.URLEncoder;
import javax.mail.internet.MimeUtility;
import javax.servlet.http.HttpServletRequest;
import com.sinever.util.StringUtils;
public class MyUtils {
/**
* 设置下载文件中文件的名称
*
* @param filename
* @param request
* @return
*/
public static String encodeFilename(String filename, HttpServletRequest request) {
/**
* 获取客户端浏览器和操作系统信息
* 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
* 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
*/
String agent = request.getHeader("USER-AGENT");
try {
if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {
String newFileName = URLEncoder.encode(filename, "UTF-8");
newFileName = StringUtils.replace(newFileName, "+", "%20");
if (newFileName.length() > 150) {
newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");
newFileName = StringUtils.replace(newFileName, " ", "%20");
}
return newFileName;
}
if ((agent != null) && (-1 != agent.indexOf("Mozilla")))
return MimeUtility.encodeText(filename, "UTF-8", "B");
return filename;
} catch (Exception ex) {
return filename;
}
}
}
6、创建ViewExcel类，此类要继承AbstractExcelView类，覆写AbstractExcelView类中的buildExcelDocument方法
package com.sinever.poi;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.entity.PersonEntity;
public class ViewExcel extends AbstractExcelView {
@Override
protected void buildExcelDocument(Map<String, Object> obj,
HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
throws Exception {
// map的key，在对应的controller中设置
List<PersonEntity> list = (List<PersonEntity>) obj.get("list");
HSSFSheet sheet = workbook.createSheet("list");
sheet.setDefaultColumnWidth((short) 12);
HSSFCell cell = getCell(sheet, 0, 0);
setText(cell, "ID");
cell = getCell(sheet, 0, 1);
setText(cell, "姓名");
cell = getCell(sheet, 0, 2);
setText(cell, "年龄");
for (short i = 0; i < list.size(); i++) {
HSSFRow sheetRow = sheet.createRow(i+1);
PersonEntity entity = list.get(i);
sheetRow.createCell(0).setCellValue(entity.getId());
sheetRow.createCell(1).setCellValue(entity.getName());
sheetRow.createCell(2).setCellValue(entity.getAge());
}
//设置下载时客户端Excel的名称
String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
//处理中文文件名
filename = MyUtils.encodeFilename(filename, request);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
}
7、创建相应的controller类
package com.controller;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import com.rmi.entity.PersonEntity;
import com.service.PersonEntityService;
import com.sinever.poi.ViewExcel;
@Controller
@RequestMapping(value="/person")
public class PersonEntityController {
@Resourse
private PersonEntityService personEntityService;
@RequestMapping(value ="/export",method=RequestMethod.GET)
public ModelAndView export(ModelMap model, HttpServletRequest request){
List<PersonEntity> list = personEntityService.getList()
ViewExcel viewExcel = new ViewExcel();
//将查询出的list集合存入ModelMap 对象中，此时的key就是ViewExcel类中Map所对应的key
model.put("list", list);
return new ModelAndView(viewExcel, model);
}
}
至此，整个过程结束，效果如下图：
\
\
