SpringBoot通过Ajax批量将excel中数据导入数据库

2022-12-08 15:53:24

Spring Boot通过Ajax上传Excel并将数据批量读取到数据库中

适合场景：需要通过excel表格批量向数据库中导入信息

操作流程

【1】前端上传一个excel表格

【2】后端接收这个excel表格，将表格中的数据存入List集合中

【3】后端通过这个List集合将数据批量填入数据库中

源码地址：https://gitee.com/residual-temperature/import-excel-demo

实现过程

1、pom文件中要加入的jar包

 <!-- 导入excel相关 -->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi</artifactId>

            <version>3.9</version>

        </dependency>

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi-ooxml</artifactId>

            <version>3.9</version>

        </dependency>

因为加入这两个jar包application.yml文件中没有添加配置，所以这里就不展示

2、实体类User

@Repository

@Data

@AllArgsConstructor

@NoArgsConstructor

public class User implements Serializable {

  private long id;

  private String account;

  private String password;

  private String username;

  private String address;

}

3、UserMapper文件

@Mapper

@Repository

public interface UserMapper {

    // 批量添加用户数据

    public int addBatchUser(List<User> userList);

    // 根据用户账号查询用户数据

    public User findUserByAccount(String account);

    // 根据用户账号修改用户信息

    public int updateUserByAccount(User user);

}

4、UserMapper.xml文件的配置

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace= "com.yuwen.mapper.UserMapper" >

	<!-- 批量导入的实现 -->

    <insert id="addBatchUser">

        insert into user(account,password,username,address)

        values

        <foreach collection="userList" item="item" separator=",">

            (

                #{item.account},

                #{item.password},

                #{item.username},

                #{item.address}

            )

        </foreach>

    </insert>

    <select id="findUserByAccount" resultType="com.yuwen.pojo.User">

        select * from user where account = #{account}

    </select>

    <update id="updateUserByAccount">

        update user set account = #{account},password = #{password},username = #{username},address = #{address}

        where account = #{account}

    </update>

</mapper>

因为UserService只有方法没有实现，所以直接看UserService的实现类UserServiceImpl文件

5、UserServiceImpl文件

@Service

public class UserServiceImpl implements UserService {

    @Resource

    UserMapper userMapper;

    @Resource

    ImportExcelUtils excelUtils;

    /**

     * 批量添加用户信息

     * @param userList 用户信息

     * @return 用户信息是否添加成功

     */

    @Override

    public boolean addBatchUser(List<User> userList) {

        return userMapper.addBatchUser(userList) > 0;

    }

    /**

     * 根据用户账号查看用户是否存在

     * @param account 用户账号

     * @return 查询到的用户信息

     */

    @Override

    public User findUserByAccount(String account) {

        return userMapper.findUserByAccount(account);

    }

    /**

     * 根据用户账号修改用户信息

     * @param user 用户信息

     * @return 是否修改成功

     */

    @Override

    public boolean updateUserByAccount(User user) {

        return userMapper.updateUserByAccount(user) > 0;

    }

    /**

     * 批量导入用户信息

     * @param fileName 导入的表名

     * @param is 导入表的输入流

     * @return 是否导入成功

     */

    @Override

    public boolean importUserInfo(String fileName, InputStream is){

        try {

            List<User> userList = excelUtils.upload(fileName, is);

            for (int i = 0; i < userList.size(); i++) {

                User user = findUserByAccount(userList.get(i).getAccount()); // 查找数据库中看这个用户信息是否存在

                if (user != null){

                    updateUserByAccount(userList.get(i)); // 更新数据库中的用户信息

                    userList.remove(i); // 移除更新后的用户

                    i = i - 1; // 因为移除了，所以userList大小减了一而循环加了一，所以要减回去

                }

            }

            return addBatchUser(userList); // 批量添加

        } catch (Exception e) {

            e.printStackTrace();

            return false;

        }

    }

}

6、UserController文件

@RestController

public class UserController {

    @Resource

    UserService userService;

    // 批量添加数据

    @PostMapping("/upload")

    public Map<String,String> upload(MultipartFile excelFile) throws Exception{

        Map<String,String> map = new HashMap<>();

        if (excelFile.isEmpty()){

            map.put("mag","文件夹为空，重新上传");

            return map;

        }else {

            String fileName = excelFile.getOriginalFilename();

            InputStream is = excelFile.getInputStream();

            if (userService.importUserInfo(fileName,is)){

                map.put("msg","数据添加成功");

                return map;

            }else {

                map.put("msg","数据添加失败，请重新添加");

                return map;

            }

        }

    }

}

7、导入excel的工具类ImportExcelUtils的编写

批量导入的主要代码，主要呈现在这个工具类和上面UserMapper.xml中的批量导入

@Component

public class ImportExcelUtils {

    // 将表格中的数据添加到List集合中

    public List<User> upload(String fileName, InputStream is) throws Exception{

        Workbook workbook = getWorkbook(fileName,is);

        List<User> userList = new ArrayList<>();

        int number = workbook.getNumberOfSheets(); // 获取sheet值

        for (int i = 0; i < number; i++) {

            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码

            if (sheet != null){

                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行

                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取

                    Row row = sheet.getRow(j); // 获取表格行

                    User user = new User();

                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型

                    user.setAccount(row.getCell(0).getStringCellValue()); // 获取表格单元格并给User设置值

                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);

                    user.setPassword(row.getCell(1).getStringCellValue());

                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);

                    user.setUsername(row.getCell(2).getStringCellValue());

                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);

                    user.setAddress(row.getCell(3).getStringCellValue());

                    System.out.println(user);

                    userList.add(user);

                }

            }

        }

        return userList;

    }

    // 判断传入的文件是哪种类型的excel文件

    public Workbook getWorkbook(String fileName,InputStream is) throws Exception{

        Workbook workbook = null;

        String name = fileName.substring(fileName.lastIndexOf("."));

        System.out.println(name);

        if (".xls".equals(name)){

            workbook = new HSSFWorkbook(is);

        }else if (".xlsx".equals(name)){

            workbook = new XSSFWorkbook(is);

        }else {

            throw new Exception(" 请上传.xls/.xlsx格式文件！");

        }

        return workbook;

    }

}

8、html页面的编写

<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="UTF-8">

    <title>上传文件</title>

    <script src="http://lib.sinaapp.com/js/jquery/1.9.1/jquery-1.9.1.min.js"></script>

    <script>

        function upload(){

            var formData = new FormData(); //创建一个formData对象实例

            var excelFile = $("#uploadFile").get(0).files[0];

            formData.append("excelFile",excelFile)

            $.ajax({

                url: 'http://localhost:8081/upload',

                data: formData,

                type: 'post',

                contentType:false, // 必须false才会自动加上正确的Content-Type

                processData: false, // 必须false才会避开jQuery对 formdata 的默认处理,XMLHttpRequest会对 formdata 进行正确的处理

                success: function (resp){

                    console.log(resp);

                    // 后续的操作

                }

            })

        }

    </script>

</head>

<body>

    <p>上传的文件</p>

    <input multiple="multiple" type="file" id="uploadFile">

    <br>

    <br>

    <button onclick="upload()">上传</button>

</body>

</html>