sprmvc与poi结合导出Excel

1. 思路

1. 通过response获取OutPutStream
2. 通过POI创建HSSFWorkBook，根据查询出数据生成表格
3. 通过workBook.write导出

1. response设置返回类型、文件名

1. 通过poi创建HSSFWorkBook

/**  * 生成客户列表  */ private HSSFWorkbook getWorkbook(List<Customer> customerList) throws IOException { //1.创建表格 HSSFWorkbook workBook = new HSSFWorkbook(); //2.创建工作簿 HSSFSheet sheet = workBook.createSheet("工作簿01");   //样式 HSSFCellStyle cellStyle = workBook.createCellStyle(); HSSFFont font = workBook.createFont(); font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short)16); cellStyle.setFont(font);   HSSFRow row = sheet.createRow(0);   String[] heads = {"客户编号","客户名称","客户来源","客户类型","业务员","发布人"}; //创建表头 for(int i=0;i<heads.length;i++){   HSSFCell cell = row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(heads[i]); } //设置表格内容 for(int i=0;i<customerList.size();i++){ HSSFRow rowContent = sheet.createRow(i+1); HSSFCell cell0 = rowContent.createCell(0); cell0.setCellValue(customerList.get(i).getCusnum());   HSSFCell cell1 = rowContent.createCell(1); cell1.setCellValue(customerList.get(i).getCusname());     HSSFCell cell2 = rowContent.createCell(2); cell2.setCellValue(customerList.get(i).getOriginate());   HSSFCell cell3 = rowContent.createCell(3); cell3.setCellValue(customerList.get(i).getType());   HSSFCell cell4 = rowContent.createCell(4); cell4.setCellValue(customerList.get(i).getBusinessname());   HSSFCell cell5 = rowContent.createCell(5); cell5.setCellValue(customerList.get(i).getIssuername()); }   //6.写入磁盘 workBook.write(new File("D:/helloWorld03.xls")); return workBook; }

1. workBook.write导出

1. 完整代码

@RequestMapping("/export/excel") public String contactsList( @RequestParam(defaultValue="1") Integer currentPage, @RequestParam(defaultValue="15") Integer pageSize, String cusnum, HttpServletResponse response) throws IOException{ String dateStr = DateUtil.getDateStr(new Date(),"yyyyMMddHHmmss"); String fileName = URLEncoder.encode("客户信息列表_"+dateStr+".xls", "UTF-8"); //1.设置返回的数据类型 response.setContentType("application/vnd.ms-excel"); //2.设置返回的文件名 response.setHeader("Content-Disposition","attachment;filename*=utf-8'zh_cn'"+fileName);   //3.获取输出流 OutputStream outputStream = response.getOutputStream();   //4.查询数据 //根据条件 查询客户分页信息 PageInfo<Customer> pageInfo = customerService.findPageInfo(currentPage,pageSize,cusnum); //4.通过POI创建excel HSSFWorkbook workBook = getWorkbook(pageInfo.getList()); //写出 workBook.write(outputStream); //关闭 workBook.close(); return null; }   /**  * 生成客户列表  */ private HSSFWorkbook getWorkbook(List<Customer> customerList) throws IOException { //1.创建表格 HSSFWorkbook workBook = new HSSFWorkbook(); //2.创建工作簿 HSSFSheet sheet = workBook.createSheet("工作簿01");   //样式 HSSFCellStyle cellStyle = workBook.createCellStyle(); HSSFFont font = workBook.createFont(); font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short)16); cellStyle.setFont(font);   HSSFRow row = sheet.createRow(0);   String[] heads = {"客户编号","客户名称","客户来源","客户类型","业务员","发布人"}; //创建表头 for(int i=0;i<heads.length;i++){   HSSFCell cell = row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(heads[i]); } //设置表格内容 for(int i=0;i<customerList.size();i++){ HSSFRow rowContent = sheet.createRow(i+1); HSSFCell cell0 = rowContent.createCell(0); cell0.setCellValue(customerList.get(i).getCusnum());   HSSFCell cell1 = rowContent.createCell(1); cell1.setCellValue(customerList.get(i).getCusname());     HSSFCell cell2 = rowContent.createCell(2); cell2.setCellValue(customerList.get(i).getOriginate());   HSSFCell cell3 = rowContent.createCell(3); cell3.setCellValue(customerList.get(i).getType());   HSSFCell cell4 = rowContent.createCell(4); cell4.setCellValue(customerList.get(i).getBusinessname());   HSSFCell cell5 = rowContent.createCell(5); cell5.setCellValue(customerList.get(i).getIssuername()); }   //6.写入磁盘 workBook.write(new File("D:/helloWorld03.xls")); return workBook; }