用POI加载模板生成excel报表 淡淡的烟草味﹌ 2022-08-26 01:23 153阅读 0赞 加载模板生成excel报表 \---------------------------------------------------------------------------------------------------------------------------------------- 1. package com.stake.mis.baobiao; 2. 3. import java.io.FileInputStream; 4. import java.io.FileNotFoundException; 5. import java.io.FileOutputStream; 6. import java.io.IOException; 7. import java.util.ArrayList; 8. import java.util.List; 9. 10. import javax.servlet.ServletOutputStream; 11. 12. import org.apache.poi.hssf.usermodel.HSSFCell; 13. import org.apache.poi.hssf.usermodel.HSSFCellStyle; 14. import org.apache.poi.hssf.usermodel.HSSFFont; 15. import org.apache.poi.hssf.usermodel.HSSFFooter; 16. import org.apache.poi.hssf.usermodel.HSSFRow; 17. import org.apache.poi.hssf.usermodel.HSSFSheet; 18. import org.apache.poi.hssf.usermodel.HSSFWorkbook; 19. import org.apache.poi.hssf.util.HSSFColor; 20. import org.apache.poi.hssf.util.Region; 21. import com.stake.mis.bean.User ; 22. 23. 24. public class ExeclManager \{ 25. 26. /\*\* 27. 28. \* list 存放查询返回得结果列表 29. 30. \* sheetname excel模板中sheet得名字 31. 32. \* out 输出流对象 33. 34. / 35. 36. public void getMainCardExcel(List list,String filepath,String sheetname,ServletOutputStream out) throws FileNotFoundException, IOException 37. \{ 38. // 读取工作簿 39. System.out.println(filepath); 40. HSSFWorkbook workbook =new HSSFWorkbook(new FileInputStream(filepath)); 41. try 42. \{ 43. 44. //读取工作表 45. HSSFSheet sheet = workbook.getSheet(sheetname); 46. HSSFRow row; 47. HSSFCell cell = null; 48. int a = 8; 49. HSSFCellStyle style = this.getStyle(workbook); 50. for(int i =0;i<list.size();i++) 51. \{ 52. 53. //从查询结果列表中获取对象 54. User user = (User )list.get(i); 55. 56. //该行以前得部分从模板中取得; 57. row = sheet.createRow(a); 58. 59. cell = row.createCell((short) 0,HSSFCellStyle.ALIGN\_CENTER); 60. cell.setCellType(HSSFCell.CELL\_TYPE\_STRING); 61. cell.setEncoding(HSSFCell.ENCODING\_UTF\_16); 62. cell.setCellValue(i+1); 63. cell.setCellStyle(style); 64. 65. cell = row.createCell((short) 1,HSSFCellStyle.ALIGN\_CENTER); 66. cell.setCellType(HSSFCell.CELL\_TYPE\_STRING); 67. cell.setEncoding(HSSFCell.ENCODING\_UTF\_16); 68. cell.setCellValue(mc.getUserName()); 69. cell.setCellStyle(style); 70. 71. cell = row.createCell((short) 2,HSSFCellStyle.ALIGN\_CENTER); 72. cell.setCellType(HSSFCell.CELL\_TYPE\_STRING); 73. cell.setEncoding(HSSFCell.ENCODING\_UTF\_16); 74. cell.setCellValue(mc.getUserAge()); 75. cell.setCellStyle(style); 76. 77. cell = row.createCell((short) 3,HSSFCellStyle.ALIGN\_CENTER); 78. cell.setCellType(HSSFCell.CELL\_TYPE\_STRING); 79. cell.setEncoding(HSSFCell.ENCODING\_UTF\_16); 80. cell.setCellValue(mc.get某属性()); 81. cell.setCellStyle(style); 82. 83. cell = row.createCell((short) 4,HSSFCellStyle.ALIGN\_CENTER); 84. cell.setCellType(HSSFCell.CELL\_TYPE\_STRING); 85. cell.setEncoding(HSSFCell.ENCODING\_UTF\_16); 86. cell.setCellValue(mc.getEq某属性()); 87. cell.setCellStyle(style); 88. 89. a++; 90. \} 91. workbook.write(out); 92. out.flush(); 93. out.close(); 94. \} 95. catch(Exception e) 96. \{ 97. e.printStackTrace(); 98. \} 99. \} 100. 101. public HSSFCellStyle getStyle(HSSFWorkbook workbook) 102. \{ 103. // 设置字体; 104. HSSFFont font = workbook.createFont(); 105. //设置字体大小; 106. font.setFontHeightInPoints((short)9); 107. //设置字体名字; 108. font.setFontName("Courier New"); 109. //font.setItalic(true); 110. //font.setStrikeout(true); 111. // 设置样式; 112. HSSFCellStyle style = workbook.createCellStyle(); 113. //设置底边框; 114. style.setBorderBottom(HSSFCellStyle.BORDER\_THIN); 115. //设置底边框颜色; 116. style.setBottomBorderColor(HSSFColor.BLACK.index); 117. //设置左边框; 118. style.setBorderLeft(HSSFCellStyle.BORDER\_THIN); 119. //设置左边框颜色; 120. style.setLeftBorderColor(HSSFColor.BLACK.index); 121. //设置右边框; 122. style.setBorderRight(HSSFCellStyle.BORDER\_THIN); 123. //设置右边框颜色; 124. style.setRightBorderColor(HSSFColor.BLACK.index); 125. //设置顶边框; 126. style.setBorderTop(HSSFCellStyle.BORDER\_THIN); 127. //设置顶边框颜色; 128. style.setTopBorderColor(HSSFColor.BLACK.index); 129. //在样式用应用设置的字体; 130. style.setFont(font); 131. //设置自动换行; 132. style.setWrapText(false); 133. //设置水平对齐的样式为居中对齐; 134. style.setAlignment(HSSFCellStyle.ALIGN\_CENTER); 135. //设置垂直对齐的样式为居中对齐; 136. style.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER); 137. return style; 138. \} 139. 140. \} 141. strut的action某方法中在获取查询结果后的部分代码 \------------------------------------------------------------------------------------------------------------------------------------------- 1. List list = null; 2. 3. String filename = ""; 4. 5. //action中调用service对象的方法,查询User信息; 6. 7. list = service.queryUser(查询约束); 8. ExeclManager ma = new ExeclManager(); 9. 10. //设置响应的编码方式; 11. response.setCharacterEncoding("gb2312"); 12. filename = filename + "设备台账.xls"; 13. response.addHeader("Content-Disposition","attachment;filename="\+java.net.URLEncoder.encode(filename, "utf-8")); 14. response.setContentType("application/vnd.ms-excel;charset=gb2312"); 15. 16. //excel模板所在的位置; 17. String filepath = this.getServlet().getServletContext().getRealPath("/bbmanage"); 18. ServletOutputStream out = response.getOutputStream(); 19. 20. //模板名称为example.xls;Sheet1为模板中sheet的名称 21. 22. //调用方法生成excel; 23. ma.getMainCardExcel(list, filepath+"/example.xls", "Sheet1",out); 24.
还没有评论,来说两句吧...