- 思路
- 通过response获取OutPutStream
- 通过POI创建HSSFWorkBook,根据查询出数据生成表格
- 通过workBook.write导出
- response设置返回类型、文件名
- 通过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; } |
- workBook.write导出
- 完整代码
@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; } |