java后端服务器读取excel将数据导入数据库

使用的是easypoi，官网文档：http://easypoi.mydoc.io/

 1 /**
 2      * 导入Excel文件
 3      */
 4     @PostMapping("/importTeacher")
 5     @ResponseBody
 6     public RestResponse importTeacher(@RequestParam MultipartFile file) {
 7         System.out.println("进入1");
 8         ImportParams params =  new ImportParams();
 9         //设置excel表的标题
10         params.setTitleRows(1);
11         //设置excel字段的名字
12         params.setHeadRows(1);
13         List<TeacherDto> list = null;
14 
15         try {
16             list = ExcelImportUtil.importExcel(file.getInputStream(), TeacherDto.class, params);
17         } catch (Exception e) {
18             e.printStackTrace();
19             return RestResponse.failure("导入文件异常");
20         }
21 
22         if (!list.isEmpty()){
23             System.out.println("进入2");
24             TeacherDao teacherDao=new TeacherDao();
25                 //插入数据
26             teacherDao.insert(list);
27 
28         }else {
29             return RestResponse.failure("文件没有数据,导入失败");
30         }
31         return RestResponse.success("导入成功");
32     }
@GetMapping("/exportTeacher")
    public void exportTeacher(HttpServletResponse response) throws IOException {
        TeacherDao teacherDao=new TeacherDao();
        List<TeacherDto> list=teacherDao.export();

        ExportParams params = new ExportParams("teacher","teacher");
        Workbook workbook = ExcelExportUtil.exportBigExcel(params, TeacherDto.class, list);
        ExcelExportUtil.closeExportBigExcel();
        //文件命名
        SimpleDateFormat df = new SimpleDateFormat("MMddHHmmss");
        String date = df.format(new Date());
        String filename = "teacher_"+date+".xlsx";
        // 进行转码，使其支持中文文件名
        response.reset();
        response.setContentType("application/x-download; charset=utf-8");
        //清除jsp页面缓存，用window open打开非jsp页面导出，有此项IE会报错
        if (true) {
            //http 1.1
            response.setHeader("Cache-Control", "no-cache");
            //http 1.0
            response.setHeader("Pragma", "no-cache");
        }//http 1.0和1.1都支持
        response.setDateHeader("Expires", 0);
        filename = URLEncoder.encode(filename, "UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
        workbook.write(response.getOutputStream());
    }

需要一个对应的实体类

  1 public class TeacherDto  {
  2     public String getUid() {
  3         return uid;
  4     }
  5 
  6     public void setUid(String uid) {
  7         this.uid = uid;
  8     }
  9 
 10     public String getTenant_id() {
 11         return tenant_id;
 12     }
 13 
 14     public void setTenant_id(String tenant_id) {
 15         this.tenant_id = tenant_id;
 16     }
 17 
 18     public String getSchool_id() {
 19         return school_id;
 20     }
 21 
 22     public void setSchool_id(String school_id) {
 23         this.school_id = school_id;
 24     }
 25 
 26     public String getNickname() {
 27         return nickname;
 28     }
 29 
 30     public void setNickname(String nickname) {
 31         this.nickname = nickname;
 32     }
 33 
 34     public Integer getSex() {
 35         return sex;
 36     }
 37 
 38     public void setSex(Integer sex) {
 39         this.sex = sex;
 40     }
 41 
 42     public String getAge() {
 43         return age;
 44     }
 45 
 46     public void setAge(String age) {
 47         this.age = age;
 48     }
 49 
 50     public String getSchool() {
 51         return school;
 52     }
 53 
 54     public void setSchool(String school) {
 55         this.school = school;
 56     }
 57 
 58     public String getClazz() {
 59         return clazz;
 60     }
 61 
 62     public void setClazz(String clazz) {
 63         this.clazz = clazz;
 64     }
 65 
 66     public String getMobile() {
 67         return mobile;
 68     }
 69 
 70     public void setMobile(String mobile) {
 71         this.mobile = mobile;
 72     }
 73 
 74     public String getAccount() {
 75         return account;
 76     }
 77 
 78     public void setAccount(String account) {
 79         this.account = account;
 80     }
 81 
 82     public String getPassword() {
 83         return password;
 84     }
 85 
 86     public void setPassword(String password) {
 87         this.password = password;
 88     }
 89 
 90     @Override
 91     public String toString() {
 92         return "TeacherDto{" +
 93                 "uid='" + uid + '\'' +
 94                 ", tenant_id='" + tenant_id + '\'' +
 95                 ", school_id='" + school_id + '\'' +
 96                 ", nickname='" + nickname + '\'' +
 97                 ", sex=" + sex +
 98                 ", age='" + age + '\'' +
 99                 ", school='" + school + '\'' +
100                 ", clazz='" + clazz + '\'' +
101                 ", mobile='" + mobile + '\'' +
102                 ", account='" + account + '\'' +
103                 ", password='" + password + '\'' +
104                 '}';
105     }
106 
107     private String uid;
108     @Excel(name="tenant_id")
109     private String tenant_id;
110     @Excel(name="school_id")
111     private String school_id;
112     @Excel(name="nickname")
113     private String nickname;
114     @Excel(name="sex")
115     private Integer sex;
116     @Excel(name="age")
117     private String age;
118     @Excel(name="school")
119     private String school;
120     @Excel(name="clazz")
121     private String clazz;
122     @Excel(name="mobile")
123     private String mobile;
124     @Excel(name="account")
125     private String account;
126     @Excel(name="password")
127     private String password;
128 }

 

前端使用的是layui

<form class="layui-form">
    <div class="layui-inline">
        <button class="layui-btn" id="importExcel" type="button"><i class="layui-icon"></i>导入</button>
    </div>
    <div class="layui-inline">
        <button class="layui-btn" id="exportExcel" type="button"><i class="layui-icon"></i>导出</button>
    </div>
</form>
 1 <script>
 2 
 3     layui.use(['layer','form','table','upload'], function() {
 4         var layer = layui.layer,
 5                 $ = layui.jquery,
 6                 form = layui.form,
 7                 table = layui.table,
 8                 upload = layui.upload;
 9         //导入
10         upload.render({
11             elem: '#importExcel'
12             ,url: "${base}/admin/excel/importTeacher"
13             ,accept: 'file' //普通文件
14             ,multiple: true
15             ,done: function(res){
16                 if(res.success){
17                     layer.msg("上传成功",{time: 1000},function(){
18                         //上传成功后刷新
19 
20                     });
21                 }else{
22                     if(res.message == "导入失败！" || res.message == "导入文件格式不对" || res.message == "导入文件没有数据"){
23                         layer.msg(res.message);
24                     }else{
25                         layer.msg(res.message);
26                     }
27                 }
28             }
29         });
30 
31         //导出
32         $('#exportExcel').on('click', function () {
33             window.open('${base}/admin/excel/exportTeacher');
34             // window.open('http://www.imooc.com/','_blank','width=400,height=500,menubar=no,toolbar=no');
35         });
36 
37     });

 

posted @ 2018-11-05 10:50  帅的很耗cpu  阅读(4239)  评论(0)  编辑  收藏  举报