用springboot和mybais-plus技术实现将Excel数据导入到数据库中

        首先需要在pom.xml文件中添加spring-boot-starter-web、mybatis-plus-boot-starter和poi-ooxml依赖：

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>最新版本</version>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
  </dependency>
</dependencies>

        然后创建一个Excel文件解析工具类ExcelUtil，包括读取Excel文件、解析Excel内容、生成Insert SQL语句的方法：

import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.util.*;

public class ExcelUtil {
    public static List<Map<String, Object>> readExcel(File file) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            List<String> headerList = new ArrayList<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                headerList.add(headerRow.getCell(i).getStringCellValue());
            }

            Iterator<Row> rowIterator = sheet.rowIterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (row.getRowNum() == 0) {
                    continue;
                }
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        map.put(headerList.get(i), "");
                    } else {
                        switch (cell.getCellType()) {
                            case STRING:
                                map.put(headerList.get(i), cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                map.put(headerList.get(i), cell.getNumericCellValue());
                                break;
                            case BOOLEAN:
                                map.put(headerList.get(i), cell.getBooleanCellValue());
                                break;
                            default:
                                map.put(headerList.get(i), "");
                        }
                    }
                }
                dataList.add(map);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public static String generateInsertSql(String tableName, List<Map<String, Object>> dataList) {
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ");
        sb.append(tableName);
        sb.append("(");
        for (String key : dataList.get(0).keySet()) {
            sb.append(key);
            sb.append(", ");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.deleteCharAt(sb.length() - 1);
        sb.append(") VALUES ");
        for (Map<String, Object> dataMap : dataList) {
            sb.append("(");
            for (Object value : dataMap.values()) {
                if (value instanceof String) {
                    sb.append("'");
                    sb.append(value);
                    sb.append("', ");
                } else {
                    sb.append(value);
                    sb.append(", ");
                }
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.deleteCharAt(sb.length() - 1);
            sb.append("), ");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }
}

接下来创建一个数据表和实体类，以导入Excel中的用户信息为例：

• 创建一个user表，包含id、name、age、email四个字段
• 创建一个User实体类，包含id、name、age、email四个属性及其get/set方法

然后在Controller中处理文件上传请求，最后将Excel中的数据保存到数据库中：

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @PostMapping("/import")
    public String importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        File tempFile = File.createTempFile("temp", ".xlsx"); // 创建临时文件
        file.transferTo(tempFile); // 将上传的文件写入临时文件
        List<Map<String, Object>> dataList = ExcelUtil.readExcel(tempFile); // 读取Excel数据
        for (Map<String, Object> data : dataList) {
            String username = (String) data.get("username");
            String email = (String) data.get("email");
            String password = (String) data.get("password");
            if (StringUtils.isEmpty(username) || StringUtils.isEmpty(email) ||  StringUtils.isEmpty(password)) {
            continue;
        }
        User user = new User();
        user.setUsername(username);
        user.setEmail(email);
        user.setPassword(password);
        userService.saveOrUpdate(user); // 保存或更新用户信息
    }
    return "import success";
}

        以上代码中，使用了ExcelUtil工具类来读取Excel文件，并将数据保存到数据库中。注意，在保存或更新用户信息时，我们使用了Mybatis Plus提供的saveOrUpdate方法，这个方法会根据主键是否存在来判断是执行保存还是更新操作。