简介
本周开发任务中有一个导入导出功能,导入只支持Excel文件类型导入到数据库的插入。导出只支持按照查询条件查询数据后导出为Excel文件。都是sql和增改查的基本操作。
1,功能分析
导出:
查询条件 :(uuid + fuzzyAlias + fuzzyTag + fuzzyDec + type) 联合查询->得到数据->后台把数据返给前台->前台处理数据->下载为Excel
导入:
导入是将一个项目在不同平台的数据导出,再在某个平台导入,这些平台的表里的UUID一般不会重复。所以将UUID 作为唯一条件来判断数据是否重复。这张表里,我将alias + tag 作为了联合唯一键,但是Alias 和 Tag 是会动态改变的,一旦改变了,判断重复就不准确了,判断重复时被比对项不能是一个可以改变的值,要以不变应万变。前台解析导入表中的数据->前台给后台传入数据->后台解析数据->用uuid作为查询条件->如果有重复项就报错->当前导入数据xx,xx,xx重复,请去除重复数据后再导入。
如果没有重复项->直接调用新增的sql;
2,组件拆分
3,代码介绍
导入:
jsp:
<input type="file" id="xz_importOperator_fileUploads" />
var fileUpload = $("#xz_importOperator_fileUploads")[0];
var reader = new FileReader();
if (reader.readAsBinaryString) {
reader.onload = function (e) {
ProcessExcel(e.target.result);
};
reader.readAsBinaryString(fileUpload.files[0]);
} else {
//For IE Browser.
reader.onload = function (e) {
var data = "";
var bytes = new Uint8Array(e.target.result);
for (var i = 0; i < bytes.byteLength; i++) {
data += String.fromCharCode(bytes[i]);
}
ProcessExcel(data);
};
reader.readAsArrayBuffer(fileUpload.files[0]);
function ProcessExcel(data) {
//Read the Excel File data.
var workbook = XLSX.read(data, {
type: 'binary'
});
//Fetch the name of First Sheet.
var firstSheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);
//Add the data rows from Excel file.
var rows = [];
for (var i = 0; i < excelRows.length; i++) {
var row = {
"uuid": excelRows[i]["*UUID"],
"alias": excelRows[i]["*算子名称"],
"description": excelRows[i]["描述"],
"opIsCustom": excelRows[i]["*是否自定义"],
"opLevelTag": excelRows[i]["*层级标签"],
"opLangType": excelRows[i]["*语言"],
"opInput": excelRows[i]["*输入数据"],
"opOutput": excelRows[i]["输出数据"],
"opParams": excelRows[i]["*参数"],
"opCodeInfo": excelRows[i]["*算子代码"],
"opEntry": excelRows[i]["*入口模块"],
"opFunc": excelRows[i]["*入口函数"]
};
rows.push(row);
}
xdsAjaxGet("xaj_importOperator").process(rows);
}
后台接口返回的数据处理:
var rows = response.getBlock("result").getMappedRows();
//设置excel表格标题
var excelHeader = initData();
//添加excel中数据
var row = [];
// var excelName = "";
//添加设置excel表格标题
row.push({
cells: excelHeader
});
//向row里面添加数据
for (var i = 0, len = rows.length; i < len; i++) {
row.push({
cells: [
{value: rows[i].uuid},
{value: rows[i].alias},
{value: rows[i].description},
{value: rows[i].opLevelTag},
{value: rows[i].opIsCustom},
{value: rows[i].opLangType},
{value: rows[i].opInput},
{value: rows[i].opOutput},
{value: '"'+rows[i].opParams.split('"').join('""')+'"'},
{value: rows[i].opCodeInfo},
{value: rows[i].opEntry},
{value: rows[i].opFunc}
]
})
}
downloadCsv(excelHeader, row, "算子管理");
下载为excel:
function downloadExcel(excelHeader, data, name) {
var columns = [];
//添加列头宽度属性,
for (var i = 0; i < excelHeader.length; i++) {
if (i === 0) {
columns.push({width: 80});
} else {
columns.push({width: 150});
}
}
// 生成workbook
var workbook = new kendo.ooxml.Workbook({
sheets: [
{
columns: columns, //列
rows: data
}
]
});
// 导出excel
kendo.saveAs({
dataURI: workbook.toDataURL(),
fileName: name
});
}
下载为csv
function downloadCsv(excelHeader, data, name) {
var str = "";
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].cells.length; j++) {
str += data[i].cells[j].value + ",";
}
str += "\n";
}
var nStr = "data:text/csv;charset=utf-8,\ufeff" + str;
var link = document.createElement("a");
link.setAttribute("href", nStr);
var date = new Date().getTime();
var filename = new Date(date).toLocaleDateString();
link.setAttribute("download", filename + ".csv");
link.click();
}
4,样式观赏
5,总结