<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.4</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.2</version> </dependency> 1.这种方法是直接在某个磁盘下生成excel文件 String[] userArr={"姓名","性别","年龄","民族","学历","身份证","职称","邮箱","电话","所在部门"}; HSSFWorkbook hssfWorkbook=new HSSFWorkbook(); HSSFSheet sheet=hssfWorkbook.createSheet("从业人员信息表"); HSSFCellStyle textStyle = hssfWorkbook.createCellStyle(); HSSFDataFormat format = hssfWorkbook.createDataFormat(); textStyle.setDataFormat(format.getFormat("@"));//设置未文本样式 CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, userArr.length - 1); sheet.addMergedRegion(titleRange); HSSFRow titleRow = sheet.createRow(0); HSSFCell titleCell = titleRow.createCell(0); titleRow.setHeight((short)400); textStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 textStyle.setVerticalAlignment(textStyle.VERTICAL_CENTER);//垂直居中 titleCell.setCellStyle(textStyle); titleCell.setCellValue("从业人员基础信息表"); HSSFRow headRow = sheet.createRow(1); headRow.setHeight((short)300); for (int i=0;i<userArr.length;i++){ sheet.setDefaultColumnStyle(i,textStyle); } for (int i=0;i<userArr.length;i++){ HSSFCell headCell = headRow.createCell(i); headCell.setCellValue(userArr[i]); headCell.setCellStyle(textStyle); } List<User> list=userMapper.findAllUserByCompanyId(comId); for (int i=0;i<list.size();i++){ HSSFRow textRow = sheet.createRow(2+i); HSSFCell textcell = textRow.createCell(0); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_name()); textcell = textRow.createCell(1); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); if(list.get(i).getUser_gender()==0){ textcell.setCellValue("男"); }else { textcell.setCellValue("女"); } textcell = textRow.createCell(2); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(ExportExcel.getage(list.get(i).getUser_birthday())); textcell = textRow.createCell(3); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_nation()); textcell = textRow.createCell(4); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_education()); textcell = textRow.createCell(5); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_code()); textcell = textRow.createCell(6); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_position()); textcell = textRow.createCell(7); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_email()); textcell = textRow.createCell(8); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); textcell.setCellValue(list.get(i).getUser_tel()); textcell = textRow.createCell(9); textcell.setCellStyle(textStyle);//设置单元格格式为"文本" textcell.setCellType(HSSFCell.CELL_TYPE_STRING); Department department=departmentMapper.findOne(list.get(i).getDp_id()); textcell.setCellValue(department.getDp_name()); sheet.setColumnWidth(0,15*256); sheet.setColumnWidth(1,15*256); sheet.setColumnWidth(2,15*256); sheet.setColumnWidth(3,15*256); sheet.setColumnWidth(4,15*256); sheet.setColumnWidth(5,15*256); sheet.setColumnWidth(6,15*256); sheet.setColumnWidth(7,15*256); sheet.setColumnWidth(8,15*256); } UUID uuid = UUID.randomUUID(); String filedisplay=null; filedisplay =uuid+".xls"; String downloadpath1= Constant.EXCELUSER_FILE; String downloadpath=downloadpath1+filedisplay; //如果web项目,1、设置下载框的弹出(设置response相关参数);2、通过httpservletresponse.getOutputStream()获取 File file=new File(downloadpath1); if (!file.exists()){ file.mkdirs(); } OutputStream out = null; try { out = new FileOutputStream(downloadpath); hssfWorkbook.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } 2.生成后直接下载,在控制层只需将循环后面的替换为:
response.setContentType("APPLICATION/OCTET-STREAM"); response.setHeader("Content-Disposition", "attachment;filename=aaa.xlsx"); OutputStream out = null; try { out = response.getOutputStream(); hssfWorkbook.write(out); } catch (IOException e) { e.printStackTrace(); }