Skip to content
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

col-cache: optimize for performance #1489

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
col-cache: optimize for performance 
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.
  • Loading branch information
@myfreeer
myfreeer committed Oct 5, 2020
commit e8731f8eaaffe9bd4eb795f6159f70498d2d7b35
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