VUE导入excel并在jqgrid中加载
一.概述
在vue中导入excel可以通过xlsx.full.min.js来实现，这种方式
优点：
- 只需要前台引入不需要走后台io，节省io流开支
二.使用
-
环境
- vue.js
- layUI（可选）
- bootstrap（可选）
- jqgrid
-
首先引入js
- 本地js：
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script> - cdn引入：下载
- 本地js：
-
excelTest.html
<!DOCTYPE html>
<html>
<head>
<title>excel导入测试</title>
</head>
<body>
<div id="rrapp" style="margin: 5px" v-cloak>
<div style="margin: 10px">
<div class="form-inline" style="margin-bottom: 10px">
<div class="form-group">
<form id="tf">
<!--用来导入execl文件的input-->
<input type="file" id="file" name="file"
accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
</form>
</div>
<div class="form-group">
<button type="button" class="btn btn-sm btn-primary "
style="margin-right: 20px" @click="query">解析文件
</button>
<button type="button" class="btn btn-sm btn-success "
style="margin-right: 20px" id="test9" @click="save">保存
</button>
<button type="button" class="btn btn-sm btn-info "
style="margin-right: 20px" @click="clear">清空
</button>
<!--excel模板下载-->
<a href="../sys/downloadTemplate">
<button type="button" class="btn btn-sm btn-success "
style="margin-right: 20px">模板下载
</button>
</a>
</div>
</div>
<br/>
<table id="jqGrid"></table>
<div id="jqGridPager"></div>
</div>
</div>
<!--样式文件-->
<link rel="stylesheet" href="${rc.contextPath}/statics/css/bootstrap.min.css">
<link rel="stylesheet" href="${rc.contextPath}/statics/plugins/jqgrid/ui.jqgrid-bootstrap.css">
<link rel="stylesheet" href="${rc.contextPath}/statics/plugins/layui/css/layui.css">
<script src="${rc.contextPath}/statics/js/jquery.min.js"></script>
<!--layui的js-->
<script src="${rc.contextPath}/statics/plugins/layer/layer.js"></script>
<script src="${rc.contextPath}/statics/plugins/layui/layui.js"></script>
<!--bootstrap的js-->
<script src="${rc.contextPath}/statics/js/bootstrap.min.js"></script>
<!--vue的js-->
<script src="${rc.contextPath}/statics/js/vue.min.js"></script>
<!--jqgrid的js-->
<script src="${rc.contextPath}/statics/plugins/jqgrid/grid.locale-cn.js"></script>
<script src="${rc.contextPath}/statics/plugins/jqgrid/jquery.jqGrid.min.js"></script>
<!--xlsx的js文件，导入文件用-->
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script>
<!--html对应的js文件-->
<script src="${rc.contextPath}/statics/js/sys/excelTest.js?_${date.systemTime}"></script>
</body>
</html>
- excelTest.js文件
- 解析文件
- 用xlsx的
XLSX.read(data, {type: 'binary'});方法将excel读取为二进制文件 - 将读取后的文件解析成json导入到excel中显示
- 用xlsx的
- 保存数据
- 获取数据，数据处理后，传到后台
- 解析文件
var vm = new Vue({
el: '#rrapp',
data: {
par: {
// 读取完成的数据
xls: null,
// JSON数据
jsonData: ''
}
},
methods: {
//解析文件
query: function () {
if (checkFile()) {
importFile(document.getElementById("file"));
}
},
//保存
save: function () {
//判断解析后的文件是否为null
if (!(vm.par.jsonData === '' || vm.par.jsonData == null)) {
//定义向后台传递数据的json数组
var objArr =[];
//循环json数据，将数据的字段改为正常英文格式，设置到数组中
jQuery(vm.par.jsonData).each(function(){
var objToBack = {
createPerson:'',
createTime:'',
tenantId:'',
leaveFlag:1,
delFlag:0,
arriveFlag:0,
remarks:'',
fcargoLocationId:'',
cargoLocationName:'',
orderNo:'',
movementFlag:0,
isUsed:0,
modelType:'',
seq:0
};
var cargoLocationName=this.货位名称==null?'':this.货位名称;
var orderNo=this.班次==null?'':this.班次;
var cargoType=this.货位类型==null?'':this.货位类型;
var modelType=this.车船型号==null?'':this.车船型号;
var movementFlag=this.动静标示==null?'':this.动静标示;
var capacity=this.容量==null?'':this.容量;
var boxingNumber=this.装箱量==null?'':this.装箱量;
var remarks=this.备注==null?'':this.备注;
objToBack.cargoLocationName=cargoLocationName
objToBack.orderNo=orderNo
objToBack.cargoType=cargoType
objToBack.movementFlag=movementFlag
objToBack.capacity=capacity
objToBack.boxingNumber=boxingNumber
objToBack.remarks=remarks
objToBack.modelType=modelType
objArr.push(objToBack);
});
//后台保存的ajax
$.ajax({
type: "POST",
url: "../tbccargolocationinfo/saveCargoLoactionAndAttrInfoExt",
data: JSON.stringify(objArr),
success: function(data){
toast(data.msg);
}
});
} else {
// 请导入并且解析文件
toast("请导入并且解析文件");
}
},
//清空
clear: function () {
$("#file").val(null);
vm.par.jsonData = '';
vm.reload();
},
reload: function () {
$("#jqGrid").jqGrid('setGridParam', {
datatype: "jsonstring",
datastr: vm.par.jsonData
}).trigger("reloadGrid");
}
}
});
// 导入文件
function importFile(obj) {
if (!obj.files) {
return;
}
var f = obj.files[0];
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
//将excel读取为二进制文件
vm.par.xls = XLSX.read(data, {type: 'binary'});
//将excel中第一张表的数据导出为json
vm.par.jsonData = XLSX.utils.sheet_to_json(vm.par.xls.Sheets[vm.par.xls.SheetNames[0]]);
//将数据放入jqgrid进行展示
showDataTable(vm.par.jsonData);
layer.close(loadIndex);
};
reader.readAsBinaryString(f);
}
// 页面展示数据
function showDataTable(parData) {
var data = {
"page": "1",
"records": parData.length,
"rows": parData
};
$("#jqGrid").jqGrid({
colModel: [
{
label: '货位名称', name: '货位名称', width: 150, sortable: false, formatter: function (value) {
return value == null ? '<span class="label label-danger">无效数据</span>' : value
}
},
{
label: '班次', name: '班次', width: 150, sortable: false
},
{
label: '货位类型', name: '货位类型', width: 150, sortable: false, formatter: function (value) {
return value == null ? '<span class="label label-danger">无效数据</span>' : value
}
},
{
label: '车船型号', name: '车船型号', width: 150, sortable: false
},
{
label: '动静标示', name: '动静标示', width: 150, sortable: false, formatter: function (value) {
return value == null ? '<span class="label label-danger">无效数据</span>' : value
}
},
{
label: '容量', name: '容量', width: 75, sortable: false
},
{
label: '装箱量', name: '装箱量', width: 75, sortable: false
},
{
label: '备注', name: '备注', width: 75, sortable: false
}
],
pager: '#jqGridPager',
datatype: "jsonstring",
datastr: data,
jsonReader: {repeatitems: false},
rowNum: parData.length,
viewrecords: true,
autowidth: true,
height: "auto",
gridComplete: function () {
$("#jqGrid").closest(".ui-jqgrid-bdiv").css({"overflow-x": "show"});
}
}).jqGrid('setGridParam', {
datatype: "jsonstring",
datastr: data
}).trigger("reloadGrid");
}
// 检验文件
function checkFile() {
var allowed_extensions = new Array("xls", "xlsx");
var file_extension = document.getElementById("file").value.split('.').pop();
for (var i = 0; i <= allowed_extensions.length; i++) {
if (allowed_extensions[i] == file_extension) {
return true;
}
}
alert("不是 xls/xlsx 文件！");
vm.clear();
return false;
}
- pom.xml中导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15-beta2</version>
</dependency>
- 后台controller
/**
* 保存部分
*
* @param sysInfoexts
* @return
*/
@RequestMapping("/saveCargoLoactionAndAttrInfoExt")
public Result saveCargoLoactionAndAttrInfoExt(@RequestBody SysInfoExt[] sysInfoexts){
Result result = Result.ok();
try {
for (SysInfoExt sysInfoext:sysInfoexts){
sysInfoService.save(sysInfoext);
}
} catch (Exception e) {
result=Result.error();
e.printStackTrace();
}
return result;
}
/**
* 模板下载
*
* @param response
* @return
*/
@RequestMapping("/sys/downloadTemplate")
public Result downloadTemplate(HttpServletResponse response) {
int num = sysInfoService.downloadTemplate(response);
if (num == 0) {
return Result.ok();
} else {
return Result.error();
}
}
- 后台service
public int downloadTemplate(HttpServletResponse response) {
// 创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
// 建立新的sheet对象（excel的表单）
HSSFSheet sheet = wb.createSheet("货位数据模板");
HSSFRow row1 = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
HSSFFont redFont = wb.createFont();
//颜色
redFont.setColor(Font.COLOR_RED);
style.setFont(redFont);
row1.createCell(3).setCellStyle(style);
row1.createCell(4).setCellStyle(style);
row1.createCell(5).setCellStyle(style);
row1.createCell(6).setCellStyle(style);
row1.createCell(7).setCellStyle(style);
// 创建单元格并设置单元格内容
row1.createCell(0).setCellValue("货位名称");
row1.createCell(1).setCellValue("班次");
row1.createCell(2).setCellValue("货位类型");
row1.createCell(3).setCellValue("车船型号");
row1.createCell(4).setCellValue("动静标示");
row1.createCell(5).setCellValue("容量");
row1.createCell(6).setCellValue("装箱量");
row1.createCell(7).setCellValue("备注");
HSSFRow row2 = sheet.createRow(1);
row2.createCell(0).setCellValue("阿尔乡");
row2.createCell(1).setCellValue("001");
row2.createCell(3).setCellValue("CL001");
row2.createCell(5).setCellValue("0");
row2.createCell(6).setCellValue("0");
row2.createCell(7).setCellValue("");
//货位类型
//货位类型 6船舶 7火车 8汽车 9集装箱
List<String> listCargo = new ArrayList<String>();
listCargo.add("船舶");
listCargo.add("火车");
listCargo.add("汽车");
listCargo.add("集装箱");
String[] cargoTypeStr = new String[listCargo.size()];
for (int i = 0; i < listCargo.size(); i++) {
cargoTypeStr[i] = listCargo.get(i);
}
if (listCargo.size() > 0) {
row2.createCell(2).setCellValue(cargoTypeStr[0]);
}
//动静态标示
List<String> listMovement = new ArrayList<String>();
listMovement.add("动态");
listMovement.add("静态");
String[] movementStr = new String[listMovement.size()];
for (int i = 0; i < listMovement.size(); i++) {
movementStr[i] = listMovement.get(i);
}
if (listMovement.size() > 0) {
row2.createCell(4).setCellValue(movementStr[0]);
}
// 设置第一列的1-1000行为下拉列表
CellRangeAddressList cargoType = new CellRangeAddressList(1, 1001, 2, 2);
CellRangeAddressList movementType = new CellRangeAddressList(1, 1001, 4, 4);
// 创建下拉列表数据
DVConstraint constraintCargoType = DVConstraint.createExplicitListConstraint(cargoTypeStr);
DVConstraint constraintMovementType = DVConstraint.createExplicitListConstraint(movementStr);
// 绑定
HSSFDataValidation dataValidationCargo = new HSSFDataValidation(cargoType, constraintCargoType);
HSSFDataValidation dataValidationMovement = new HSSFDataValidation(movementType, constraintMovementType);
sheet.addValidationData(dataValidationCargo);
sheet.addValidationData(dataValidationMovement);
// 设置宽度自适应
for (int i = 0; i < 9; i++) {
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 25 / 10);
}
// 输出Excel文件
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
try {
response.setHeader("Content-disposition",
"attachment;filename=\"" + new String("导入模板".getBytes("gb2312"), "ISO8859-1") + ".xls" + "\"");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
return 1;
}
OutputStream outputStream;
try {
outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
return 1;
}
return 0;
}
扫一扫
4241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
