@RequestMapping(value = "/emp/export", method = RequestMethod.GET) public void exportExcel(@RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum, HttpServletResponse response) { PageInfo<Employee> pageInfo = employeeService.getAll(pageNum);//自定义导出的数据 List<Employee> list = pageInfo.getList(); try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ExcelUtil.exportUserExcel(list, os);//拼装数据 byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数,可以打开下载页面 response.reset(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=aaa.xls"); response.setContentLength(content.length); ServletOutputStream outputStream = response.getOutputStream(); BufferedInputStream bis = new BufferedInputStream(is); BufferedOutputStream bos = new BufferedOutputStream(outputStream); byte[] buff = new byte[8192]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } bis.close(); bos.close(); outputStream.flush(); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } }
/** * 将用户的信息导入到excel文件中去 * @param userList 用户列表 * @param out 输出表 */ public static void exportUserExcel(List<Employee> userList, OutputStream out) { try{ //1.创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //1.1创建合并单元格对象 CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,4);//起始行,结束行,起始列,结束列 //1.2头标题样式 HSSFCellStyle headStyle = createCellStyle(workbook,(short)16); //1.3列标题样式 HSSFCellStyle colStyle = createCellStyle(workbook,(short)13); //2.创建工作表 HSSFSheet sheet = workbook.createSheet("用户列表"); //2.1加载合并单元格对象 sheet.addMergedRegion(callRangeAddress); //设置默认列宽 sheet.setDefaultColumnWidth(25); //3.创建行 //3.1创建头标题行;并且设置头标题 HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); //加载单元格样式 cell.setCellStyle(headStyle); cell.setCellValue("用户列表"); //3.2创建列标题;并且设置列标题 HSSFRow row2 = sheet.createRow(1); String[] titles = {"Id","姓名","电子邮箱","性别","部门Id","所属部门"}; for(int i=0;i<titles.length;i++) { HSSFCell cell2 = row2.createCell(i); //加载单元格样式 cell2.setCellStyle(colStyle); cell2.setCellValue(titles[i]); } //4.操作单元格;将用户列表写入excel if(userList != null) { for(int j=0;j<userList.size();j++) { //创建数据行,前面有两行,头标题行和列标题行 HSSFRow row3 = sheet.createRow(j+2); HSSFCell cell1 = row3.createCell(0); cell1.setCellValue(userList.get(j).getEmpId()); HSSFCell cell2 = row3.createCell(1); cell2.setCellValue(userList.get(j).getEmpName()); HSSFCell cell3 = row3.createCell(2); cell3.setCellValue(userList.get(j).getEmail()); HSSFCell cell4 = row3.createCell(3); cell4.setCellValue("M".equals(userList.get(j).getGender())?"男":"女"); HSSFCell cell5 = row3.createCell(4); cell5.setCellValue(userList.get(j).getDepartment().getDeptId()); HSSFCell cell6 = row3.createCell(5); cell6.setCellValue(userList.get(j).getDepartment().getDeptName()); } } //5.输出 workbook.write(out); workbook.close(); //out.close(); }catch(Exception e) { e.printStackTrace(); } }
前端直接点击<a href="/emp/export?pageNum=1">导出Excel文件</a>即可下载
前提是项目中必须引用以下jar包:
<dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.2.1</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.3</version> </dependency>