一、首先在前端写一个上传功能。
<template>
<!-- 文件上传 -->
<el-upload class="upload-demo" :on-change="onChange" :auto-upload="false">
<el-button type="primary">上传Excel</el-button>
</el-upload>
<a target="_blank" type="success" href="/api/upload/write">导出Excle</a>
</template>
<script lang="ts" setup>
import { ref } 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: "上传文件",
url: "/api/upload/excel",
call: async function name(params: any) {
return await http.post(this.url, params);
},
},
二、后端代码实现
准备工作:1、一个与你上传数据相对于的实体类;
2、定义一个上传信息对象;
实体类:为了方便演示我就定义两个字段,以供参考:
@Data
public class Person {
private Integer id;
private String name;
private Integer age;
}
上传信息对象:
@Data
public class UploadInfo {
private String name;
private String base64;
}
Controller层代码:
/*
* Copyright (c) 2020, 2024, All rights reserved.
*
*/
package com.by.upload;
import cn.hutool.core.codec.Base64;
import cn.hutool.core.date.StopWatch;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.by.dao.PersonMapper;
import com.by.model.FileInfo;
import com.by.model.Upload;
import com.by.service.UploadService;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.List;
import java.util.stream.Collectors;
/**
* <p>Project: wms-root - Upload</p>
* <p>Powered by scl On 2024-02-29 19:07:05</p>
* <p>描述:<p>
*
* @author 孙臣龙 [1846080280@qq.com]
* @version 1.0
* @since 17
*/
@RestController
@RequestMapping("/api/upload")
public class UploadExcel {
@Autowired
private SqlSessionFactory sqlSessionFactory;
@Autowired
private PersonMapper personMapper;
@Autowired
private UploadService uploadService;
/**
* 导入excle
* @param uploadInfo
* @return
* @throws Exception
*/
@PostMapping("/excel")
public String upload(@RequestBody Upload uploadInfo) throws Exception {
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<Person> persons = reader.readAll(Person.class);
//StopWatch类是 Hutool 工具库中的类,用于测量代码执行时间
StopWatch stopWatch = new StopWatch();
//读取数据的结束时间同时也是写入数据库的开始时间
stopWatch.start();
//sqlSessionFactory是通过ioc容器注入的 设置其SqlSession的执行器格式ExecutorType.SIMPLE(默认)
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
PersonMapper mapper = sqlSession.getMapper(PersonMapper.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";
}
/**
* 导出Excel
* @param response
* @throws IOException
*/
@GetMapping("/write")
public void exportExcel(HttpServletResponse response) throws IOException {
// 创建Excel写入器 参数 true 表示追加数据,即在已有的 Excel 文件上追加新数据。如果设为 false,则会覆盖已有的数据。
List<Person> person = personMapper.selectAll();
// 创建ExcelWriter对象
ExcelWriter writer = ExcelUtil.getWriter(true);
int i = 0;
while (true) {
List<Person> list = person.stream().skip(i * 100000).limit(100000).parallel().collect(Collectors.toList());
if (list.isEmpty()) {
break;
}
writer.setSheet("person" + i);
// 写入表头
writer.addHeaderAlias("id", "Id");
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
// 写入当前批次的数据
writer.write(list, true);
i++;
}
//response为HttpServletResponse对象 设置响应的内容类型为Excel文件
response.setContentType("application/xlsx;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
//设置响应头,告诉浏览器以附件形式下载文件,文件名为test.xlsx。这样设置可以让浏览器弹出文件下载对话框。
response.setHeader("Content-Disposition", "attachment;filename="+"test.xlsx");
//获取响应输出流,它是用于将响应的数据发送给客户端的流。
ServletOutputStream out = response.getOutputStream();
//将Excel数据写入输出流。第二个参数为true表示追加写入,即将数据追加到已有的Excel文件中。
writer.flush(out, true);
writer.close();
//关闭输出流
out.close();
}
}
dao层代码:
/*
* Copyright (c) 2020, 2024, All rights reserved.
*
*/
package com.by.dao;
import com.by.upload.Person;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* <p>Project: wms-root - PersonMapper</p>
* <p>Powered by scl On 2024-03-02 10:51:08</p>
* <p>描述:<p>
*
* @author 孙臣龙 [1846080280@qq.com]
* @version 1.0
* @since 17
*/
@Mapper
public interface PersonMapper {
void insert(Person person);
void insertBatch(List<Person> persons);
List<Person> selectAll();
}
Mapper.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.by.dao.PersonMapper">
<!--新增数据-->
<insert id="insert">
insert into person(name,age)
values (#{name},#{age})
</insert>
<!-- 批量新增数据 -->
<insert id="insertBatch" >
insert into person(name,age)
values
<foreach collection="persons" item="entity" separator=",">
(#{entity.name},#{entity.age})
</foreach>
</insert>
<select id="selectAll" resultType="com.by.upload.Person">
select * from person
</select>
</mapper>
注意:poi的版本号
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
三、文件导出。
前端代码:文章来源:https://www.toymoban.com/news/detail-847272.html
<template>
<a target="_blank" type="success" href="/api/upload/write">导出Excle</a>
</template>
<script lang="ts" setup>
</script>
特别注意:如果你有拦截器和Spring Sercurity,这两个都需要放开请求。文章来源地址https://www.toymoban.com/news/detail-847272.html
到了这里,关于Java通过Excel批量上传数据!!!的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!