SSM框架使用POI技术导出Excel.docx

POI框架是Apache开源的可以导出导入Excel表的，本博客介绍在SSM(Spring+SpringMVC+Mybatis)项目里，如何使用POI框架，导出Excel表这里我们先要去Apache官网下载jar然后，就可以先编程了先提供一个封装的httpservlet请求和添加数据的类javaview plaincopy1. publicclassPageDataextendsHashMapimplementsMap2. 3. privatestaticfinallongserialVersionUID=1L;4. 5. Mapmap=null;6. HttpServletRequestrequest;7. 8. publicPageData(HttpServletRequestrequest)9. this.request=request;10. Mapproperties=request.getParameterMap();11. MapreturnMap=newHashMap();12. Iteratorentries=properties.entrySet().iterator();13. Map.Entryentry;14. Stringname=;15. Stringvalue=;16. while(entries.hasNext()17. entry=(Map.Entry)entries.next();18. name=(String)entry.getKey();19. ObjectvalueObj=entry.getValue();20. if(null=valueObj)21. value=;22. elseif(valueObjinstanceofString)23. Stringvalues=(String)valueObj;24. for(inti=0;ivalues.length;i+)25. value=valuesi+,;26. 27. value=value.substring(0,value.length()-1);28. else29. value=valueObj.toString();30. 31. returnMap.put(name,value);32. 33. map=returnMap;34. 35. 36. publicPageData()37. map=newHashMap();38. 39. 40. Override41. publicObjectget(Objectkey)42. Objectobj=null;43. if(map.get(key)instanceofObject)44. Objectarr=(Object)map.get(key);45. obj=request=null?arr:(request.getParameter(String)key)=null?arr:arr0);46. else47. obj=map.get(key);48. 49. returnobj;50. 51. 52. publicStringgetString(Objectkey)53. return(String)get(key);54. 55. 56. SuppressWarnings(unchecked)57. Override58. publicObjectput(Objectkey,Objectvalue)59. returnmap.put(key,value);60. 61. 62. Override63. publicObjectremove(Objectkey)64. returnmap.remove(key);65. 66. 67. publicvoidclear()68. map.clear();69. 70. 71. publicbooleancontainsKey(Objectkey)72. /TODOAuto-generatedmethodstub73. returnmap.containsKey(key);74. 75. 76. publicbooleancontainsValue(Objectvalue)77. /TODOAuto-generatedmethodstub78. returnmap.containsValue(value);79. 80. 81. publicSetentrySet()82. /TODOAuto-generatedmethodstub83. returnmap.entrySet();84. 85. 86. publicbooleanisEmpty()87. /TODOAuto-generatedmethodstub88. returnmap.isEmpty();89. 90. 91. publicSetkeySet()92. /TODOAuto-generatedmethodstub93. returnmap.keySet();94. 95. 96. SuppressWarnings(unchecked)97. publicvoidputAll(Mapt)98. /TODOAuto-generatedmethodstub99. map.putAll(t);100. 101. 102. publicintsize()103. /TODOAuto-generatedmethodstub104. returnmap.size();105. 106. 107. publicCollectionvalues()108. /TODOAuto-generatedmethodstub109. returnmap.values();110. 111. 112. 写个实体类：会员类javaview plaincopy1. publicclassMember2. 3. /*4. *会员账号5. */6. privateStringmemberID;7. 8. /*9. *会员密码10. */11. privateStringpassword;12. 13. /*14. *会员级别15. */16. privateStringrank;17. 18. /*19. *会员积分20. */21. privateintcredit;22. 23. /*24. *会员手机号25. */26. privateStringphone;27. 28. /*29. *会员皮肤30. */31. privateStringimgPath;32. 33. privateListpostes;34. 35. publicListgetPostes()36. returnpostes;37. 38. 39. publicvoidsetPostes(Listpostes)40. this.postes=postes;41. 42. 43. publicStringgetMemberID()44. returnmemberID;45. 46. 47. publicvoidsetMemberID(StringmemberID)48. this.memberID=memberID;49. 50. 51. publicStringgetPassword()52. returnpassword;53. 54. 55. publicvoidsetPassword(Stringpassword)56. this.password=password;57. 58. 59. publicStringgetRank()60. returnrank;61. 62. 63. publicvoidsetRank(Stringrank)64. this.rank=rank;65. 66. 67. publicintgetCredit()68. returncredit;69. 70. 71. publicvoidsetCredit(intcredit)72. this.credit=credit;73. 74. 75. publicStringgetPhone()76. returnphone;77. 78. 79. publicvoidsetPhone(Stringphone)80. this.phone=phone;81. 82. 83. publicStringgetImgPath()84. returnimgPath;85. 86. 87. publicvoidsetImgPath(StringimgPath)88. this.imgPath=imgPath;89. 90. 91. DAO.javajavaview plaincopy1. packagecom.appweb.core.dao;2. 3. publicinterfaceDAO4. 5. /*6. *保存对象7. *paramstr8. *paramobj9. *return10. *throwsException11. */12. publicObjectsave(Stringstr,Objectobj)throwsException;13. 14. /*15. *修改对象16. *paramstr17. *paramobj18. *return19. *throwsException20. */21. publicObjectupdate(Stringstr,Objectobj)throwsException;22. 23. /*24. *删除对象25. *paramstr26. *paramobj27. *return28. *throwsException29. */30. publicObjectdelete(Stringstr,Objectobj)throwsException;31. 32. /*33. *查找对象34. *paramstr35. *paramobj36. *return37. *throwsException38. */39. publicObjectfindForObject(Stringstr,Objectobj)throwsException;40. 41. /*42. *查找对象43. *paramstr44. *paramobj45. *return46. *throwsException47. */48. publicObjectfindForList(Stringstr,Objectobj)throwsException;49. 50. /*51. *查找对象封装成Map52. *params53. *paramobj54. *return55. *throwsException56. */57. publicObjectfindForMap(Stringsql,Objectobj,Stringkey,Stringvalue)throwsException;58. 59. DAOSupport类：javaview plaincopy1. packagecom.appweb.core.dao;2. 3. importjava.util.List;4. 5. importjavax.annotation.Resource;6. 7. importorg.apache.ibatis.session.ExecutorType;8. importorg.apache.ibatis.session.SqlSession;9. importorg.apache.ibatis.session.SqlSessionFactory;10. importorg.mybatis.spring.SqlSessionTemplate;11. importorg.springframework.stereotype.Repository;12. 13. Repository(daoSupport)14. publicclassDaoSupportimplementsDAO15. 16. Resource(name=sqlSessionTemplate)17. privateSqlSessionTemplatesqlSessionTemplate;18. 19. /*20. *保存对象21. *paramstr22. *paramobj23. *return24. *throwsException25. */26. publicObjectsave(Stringstr,Objectobj)throwsException27. returnsqlSessionTemplate.insert(str,obj);28. 29. 30. /*31. *批量更新32. *paramstr33. *paramobj34. *return35. *throwsException36. */37. publicObjectbatchSave(Stringstr,Listobjs)throwsException38. returnsqlSessionTemplate.insert(str,objs);39. 40. 41. /*42. *修改对象43. *paramstr44. *paramobj45. *return46. *throwsException47. */48. publicObjectupdate(Stringstr,Objectobj)throwsException49. returnsqlSessionTemplate.update(str,obj);50. 51. 52. /*53. *批量更新54. *paramstr55. *paramobj56. *return57. *throwsException58. */59. publicvoidbatchUpdate(Stringstr,Listobjs)throwsException60. SqlSessionFactorysqlSessionFactory=sqlSessionTemplate.getSqlSessionFactory();61. /批量执行器62. SqlSessionsqlSession=sqlSessionFactory.openSession(ExecutorType.BATCH,false);63. try64. if(objs!=null)65. for(inti=0,size=objs.size();isize;i+)66. sqlSession.update(str,objs.get(i);67. 68. sqlSession.flushStatements();69. sqlSmit();70. sqlSession.clearCache();71. 72. finally73. sqlSession.close();74. 75. 76. 77. /*78. *批量更新79. *paramstr80. *paramobj81. *return82. *throwsException83. */84. publicObjectbatchDelete(Stringstr,Listobjs)throwsException85. returnsqlSessionTemplate.delete(str,objs);86. 87. 88. /*89. *删除对象90. *paramstr91. *paramobj92. *return93. *throwsException94. */95. publicObjectdelete(Stringstr,Objectobj)throwsException96. returnsqlSessionTemplate.delete(str,obj);97. 98. 99. /*100. *查找对象101. *paramstr102. *paramobj103. *return104. *throwsException105. */106. publicObjectfindForObject(Stringstr,Objectobj)throwsException107. returnsqlSessionTemplate.selectOne(str,obj);108. 109. 110. /*111. *查找对象112. *paramstr113. *paramobj114. *return115. *throwsException116. */117. publicObjectfindForList(Stringstr,Objectobj)throwsException118. returnsqlSessionTemplate.selectList(str,obj);119. 120. 121. publicObjectfindForMap(Stringstr,Objectobj,Stringkey,Stringvalue)throwsException122. returnsqlSessionTemplate.selectMap(str,obj,key);123. 124. 125. 写个Service类:javaview plaincopy1. /*2. *会员信息列表3. *parampd4. *return5. *throwsException6. */7. publicListlistM(PageDatapd)throwsException8. return(List)dao.findForList(MemberMapper.memberList,pd);9. ObjectExcelView.java:javaview plaincopy1. packagecom.appweb.core.view;2. 3. importjava.util.Date;4. importjava.util.List;5. importjava.util.Map;6. 7. importjavax.servlet.http.HttpServletRequest;8. importjavax.servlet.http.HttpServletResponse;9. 10. importorg.apache.poi.hssf.usermodel.HSSFCell;11. importorg.apache.poi.hssf.usermodel.HSSFCellStyle;12. importorg.apache.poi.hssf.usermodel.HSSFFont;13. importorg.apache.poi.hssf.usermodel.HSSFSheet;14. importorg.apache.poi.hssf.usermodel.HSSFWorkbook;15. importorg.springframework.web.servlet.view.document.AbstractExcelView;16. 17. importcom.appweb.core.entity.PageData;18. importcom.appweb.core.utils.Tools;19. 20. /*21. *导入到EXCEL22. *类名称：ObjectExcelView.java23. *类描述：24. */25. publicclassObjectExcelViewextendsAbstractExcelView26. 27. Override28. protectedvoidbuildExcelDocument(Mapmodel,29. HSSFWorkbookworkbook,HttpServletRequestrequest,30. HttpServletResponseresponse)throwsException31. /TODOAuto-generatedmethodstub32. Datedate=newDate();33. Stringfilename=Tools.date2Str(date,yyyyMMddHHmmss);34. HSSFSheetsheet;35. HSSFCellcell;36. response.setContentType(application/octet-stream);37. response.setHeader(Content-Disposition,attachment;filename=+filename+.xls);38. sheet=workbook.createSheet(sheet1);39. 40. Listtitles=(List)model.get(titles);41. intlen=titles.size();42. HSSFCellStyleheaderStyle=workbook.createCellStyle();/标题样式43. headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);44. headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);45. HSSFFontheaderFont=workbook.createFont();/标题字体46. headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);47. headerFont.setFontHeightInPoints(short)11);48. headerStyle.setFont(headerFont);49. shortwidth=20,height=25*20;50. sheet.setDefaultColumnWidth(width);51. for(inti=0;ilen;i+)/设置标题52. Stringtitle=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. HSSFCellStylecontentStyle=workbook.createCellStyle();/内容样式60. contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);61. ListvarList=(List)model.get(varList);62. intvarCount=varList.size();63. for(inti=0;ivarCount;i+)64. PageDatavpd=varList.get(i);65. for(intj=0;jlen;j+)66. Stringvarstr=vpd.getString(var+(j+1)!=null?vpd.getString(var+(j+1):;67. cell=getCell(sheet,i+1,j);68. cell.setCellStyle(contentStyle);69.