计算谷歌应用程序脚本中的CRC-16/CCITT-FALSE

Question

我有VBA代码，可以计算文本字符串的CRC16 CCITT值，现在我计划在谷歌工作表上使用它，但不知道如何将VBA代码转换为谷歌应用程序脚本。

Function crc_ccitt_ffff(strParam As String) As String
Const CRC_POLY_CCITT       As Long = &H1021&
Const CRC_START_CCITT_FFFF As Long = &HFFFF&
Dim crc_tabccitt(0 To 255) As Long, crc As Long, b() As Byte, c As Long, i As Long, j As Long
    
   
For i = 0 To 255
crc = 0
c = i * 256
For j = 0 To 7
If (crc Xor c) And 32768 Then
crc = (crc * 2) Xor CRC_POLY_CCITT
Else
crc = crc * 2
End If
c = c * 2
Next j
crc_tabccitt(i) = crc
Next i
    
b = strParam
crc = CRC_START_CCITT_FFFF
For i = 0 To UBound(b) Step 2
crc = (crc * 256) Xor crc_tabccitt(((crc  256) Xor b(i)) And 255)
crc = ((crc  65536) * 65536) Xor crc
Next i
crc_ccitt_ffff = Hex(crc)
End Function

测试载体：

00020101021129370016A000000677010111021312345678901215802TH5406500.5553037646304

预期结果：3D85

Answer 1

尝试下面的函数。此代码将获得您引用的测试字符串的预期结果。

此自定义函数将用于单个文本字符串参数或包含文本字符串的单元格区域。它只计算文本字符串的校验和——忽略空单元格和数字单元格。

/**
* Calculates a CRC-16/CCITT-FALSE checksum.
* 
* @param {A2:D42} text A range of text strings for which to calculate checksums.
* @param {true} hex_output Use true to get hexadecimal results, and false to get decimal results. Defaults to true.
* @return {String[][]} The hexadecimal or decimal checksums for text.
* @customfunction
*/
function crc_ccitt_ffff(text, hex_output = true) {
// adapted from https://github.com/damonlear/CRC16-CCITT
// by https://stackoverflow.com/users/13045193/doubleunary
// for https://stackoverflow.com/q/68235740/13045193
// 在线校验工具及相关说明：http://www.ip33.com/crc.html
if (!Array.isArray(text))
text = [[text]];
const polynomial = 0x1021;
return text.map(row => row.map(string => {
if (!string.length)
return null;
const bytes = Array.from(String(string))
.map(char => char.charCodeAt(0) & 0xff); // gives 8 bits; higher bits get discarded
let crc = 0xffff;
bytes.forEach(byte => {
for (let i = 0; i < 8; i++) {
let bit = 1 === (byte >> (7 - i) & 1);
let c15 = 1 === (crc >> 15 & 1);
crc <<= 1;
if (c15 ^ bit)
crc ^= polynomial;
}
});
crc &= 0xffff;
return hex_output ? crc.toString(16).toUpperCase() : crc;
}));
}