Skip to content

col-cache: optimize for performance #1489

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Oct 5, 2020
Merged

col-cache: optimize for performance #1489

Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
73 changes: 51 additions & 22 deletions lib/utils/col-cache.js
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,3 +1,4 @@
const addressRegex = /^[A-Z]+\d+$/;
// =========================================================================
// Column Letter to Number conversion
const colCache = {
Expand Down Expand Up @@ -29,6 +30,7 @@ const colCache = {
'Y',
'Z',
],
_l2nFill: 0,
_l2n: {},
_n2l: [],
_level(n) {
Expand All @@ -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);
Expand All @@ -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));
Expand All @@ -80,6 +86,7 @@ const colCache = {
this._l2n[c] = n;
n++;
}
this._l2nFill = 3;
}
},
l2n(l) {
Expand Down Expand Up @@ -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
Expand Down