hutool工具ExcelUtils实现文件的导入导出

hutool工具封装了很多丰富的工具类，当然不乏最常用的文件上传与下载

pom.xml依赖

	<!--hutool工具-->
	<dependency>
	   <groupId>cn.hutool</groupId>
	   <artifactId>hutool-all</artifactId>
	   <version>5.7.20</version>
	</dependency>
	<dependency>
	   <groupId>org.apache.poi</groupId>
	   <artifactId>poi-ooxml</artifactId>
	   <version>4.1.2</version>
	</dependency>

设置浏览器响应的格式

servletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
String fileName = URLEncoder.encode("市场活动列表", "UTF-8");
servletResponse.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

导出接口

    /*
    导出市场活动
     */
    @GetMapping("/export")
    public void exportAllActivity(HttpServletResponse servletResponse) throws Exception{
        //1.获取所有数据
        List<Activity> activities = activityService.queryAllActivities();
        //2.在内存操作写到浏览器
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //自定义标题别名
        writer.addHeaderAlias("owner", "所有者");
        writer.addHeaderAlias("name", "名称");
        writer.addHeaderAlias("startDate", "开始日期");
        writer.addHeaderAlias("endDate", "结束日期");
        writer.addHeaderAlias("cost", "成本");
        writer.addHeaderAlias("description", "描述");
        writer.addHeaderAlias("createTime", "创建时间");
        writer.addHeaderAlias("editTime", "修改时间");
        writer.addHeaderAlias("editBy", "修改者");
        //写出所有信息到excel中
     	writer.write(activities,true);
        //3.设置浏览器响应格式
        servletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        String fileName = URLEncoder.encode("市场活动列表", "UTF-8");
        servletResponse.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        ServletOutputStream outputStream = servletResponse.getOutputStream();
        writer.flush(outputStream,true);
        outputStream.close();
        writer.close();
    }

导入接口

	/*
    导入市场活动表
     */
    @PostMapping("/importExcel")
    public ReturnObject imp(MultipartFile file) throws Exception{
        //1.封装返回信息
        ReturnObject returnObject = new ReturnObject();
        //2.获取当前用户
        User user = TokenUtils.getUser();
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        List<Activity> activities = reader.readAll(Activity.class);
		 // 方式1：(推荐) 通过 javabean的方式读取Excel内的对象，但是要求表头必须是英文，跟javabean的属性要对应起来
		//List<Activity> list = reader.readAll(Activity.class);
		/** 方式2：(推荐) 通过 javabean的方式读取Excel内的对象，但是要求在类属性上加hutool工具提供的@Alias("")注解，值填写要求输出的表头，形式如下
		@Alias("所有者")
	    private String owner;
	    @Alias("名称")
	    private String name;
	    @Alias("开始日期")
	    private String startDate;
	    @Alias("结束日期")
	    private String endDate;
	    @Alias("成本")
	    private String cost;
	    @Alias("描述")
	    private String description;
	    @Alias("创建时间")
	    private String createTime;
	    @Alias("创建者")
	    private String createBy;
	    @Alias("修改时间")
	    private String editTime;
	    @Alias("修改者")
	    private String editBy;
		List<Activity> list = reader.readAll(Activity.class);
		**/
        // 方式3：忽略表头的中文，直接读取表的内容
        List<List<Object>> list = reader.read(1);
        List<Activity> act = CollUtil.newArrayList();
        for (List<Object> row : list) {
            Activity activity = new Activity();
            activity.setName(row.get(0).toString());
            activity.setStartDate(row.get(1).toString());
            activity.setEndDate(row.get(2).toString());
            activity.setCost(row.get(3).toString());
            activity.setDescription(row.get(4).toString());
            act.add(activity);
        }
     	//3.调用service的方法保存
        int i = activityService.saveCreateActivityByList(act);
        if (i>0){
            returnObject.setCode(Constants.CODE_200);
            returnObject.setMessage("数据导入成功,共导入"+i+"条数据");
            return returnObject;
        }else {
            returnObject.setMessage("文件上传失败!");
            returnObject.setCode(Constants.CODE_400);
            return returnObject;
        }
    }

前端导入

vue实现导入

<el-upload action="http://localhost:9090/workbench/activity/importExcel" :headers="myHeaders" :on-success="handleExcelImportSuccess" :show-file-list="false" accept="xlsx" style="display: inline-block">
	<el-button type="primary" icon="el-icon-folder-add" class="ml-5">导入</el-button>
</el-upload>
data(){
	myHeaders:{
		token:"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJhdWQiOiI0MGY2Y2RlYTBiZDM0YWNlYjc3NDkyYTE2NTZkOWZiMyIsImV4cCI6MTY2NzUyNjAxMX0.RCghA1LFJy01Q5HChlc4ILAIPXOsJqLWnA44LnYFVws",
   },
}

Vue实现导出

<el-button icon="el-icon-top" type="primary" style="margin-left:10px" class="ml-5" @click="activityExport">导出</el-button>
methods:{
	/*导出市场活动*/
    activityExport(){
        window.open("http://localhost:9090/workbench/activity/export")
   },
}

这里导入详细看我之前写的一篇element-ui中的upload组件带token的两种方法

点击查看el-upload带token的怎么实现