导出excel功能实现

小玉的飞猪

已于 2024-01-20 16:34:34 修改

阅读量938

点赞数 22

分类专栏：拿来主义系列文章标签： excel springboot jquery java

于 2023-12-19 02:14:22 首次发布

本文链接：https://blog.csdn.net/jishuai6p/article/details/135074887

版权

拿来主义系列专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、依赖加上

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.1</version>
</dependency>

二、按钮和点击事件加上

$(document).ready(function(){

$(".input-group-btn").on("click",".preadd-btn",outputExcel);//绑定点击事件

});

function outputExcel(){
window.location.href="user/outputExcel"; //controller层
}

</script>

三、controller层

@RequestMapping("outputExcel")
public void outputExcel(HttpServletRequest request, HttpServletResponse response) {
sysUserService.outputExcel(request, response);
}

四、service层

//service接口

void outputExcel(HttpServletRequest request, HttpServletResponse response);

//service实现

@Override
public void outputExcel(HttpServletRequest request, HttpServletResponse response){
// TODO Auto-generated method stub

//--ExportExcelUtils工具类需要几个参数-title,data,position,sheetName和outputstream

//用List<对象>查出数据，遍历,一行(一个对象)就换成一个map，所有map放进data
       Map<String, String> title = new HashMap(); // 表头
List<Map<String, Object>> data = new ArrayList(); // 需要导出的数据
Map<String, Integer> position = new HashMap(); // 表头字段对应的位置（自定义位置）

       // 设置表头字段位置
position.put("username", 0);
position.put("dept", 1);
position.put("email", 2);
position.put("mobile", 3);
position.put("valid", 4);
position.put("createdTime", 5);
position.put("modifiedTime", 6);

// 设置表头信息
title.put("username", "用户名");
title.put("dept", "所属部门");
title.put("email", "邮箱");
title.put("mobile", "手机号");
title.put("valid", "是否被禁用");
title.put("createdTime", "创建时间");
title.put("modifiedTime", "最后修改时间");
List<SysUserDeptVo> list=sysUserDao.getAllUsers();

Map<String, Object> userMap = null;

for (SysUserDeptVo user : list) {
   userMap = new HashMap();
   userMap.put("username", user.getUsername());
   System.out.println(user.getUsername());
   userMap.put("dept", "长沙");
   userMap.put("email", user.getEmail());
   userMap.put("mobile", user.getMobile());
   userMap.put("createdTime", user.getCreatedTime());
   userMap.put("modifiedTime", user.getModifiedTime());
   String valid = "";
   if(user.getValid()== null || user.getValid().equals("")) valid="空值";
   else if(user.getValid()==1) valid="正常";
   else if(user.getValid()==0) valid="被禁用";
   userMap.put("valid", valid);
data.add(userMap); // 将userMap添加到List集合中
}
DateFormat df = new SimpleDateFormat("yyyy-MM-dd_HH_mm_ss");
String date = df.format(new java.util.Date());
String excelName = "用户列表-" + date + ".xlsx";
String sheetName = "用户列表数据";
try {
           excelName = URLEncoder.encode(excelName, "UTF-8");
       } catch (UnsupportedEncodingException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + excelName);
response.setContentType("application/x-download");
// 调用写好的工具类的导出数据方法传入对应的参数
try {
           ExportExcelUtils.exportDataToExcel(title, position, data, sheetName, response.getOutputStream());
           System.out.println("AAAAAAAAAA");
       } catch (IOException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   }