第一次接触Excel导出功能,写的有点费劲,把代码贴出来希望能帮到大家
后端:springboot+springcloud+mybatis
前端:vue+iview
数据库:oracle
先上vue
<Upload
ref="upload"
action="此处填写你访问的后台接口地址"
:on-format-error="handleFormatError"
:on-success="handleSuccess"
:on-error="handleError"
enctype="multipart/form-data"
:format ="['xlsx','xls']">
<Button type="primary" icon="ios-cloud-upload-outline">批量导入</Button>
</Upload>
uploadLoading: false,
file: null,
//导入
handleFormatError(file){
this.$Notice.warning({
title: '文件格式不正确',
desc: '文件 ' + file.name + ' 格式不正确,请上传.xls,.xlsx文件。'
})
},
handleSuccess(res,file){
this.$Message.success("数据导入成功!")
//提示完渲染页面
this.getTreeData(1)
},
handleError(error,file){
this.$Message.error("数据导入失败!")
},
接下来我们写后台
导入jar包(不知道是否都能用到,引就vans了,版本可能对代码有影响)
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.9</version>
</dependency>
controller
/**
* 导入Excel
* @param file
* @throws Exception
*/
@ApiOperation(value="Excel导入", notes="上传Excel表格插入数据库")
@RequestMapping(value = "/upload",method = RequestMethod.POST)
public void upload(@RequestParam("file") MultipartFile file) throws Exception {
stationService.batchImport(file);
}
service
/**
* 导入Excel
* @param file
* @throws Exception
*/
void batchImport(MultipartFile file) throws Exception;
serviceimpl
/**
* 导入Excel
* @param file
* @throws Exception
*/
@Transactional(readOnly = false,rollbackFor = Exception.class)
@Override
public void batchImport(MultipartFile file)throws Exception {
List<Station> ss = new ArrayList<>();
InputStream is = file.getInputStream();
Workbook workbook = WorkbookFactory.create(is);
//确定版本
boolean isExcel2003 = file.getOriginalFilename().endsWith("xls")?true:false;
List<Map<String, Object>> returnMap;
if(isExcel2003) {
//有多少个sheet
int sheets = workbook.getNumberOfSheets();
for (int i = 0; i < sheets; i++) {
HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(i);
//获取多少行
int rows = sheet.getPhysicalNumberOfRows();
Station s = null;
//遍历每一行,注意:第 0 行为标题
for (int j = 1; j < rows; j++) {
s = new Station();
//获得第 j 行
HSSFRow row = sheet.getRow(j);
//转换格式 row.getCell(0).setCellType(Cell.CELL_TYPE_STRING)
s.setStationName(row.getCell(0).getStringCellValue());
s.setContacts(row.getCell(1).getStringCellValue());
s.setAddress(row.getCell(2).getStringCellValue());
s.setIntoUseTime(row.getCell(3).getStringCellValue());
ss.add(s);
if(StringUtils.isBlank(s.getStationId())){
s.setStationId(UUIDGenerate.getUUID());
}
Map<String, Object> returnMap1=new HashMap<>();
returnMap1.put("station",s);
stationDao.addStationExcel(s);
}
}
}else{
//有多少个sheet
int sheets = workbook.getNumberOfSheets();
for (int i = 0; i < sheets; i++) {
XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(i);
//获取多少行
int rows = sheet.getPhysicalNumberOfRows();
Station s = null;
//遍历每一行,注意:第 0 行为标题
for (int j = 1; j < rows; j++) {
s = new Station();
//获得第 j 行
XSSFRow row = sheet.getRow(j);
//设置参数与Excel列对应
s.setStationName(row.getCell(0).getStringCellValue());
s.setContacts(row.getCell(1).getStringCellValue());
s.setAddress(row.getCell(2).getStringCellValue());
s.setIntoUseTime(row.getCell(3).getStringCellValue());
ss.add(s);
//设置id为随机数
if(StringUtils.isBlank(s.getStationId())){
s.setStationId(UUIDGenerate.getUUID());
}
stationDao.addStationExcel(s);
}
}
}
}
dao
/**
* Excel导入
* @param s
*/
void addStationExcel(Station s);
**.xml
<!-- excel导入 -->
<insert id="addStationExcel" parameterType="实体类" >
insert into 表名
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="stationId != null and stationId != ''">
station_id,
</if>
<if test="stationName != null and stationName != ''">
station_name,
</if>
<if test="contacts != null and contacts != ''">
contacts,
</if>
<if test="intoUseTime != null and intoUseTime != ''">
into_use_time,
</if>
<if test="address != null and address != ''">
address,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="stationId != null and stationId != ''">
#{stationId,jdbcType=VARCHAR},
</if>
<if test="stationName != null and stationName != ''">
#{stationName,jdbcType=VARCHAR},
</if>
<if test="contacts != null and contacts != ''">
#{contacts,jdbcType=VARCHAR},
</if>
<if test="intoUseTime != null and intoUseTime != ''">
to_date(#{intoUseTime,jdbcType=DATE},'yyyy-MM-dd'),
</if>
<if test="address != null and address != ''">
#{address,jdbcType=VARCHAR},
</if>
</trim>
</insert>