目的：访问url（http://localhost:8080/POIOutputExcel/outputexcel.do）实现excel导出，效果图如下：
文件目录（配置文件就不多说了，跟前面的随笔一样一样的）
User.java
public class User { private Integer id; private String name; private Double cash; //此处省略getter/setter方法 }
Datalist.java
public class Datalist { //这里不走数据库，直接模拟数据库查询结果 public List<User> getUsers(int n){ List<User> list = new ArrayList<User>(); for (int i = 0; i < n; i++) { User user = new User(); user.setId(i); user.setName("xiaoming"+i); user.setCash(1000.0+i); list.add(user); } return list; } }
BeanToMap.java
public class BeanToMap<T> { public Map<String,Object> getMap(T entity){ Field[] fields = entity.getClass().getDeclaredFields(); Map<String,Object> map = new HashMap<String,Object>(); for (int i = 0; i < fields.length; i++) { String methodName = getMethodName(fields[i].getName()); try { Method method = entity.getClass().getMethod(methodName); map.put(fields[i].getName(),method.invoke(entity)); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } return map; } public String getMethodName(String fieldName){ byte[] buffer = fieldName.getBytes(); buffer[0] = (byte)(buffer[0]-32); String name = new String(buffer); return "get"+name; } }
PoiExcelExport.java
public class PoiExcelExport<T> { // excle导出名称 private String fileName; // excel 表头 private String[] heads; // excel 列 private String[] cols; // 设置数值型的列 从0开始计数 private int[] numerics; //list集合 private List<T> list; // 输出流 private OutputStream out; // 构造函数 public PoiExcelExport(String fileName, String[] heads, String[] cols, List<T> list, OutputStream out) { this.fileName = fileName; this.heads = heads; this.cols = cols; this.list = list; this.out = out; } // 构造函数 带数字类型 public PoiExcelExport(String fileName, String[] heads, String[] cols, List<T> list, int[] numerics, OutputStream out) { this.fileName = fileName; this.heads = heads; this.cols = cols; this.list = list; this.numerics = numerics; this.out = out; } public void exportExcel() { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); // 创建一个excel对象 for (int i = 0; i <= (list.size() / 65535); i++) { HSSFSheet hssfsheet = hssfworkbook.createSheet(); // 工作表 // 工作表名称 hssfworkbook.setSheetName(i, fileName.replace(".xls", "") + "(第" + (i + 1) + "页)"); int beginRows = 65535 * i; int endRows = (list.size() > 65535 * (i + 1)) ? 65535 * (i + 1) - 1 : list.size() - 1; HSSFRow hssfrowHead = hssfsheet.createRow(0); // 输出excel 表头 if (heads != null && heads.length > 0) { for (int h = 0; h < heads.length; h++) { HSSFCell hssfcell = hssfrowHead.createCell(h,Cell.CELL_TYPE_STRING); hssfcell.setCellValue(heads[h]); } } // 要设置数值型 列表 HSSFCellStyle cellstyle = hssfworkbook.createCellStyle(); cellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("##0")); // 是否是数值型 boolean isnum = false; // 输出excel 数据 for (int curRow = beginRows; curRow <= endRows; curRow++) { // 获取数据 BeanToMap<T> btm = new BeanToMap<T>(); Map<String,Object> hm = btm.getMap(list.get(curRow)); // 创建excel行 表头1行 导致数据行数 延后一行 HSSFRow hssfrow = hssfsheet.createRow(curRow % 65535 + 1); // 读取数据值 for (int k = 0; k < cols.length; k++) { HSSFCell hssfcell = hssfrow.createCell(k); // hssfcell.setCellValue(hm.get(cols[k])==null?"":hm.get(cols[k]).toString()); isnum = false; for (int z = 0; z < numerics.length; z++) { if (numerics[z] == k) { isnum = true; break; } } if (isnum) { if (hm.get(cols[k]) == null || hm.get(cols[k]).equals("")) { } else { hssfcell.setCellStyle(cellstyle); hssfcell.setCellValue(Double.parseDouble( hm.get(cols[k]) == null ? "" : hm.get(cols[k]).toString().replace(",", ""))); } } else { hssfcell.setCellValue(hm.get(cols[k]) == null ? "" : hm.get(cols[k]).toString()); } } } } // excel生成完毕，写到输出流 try { hssfworkbook.write(out); out.flush(); out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
ServletUtil.java
public class ServletUtil { private String fileName; private HttpServletRequest req; private HttpServletResponse resp; public OutputStream getOut(){ try { return resp.getOutputStream(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } } public ServletUtil(HttpServletResponse resp){ this.resp = resp; } public ServletUtil(String fileName, HttpServletRequest req, HttpServletResponse resp){ this.fileName = fileName; this.req = req; this.resp = resp; } public void poiExcelServlet(){ resp.setContentType("application/vnd.ms-excel"); String contentDisposition = ""; try { if (req.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) { contentDisposition = "attachment; filename="" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + """;// firefox浏览器 } else { contentDisposition = "attachment; filename="" + URLEncoder.encode(fileName, "UTF-8") + """;// IE浏览器 } } catch (UnsupportedEncodingException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } resp.setHeader("Content-Disposition", contentDisposition); resp.setCharacterEncoding("UTF-8"); } }
UserController.java
@Controller public class UserController { @RequestMapping(value = "/outputexcel.do") public void showImgCode(HttpServletRequest req, HttpServletResponse resp) { String fileName = "springMVC导出excel小练习"; ServletUtil su = new ServletUtil(fileName, req, resp); su.poiExcelServlet(); String[] heads = {"id号","姓名","身价(元)"}; String[] cols = {"id","name","cash"}; int[] numerics = {2}; List<User> list = new Datalist().getUsers(70000);//获取1000条样例数据 ServletUtil suresp = new ServletUtil(resp); PoiExcelExport<User> pee = new PoiExcelExport<User>(fileName, heads, cols, list, numerics, suresp.getOut()); pee.exportExcel(); } }