SpringBoot整合EasyExcel實現(xiàn)導入導出數(shù)據(jù)

第SpringBoot整合EasyExcel實現(xiàn)導入導出數(shù)據(jù)目錄前言1.前端2.數(shù)據(jù)庫3.后端3.1contrller3.2mapper3.3bean3.4listener3.5config3.6配置文件4.啟動測試

前言

創(chuàng)建一個普通的maven項目即可

項目目錄結(jié)構(gòu)

1.前端

存放在resources/static下

index.html

!DOCTYPEhtml

htmllang="en"

head

metacharset="UTF-8"/

metahttp-equiv="X-UA-Compatible"content="IE=edge"/

metaname="viewport"content="width=device-width,initial-scale=1.0"/

titleDocument/title

!--開發(fā)環(huán)境版本，包含了有幫助的命令行警告--

scriptsrc="/npm/vue@2/dist/vue.js"/script

!--引入樣式--

link

rel="stylesheet"

href="/element-ui/lib/theme-chalk/index.css"rel="externalnofollow"

!--引入組件庫--

scriptsrc="/element-ui/lib/index.js"/script

scriptsrc="/axios/dist/axios.min.js"/script

/head

body

divid="app"

div

div

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-itemlabel="請選擇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-buttonsize="small"type="primary"點擊上傳/el-button

/el-upload

/el-form-item

/el-form

divslot="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

el-table-columnprop="name"label="姓名"width="180"

/el-table-column

el-table-columnprop="birthday"label="生日"width="180"

/el-table-column

el-table-columnprop="salary"label="薪資"/el-table-column

/el-table

div

el-pagination

@size-change="handleSizeChange"

@current-change="handleCurrentChange"

:current="pageNum"

:sizes="[2,5,10,20]"

:size="pageSize"

background

layout="total,sizes,prev,pager,next,jumper"

:total="total"

/el-pagination

/div

/div

/div

/div

/body

script

newVue({

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(){

//使用自定義配置

constrequest=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ù)庫

CREATETABLE`student`(

`name`varchar(255)DEFAULTNULLCOMMENT'姓名',

`birthday`datetimeDEFAULTNULLCOMMENT'生日',

`salary`decimal(10,4)DEFAULTNULLCOMMENT'薪資'

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

3.后端

3.1contrller

StudentController

@Slf4j

@RestController

@CrossOrigin

@RequestMapping("/api/excel/")

publicclassStudentController{

@Resource

privateStudentMapperstudentMapper;

@GetMapping("list")

publicHashMapString,Objectlist(@RequestParamintpageNum,@RequestParamintpageSize){

//分頁查詢

PageStudentpage=newPage(pageNum,pageSize);

studentMapper.selectPage(page,null);

//封裝數(shù)據(jù)

HashMapString,Objectmap=newHashMap();

ArrayListExcelStudentDTOexcelDictDTOList=newArrayList();

//轉(zhuǎn)換數(shù)據(jù)

page.getRecords().forEach(student-{

ExcelStudentDTOstudentDTO=newExcelStudentDTO();

BeanUtils.copyProperties(student,studentDTO);

excelDictDTOList.add(studentDTO);

map.put("list",excelDictDTOList);

map.put("size",page.getTotal());

returnmap;

*導入

*@paramfile文件對象

@RequestMapping("import")

@Transactional(rollbackFor={Exception.class})

publicStringimportData(@RequestParam("file")MultipartFilefile){

try{

//讀取文件流

EasyExcel.read

(file.getInputStream(),//前端上傳的文件

ExcelStudentDTO.class,//跟excel對應的實體類

newExcelDictDTOListener(studentMapper))//監(jiān)聽器

.excelType(ExcelTypeEnum.XLSX)//excel的類型

.sheet("模板").doRead();

("importDatafinished");

}catch(IOExceptione){

("失敗");

e.printStackTrace();

return"上傳成功";

*導入

@GetMapping("export")

publicStringexportData(HttpServletResponseresponse){

try{

//設置響應體內(nèi)容

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

//這里URLEncoder.encode可以防止中文亂碼當然和easyexcel沒有關(guān)系

StringfileName=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(Exceptione){

e.printStackTrace();

return"上傳成功";

}

3.2mapper

StudentMapper

@Mapper

publicinterfaceStudentMapperextendsBaseMapperStudent{

voidinsertBatch(ListExcelStudentDTOlist);

}

StudentMapper.xml

xmlversion="1.0"encoding="UTF-8"

!DOCTYPEmapperPUBLIC"-////DTDMapper3.0//EN""/dtd/mybatis-3-mapper.dtd"

mappernamespace="look.word.mapper.StudentMapper"

insertid="insertBatch"

insertintostudent(name,birthday,salary)

values

foreachcollection="list"item="item"separator=","

#{},

#{item.birthday},

#{item.salary}

/foreach

/insert

/mapper

3.3bean

ExcelStudentDTO

導入數(shù)據(jù)時要保證excel中列名和ExcelStudentDTO一致奧

/**

*excel對應的實體類

*@authorjiejie

@Data

publicclassExcelStudentDTO{

//excel中的列名

@ExcelProperty("姓名")

privateStringname;

@ExcelProperty("生日")

privateDatebirthday;

@ExcelProperty("薪資")

privateBigDecimalsalary;

}

Student

/**