`
javatome
  • 浏览: 824658 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

Struts 2导出EXCEL

 
阅读更多
view plaincopy to clipboardprint?
  1. importjava.io.IOException;
  2. importjava.io.OutputStream;
  3. importjava.util.List;
  4. importjavax.servlet.http.HttpServletResponse;
  5. importorg.apache.poi.hssf.usermodel.HSSFCell;
  6. importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
  7. importorg.apache.poi.hssf.usermodel.HSSFDataFormat;
  8. importorg.apache.poi.hssf.usermodel.HSSFFont;
  9. importorg.apache.poi.hssf.usermodel.HSSFRow;
  10. importorg.apache.poi.hssf.usermodel.HSSFSheet;
  11. importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
  12. importorg.apache.struts2.ServletActionContext;
  13. importcom.neusoft.dcd.css.common.BaseAction;
  14. importcom.neusoft.dcd.css.model.management.user.UserBaseInfo;
  15. importcom.neusoft.dcd.css.model.tsc.Tsc;
  16. importcom.neusoft.dcd.css.service.tsc.TscService;
  17. importcom.opensymphony.xwork2.Preparable;
  20. publicclassExportExcel{
  21. privatestaticfinallongserialVersionUID=1L;
  22. privateTscServicecssTscService;
  23. publicvoidprepare()throwsException{
  24. }
  26. /**
  27. *绘出Excel
  28. *
  29. *@paramactionMapping
  30. *@paramactionForm
  31. *@paramrequest
  32. *@paramresponse
  33. *@return
  34. *@throwsException
  35. */
  36. publicStringinit()throwsException{
  37. UserBaseInfouserinfo=(UserBaseInfo)ServletActionContext
  38. .getRequest().getSession().getAttribute("UserBaseInfo");
  39. if(userinfo!=null){
  40. HttpServletResponseresponse=ServletActionContext.getResponse();
  41. ListdataList=cssTscService.queryForProTsclb(userinfo.getUserId());
  42. HSSFWorkbookworkbook=exportExcel(dataList);
  43. if(workbook!=null){
  44. this.printExcel(workbook,response,"ExcelTsc.xls");
  45. }
  46. }
  47. returnSUCCESS;
  48. }
  51. //导出Excel
  53. privatevoidprintExcel(HSSFWorkbookworkbook,HttpServletResponseresponse,Stringstring)throwsIOException{
  54. OutputStreamout=response.getOutputStream();
  55. response.setHeader("Content-disposition","attachment;filename="+"TSC.xls");
  56. response.setContentType("application/msexcel;charset=UTF-8");
  57. workbook.write(out);
  58. out.flush();
  59. out.close();
  61. }
  63. publicHSSFWorkbookexportExcel(ListdataList)throwsException
  64. {
  65. HSSFWorkbookworkbook=null;
  66. try
  67. {
  68. //这里的数据即时你要从后台取得的数据
  70. //创建工作簿实例
  71. workbook=newHSSFWorkbook();
  72. //创建工作表实例
  73. HSSFSheetsheet=workbook.createSheet("TscExcel");
  74. //设置列宽
  75. this.setSheetColumnWidth(sheet);
  76. //获取样式
  77. HSSFCellStylestyle=this.createTitleStyle(workbook);
  79. //
  80. if(dataList!=null&&dataList.size()>0)
  81. {
  82. //创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
  83. HSSFRowrow=sheet.createRow((short)0);//建立新行
  85. this.createCell(row,0,style,HSSFCell.CELL_TYPE_STRING,
  86. this.getText("css.tsc.asktime"));
  87. this.createCell(row,1,style,HSSFCell.CELL_TYPE_STRING,
  88. this.getText("css.tsc.productname"));
  89. this.createCell(row,2,style,HSSFCell.CELL_TYPE_STRING,
  90. this.getText("css.tsc.passsort"));
  91. this.createCell(row,3,style,HSSFCell.CELL_TYPE_STRING,
  92. this.getText("css.tsc.askusername"));
  93. this.createCell(row,4,style,HSSFCell.CELL_TYPE_STRING,
  94. this.getText("css.tsc.dtype"));
  95. this.createCell(row,5,style,HSSFCell.CELL_TYPE_STRING,
  96. this.getText("css.tsc.askone"));
  97. this.createCell(row,6,style,HSSFCell.CELL_TYPE_STRING,
  98. this.getText("css.tsc.department"));
  99. this.createCell(row,7,style,HSSFCell.CELL_TYPE_STRING,
  100. this.getText("css.tsc.requesttime"));
  101. this.createCell(row,8,style,HSSFCell.CELL_TYPE_STRING,
  102. this.getText("css.tsc.answertime"));
  103. this.createCell(row,9,style,HSSFCell.CELL_TYPE_STRING,
  104. this.getText("css.tsc.requesttow"));
  105. //给excel填充数据
  106. for(inti=0;i<dataList.size();i++)
  107. {
  108. //将dataList里面的数据取出来，假设这里取出来的是Model,也就是某个javaBean的意思啦
  109. Tscmodel=(Tsc)dataList.get(i);
  110. HSSFRowrow1=sheet.createRow((short)(i+1));//建立新行
  111. if(model.getAsktime()!=null)
  112. this.createCell(row1,0,style,HSSFCell.CELL_TYPE_STRING,
  113. model.getAsktime());
  114. if(model.getProductname()!=null)
  115. this.createCell(row1,1,style,HSSFCell.CELL_TYPE_STRING,
  116. model.getProductname());
  117. if(model.getPasssort()!=null)
  118. this.createCell(row1,2,style,HSSFCell.CELL_TYPE_STRING,
  119. model.getPasssort());
  120. if(model.getAskusername()!=null)
  121. this.createCell(row1,3,style,HSSFCell.CELL_TYPE_STRING,
  122. model.getAskusername());
  123. if(model.getDtype()!=null)
  124. this.createCell(row1,4,style,HSSFCell.CELL_TYPE_STRING,
  125. model.getDtype());
  126. if(model.getAskone()!=null)
  127. this.createCell(row1,5,style,HSSFCell.CELL_TYPE_STRING,
  128. model.getAskone());
  129. if(model.getDepartment()!=null)
  130. this.createCell(row1,6,style,HSSFCell.CELL_TYPE_STRING,
  131. model.getDepartment());
  132. if(model.getRequesttime()!=null)
  133. this.createCell(row1,7,style,HSSFCell.CELL_TYPE_STRING,
  134. model.getRequesttime());
  135. if(model.getAnswertime()!=null)
  136. this.createCell(row1,8,style,HSSFCell.CELL_TYPE_STRING,
  137. model.getAnswertime());
  138. if(model.getRequesttow()!=null)
  139. this.createCell(row1,9,style,HSSFCell.CELL_TYPE_STRING,
  140. model.getRequesttow());
  142. }
  143. }
  144. else{
  145. this.createCell(sheet.createRow(0),0,style,
  146. HSSFCell.CELL_TYPE_STRING,"查无资料");
  147. }
  148. }catch(Exceptione)
  149. {
  150. e.printStackTrace();
  151. }
  152. returnworkbook;
  154. }
  155. privatevoidsetSheetColumnWidth(HSSFSheetsheet)
  156. {
  157. //根据你数据里面的记录有多少列，就设置多少列
  158. sheet.setColumnWidth((short)0,(short)3000);
  159. sheet.setColumnWidth((short)1,(short)3000);
  160. sheet.setColumnWidth((short)2,(short)3000);
  161. sheet.setColumnWidth((short)3,(short)3000);
  162. sheet.setColumnWidth((short)4,(short)5000);
  163. sheet.setColumnWidth((short)5,(short)5000);
  164. sheet.setColumnWidth((short)6,(short)5000);
  165. sheet.setColumnWidth((short)7,(short)5000);
  166. sheet.setColumnWidth((short)8,(short)5000);
  167. sheet.setColumnWidth((short)9,(short)5000);
  168. }
  169. //设置excel的title样式
  170. privateHSSFCellStylecreateTitleStyle(HSSFWorkbookwb){
  171. HSSFFontboldFont=wb.createFont();
  172. boldFont.setFontHeight((short)200);
  173. HSSFCellStylestyle=wb.createCellStyle();
  174. style.setFont(boldFont);
  175. style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
  176. returnstyle;
  177. }
  178. //创建Excel单元格
  179. privatevoidcreateCell(HSSFRowrow,intcolumn,HSSFCellStylestyle,
  180. intcellType,Objectvalue){
  181. HSSFCellcell=row.createCell((short)column);
  182. cell.setEncoding(HSSFCell.ENCODING_UTF_16);
  183. if(style!=null){
  184. cell.setCellStyle(style);
  185. }
  186. switch(cellType){
  187. caseHSSFCell.CELL_TYPE_BLANK:{
  188. }
  189. break;
  190. caseHSSFCell.CELL_TYPE_STRING:{
  191. cell.setCellValue(value.toString());
  192. }
  193. break;
  194. caseHSSFCell.CELL_TYPE_NUMERIC:{
  195. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
  196. //DecimalFormatformat=newDecimalFormat("###,##0.00");
  197. //cell.setCellValue(Float.parseFloat(value.toString()));
  198. cell.setCellValue(Double.parseDouble(value.toString()));
  199. }
  200. break;
  201. default:
  202. break;
  203. }
  204. }
  206. publicvoidsetCssTscService(TscServicecssTscService){
  207. this.cssTscService=cssTscService;
  208. }
  210. }

view plaincopy to clipboardprint?
  1. importjava.io.IOException;
  2. importjava.io.OutputStream;
  3. importjava.util.List;
  4. importjavax.servlet.http.HttpServletResponse;
  5. importorg.apache.poi.hssf.usermodel.HSSFCell;
  6. importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
  7. importorg.apache.poi.hssf.usermodel.HSSFDataFormat;
  8. importorg.apache.poi.hssf.usermodel.HSSFFont;
  9. importorg.apache.poi.hssf.usermodel.HSSFRow;
  10. importorg.apache.poi.hssf.usermodel.HSSFSheet;
  11. importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
  12. importorg.apache.struts2.ServletActionContext;
  13. importcom.neusoft.dcd.css.common.BaseAction;
  14. importcom.neusoft.dcd.css.model.management.user.UserBaseInfo;
  15. importcom.neusoft.dcd.css.model.tsc.Tsc;
  16. importcom.neusoft.dcd.css.service.tsc.TscService;
  17. importcom.opensymphony.xwork2.Preparable;
  20. publicclassExportExcel{
  21. privatestaticfinallongserialVersionUID=1L;
  22. privateTscServicecssTscService;
  23. publicvoidprepare()throwsException{
  24. }
  26. /**
  27. *绘出Excel
  28. *
  29. *@paramactionMapping
  30. *@paramactionForm
  31. *@paramrequest
  32. *@paramresponse
  33. *@return
  34. *@throwsException
  35. */
  36. publicStringinit()throwsException{
  37. UserBaseInfouserinfo=(UserBaseInfo)ServletActionContext
  38. .getRequest().getSession().getAttribute("UserBaseInfo");
  39. if(userinfo!=null){
  40. HttpServletResponseresponse=ServletActionContext.getResponse();
  41. ListdataList=cssTscService.queryForProTsclb(userinfo.getUserId());
  42. HSSFWorkbookworkbook=exportExcel(dataList);
  43. if(workbook!=null){
  44. this.printExcel(workbook,response,"ExcelTsc.xls");
  45. }
  46. }
  47. returnSUCCESS;
  48. }
  51. //导出Excel
  53. privatevoidprintExcel(HSSFWorkbookworkbook,HttpServletResponseresponse,Stringstring)throwsIOException{
  54. OutputStreamout=response.getOutputStream();
  55. response.setHeader("Content-disposition","attachment;filename="+"TSC.xls");
  56. response.setContentType("application/msexcel;charset=UTF-8");
  57. workbook.write(out);
  58. out.flush();
  59. out.close();
  61. }
  63. publicHSSFWorkbookexportExcel(ListdataList)throwsException
  64. {
  65. HSSFWorkbookworkbook=null;
  66. try
  67. {
  68. //这里的数据即时你要从后台取得的数据
  70. //创建工作簿实例
  71. workbook=newHSSFWorkbook();
  72. //创建工作表实例
  73. HSSFSheetsheet=workbook.createSheet("TscExcel");
  74. //设置列宽
  75. this.setSheetColumnWidth(sheet);
  76. //获取样式
  77. HSSFCellStylestyle=this.createTitleStyle(workbook);
  79. //
  80. if(dataList!=null&&dataList.size()>0)
  81. {
  82. //创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
  83. HSSFRowrow=sheet.createRow((short)0);//建立新行
  85. this.createCell(row,0,style,HSSFCell.CELL_TYPE_STRING,
  86. this.getText("css.tsc.asktime"));
  87. this.createCell(row,1,style,HSSFCell.CELL_TYPE_STRING,
  88. this.getText("css.tsc.productname"));
  89. this.createCell(row,2,style,HSSFCell.CELL_TYPE_STRING,
  90. this.getText("css.tsc.passsort"));
  91. this.createCell(row,3,style,HSSFCell.CELL_TYPE_STRING,
  92. this.getText("css.tsc.askusername"));
  93. this.createCell(row,4,style,HSSFCell.CELL_TYPE_STRING,
  94. this.getText("css.tsc.dtype"));
  95. this.createCell(row,5,style,HSSFCell.CELL_TYPE_STRING,
  96. this.getText("css.tsc.askone"));
  97. this.createCell(row,6,style,HSSFCell.CELL_TYPE_STRING,
  98. this.getText("css.tsc.department"));
  99. this.createCell(row,7,style,HSSFCell.CELL_TYPE_STRING,
  100. this.getText("css.tsc.requesttime"));
  101. this.createCell(row,8,style,HSSFCell.CELL_TYPE_STRING,
  102. this.getText("css.tsc.answertime"));
  103. this.createCell(row,9,style,HSSFCell.CELL_TYPE_STRING,
  104. this.getText("css.tsc.requesttow"));
  105. //给excel填充数据
  106. for(inti=0;i<dataList.size();i++)
  107. {
  108. //将dataList里面的数据取出来，假设这里取出来的是Model,也就是某个javaBean的意思啦
  109. Tscmodel=(Tsc)dataList.get(i);
  110. HSSFRowrow1=sheet.createRow((short)(i+1));//建立新行
  111. if(model.getAsktime()!=null)
  112. this.createCell(row1,0,style,HSSFCell.CELL_TYPE_STRING,
  113. model.getAsktime());
  114. if(model.getProductname()!=null)
  115. this.createCell(row1,1,style,HSSFCell.CELL_TYPE_STRING,
  116. model.getProductname());
  117. if(model.getPasssort()!=null)
  118. this.createCell(row1,2,style,HSSFCell.CELL_TYPE_STRING,
  119. model.getPasssort());
  120. if(model.getAskusername()!=null)
  121. this.createCell(row1,3,style,HSSFCell.CELL_TYPE_STRING,
  122. model.getAskusername());
  123. if(model.getDtype()!=null)
  124. this.createCell(row1,4,style,HSSFCell.CELL_TYPE_STRING,
  125. model.getDtype());
  126. if(model.getAskone()!=null)
  127. this.createCell(row1,5,style,HSSFCell.CELL_TYPE_STRING,
  128. model.getAskone());
  129. if(model.getDepartment()!=null)
  130. this.createCell(row1,6,style,HSSFCell.CELL_TYPE_STRING,
  131. model.getDepartment());
  132. if(model.getRequesttime()!=null)
  133. this.createCell(row1,7,style,HSSFCell.CELL_TYPE_STRING,
  134. model.getRequesttime());
  135. if(model.getAnswertime()!=null)
  136. this.createCell(row1,8,style,HSSFCell.CELL_TYPE_STRING,
  137. model.getAnswertime());
  138. if(model.getRequesttow()!=null)
  139. this.createCell(row1,9,style,HSSFCell.CELL_TYPE_STRING,
  140. model.getRequesttow());
  142. }
  143. }
  144. else{
  145. this.createCell(sheet.createRow(0),0,style,
  146. HSSFCell.CELL_TYPE_STRING,"查无资料");
  147. }
  148. }catch(Exceptione)
  149. {
  150. e.printStackTrace();
  151. }
  152. returnworkbook;
  154. }
  155. privatevoidsetSheetColumnWidth(HSSFSheetsheet)
  156. {
  157. //根据你数据里面的记录有多少列，就设置多少列
  158. sheet.setColumnWidth((short)0,(short)3000);
  159. sheet.setColumnWidth((short)1,(short)3000);
  160. sheet.setColumnWidth((short)2,(short)3000);
  161. sheet.setColumnWidth((short)3,(short)3000);
  162. sheet.setColumnWidth((short)4,(short)5000);
  163. sheet.setColumnWidth((short)5,(short)5000);
  164. sheet.setColumnWidth((short)6,(short)5000);
  165. sheet.setColumnWidth((short)7,(short)5000);
  166. sheet.setColumnWidth((short)8,(short)5000);
  167. sheet.setColumnWidth((short)9,(short)5000);
  168. }
  169. //设置excel的title样式
  170. privateHSSFCellStylecreateTitleStyle(HSSFWorkbookwb){
  171. HSSFFontboldFont=wb.createFont();
  172. boldFont.setFontHeight((short)200);
  173. HSSFCellStylestyle=wb.createCellStyle();
  174. style.setFont(boldFont);
  175. style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
  176. returnstyle;
  177. }
  178. //创建Excel单元格
  179. privatevoidcreateCell(HSSFRowrow,intcolumn,HSSFCellStylestyle,
  180. intcellType,Objectvalue){
  181. HSSFCellcell=row.createCell((short)column);
  182. cell.setEncoding(HSSFCell.ENCODING_UTF_16);
  183. if(style!=null){
  184. cell.setCellStyle(style);
  185. }
  186. switch(cellType){
  187. caseHSSFCell.CELL_TYPE_BLANK:{
  188. }
  189. break;
  190. caseHSSFCell.CELL_TYPE_STRING:{
  191. cell.setCellValue(value.toString());
  192. }
  193. break;
  194. caseHSSFCell.CELL_TYPE_NUMERIC:{
  195. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
  196. //DecimalFormatformat=newDecimalFormat("###,##0.00");
  197. //cell.setCellValue(Float.parseFloat(value.toString()));
  198. cell.setCellValue(Double.parseDouble(value.toString()));
  199. }
  200. break;
  201. default:
  202. break;
  203. }
  204. }
  206. publicvoidsetCssTscService(TscServicecssTscService){
  207. this.cssTscService=cssTscService;
  208. }
  210. }
分享到：
| POI导出EXCEL经典实现
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics