SpringBoot + Vue实现导入Excel到数据库与导出数据库数据到Excel表格
一、导入excel表格到数据库
(一)后端实现
1.导入POI依赖
< dependency>
< groupId> org.apache.poi</ groupId>
< artifactId> poi</ artifactId>
< version> 3.14</ version>
</ dependency>
< dependency>
< groupId> org.apache.poi</ groupId>
< artifactId> poi-ooxml</ artifactId>
< version> 3.14</ version>
</ dependency>
2.下载模板
controller层
@GetMapping ( "/download" )
@ApiOperation ( value= "文件下载" , notes= "描述的具体信息可以省略" )
public ResultBody download ( HttpServletResponse response) {
try {
response. setContentType ( "application/octet-stream;charset=UTF-8" ) ;
String filename = "attachment;filename=\""
+ URLEncoder. encode ( "课题管理模板.xls" , "UTF-8" ) + "\";" ;
response. setHeader ( "Content-disposition" , filename) ;
HSSFWorkbook wb = new HSSFWorkbook ( ) ;
service. download ( wb) ;
try {
wb. write ( response. getOutputStream ( ) ) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
} catch ( Exception e) {
return ResultBody. failure ( e. getMessage ( ) ) ;
}
return ResultBody. success ( ) ;
}
service层
public void download ( HSSFWorkbook wb) ;
@Override
public void download ( HSSFWorkbook wb) {
HSSFSheet sheet = null;
sheet = wb. createSheet ( "课题管理模板导入模板" ) ;
HSSFRow row1 = sheet. createRow ( 0 ) ;
sheet. setColumnWidth ( 0 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 1 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 2 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 3 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 4 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 5 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 6 , ( int ) 55.7 * 100 ) ;
sheet. setColumnWidth ( 7 , ( int ) 35.7 * 100 ) ;
sheet. setColumnWidth ( 8 , ( int ) 35.7 * 100 ) ;
HSSFCellStyle style = wb. createCellStyle ( ) ;
style. setFillForegroundColor ( HSSFColor. GREY_25_PERCENT. index) ;
style. setFillPattern ( HSSFCellStyle. SOLID_FOREGROUND) ;
style. setAlignment ( HSSFCellStyle. ALIGN_CENTER) ;
HSSFFont font = wb. createFont ( ) ;
font. setBoldweight ( font. BOLDWEIGHT_BOLD) ;
font. setFontHeightInPoints ( ( short ) 11 ) ;
font. setFontName ( "宋体" ) ;
style. setFont ( font) ;
style. setBorderTop ( HSSFBorderFormatting. BORDER_THIN) ;
style. setBorderBottom ( HSSFBorderFormatting. BORDER_THIN) ;
style. setBorderLeft ( HSSFBorderFormatting. BORDER_THIN) ;
style. setBorderRight ( HSSFBorderFormatting. BORDER_THIN) ;
style. setTopBorderColor ( HSSFColor. BLACK. index) ;
style. setBottomBorderColor ( HSSFColor. BLACK. index) ;
style. setLeftBorderColor ( HSSFColor. BLACK. index) ;
style. setRightBorderColor ( HSSFColor. BLACK. index) ;
HSSFCell pCell = row1. createCell ( 0 ) ;
pCell. setCellValue ( new HSSFRichTextString ( "课题代号" ) ) ;
pCell. setCellStyle ( style) ;
HSSFCell proCell = row1. createCell ( 1 ) ;
proCell. setCellValue ( new HSSFRichTextString ( "课题名称" ) ) ;
proCell. setCellStyle ( style) ;
HSSFCell modelCell = row1. createCell ( 2 ) ;
modelCell. setCellValue ( new HSSFRichTextString ( "课题主管" ) ) ;
modelCell. setCellStyle ( style) ;
HSSFCell partCell = row1. createCell ( 3 ) ;
partCell. setCellValue ( new HSSFRichTextString ( "所属型号" ) ) ;
partCell. setCellStyle ( style) ;
HSSFCell partNameCell = row1. createCell ( 4 ) ;
partNameCell. setCellValue ( new HSSFRichTextString ( "所属系统" ) ) ;
partNameCell. setCellStyle ( style) ;
HSSFCell yearCountCell = row1. createCell ( 5 ) ;
yearCountCell. setCellValue ( new HSSFRichTextString ( "课题描述" ) ) ;
yearCountCell. setCellStyle ( style) ;
}
3.上传文件
controller层
@PostMapping ( "/upload" )
public ResultBody importData ( MultipartFile file, HttpServletRequest req) throws IOException {
List< Topic> topics= PoiUtils. parseFile2List ( file) ;
try {
service. addTopicList ( topics) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
return ResultBody. failure ( e. getMessage ( ) ) ;
}
return ResultBody. success ( ) ;
}
service层
public Integer addTopicList ( List< Topic> topics) ;
@Override
public List< Topic> findAll ( ) {
List< Topic> list = topicRepository. findAll ( ) ;
return list;
}
repository层
public List< Topic> findAll ( ) ;
mapper.xml
< insert id = " addTopicList" parameterType = " com.meritdata.cloud.entity.Topic" >
< selectKey keyProperty = " id" resultType = " String" order = " BEFORE" > SELECT left(UUID(),32)</ selectKey>
insert into sfy_topic(id,name,number,manager_id,model_id,system_ids,description,create_time,creator)
values
< foreach collection = " list" item = " topic" separator = " ," >
((SELECT REPLACE(UUID(), '-', '') AS id),#{topic.name},#{topic.number},#{topic.managerId},#{topic.modelId},#{topic.systemIds},#{topic.description},now(),#{topic.creator})
</ foreach>
</ insert>
PoiUtils工具类
package com. meritdata. cloud. comments;
import com. meritdata. cloud. entity. Topic;
import net. sf. jsqlparser. statement. select. Top;
import org. apache. poi. hpsf. DocumentSummaryInformation;
import org. apache. poi. hssf. usermodel. *;
import org. apache. poi. ss. usermodel. Cell;
import org. springframework. http. HttpHeaders;
import org. springframework. http. HttpStatus;
import org. springframework. http. MediaType;
import org. springframework. http. ResponseEntity;
import org. springframework. web. multipart. MultipartFile;
import java. io. ByteArrayOutputStream;
import java. io. IOException;
import java. util. ArrayList;
import java. util. List;
public class PoiUtils {
public static ResponseEntity< byte [ ] > exportJobLevelExcel ( List< Topic> topics) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook ( ) ;
workbook. createInformationProperties ( ) ;
DocumentSummaryInformation info = workbook. getDocumentSummaryInformation ( ) ;
info. setCompany ( "sfy." ) ;
info. setManager ( "hjg" ) ;
info. setCategory ( "课题表" ) ;
HSSFCellStyle datecellStyle = workbook. createCellStyle ( ) ;
datecellStyle. setDataFormat ( HSSFDataFormat. getBuiltinFormat ( "m/d/yy" ) ) ;
HSSFSheet sheet = workbook. createSheet ( "课题表" ) ;
HSSFRow r0 = sheet. createRow ( 0 ) ;
HSSFCell c0 = r0. createCell ( 0 ) ;
HSSFCell c1 = r0. createCell ( 1 ) ;
HSSFCell c2 = r0. createCell ( 2 ) ;
HSSFCell c3 = r0. createCell ( 3 ) ;
HSSFCell c4 = r0. createCell ( 4 ) ;
HSSFCell c5 = r0. createCell ( 5 ) ;
c0. setCellValue ( "课题代号" ) ;
c1. setCellValue ( "课题名称" ) ;
c2. setCellValue ( "课题主管" ) ;
c3. setCellValue ( "所属型号" ) ;
c4. setCellValue ( "所属系统" ) ;
c5. setCellValue ( "课题描述" ) ;
for ( int i = 0 ; i < topics. size ( ) ; i++ ) {
Topic topic= topics. get ( i) ;
HSSFRow row = sheet. createRow ( i + 1 ) ;
HSSFCell cell0 = row. createCell ( 0 ) ;
cell0. setCellValue ( topic. getNumber ( ) ) ;
HSSFCell cell1 = row. createCell ( 1 ) ;
cell1. setCellValue ( topic. getName ( ) ) ;
HSSFCell cell2 = row. createCell ( 2 ) ;
cell2. setCellValue ( topic. getManagerId ( ) ) ;
HSSFCell cell3 = row. createCell ( 3 ) ;
cell3. setCellValue ( topic. getModelId ( ) ) ;
HSSFCell cell4 = row. createCell ( 4 ) ;
cell4. setCellValue ( topic. getSystemIds ( ) ) ;
HSSFCell cell5 = row. createCell ( 5 ) ;
cell5. setCellValue ( topic. getDescription ( ) ) ;
}
HttpHeaders headers = new HttpHeaders ( ) ;
headers. setContentDispositionFormData ( "attachment" ,
new String ( "课题表.xls" . getBytes ( "UTF-8" ) , "iso-8859-1" ) ) ;
headers. setContentType ( MediaType. APPLICATION_OCTET_STREAM) ;
ByteArrayOutputStream baos= new ByteArrayOutputStream ( ) ;
workbook. write ( baos) ;
ResponseEntity< byte [ ] > entity = new ResponseEntity < > ( baos. toByteArray ( ) , headers, HttpStatus. CREATED) ;
return entity;
}
public static List< Topic> parseFile2List ( MultipartFile file) throws IOException {
List< Topic> topics = new ArrayList < > ( ) ;
HSSFWorkbook workbook = new HSSFWorkbook ( file. getInputStream ( ) ) ;
HSSFSheet sheet = workbook. getSheetAt ( 0 ) ;
int physicalNumberOfRows = sheet. getPhysicalNumberOfRows ( ) ;
for ( int i = 1 ; i < physicalNumberOfRows; i++ ) {
HSSFRow row = sheet. getRow ( i) ;
Topic topic= new Topic ( ) ;
HSSFCell c0 = row. getCell ( 0 ) ;
if ( row. getCell ( 0 ) != null) {
row. getCell ( 0 ) . setCellType ( Cell. CELL_TYPE_STRING) ;
topic. setNumber ( c0. getStringCellValue ( ) ) ;
}
HSSFCell c1 = row. getCell ( 1 ) ;
if ( row. getCell ( 1 ) != null) {
row. getCell ( 1 ) . setCellType ( Cell. CELL_TYPE_STRING) ;
topic. setName ( c1. getStringCellValue ( ) ) ;
}
HSSFCell c2 = row. getCell ( 2 ) ;
if ( row. getCell ( 2 ) != null) {
row. getCell ( 2 ) . setCellType ( Cell. CELL_TYPE_STRING) ;
topic. setManagerId ( c2. getStringCellValue ( ) ) ;
}
HSSFCell c3 = row. getCell ( 3 ) ;
if ( row. getCell ( 3 ) != null) {
row. getCell ( 3 ) . setCellType ( Cell. CELL_TYPE_STRING) ;
topic. setModelId ( c3. getStringCellValue ( ) ) ;
}
HSSFCell c4 = row. getCell ( 4 ) ;
if ( row. getCell ( 4 ) != null) {
row. getCell ( 4 ) . setCellType ( Cell. CELL_TYPE_STRING) ;
topic. setSystemIds ( c4. getStringCellValue ( ) ) ;
}
HSSFCell c5 = row. getCell ( 5 ) ;
if ( row. getCell ( 5 ) != null) {
row. getCell ( 5 ) . setCellType ( Cell. CELL_TYPE_STRING) ;
topic. setDescription ( c5. getStringCellValue ( ) ) ;
}
topics. add ( topic) ;
}
return topics;
}
}
(二)、前端代码
1.导入功能
<el-button
size="small"
type="primary"
@click="inputdialogVisible = true"
>导入课题</el-button
>
<!-- </el-upload> -->
<el-button type="primary" @click="exportTopic">导出课题</el-button>
<el-dialog
title="导入课题"
:visible.sync="inputdialogVisible"
width="30%"
:before-close="handleClose"
>
<el-button @click="download">下载模板</el-button>
<el-upload
action="http://127.0.0.1:8082/gateway/sfy/api/topic/upload"
:show-file-list="false"
:before-upload="beforeUpload"
:on-success="onSuccess"
:on-error="onError"
:disabled="importDisabled"
>
<el-button
size="small"
:disabled="importDisabled"
type="success"
:icon="importDataIcon"
>{{ importDataText }}
</el-button>
<!--<label>未选择任何文件</label> -->
</el-upload>
<span slot="footer" class="dialog-footer">
<el-button @click="inputdialogVisible = false">取 消</el-button>
<el-button type="primary" @click="upload = false"
>确 定</el-button
>
</span>
</el-dialog>
2.数据定义
export default {
data() {
return {
//导入按钮的文本
importDataText: "导入数据",
//导按钮的图标
importDataIcon: "el-icon-upload2",
//导入按钮是否被禁用
importDisabled: false,
inputdialogVisible: false,
}
}
}
3.方法实现
下载方法
method(){
//下载模板
download() {
var _this = this;
_this.axios["business-manage"]
.get(this.HOST + "/download")
// .get("http://127.0.0.1:8082/gateway/sfy/api/topic/download")
.then(function (res) {
if (!res) {
return;
}
var blob = new Blob([res], {
type: "application/vnd.ms-excel;charset=utf-8",
});
//创建下载地址以及a标签,并且模拟a标签的点击事件进行下载文件。
var url = window.URL.createObjectURL(blob);
var aLink = document.createElement("a");
aLink.style.display = "none";
aLink.download = "课题管理模板.xls";
aLink.href = url;
document.body.appendChild(aLink);
aLink.click();
})
.catch((error) => {
throw error;
});
},
}
上传文件(导入)方法
method(){
// 导入文件失败后回调
onError() {
this.importDataText = "导入数据";
this.importDataIcon = "el-icon-upload2";
this.importDisabled = false;
// this.initEmps();
this.$message.success("导入失败!");
},
// 导入文件成功后回调
onSuccess() {
// 成功后文本修改为原来的导入数据
// 图标修改
this.importDataIcon = "el-icon-upload2";
// 将上传组件改为允许使用
this.importDisabled = false;
// 调用刷新数据的方法
// this.initEmps();
// message 弹出消息
this.$message.success("导入成功!");
},
// 上传文件调用
beforeUpload() {
// 将文本修改为正在导入
this.importDataText = "正在导入";
// 修改其图标
this.importDataIcon = "el-icon-loading";
// 将其上传组件暂时禁用
this.importDisabled = true;
},
handleClose(done) {
this.$confirm("确认关闭?")
.then((_) => {
done();
})
.catch((_) => {});
},
}
二、导出数据到excel表格
(一)、后端实现
1.controller层 (PoiUtil工具类见上)
@GetMapping ( "/export" )
public ResponseEntity< byte [ ] > exportExcel ( ) throws IOException {
java. lang. System. out. println ( service. findAll ( ) ) ;
return PoiUtils. exportJobLevelExcel ( service. findAll ( ) ) ;
}
2.service层
public ResponseEntity< byte [ ] > exportExcel ( ) throws IOException;
public List< Topic> findAll ( ) ;
@Override
public ResponseEntity< byte [ ] > exportExcel ( ) throws IOException {
return PoiUtils. exportJobLevelExcel ( topicRepository. findAll ( ) ) ;
}
@Override
public List< Topic> findAll ( ) {
List< Topic> list = topicRepository. findAll ( ) ;
return list;
}
3.repository层
public List< Topic> findAll ( ) ;
4.mapper.xml
< select id = " findAll" resultType = " com.meritdata.cloud.entity.Topic" >
select t.name,t.number,u.name as managerId,a.name as modelId,s.name as systemIds,t.description
from sfy_topic t
inner join sfy_user u on t.manager_id = u.id
inner join sfy_aircraft_model a on a.id = t.model_id
inner join sfy_system s on t.system_ids = s.id
</ select>
(二)、前端代码
1.导出功能
<el-button type="primary" @click="exportTopic">导出课题</el-button>
2.方法实现
method(){
// 导出,通过blob
exportTopic() {
var _this = this;
_this.axios["business-manage"]
.get(this.HOST + "/export")
// .get("http://127.0.0.1:8082/gateway/sfy/api/topic/export")
.then(function (res) {
if (!res) {
return;
}
var blob = new Blob([res], {
type: "application/vnd.ms-excel;charset=utf-8",
});
//创建下载地址以及a标签,并且模拟a标签的点击事件进行下载文件。
var url = window.URL.createObjectURL(blob);
var aLink = document.createElement("a");
aLink.style.display = "none";
aLink.download = "课题表.xls";
aLink.href = url;
document.body.appendChild(aLink);
aLink.click();
})
.catch((error) => {
throw error;
});
},
}