#使用tools-excel进行excel表格导入导出

#使用tools-excel进行excel表格导入导出

依赖

 <dependency>
            <groupId>cn.gjing</groupId>
            <artifactId>tools-excel</artifactId>
            <version>2.0.0</version>
        </dependency>

1.对应实体

package com.itany.myexcel.entity;

import cn.gjing.tools.excel.Excel;
import cn.gjing.tools.excel.ExcelField;
import cn.gjing.tools.excel.write.valid.ExcelDateValid;
import cn.gjing.tools.excel.write.valid.OperatorType;
import lombok.Data;

import java.time.LocalDate;
import java.util.Date;

/**
 * Author:XuDing
 * Date:2020/4/11
 */
@Data
@Excel("单级表头")
public class SingleHead {
    @ExcelField("姓名")
    private String userName;
    @ExcelField("爱好")
    private String favorite;
    @ExcelField(value = "年龄", format = "0")
    private Integer age;

    @ExcelField(value = "生日", format = "yyyy-MM-dd")
    @ExcelDateValid(expr1 = "2000-01-01", operatorType = OperatorType.LESS_OR_EQUAL, errorContent = "出生日期不能超过2000年")
    private Date birthday;


    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getFavorite() {
        return favorite;
    }

    public void setFavorite(String favorite) {
        this.favorite = favorite;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

2.controller类.导出

package com.itany.myexcel.controller;

import cn.gjing.tools.excel.ExcelFactory;
import com.itany.myexcel.entity.MultiHead;
import com.itany.myexcel.entity.SingleHead;
import com.itany.myexcel.service.UserService;
import com.wordnik.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * Author:XuDing
 * Date:2020/4/11
 */
@RestController
public class UserController {

    @Autowired
    private UserService userService;
    @GetMapping("/test_export")
    @ApiOperation("导出一级表头")
    public void testExcel(HttpServletResponse response){
        //指定映射的实体为刚刚定义的
        ExcelFactory.createWriter(SingleHead.class,response)
        .write(null).flush();
    }
    @GetMapping("/test_export2")
    @ApiOperation("导出一级表头")
    public void testExcel2(HttpServletResponse response){
        //指定映射的实体为刚刚定义的
        ExcelFactory.createWriter(MultiHead.class,response)
                //需要在write前激活多级表头，否则不会自动合并
                .enableMultiHead()
                .write(null).flush();
    }
    @GetMapping("/test_export3")
    @ApiOperation("带时间校验")
    public void testExport(HttpServletResponse response) {
        List<Object> all = userService.findAll();
        ExcelFactory.createWriter(SingleHead.class, response)
                //需要在write前激活校验
                .enableValid()
                .write(all)
                .flush();
    }
}

service：
@Override
public List findAll() {
System.out.println("---------------");
Listlist=new LinkedList<>();
for(int i=0;i<10;i++){
SingleHead s=new SingleHead();
s.setAge(10+i);
s.setBirthday(new Date());
s.setFavorite(“吃饭”+i);
s.setUserName(“姓名”+i);
list.add(s);
System.out.println("-----------------------");
}
return list;
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TPKOh9wH-1586678607858)(C:\Users\86151\AppData\Roaming\Typora\typora-user-images\image-20200412160312998.png)]

3.controller导入

package com.itany.myexcel.controller;

import cn.gjing.tools.excel.ExcelFactory;
import cn.gjing.tools.excel.write.resolver.ExcelWriter;
import com.google.gson.JsonArray;
import com.itany.myexcel.entity.ReadExcelUtil;
import com.itany.myexcel.entity.SingleHead;
import com.itany.myexcel.service.UserService;
import com.wordnik.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.Reader;

/**
 * Author:XuDing
 * Date:2020/4/11
 */
@RestController
@RequestMapping("/test")
public class TestController {
    @Resource
    private UserService userService;


    @PostMapping("/post")
    @ApiOperation("导入单表头")
    public void userExcel(@RequestParam(name = "file" ,required = false) MultipartFile file)throws IOException {
        System.out.println("file===>"+file);
//        JsonArray array=null;
//        String fileName = file.getName().toLowerCase();
//        Workbook workbook=null;
//        if(fileName.endsWith(".xlsx")){
//          workbook=new XSSFWorkbook(new FileInputStream((File) file));
//        }else if (fileName.endsWith(".xls")) {
//            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
//            workbook = new HSSFWorkbook(poifsFileSystem);
//        }
//        array= Read(workbook);
        ExcelFactory.createReader(file, SingleHead.class)
        //在read()方法前通过订阅方法增加一个结果监听器，该监听器会在每一次read()结束之后触发
                 //如果Excel中数据量太大，不建议使用结果监听器，会造成生成了过多的映射实体对象造成内存溢出
         .subscribe(e->this.userService.saveUser(e))
         .read().end();

    }

    @ExceptionHandler(Exception.class)
    public ModelAndView exceptionHander(Exception e){
        ModelAndView mav=new ModelAndView();
        mav.addObject("msg",e.getMessage());
        mav.setViewName("exception");
        return mav;
    }
}
 ModelAndView exceptionHander(Exception e){
        ModelAndView mav=new ModelAndView();
        mav.addObject("msg",e.getMessage());
        mav.setViewName("exception");
        return mav;
    }
}