帆软:使用JS解决多扩展列值转json并使用base64加密存取
填报报表
一、值转换写入数据库
1.需求
将C列到L列的填报信息已经评价人ID和填报日期等转换以键值对的形式转换成JSON格式例如{“评价人ID”:“小只因”,“建议”:“练习两年半”},再以base64加密存入数据表中
2.解决思路
a.编写js,使用帆软提供的函数获取单元格值,在一行中遍历获取每一列值
b.将拼接好的字符串使用base64加密,并写入帆软报表中的一个单元格内
c.设置报表填报属性,将b中写入的单元格绑定内置sql提交中对应表中字段
3.详细过程
先设计出基本报表
(由于公司特殊要求D6列需要动态扩展,不过逻辑是一样的别纠结哈哈哈)
由于用户提交这个动作的时候会自动触发校验(这里选择提交校验后也是可以的)
编写js代码
//校验后++
//tbdate填报时间
//gradeItemIDLen D5扩展出来的列个数
//avgValLen 用户提交时填报的单元格行数
for (var j = 0; j < avgValLen; j++) {
//用于存储拼接后的json值
var value = "{";
var avg=0.0;
for (i = 0; i < gradeItemIDLen; i++) {
var gradeItemID = _g().getCellValue(0, i + 3, 4);
//获取D5的扩展值,每个评分项的ID
var grade = _g().getCellValue(0, i + 3, j + 5);
//获取D6的扩展值,每个评分项对应的分数
avg+=parseInt(grade);
value+="\""+gradeItemID+"\":\""+grade+"\",";
}
avg=(avg*1.0)/gradeItemIDLen;
var ac=_g().setCellValue(0,gradeItemIDLen + 3,j+5,avg);
var A91=_g().getCellValue(0,gradeItemIDLen + 4,j+5);
var A92=_g().getCellValue(0,gradeItemIDLen + 5,j+5);
var ZPJRID=_g().getCellValue(0,1,5+j);
value+='"A80":"'+avg.toFixed(2)+'","A91":"'+A91+'","A92":"'+ A92 +'","ZPJRID":"'+ZPJRID+'","tbdate":"'+tbdate+'"}';
//处理字符串中的\r,\n值
value=value.replace("/\r/g","\\r").replace("/\n/g","\\n");
//调用base64加密函数
var base = new Base64();
var result = base.encode(value);
//将转换好的值填入A列的特定行,从第6行即A6处开始填入
_g().setCellValue(0, 0, j + 5,result);
}
其中avgValLen为A1单元格的值,A1单元格内填入公式count(B6)
(这里不能直接在参数avgValLen中绑定count(B6)公式,此处有大坑)
最后一步填报提交
将js最后填入的A6单元格绑定到此处
注意事项(有大坑):
A6单元格需要设置为可以纵向扩展
A6单元格和填报的其他单元格的左父格关系要搞清楚,即用户新增一行记录时的会出现出现类似于下面的状况,此处会导致js赋值报错
二、将上述存入数据表中的数据拆解展示在报表中
1.需求
将数据表中base64加密的json数据取出分别展示在报表的对应列处
2.解决思路
a.将数据表中该字段值,在单元格元素中以数据列填入
b.编写js获取上述单元格值->解密,解析json,将对应值填入帆软报表的对应列
3.详细过程
设置数据列,并将H6设置为可向下扩展
编写js对H6中值进行操作
这里要选择加载结束(如果选择加载起始是会报错的,因为加载起始的时候H6中还没有值呢)
//加载结束
//rowsize //历史填报数据行数
//gradeItemIDLen D5单元格扩展出来的列数
try{
for (var i = 0; i < rowsize; i++) {
var jsontxt = _g().getCellValue(0, gradeItemIDLen + 6,i + 5);
if(jsontxt==null)break;
var base = new Base64();
//解密
var result = base.decode(jsontxt);
var obj = JSON.parse(result);
var colnum=3;
for (var k in obj) {
if (k=="ZPJRID") {
//var kccc = _g().setCellValue(0, colnum+2,i + 5 ,obj[k]);
break;
}
var kc = _g().setCellValue(0, colnum,i + 5 ,obj);
colnum+=1;
// if(colnum>=(gradeItemIDLen+5))break;
}
var kkkc = _g().setCellValue(0, colnum+2,i + 5 ,obj[k]);
}
}catch(err) {
console.log("错误信息:" + err);
}
到这流程就结束啦,下面附上校验后和加载结束的完整js代码
如果需要优化性能:拼接字符串的时候可以使用类StringBuffer
校验后
//校验后++
//tbdate填报时间
for (var j = 0; j < avgValLen; j++) {
var value = "{";
var avg=0.0;
for (i = 0; i < gradeItemIDLen; i++) {
var gradeItemID = _g().getCellValue(0, i + 3, 4);
//获取D5的扩展值,每个评分项的ID
var grade = _g().getCellValue(0, i + 3, j + 5);
//获取D6的扩展值,每个评分项对应的分数
avg+=parseInt(grade);
value+="\""+gradeItemID+"\":\""+grade+"\",";
}
avg=(avg*1.0)/gradeItemIDLen;
var ac=_g().setCellValue(0,gradeItemIDLen + 3,j+5,avg);
var A91=_g().getCellValue(0,gradeItemIDLen + 4,j+5);
var A92=_g().getCellValue(0,gradeItemIDLen + 5,j+5);
var ZPJRID=_g().getCellValue(0,1,5+j);
value+='"A80":"'+avg.toFixed(2)+'","A91":"'+A91+'","A92":"'+ A92 +'","ZPJRID":"'+ZPJRID+'","tbdate":"'+tbdate+'"}';
var base = new Base64();
var result = base.encode(JSON.stringify(value));
_g().setCellValue(0, 0, j + 5,result);
}
function Base64() {
// private property
_keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
// public method for encoding
this.encode = function (input) {
var output = "";
var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
var i = 0;
input = _utf8_encode(input);
while (i < input.length) {
chr1 = input.charCodeAt(i++);
chr2 = input.charCodeAt(i++);
chr3 = input.charCodeAt(i++);
enc1 = chr1 >> 2;
enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
enc4 = chr3 & 63;
if (isNaN(chr2)) {
enc3 = enc4 = 64;
} else if (isNaN(chr3)) {
enc4 = 64;
}
output = output +
_keyStr.charAt(enc1) + _keyStr.charAt(enc2) +
_keyStr.charAt(enc3) + _keyStr.charAt(enc4);
}
return output;
}
// public method for decoding
this.decode = function (input) {
var output = "";
var chr1, chr2, chr3;
var enc1, enc2, enc3, enc4;
var i = 0;
input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");
while (i < input.length) {
enc1 = _keyStr.indexOf(input.charAt(i++));
enc2 = _keyStr.indexOf(input.charAt(i++));
enc3 = _keyStr.indexOf(input.charAt(i++));
enc4 = _keyStr.indexOf(input.charAt(i++));
chr1 = (enc1 << 2) | (enc2 >> 4);
chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);
chr3 = ((enc3 & 3) << 6) | enc4;
output = output + String.fromCharCode(chr1);
if (enc3 != 64) {
output = output + String.fromCharCode(chr2);
}
if (enc4 != 64) {
output = output + String.fromCharCode(chr3);
}
}
output = _utf8_decode(output);
return output;
}
// private method for UTF-8 encoding
_utf8_encode = function (string) {
string = string.replace(/\r\n/g,"\n");
var utftext = "";
for (var n = 0; n < string.length; n++) {
var c = string.charCodeAt(n);
if (c < 128) {
utftext += String.fromCharCode(c);
} else if((c > 127) && (c < 2048)) {
utftext += String.fromCharCode((c >> 6) | 192);
utftext += String.fromCharCode((c & 63) | 128);
} else {
utftext += String.fromCharCode((c >> 12) | 224);
utftext += String.fromCharCode(((c >> 6) & 63) | 128);
utftext += String.fromCharCode((c & 63) | 128);
}
}
return utftext;
}
// private method for UTF-8 decoding
_utf8_decode = function (utftext) {
var string = "";
var i = 0;
var c = c1 = c2 = 0;
while ( i < utftext.length ) {
c = utftext.charCodeAt(i);
if (c < 128) {
string += String.fromCharCode(c);
i++;
} else if((c > 191) && (c < 224)) {
c2 = utftext.charCodeAt(i+1);
string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
i += 2;
} else {
c2 = utftext.charCodeAt(i+1);
c3 = utftext.charCodeAt(i+2);
string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
i += 3;
}
}
return string;
}
}
加载结束
//加载结束
//rowsize //历史填报数据行数
//gradeItemIDLen
try{
for (var i = 0; i < rowsize; i++) {
var jsontxt = _g().getCellValue(0, gradeItemIDLen + 6,i + 5);
if(jsontxt==null)break;
var base = new Base64();
var result2 = base.decode(jsontxt);
// var json = JSON.stringify(result2);
// var obj1 = JSON.parse(result2);
var obj = JSON.parse(result2);
var colnum=3;
for (var k in obj) {
if (k=="ZPJRID") {
//var kccc = _g().setCellValue(0, colnum+2,i + 5 ,obj[k]);
break;
}
var kc = _g().setCellValue(0, colnum,i + 5 ,obj);
colnum+=1;
// if(colnum>=(gradeItemIDLen+5))break;
}
var kkkc = _g().setCellValue(0, colnum+2,i + 5 ,obj[k]);
}
}catch(err) {
console.log("错误信息:" + err);
}
function Base64() {
// private property
_keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
// public method for encoding
this.encode = function (input) {
var output = "";
var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
var i = 0;
input = _utf8_encode(input);
while (i < input.length) {
chr1 = input.charCodeAt(i++);
chr2 = input.charCodeAt(i++);
chr3 = input.charCodeAt(i++);
enc1 = chr1 >> 2;
enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
enc4 = chr3 & 63;
if (isNaN(chr2)) {
enc3 = enc4 = 64;
} else if (isNaN(chr3)) {
enc4 = 64;
}
output = output +
_keyStr.charAt(enc1) + _keyStr.charAt(enc2) +
_keyStr.charAt(enc3) + _keyStr.charAt(enc4);
}
return output;
}
// public method for decoding
this.decode = function (input) {
var output = "";
var chr1, chr2, chr3;
var enc1, enc2, enc3, enc4;
var i = 0;
input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");
while (i < input.length) {
enc1 = _keyStr.indexOf(input.charAt(i++));
enc2 = _keyStr.indexOf(input.charAt(i++));
enc3 = _keyStr.indexOf(input.charAt(i++));
enc4 = _keyStr.indexOf(input.charAt(i++));
chr1 = (enc1 << 2) | (enc2 >> 4);
chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);
chr3 = ((enc3 & 3) << 6) | enc4;
output = output + String.fromCharCode(chr1);
if (enc3 != 64) {
output = output + String.fromCharCode(chr2);
}
if (enc4 != 64) {
output = output + String.fromCharCode(chr3);
}
}
output = _utf8_decode(output);
return output;
}
// private method for UTF-8 encoding
_utf8_encode = function (string) {
string = string.replace(/\r\n/g,"\n");
var utftext = "";
for (var n = 0; n < string.length; n++) {
var c = string.charCodeAt(n);
if (c < 128) {
utftext += String.fromCharCode(c);
} else if((c > 127) && (c < 2048)) {
utftext += String.fromCharCode((c >> 6) | 192);
utftext += String.fromCharCode((c & 63) | 128);
} else {
utftext += String.fromCharCode((c >> 12) | 224);
utftext += String.fromCharCode(((c >> 6) & 63) | 128);
utftext += String.fromCharCode((c & 63) | 128);
}
}
return utftext;
}
// private method for UTF-8 decoding
_utf8_decode = function (utftext) {
var string = "";
var i = 0;
var c = c1 = c2 = 0;
while ( i < utftext.length ) {
c = utftext.charCodeAt(i);
if (c < 128) {
string += String.fromCharCode(c);
i++;
} else if((c > 191) && (c < 224)) {
c2 = utftext.charCodeAt(i+1);
string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
i += 2;
} else {
c2 = utftext.charCodeAt(i+1);
c3 = utftext.charCodeAt(i+2);
string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
i += 3;
}
}
return string;
}
}
最后要感谢一位大佬,上述的加密解密base函数就是参考的这位大佬的
这里附上参考的文章链接js Base64加密解密