参考：
https://blog.csdn.net/qq_39291028/article/details/95065231
https://blog.csdn.net/w893932747/article/details/89354979
目标
- 导入
- 导出
特殊说明
本实例为了增强通用性和可移植性，采用的是前端上传文件后端springboot处理的方式
后端下载相关依赖
<!-- excel支持 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
导入
excel数据导入主要涉及三个步骤:
1.文件上传
2.excel解析
3.数据插入
前台
springboot-vue工程下的mulupload.vue
<template>
<el-row>
<el-col :span="8" :offset="8">
<div id="upload">
<label class="el-form-item__label" style="width: 80px;">上传图片</label>
<!--elementui的上传图片的upload组件-->
<el-upload
class="upload-demo"
:before-upload="beforeupload"
drag
action="http://localhost:8000/haha/multiUpload4Param"
style="margin-left:80px;"
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
<!--<div class="el-upload__tip" slot="tip">只能上传jpg/png文件，且不超过500kb</div>-->
</el-upload>
<!--elementui的form组件-->
<el-form ref="form" :model="form" label-width="80px">
<el-form-item label="活动名称">
<el-input v-model="form.name" name="names" style="width:360px;"></el-input>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="onSubmit">立即创建</el-button>
<el-button>取消</el-button>
</el-form-item>
</el-form>
</div>
</el-col>
<!--展示选中图片的区域-->
<el-col :span="4" >
<div style="width:100%;overflow: hidden;margin-left:150px;">
<img :src="src" alt="" style="width:100%;"/>
</div>
</el-col>
</el-row>
</template>
<script>
export default {
data() {
return {
form: {//form里面的参数
name: ''
},
param:"",//表单要提交的参数
src:"https://afp.alicdn.com/afp-creative/creative/u124884735/14945f2171400c10764ab8f3468470e4.jpg" //展示图片的地址
};
},
methods: {
beforeRemove(file, fileList) {
//return this.$confirm(`确定移除 ${ file.name }？`);
},
//阻止upload的自己上传，进行再操作
beforeupload(file) {
console.log(file);
//创建临时的路径来展示图片
var windowURL = window.URL || window.webkitURL;
this.src=windowURL.createObjectURL(file);
//重新写一个表单上传的方法
this.param = new FormData();
this.param.append('file', file, file.name);
return false;
},
//覆盖默认的上传行为
httprequest() {
},
onSubmit(){//表单提交的事件
var names = this.form.name;
//下面append的东西就会到form表单数据的fields中；
this.param.append('name', names);
let config = {
headers: {
'Content-Type': 'multipart/form-data'
}
};
//然后通过下面的方式把内容通过axios来传到后台
//下面的this.$reqs 是在主js中通过Vue.prototype.$reqs = axios 来把axios赋给它;
/* this.$axios.post("http://localhost:8000/haha/multiUpload4Param", this.param, config).then(function(result) {
console.log(result);
}) */
this.$axios.post("http://localhost:8000/haha/multiUpload4ParamExcel", this.param, config).then(function(result) {
alert(result.data);
})
}
}
}
</script>
<style>
</style>
后台
demo-his01工程下的UploadController
@PostMapping("/multiUpload4ParamExcel")
@ResponseBody
public String multiUpload4ParamExcel(HttpServletRequest request,String name,String age,MultipartFile[] file,String c_id) throws Exception {
//System.out.println(name);
//System.out.println(age);
List<MultipartFile> files = ((MultipartHttpServletRequest) request).getFiles("file");
for (int i = 0; i < files.size(); i++) {
MultipartFile fileitem = files.get(i);
if (fileitem.isEmpty()) {
return "上传第" + (i++) + "个文件失败";
}
InputStream inputStream = fileitem.getInputStream();
List<List<Object>> list = importService.getBankListByExcel(inputStream, fileitem.getOriginalFilename());
inputStream.close();
for (int j = 0; j < list.size(); j++) {
List<Object> lo = list.get(j);
//随意发挥,可以拼装对象并入库
System.out.println(lo);
}
}
return "上传成功";
}
service
package com.neuedu.demo.service.impl;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
@Service
public class ImportService {
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public List getBankListByExcel(InputStream in, String fileName) throws Exception {
List list = new ArrayList<>();
//创建Excel工作薄 根据后缀不同，创建不同的工作薄对象
Workbook work = this.getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;//sheet页 工作表
Row row = null;//行
Cell cell = null;//列
for (int i = 0; i < work.getNumberOfSheets(); i++) {//逐个读取工作表
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {//读取行
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {//路过第一行表头
continue;
}
List<Object> li = new ArrayList<>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(cell);
}
list.add(li);
}
}
work.close();
return list;
}
/**
* 判断文件格式
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(inStr);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(inStr);
} else {
throw new Exception("请上传excel文件！");
}
return workbook;
}
}
存成map
@PostMapping("excel/import")
public String excelImport(MultipartFile[] file, String name, String age) throws Exception {
String result = "";
// 打印文件上传的原始文件名
System.out.println(name);
for (MultipartFile fileitem : file) {
// 获取原始文件名称
String fileName = fileitem.getOriginalFilename();
// 第一步构建 工作簿
// 定义一个空的工作簿
Workbook workbook = null;
// 截取文件后缀
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {// 如果后缀为.xls 2007版之前的
// HSSFWorkbook接受的是输入流参数
workbook = new HSSFWorkbook(fileitem.getInputStream());
} else if (".xlsx".equals(fileType)) {// 2007版本以后的
workbook = new XSSFWorkbook(fileitem.getInputStream());
} else {
throw new Exception("请上传excel文件！");
}
// 第二步 读取工作表对象(假设有多个)
Sheet sheet = null;
Row row = null;
Cell cell = null;
//List<Object> list = new ArrayList<>();
List<Map<String,Object>> list=new ArrayList();
int sheet_size = workbook.getNumberOfSheets();// 方法可以获取该工作簿里的工作表个数
if (sheet_size > 0) {// 如果工作表的个数大于0
for (int j = 0; j <sheet_size; j++) {
// 用for循环逐个读取工作表
// workbook.getSheet("工作表名");//用工作表名来读取
sheet = workbook.getSheetAt(j);
// 第三步 读取行:需要知道有多少行
for (int k = sheet.getFirstRowNum(); k <= sheet.getLastRowNum(); k++) {
if(k<=1){
continue;
}
row = sheet.getRow(k);
Map<String,Object> map=new HashMap();
for (int i = row.getFirstCellNum(); i <=row.getLastCellNum(); i++) {
cell = row.getCell(i);
// 第四步，读取列：需要知道有多少列
map.put(""+i, cell);
}
list.add(map);
}
}
}
System.out.println(list);
for(Map<String,Object> mapitem:list){
System.out.println(mapitem.get("1"));
System.out.println("----------------->");
//封装对象，入库
}
}
return result;
}
测试
image.png
后台打印
[1.0, wang.qj, 123456.0]
[2.0, zhangs, 123456.0]
image.png
导出-简单实例
@GetMapping("exportexcel")
public void exportPermMatrix(HttpServletRequest request, HttpServletResponse response) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("员工信息表");
//此处添加数据
HSSFRow headerRow1 = sheet.createRow(0);
headerRow1.createCell(0).setCellValue("编号");
headerRow1.createCell(1).setCellValue("内容");
HSSFRow headerRow2 = sheet.createRow(1);
headerRow2.createCell(0).setCellValue("01");
headerRow2.createCell(1).setCellValue("测试文本");
//清空response
response.reset();
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition",
"attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xls");
OutputStream os = new BufferedOutputStream(response.getOutputStream());
workbook.write(os);
os.flush();
os.close();
workbook.close();
}
作业
学生完成his系统常数项表的导入和导出
网友评论