SpringBoot整合EasyExcel實現(xiàn)導入導出數(shù)據(jù)
前言
創(chuàng)建一個普通的maven項目即可
項目目錄結構

1.前端
存放在resources/static 下
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<!-- 開發(fā)環(huán)境版本,包含了有幫助的命令行警告 -->
<script src="https://cdn.jsdelivr.net/npm/vue@2/dist/vue.js"></script>
<!-- 引入樣式 -->
<link
rel="stylesheet"
rel="external nofollow"
/>
<!-- 引入組件庫 -->
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
</head>
<body>
<div id="app">
<div class="app-container">
<div style="margin-bottom: 10px">
<el-button
@click="dialogVisible = true"
type="primary"
size="mini"
icon="el-icon-download"
>
導入Excel
</el-button>
<el-dialog
title="數(shù)據(jù)字典導入"
:visible.sync="dialogVisible"
width="30%"
>
<el-form>
<el-form-item label="請選擇Excel文件">
<el-upload
:auto-upload="true"
:multiple="false"
:limit="1"
:on-exceed="fileUploadExceed"
:on-success="fileUploadSuccess"
:on-error="fileUploadError"
:action="importUrl"
name="file"
accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
>
<!--accept 只接受某種格式的文件-->
<el-button size="small" type="primary">點擊上傳</el-button>
</el-upload>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogVisible = false">取消</el-button>
</div>
</el-dialog>
<!-- 導出 -->
<el-button
@click="exportData"
type="primary"
size="mini"
icon="el-icon-upload2"
>
導出Excel
</el-button>
<!-- 數(shù)據(jù)展示 -->
<el-table :data="list" stripe style="width: 100%">
<el-table-column prop="name" label="姓名" width="180">
</el-table-column>
<el-table-column prop="birthday" label="生日" width="180">
</el-table-column>
<el-table-column prop="salary" label="薪資"> </el-table-column>
</el-table>
<div>
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="pageNum"
:page-sizes="[2, 5, 10, 20]"
:page-size="pageSize"
background
layout="total, sizes, prev, pager, next, jumper"
:total="total"
>
</el-pagination>
</div>
</div>
</div>
</div>
</body>
<script>
new Vue({
el: '#app',
data() {
return {
dialogVisible: false, //文件上傳對話框是否顯示
list: [], // 字典的數(shù)據(jù)
importUrl: 'http://localhost:8811/api/excel/import',
pageNum: 1, // 頁數(shù)
pageSize: 5, // 每頁條數(shù)
total: 1000,
}
},
created() {
this.showList()
},
methods: {
showList() {
//使用自定義配置
const request = axios.create({
baseURL: 'http://localhost:8811', //url前綴
timeout: 1000, //超時時間
// headers: { token: 'helen123456' }, //攜帶令牌
})
request
.get('/api/excel/list', {
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
},
})
.then((res) => {
this.total = res.data.size
this.list = res.data.list
console.log(res)
})
},
// 上傳多于一個文件時
fileUploadExceed() {
this.$message.warning('只能選取一個文件')
},
// 導出
exportData() {
window.location.href = 'http://localhost:8811/api/excel/export'
},
//上傳成功回調(diào)
fileUploadSuccess(response) {
if (response.code === 0) {
this.$message.success('數(shù)據(jù)導入成功')
this.dialogVisible = false
} else {
this.$message.error(response.message)
}
},
//上傳失敗回調(diào)
fileUploadError(error) {
this.$message.error('數(shù)據(jù)導入失敗')
},
/**
* 用戶所選擇當前頁面展示的數(shù)據(jù)條數(shù)
*/
handleSizeChange(val) {
console.log(`每頁 ${val} 條`)
this.pageSize = val
this.showList()
},
handleCurrentChange(val) {
console.log(`當前頁: ${val}`)
this.pageNum = val
this.showList()
},
},
})
</script>
</html>2.數(shù)據(jù)庫
CREATE TABLE `student` ( `name` varchar(255) DEFAULT NULL COMMENT '姓名', `birthday` datetime DEFAULT NULL COMMENT '生日', `salary` decimal(10,4) DEFAULT NULL COMMENT '薪資' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.后端
3.1 contrller
StudentController
@Slf4j
@RestController
@CrossOrigin
@RequestMapping("/api/excel/")
public class StudentController {
@Resource
private StudentMapper studentMapper;
@GetMapping("list")
public HashMap<String, Object> list(@RequestParam int pageNum,@RequestParam int pageSize){
// 分頁查詢
Page<Student> page = new Page<>(pageNum, pageSize);
studentMapper.selectPage(page,null);
// 封裝數(shù)據(jù)
HashMap<String, Object> map = new HashMap<>();
ArrayList<ExcelStudentDTO> excelDictDTOList = new ArrayList<>();
// 轉(zhuǎn)換數(shù)據(jù)
page.getRecords().forEach(student -> {
ExcelStudentDTO studentDTO = new ExcelStudentDTO();
BeanUtils.copyProperties(student,studentDTO);
excelDictDTOList.add(studentDTO);
});
map.put("list",excelDictDTOList);
map.put("size",page.getTotal());
return map;
}
/**
* 導入
* @param file 文件對象
*/
@RequestMapping("import")
@Transactional(rollbackFor = {Exception.class})
public String importData( @RequestParam("file") MultipartFile file){
try {
// 讀取文件流
EasyExcel.read
(file.getInputStream(),// 前端上傳的文件
ExcelStudentDTO.class,// 跟excel對應的實體類
new ExcelDictDTOListener(studentMapper))// 監(jiān)聽器
.excelType(ExcelTypeEnum.XLSX)// excel的類型
.sheet("模板").doRead();
log.info("importData finished");
} catch (IOException e) {
log.info("失敗");
e.printStackTrace();
}
return "上傳成功";
}
/**
* 導入
*/
@GetMapping("export")
public String exportData(HttpServletResponse response){
try {
// 設置響應體內(nèi)容
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 這里URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關系
String fileName = URLEncoder.encode("myStu", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream()
,ExcelStudentDTO.class).sheet().doWrite(studentMapper.selectList(null));
} catch (Exception e) {
e.printStackTrace();
}
return "上傳成功";
}
}3.2 mapper
StudentMapper
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
void insertBatch(List<ExcelStudentDTO> list);
}StudentMapper.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="look.word.mapper.StudentMapper">
<insert id="insertBatch" >
insert into student(name, birthday, salary)
values
<foreach collection="list" item="item" separator=",">
(
#{item.name} ,
#{item.birthday} ,
#{item.salary}
)
</foreach>
</insert>
</mapper>3.3 bean
ExcelStudentDTO
導入數(shù)據(jù)時 要保證excel中列名和ExcelStudentDTO一致奧
/**
* excel對應的實體類
* @author jiejie
*/
@Data
public class ExcelStudentDTO {
// excel中的列名
@ExcelProperty("姓名")
private String name;
@ExcelProperty("生日")
private Date birthday;
@ExcelProperty("薪資")
private BigDecimal salary;
}Student
/**
* 數(shù)據(jù)庫對應的實體類
* @author jiejie
*/
@Data
@TableName(value = "student")
public class Student {
/**
* 姓名
*/
@TableField(value = "name")
private String name;
/**
* 生日
*/
@TableField(value = "birthday")
private Date birthday;
/**
* 薪資
*/
@TableField(value = "salary")
private BigDecimal salary;
public static final String COL_NAME = "name";
public static final String COL_BIRTHDAY = "birthday";
public static final String COL_SALARY = "salary";
}3.4 listener
EasyExcel讀取文件需要用到
ExcelDictDTOListener
/**
* 監(jiān)聽
* 再讀取數(shù)據(jù)的同時 對數(shù)據(jù)進行插入操作
* @author : look-word
* @date : 2022-05-10 21:35
**/
@Slf4j
//@AllArgsConstructor //全參
@NoArgsConstructor //無參
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelStudentDTO> {
/**
* 每隔5條存儲數(shù)據(jù)庫,實際使用中可以3000條,然后清理list ,方便內(nèi)存回收
*/
private static final int BATCH_COUNT = 5;
List<ExcelStudentDTO> list = new ArrayList<ExcelStudentDTO>();
private StudentMapper studentMapper;
//傳入mapper對象
public ExcelDictDTOListener(StudentMapper studentMapper) {
this.studentMapper = studentMapper;
}
/**
*遍歷每一行的記錄
* @param data
* @param context
*/
@Override
public void invoke(ExcelStudentDTO data, AnalysisContext context) {
log.info("解析到一條記錄: {}", data);
list.add(data);
// 達到BATCH_COUNT了,需要去存儲一次數(shù)據(jù)庫,防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存儲完成清理 list
list.clear();
}
}
/**
* 所有數(shù)據(jù)解析完成了 都會來調(diào)用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 這里也要保存數(shù)據(jù),確保最后遺留的數(shù)據(jù)也存儲到數(shù)據(jù)庫
saveData();
log.info("所有數(shù)據(jù)解析完成!");
}
/**
* 加上存儲數(shù)據(jù)庫
*/
private void saveData() {
log.info("{}條數(shù)據(jù),開始存儲數(shù)據(jù)庫!", list.size());
studentMapper.insertBatch(list); //批量插入
log.info("存儲數(shù)據(jù)庫成功!");
}
}3.5 config
mybatisPlus分頁插件
MybatisPlusConfig
@Configuration
public class MybatisPlusConfig {
/**
* 新的分頁插件,一緩和二緩遵循mybatis的規(guī)則,
* 需要設置 MybatisConfiguration#useDeprecatedExecutor = false
* 避免緩存出現(xiàn)問題(該屬性會在舊插件移除后一同移除)
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setDbType(DbType.MYSQL);
paginationInnerInterceptor.setOverflow(true);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> configuration.setUseDeprecatedExecutor(false);
}
}3.6 配置文件
application.yaml
server:
port: 8811
spring:
datasource: # mysql數(shù)據(jù)庫連接
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezone=GMT%2B8&characterEncoding=utf-8
username: root
password: 317311
mybatis-plus:
configuration:# sql日志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations:
- classpath:mapper/*.xml4.啟動測試
啟動springboot哦
頁面效果圖

導出效果

注意
導入數(shù)據(jù)時要保證excel中列名和ExcelStudentDTO一致奧
到此這篇關于SpringBoot整合EasyExcel實現(xiàn)導入導出數(shù)據(jù)的文章就介紹到這了,更多相關SpringBoot EasyExcel導入導出數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
- SpringBoot?整合?EasyExcel?實現(xiàn)自由導入導出功能
- SpringBoot整合EasyExcel實現(xiàn)批量導入導出
- SpringBoot整合easyExcel實現(xiàn)CSV格式文件的導入導出
- SpringBoot整合EasyExcel實現(xiàn)復雜Excel表格的導入導出
- 使用SpringBoot與EasyExcel實現(xiàn)復雜的導入導出
- SpringBoot整合EasyExcel實現(xiàn)導入導出功能
- SpringBoot中EasyExcel實現(xiàn)execl導入導出
- SpringBoot整合EasyExcel實現(xiàn)文件導入導出
- Springboot整合easyexcel實現(xiàn)一個接口任意表的Excel導入導出
相關文章
springboot框架中如何整合mybatis框架思路詳解
這篇文章主要介紹了springboot框架中如何整合mybatis框架,本文通過示例圖文相結合給大家介紹的非常詳細,需要的朋友可以參考下2022-12-12
SpringBoot整合token實現(xiàn)登錄認證的示例代碼
本文主要介紹了SpringBoot整合token實現(xiàn)登錄認證的示例代碼,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2022-07-07

