easypoi导入导出工具类

前言

        每次导入和导出都需要写一些重复的代码太繁琐，下面提供一个工具类，只要传入相应的类和数据即可轻松实现

pom

我们引入mybatis-pus，因为可以会用他的公共保存方法

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>2.0.14</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.22</version>
        </dependency>

ExcelUtil类重点

public class ExcelUtil {

    public static boolean importData(MultipartFile file, Class clazz, IService service) {
        ImportParams importParams = new ImportParams();
        //标题行设置为1行，默认是0，可以不设置；依实际情况设置。
        importParams.setTitleRows(1);
        // 表头设置为1行
        importParams.setHeadRows(1);
        try {
            List<Object> users = ExcelImportUtil.importExcel(file.getInputStream(), clazz, importParams);
            boolean flag = service.saveBatch(users);
            return flag;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static void exportData(HttpServletResponse response, Collection<?> list, Class clazz, String fileName, String sheetName, String title) {
        try {
            response.setHeader("content-Type", "application/vnd.ms-excel");
            fileName = new String(fileName.getBytes(), "ISO-8859-1");
            //设置下载名称
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            //字节流输出
            ServletOutputStream out = response.getOutputStream();
            //设置excel参数
            ExportParams params = new ExportParams();
            //设置sheet名
            params.setSheetName(sheetName);
            //设置标题
            params.setTitle(title);
            //导入excel
            Workbook workbook = ExcelExportUtil.exportExcel(params, clazz, list);
            //写入
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

这里的IService类就是mybatis-pus提供的接口类，一般我们的service接口继承此接口

比如下面代码

public interface SiteService extends IService<SiteEntity> {
}

@Service
public class SiteServiceImpl extends ServiceImpl<SiteMapper, SiteEntity> implements SiteService {
}
public interface SiteMapper extends BaseMapper<SiteEntity> {
}

提供controller

@RestController
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private SiteService siteService;

    /**
     * excel批量导入用户
     *
     * @return
     */
    @GetMapping("/import")
    public boolean importUsers(@RequestParam("file") MultipartFile file) {
        return ExcelUtil.importData(file, SiteEntity.class, siteService);
    }

    @GetMapping("/export")
    public void exportUsers(HttpServletResponse response) {
        List<SiteEntity> list = siteService.list();
        ExcelUtil.exportData(response, list, SiteEntity.class, "站点测试数据.xlsx", "数据", "标题");
    }
}

很简单吧，下次出个批量导入的，也是很简单