springboot+easypoi 导入数据

2023-12-30 20:50:52

首先添加依赖

       <!--    web    -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--   lombok主要是来简化实体类方法     -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- io常用工具类 -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

        <!-- 文件上传工具类 -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>

        <!--    easypoi 依赖    -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

新建 EasyPoiUtils 工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.pigs.springbooteasypoipigs.entity.UserEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * @author PIGS
 * @version 1.0
 * @date 2020/4/25 14:16
 * @effect :
 * 表格数据工具类
 */
public final class EasyPoiUtils {

    private EasyPoiUtils() {
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private static <T> void defaultExport(List<T> dataList, Class<?> clz, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clz, dataList);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(dataList, clz, fileName, response, exportParams);
    }

    public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, HttpServletResponse response) {
        defaultExport(dataList, clz, fileName, response, new ExportParams(title, sheetName));
    }

    private static void defaultExport(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static void exportExcel(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
        defaultExport(dataList, fileName, response);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clz) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);

        try {
            return ExcelImportUtil.importExcel(new File(filePath), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clz) {
        if (file == null) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);

        try {
            return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入数据
     * userEnity 你自己新建的实体类 实体类代码在下面
     * @param file
     * @param clz
     * @return
     */
    public static List<UserEntity> importExcel(MultipartFile file, Class<UserEntity> clz) {
        if (file == null) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        try {
            return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

新建一个实体类接收参数

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author PIGS
 * @version 1.0
 * @date 2020/4/25 14:16
 * @effect :
 * 用户实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserEntity {

    @Excel(name = "用户ID", width = 15)
    private Integer userId;

    @Excel(name = "用户名", width = 15)
    private String userName;

    @Excel(name = "用户性别", width = 15)
    private String userSex;

    @Excel(name = "用户年龄", width = 15)
    private Integer userAge;
}

新建一个前端控制器

import com.pigs.springbooteasypoipigs.entity.UserEntity;
import com.pigs.springbooteasypoipigs.utils.EasyPoiUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author PIGS
 * @version 1.0
 * @date 2020/4/25 14:22
 * @effect :
 * Excel 前端控制器
 */
@RestController
public class ExcelController {

    /**
     * 从表格插入数据
     * 接收并返回前台
     *
     * @param file
     * @return
     * @throws IOException
     */
    @RequestMapping("/uploadExcel")
    public Map<String,Object> uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
        List<UserEntity> checkingIns = EasyPoiUtils.importExcel(file, UserEntity.class);
        Map<String,Object> map = new HashMap<>();
        map.put("code",200);
        map.put("msg","ok");
        map.put("data",checkingIns);
        return map;
    }
}

新建一个html页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>上传excel</title>
</head>
<body>
<h1>上传excel文件</h1>
<form action="/uploadExcel" method="post" enctype="multipart/form-data">
    <p>文件上传</p>
    <input type="file" name="file">
    <p><input type="submit" value="提交"></p>
</form>
</body>
</html>

自制几条数据

这个字段名跟实体类 @Excel(name = "用户ID", width = 15) 需要一致不然会接收不到参数的

最后提交就完事了

demo 已经上传了 gitee了需要就去看吧

https://gitee.com/pig_farmer_x/springboot-easypoi-pigs

码农公寓

相关文章