Excel文件导出

jar坐标

<!--POI包-Excel导入导出操作包--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>3.17</version> </dependency>

采用同步提交表单的方式

前端： 导出按钮

<button id="demo1" onclick="excelTo()" class="btn btn-sm btn-primary" type="button"><i class="fa fa-table"></i> 导出Excel</button>

js导出方法

// 导出excel文件 function excelTo() { // 获取被选中的数据的id var cs = $("input[type='checkbox']:checked"); if (cs.length>0){ $("#delAll").submit(); }else{ alert("您没选中任何导出数据"); } }

后台： controller类

// excel导出 @RequestMapping("ExcelTo.xlsx") public String ExcelTo(int[] ids, HttpServletResponse response) throws IOException { // 根据id获取要导出的Employee数据 List<Employee> list = employeeService.findIds(ids); // 创建文档对象 SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // 是否设置零时文件 workbook.setCompressTempFiles(true); // 创建表格sheet SXSSFSheet sheet = workbook.createSheet("employee"); // 创建标题数组 String[] captions = {"编号","姓名","性别","年龄","电话","入职时间","身份号码","备注"}; // 创建标题行,在第0行创建标题 SXSSFRow headerRow = sheet.createRow(0); // 单元格对象申明，未初始化 SXSSFCell headerCell = null; for (int i=0; i<captions.length; i++){ // 单元格初始化 headerCell = headerRow.createCell(i); // 给单元格填充标题值 headerCell.setCellValue(captions[i]); } // 数据行 for (int i=0; i<list.size(); i++){ Employee employee = list.get(i); // 创建一个数据行 SXSSFRow row = sheet.createRow(i + 1); // 声明单元格 SXSSFCell cell = null; int te = 0; // 设置编号值 cell = row.createCell(te++); cell.setCellValue(employee.getEid()); // 设置姓名 cell = row.createCell(te++); cell.setCellValue(employee.getEname()); //设置性别 cell = row.createCell(te++); cell.setCellValue(employee.getEsex()); //年龄 cell = row.createCell(te++); cell.setCellValue(employee.getEage()); //设置电话 cell = row.createCell(te++); cell.setCellValue(employee.getTelephone()); //入职时间 Date hiredate = employee.getHiredate(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String format = sdf.format(hiredate); cell = row.createCell(te++); cell.setCellValue(format); //号码 cell = row.createCell(te++); cell.setCellValue(employee.getPnum()); //备注 cell = row.createCell(te++); cell.setCellValue(employee.getRemark()); } ServletOutputStream os = response.getOutputStream(); workbook.write(os); os.close(); return "redirect:/emps/list-employee.jsp"; }

serviceImpl类

// 根据id[]查询所有数据 @Override public List<Employee> findIds(int[] ids) { return employeeDao.findByIds(ids); }

dao类

List<Employee> findByIds(int[] ids);

mapper.xml

<select id="findByIds" parameterType="int[]" resultType="Employee"> select * from employee <where> <foreach collection="array" item="id" open="eid in(" close=")" separator=","> #{id} </foreach> </where> </select>