excel表中数据导入到数据库中

一、引入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

二、工具类

直接上代码。

package com.wb.srpingboot.demoz.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * Description: poi将excle文件中的信息导入到数据库中工具类
 * </p>
 *
 * @author 阿萨
 * @version v1.0.0
 * @see com.wb.srpingboot.demoz.utils
 * @since 2020-08-25 19:42:28
 */
public class ImportExcel {
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * TODO: 判断文件是否符合标准
     *
     * @param fileName 文件命
     * @return boolean
     */
    private static Result isXls(String fileName) {
        // (?i)忽略大小写
        if (fileName.matches("^.+\\.(?i)(xls)$")) {
            return Result.ok("xls");
        } else if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return Result.ok("xlsx");
        } else {
            return Result.fail("文件格式不对");
        }
    }

    /**
     * TODO: 读取信息方法
     *
     * @param fileName    文件命
     * @param inputStream 文件
     * @return List<Map < String, Object>>
     */
    public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) {

        Result ret = isXls(fileName);
        //System.out.println(ret);
        Workbook workbook = null;
        // 根据后缀创建不同的对象
        try {
            if (XLS.equals(ret.getMessage())) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (XLSX.equals(ret.getMessage())) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                return null;
            }
            Sheet sheet = workbook.getSheetAt(0);
            // 得到标题行
            Row titleRow = sheet.getRow(0);

            int lastRowNum = sheet.getLastRowNum();
            int lastCellNum = titleRow.getLastCellNum();


            List<Map<String, Object>> list = new ArrayList<>();

            for (int i = 1; i <= lastRowNum; i++) {
                Map<String, Object> map = new HashMap<>();
                Row row = sheet.getRow(i);
                for (int j = 0; j < lastCellNum; j++) {
                    // 得到列名
                    String key = titleRow.getCell(j).getStringCellValue();
                    Cell cell = row.getCell(j);
                    cell.setCellType(CellType.STRING);

                    map.put(key, cell.getStringCellValue());
                }
                list.add(map);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

三、controller代码

/**
     * TODO: 将上传的excel文件导入到数据库中（仅支持xls和xlsx文件）
     * @param mFile 上传的文件
     * @return com.wb.srpingboot.demoz.utils.Result
     */
    @RequestMapping("/uploadExcel")
    @ResponseBody
    public Result importExcel(@RequestParam(value = "mFile")MultipartFile mFile) {

        InputStream inputStream = null;
        try {
            String fileName = mFile.getOriginalFilename();
            // 获取上传文件的输入流
            inputStream = mFile.getInputStream();
            // 调用工具类中方法，读取excel文件中数据
            List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);

            // 将对象先转为json格式字符串，然后再转为List<SysUser> 对象
            ObjectMapper objMapper = new ObjectMapper();
            String infos = objMapper.writeValueAsString(sourceList);

            // json字符串转对象
            List<User> list = objMapper.readValue(infos, new TypeReference<List<User>>() {});

            if (list != null && list.size() >0) {
                // 批量添加
                return userService.userAdds(list);
            }
            return Result.fail("文件格式错误,仅支持xls和xlsx格式的文件");

        } catch (Exception e) {
            e.printStackTrace();
            return Result.fail("文件格式错误");
        }finally {
            if (inputStream != null ){
                try {
                    inputStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

四、给出Result返回值类

我还是自定义了一个返回值类型，可以自己根据自己公司的规范进行更改。

package com.wb.srpingboot.demoz.utils;

import java.io.Serializable;

/**
 * Controller中处理方法的返回值类型
 * @author 阿萨
 * @since 1.0
 */
public class Result implements Serializable {

    // 状态码
    private int code;
    // 消息
    private String message;
    // 数据
    private Object data;

    public Result() { }

    public Result(int code, String message, Object data) {
        this.code = code;
        this.message = message;
        this.data = data;
    }

    public Result(int code, String message) {
        this(code, message, null);
    }

    public Result(int code, Object data) {
        this(code, null, data);
    }

    public static Result ok(String message, Object data) {
        return new Result(0, message, data);
    }

    public static Result ok(String message) {
        return new Result(0, message, null);
    }

    public static Result ok(Object data) {
        return new Result(0, null, data);
    }

    public static Result fail(String message) {
        return new Result(-1, message);
    }

    public static Result fail(String message, Object data) {
        return new Result(-1, message, data);
    }

    public static Result fail(Object data) {
        return new Result(-1, null, data);
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "Result{" +
                "code=" + code +
                ", message='" + message + '\'' +
                ", data=" + data +
                '}';
    }
}

感谢观看。