google-apps-script - 在 Google Apps 脚本中计算 CRC-16/CCITT-FALSE

Question

我有计算文本字符串的 CRC16 CCITT 值的 VBA 代码，现在我打算在 Google Sheet 上使用它，但不知道如何将 VBA 代码转换为 Google Apps 脚本。

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

标签： google-apps-scriptgoogle-sheets

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;
  }));
}