POI框架是Apache开源的可以导出导入Excel表的，本博客介绍在SSM(spring+SpringMVC+Mybatis)项目里，如何使用POI框架，导出Excel表
这里我们先要去Apache官网下载jar
然后，就可以先编程了
先提供一个封装的httpservlet请求和添加数据的类
[java] view plain copy
1. public class PageData extends HashMap implements Map{
2.
3. private static final long serialVersionUID = 1L;
4.
5. Map map = null;
6. HttpServletRequest request;
7.
8. public PageData(HttpServletRequest request){
9. this.request = request;
10. Map properties = request.getParameterMap();
11. Map returnMap = new HashMap();
12. Iterator entries = properties.entrySet().iterator();
13. Map.Entry entry;
14. String name = "";
15. String value = "";
16. while (entries.hasNext()) {
17. entry = (Map.Entry) entries.next();
18. name = (String) entry.getKey();
19. Object valueObj = entry.getValue();
20. if(null == valueObj){
21. value = "";
22. }else if(valueObj instanceof String[]){
23. String[] values = (String[])valueObj;
24. for(int i=0;i<values.length;i++){
25. value = values[i] + ",";
26. }
27. value = value.substring(0, value.length()-1);
28. }else{
29. value = valueObj.toString();
30. }
31. returnMap.put(name, value);
32. }
33. map = returnMap;
34. }
35.
36. public PageData() {
37. map = new HashMap();
38. }
39.
40. @Override
41. public Object get(Object key) {
42. Object obj = null;
43. if(map.get(key) instanceof Object[]) {
44. Object[] arr = (Object[])map.get(key);
45. obj = request == null ? arr:(request.getParameter((String)key) == null ? arr:arr[0]);
46. } else {
47. obj = map.get(key);
48. }
49. return obj;
50. }
51.
52. public String getString(Object key) {
53. return (String)get(key);
54. }
55.
56. @SuppressWarnings("unchecked")
57. @Override
58. public Object put(Object key, Object value) {
59. return map.put(key, value);
60. }
61.
62. @Override
63. public Object remove(Object key) {
64. return map.remove(key);
65. }
66.
67. public void clear() {
68. map.clear();
69. }
70.
71. public boolean containsKey(Object key) {
72. // TODO Auto-generated method stub
73. return map.containsKey(key);
74. }
75.
76. public boolean containsValue(Object value) {
77. // TODO Auto-generated method stub
78. return map.containsValue(value);
79. }
80.
81. public Set entrySet() {
82. // TODO Auto-generated method stub
83. return map.entrySet();
84. }
85.
86. public boolean isEmpty() {
87. // TODO Auto-generated method stub
88. return map.isEmpty();
89. }
90.
91. public Set keySet() {
92. // TODO Auto-generated method stub
93. return map.keySet();
94. }
95.
96. @SuppressWarnings("unchecked")
97. public void putAll(Map t) {
98. // TODO Auto-generated method stub
99. map.putAll(t);
100. }
101.
102. public int size() {
103. // TODO Auto-generated method stub
104. return map.size();
105. }
106.
107. public Collection values() {
108. // TODO Auto-generated method stub
109. return map.values();
110. }
111.
112. }
写个实体类：
会员类
[java] view plain copy
1. public class Member {
2.
3. /**
4. * 会员账号
5. */
6. private String memberID;
7.
8. /**
9. * 会员密码
10. */
11. private String password;
12.
13. /**
14. * 会员级别
15. */
16. private String rank;
17.
18. /**
19. * 会员积分
20. */
21. private int credit;
22.
23. /**
24. * 会员手机号
25. */
26. private String phone;
27.
28. /**
29. * 会员皮肤
30. */
31. private String imgPath;
32.
33. private List<GroupPost> postes;
34.
35. public List<GroupPost> getPostes() {
36. return postes;
37. }
38.
39. public void setPostes(List<GroupPost> postes) {
40. this.postes = postes;
41. }
42.
43. public String getMemberID() {
44. return memberID;
45. }
46.
47. public void setMemberID(String memberID) {
48. this.memberID = memberID;
49. }
50.
51. public String getPassword() {
52. return password;
53. }
54.
55. public void setPassword(String password) {
56. this.password = password;
57. }
58.
59. public String getRank() {
60. return rank;
61. }
62.
63. public void setRank(String rank) {
64. this.rank = rank;
65. }
66.
67. public int getCredit() {
68. return credit;
69. }
70.
71. public void setCredit(int credit) {
72. this.credit = credit;
73. }
74.
75. public String getPhone() {
76. return phone;
77. }
78.
79. public void setPhone(String phone) {
80. this.phone = phone;
81. }
82.
83. public String getImgPath() {
84. return imgPath;
85. }
86.
87. public void setImgPath(String imgPath) {
88. this.imgPath = imgPath;
89. }
90.
91. }
DAO.Java
[java] view plain copy
1. package com.appweb.core.dao;
2.
3. public interface DAO {
4.
5. /**
6. * 保存对象
7. * @param str
8. * @param obj
9. * @return
10. * @throws Exception
11. */
12. public Object save(String str, Object obj) throws Exception;
13.
14. /**
15. * 修改对象
16. * @param str
17. * @param obj
18. * @return
19. * @throws Exception
20. */
21. public Object update(String str, Object obj) throws Exception;
22.
23. /**
24. * 删除对象
25. * @param str
26. * @param obj
27. * @return
28. * @throws Exception
29. */
30. public Object delete(String str, Object obj) throws Exception;
31.
32. /**
33. * 查找对象
34. * @param str
35. * @param obj
36. * @return
37. * @throws Exception
38. */
39. public Object findForObject(String str, Object obj) throws Exception;
40.
41. /**
42. * 查找对象
43. * @param str
44. * @param obj
45. * @return
46. * @throws Exception
47. */
48. public Object findForList(String str, Object obj) throws Exception;
49.
50. /**
51. * 查找对象封装成Map
52. * @param s
53. * @param obj
54. * @return
55. * @throws Exception
56. */
57. public Object findForMap(String sql, Object obj, String key , String value) throws Exception;
58.
59. }
DAOSupport类：
[java] view plain copy
1. package com.appweb.core.dao;
2.
3. import java.util.List;
4.
5. import javax.annotation.Resource;
6.
7. import org.apache.ibatis.session.ExecutorType;
8. import org.apache.ibatis.session.SqlSession;
9. import org.apache.ibatis.session.SqlSessionFactory;
10. import org.mybatis.spring.SqlSessionTemplate;
11. import org.springframework.stereotype.Repository;
12.
13. @Repository("daoSupport")
14. public class DaoSupport implements DAO {
15.
16. @Resource(name = "sqlSessionTemplate")
17. private SqlSessionTemplate sqlSessionTemplate;
18.
19. /**
20. * 保存对象
21. * @param str
22. * @param obj
23. * @return
24. * @throws Exception
25. */
26. public Object save(String str, Object obj) throws Exception {
27. return sqlSessionTemplate.insert(str, obj);
28. }
29.
30. /**
31. * 批量更新
32. * @param str
33. * @param obj
34. * @return
35. * @throws Exception
36. */
37. public Object batchSave(String str, List objs )throws Exception{
38. return sqlSessionTemplate.insert(str, objs);
39. }
40.
41. /**
42. * 修改对象
43. * @param str
44. * @param obj
45. * @return
46. * @throws Exception
47. */
48. public Object update(String str, Object obj) throws Exception {
49. return sqlSessionTemplate.update(str, obj);
50. }
51.
52. /**
53. * 批量更新
54. * @param str
55. * @param obj
56. * @return
57. * @throws Exception
58. */
59. public void batchUpdate(String str, List objs )throws Exception{
60. SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
61. //批量执行器
62. SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
63. try{
64. if(objs!=null){
65. for(int i=0,size=objs.size();i<size;i++){
66. sqlSession.update(str, objs.get(i));
67. }
68. sqlSession.flushStatements();
69. sqlSession.commit();
70. sqlSession.clearCache();
71. }
72. }finally{
73. sqlSession.close();
74. }
75. }
76.
77. /**
78. * 批量更新
79. * @param str
80. * @param obj
81. * @return
82. * @throws Exception
83. */
84. public Object batchDelete(String str, List objs )throws Exception{
85. return sqlSessionTemplate.delete(str, objs);
86. }
87.
88. /**
89. * 删除对象
90. * @param str
91. * @param obj
92. * @return
93. * @throws Exception
94. */
95. public Object delete(String str, Object obj) throws Exception {
96. return sqlSessionTemplate.delete(str, obj);
97. }
98.
99. /**
100. * 查找对象
101. * @param str
102. * @param obj
103. * @return
104. * @throws Exception
105. */
106. public Object findForObject(String str, Object obj) throws Exception {
107. return sqlSessionTemplate.selectOne(str, obj);
108. }
109.
110. /**
111. * 查找对象
112. * @param str
113. * @param obj
114. * @return
115. * @throws Exception
116. */
117. public Object findForList(String str, Object obj) throws Exception {
118. return sqlSessionTemplate.selectList(str, obj);
119. }
120.
121. public Object findForMap(String str, Object obj, String key, String value) throws Exception {
122. return sqlSessionTemplate.selectMap(str, obj, key);
123. }
124.
125. }
写个Service类:
[java] view plain copy
1. /**
2. * 会员信息列表
3. * @param pd
4. * @return
5. * @throws Exception
6. */
7. public List<PageData> listM(PageData pd)throws Exception{
8. return (List<PageData>)dao.findForList("MemberMapper.memberList", pd);
9. }
ObjectExcelView.java:
[java] view plain copy
1. package com.appweb.core.view;
2.
3. import java.util.Date;
4. import java.util.List;
5. import java.util.Map;
6.
7. import javax.servlet.http.HttpServletRequest;
8. import javax.servlet.http.HttpServletResponse;
9.
10. import org.apache.poi.hssf.usermodel.HSSFCell;
11. import org.apache.poi.hssf.usermodel.HSSFCellStyle;
12. import org.apache.poi.hssf.usermodel.HSSFFont;
13. import org.apache.poi.hssf.usermodel.HSSFSheet;
14. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
15. import org.springframework.web.servlet.view.document.AbstractExcelView;
16.
17. import com.appweb.core.entity.PageData;
18. import com.appweb.core.utils.Tools;
19.
20. /**
21. * 导入到EXCEL
22. * 类名称：ObjectExcelView.java
23. * 类描述：
24. */
25. public class ObjectExcelView extends AbstractExcelView{
26.
27. @Override
28. protected void buildExcelDocument(Map<String, Object> model,
29. HSSFWorkbook workbook, HttpServletRequest request,
30. HttpServletResponse response) throws Exception {
31. // TODO Auto-generated method stub
32. Date date = new Date();
33. String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
34. HSSFSheet sheet;
35. HSSFCell cell;
36. response.setContentType("application/octet-stream");
37. response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
38. sheet = workbook.createSheet("sheet1");
39.
40. List<String> titles = (List<String>) model.get("titles");
41. int len = titles.size();
42. HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
43. headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
44. headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
45. HSSFFont headerFont = workbook.createFont(); //标题字体
46. headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
47. headerFont.setFontHeightInPoints((short)11);
48. headerStyle.setFont(headerFont);
49. short width = 20,height=25*20;
50. sheet.setDefaultColumnWidth(width);
51. for(int i=0; i<len; i++){ //设置标题
52. String title = titles.get(i);
53. cell = getCell(sheet, 0, i);
54. cell.setCellStyle(headerStyle);
55. setText(cell,title);
56. }
57. sheet.getRow(0).setHeight(height);
58.
59. HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
60. contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
61. List<PageData> varList = (List<PageData>) model.get("varList");
62. int varCount = varList.size();
63. for(int i=0; i<varCount; i++){
64. PageData vpd = varList.get(i);
65. for(int j=0;j<len;j++){
66. String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
67. cell = getCell(sheet, i+1, j);
68. cell.setCellStyle(contentStyle);
69. setText(cell,varstr);
70. }
71.
72. }
73.
74. }
75.
76. }
控制类导出Excel表：
[java] view plain copy
1. /**
2. * 导出Excel
3. * @return
4. * @throws Exception
5. */
6. @RequestMapping("/exportExcel")
7. public ModelAndView exportExcel()throws Exception{
8. ModelAndView mv = this.getModelAndView();
9. PageData pd = new PageData();
10. pd = this.getPageData();
11. //检索条件
12. Map<String,Object> dataMap = new HashMap<String,Object>();
13. List<String> titles = new ArrayList<String>();
14.
15. titles.add("用户名");
16. titles.add("密码");
17. titles.add("级别");
18. titles.add("积分");
19. titles.add("手机号");
20.
21. dataMap.put("titles", titles);
22.
23. List<PageData> memberList = memberService.listM(pd);
24. List<PageData> varList = new ArrayList<PageData>();
25. for(int i=0;i<memberList.size();i++){
26. PageData vpd = new PageData();
27. vpd.put("var1", memberList.get(i).getString("memberID"));
28. vpd.put("var2", memberList.get(i).getString("password"));
29. vpd.put("var3", memberList.get(i).getString("rank"));
30. vpd.put("var4", memberList.get(i).get("credit").toString());
31. vpd.put("var5", memberList.get(i).getString("phone"));
32. varList.add(vpd);
33. }
34. dataMap.put("varList", varList);
35. ObjectExcelView erv = new ObjectExcelView();
36. mv = new ModelAndView(erv,dataMap);
37.
38. return mv;
39. }
40.
