Kettle_js脚本_示例

2023-11-01

Kettle 版本7.0

一，String function

1 .date2str

//js操作时间

var dValue = new Date();

//把时间变成字符串

var dValue1 = date2str(dValue);

//把时间变成当前格式字符串

var dValue2 = date2str(dValue,"dd.MM.yyyy");

//把时间变成当前格式字符串

var dValue3 = date2str(dValue,"dd.MM.yyyy HH:mm:ss");

//把时间变成当前格式字符串 + 语言

var dValue4 = date2str(dValue,"E.MMM.yyyy","DE");

//把时间变成当前格式字符串 + 语言

var dValue5 = date2str(dValue,"dd.MM.yyyy HH:mm:ss","EN");

//把时间变成当前格式字符串 + 语言 + 时区

var dValue6 = date2str(dValue,"dd.MM.yyyy HH:mm:ss","EN", "EST");

var str = dValue1 + "\n" + dValue2 + "\n" + dValue3 + "\n" + dValue4 + "\n" + dValue5 + "\n" + dValue6;

Alert(str);

2.escapeStr1

unEscapeHtml

//js加密解密1

var escapeStr = "<html></html>";

//编码字符串

var escapeStr1 = escapeHtml(str);

//解码字符串

var escapeStr2 = unEscapeHtml(escapeStr1);

Alert(escapeStr1 + "\n" + escapeStr2);

3.escapeXml

unEscapeXml

//js加密解密2

var escapeXmlStr = "<html></html>" ;

//编码字符串

var escapeXmlStr1 = escapeXml(escapeXmlStr);

//解码字符串

var escapeXmlStr2 = unEscapeXml(escapeXmlStr1);

Alert(escapeXmlStr1 + "\n" + escapeXmlStr2);

4.escapeSQL

//js转换SQL，把单引号变成双引号

var escapeSQLStr = "a'b'c"

var escapeSQLStr1 = escapeSQL(escapeSQLStr);

Alert(escapeSQLStr1);

5.fillString

//把这个 char复制10遍

Alert(fillString("a",10));

6.getOcuranceString

//判断这个字符串在另外一个字符串有多少个

Alert(getOcuranceString("abcakldhiasdku","h"));

7.indexOf

//搜索字符串

var str1= "Hello Pentaho!";

//搜索某个指定的字符串值在字符串中首次出现的位置

var str2= indexOf(str1, "Pentaho");

//搜索某个指定的字符串值在字符串第7个之后中首次出现的位置

var str3= indexOf(str1, "o", 7);

Alert(str2 + "\n" +str3);

8.initCap

//返回经过处理的字符串 -My Home

var str1 = "my home";

Alert(initCap(str1));

9.lower

//把字符串的大写变成小写

var str1= "Hello World!";

var str2= lower(str1);

Alert("Input:" + str1);

Alert("Converted to LowerCase:" + str2);

10.upper

//把字符串变成大写

var str1= "Hello World!";

var str2= upper(str1);

Alert("Input:" + str1);

Alert("Converted to UpperCase:" + str2);

11.lpad

//判断字符串是否=>13,如果不够13，则在前面补足x至13个

var str1= "Hello World!";

Alert("Input:" + str1);

Alert("Lpad:" + lpad(str1, "x",13));

12.rpad

//判断字符串是否=>13,如果不够13，则在后面补足x至13个

var str1= "Hello World!";

Alert("Input:" + str1);

Alert("Rpad:" + rpad(str1, "x",13));

13.ltrim

Rtrim

trim

//去除字符串空格

var str1= " Hello World! ";

Alert("Input:" + str1+" "+str1.length);

//去除左边字符串

Alert("Ltrim:" + ltrim(str1)+" "+ltrim(str1).length);

//去除右边字符串

Alert("Rtrim:" + rtrim(str1)+" "+rtrim(str1).length);

//去除全部字符串空格

Alert("Trim:" + trim(str1)+" "+trim(str1).length);

14.num2str

//把数字变成字符串

var d1 = 123.40;

var d2 = -123.40;

//把数字变成字符串

Alert(num2str(d2));

//把字符串变成指定格式的字符串

Alert(num2str(d1, "0.00"));

//把字符串变成指定格式的字符串+区域

Alert(num2str(d1, "0.00", "EN"));

//把字符串变成指定格式的字符串+区域

