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

最新推荐文章于 2024-03-05 16:32:18 发布

转载最新推荐文章于 2024-03-05 16:32:18 发布 · 802 阅读

框架专栏收录该内容

14 篇文章

订阅专栏

本文介绍如何在SSM框架中使用Apache POI库实现从数据库查询数据并导出为Excel文件的过程。通过示例代码展示了PageData类用于封装HTTP请求参数、Member实体类定义会员信息、DAO接口及其实现类进行数据操作、Service类调用DAO获取数据以及最终在控制器中使用ObjectExcelView视图导出Excel。

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

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

然后，就可以先编程了

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

[java] view plain copy

1. public class PageData extends HashMap implements Map{

3. private static final long serialVersionUID = 1L;

5. Map map = null;

6. HttpServletRequest request;

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 {

3. /**

4. * 会员账号

5. */

6. private String memberID;

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;

3. public interface DAO {

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;

3. import java.util.List;

5. import javax.annotation.Resource;

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;

3. import java.util.Date;

4. import java.util.List;

5. import java.util.Map;

7. import javax.servlet.http.HttpServletRequest;

8. import javax.servlet.http.HttpServletResponse;

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.