Excel 导入数据到数据库（基于base64)

• 前端代码

①在element plus 里找到文件上传组件 直接粘贴。

 <!-- 导入excel到数据库 -->
  <el-upload
    class="upload-demo"
    :on-change="onChange"
    :auto-upload="false"
  >
    <el-icon class="el-icon--upload"><upload-filled /></el-icon>
    <div class="el-upload__text"> <em>导入数据</em>
    </div>
  </el-upload>

<script lang="ts" setup>
import { ref, onMounted } from "vue";
import { uploadApi } from "@/api/index";
import { ElMessage } from "element-plus";

//定义文件上传的函数
const onChange = (file: any, _uploadFiles: any) => {
  let reader = new FileReader();
  reader.readAsDataURL(file.raw);
  reader.onload = (f) => {
    callUploadApi(file.name, f.target?.result);
  };
};
//文件上传的函数
const callUploadApi = (name: any, base64: any) => {
  uploadApi.uploadExcel.call({ name, base64 }).then((res: any) => {
    ElMessage.success("上传成功");
  });
};
</script>

② uploadApi.ts

 uploadExcel: {
        name: "上传excel",
        url: "/api/uploade/excel",
        call: async function name(params: any) {
          return await http.post(this.url, params);
        },
      },

• 后端代码

① 数据库脚本 userx.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for userx
-- ----------------------------
DROP TABLE IF EXISTS `userx`;
CREATE TABLE `userx`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of userx
-- ----------------------------
INSERT INTO `userx` VALUES (1, '11', '11');
INSERT INTO `userx` VALUES (2, '22', '11');
INSERT INTO `userx` VALUES (3, '11', '1');
INSERT INTO `userx` VALUES (4, '11', '1');
INSERT INTO `userx` VALUES (5, '11', '11');

SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for userx
-- ----------------------------
DROP TABLE IF EXISTS `userx`;
CREATE TABLE `userx`  (
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `base64` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of userx
-- ----------------------------
INSERT INTO `userx` VALUES ('11', '11');
INSERT INTO `userx` VALUES ('22', '22');
INSERT INTO `userx` VALUES ('11', '11');
INSERT INTO `userx` VALUES ('11', '11');

SET FOREIGN_KEY_CHECKS = 1;

② 用于接受base64 写一个类来接收 （这个类只是单纯接受前端的base64 不用建表）

@Data
public class UploadInfo {
    private String name;
    private String  base64;
}

② 实体类 UserX.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserX {
    private Integer id;
    private String name ;
    private String sex;

}

③ UsersController

@RestController
@RequestMapping("/api")
public class UsersController{
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    // 导入
    @Autowired
    private UserXDao userXDao; // service 无业务 直接调用 dao层
    /**
     *导入Excel
     */
    @PostMapping("/uploade/excel")
    public String upload(@RequestBody UploadInfo uploadInfo) throws Exception {
        /**
         * 接受前端传递的base64 和 name
         * uploadInfo :只有两个字段 不需要有对应的数据表
         */
        String name = uploadInfo.getName();
        String base64 = uploadInfo.getBase64();
        String[] strArray = StrUtil.splitToArray(base64, "base64,");
        byte[] bytes = Base64.decode(strArray[1]);

        //用于创建一个基于字节数组的输入流。它允许你从一个字节数组中读取数据。
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
        // 使用Hutool读取Excel文件
        ExcelReader reader = ExcelUtil.getReader(byteArrayInputStream);
        //将读取到的 reader 转化为 List<Man>集合
        List<UserX> persons = reader.readAll(UserX.class);

        //StopWatch类是 Hutool 工具库中的类,用于测量代码执行时间
        StopWatch stopWatch = new StopWatch();
        //读取数据的结束时间同时也是写入数据库的开始时间
        stopWatch.start();

        //sqlSessionFactory是通过ioc容器注入的  设置其SqlSession的执行器格式ExecutorType.SIMPLE（默认）
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        UserXDao mapper = sqlSession.getMapper(UserXDao.class);
        //循环将List<Man>中的数据插入数据库 方法一
        //for (Person person : persons) {
        //    PoItemMapper.xml.insert(person);
        //}
        //方法二：
        mapper.insertBatch(persons);
        sqlSession.commit();
        stopWatch.stop();
        sqlSession.close();
        System.out.println("插入数据库最终的结果为：" + stopWatch.getTotalTimeSeconds());
        return "ok";
    }

}

④ UserXDao

@Mapper
public interface UserXDao {
    void insertBatch(List<UserX> users);
}

⑤ UserXMapper.xml

   <insert id="insertBatch">
        INSERT INTO userx(name,sex)
        VALUES
        <foreach collection="users" item="user" separator=",">
            (#{user.name},#{user.sex})
        </foreach>
    </insert>

• 注意点：模板里一定要有数据 否则会报字符串超出范围 非常恶心。
•