Alert(num2str(d2, "0.00;(0.00)", "EN"));

15.protectXMLCDATA

//) 在XML中，指定某段内容不必被XML解析器解析时，使用<![CDATA[...]]>。也就是说中括号中的内容解析器不会去分析。所以其中可以包含>, <, &, ', "这5个特殊字符。经常把一段程序代码嵌入到<![DATA[...]]>中。因为代码中可能包含大量的 >, <, &, "这样的特殊字符。

var str1 = "my home";

Alert(protectXMLCDATA(str1));

16.removeCRLF

//去除字符串里面的回车 \n\r字符

var removeCRLFStr = "abc"+"\n"+"\r"+"abc";

Alert(removeCRLF(removeCRLFStr));

17.replace

//转换字符串

var str1 = "Hello World, this is a nice function";

//把字符串的World转换成 Folk

var str2 = replace(str1,"World", "Folk");

Alert(str2);

//把字符串的World转换成 Folk nice 转换成 beautifull

var str2 = replace(str1,"World", "Folk", "nice","beautifull");

Alert(str2);

18.str2RegExp

//跟据指定字符切割字符串

var strToMatch = "info@proconis.de";

var strReg = "^(\\w+)@([a-zA-Z_]+?)\\.([a-zA-Z]{2,3})$";

var xArr = str2RegExp(strToMatch, strReg);

