col-cache: optimize for performance

myfreeer · myfreeer · commit e8731f8eaaff · 2020-10-05T22:20:36.000+08:00
Doing a profiling in chrome dev tools shows that the function decodeAddress and validateAddress in lib/utils/col-cache.js are taking considerable amount of time, mainly on the cache lookup and regexp matching. This would make that faster, especially with large worksheets.
diff --git a/lib/utils/col-cache.js b/lib/utils/col-cache.js
@@ -1,3 +1,4 @@
+const addressRegex = /^[A-Z]+\d+$/;
 // =========================================================================
 // Column Letter to Number conversion
 const colCache = {
@@ -29,6 +30,7 @@ const colCache = {
     'Y',
     'Z',
   ],
+  _l2nFill: 0,
   _l2n: {},
   _n2l: [],
   _level(n) {
@@ -48,18 +50,20 @@ const colCache = {
     let l3;
     let n = 1;
     if (level >= 4) {
-      throw new Error(`Out of bounds. Excel supports columns from 1 to 16384`);
+      throw new Error('Out of bounds. Excel supports columns from 1 to 16384');
     }
-    if (level >= 1) {
+    if (this._l2nFill < 1 && level >= 1) {
       while (n <= 26) {
         c = this._dictionary[n - 1];
         this._n2l[n] = c;
         this._l2n[c] = n;
         n++;
       }
+      this._l2nFill = 1;
     }
-    if (level >= 2) {
-      while (n <= 26 + 26 * 26) {
+    if (this._l2nFill < 2 && level >= 2) {
+      n = 27;
+      while (n <= 26 + (26 * 26)) {
         v = n - (26 + 1);
         l1 = v % 26;
         l2 = Math.floor(v / 26);
@@ -68,10 +72,12 @@ const colCache = {
         this._l2n[c] = n;
         n++;
       }
+      this._l2nFill = 2;
     }
-    if (level >= 3) {
+    if (this._l2nFill < 3 && level >= 3) {
+      n = 26 + (26 * 26) + 1;
       while (n <= 16384) {
-        v = n - (26 * 26 + 26 + 1);
+        v = n - ((26 * 26) + 26 + 1);
         l1 = v % 26;
         l2 = Math.floor(v / 26) % 26;
         l3 = Math.floor(v / (26 * 26));
@@ -80,6 +86,7 @@ const colCache = {
         this._l2n[c] = n;
         n++;
       }
+      this._l2nFill = 3;
     }
   },
   l2n(l) {
@@ -107,41 +114,63 @@ const colCache = {
 
   // check if value looks like an address
   validateAddress(value) {
-    if (!value.match(/^[A-Z]+\d+$/)) {
+    if (!addressRegex.test(value)) {
       throw new Error(`Invalid Address: ${value}`);
     }
     return true;
   },
 
   // convert address string into structure
   decodeAddress(value) {
-    const addr = this._hash[value];
+    const addr = value.length < 5 && this._hash[value];
     if (addr) {
       return addr;
     }
-    const matchCol = value.match(/[A-Z]+/);
-    let col;
-    let colNumber;
-    if (matchCol) {
-      col = matchCol[0];
-      colNumber = this.l2n(col);
+    let hasCol = false;
+    let col = '';
+    let colNumber = 0;
+    let hasRow = false;
+    let row = '';
+    let rowNumber = 0;
+    for (let i = 0, char; i < value.length; i++) {
+      char = value.charCodeAt(i);
+      // col should before row
+      if (!hasRow && char >= 65 && char <= 90) {
+        // 65 = 'A'.charCodeAt(0)
+        // 90 = 'Z'.charCodeAt(0)
+        hasCol = true;
+        col += value[i];
+        // colNumber starts from 1
+        colNumber = (colNumber * 26) + char - 64;
+      } else if (char >= 48 && char <= 57) {
+        // 48 = '0'.charCodeAt(0)
+        // 57 = '9'.charCodeAt(0)
+        hasRow = true;
+        row += value[i];
+        // rowNumber starts from 0
+        rowNumber = (rowNumber * 10) + char - 48;
+      } else if (hasRow && hasCol && char !== 36) {
+        // 36 = '$'.charCodeAt(0)
+        break;
+      }
+    }
+    if (!hasCol) {
+      colNumber = undefined;
+    } else if (colNumber > 16384) {
+      throw new Error(`Out of bounds. Invalid column letter: ${col}`);
     }
-    const matchRow = value.match(/\d+/);
-    let row;
-    let rowNumber;
-    if (matchRow) {
-      row = matchRow[0];
-      rowNumber = parseInt(row, 10);
+    if (!hasRow) {
+      rowNumber = undefined;
     }
 
     // in case $row$col
-    value = (col || '') + (row || '');
+    value = col + row;
 
     const address = {
       address: value,
       col: colNumber,
       row: rowNumber,
-      $col$row: `$${col || ''}$${row || ''}`,
+      $col$row: `$${col}$${row}`,
     };
 
     // mem fix - cache only the tl 100x100 square
