java实现导出数据到Excel表格

• 代码实现

 	/**
 	* 导出数据到excel表格
     * @param request
     * @param response
     */
    @RequestMapping("export")
    @ResponseBody
    public void export(HttpServletRequest request,HttpServletResponse response){
    	//获取手机号
        String phone = request.getParameter("phone");
      
        //根据手机号，查询数据库中需要导出的数据
        List<User> userList = userService.getUserByPhone(phone);

        //创建excel表头
        List<String> column = new ArrayList<>();
        column.add("序号");
        column.add("姓名");
        column.add("年龄");
        column.add("手机");

        //表头对应的数据
        List<Map<String,Object>> data = new ArrayList<>();

        //遍历获取到的需要导出的数据，k要和表头一样
        for (int i = 0; i < userList.size(); i++) {
            Map<String,Object> dataMap = new HashMap<>();
            User user = listUser.get(i);
            dataMap.put("序号",user.getId());
            dataMap.put("姓名",user.getName());
            dataMap.put("年龄",user.getAge());
            dataMap.put("手机",user.getPhone());

            data.add(dataMap);
        }

        //调用导出工具类
        ExportExcel.exportExcel("工作人员表",column,data,request,response);

    }

• 导出excel表格工具类

public class ExportExcel {
	/**
     * @param sheetName 工作表的名字
     * @param column  列名
     * @param data  需要导出的数据    ( map的键定义为列的名字 一定要和column中的列明保持一致  )
     * @param response
     */
    public static void exportExcel(String sheetName,List<String> column,List<Map<String,Object>> data,HttpServletRequest request,HttpServletResponse response){
    	//创建工作薄
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		//创建sheet
		HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
		// 表头
		HSSFRow headRow = sheet.createRow(0);
		for (int i = 0; i < column.size(); i++){
			headRow.createCell(i).setCellValue(column.get(i));
		}
		
		for (int i = 0; i < data.size(); i++) {
			HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
			for (int x = 0; x < column.size(); x++) {
				dataRow.createCell(x).setCellValue(data.get(i).get(column.get(x))==null?"":data.get(i).get(column.get(x)).toString());
			}
		}

		response.setContentType("application/vnd.ms-excel");
		
		try {
			   //获取浏览器名称
			   String agent=request.getHeader("user-agent");
			   String filename=sheetName+".xls";
			   //不同浏览器需要对文件名做特殊处理
			   if (agent.contains("Firefox")) { // 火狐浏览器
			    filename = "=?UTF-8?B?"
			      + new BASE64Encoder().encode(filename.getBytes("utf-8"))
			      + "?=";
			    filename = filename.replaceAll("\r\n", "");
			   } else { // IE及其他浏览器
			    filename = URLEncoder.encode(filename, "utf-8");
			    filename = filename.replace("+"," ");
			   }
			   //推送浏览器
			   response.setHeader("Content-Disposition","attachment;filename="+filename);
			   hssfWorkbook.write(response.getOutputStream());
			  } catch (Exception e) {
			   e.printStackTrace();
			  }
    }

}

• maven依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>