if ( xArr != null ) {

for(i=0;i<xArr.length;i++) {

Alert(xArr[i]);

}

else {

Alert("no match");

}

19.substr

//截取字符串

var str1= "0123456789";

//截取前面6个字符串，返回6789

var str2= substr(str1, 6);

//截取8个字符串，从第2个开始，返回23456789

var str3 = substr(str1,2,8)

Alert(str2);

Alert(str3);

二，numeric function

1.abs

//返回绝对值

var d1 = -1234.01;

var d2 = 1234.01;

Alert(abs(d1));

Alert(abs(d2));

2.ceil

//返回相反数

var d1 = -1234.01;

var d2 = 1234.01;

Alert(ceil(d1));

Alert(ceil(d2));

3.floor

//返回该数值的最大整数

var d1 = -1234.01;

var d2 = 1234.01;

Alert(floor(d1));

Alert(floor(d2));

4.str2num

//把字符串转成数字

var str1 = "1.234,56";

var str2 = "12";

//把字符串按照指定的格式转换成数字

Alert((str2num(str1,"#,##0.00")));

Alert((str2num(str2)));

5.trunc

//四舍五入取整，只能传double

Alert(trunc(new Date()));

三，Date Function

1.dateAdd

//再Date时间上增加

// y - Years

// m - Months

// d - Days

// w - Weeks

// wd - Workingdays

// hh - Hours

// mi - Minutes

// ss - Seconds

var d1 = new Date();

Alert(dateAdd(d1, "y", 1));

Alert(dateAdd(d1, "m", 4));

Alert(dateAdd(d1, "d", 5));

Alert(dateAdd(d1, "wd", 5));

2.dateDiff

//返回俩个时间类型之间的差异

var d1 = new Date();

var d2 = dateAdd(d1,"y",1);

Alert(dateDiff(d1,d2,"y"));

Alert(dateDiff(d1,d2,"m"));

Alert(dateDiff(d1,d2,"d"));

Alert(dateDiff(d1,d2,"wd"));

3.getDayNumber

//返回一个数值，对应一个格式

var d1 = new Date();

Alert(d1);

//这一年的第几天

Alert(getDayNumber(d1, "y"));

//这个月的第几天

Alert(getDayNumber(d1, "m"));

//这个星期的第几天

Alert(getDayNumber(d1, "w"));

//这个月的第几个星期

Alert(getDayNumber(d1, "wm"));

4.getFiscalDate

//返回财政日期的日期值,基于给定的偏移量。格式 dd.MM.（暂时不知道怎么用）

var d1 = new Date();

var str1 = "01.00.";

var str2 = "00.01.";

Alert(getFiscalDate(d1, str1));

Alert(getFiscalDate(d1, str2));

5.getNextWorkingDay

//返回下一个工作日，星期4-星期5，星期5-星期1

var d = new Date();

var d1 = getNextWorkingDay(d);

Alert(getNextWorkingDay(d1));

6.month

//返回当前月份0-11

var d1 = new Date();

Alert(month(d1));

7.quarter

//返回当前季度 1-4

var d1 = new Date();

Alert(quarter(d1));

8.week

//返回当前是这一年的第几个星期year(var)

var d1 = new Date();

Alert(week(d1));

9.year

//返回当前是第几年

var d1 = new Date();

Alert(year(d1));

10.str2date

//按照字符串格式转换成Data数据

Alert(str2date("01.12.2006","dd.MM.yyyy"));

Alert(str2date("01.12.2006 23:23:01","dd.MM.yyyy HH:mm:ss"));

Alert(str2date("Tue.May.2006","E.MMM.yyyy","EN"));

Alert(str2date("22.02.2008 23:23:01","dd.MM.yyyy HH:mm:ss","DE"));

Alert(str2date("22.02.2008 23:23:01","dd.MM.yyyy HH:mm:ss","DE", "EST"));

四，Logic Function

1.isCodepage

//检查,一个字符串值对给定的代码页（不知道怎么用）

var xStr = "RÃ©al";

Alert(isCodepage(xStr, "UTF-8"));

2.isDate

//检查当前变量是不是时间变量

var d1 = "Hello World";

var d2 = new Date();

Alert(isDate(d1));

Alert(isDate(d2));

3.isEmpty

//检查字符串是不是空的

var d = "Hello World";

Alert(isEmpty(d));

4.isMailValid

//检查这个邮件地址是不是对的

var str = "qq132165432168468@.com";

Alert(isMailValid(str));

5.isNum

//检查这个有变量是不是数字

var str1 = "Hello World";

var str2 = 123456;

Alert(isNum(str1));

Alert(isNum(str2));

6.isRegExp

//正则表达式匹配，先匹配第一个，如果没有直接返回-1，如果有接下去匹配，返回匹配条数

var email1 ="info@proconis.de";

var email2= "support@proconis.co.uk";

var email3= "HelloWorld@x";

var str = "啊";

var reg1="^\\w+@[a-zA-Z_]+?\\.[a-zA-Z]{2,3}$";

var reg2="^[\\w-\.]+@([\\w-]+\\.)+[\\w-]{2,4}$";

var reg3 ="[^\x00-\xff]";

Alert(isRegExp(email1, reg1,reg2) + " Matches");

Alert(isRegExp(email2, reg1,reg2) + " Matches");

Alert(isRegExp(email3, reg1,reg2) + " Matches");

Alert(isRegExp(str, reg3) + " Matches");

7.isWorkingDay

//判断当前时间是不是工作日

var d1 = new Date();

Alert(isWorkingDay(d1));

五，Special Function

1.Alert

//消息弹出框

Alert("Hello World!");

2.LoadScriptFile

//读取当前路径下的JavaScript文件

var xPfad = "C:/Users/LiHan/Desktop/adc.js";

LoadScriptFile(xPfad);

3.LoadScriptFromTab

//从表里读取Script(不知道怎么用)

var xTab = "Alert("+"Hello World!"+")";

LoadScriptFromTab(xTab);

4.LuhnCheck

//判断当前卡号是不是正确信用卡号

var str1 = "4444333322221111";

Alert(str1 + ": " + LuhnCheck(str1));

var str2 = "4444333322221110";

Alert(str2 + ": " + LuhnCheck(str2));

5.appendToFile

//把数据添加到文件。如果文件不存在,它将被创建。

var strFile = "‪C:/Users/LiHan/Desktop/abc.txt";

var strContent = "Hello World!";

appendToFile(strFile, strContent);

6.decode

//匹配字符串，返回指指定字符串或默认值

var str1 = "Hallo";

//匹配hallo 如果有返回hello，没有返回Hallo

Alert(decode(str1, "Hall", "Hello"));

//匹配1，如果有返回Mr。匹配2，如果有返回Mrs，没有返回指定值N/A

Alert(decode(str1, "1", "Mr", "2", "Mrs", "N/A"));

//匹配1，如果有返回Mr。匹配2，如果有返回Mrs,没有返回默认值，原字符串

Alert(decode(str1, "1", "Mr", "2", "Mrs"));

var str1 = "Mrs";

//匹配1，如果有返回Mr，匹配2，如果有返回Mrs，没有返回默认值，原字符串

Alert(decode(str1, "1", "Mr", "2", "Mrs"));

7.execProcess

//执行一个processus（不知道什么东西），返回执行结果

var processus="文件路径";

execProcess(processus);

8.fireToDB

//使用时（找不到MySQL连接不知道怎么使用）

var strConn = "MySQL";

var strSQL = "SELECT count(*) from ttt";

var xArr = fireToDB(strConn, strSQL);

9.getDigitsOnly

removeDigits

//返回字符串包含的数字的值

var str1 = "abc123cde";

Alert(getDigitsOnly(str1));

//去除字符串中的数字

var str1 = "abc123cde";

Alert(removeDigits(str1));

10.getEnvironmentVar

setEnvironmentVar

//获得kettle系统变量的值

Alert(getEnvironmentVar("user.dir"));

Alert(getEnvironmentVar("user.name"));

//设置系统全局变量

var strVarName="setEnvTest";

var strVarValue="123456";

Alert(getEnvironmentVar(strVarName));

setEnvironmentVar(strVarName,strVarValue);

Alert(getEnvironmentVar(strVarName));

strVarValue="654321";

setEnvironmentVar(strVarName,strVarValue);

Alert(getEnvironmentVar(strVarName));

11.getProcessCount

//返回当前处理行数

// 1: String - The Pentaho/Kettle Type:

// u - Lines Update

// i - Lines Insert

// w - Lines Write

// r - Lines Read

// o - Lines Output

Alert(getProcessCount("i"));

Alert(getProcessCount("o"));

12.getTransformationName

//返回当前js步骤名称，如果在转换中运行加上转换的名字

var xTranName = getTransformationName();

Alert(xTranName);

13.setVariable

getVariable

//设置kettle变量

var strVarName="getVariableTest";

var strVarValue="123456";

// 1: String - The name of the variable to set.

// 2: Variable - The value which should be assigned

// 3: Type - "s"ystem, "r"oot, "p"arent, "g"randparent. Upto which

// level the variable is set.

setVariable(strVarName,strVarValue, "r");

//获取kettle变量，如果没有显示为空

Alert(getVariable("strVarName", ""));

14.Print

Println

//在结果中打印出这个字符串

var str = "Hello World!";

print(str);

//加一个换行

Println(str);

15.resolveIP

//转换为一个数字IP地址(x.x.x.x)作为字符串返回(不知道怎么用)

Alert(resolveIP("127.0.0.1","IP"));

16.sendMail

//把电子邮件发送到指定的地址

// sendMail(var, var, var, var, var);

// 1: String - SMTP-Server

// 2: String - The Address from which the mail shoud be send

// 3: String - The Address to which the mail should be send.

// For more Recipient's, please provide a list seperated by ','

// 4: String - Subject of the Mail

// 5: String - The Message Text of the mail.

var xSMTP="Your SMTP Server";

var xSender = "Sender eMail";

var xRecipients = "Recipient1,Recipient2,...";

var xSubject = "Hello World!";

var xMessage = "This is the message";

sendMail(xSMTP,xSender,xRecipients,xSubject,xMessage);

17.writeToLog

//写一个字符串到kettle日志里

// writeToLog(var);

// 1: String - The Message which should be written to

// the Kettle Debug Log

// writeToLog(var,var);

// 1: String - The Type of the Log

// d - Debug

// l - Detailed

// e - Error

// m - Minimal

// r - RowLevel

// 2: String - The Message which should be written to

// the Kettle Log

writeToLog("Hello World!");

writeToLog("r", "Hello World!");

六，File Function

1.copyFile

//复制一个文件

// copyFile(var1,var2,var3);

// 1: String - The file to copy with Path.

// 2: String - The destination filename with Path.

// 3: Boolean- Overwrite destination file if exist.

var sourcefile="‪C:/Users/LiHan/Desktop/abc.txt";

var destinationfile="D:/abc.txt";

copyFile(sourcefile,destinationfile,true);

2.createFolder

//在指定路径创建一个文件夹

var strFolder = "‪C:/Users/LiHan/Desktop/abcd";

createFolder(strFolder);

3.deletFile

//删除指定路径的文件

var strFile = "‪C:/Users/LiHan/Desktop/abc.txt";

deletFile(strFile);

4.fileExists

//判断文件是否存在

var strFile = "‪C:/Users/LiHan/Desktop/文件路径.xlsx";

Alert(fileExists(strFile));

5.getFileExtension

//返回文件类型